TABLE Customers-- ------------------- -- Add a Not Null constraint on LastName and FirstName by altering table and
Question:
TABLE Customers--
-------------------
-- Add a Not Null constraint on LastName and FirstName by altering table and modifying the column
-- Add a primary key constraint on Customers table using Customer# column
-- Add a check constraint on region column to verify if it is with in the following region ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')
-- Add CONSTRAINTS here after this line:
-- After adding constraints, add the following rows and observe the output and errors:
INSERT INTO customers
VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'NW');
INSERT INTO customers
VALUES (1021, NULL, NULL, 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE');
INSERT INTO customers
VALUES (1021, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'NO');
INSERT INTO customers
VALUES (1021, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'NW');
----------------
--TABLE Orders--
----------------
-- Add a primary key constraint on Orders table using Order# column
-- Add a foreign key constraint to define a relationship between Customers and Orders, also apply integity rule On DELETE CASCADE
-- Add CONSTRAINTS here after this line:
-- After adding constraints excute the below command and observe the output and errors:
SELECT * FROM orders WHERE customer# = 1005;
DELETE FROM customers WHERE customer# = 1005;
SELECT * FROM orders WHERE customer# = 1005;
-------------
--Publisher--
-------------
-- Add a primary key constraint on Publisher table using PubID column
-- Add CONSTRAINTS here after this line:
----------
--Author--
----------
-- Add a primary key constraint on Author table using AuthorID column
-- Add CONSTRAINTS here after this line:
---------
--Books--
---------
-- Add a primary key constraint on Books table using ISBN column
-- Add a foreign key constraint to define a relationship between Publisher and Book, also apply integity rule On DELETE CASCADE
-- Add CONSTRAINTS here after this line:
--------------
--ORDERITEMS--
--------------
-- Add a composite key constraint on ORDERITEMS table using Order# and Item# columns
-- Add a foreign key constraint to define a relationship between ORDERITEMS and Books, also apply integity rule On DELETE CASCADE
-- Add a foreign key constraint to define a relationship between ORDERITEMS and Order, also apply integity rule On DELETE CASCADE
-- Add a CHECK constraint to to validate the order quantity > 0
-- Add CONSTRAINTS here after this line:
-- After adding constraints, add the following rows and observe the output and errors:
INSERT INTO orderitems
VALUES (1000,2,'3437212490',0,19.95);
--------------
--BOOKAUTHOR--
--------------
-- Add a composite key constraint on BOOKAUTHOR table using isbn and authorid columns
-- Add a foreign key constraint to define a relationship between BOOKAUTHOR and Books
-- Add a foreign key constraint to define a relationship between BOOKAUTHOR and Author
-- Add CONSTRAINTS here after this line:
Your Humber Number:
-- Your Last Name:
-- Date:
-- Purpose: Lab 4
-- Submission Instructions: Please complete the file with constraints, take screen-shots of the output of each step, put all screenshots together in a word document and submit it along with Lab4.sql file.
---------------------------------
-- The objective of this lab is to practice applying constraints on tables and test them to observe the output.
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last