ITSE 2309 LAB 3 Normalization and Creating Tables

ITSE 2309 LAB 3 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 faithful – pencil/pen/ruler and paper – ( which may require the use of a scanner for submission )

1. Using the table description and business rules listed below answer or perform the following:
a. Identify the Primary Key of the table as it is currently shown.
b. Identify all of the functional dependencies.
c. Draw the dependency diagram for the table(s)
( Hint: 1NF see pages 197-198 in Database System text book)

2. Normalize the relation to 3rd Normal Form (3NF).
a. List the normalized tables using the standard table notation (remember Chen and/or Crow’s foot)
– Tablename (Col1, Col2……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

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
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.