Using the table description and business rules on the following page, answer or

perform the following:

Identify the Primary Key of the table as it is currently shown.

Identify all of the functional dependencies. Draw the dependency diagram for the initial table showing all dependencies.

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

List of Normalized tables.

Provide a Relational SchemaList the normalized tables using the standard table notation:

Tablename (Col1, Col2Coln)

Primary Key:

Foreign Key:

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

Provide an Attribute Chart

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 output to hand in should include:

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 (Chin or Crows Foot)

Question 2, Part d.: Relational Schema/Relational Diagram (Page 85 in text)

Question 2, Part e.: Attribute Chart/Data Dictionary (Page 221 in text)

Question 3: Print out of the CREATE TABLE commands & show resulting SQL

message.

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
268300458	Williams	208 Brooks	CIS	CIS 350	Database	301	Codd	B104	A
268300458	Williams	208 Brooks	CIS	CIS 465	Systems Anal	451	Parsons	B317	B
543291073	Baker	104 Philips	Acct	CIS 350	Database	301	Codd	B104	C
543291073	Baker	104 Philips	Acct	Acc 201	Fund of Acctg.	255	Miller	H310	B
543291073	Baker	104 Philips	Acct	Mkt 300	Into to Mktg	518	Bennett	B212	A
695381127	White	208 Brooks	Math	Mth 202	College algebra	622	Hilbert	M301	B
695381127	White	208 Brooks	Math	Acc 201	Fund of Acctg	255	Miller	H310	A

Business Rules:

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.