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