Case Description Assume that The Queen Anne Curiosity Shop designs a database wi

Question

Case Description

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

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)

EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

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:

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, ItemID of ITEM, SaleID of SALE, and VendorID of VENDOR 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.

Question: Specify NULL/NOT NULL constraints for each table column and indicate alternate keys, if any.

Explanation / Answer

CREATE TABLE CUSTOMER
(
CustomerID Int PRIMARY KEY,
LastName Char (35) NOT NULL,
FirstName Char (35) NOT NULL,
Address Varchar2 (200),
City Char (20),
State Char (35) NOT NULL,
ZIP Varchar2 (35) NOT NULL,
Phone Number,
Email Varchar2 (35)
);

CREATE TABLE EMPLOYEE
(
EmployeeID Int PRIMARY KEY,
LastName Char (35) NOT NULL,
FirstName Char (35) NOT NULL,
Phone Number,
Email Varchar2 (35)
);

CREATE TABLE VENDOR
(
VendorID Int PRIMARY KEY,
CompanyName Char (40) NOT NULL,
ContactLastName Char (35) NOT NULL,
ContactFirstName Char (35),
Address Varchar2 (200),
City Char (20),
State Char (35) NOT NULL,
ZIP Varchar2 (35) NOT NULL,
Phone Number,
Fax Number,
Email Varchar2 (35)
);

CREATE TABLE ITEM
(
ItemID Int,
ItemDescription PRIMARY KEY Char (35) NOT NULL,
PurchaseDate Date NOT NULL,
ItemCost Money NOT NULL,
ItemPrice Money NOT NULL,
VendorID Int NOT NULL
);

CREATE TABLE SALE
(
SaleID Int PRIMARY KEY,
CustomerID Int NOT NULL,
EmployeeID Int NOT NULL,
SaleDate Date NOT NULL,
SubTotal Money NOT NULL,
Tax Money NOT NULL,
Total Money NOT NULL
);

CREATE TABLE SALE_ITEM
(
SaleID Int PRIMARY KEY SaleItemID Int NOT NULL,
ItemID Int NOT NULL,
ItemPrice Money NOT NULL
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.