Walk-throughs, no answers required please review the exercise and email in for assistance.
Table Creates:
--One possible set of CREATE TABLE statements is given below:
CREATE TABLE LIBRARY_BRANCH (
BranchId INTEGER NOT NULL,
BranchName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
PRIMARY KEY (BranchId)
);
CREATE TABLE BORROWER (
CardNo INTEGER NOT NULL,
Name VARCHAR(30) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (CardNo)
);
CREATE TABLE PUBLISHER (
Name VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (Name)
);
CREATE TABLE BOOK (
BookId CHAR(20) NOT NULL,
Title VARCHAR(30) NOT NULL,
PublisherName VARCHAR(20),
PRIMARY KEY (BookId),
FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (Name)
ON UPDATE CASCADE
);
CREATE TABLE BOOK_AUTHORS (
BookId CHAR(20) NOT NULL,
AuthorName VARCHAR(30) NOT NULL,
PRIMARY KEY (BookId, AuthorName),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE BOOK_COPIES (
BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
No_Of_Copies INTEGER NOT NULL,
PRIMARY KEY (BookId, BranchId),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE BOOK_LOANS (
CardNo INTEGER NOT NULL,
BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
DateOut DATE NOT NULL,
DueDate DATE NOT NULL,
PRIMARY KEY (CardNo, BookId, BranchId),
FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE
);
Explanation of referential triggered operations
Below are possible choices. In general, if it is not clear which action to choose, REJECT should be chosen, since it will not permit automatic changes to happen (by update propagation) that may be unintended.
BOOK_AUTHORS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (since this corresponds to a multi-valued attribute of BOOK (see the solution to Exercise 6.27); hence, if a BOOK is deleted, or the value of its BookId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_AUTHORS tuples)
BOOK.(PublisherName) --> PUBLISHER.(Name)
REJECT on DELETE (we should not delete a PUBLISHER tuple which has existing BOOK tuples that reference the PUBLISHER) CASCADE on UPDATE (if a PUBLISHER's Name is updated, the change should be propagated automatically to all referencing BOOK tuples)
BOOK_LOANS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE)
BOOK_COPIES.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_COPIES tuples)
BOOK_LOANS.(CardNo) --> BORROWER.(CardNo)
CASCADE on both DELETE or UPDATE (if a BORROWER tuple is deleted, or the value of its CardNo is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE, with the idea that if a BORROWER is deleted, it is necessary first to make a printout of all BOOK_LOANS outstanding before deleting the BORROWER; in this case, the tuples in BOOK_LOANS that reference the BORROWER being deleted would first be explicitly deleted after making the printout, and before the BORROWER is deleted)
BOOK_COPIES.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of its BranchId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_COPIES tuples) (Note: One could also choose REJECT on DELETE)
BOOK_LOANS.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of its BranchId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE)
-- **a.** Retrieve the names of all employees in
-- department 5 who work more that 10 hours
-- per week on the ProductX project.
SELECT fname, lname, pname, dno, hours
FROM employee, works_on, project, department
WHERE employee.ssn = works_on.essn AND
works_on.pno = project.pnumber AND
department.dnumber = project.dnum AND
department.dnumber = 5 AND
works_on.hours > 10 AND
project.pname='ProductX';
-- **b.** List the names of all employees who have a
-- dependent with the same first name as themselves.
SELECT fname, lname, dependent_name
FROM employee, dependent
WHERE dependent.essn = employee.ssn AND
employee.fname = dependent.dependent_name
-- **c.** Find the names of all employees that are
-- directly supervised by 'Franklin Wong'.
SELECT e.fname, e.lname, s.fname,s.lname
FROM employee AS e, employee AS s
WHERE e.superssn = s.ssn AND
s.fname = 'Franklin' AND
s.lname = 'Wong'