$[$mailAddress$)$

PL OYEE $($EmployeeID$,$ LastName, FirstName, Phone, EmailAdeirstName, Ad$-$

NDOR $($VendorlD$,$ CompanyName, ContactLastName, ContactFirstName, M $($lemiD $,$ State, ZIP, Phone, Fax, EmailAddress$)$

I $($SaleII$),$ CustomerID, EmployeeID, SaleD Item Cost, ItemPrice, VendorID$)$

F ITEM $($SaleID$.$ SaleItemID, ItemID, ItemPrice, SubTotal, Tax, Total$)$

referential integrity constraints are:

$($worlD in TTEM must exist in VendorID in VENDOR

$($avmerID in SALE must exist in CustomerID in CUSTOMER

uptoyecID in SALE must exist in EmployeeID in EMPLOYEE

ateli$)$ in SALE ITEM must exist in SaleID in SALE

$($ID in SALE ITEM must exist in ItemID in ITEM

A ssume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, VendorID of VEN. DORE, IremID of ITEM, and SaleID of SALE are all surrogate keys with values as follows:

$\backslash$table$[[$CustomerID$,$Start at $1,$Increment by $1],[$FimployeelD$,$Start at $1,$Increment by $1],[$VendorID$,$Start at $1,$Increment by $1],[$ltemID$,$Start at $1,$Increment by $1],[$SalelD$,$Start at $1,$Increment by $1]]$

SaleItemID of SALE$\_$ITEM is not a true surrogate key, but rather a counter that starts at $1$ and increments by $1$ for each SaleID in SALE. This number will require special handling in the database, needing to be either manually inserted or to have specific program logic written to insert the correct number. In this text we will simply insert the number manually.

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.

Sample data are shown in Figure $3-47,$ Figure $3-48,$ Figure $3-49,$ Figure $3-50,$ Figure $3-51$ and Figure $3-52.$ These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. If possible, run these state ments in an actual DBMS$,$ as appropriate, to obtain your results. Name your database QACS

Use data types consistent with the DBMS you are using. If you are not using an actua DBMS$,$ consistently represent data types using either the MySQL$,$ Microsoft SQL Serve or Oracle Database data types shown in Figure $3-5.$ For each SQL statement you write show the results based on your data.

Write SQL statements and answer questions for this database as follows:

A$.$ Write SQL CREATE TABLE statements for each of these tables. Omit foreign keys.

B$.$ Write foreign key constraints for the relationships in each of these tables. Make you own assumptions regarding cascading deletions and justify those assumptions. $($Hin You can combine the SQL for your answers to parts A and B$.)$