Suppose that each of the following Update operations is applied directly to the database state shown in Figure 3.6. Discuss all integrity constraints vio- lated by each operation, if any, and the different ways of enforcing these con- straints. a. Insert <'Robert, 'F, 'Scott', '943775543, '1972-06-213 '2365 Newcastle Rd, Bellaire, TX, M, 58000, '888665555, l> into EMPLOYEE. b. Insert <'ProductA, 4, 'Bellaire, 2> into PROJECT. c. Insert <'Production', 4, '943775543, '2007-10-01’> into DEPARTMENT. d. Insert <'677678989', NULL, '40.0'> into WORKS_ON. e. Insert <'453453453, 'John, 'M, '1990-12-12, 'spouse'> into DEPENDENT. f. Delete the WORKS_ON tuples with Essn = '333445555? g. Delete the EMPLOYEE tuple with Ssn = '987654321?. %3D h. Delete the PROJECT tuple with Pname = 'ProductX. i. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ʻ123456789' and '2007-10-01', respectively. j. Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = '999887777’ to '943775543' k. Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777’ and Pno = 10 to '5.0.

Suppose that each of the following Update operations is applied directly to the database state shown in Figure 3.6. Discuss all integrity constraints vio- lated by each operation, if any, and the different ways of enforcing these con- straints. a. Insert <'Robert, 'F, 'Scott', '943775543, '1972-06-213 '2365 Newcastle Rd, Bellaire, TX, M, 58000, '888665555, l> into EMPLOYEE. b. Insert <'ProductA, 4, 'Bellaire, 2> into PROJECT. c. Insert <'Production', 4, '943775543, '2007-10-01’> into DEPARTMENT. d. Insert <'677678989', NULL, '40.0'> into WORKS_ON. e. Insert <'453453453, 'John, 'M, '1990-12-12, 'spouse'> into DEPENDENT. f. Delete the WORKS_ON tuples with Essn = '333445555? g. Delete the EMPLOYEE tuple with Ssn = '987654321?. %3D h. Delete the PROJECT tuple with Pname = 'ProductX. i. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ʻ123456789' and '2007-10-01', respectively. j. Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = '999887777’ to '943775543' k. Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777’ and Pno = 10 to '5.0.

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

Which relational constraints would be violated if the following operation were applied to the COMPANY database snapshot illustrated bellow?Insert:2. Insert < 'Jasmine', 'Rani', 321, 3/9/1982, 3000, NULL, 30> into EMPLOYEE.3. Insert < 'Jasmine', 'Rani', 923, 3/19/1980, '3000JD', NULL, 30> into EMPLOYEE.4. Insert <123456, 'Rani', 321, 3/9/1985, '3000$', 432, 10> into EMPLOYEE.5. Insert <400,'Planning'> into DEPARTMENT.6. Insert <123456, 'Rani', 123, 3/9/1985, '3500JD', 432, 10> into EMPLOYEE.7. Insert <30,'Planning'> into DEPARTMENT.8. Insert < 'Jasmine', 'Rani',NULL, 30/9/1977, '4000JD', 587, 10> into EMPLOYEE.9. Insert <NULL,'Planning'> into DEPARTMENT.10. Insert < 'Jasmine', 'Rani',222, 3/2/1980, 3000, 587, 50> into EMPLOYEE .11. Insert < 'Alice', 'Rani',222, 30/2/1976, 3000, 687, 10> into EMPLOYEE.12. Insert < 'Jasmine', 'Rani',NULL, 3/22/1976, '1000JD', 587, 50> into EMPLOYEE.13. Insert < 'Sali', 'Rani', 'NULL',…
What happens if an attempt is made to compromise a database's referential integrity? What sorts of blunders are conceivable?
Indexed sequential file. Consider the relation named "student" which is mapped to a file as shown below, which is physically ordered by street address. (Note when there is a mix of numerical numbers and character strings, numerical numbers come first, followed by character strings, which are ordered alphabetically.) Suppose we have an indexed sequential file with a sparse index containing only three index items. Show a possible scenario of index items, along with pointers pointing to appropriate records in the data file. [Put your answer in the space below, by providing index items and pointers.] Index File name: studentss Index item Name Street Mike 30th street Hilary Donald 72nd street 90th street 168th street George A street Dodge street Farnam street Pacific street Joe Barack Al Kamala Explanation (optional):
TASK 1 1. The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENANCE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, cook up a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team. Task: Make a new table to store car maintenance types. Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Make a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the…
In what ways may the referential integrity of a database be broken? In what ways are you most prone to committing these mistakes?
Answer the fowllowing questions :-( Only Handwritten, the name of the answer file is your university number and the exetintion is PDF, the time allawed is 24 hours (1 day) Q1:- 1- Find superkeys, candidate keys, primary keys, alternative keys in the following CAR relation schema? CAR (State, Reg#, SerialNo, Model, Year) 2- Find superkeys, candidate keys, primary keys, alternative keys in the following relation? A1 Hh 101 D10 10 A2 Mm 200 D30 20 АЗ Ff 50 D90 30 A4 LI 60 D53 30 A1 Ff 70 D6 10 Q2:- 1- Which relational constraints would be violated if the following operation were applied to the COMPANY database snapshot illustrated bellow? FName LName SSN BDate Salary SuperSSN DNo Smith Jone 123 1/2/1979 2100JD NULL 10 Susan 432 3/6/1982 1547JD 123 10 James Ford 587 20/9/1980 | 3650JD 983 20 Sali Miller 983 1/12/1970 890JD 123 30 Department DNo DName 10 Headquarter 20 Accounting 30 IT 1. Insert into EMPLOYEE. 2. Insert into DEPARTMENT. 3. Insert into EMPLOYEE. 4. Insert into…
A driving school wants to build a database to manage the theoretical traffic laws tests of its students. After discussing with the manager of the driving school, we have documented the following requirements: Each student is identified by a unique number and is characterized by his last name, first name, an address, and date of birth. Each student may attend several training sessions (as much as he needs). Each session has a unique number and is characterized by a specific date and time. For each training session, the manager chooses a series of questions from a CD. Each CD is identified by a number and is characterized by a publisher name. Each CD consists of 6 series of questions, numbered from 1 to 6 and each series consists of 40 questions. Each question is identified by a number and is characterized by the question text, answer, level of difficulty and a theme. The same question may appear in several series with an order number for each series. For example, question number 35 may…
In the problem below, x and y are local application program values Read( ) and Writel ) fetch and stored the persistent values in a database. The database values are read / written by two different transactions. The time sequence of these operations is shown in the table below. You cannot change the time sequence. Take the initial values of x and y to be x = 4, y = 2. a) In this nart the transactions are operating with isolation level READ UNCOMMITTED (no locks) Time Transaction 1 Transaction 2 Read(y) y=y+5 2 Read(y) y=y+ 10 3 4 Write(y) Read(x) 7 Read(x) 8 X=x+ y: Write(y) Commit 9. 10 11 x=x+y Write(y) 12 13 Commit For the execution order shown above, what are the final values of x and y? Answer: x= b) There is a problem with the interleaved execution plan for these transactions. What is the problem, i.e., which concurrency problem does this demonstrate? Answer: c) Rewrite the sequence of operations using the standard technique for dealing with concurrency problems of this type for…
The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENANCE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team.
Question 1) We plan to construct a database for the FAU parking service shstem. The FAU has many parking-lots which can accommodate many vehicles.  Each parking-lot has id, name, and location. A parking-lot consists of many parking spaces (or spots) and each parking space (or spots) has a unique number.  Every student has School_ID and name. A student can purchase more than one parking-permit.  A vehicle has id, brand, and color. A student can own multiple vehicle.  Each parking permit has no. and price. A parking-permit allows a vehicle to park in dedicated parking spaces (or spots).  The FAU maintains a time table and uses it to record when a student parks a vehicle at a dedicated parking space (or spot) at a specific time-slot in the time- table.  A time-table has id, date, and hour. Draw an ER diagram for this application. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate…
PLZ HELP WITH THE FOLLOWING: We will assume that the Computer Science Department at a university has an agreement with the publishers to allow faculty and students to have free exclusive access to the e-books online through their website. They need you to design a database for them using the following business rules: The faculty and students (we refer to them in our system as clients) can electronically check out any book at any time. A book must have at least one author but can have more authors. An author must have written at least one book or more to be included in the system. A book may have never been checked out, but can be checked out many times by the same client or different clients over time. A client may have never checked out a book or they may have checked out many books over time. To simplify determining which client currently has a given book checked out, a M:N relationship between BOOK and CLIENT should be maintained. The information recorded for each book is book…
107. On a relational database, the kind of constraint which must be specified and does not contain large classes of constraints is considered as a. referential integrity constraint b. null integrity constraint c. semantic integrity constraints d. secondary integrity constraint

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

SEE MORE TEXTBOOKS