BCIS 3680 Enterprise Programming Instructor: Dr. Andy Wu

Assignment 5

Part 1. Building Database In this part of our development of Music Galore, we will build the database to which the web front‐ end will connect. Create a MySQL database named (for example, CaseJ for Justin Case) and create the following tables. Database Schema 1. An album table to contain album information:
Field sku title genre label format Use SKU number of album Album title Genre of album Label (distributor) of album Media format of album (CD, CD+DVD, vinyl, etc.) Release date Price of album Data to Contain Integer, must have value, primary key String, up to 96 characters, must have value String, up to 50 characters String, up to 50 characters String, up to 24 characters

rlsdate price

Date value, in yyyy-mm-dd format Float

2. An artist table to contain information on singers, bands, producers, etc.
Field aid name1 name2 amgid url Artist ID Last name or name of band First name (if band, leave it blank) All Music Guide (allmusic.com) ID URL of artist’s official website Use Data to Contain Integer, must have value, primary key, auto-numbered String, up to 72 characters, must have value String, up to 50 characters String, up to 10 characters Large text field. Input should be limited to 2083 1 characters

1

The length limitation should be enforced programmatically. In MySQL script, simply use text datatype.

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 1

3. A music table to contain individual pieces of music:
Field mid ID of music Use Data to Contain Integer, must have value, primary key, autonumbered String, up to 96 characters, must have value

title

Title of music

4. An associative table named musicalbum that relates music to an album, i.e., tracklist of the album:
Field sku SKU of album Use Data to Contain Part of primary key, foreign key referring to sku in album table Part of primary key, foreign key referring to mid in music table Part of primary key, integer, must have value

mid

ID of music

trackno

Track number (music is included in album as the #th track) Duration of track (in seconds)

duration

Integer

Notes: Don’t be too liberal with the use of auto‐numbered primary keys. Designate a primary key column as auto‐numbered only when instructed to do so. You are required to use char data type for textual data (string) here. However, it’s recommended that you read about the varchar datatype and know the pros and cons of the two textual datatypes. In these assignments, foreign key relationships and constraints will not be enforced in MySQL, due to the recommended MyISAM engine. You may optionally enforce referential integrity in JSP code. Database Scripts Write a script (create.sql) to create the tables. To make testing your script easier, it is recommended that you have these lines at the top of your script: drop table musicalbum; drop table artist; drop table album; drop table music; These lines will undo whatever previous effort you made to create and populate the tables. It wipes everything clean in the database so previous data won’t get in your way. Follow these lines with your scripts to create the above mentioned tables.

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 2

Write another script (populate.sql) to populate the tables. Note that the information below is presented in a way that is friendly to human readers, not to MySQL. Many parts of it may not be in a format that is compatible with the data types of table columns. If that’s the case, you want to make proper modifications before you enter them into the VALUES clauses. In particular, if an entity (e.g., album, music) is described below in textual form while in the table it’s represented as a foreign key, you should look up the primary key of the entity in its own table and enter it into the focal table as a foreign key. 1. In the album table, add the following albums (not necessarily my favorites) and their primary keys and related information:
217001, Falling Into You, Rock, 550 Music/Epic, CD, Mar. 12, 1996, 6.99 217008, Use Somebody, Rock, RCA, CD, Dec. 8, 2008, 10.99 217015, I Am… Sasha Fierce, R&B, Sony, CD, Nov. 18, 2008, 12.49 217022, Braveheart, Soundtracks, PolyGram, CD, Oct. 7, 1997, 7.99 217029, Dangerously in Love, R&B, Columbia, CD, Jun. 24, 2003, 9.95 217036, The Very Best of Mozart, Classical, Naxos, CD, Oct. 18, 2005, 11.99 217043, Avatar, Soundtracks, Atlantic, CD, Dec .15, 2009, 12.95 217050, Wolfgang Amadeus Phoenix, Rock, Glass Note, Vinyl, May 25, 2009, 17.99 217057, Faith, Rock, Sony, CD+DVD, Jan. 1, 1987, 19.95

2. In the artist table, add the following artists (not necessarily my favorites) and their primary keys and other information. (Note: If URL is not available, the field should contain null.)
1, James Horner, q7470, no URL, 2, London Symphony Orchestra, q37333, http://lso.co.uk 3, Beyonce, p349078, http://www.beyonceonline.com 4, Wolfgang Amadeus Mozart, q7754, no URL 5, Celine Dion, p4099, http://www.celiondion.com 6, Kings of Leon, p554165, http://www.kingsofleon.com/home 7, Bob Marley, p2907, http://www.bobmarley.com 8, Phoenix, p401761, http://www.wearephoenix.com 9, Pet Shop Boys, p5135, http://www.petshopboys.co.uk 10, George Michael, p4909, http://www.georgemichael.com

3. In the music table, add the following songs and their primary keys. (Note: Apostrophes in music titles must be part of the value stored; hint – use escape sequence.)
10001, It’s all coming back to me 10002, Because you loved me 10003, Falling into you 10004, Make you happy 10005, Seduces me 10006, All by myself 10007, If I were a boy 10008, Halo 10009, Disappear 10010, Broken-hearted girl 10011, Ave Maria 10012, Satellites

4. In the musicalbum table, add the following association between music and albums:
Falling Into You, It’s all coming back to me, Track #1, 457 seconds Falling Into You, Because you loved me, Track #2, 273 seconds Falling Into You, Falling into you, Track #3, 258 seconds Falling Into You, Make you happy, Track #4, 271 seconds Falling Into You, Seduces me, Track #5, 226 seconds

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 3

Falling Into You, All by myself, Track #6, 312 seconds I Am…Sasha Fierce, If I were a boy, Track #1, 249 seconds I Am…Sasha Fierce, Halo, Track #2, 261 seconds I Am…Sasha Fierce, Disappear, Track #3, 267 seconds I Am…Sasha Fierce, Broken-hearted girl, Track #4, 277 seconds I Am…Sasha Fierce, Ave Maria, Track # 5, 222 seconds I Am…Sasha Fierce, Satellites, Track #6, 187 seconds

Part 2. Web Pages Now you will actually build the dynamic pages on the Music Galore site. You will modify the existing music.jsp (from Assignment 4) and create two more JSP files: artists.jsp and albumdetail.jsp. Also, you will create two HTML files: header.htm and footer.htm. You must have these navigation links at the top of each page. Leave the Contact Us, and Help links inactive. If you prefer the underlined, hyperlinked look, you may set their href property to #.
Hyperlink Text Home Albums Artists Destination default.jsp music.jsp artists.jsp Hyperlink Text Contact Us Help Destination None or # None or #

1. Make a copy of your deployment folder for Assignment 4 and rename it for Assignment 5. 2. Build a header file called header.htm that contains the above navigation links. Then, build a footer.htm file that contains a link to the Media Galore home page (default.jsp) and copyright notice. Every new JSP file should include the header and footer. The title bars of the JSPs should be specific to the nature of the page, instead of a generic title. 3. The music.jsp page lists all albums in the database:

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 4

Specifications of this page: For each album, display the regular price from the database column price. In addition, in the HTML table, display another column for Sales Price. The sales price for each album is 80% of the regular price. Both regular and sales prices must be displayed in the currency format (with dollar sign and two places after the decimal point). For each album, add a radio button at the beginning of its row. Set the value attribute of the radio button to the SKU of that album. Name the radio button group selSKU. This sets up the radio buttons for what we will do in Assignment 6. No further coding is needed at this point. Tip: Create a DecimalFormat object and use the proper display mask for the price figures. 4. The title of each album is displayed as a hyperlink. When clicked, a page (albumdetail.jsp) should appear and display some information on the album. For example, clicking the title Falling Into You on music.jsp causes the following page to be displayed:

Specifications of this page: The title bar displays the title of the album. The top portion of this file displays the summary information of the album: title, label, genre, and release date. Note that the release date is displayed in full, and the three parts (year, month, and day) are separated by two slashes (/), not dashes (‐). The bottom portion is a listing of tracks in the album (for simplicity we display only six tracks).

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 5

For each track, display its track number in the album (as recorded in the musicalbum table), title, and duration. If a track title contains an apostrophe (e.g., It’s All Coming Back to Me), it should be displayed. Note that the duration is displayed in the mm:ss format. If the number in the seconds part is less than ten, there is a leading zero in front of it (e.g., the “09” part in the first track). However, even if the minutes part is less than ten minutes, there is no leading zero. Tip: To convert duration in seconds to the #:## format, after you retrieve the duration value from the musicalbum table, divide it by 60 and round the result to obtain the minutes part. Then run a modulus operation on the duration value to obtain the seconds part.

5. Clicking the Artists link displays artists.jsp, which shows all entries in the artist table:

Specifications of this page: If an artist has both a first name and a last name (e.g., Celine Dion), his/her name must be displayed in the order of first name followed by last name. If the artist is a band or orchestra, its name as entered in the name1 field in the table should be displayed. The list must be sorted by last name/band name in alphabetic order, as shown above. The Artist ID: line displays the artist’s ID as stored in the aid column in the artist table. The AllMusic Page: line shows the URL to the artist’s biography page on the All Music Guide website. This URL follows a standard format: http://www.allmusic.com/ artist/-. The part is the artist’s full name, with all parts connected by hyphens. For example, it is kings-of-leon for Kings of Leon;

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 6

wolfgang-amadeus-mozart for Mozart, and so on. Replace the part with the value you retrieve from the amgid column in the artist table. The Official Website: line shows the URL of an artist’s official website, Facebook page, fan site, etc., if available. The value comes from the url column in the artist table. If for an artist that column contains no information, then this line, including the Official Website: heading, does not appear at all. For example, notice the difference between James Horner (without URL) and Kings of Leon (with URL):

Clicking the AllMusic link opens up the artist bio page in a separate window (the default setting in Firefox is to open in a new tab):

Tip: To generate the part of the AllMusic link, use the replace() method of the String class and replace the spaces in names with hyphens. One version of the method takes two parameters, a char that stands for the character to be replaced and another char as the replacement character. Similarly, clicking the Website link opens the site in a new window/tab:

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 7

 Preparing Your Submission In the deployment/submission folder, include your database data files and scripts that created them. In your Assignment 5 submission folder, add a database subfolder. In your mySQL data folder (which you set as the value of the datadir attribute in the my.ini configuration file), locate your data folder for the assignment, e.g., Justin Case has these contents in his (note there are three files for each table, plus a file called db.opt):

Go up one level, copy this folder, as well as the mysql folder. Paste both folders into the database folder you created earlier. Also include in this folder the two scripts – create.sql and populate.sql. After you have done the above, your database subfolder will contain two files and two subfolders, similar to Justin’s below:

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 8

You submission folder now looks something like this (except artistdetail.jsp, which is not required in this assignment):

Submission Due Date: Fri., Nov. 30, 2012 (11:59 PM) Submission Requirements: No hard copy is required. Zip your submission folder into BCIS3680-Assignment5-YourLastNameYourFirstName.zip (RAR is fine too). Submit the zip file as an attachment on Blackboard.

BCIS 3680 Enterprise Programming

Assignment 5 ‐ 9