2. Your assignment will be graded based on its documentation, readability, correctness, and simplicity. 1. PROBLEMS: 1. (50 pts = 5 x 10 pts) Consider that the update operations below are applied to the COMPANY relational database schema (Figure 3.7 on p. 75 of the textbook "Fundamentals of Database Systems," 6th Edition) 1 Insert into EMPLOYEE. 2 Insert into EMPLOYEE. 3 Insert into PROJECT. 4 Delete the WORKS ON tuples with Essn = '333445555'. 5 Modify the Mgrssn and Mgrstartdate of the DEPARTMENT tuple with Dnumber - 4 to 123456788' and '2007-10-01', respectively. For each operation, write down (1) EACH of the four integrity constraint violated by the operation (op), if any, and (2) ALL possible different ways, also called actions (Reject, Change the op, Set Default; Set Null, and RECALL: The 4 integrity constraints (ICs) are: Domain constraint, Key constraint Entity. Ic, and Referential each term in a sepa thea CS 411-01 ferential integrity) constraint, you write down at lying to this violation, explaining how each action is Cascade) of enforcing each of the violated constraint. . For example, say, if you have one violated (referential inte maximum the 5 different actions applying to this viola applied to this violation (ie, at maximum, explanations, totally ential integrity) constraints, you write of the 2 violations, explaining how ve violation (i.e., at maximum, 2 violations possible For another example, say, if you have two violated (referential integris down at maximum, the 4 different actions applying to each of the 2 violati cach action is to be applied to the respective violation (i actions cach - 10 explanations, totally). Etc., for three violated constraints, and so on. relational data model: domain constraint, Notice that there are four relational constraints in the relational data model key constraint, entity integrity constraint, referential integrity constraint. For each operation, you need to check for violation of each of the four constraints (not all of the constraints will be violated, just some of them but you need to check all of them to be sure ones are violated, if any, otherwise you may overlook for some of the violations 72 . For that you need also to use the COMPANY darabase stare in Figure 3,6 on the Elmasri's textbook; this Figure 3.6 is also shown in the lecture notes) For the format of answering this question, you need to look in the examples of violations of constraints p. 76, 77, and 78 in the Elmasri's textbook (6/E) and answer here in THE SAME manner and format. Also you may consult the Ch. 3 lecture notes, pages 15-17) 2. (23 pts - 8 pts PKs & 5 x 3 = 15 pts FKs) Consider the following six relations for an order processing database application in a company: CUSTOMER (Cust#, Cname, City) ORDER (Ordert, Odate, Cust#, Ord_Amt) ORDER_ITEM (Order#, Item#, Qty) ITEM (Item#, Unit_price) SHIPMENT (Order#, Warehouse#, Ship date) WAREHOUSE (Warehouse, City) Here, Ord Amt refers to total dollar amount of an order; Odate refers to total dollar amount of an order: Odate is the date the order was place: Ship date is the date an order is shipped from the warehouse. Assume that several warehouses. arehouse. Assume that an order can be shipped from a (8 points) Underline the primary keys in each relation. b. (15 points) Specify foreign keys for this schema, stating any assump ating any assumptions you make. To do this, use the notation SHOWN in the following example: (Here, Custno in the ORDER relation is a foreign Custno in the CUSTOMER relation). ng example: ORDER.Custno CUSTOMER and it refers IMPLICITLY to the primary key 3.3 Update Operations, Transactions, and Dealing with Constraint Violations 75 EMPLOYEE Frame Minit Lname Bdate Address Sex Salary Super_ssn Dno Ssn TIL DEPARTMENT Dname | Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pro Hours DEPENDENT Essn Dependent name Sex Bdate Figure 3.7 Referential integrity constraints displayed on the COMPANY relational database schema Relationship