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)
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 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
( 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.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more