Can someone help me with this please? The link for the diagram is below. Convert
ID: 3560854 • Letter: C
Question
Can someone help me with this please? The link for the diagram is below.
Convert the data model you constructed for the Queen Anne Curiosity Shop in part D at the end of Chapter 4 into a relational database design for the Queen Anne Curiosity Shop. Document your database design as follows.
Diagram: http://imgur.com/UGjXLTE
A. (32 points) Given the provided E-R diagram, specify tables, primary keys, foreign keys, and other column properties. You may find this template to be helpful:
Table Name
Column Name
Data Type
Key
Required
Default Value
Remarks (Surrogate?)
B. (4 points) Describe how you have represented weak entities, if any exist.
C, (4 points) Describe how you have represented supertype and subtype entities, if any exist.
Table Name
Column Name
Data Type
Key
Required
Default Value
Remarks (Surrogate?)
Explanation / Answer
A. (32 points) Given the provided E-R diagram, specify tables, primary keys, foreign keys, and other column properties. You may find this
template to be helpful:
Table Name Column Name Data Type Key Required Default Value Remarks (Surrogate?)
PERSON PersonID NUMBER primary yes surrograte primary key by sequence
CUSTOMER PersonID NUMBER primary yes surrograte primary key by sequence
CUSTOMER PersonID NUMBER foreign yes foreign key to Person table
EMPLOYEE PersonID NUMBER primary yes surrograte primary key by sequence
EMPLOYEE PersonID NUMBER foreign yes foreign key to Person table
VENDOR VendorID NUMBER primary yes surrograte primary key by sequence
SALE SaleID NUMBER primary yes surrograte primary key by sequence
ORDER InvoiceNumber NUMBER primary yes surrograte primary key by sequence
ITEM ItemID NUMBER primary yes surrograte primary key by sequence
SALE_ITEM SaleID NUMBER combine primary yes
SALE_ITEM SaleItemID NUMBER combine primary yes
SALE_ITEM SaleID NUMBER foreign yes foreign key to Sale table
SALE_ITEM SaleItemID NUMBER foreign yes foreign key to Sale table
SALE SaleDate Date foreign yes foreign key to Order table
ITEM ItemPrice NUMBER foreign yes foreign key to Order table
B. (4 points) Describe how you have represented weak entities, if any exist.
Customer and Employee tables are weak entity. A record can be uniquely identified when link back to Person table based on PersonID
C, (4 points) Describe how you have represented supertype and subtype entities, if any exist.
supertype --> Person
subtype --> Employee, Customer
Primary Key for Person table (PersonID) is the primary key for both Customer and Employee. Attributes in Person table applied to both Customer and Employee.
Customer and Employee table only have attribute applied to each type.