Consider the following database schema and state for a property rental database.
ID: 639605 • Letter: C
Question
Consider the following database schema and state for a property rental database. The schema of each relation is given below:
PropertyForRent (propertyNo: VARCHAR(8), Street: VARCHAR(20), City: VARCHAR(20))
PRIMARY KEY(propertyNo)
Client (clientNo: VARCHAR(8), fName: VARCHAR(10), lName: VARCHAR(10))
PRIMARY KEY(clientNo)
Branch (branchNo: VARCHAR(8), Street: VARCHAR(20), City: VARCHAR(20), postCode: VARCHAR(12))
PRIMARY KEY(branchNo)
Viewing (propertyNo: VARCHAR(8), clientNo: VARCHAR(8), viewDate: DATE, Comment: VARCHAR(30))
PRIMARY KEY(propertyNo, clientNo, viewDate)
FOREIGN KEY(propertyNo) REFERENCES PropertyForRent(propertyNo)
FOREIGN KEY(clientNo) REFERENCES Client(clientNo)
Staff (staff No: VARCHAR(8), fName: VARCHAR(10), lName: VARCHAR(10), position: CHAR(12), sex: CHAR, DoB: DATE, salary: INT, branchNo: VARCHAR(8))
PRIMARY KEY(staff No)
FOREIGN KEY(branchNo) REFERENCES Branch(branchNo)
>>>>>> http://imgur.com/Oe7ahjo <<<<<<<<<<<<
(a) For each of the following operations, state whether they violate the integrity constraints of the database. If they do, list ALL the constraints violated by the operation (domain constraint, key constraint, referential integrity, or entity integrity). Otherwise, state that no constraint is violated.
i. Inserting the tuple (`PG16', `2 Dover St', `London') into PropertyForRent relation.
ii. Inserting the tuple (1221, NULL, `London') into PropertyForRent relation.
iii. Inserting the tuple (`CR22', `John', 'Kay') into Client relation.
iv. Inserting the tuple (`PA14', `CR62', NULL, NULL) into Viewing relation.
v. Inserting the tuple (`SR4', `John', `Kay', `Sta ', `M', `4-Dec-71', 40000, NULL) into Sta relation.
vi. Deleting the tuple (`PA14', `16 Holhead', `Aberdeen') from PropertyForRent relation.
vii. Deleting the tuple (`PG4', `CR56', `26-May-01', null) from Viewing relation.
viii. Updating the propertyNo attribute for PL94 in PropertyForRent relation to PG22.
ix. Updating the branchNo for the tuple (`SL41', `Julie', `Lee', `Assistant', `F', '13-Jun-65', 9000, B005) in Sta relation to `B006'.
(b) Consider the following constraint on the Viewing relation:
FOREIGN KEY(propertyNo) REFERENCES PropertyForRent(propertyNo)
ON DELETE CASCADE
FOREIGN KEY(clientNo) REFERENCES Client(clientNo)
ON DELETE SET DEFAULT
i. Explain if any corrective action will be taken by the DBMS when the tuple (`PG4', `CR56', `26-May-01', null) is deleted from Viewing relation. If yes, what action will be taken?
ii. Explain if any corrective action will be taken by the DBMS when the tuple (`PA14', `16 Holhead', `Aberdeen') is deleted from PropertyForRent relation. If yes, what action will be taken?
iii. Explain if any corrective action will be taken by the DBMS when the tuple (`CR76', `John', `Kay') is deleted from Client relation. If yes, what action will be taken?
Explanation / Answer
i. Inserting the tuple (`PG16', `2 Dover St', `London') into PropertyForRent relation.
Ans : Unique constraint error as PG16 is already present in PropertyNo column in PropertyForRent relation.
PropertyNo is primary key in PropertyForRent relation.
ii. Inserting the tuple (1221, NULL, `London') into PropertyForRent relation.
Ans:Error as you are inserting numeric data in VARCHAR PropertyNo column of PropertyForRent relation.
iii. Inserting the tuple (`CR22', `John', 'Kay') into Client relation.
Ans:No error. Since first column will be stored as string and is unique. Other columns doesn't have any NOT NULL constraints.
iv. Inserting the tuple (`PA14', `CR62', NULL, NULL) into Viewing relation.
Ans: Column viewDate can't be NULL as its part of PRIMARY KEY column.
v. Inserting the tuple (`SR4', `John', `Kay', `Sta ', `M', `4-Dec-71', 40000, NULL) into Sta relation.
Ans: No Error. As the primary key column is unique and referenced column branchNo can be NULL.
vi. Deleting the tuple (`PA14', `16 Holhead', `Aberdeen') from PropertyForRent relation.
Ans: Error! Referential Integrity constraint error as PA14 rows are referenced in Viewing table.
vii. Deleting the tuple (`PG4', `CR56', `26-May-01', null) from Viewing relation.
Ans: No Error. Can be deleted without any errors.
viii. Updating the propertyNo attribute for PL94 in PropertyForRent relation to PG22.
Ans: No Error. The rows can be updated as PL94 isn't being referenced in any of the child tables viewing.
ix. Updating the branchNo for the tuple (`SL41', `Julie', `Lee', `Assistant', `F', '13-Jun-65', 9000, B005) in Sta relation to `B006'.
Ans: Update error with referential integrity constraint on branchNo column as B006 entry is not present in Branch relation.
(b) Consider the following constraint on the Viewing relation:
FOREIGN KEY(propertyNo) REFERENCES PropertyForRent(propertyNo)
ON DELETE CASCADE
FOREIGN KEY(clientNo) REFERENCES Client(clientNo)
ON DELETE SET DEFAULT
i. Explain if any corrective action will be taken by the DBMS when the tuple (`PG4', `CR56', `26-May-01', null) is deleted from Viewing relation. If yes, what action will be taken?
Ans:
a) When the delete is done on viewing table then delete operation will be same as pervious
b) when the parent table PropertyForRent any rows are deleted then all of the referenced rows in viewing table will be deleted
c) when the parent table Client any rows are deleted then all of the referenced rows in viewing table will be set to default values but here viewing table doesn't have default value set for clientNo column therefore it will fail.
ii. Explain if any corrective action will be taken by the DBMS when the tuple (`PA14', `16 Holhead', `Aberdeen') is deleted from PropertyForRent relation. If yes, what action will be taken?
Ans: When the parent table PropertyForRent PA14 row is deleted then all of the referenced rows with PA14 in VIEWING table are deleted. i.e. first 2 rows of your viewing table in image screenshot.
iii. Explain if any corrective action will be taken by the DBMS when the tuple (`CR76', `John', `Kay') is deleted from Client relation. If yes, what action will be taken?
Ans : When the parent table child CR76 row is deleted then all of the referenced rows with CR76 should be set to default but since there are no defaults defined in this table this would throw an ERROR.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.