Sql Help: ASSIGNMENT DESCRIPTION: Using your knowledge of a college environment,

Question

Sql Help:

ASSIGNMENT DESCRIPTION: Using your knowledge of a college environment, convert the following table to an equivalent collection of tables in 3NF (third normal form) Show the tables in 1NF, 2NF and 3NF during the conversion. List the functional dependencies Please complete the question in electronic format such as Word or PDF so that they can be uploade to Canvas TABLE STUDENT (STUNUMB._STUNAME, NUMCRED, ADVNUMB, ADVNAME, CRSENUMB CRSEDESC, GRADE) STUNUMB STUNAME NUMCREDADVNUMB ADVNAME CRSENUMB CRSEDESC GRADE 800342124 Joe Beck 3 3160 DatabaseB Networks A 800076565Roy Cone ITCS3 ITCS4102 Languages B 800217341 Ann Alda 4 800025414 lan Jones ITCS1213 Java ITCS1213L Java Lab A MATH1241 Calculus C LBST1104 Films 3 3 800666154 Sue Dane 3 3 800001136 Eva Thick ITCS2215 Algorithms B MATH1242 Calculusll D ITCS3160Database 800134666 Bob Wall 3 800025414 lan Jones MATH1242 CalculusD ITCS3166 Networks ITCS4102 Languages C ITCS3160 DatabaseB 800189871 Ava Best 4 800001136 |Eva Thick ITCS1213 Java ITCS1213L Java Lab A PSYC1101 Psychology C PSYC1101L Psych Lab C LBST1104 Films 3 800314321 Will Sneed 3 800001136 |Eva Thick MATH1242 Calculus A Networks A TCS4102Languages A ITCS3160Database 3

Explanation / Answer

Normalization of Database.

Student(STUNUMB, STUNAME,NUMCRED, ADVNUMB,ADVNAME,CRSENUMB, CRSEDESC, GRADE)

Condition needs to satisfy 3NF:

1. Contains only atomic values(1 NF)

2. All the non key attributes are functionally dependant on primary key.(2NF)

3. No transitive functional dependancy (3NF)

Step 1 : First Normal form

The columns NUMCRED, CRSENUMB, CRSEDESC,GRADE are not atomic so the table needs to divided further

STUDENT(STUNUMB, STUNAME,ADVNUMB, ADVNAME)

STUDENT_CRED(STUNUMB,CRSENUMB, CRSEDESC, NUMCRED, GRADE)

Now the above relation was in First Normal form.

Step2 : Second Normal Form

                             In the above relation STUDENT(STUNUMB, STUNAME,ADVNUMB, ADVNAME), it has a composite primary key, since ADVNAME not only depends on STUNUMB, it directly depends on ADVNUMB. SO relation is further sub divided to satisfy 2NF.

STUDENT(STUNUMB, STUNAME)

ADV(ADVNUMB, ADVNAME)

STUDENT_CRED(STUNUMB,CRSENUMB, CRSEDESC, NUMCRED, GRADE)

Now the above relations is in 2NF.

Step3: Third Normal form

There is transitive dependency in the relation STUDENT_CRED(STUNUMB,CRSENUMB, CRSEDESC, NUMCRED, GRADE), where STUNUMB determines CRSENUMB, and CRSENUMB determines NUMCRED

Hene the relation is subdivided further.

STUDENT(STUNUMB, STUNAME)

ADV(ADVNUMB, ADVNAME)

STUDENT_CRED(STUNUMB,CRSENUMB, GRADE)

CRED(CRSENUMB,CRSEDESC, NUMCRED)

Now there is no transitive relation in the above tables,hence the above relations are in 3NF.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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