Home‎ > ‎CIS 54‎ > ‎

Lab 12 - Design Review

In this lab you'll review your classmate's E-R diagrams.  

Introduction

In this assignment you'll grade your classmate's work on lab 11. Your grade will not be the course grade (I'll do that). You will be graded on the thoroughness of your analysis. Grade the diagrams against the reference diagram and description that is shown in this assignment. After you review the design you will correct your own submission for lab 11 and resubmit it. 

Diagram Assignments

The table below has your diagram assignments. Download the link next to your name and work on it for this assignment. 

Declan https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/2126_148923_QueenAnne.mwb?attredirects=0&d=1
Charlie https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/16464_145479_QueenAnne.mwb?attredirects=0&d=1
Shahram https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/15819_144967_QueenAnne.mwb?attredirects=0&d=1
Thomas https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/14946_148925_QueenAnne.mwb?attredirects=0&d=1
Sean https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/14159_145083_QueenAnne.mwb?attredirects=0&d=1
David https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/13820_148861_QueenAnne.mwb?attredirects=0&d=1
Donovan https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/10548_145232_QueenAnne.mwb?attredirects=0&d=1
Karl-Heinz https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/10476_145351_QueenAnne.mwb?attredirects=0&d=1
Jennifer https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/9432_145439_QueenAnne.mwb?attredirects=0&d=1
Eric https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/7610_144788_QueenAnne.mwb?attredirects=0&d=1
Aaron https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/5230_144777_QueenAnne.mwb?attredirects=0&d=1
Veronica https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/2126_148923_QueenAnne.mwb?attredirects=0&d=1
Raymond https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/16464_145479_QueenAnne.mwb?attredirects=0&d=1
Efrain https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/15819_144967_QueenAnne.mwb?attredirects=0&d=1
Mayra https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/14946_148925_QueenAnne.mwb?attredirects=0&d=1
Sergiy https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/14159_145083_QueenAnne.mwb?attredirects=0&d=1
Samantha https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/13820_148861_QueenAnne.mwb?attredirects=0&d=1
Dennis https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/10548_145232_QueenAnne.mwb?attredirects=0&d=1
Ryan https://sites.google.com/a/lifealgorithmic.com/cabrillo-home/home/cis-154/lab-12---design-review/10476_145351_QueenAnne.mwb?attredirects=0&d=1

You will re-upload your drawing with your assignment. 

Reference Diagram

Below is shown my solution to Lab 11:


Description

The following tables are derived from the Queen Anne schema. 
  • EMPLOYEE
  • SALE
  • CUSTOMER
  • SALE_ITEM
  • ITEM
  • VENDOR
The following tables were added for parts B and C (then combined with the first drawing in D):
  • Human
  • Order
  • ITEM_has_VENDOR
In each of the original tables the attribute types match the public_queen_anne database exactly. For part B the following tables were added:
  • Order
  • ITEM_has_VENDOR
The ITEM_has_VENDOR table is an intersection table that implements the M:N relationship between ITEM and VENDOR. Reading the diagram the relationship says, "An item has one or more vendors and a vendor has one or more item." The relationship also shows that there is a multivalued dependency in our schema:

     Item →→ Vendor
     Vendor →→ Item

The ITEM table adds the QtyOnHand attribute (quantity on hand). The quantity on hand logically belongs with the ITEM entity because ItemID functionally determines the quantity in the store, there's only one inventory quantity for any item. The quantity on order and the due date are placed in the Order table. Each order that is placed gets an OrderID and is for a particular quantity, to one vendor and with one due date. This table embodies the functional dependency:

     OrderID → ( Vendor, OrderQty, DueDate )

This design allows for multiple orders to be placed for the same item with any combination of vendors. Reading the diagram the relationship between Order and ITEM says, "An item has one or more order and an order has exactly one item." The relationship between Order and Vendor says, "A vendor has one or more orders and an order has exactly one vendor."

The Human table is an intersection table that was added for part C. The Human table takes the attributes common to CUSTOMER and EMPLOYEE. Those attributes are removed from CUSTOMER and EMPLOYEE. Only the common attributes are combined, so Customer retains the customer's address. I chose to do the minimum possible change that seemed to be needed by the assignment --I'm getting more cautious in my old age. Placing a person's address into the Human table is a common sense alternative. I created a HumanID key because it's safe. But phone number could have worked well (think of how many times you get asked for your rewards card phone number in a checkout line). The Human table has mandatory relationships with CUSTOMER and EMPLOYEE. The relationships read, "An Employee is exactly one Human and a Human may be one employee." and, "A Customer is exactly one Human and a Human may be one Customer." 

Grading Your Assigned Drawing 

You will grade the diagram you've been assigned using the following criteria. Remember, the grade you give will not be the grade the other student receives. Your grade for this lab will depend on the correctness of your interpretation of their diagrams and if you spot major problems. 
  1. Part A. (0 to 10 points possible). What are the relationships between SALE, EMPLOYEE and ITEM? Do they make sense? If not, why? 
  2. Part B (0 to 5 points possible). 
    1. Has the relationship between ITEM and VENDOR been updated? What is it? 
    2. Does the schema allow for orders to be placed? Why or why not? 
  3. Part C (0 to 5 points possible). Have CUSTOMER and EMPLOYEE been sufficiently combined. What is there relationship? 

Update Your Lab 11

With your updated understanding of the design, update your lab 11 assignment and resubmit it. Your second submission will be counted for credit and it will be graded against the criteria above. 

Lab 12 Grading Criteria

Your lab will be graded on how well you answer the questions for each part. Be clear in your answers and work carefully. This lab is designed to give you practice interpreting E-R diagrams and to prepare you for the kind of question I like to ask on Finals. Some of you will have drawings that aren't very complete. I will not deduct points for questions you can't answer, but I expect that the answers you can give will be especially well explained and that you tell me why you can't answer the question. 


Turn In
  1. Submit your write-up as an uploaded document (not a comment). 
  2. Submit the drawing you graded as an upload.
  3. Submit your updated lab 11. 
Submit your homework on Canvas.
ċ
10476_145351_QueenAnne.mwb
(17k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
10548_145232_QueenAnne.mwb
(13k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
13820_148861_QueenAnne.mwb
(22k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
14159_145083_QueenAnne.mwb
(13k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
14946_148925_QueenAnne.mwb
(14k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
15819_144967_QueenAnne.mwb
(16k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
16464_145479_QueenAnne.mwb
(18k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
2126_148923_QueenAnne.mwb
(11k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
476_145484_QueenAnne.mwb
(16k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
5230_144777_QueenAnne.mwb
(15k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
7610_144788_QueenAnne.mwb
(55k)
Michael Matera,
Apr 29, 2016, 1:13 PM
ċ
9432_145439_QueenAnne.mwb
(14k)
Michael Matera,
Apr 29, 2016, 1:13 PM
Comments