$2.$ Normalize the relation to $3$rd Normal Form $(3$NF$).$

Include progression from $1$NF to $3$NF

a$.$ List the normalized tables using the standard table notation

$($Remember Chen and$/$or Crow$$s foot$)$

$$ Tablename $($Col$1,$ Col$2......$Coln$)$

Primary Key:

Foreign Key:

b$.$ Draw the dependency diagrams for each of the tables.

$($ Hint: you should have no less than four tables$)$

c$.$ Provide an E$-$R Diagram of the tables to be created

d$.$ Review pages $208212$ in text

$3.$ Using the CREATE TABLE command, create each of the normalized tables. Run a

DESCRIBE command for each table.

$$ Include the PRIMARY KEY constraint for each table.

$$ Include the FOREIGN KEY constraint for each table to which it applies.

$$ Include the NOT NULL constraint for Student Name and Instructor Name.

$$ Include the CHECK constraint for the Grade to ensure it is one of the $5$ acceptable values $($A$,$ B$,$ C$,$ D$,$ or F$).$

$4.$ Code INSERT commands to insert the data from the attached page into the tables you created in $2.$

$$ Run $$SELECT $*$ FROM tablename; commands to check the contents of your tables.

$5.$ Your submittal$/$output to hand in should include: $($with no less than ten pages$)$

$$ Question $1,$ part a$.$: Primary Key columns

$$ Question $1,$ part b$.$: Dependency Diagram

$$ Question $2,$ Part a$.$: List of normalized table descriptions

$$ Question $2,$ Part b$.$: Dependency diagrams for the normalized tables

$$ Question $2,$ Part c$.$: E$-$R Diagram of the tables to be created$($ Chen and Crow$$s feet$)$

$$ Question $2$ Part d$.$: UML Diagrams of the tables to be created

$($See pages $143,144)$

$$ Question $3$: Print out of the CREATE TABLE commands & results.

$$ Question $3$: Print out of the DESCRIBE table commands & resulting SQL message.

$$ Question $4$: Print out of the INSERT commands & Resulting SQL message.

$$ Question $4$: Print out of the SELECT $*$ FROM tablename listing and SQL results.

$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

STUDENT TABLE

Student Student Student Student Course Course Instructor Instructor Instructor Stu$\_$Crse

ID Name Address Major ID Title ID Name Office Grade

$202312823$ Williams $209$ Books CIS CIS $350$ Database $301$ Codds B$104$ A

$202312823$ Williams $209$ Books CIS CIS $465$ Systems Anal $451$ Parsons B$317$ B

$543291073$ Bakker $104$ Philips Acct CIS $350$ Database $301$ Codds B$104$ C

$543291073$ Bakker $104$ Philips Acct Acc $201$ Fund of Acctg. $255$ Mills H$310$ B

$543291073$ Bakker $104$ Philips Acct Mkt $300$ Into to Mktg $518$ Bennett B$212$ A

$695381127$ White $208$ Brooks Math Mth $202$ College algebra $622$ Hilbert M$301$ B

$695381127$ White $208$ Brooks Math Acc $201$ Fund of Acctg $255$ Mills H$310$ A

Business Rules:

$($ see page $249$ in Database Systems Text Figure $7\mathrm{.}1)$

$$ Only one class is taught for each course ID$.$

$$ Students may take up to $4$ courses.

$$ Each course may have a maximum of $25$ students.

$$ Each course is taught by only one Instructor.

$$ Each student may have only one major.

$$