Please help with this SQL Oracle lab. At least with part of it.

----------------------------------------------------------------------------------------

Normalization and Creating Tables

(For this Lab -there are various programs that can be used -- there is MS Excel, where by drawing boxes and using the arrows /lines option the graphics can be accomplished - there is also MS Visio, if available and the always faithful pencil/pen/ruler and paper - ( which may require the use of a scanner for submission )

Using the table description and business rules listed below 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 table(s)

(Hint: 1NF see pages 197-198 in Database System text book)

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

List the normalized tables using the standard table notation

(remember Chen and/or Crows foot)

Tablename (Col1, Col2Coln)

Primary Key:

Foreign Key:

Draw the dependency diagrams for each of the tables.

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

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

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 Crows 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
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:

( see page 239 in Database Systems Text Figure 7.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.