ITSE 2309 Database Exercises 2

Normalization and Creating Tables

1.Using the table description and business rules on the following page, 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. Draw the dependency diagram for the initial table showing all dependencies.

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

a.List of Normalized tables.
b.Provide a Relational Schema

List the normalized tables using the standard table notation:

Tablename (Col1, Col2……Coln)
Primary Key:
Foreign Key:

c.Provide an E-R Diagram of the tables to be created
d.Provide an Attribute Chart

3. Using the CREATE TABLE command, create each of the normalized tables. Run a DESCRIBEcommand 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 Crow's 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 ID Student Name Student Address Student Major Course ID Course Title Instructor ID Instructor Name Instructor Office Stu_Crse Grade
268300458 Williams 208 Brooks CIS CIS 350 Database 301 Codd B104 A
268300458 Williams 208 Brooks CIS CIS465 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 Fun of Acctg. 255 Miller H310 B
695381127 White 208 Brooks Math Mth 202 College Algebra 622 Hilbert M301 B
6955381127 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.
Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.