SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 360

D. State relationships as implied by foreign keys, and specify the maximum and minimum
cardinality of each relationship. Justify your choices.
E. Explain how you will enforce the minimum cardinalities in your answer to part D. Use
referential integrity actions for required parents, if any. Use Figure 6-28(b) as a boiler-
plate for required children, if any.
F. Create a database named MI in your DBMS.
G. 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 MI-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 MI-Database .
● For the SQL Workbench, create a folder named MI-Database in the Schemas folder
in your MyDocuments folder.
Using the MI database, create an SQL script named MI-Create-Tables.sql to answer
questions h and I.
h. Write CREATE TABLE statements for each of the tables using your answers to the
parts A-E, as necessary. If you decided to use a StoreID surrogate key, set the first value
to 1000 and increment by 50. Set the first value of EmployeeID to 1 and increment it
by 1. Set the first value of PurchaseID to 500 and increment it by 5. Set the first value
of ShipmentID to 100 and increment it by 1. ReceiptNumber should start at 200001
and increment by 1. Use FOREIGN KEY constraints to create appropriate referential
integrity constraints. Set UPDATE and DELETE behavior in accordance with your
referential integrity action design. Set the default value of InsuredValue to 100. Write a
constraint that STORE.Country be limited to seven countries (Hong Kong, India, Japan,
Peru, Philippines, Singapore, United States).
I. Explain how you would enforce the rule that SHIPMENT_ITEM.InsuredValue be at
least as great as ITEM.PriceUSD.
Using the MI database, create an SQL script named MI- Insert-Data.sql to answer
question J.
J. Write INSERT statements to insert the data shown in Figures 7-49, 7-50, 7-51, 7-52, and
7-53.
Using the MI database, create an SQL script named MI- DML-CH07.sql to answer
questions K and L.
K. Write an UPDATE statement to change values of STORE.City from New York City to
NYC .
L. Create and INSERT new data records to record a SHIPMENT and the SHIPMENT_
ITEMs for that SHIPMENT. Then write a DELETE statement(s) to delete that
SHIPMENT and all of the items on that SHIPMENT. How many DELETE statements
did you have to use? Why?
Using the MI database, create an SQL script named MI-Create-Views-and-Functions.sql
to answer questions M through R.
M. Write an SQL statement to create a view called PurchaseSummaryView that shows
only ITEM.ItemID, ITEM.PurchaseDate, ITEM.ItemDescription, and ITEM.PriceUSD.
Run the statement to create the view, and then test the view with an appropriate SQL
SELECT statement.