PRIYA ADBS Assignment-5
docx
keyboard_arrow_up
School
Southeast Missouri State University *
*We aren’t endorsed by this school
Course
503
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
13
Uploaded by GeneralExploration6683
NAME: PUSHPA PRIYA MIKKILI
SE.ID: S02048083
ADVANCED DATABASE SYSTEMS
ASSIGNMENT-5
Question
1 (10 points)
Suppose each of the following Update operations is applied directly to the following database. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints:
1.
Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
2. Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
3. Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
4. Insert < '677678989', null, '40.0' > into WORKS_ON.
5. Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
6. Delete the WORKS_ON tuples with ESSN= '333445555'.
7. Delete the EMPLOYEE tuple with SSN= '987654321'.
8. Delete the PROJECT tuple with PNAME= 'ProductX'.
9.Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER=5 to '123456789' and '01-OCT-88', respectively.
10. Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to '943775543'.
ANSWERS:
1.No restrictions have been broken, for one.
2. Because Dnum=2 and the DEPARTMENT relation doesn't contain a tuple with Dnum=2, this violates referential integrity. The restriction could be put into effect by: i.
Turning down the new PROJECT tuple adding.
ii.
Insert a fresh DEPARTMENT tuple with the number 2 as the Dnumber.
3. The referential integrity as well as the key constraint are both broken. Due to the existence of a
DEPARTMENT tuple with Dnumber=4, the key constraint is broken. This restriction may be put
into effect by i.
Dismissing the insertion of and ii.
Changes to the Department tuple's Dnumber values.
Since Mgr_ssn='943775543' and EMPLOYEE have various Ssn values, the referential integrity of the data is compromised. In order to impose referential constraints, we may either i.
reject the insertion or ii.
change Mgr_ssn to an already-existing Ssn value in Employee.
iii.
Adding a new employee tuple with the SSN="943775543"
4. Entity integrity is abused since Pno, WORKS_ON's primary key, is null.
i.
Disallowing the implantation.
ii.
In the new WORKS_ON tuple, substituting Pno for the project's present Pnumber.
Due to ESSN='677678989' and the lack of a tuple in the EMPLOYEE relation with Ssn='677678989, referential integrity is broken.
i.
Disallowing the implantation.
ii.
Update the Essn values in EMPLOYEE to a valid Ssn value.
iii.
Adding a new employee tuple with the value "677678989"
5. No restrictions have been broken.
6. No restrictions have been broken.
7. Due to the loss of referential integrity caused by tuples erased from the relations WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE.
i.
Disapproving of the deletion.
ii.
Erasing any tuples having values of Essn, Essn, Mgr_ssn, and Super_ssn='987654321' in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations.
8. Referential integrity is violated because two tuples in WORKS_ON relations are connected to the tuple that was removed from the project. We could impose restrictions by,
i.
Disapproving of the deletion.
ii.
Eliminating the tuples with the Pno=1 value from the WORKS_ON relation.
9. No restrictions have been broken.
10. The Employee relation with the SSN='943775543' and the new value of Super_ssn='943775543' does not contain a tuple, which violates referential integrity.We could enforce the limitation by
i.
Disapproving of the deletion.
ii.
Adding a fresh employee tuple with the value "943775543"
Question
2 (10 points)
Consider the AIRLINE relational database schema shown in the following figure, which describes a database for airline flight information. Each FLIGHT is identified by a flight NUMBER, and consists of one or more FLIGHT_LEGs with LEG_NUMBERs 1, 2, 3, etc. Each leg has scheduled arrival and departure times and airports, and has many LEG_INSTANCEs--one for each DATE on which the flight travels. FARES are kept for each flight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used in the leg, and the actual arrival and departure times and airports. An AIRPLANE is identified by an AIRPLANE_ID, and is of a particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs in which they can land. An AIRPORT is identified by an AIRPORT_CODE. 1.(2 points) Give/write an operation to insert a reservation on a particular flight or flight leg on a given date.
INSERT INTO SEAT_RESERVATION (FLIGHT_NUMBER, LEG_NUMBER, DATE, SEAT_NUMBER, CUSTOMER_NAME, CUSTOMER_PHONE)
VALUES ('QR567', 4, '2023-07-12', '16B', 'Priya', '573-331-9571');
2. (1 point) What constraint is checked for previous question? A foreign key constraint in the FLIGHT_NUMBER and LEG_NUMBER columns of the SEAT_RESERVATION database points to the FLIGHT_LEG table. The flight number and leg number specified in the reservation must be present in the FLIGHT_LEG database in order to comply with this constraint.
3. (2 points) Give/write an operation to delete a particular flight on a given date. DELETE FROM FLIGHT_LEG WHERE FLIGHT_NUMBER = 'QR567' AND DATE = '2023-07-12';
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
4. (1 point) What constraint is checked for previous question? A foreign key constraint in the FLIGHT_NUMBER and LEG_NUMBER columns of the SEAT_RESERVATION database points to the FLIGHT_LEG table. The deletion of any related reservations for a flight leg is prohibited by this restriction. As a result, before removing the flight leg, the database will look to see if there are any reservations for it. There won't be any deletions if there are any reservations.
5. (2 points) Give/write an operation to Modify a flight number.
UPDATE FLIGHT_LEG SET FLIGHT_NUMBER = 'AA356' WHERE FLIGHT_NUMBER = 'QR567';
6. (1 point) What constraint is checked for previous question?
Before updating a flight number, one must consider the following two foreign key restrictions:
The FLIGHT_NUMBER column of the SEAT_RESERVATION database has a foreign key constraint that uses the FLIGHT_LEG table. According to this requirement, the reservation's flight number must be listed in the FLIGHT_LEG database.
The AIRPLANE_TYPE column of the CAN_LAND database contains a foreign key constraint that utilizes the AIRPLANE_TYPE table. This constraint ensures that the airplane type listed in the CAN_LAND column is present in the AIRPLANE_TYPE table.
7. (1 points) Specify all the referential integrity constraints on the figure
The column AIRPLANE_ID of the FLIGHT_LEG database contains a foreign key constraint that uses the data in the AIRPLANE table. The aircraft ID provided for the flight leg must exist in the AIRPLANE table in order for this constraint to be satisfied.
The FLIGHT_LEG table is referred to by a foreign key constraint in the FLIGHT_NUMBER and LEG_NUMBER columns of the SEAT_RESERVATION database. The reservation's flight number and leg number must both be present in the FLIGHT_LEG database in order for this limitation to apply.
The AIRPLANE_TYPE column of the CAN_LAND database contains a foreign key constraint that uses the data in the AIRPLANE_TYPE table. With the use of this constraint, the AIRPLANE_TYPE database will always contain the type of aircraft listed in the CAN_LAND column.
Question 3 (20 points):
Check Appendix A first.
In MySQL and for the following DB
• Create scheme.
• Create the tables: 1) Customer 2) Invoice. For all tables, take a Print Screen of SQL
script
and paste it into the word document. Don’t crop/cut your images. I want to see the
complete screen including the time and date on the right lower corner. If any part of the
image is cut/cropped, you will receive a zero for this question.
1.
CREATE TABLE `test`.`customer` (
`CustomerId` INT NOT NULL,
`Name` VARCHAR(45) NULL,
`EmailAddress` VARCHAR(45) NULL,
`Gender` VARCHAR(45) NULL,
`EmailVerified` INT NULL,
PRIMARY KEY (`CustomerId`));
2.
CREATE TABLE `test`.`invoice` (
`InvoiceId` INT NOT NULL,
`CustomerId` VARCHAR(45) NULL,
`Amount` DECIMAL(20) NULL,
`DateCreated` DATETIME NULL,
PRIMARY KEY (`InvoiceId`));
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
• Insert data into table /fill table with data. For all tables, take a Print Screen of the tables
(with data) and paste it into the word document. Don’t crop/cut your images. I want to
see the complete screen including the time and date on the right lower corner. If any
part of the image is cut/cropped, you will receive a zero for this question.
1.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (1, 'Jack Frost ', 'jfrost@winter.com', 'Male', 1);
2.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (2, 'Miss Piggy', 'queen@muppets', 'Female', 1);
3.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (3, 'Dr.Octopus', 'doc@octopus.net', 'Male', 0);
1.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (1, '1', 80, '2010-12-11 04:19:12');
2.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (2, '2', 24.95, '2011-01-05 16:35:56');
3.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (3, '1', 25, '2011-01-07 20:05:33');
4.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (4, '1', 45, '2011-02-20 08:09:42');
• Delete one row from the table. 1) Show a picture of the table before operation. 2) Show
SQL script. 3) Show a picture of the table after operation
This is before delete operation.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DELETE FROM `test`.`customer` WHERE 'CustomerId' = '1';
This is after delete operation.
• List the ID, name and email of customer information (use SELECT). Take a Print
Screen of
the output new table (with data) and paste it into the word document. Don’t crop/cut
your images. I want to see the complete screen including the time and date on the right
lower corner. If any part of the image is cut/cropped, you will receive a zero for this
question.
SELECT CustomerId,Name,EmailAddress FROM `test`.`customer`;
• Update the email address of Dr. Octopus to doc123@octopus.net. Do the following 1)
Show a picture of the table before operation. 2) Show SQL script. 3) Show a picture of
the table after operation
This is before update operation.
UPDATE `test`.`customer` SET `EmailAddress` = 'doc123@octopus.net' WHERE 'CustomerId' = '3';
This is after update operation.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help