Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I. PROBLEMS: 1. (50 pts = 5 x 10 pts) Consider that the update operations below are applied to the COMPANY relational database schema (Figure

image text in transcribed
image text in transcribed
I. 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 IC Cascade) of enforcing each of the violated constraint. For example, say, if you have one violated (referential integrity) constraint, you write down al maximum!, the 5 different actions applying to this violation, explaining how each action is to be applied to this violation (ie, at maximum. 5 explanations, totally). For another example, say, if you have two violated (referential integrity) constraints, you write down at maximum, the 4 different actions applying to each of the 2 violations, explaining how each action is to be applied to the respective violation (i.e., at maximum, 2 violations * 5 possible actions each - 10 explanations, totally). Etc., for three violated constraints, and so on. Notice that there are four relational constraints in the reintional data model: domain constraint, 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 which ones are violated, if any, otherwise you may overlook for some of the violations). For that, you need also to use the COMPANY database state in Figure 3.6 on p. 72 of 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 on 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 nores, pages 15-17] 2. (23 pts = 8 pts PKs & 5x3 = 15 pts FKs) Consider the following six relations for an order processing database application in a company: CUSTOMER (Cust#, Cname, City) ORDER (Ordert, Odate, Custi, Ord Amt) ORDER ITEM (Order#, Item#, Qty) ITEM (Itemt, Unit price) SHIPMENT (Order!, Warehouset, Ship_date) WAREHOUSE (Warehouse, City) Here, Ord Amt refers to total dollar amount of an order: Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. Assume that an order can be shipped from several warehouses. a. (8 points) Underline the primary keys in each relation. b. (15 points) Specify foreign keys for this schema, stating any assumptions you make. To do this, use the notation SHOWN in the following example: ORDER.Custno CUSTOMER Here, Custno in the ORDER relation is a foreign key and it refers IMPLICITLY to the primary key Custno in the CUSTOMER relation). 3. (8 points) Write down short answer to the following: When you implement a DB (say COMPANY DB) in SOL you MUST choose only one of these actions for each fk "at maximum. since some constraints, when violated, can NOT be fixed with 5 alternative actions, but only with 1 or 2, for example the entity integrity constraint violation I. 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 IC Cascade) of enforcing each of the violated constraint. For example, say, if you have one violated (referential integrity) constraint, you write down al maximum!, the 5 different actions applying to this violation, explaining how each action is to be applied to this violation (ie, at maximum. 5 explanations, totally). For another example, say, if you have two violated (referential integrity) constraints, you write down at maximum, the 4 different actions applying to each of the 2 violations, explaining how each action is to be applied to the respective violation (i.e., at maximum, 2 violations * 5 possible actions each - 10 explanations, totally). Etc., for three violated constraints, and so on. Notice that there are four relational constraints in the reintional data model: domain constraint, 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 which ones are violated, if any, otherwise you may overlook for some of the violations). For that, you need also to use the COMPANY database state in Figure 3.6 on p. 72 of 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 on 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 nores, pages 15-17] 2. (23 pts = 8 pts PKs & 5x3 = 15 pts FKs) Consider the following six relations for an order processing database application in a company: CUSTOMER (Cust#, Cname, City) ORDER (Ordert, Odate, Custi, Ord Amt) ORDER ITEM (Order#, Item#, Qty) ITEM (Itemt, Unit price) SHIPMENT (Order!, Warehouset, Ship_date) WAREHOUSE (Warehouse, City) Here, Ord Amt refers to total dollar amount of an order: Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. Assume that an order can be shipped from several warehouses. a. (8 points) Underline the primary keys in each relation. b. (15 points) Specify foreign keys for this schema, stating any assumptions you make. To do this, use the notation SHOWN in the following example: ORDER.Custno CUSTOMER Here, Custno in the ORDER relation is a foreign key and it refers IMPLICITLY to the primary key Custno in the CUSTOMER relation). 3. (8 points) Write down short answer to the following: When you implement a DB (say COMPANY DB) in SOL you MUST choose only one of these actions for each fk "at maximum. since some constraints, when violated, can NOT be fixed with 5 alternative actions, but only with 1 or 2, for example the entity integrity constraint violation

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_step_2

Step: 3

blur-text-image_step3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

MySQL/PHP Database Applications

Authors: Brad Bulger, Jay Greenspan, David Wall

2nd Edition

ISBN: 0764549634, 9780764549632

More Books

Students also viewed these Databases questions

Question

=+ (a) Extend to the case of bounded F.

Answered: 1 week ago

Question

What is the formula to calculate the mth Fibonacci number?

Answered: 1 week ago