TABLE Customers– ——————- — Add a Not Null constraint on LastName and FirstName by altering table and modifying the column

TABLE Customers– ——————- — Add a Not Null constraint on LastName and FirstName by altering table and modifying the column

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.

Expert Answer:

Answer rating: 100% (QA)

SQL script with the requested constraints added to the respective tables TABLE Customers ALTER TABLE Customers MODIFY LastName VARCHAR 255 NOT NULL Adding Not Null constraint on LastName MODIFY FirstN
View the full answer