QACS Database

Step 1 Table Creation and Data Import

Table and Surrogate Key Definitions:

CUSTOMER(CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone)
CustomerIDStart at 1Increment by 1

EMPLOYEE(EmployeeID, LastName, FirstName, Phone, Email)
EmployeeIDStart at 1Increment by 1

VENDOR(VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)
VendorIDStart at 1Increment by 1

ITEM(ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)
ItemIDStart at 1Increment by 1

SALE(SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)
SaleIDStart at 1Increment by 1

SALE_ITEM(SaleID, SaleItemID, ItemID, ItemPrice)

Data Descriptions and Relationship Diagram:

CUSTOMER Table

Attribute Data Type Required
CustomerID Integer Yes
LastName Char(25) Yes
FirstName Char(25) Yes
EmailAddress Varchar(100) Yes
EncryptedPassword Varchar(25) Yes
Address Char(35) No
City Char(35) No
State Char(2) No
ZIP Char(10) No
Phone Char(12) Yes

EMPLOYEE Table

Attribute Data Type Required
EmployeeID Integer Yes
LastName Char(25) Yes
FirstName Char(25) Yes
Phone Char(12) Yes
Email Varchar(100) Yes

VENDOR Table

Attribute Data Type Required
VendorID Integer Yes
CompanyName Char(50) No
ContactLastName Char(25) Yes
ContactFirstName Char(25) Yes
Address Char(35) No
City Char(35) No
State Char(2) No
ZIP Char(10) No
Phone Char(12) Yes
Fax Char(12) No
Email VarChar(100) Yes

ITEM Table

Attribute Data Type Required
ItemID Integer Yes
ItemDescription Varchar(50) Yes
PurchaseDate Date Yes
ItemCost Number(9,2) Yes
ItemPrice Number(9,2) Yes
VendorID Integer Yes

SALE Table

Attribute Data Type Required
SaleID Integer Yes
CustomerID Integer Yes
EmployeeID Integer Yes
SaleDate Date Yes
SubTotal Money No
Tax Money No
Total Money No

SALE_ITEM Table

Attribute Data Type Required
SaleID Integer Yes
SaleItemID Integer Yes
ItemID Integer Yes
ItemPrice Number(9,2) No

ERD Diagram. see image.

Step 2 Table Update, View Creation and Function Creation

Store all SQL statements into a SQL script named Ch7-Step2.sql. All SQL Code should be written using good style techniques so that the code is not only correct, but is clear and easy to read.

1.Use the ALTER TABLE statement to set the composite primary key for SALE_ITEM.

2.Use the ALTER TABLE statement to add named Foreign Key constraints which satisfy the following referential integrity constraints:

• 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

3.Add a new customer with your personal information to the CUSTOMER table. Assign NULL to the encrypted password attribute.

4.Write an UPDATE statement to change values of ITEM.ItemDescription from Desk Lamp to Desk Lamps.

5.Write an SQL statement to create a view called SaleSummaryView that contains SALE.SaleID, SALE.SaleDate, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.

6.Create and test a user-defined function named LastNameFirst that combines two parameters named FirstName and LastName into a concatenated name field formatted LastName, FirstName (including the comma and space).

7.Create and test a user-defined function named CityStateZIP that combines three parameters named City, State, and ZIP into a concatenated name field formatted City, State ZIP (including the comma and the spaces).

8.Write an SQL statement to create a view called CustomerSaleSummaryView that contains SALE.SaleID, SALE.SaleDate, CUSTOMER.LastName, CUSTOMER,FirstName, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.

9.Write an SQL statement to create a view called CustomerLastNameFirstSaleSummaryView that contains SALE.SaleID, SALE.SaleDate, the concatenated customer name using the LastNameFirst function, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.

10.Write an SQL statement to create a view called CustomerSaleHistoryView that:

• (1)Includes all columns of CustomerSaleSummaryView except SaleItemID, SaleDate, ItemID, and ItemDescription.
• (2)Groups orders by SaleID, LastName, and FirstName, and in that order.
• (3)Sums and averages ItemPrice for each order for each customer. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.

11.Write an SQL statement to create a view called CustomerSaleCheckView that uses CustomerSaleHistoryView and that shows that any customer names and sales ID for which the sum of the item prices is not equal to SALE.SubTotal. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.