Database Systems

EXERCISES ON RELATIONAL ALGEBRA

(NEEDS TO USE ORACLE LANGAUGE SOFTWARE) - ORACLE PLEASE

DO NOT NOT WRITE IN SQL

ORACLE SOFTWARE ONLY!!

Exercises on Normalization

1. Convert the following relation to an equivalent collection of relations that are in 3NF. (10 points)

MUSICIAN (ssn, mname, address, phone, instrumentid, instrumentname, mkey, (albumid, albumtitle, copyrightdata, albumspeed, songtitle, songauthor))

The following functional dependencies exist in MUSICIAN

ssn --> mname, address, phone instrumentid, instrumentname, mkey

instrumentid --> instrumentname, mkye

albumid --> albumtitle, copyrightdata, albumspeed, songtitle, songauthor

ssn, songtitle --> songauthor

2. a) List the functional dependencies in the following relation, subject to the specified conditions that follows. b) Convert this relation to an equivalent collection of relations that are in 3NF. (20 points)

INVOICE (invnumb, custnumb, custname, custaddress, invdate, (partnumb, partdesc, unitprice, numbship))

This relation concerns invoice information. For a given invoice (identified by the invoice number), there will be a single customer. The customers number, name, and address appear on the invoice as well as the invoice date. Also, there may be several different parts appearing on the invoice. For each part that appears, the part number, description, price, and number shipped will be displayed. The price is from the current master price list.