1.    The following is an example of a grade report for two students at the University of Southern New Jersey:

 

USNJ Grade Report Spring Semester 2000
Name: I. M. Smarte Student: 053-6929-24				Major: MIS Status: Senior
Course Number		Course Title	Professor	Professor’s Department	Grade
MIS	403	Systems Analysis	Diggs, T.	MIS	A
MIS	411	Conceptual Foundations	Barre, G.	MIS	A
MIS	420	Human Factors in IS	Barre, G.	MIS	B
CIS	412	Database Design	Menzel, I.	CIS	A
DESC	353	Management Models	Murney, J.	MIS	A

 

USNJ Grade Report Spring Semester 2000
Name: E.Z. Grayed Student: 472-6124-59				Major: MIS Status: Senior
Course Number		Course Title	Professor	Professor’s Department	Grade
MIS	403	Systems Analysis	Diggs, T.	MIS	B
MIS	411	Conceptual Foundations	Barre, G.	MIS	A

 

Note: there is no question asked for problem 2.  The example is used in problems 3 and 4.

 

2.         Draw a data model diagram with associations for the user view in Problem 2.

 

The data model diagram is illustrated on the next page.

 

Data model diagram, attribute relationships.

 

3.         Convert the user view in Problem 3 to a 3NF relation. Show each step along the way.

 

Un-normalized

 

STUDENT NUMBER

STUDENT NAME

MAJOR

STATUS

COURSE  NUMBER (1-N)

COURSE TITLE (1-N)

TERM (1-N)

PROFESSOR (1-N)

PROFESSOR DEPARTMENT (1-N)

GRADE (1-N)

 

 

1^st normal form

 

STUDENT**

STUDENT NUMBER

STUDENT NAME

MAJOR

STATUS

 

STUDENT-COURSE

STUDENT NUMBER

COURSE  NUMBER

COURSE TITLE

TERM

PROFESSOR

PROFESSOR DEPARTMENT

GRADE

 

 

2^nd normal form  (STUDENT same as above)

 

STUDENT-COURSE

STUDENT NUMBER

COURSE  NUMBER

TERM

PROFESSOR

PROFESSOR DEPARTMENT

GRADE

 

COURSE**

COURSE  NUMBER

COURSE TITLE

               

3^rd normal form  (STUDENT and COURSE same as above)

 

STUDENT-COURSE**

STUDENT NUMBER

COURSE  NUMBER

TERM

PROFESSOR - - - - - - - - -

GRADE

 

PROFESSOR**

PROFESSOR

PROFESSOR DEPARTMENT