--create tables CREATE TABLE boats ( bid integer, bname color char (20), PRIMARY
ID: 3919418 • Letter: #
Question
--create tables CREATE TABLE boats ( bid integer, bname color char (20), PRIMARY KEY (bid)); char (20), CREATE TABLE sailors sid integer, sname char (30), rating number, age number, PRIMARY KEY (sid)) CREATE TABLE reserves (sid integer, bid integer, day date, PRIMARY KEY (sid,bid, day), FK BID RES FOREIGN KEY (bid) REFERENCES boats (bid), FK SID REs FOREIGN KEY (sid) REFERENCES sailors(sid)); CONSTRAINT CONSTRAINT --insert data into tables --boats INSERT INTO boats VALUES (101, INSERT INTO boats VALUES (102 INSERT INTO boats VALUES (103, 'Clipper', 'green'); INSERT INTO boats VALUES (104, 'Marine 'red commit Interlake', 'blue') 'Interlake', 'red') --sailors INSERT INTO sailors VALUES (22, Dustin', 7, 45.0) INSERT INTO sailors VALUES INSERT INTO sailors VALUES INSERT INTO sailors VALUES (32, ,Andy', 8, .25.5'); INSERT INTO sailors VALUES (58, Rusty', INSERT INTO Sailors VALUES (64, (29, (31, 'Brutus', ,Lubber, 1, 8, ,33.0'); 155.5.); 10, '35.0) Horatio',7, 35.O)Explanation / Answer
a) After step 2 we are going to delete rows from reserves table whose sid is 74. Hence now reserves table do not contains rows with sid 74.
b) commit makes the changes to the database permanently.
c) Two new rows will be inserted into boats table with bids 105 and 106. Now the boats table contains six rows. Select *from boats displays all these rows.
d) At step 9 we used rollback statement. This means earlier insertion of two rows into boats with bids 105,106 will be undone. At step 11 commit is used. So the changes made to the database are permanent. So the answer for d) is boats table with only four rows.
e) Step 13--new row is inserted into sailors with sid 97
Step 14--new row is inserted into boats with bid 107
Step 15--trying to insert a row into boats with bid 107 which is not valid because already a row with bid 107 exists. Hence now as bid is primarykey it cant accept duplicate values.
Step 16-- similar to step 15.Here inserting a new row into sailors with sid 97 will not be succeeded.Because already sailors contain a row with sid 97 which is primarykey.
f) At step 17 rollback is applied which means all the recent uncommited transactions will be undone.
Now here rows in sailors table with sid 97 and rows in boats with bid 107 are no longer available.
g) After step 21 commit is used. Now the sailor table and boats table will be displayed with the recent changes effected permanaently.
h) Here sailors and boats tables contains actual rows that were exist before applying any changes.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.