- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 1 (49.3 KiB) Viewed 65 times
- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 2 (33.42 KiB) Viewed 65 times
- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 3 (50.09 KiB) Viewed 65 times
- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 4 (50.09 KiB) Viewed 65 times
- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 5 (56.92 KiB) Viewed 65 times
- For This Lab There Are Various Programs That Can Be Used There Is Ms Excel Where By Drawing Boxes And Using The Arr 6 (19.21 KiB) Viewed 65 times
STUDENT TABLE Student Student Student Student Course Course Stu Crse Instructo Instructo Instructo ID Name Address Major ID Title ID Name Office Grade CIS CIS 350 Database 301 Codds B104 A 26830045 Williams 208 8 Brooks 26830045 Williams 208 8 Brooks CIS CIS 465 Systems Anal 451 Parsons B317 B 104 Philips Acct CIS 350 Database 301 Codds B104 с 54329107 Bakker 3 54329107 Bakker 3 104 Philips Acct Ace 201 Fund of Acety 255 Mills H310 B 54329107 Bakker 3 104 Philips Act Mit 300 Into to Mag 518 Bennett B212 А 69538112 White 7 Math Mth 202 622 Hilbert M301 B 208 Brooks 208 Brooks College algebra Fund of Acte 69538112 White 7 Math Ace 201 255 Mills H310 A Business Rules: (see page 249 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 . .
Normalization and Creating Tables (1000 Points) (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) c. 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. Draw the dependency diagram for the table(s) 2. Normalize the relation to 3rd Normal Form (3NF). a. List the normalized tables using the standard table notation fremember Cher and for Chow Tablename (Coll, 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 d. Review pages 208 – 212 in text 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 page) . . . . . . 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 Stu_Crse Instructo Instructo Instructo r r T ID Name Address Major ID Title ID Name Office Grade CIS 350 Database 301 Codds B104 A 26830045 Williams 208 CIS 8 Brooks 26830045 Williams 208 CIS 8 Brooks 54329107 Bakker 104 Philips Acct 3 451 CIS 465 Systems Anal Parsons B317 B CIS 350 Database 301 Codds B104 с 54329107 Bakker 3 104 Philips Acet Асс 201 Fund of Acctg 253 Mills H310 B 54329107 Bakker 3 104 Philips Acct Mkt 300 Into to Mktg 518 Bennett B212 A 69538112 White 7 208 Brooks Math Mth 202 622 Hilbert M301 B College algebra Fund of Acety Math 69538112 White 7 208 Brooks Асс 201 255 Mills H310 A
Business Rules: (see page 249 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. a