Referential Integrity Constraints

 

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.

Don't use plagiarized sources. Get Your Custom Essay on
Referential Integrity Constraints
For as little as $15/Page
Order Essay

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)

Order your essay today and save 25% with the discount code: MANGO

Order a unique copy of this paper

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
Top Academic Writers Ready to Help
with Your Research Proposal
Live Chat+1(978) 822-0999EmailWhatsApp

Order your essay today and save 25% with the discount code THANKYOU