Ass1_Opt1 (StudMgnt)

ASSIGNMENT

Question 1 Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course (Primary Key is mark post-fix with #): !"$%!(%#& %ame& 'aor& date) C*"+$(Course#& Cname& ept) $%+*,,(%& Course#& uarter& .rade) **K/0*P!1*%(Course#& uarter& ook/1%) !$2!(ook/1%#& ook/!itle& Publisher& 0uthor) raw a relational schema diagram specifying the foreign keys for this schema3



olution:

ENROLL *

STUDENT *

COURSE *

BOOK_ ADOPTION * TEXT *

Question 2 .i4en the 1n4oice table with following initial structure (Primary Key is mark post-fix with #)

Please: 53 73

%ormali6e that table structure to the first& second and third normali6ation forms raw relational schema diagram for the tables defined in the third normali6ation form abo4e



olution

1.

1NF

Invoice#

Cust

Name

89 89 89 << << 89

1001 1001 1001 100! 100! 100"

ones ones ones mith mith ones

Addr

Quant st

575 5 575 5st 575 5st 777 7nd 777 7nd 575 5st

!NF

!able 1n4oice Invoice# 1001 100! 100"

Cust

Name

Addr  

89 << 89

ones mith ones

575 5st 777 7nd 575 5st

!able 0mount Part#

Amt

Screw Nut

73;; 737< ;3=< <73;; 5753;; 88388

Wasr  Motor  Saw race

!able uantity Invoice# 1001 1001 1001 100! 100! 100"

"NF

!able 1n4oice

Quant

7;; 9;; 5;; 5 5; <

 

Part# Screw

 

Nut Wasr 

 

Motor  Saw

 

race

7;; 9;; 5;; 5 5; <

Part#

Amt

 

Screw Nut

   

Wasr  Motor 

 

Saw race

73;; 737< ;3=< <73;; 5753;; 88388

Invoice#

Cust

1001 100!

89 << 89

100"

!able Customer  Cust# $" %%

Name

Addr  

ones mith

575 5st 777 7nd

!able uantity Invoice# 1001 1001 1001 100! 100! 100"

Quant

7;; 9;; 5;; 5 5; <

 

Part# Screw

 

Nut Wasr  Motor 

 

Saw race

 

!able 0mount Part#

Amt

Screw Nut

73;; 737< ;3=< <73;; 5753;; 88388

Wasr  Motor  Saw race

!.

&e'ationa' )ata*ase Scema

Inoi!e * Customer *

Qu"ntit# *

Amount *