And knows that the album name has the words blue and train the title
TERM PROJECT (sample provided at the end for reference)
I. Describe the application that you would like to design. This should include the purpose of the
now, just make sure that you know what data points you want to capture. The names for these data should
not be database-style names, just plain English. While we will only be implementing the database for this
updates or other database manipulation statements.
III. Draw one or more Entity-Relationship diagrams for your database. Optimally you will have 7-10
into Word.
IV. Create the relational schema for your database based on your E-R diagram. Then revise this schema
b. the relational schema (not CREATE TABLE statements)
c. a description of the normalization of these tables
V. Write the SQL queries that support at least three of your use cases. Perform a screen capture showing
the results when you run these queries against your database. Please edit the screen capture to delete all
understandable by someone who has not seen your project before.
Page 2 of 10
derived from the project introduction, data description and use cases.
On the following pages is an example of a completed project.
Database is limited to online sales (as opposed to in house purchases)
Bakers work around the clock with the exception of a 3 hour break in between shifts
CUSTOMERS: Information about each patron includes first name, last name, company name, address, city, state, zip/postal code, country, phone/cell number, e-mail address, birthday, and special notes entry area for special instructions.
ORDERS: Establish method of order delivery: pick up or delivery. If delivered, to existing address on file or to new address location. Date for when order should be received.
Need to Add
Use Cases and the Rest:
o Database is limited to instrumental music (as opposed to vocals)
o Musicians can play more than one instrument, with two being the maximum tracked by this
Data
Use Cases
Conclusion
Data
RECORD COMPANIES: Information about record company includes the name, a start and ending date of the company (if applicable) and location; in addition, each company may have more than one record label which will also have a start and ending date and location.
Page 4 of 10
Description: User wants to know what album(s) contain a specific song based on song title, performer, and duration—e.g. Jazz Collector wants to find album(s) that contain the historically significant, extended 6-minute version of “Embraceable You” by Charlie Parker.
3. Find album/songs by album name
5. Find recordings by composer.
Description: User wants to find all recordings of songs by a specific composer—e.g. Jazz fan wants to locate all songs which were composed by Simon Jacobs, including the date of the recording and musicians.
o Record Company names are unique.
o Record labels are associated with only one company.
relationships may adequately model the data. This is particularly true since the song is a particular
performance of a song and therefore the musicians will always be the same for that song regardless of
relationships may adequately model the data. This is particularly true since the song is a particular
performance of a song and therefore the musicians will always be the same for that song regardless of
Musician (Name, DOB, BirthPlace, PrimaryInstrument, SecondaryInstrument)
Album (Title, ReleaseDate, Availability, DOB, mName, lName)
rcName FK RecordCo
HasSongs(ID, DOB, mName, sTitle, PerformDate, Composer, aTitle, ReleaseDate)
SQL DDL and DML Statements
CREATE TABLE Musician (
SecondaryInstrument varchar2(15),
PRIMARY KEY (Name, DOB))
AlternateTitle varchar2(30),
DurationMin number CHECK (DurationMin <60),
StartDate date,
TermDate date,
StartDate date,
EndDate date,
Availability char CHECK (Availability in ('Y','N')),
mName varchar2(30),
CREATE TABLE HasSongs (
ID number(6) PRIMARY KEY,
Composer varchar2(30),
aTitle varchar2(20),
UNIQUE (DOB,mName, sTitle, PerformDate, Composer, aTitle, ReleaseDate))
INSERT INTO Musician VALUES ('Harry Belafonte', '01-Mar-1927', 'New York, NY USA', 'Vocal',
INSERT INTO Album VALUES ( 'Midnight Special', to_date('1962', 'YYYY'), 'Y', 'Harry Belafonte',
'01-Mar-1927', 'RCA Records')
SQL SELECT Statements
Title: Find albums that contain a song title.
AND s.Title = hs.sTitle
AND s.PerformDate = hs.PerformDate
SELECT a.Title, a.ReleaseDate, a.Availability
FROM Album a, Song s, HasSongs hs, Musician m
AND s.Composer = hs.Composer
AND m.Name = hs.mName
Title: Count albums for musician by record company
AND a.lName = l.Name
AND l.rcName = rc.Name
The goal of this project was to develop a discography database for jazz music. This was created using 6 tables. In future upgrades, I would consider having more than one musician per album. One lesson learned when writing queries is that when tables have multiple compound foreign keys, the queries become difficult to write. This might be a time to consider using auto-generated keys. This is a database that can be easily implemented in a desktop database application such as Access, since only a single user needs to access it and it does not have high security requirements.