Database Systems EXERCISES ON RELATIONAL ALGEBRA (NEEDS TO USE ORACLE LANGAUGE S

Question

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 customer’s 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.

Explanation / Answer

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

1NF --- remove nonatomic column

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

2NF --- All non key attributes should be fully functionally dependent on the primary key

using functional dependencies , following tables need to be formed

MUSICIAN(ssn , mname, address, phone instrumentid, instrumentname, mkey)

INSTRUMENT(instrumentid , instrumentname, mkye)

ALBUM(albumid , albumtitle, copyrightdata, albumspeed, songtitle, songauthor)

SONGAUTHOR(ssn, songtitle , songauthor)

there are no transitive dependencies so the relations are also in 3NF.

2a) functional dependencies :

invnumb -> custnumb

invnumb -> invdate

custnumb -> custname

custnumb -> custaddress

partnumb -> partdesc

partnumb -> unitprice

partnumb -> numbship

underlined attributes are primray keys.

b) 3NF

1NF --- remove non atomic attribute (partnumb, partdesc, unitprice, numbship) and make the attributes atomic

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

2NF --- using functional dependencies , need to make following tables

INVOICE (invnumb, custnumb, invdate)

CUSTOMER (custnumb, custname, custaddress)

PART( partnumb, partdesc, unitprice, numbship)

There is no transitive dependency so relations are also in 3NF.

underlined attributes are primary keys.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.