Define referential integrity constraints as defined in Tables 2–7. Enforce each referential integrity constraint. For each referential integrity constraint, use a constraint name in the CREATE TABLE statement.
Choose appropriate actions when referenced rows are deleted. When an expense report is deleted, both the heading row in the ExpenseReport table and the associated detail rows in the ExpenseItem table must be deleted. For other referential integrity constraints involving required foreign keys (nulls not allowed), deleting a referenced row in a parent table must not be permitted if there are associated referencing rows in a child table. For referential integrity constraints involving optional foreign keys (nulls allowed) except ExpenseReport.ApprUserNo, deleting a referenced row must make the foreign key of the referencing row null. For ExpenseReport.ApprUserNo, restrict deletion of related User rows.
Check Constraints
Write named constraints using the CHECK CONSTRAINT clause for the following integrity constraints:
Email contains an @
ERStatusDate >= ERSubmitDate
BIYear>
ERStatus is ‘PENDING’ ‘APPROVED’, or ‘DENIED’
ExpApprAmt <= ExpAmt
The combination of BIYear, OrgNo, and ECNo is unique in the BudgetItem table
Nulls are allowed for ExpenseReport.ApprUserNo only if ERStatus is equal to PENDING
Load Data
Use the text file containing INSERT statements to load the data in the tables. The text file is located in Blackboard. Note that the INSERT statements use the table and column names listed in Tables 1–7.
Attention: I have tried to execute the below information my query but just keep getting error messages. I would really need some assistance.
3. Insert a new OrderTbl row with you as the customer, the person from problem 2 (Part 2) as the employee, and your choice of values for the other columns of the OrderTbl table.
INSERT INTO OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity, OrdState, OrdZip)
VALUES (‘O25559801’, ’26-Dec-1994’, ‘C12345689’, ‘E98765432’, ‘Marisa Fox’, ‘100 Capital Street’, ‘Lynchburg’, ‘VA’, 24502-0000’)
4. Insert two rows in the OrdLine table corresponding to the OrderTbl row inserted in problem 3 (Part 2).
INSERT INTO OrdLine (OrdNo, ProdNo, Qty)
VALUES (‘O25559801’, ‘P8424510’, 2) INSERT INTO OrdLine (OrdNo, ProdNo, Qty) VALUES (‘O25559801’, ‘P8787345’, 2)
5. Increase the price by 10 percent of products containing the words Ink Jet.
UPDATE Product SET ProdPrice * 1.1 WHERE ProdName LIKE ‘%Ink Jet%’
6. Change the address (street, city, and zip) of the new row inserted in problem 1 (Part 2).
UPDATE Customer SET CustStreet = ‘102 ’ Rockport RD CustCity = ‘Worcester CustZip =’01605’’
7)Delete cascade sets as a rule to delete referenced rows and its associated dependent rows.8) DELETE the new row(s) of the table listed first in the order form 7(part 2)(8)–> Delete the new row(s) of the table listed first in the order for problem 7 (part2)