|
The table below lists the course-instructor-textbook assignments for courses taught during a particular semester at a fictitious community college a few years ago. Nakasone, Seita and Singer each taught one section of ICS101. Nakasone also taught a section of ITS129 and a section of ITS149. Seita also taught one section of BUS250. Singer also taught a section of ITS227.
Columns: 3, Rows: 10
| COURSE | INSTRUCTOR | TEXTBOOK |
|---|
| 1 | BUS250 | Seita | Business Mathematics |
| 2 | ICS101 | Nakasone | Microsoft Office 2019 Step-by-Step |
| 3 | ICS101 | Nakasone | Technology in Action |
| 4 | ICS101 | Seita | Microsoft Office 2019 Step-by-Step |
| 5 | ICS101 | Seita | Technology in Action |
| 6 | ICS101 | Singer | Microsoft Office 2019 Step-by-Step |
| 7 | ICS101 | Singer | Technology in Action |
| 8 | ITS129 | Nakasone | A Guide to SQL |
| 9 | ITS149 | Nakasone | A Guide to SQL |
| 10 | ITS227 | Singer | Web Programming with JavaScript |
For textbooks, ICS 101 used two textbooks: "Technology in Action" and "Microsoft Office 2019 Step-by-Step." BUS 250 used "Business Mathematics", ITS 227 used "Web Programming with JavaScript." Both ITS 129 and ITS 149 used "A Guide to SQL."
Notice that even though the table is in BCNF, there is a lot of redundancy, which means update anomalies can still occur. The textbooks for ICS101 are in three places. The instructors for ICS101 are also in three places.
Assignment:
- Create an InnoDB database on your server named COURSES exactly.
- Import the above table into COURSES.
- Name the table "ORIGINAL"
- Set COURSE as varchar(10), INSTRUCTOR as varchar(50) and TEXTBOOK as varchar(100).
- Make sure you specify the primary key correctly
- Decompose the ORIGINAL table into two tables in 4NF to eliminate the chance of an update anomaly occurring. Name your tables COURSE_TEXT and COURSE_INSTRUCTOR.
- Make sure you specify the keys correctly.
- Make sure populate the tables from the original table.
- Create a VIEW named VIEW_ORIGINAL with a NATURAL JOIN statement to produce the original relation back from your two 4NF relations.
When you are through, confirm you created your database correctly using the autograder.
|