Due: February 4, 2000; 5pm.
Please: Be sure to read the whole assignment from start to finish before you start working; there are numerous dependencies where the wrong design choice early-on could hurt you later.
Objective: Provide the infrastructure so that your site can support large numbers of MP3 files and support quick and flexible searches.
Groups & Collaboration: Starting with this assignment, students will form into groups of two or three students. We encourage groups to collaborate, provided that (1) each group does the reading and spends some time thinking about each problem before discussing it with others, and (2) each group writes up each problem itself. Directly copying another group's work (architectures, diagrams, or code) is cheating, a serious offense.
The first step is creating a short name for your group and sending Tessa the name along with the usernames of the group participants; she'll create a MySQL database for each group and name it using the group name.
Your next step is designing the database schema, i.e. answering the following questions: How many tables should we create? Which attributes will each table have? What data type should be used for each attribute? Which attributes should have indicies? If a group member has taken a database class, he or she will have studied normalization procedures and should know quite a bit about this schema design problem, but since this is a relatively small project it's pretty easy to come up with a reasonable design by following one's intuitions. You should feel to use any design you think is best, but one reasonable choice is to use the following two tables:
band(name, location, genre)
track(title, band, size-in-mb, source, url)
where name is the key (i.e. is presumed unique) of the band relation, and the band attribute of the track relation indexes into the band relation. It's probably wise to have indicies on name and title attributes; possibly also on band and genre.
Since bands often release multiple recordings (e.g., studio, live, etc.) of a given song, it seems likely that one needs to give a title, band, and date in order to uniquely specify a track. Feel free to augment your tables in order to store this information, pictures, lyrics, or any other information you like. You might wish to add another string field to store any XML data which doesn't match the relational attributes you have defined (or you could simply discard all that info). Or you may choose a completely different table design if you prefer. Simple is fine, but we'll award extra credit for ambitious efforts.
Once you have designed your table format, you'll want to connect to the database and create the tables. Probably the easiest way to do this is through the command-line program mysql. Invoke it by specifying your username and password: mysql -u user -p (which will prompt you to type in your password).
To help get you started, we provide a sample Java program which uses the Xerces parser to create a DOM tree and extract elements. Specifically, /www/htdocs/project2/xmlparse.java parses an XML document (such as the hello.xml file in that directory) and extracts <title> elements and their contents. There are plenty of XML parsers, and you are welcome to use a different one if you choose. We expect (hope?) that the choice of parser shouldn't matter much, since the DOM part of the XML standard hasn't fluctuated nearly as much as the XSL parts we used in part 1, and the parsers have been around longer and appear substantially more stable.
Parsing XML is just a a matter of instantiating the parser, converting a relative filename into a file:// URL, and feeding that URL to the parser. The hardest part is figuring out what to do with a DOM tree once you get it from the parser. You should read the DOM specification at w3.org in conjunction with its appendix on the Java language binding.
We also provide DB.java (in the same directory) which will insert a (name, age) pair into the test database, and print the updated contents of the database after the insert.
A final approach is to use the SQLProcessor part of Cocoon to generate dynamic search results. With this approach, however, it seems that you have to embed the SQL query into the XML file rather than having it formulated on the fly. It's probably possible to write server-side JavaScript to generate a query dynamically, and have Cocoon render the page twice (once to turn the query paramaters into a SQL query using JavaScript, and another time to turn the XMLified query results into HTML). You can see a demo of this approach as well as read documentation.
You are welcome to use any approach you wish; just explain what you did and why.
Note that a small part of the grade will depend on the cleanliness and usability of your forms interface. You might wish to look at several professional sites to get some tips about usability.