Assignment #5
Due Tuesday, April 22
75 points
In this assignment you will have an opportunity to work with database
design and write some SQL.
Although PHP does not really factor into
this assignment, this assignment is setting the stage for the next
assignment which will use PHP extensively. Therefore, you should not
let your PHP skills fall by the wayside. I’d highly recommend that
you continue to practice your PHP programming as you work on this assignment.
Since you will not be creating any Web pages for this assignment, the
submission will be done differently. There will be several text
files and a diagram that you will need to submit. Since they are simply
text files, you may create the SQL files in any text editor. The output
files you will create by executing your SQL files with the mysql command and
capturing the output with redirection. The command line you will use
for this will look something like the following:
mysql -uxxx -pyyy -e 'source in.sql' >& out.txt
Where xxx is your username, yyy is your MySQL password,
in.sql is the name of your SQL file (which can be anything you
wish), and out.txt is the name of your output file (which also
can be anything you wish).
Run the MySQL command line client interactively as discussed
in lecture while testing and refining your SQL, and then use the above
command line at the Unix command shell prompt to generate your output
for submission once you are all done.
You may create the ER model diagram by whatever means you see fit.
I am not requiring that you use graphics software to create the diagram,
but I do expect that the end result will be neat, correct and professional.
Therefore, you may find that using some sort of vector-based graphics
editor helps eliminate some of the frustration of trying to arrange
the various parts of the diagram neatly on the page. If you do decide
to draw it by hand, you will likely find that you’ll need several
rough drafts prior to investing the time to do your final draft as neatly
as possible.
The text files may be e-mailed to me any time before the deadline. However,
to send the ER model diagram by e-mail you must first get it into digital
form. If you use a graphics editor to create it, simply save it into
a reasonably common image file format, such as GIF or PDF. If you draw
it by hand, you will probably find it easier to simply submit it to
me in hard copy form prior to the deadline (which means you will need
to have it done well ahead of the midnight deadline). If
you draw the diagram by hand and prefer to send it to me by e-mail
you will first need to scan your hand-drawn diagram into digital
form, such as a GIF or PDF file.
Unlike the last assignment, you should treat all parts of this assignment
with equal priority. Invest the necessary time and effort to get as
much of this assignment done as possible. We will build upon these
results in the next assignment.
- (20 points) Write an SQL file that will insert
the contents of the guestbook.txt file from the previous assignment
into a table. All of your SQL statements should be included in this
file. The SQL file should perform the following tasks (in order):
- Select your database as the current database
- Delete the guestbook table if it exists. This will
allow you to run the SQL repeatedly without getting errors.
- Define the guestbook table. Include all the attributes
and any indexes that you think are appropriate.
- Fill the guestbook table. Each line of your guestbook.txt
file should be inserted as one row of your guestbook table. You
should be able to do them all with a single SQL statement as
discussed in lecture.
- Display the guestbook table by listing all the rows in their entirety.
Submit your original guestbook.txt file, your SQL file, and a text file
containing the results of running the SQL file (as described above).
Although it should not be necessary, you are allowed to write a PHP
script to “fine-tune” your guestbook.txt file if you find that
adjustments are necessary to make things work properly. However,
if you do, you should save and submit a copy of the original guestbook.txt
file (prior to modification) along with the modified guestbook.txt
file (after modification) and the PHP script used to perform the
modification (commented to explain the problem it is addressing).
-
(55 points) Design and implement a relational database
for organizing a collection of music CDs.
- Follow the design process outlined in lecture (including
our simplified approach to normalization) to decide what tables
you will need, what attributes they will contain, and how to organize
them. For each CD in the collection, you should (at least) store
the title, the recording artist, the URL of the artist’s official
Web site, the year of release, the recording label, the mailing
address of the label, the list of tracks, and the play time (or
length) of each track. Be sure you read and understand this entire
specification before you begin your design process, since your
design will need to support all the requirements stated below,
and you may need to add additional attributes and/or tables to
make that happen. When you have your design done, document it with
a neat and complete ER model diagram.
- Write an SQL file that will implement your design.
All of your SQL statements should be included in this file. As
described in part 1 above, you should delete each table, if it exists,
before creating it to facilitate repeated runs of the file. Be sure that
your SQL fully implements your design, taking into account primary
keys, foreign keys and indexes.
- Add at least 5 CDs to your tables. Most of the information
you need should be available on the CD packaging, but some may
require a bit of research. To find URLs, for instance, you may
need to do a few Web searches. And to find the play time of each
track, you may need to either do some Web searches or load the
CD into a player on your computer. While I’d prefer accurate information,
you may make up reasonable values for missing data as a last resort.
If you wish, you may load the bulk of the data into the tables
by importing from text files (as you did in part 1 above). However,
there should be at least one INSERT statement for each table in
your SQL as well. Be careful to maintain consistency among the
primary and foreign keys! Add the necessary SQL statements to your
SQL file to insert the data into the tables, then add statements
that will display each table’s
contents in their entirety.
- Perform some updates on your tables. Add the necessary
code to your SQL file to perform some updates on the data stored
in the tables. The updates you perform are up to you, but the creativity,
variety, complexity and appropriateness of the updates you perform
will be considered during grading. Look over the output of the
tables from the previous step and try to think of updates that
will ultimately add to the utility of the data. Although we did
not discuss the ALTER TABLE statement in lecture, you are welcome
to research and use it to add new attributes to your tables as
part of the updating process. Following the updates, add code to
redisplay each table’s contents in their entirety.
- Perform some queries on your tables. Add the necessary
code to your SQL file to perform some queries on the data stored
in the tables. At the very least, you should include the following
queries:
- Display the title and release date of all CDs in the collection,
alphabetical by title
- Display the title, artist and release date of all CDs in the collection,
alphabetical by artist
- Display the list of tracks on a single CD (just pick one) in the
order they appear on the CD
- Display the name, play time, track number and CD title of each track
in the collection in alphabetical order by track name
Create at least three additional queries. For additional queries, you
will be graded on their creativity,
variety, complexity and appropriateness.
Submit your ER model diagram, the SQL file, the output file and any other
files you produced as part of the design or data entry phases.