CS430 - Database Systems - Project 1 - Notown Records

Fall Semester, 2004

Due in class on Monday, September 27th, 2004

Notown records was so impressed with the ERD that you showed them that they hired you to produce a database schema and a few basic reports for their business.

Using your design from exercise 2.5 produce a relational schema, test data, and the reports described below.

Be sure to normalize your data model, i.e. no repeating sub-groups, unique primary keys, appropriate foreign keys, referential integrity, integrity constraints, etc. Use look-up tables to serve as domains where appropriate.

In order to complete this assignment you will need to turn-in:

• An overview of your design (1-2 pages of text) which describes each of the entities, their attributes, and their relations with other entities.

• SQL scripts for the creation of all objects (tables, indexes, constraints, etc.) and scripts which populate the schema with test data (see below).

• SQL "select" scripts which serve as reports for the following:
  • A list of albums with the producer's name
  • A list of albums with all the songs and their author(s)
  • A list of musicians with the instrument(s) they play
  • A list of musicians with the song(s) they have performed. If a musician has not authored any songs they should still appear on the list."

Be sure to populate the database with at least 10 musicians, 5 albums, 25 songs (and associated data) to make the reports reasonable. Make sure at least one of your musicians did not author any songs. You will need to turn-in both the SQL scripts for the reports and the output from each of the reports using your data.