Answer below are possible choices in general if it is

Info icon This preview shows pages 3–5. Sign up to view the full content.

View Full Document Right Arrow Icon
Answer: 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)
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
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) 8.10 Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema of Figure 76.12. Use the referential action chosen in Exercise 8.9. Answer: One possible set of CREATE TABLE statements is given below: 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),
Image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern