Database Reference
In-Depth Information
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 MyDocuments folder.
Using the QACS database, create an SQL script named QACS-Create-Tables.sql to
answer questions G and h.
G. 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.
Using the QACS database, create an SQL script named QACS-Insert-Data.sql to
answer question I.
I. Write INSERT statements to insert the data shown in Figures 7-43, 7-44, 7-45, 7-46,
7-47, and 7-48.
Using the QACS database, create an SQL script named QACS-DML-CH07.sql to answer
questions J and K.
J. Write an UPDATE statement to change values of ITEM.ItemDescription from Desk
Lamp to Desk Lamps .
K. Create and INSERT new data records to record a SALE and the SALE_ITEMs for that
sale. Then write a DELETE statement(s) to delete that SALE and all of the items on
that SALE. How many DELETE statements did you have to use? Why?
Search WWH ::




Custom Search