Problem 1.
For each table in the database, identify the primary key and any foreign key(s). If a table has a composite or multi-column key, then denote it using the plus sign with Columnname1 + Columnname2. For each foreign key, name the table and primary key it references. Use the notation Tablename.Columnnameto indicate the table and column referenced by a foreign key.If a table doesn't have a foreign key, write None in the assigned space.
|
Table
|
Primary Key
|
Foreign Key(s)
|
Table and columns of the primary key referenced by the foreign key (if any).
|
|
INVOICE
|
|
|
|
|
INVOICE_LINE
|
|
|
|
|
CUSTOMER
|
|
|
|
|
PRODUCT
|
|
|
|
|
REPRESENTATIVE
|
|
|
|
Problem 2.
Based upon the data, do the tables exhibit entity integrity? Answer Yes or No, then explain your answer by citing the evidence that entity integrity is satisfied.
|
Table
|
Entity integrity
|
Explanation
|
|
INVOICE
|
|
|
|
INVOICE_LINE
|
|
|
|
CUSTOMER
|
|
|
|
PRODUCT
|
|
|
|
REPRESENTATIVE
|
|
|
Problem 3.
Do the tables exhibit REFERENTIAL integrity? Answer Yes or No, then explain your answer by citing the evidence that the rules for foreign keys are satisfied. Write Not Applicable if the table doesn't have a foreign key. Remember that a foreign key can only reference a primary key or column(s) with a unique constraint.
|
Table
|
Referential integrity
|
Explanation
|
|
INVOICE
|
|
|
|
INVOICE_LINE
|
|
|
|
CUSTOMER
|
|
|
|
PRODUCT
|
|
|
|
REPRESENTATIVE
|
|
|
Problem 4. Draw the entity relationship diagram (ERD) to show the relationships among the five tables. Include the connectivities and position the 1's and M's or N's on the correct side. Omit the ovals with attributes (a relational schema is better if you want to show columnnames).
You can insert other lines or geometrical shapes from INSERT (on the menubar) | Shapes
Your supply of draggable 1s, Ms, lines, and rectangles. The rectangle with the diamond for the bridge table is grouped. You can shorten/lengthen the lines by selecting the line, then dragging the end. Extra objects supplied by the professor are $100 each.
Problem 5. Draw the relational schema to show the relationships between the tables. In a relational schema, each table has a name (in the gray area below) and columns are listed below. Primary keys are bolded or underlined. Draw your lines between tables so they show which columns connect the tables, and position your 1's and ∞'s correctly above the lines. Relational schemas use ∞ for Many.
Bridge Tables
Copy and edit this file, CS60 Project 4 2016Fall Bridge Tables.docx, which is stored at \\Acshare\busdata\ROGLER_HAROLD\CS60 Database Concepts\CS60 Projects. Rename it asCS60_4_LastName_FirstName.docx and edit the footer so your filename appears there.
When two tables have a many-to-many (M:N) relationship, the database must be redesigned to include a bridge table so you end up with two 1:M relationships. Similarly, if one table has a many-to-many unary or recursive relationship with itself, the database must be redesigned to include a bridge table.
The following describe tables with M:N relationships. As described, databases1-5 have binary relationships (relationships between two tables), while database 6 has a unary relationship (a relationship between a table and itself).
1. A part can be supplied by many suppliers, and each supplier can provide many parts.
2. An employee of an organization can have many dependents (sons, daughters, etc.), and each dependent can be the dependent of many employees if both parents work for the organization.
3. A professor normally teaches many sections of a course, and each section could be co-taught by several professors. Note that some courses may not be taught in some semesters, other courses have one section in a semester, and other courses (such as CS3 and CS50) have many sections in some semesters. A professor is assigned to teach a section (or several sections) of a course.
4. A certificate requires the completion of many courses, and each course can be part of many certificates. For example, CS3 appears in all four large certificates offered by the CSIS Department. CS60 also appears as part of four Certificates of Achievement (Computer Programming, Web Programmer, Database Applications Developer, and Computer Science).
5. A painter (of murals that hang on the wall) can paint many murals, but each mural may have been painted by many painters (who each specialize in eyes, leaves, surf, hands, landscapes, etc.).
6. A person has many friends (like on Facebook) and each friend has many friends. How are you going to keep track of all these friendships? This table has a unary M:N relationship with itself. How could you handle the problem of Bill and Mary are friends, but also not storing that Mary and Bill are friends?
For any three of the above, redesign the databases so your new design has no M:N relationships. Using the template below, draw the entity relationship diagram (ERD) of your new design. Name your tables descriptively. Include the connectivities and position the 1's and M's or N's on the correct side. Use the circle bumped up against a rectangle to indicate optional participation, and use a straight line coming into the rectangle to indicate mandatory participation. Include the cardinalities by editing the (1, 1)s and positioning them. The tables are in textboxes. Omit the ovals with attributes in your ERD (skip the columnnames).
Also for each of your three designs, draw the relational schema to show the relationships between your tables. Use descriptive names for the columns, and follow the rules of no synonyms and no homonyms. Draw your lines between tables so they show which columns connect the tables, and position your 1's and ∞'s correctly. Bold your primary keys. The tables are within textboxes that you can select and move around.
Please edit your footer and include your filename CS60_3_Last_First.docx (with your names) inside your file. Copy/paste your file to the server Zeus:
\\Zeus\data\ROGLER_HAROLD\CS60 Database Concepts and Applications
as described in the syllabus. Check the file size to make sure it's not 0KB (empty) and the file date and time indicate the final version of your file.
Attachment:- tables.rar