Adriana's Audio

Adriana’s Audio needs a database. She needs it for three major purposes:

1.    To manage her Sales,
2.    To manage her inventory, and
3.    To answer customer questions

In terms of managing her sales, she needs to relate the items she sells with her customers. She keeps the following information on each of her customers:

A.  Cust_ID: A Unique Identifier which Adriana will generate
B.  Cust_Name:  The Customer's Name
C.  Cust_Address: A composite Attribute consisting of Street, City, State, &  Zipcode

In terms of managing inventory, Adriana needs to know the following for each Item (the CD/Tape/DVD/LP) in stock:

A.    A Unique Item Identifier which Adriana will generate
B.    The Name of the Item in Stock
C.    How many copies of each item she has in stock
D.    How much each item cost her
E.    How much each item sells for
F.    What format the item is in (LP, Audio Tape, Digital Tape, CD, etc.)
G.    What type of item it is (Classical, Rock, Rap, Anthology, Mixed, etc.)

Based on this information, she would like to know such things as:

SQL#1.    "What Items in Inventory are out of Stock?"
SQL#2.    "What Items yield the largest Profit?"

Naturally, she needs to relate customers and items in inventory, since she needs to keep track of sales (ASSUME AN 8% Sales Tax). She would (eventually) like the list to look something like (SQL#3 and SQL#4):

John Smith
123 Main Street
Arlington, TX 76011
Date of Sale: 10/01/01

Total Cost  
54.13

She also needs to keep track of:

SQL#5.    What were the Total Sales for the month?
SQL#6.    What were the Total Taxes collected for the month?
SQL#7.    What were the Total Profits for the month?

If we look at this, I would guess we need one table for her CUSTOMER (which probably should contain information about the customer, each customer having a unique ID, of course) which should be linked to ITEMs in inventory, since inventory must be updated when a sale is made. Let's call the relationship between the two entities 'purchases' (i.e., CUSTOMER purchases ITEMs from inventory). Given one CUSTOMER, they can can use many ITEMs from inventory. Given one ITEM in inventory, it can be purchased by many CUSTOMERs. Well, I guess that a SALE is really the relationship between CUSTOMER and ITEM.

If we look at the table, notice that there is a field called Artist. That means, obviously, that we are going to have also have a table called ARTIST. We could probably include such information as artist ID, and Artist Name here. Since (I suppose) many artists could have the same name, we will have to generate a unique ID for them. Let's see. I know that an artist can have many Items in inventory. Given an item in inventory, however, can there be more than one artist who recorded it? Well, there are those anthologies. I wonder how we can handle that?  

Making inquiries about ITEMs in inventory is a little trickier. Some of the questions Ardriana needs to answer includes:

If we examine this situation, it isn’t too difficult (well, maybe a little).We have an ALBUM (let's this is as the general term for either a LP, Audio Tape, CD, or DVD). An ALBUM should have a unique ID. It would probably make sense to use same ID as we used for ITEMs in inventory. The main difference between an Album and an Item in Inventory, is that we need to keep track of of all of the songs on the Album, whereas an Item in inventory is a single record.

An ALBUM generally (although not always) consists of a number of SONGS (actually, that's a bad label. A song always is sung (i.e., it has words). A tune is a piece of music without words, so maybe we should call it SONGS/TUNES? Ah well, let's just call it SONGS). We need to keep track of things like song name, but again, different songs can have the same song name. I guess we will once again have to generate unique song IDs. 

An ALBUM must be related to an ARTIST (i.e,, who performed the ALBUM). We could probably include such information as artist ID, and Artist Name here. Others, if we want. I suppose that an ARTIST (whether a  group or individual performer) can release many ALBUMs, but an ALBUM can be produced by only one ARTIST. No - now that I think about it, that's wrong.  suppose an ALBUM May have Many ARTISTs (For example, an anthology - one of those best of the 50's type things). Certainly, and Artist can have many ALBUMS.  

Wait a minute!! Can a SONG exist without an ARTIST and an ITEM in inventory? Can an ARTIST exist without an ALBUM and a SONG? Can an ALBUM exist without an ARTIST and SONG?

Just one more hitch. Every SONG must have a COMPOSER (i.e., Composer ID and Composer Name). Obviously, not every song was written by the person artist who performed it (probably most ARTISTs have never composed a SONG). Anyone have an album performed by Beethoven? I'd love to hear it, especially since Beethoven died in 1827. Still we have to answer the question “What versions of Beethoven’s 5^th Symphony do you have?” 

Given one SONG there may be Many COMPOSERs (e.g., Lennon and McCartney). Given one COMPOSER, there may certainly be many SONGs (Beethoven wrote not only nine complete symphonies, but over 300 other Concertos, Trios, Quartets, Quintets, Piano Pieces, Songs, Masses, and even an Opera and a ballet).

Should we include COMPOSER as part of our associative entity ALBUM? No - that would violate all sorts of normalization constraints.

Part 1.  You are to prepare an ERD which lays out all  of the entities, attributes, and relationships necessary for the audio database (More information can be found on the link above).

Part 2. You are to prepare create the tables necessary to correspond to the ERD constructed in Part 1. (More information can be found on the link above). 

Part 3.  You are to enter in some dummy data into the tables (More information can be found on the link above).

Part 4.  Write the SQL commands to answer all (15) of the questions Adriana needs answered or that a customer might ask (More information can be found on the link above).

The due date is tentatively set for TWO WEEKS BEFORE THE END OF CLASSES (NOT the final exam). If you wait too long to get started, I think you will have a problems finishing it. 

NOTE: This is to be an INDIVIDUAL project. It is very easy to get a copy of the project from a friend. HOWEVER, it is equally as easy for me to figure out if it an original work. IT IS HIGHLY UNLIKELY that you would come up with EXACTLY the same ERD, Tables, Data and Output from SQL commands as someone else in the class (especially with respect to the data entered. and hence, the output). If you do, well, I will have to assume that the project was NOT done by one person alone, and will have to grade accordingly (i.e., all the parties involved will get a ZERO (0) for the project.

We will periodically be discussing this case in class, and I will be happy to meet with you individually to discuss the project.

Bon Chance!