Question

Assume that The Queen Anne Curiosity Shop designs a database with the following tables.

CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncyptedPassword, City, State, ZIP, Phone, ReferredBy)

EMPLOYEE (EmployeeID, LastName, FirstName, Position, Supervisor, OfficePhone, EmailAddress)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName,

Address, City, State, ZIP, Phone, Fax, EmailAddress)

ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)

SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)

SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

The referential integrity constraints are:

ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER

Supervisor in EMPLOYEE must exist in EmployeeID in EMPLOYEE

CustomerID in SALE must exist in CustomerID in CUSTOMER

VendorID in ITEM must exist in VendorID in VENDOR

EmployeeID in SALE must exist in EmployeeID in EMPLOYEE

SaleID in SALE_ITEM must exist in SaleID in SALE

ItemID in SALE_ITEM must exist in ItemID in ITEM

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, VendorID of VENFOR, ItemID of ITEM, and SaleID of SALE are all surrogate keys with values as follows:

CustomerID   Start at 1        Increment by 1

EmployeeID   Start at 1        Increment by 1

VendorID        Start at 1        Increment by 1

ItemID             Start at 1        Increment by 1

SaleID             Start at 1        Increment by 1

A vendor may be an individual or a company. If the vendor is an individual, the CompanyName field is left blank, while the ContactLastName and ContactFirstName fields must have data values. If the vendor is a company, the company name is recorded in the CompanyName field, and the name of the primary contact at the company is recorded in the ContactLastName and ContactFirstName fields.

A. Specify NULL/NOT NULL constraints for each table column.

B. Specify alternate keys, if any.

C. State relationships as implied by foreign keys, and specify the maximum and minimum cardinality of each relationship. Justify your choices.

D. Explain how you will enforce the minimum cardinalities in your answer to part C. Use referential integrity actions for required parents, if any. Use Figure 6-28(b) as a boilerplate for required children, if any.

E. Create a database named QACS in your DBMS.

F. Create a folder in your My Documents folder to save and store *.sql scripts containing the SQL statements that you are asked to create in the remaining questions in this section.

• For the SQL Server Management Studio, create a folder named QACS-Database in the Projects folder structure in your My Documents folder.
• In the Oracle SQL Developer folder structure in your My Documents folder, create a folder named QACS-Database.
• For the SQL Workbench, create a folder named QACS-Database in the Schemas folder in your My Documents folder.

G. Write SQL CREATE TABLE statements for each of these tables. Write CREATE TABLE statements for each of the tables using your answers to parts A–D, as necessary. Set the surrogate key values as shown above. Use FOREIGN KEY constraints to create appropriate referential integrity constraints. Set UPDATE and DELETE behavior in accordance with your referential integrity action design. Run these statements to create the QACS tables.

H. Explain how you would enforce the data constraint that SALE_ITEM.UnitPrice be equal to ITEM.ItemPrice, where SALE_ITEM.ItemID = ITEM.ItemID.

Answer 1

A) Specify NULL/NOT NULL constraints for each table column.

Table Name	Null constraint	Not Null Constraint
Customer	ReferredBy	CustomerID, LastName, FirstName, EmailAddress, EncyptedPassword, City, State, ZIP, Phone
Employee	OfficePhone	EmployeeID, LastName, FirstName, Position, Supervisor, EmailAddress
Vendor	Fax	VendorID, CompanyName, ContactLastName, ContactFirstName,Address, City, State, ZIP, Phone, EmailAddress
Item	PurchaseDate,ItemDescription	ItemID, ItemCost, ItemPrice, VendorID
Sale	-	SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total
Sale_Item	-	SaleID, SaleItemID, ItemID, ItemPrice

B) Specify Alternate keys, if any

Alternate Key: This is a key of table with one or more columns which can be unique but not a primary key.

Table Name	Alternate Keys
Customer	EmailAddress
Employee	EmailAddress
Vendor	EmailAddress
Item	-
Sale	-
Sale_Item	-

C) & D) State relationships as implied by foreign keys, and specify the maximum and minimum cardinality of each relationship. Justify your choices.

Explain how you will enforce the minimum cardinalities in your answer to part C. Use referential integrity actions for required parents, if any.

Cardinality: Express the number of entities to with another entity can be associated via a relationship set.

These are two types:

Minimum Cardinality & Maximum Cardinality:

Minimum cardinality is the minimum count of instances of an entity that are related with each instance of another entity. Maximum cardinality is the maximum number of instances of an entity that are related with each instance of another entity.

Table Name	Primary Key	Foreign Key	Minimum Cardinality	Maximum Cardinality	Justification
Customer	CustomerID	-	-	-	Parent tables - No Foreign key is needed
Employee	EmployeeID	-	-	-	Parent tables - No Foreign key is needed
Vendor	VendorID	-	-	-	Parent tables - No Foreign key is needed
Item	ItemID	VendorID	mandatory-mandatory	many-one	Many items can have single vendor
Sale	SaleID	CustomerID, EmployeeID	mandatory-mandatory	one-one	one sale id can contain only one emp id and cust id
Sale_Item	SaleItemID	SaleID, ItemID	mandatory-mandatory	one-many	one sale item can have many one sale ID and multiple ID