Expert Answers

Expert Answers

Question 1: Data Normalization. [10 points] 

Tables TAB and TJK below have their primary key attributes underlined: 

TAB    (A, B, C, D, E, F, G, H)

TJK     (J, K, M, N, O, P, Q, R)

Decompose these tables into tables in the third normal form using the functional dependencies specified below:

A         ®        C, D, E

B         ®        F, G

C         ®        E

J          ®        M, N

K         ®        O, P, Q

P          ®        Q 

Give each table a unique name. Underline the primary key attribute(s) in each table.
Specify the foreign key attribute(s) as: FK [Attribute_Name] references [Table_Name] 

Question 2: SQL [2 ´ 10 = 20 Points] 

Formulate SQL queries for (a) and (b) with reference to the database specified for homework 2. Ensure that your SQL queries will produce a correct response for every instance of the database.

List the employeeid and salary of those supervisors whose supervisees, collectively, have received at least one order. There should be one record per eligible supervisor.

List the employeeid and salary of salespersons who have received orders from at least ten different customers.
Powered by