CREATE TABLE Patrons ( ID INTEGER, name CHAR(40), numberBorrowed INTEGER, PRIMAR

Question

CREATE TABLE Patrons (
ID INTEGER,
name CHAR(40),
numberBorrowed INTEGER,
PRIMARY KEY (ID)
);

CREATE TABLE Shelves (
shelfID INTEGER,
capacity INTEGER,
PRIMARY KEY (shelfID)
);

CREATE TABLE Books (
isbn CHAR(10),
title VARCHAR,
author VARCHAR,
currentStatus CHAR(10),
borrowerID INTEGER,
shelfID INTEGER,
CHECK (currentStatus IN ('on-shelf' , 'on-loan')),
PRIMARY KEY (isbn) ,
FOREIGN KEY (borrowerID) REFERENCES Patrons (ID),
FOREIGN KEY (shelfID) REFERENCES Shelves (shelfID),
);

includes all necessary integrity constraints. In particular ensure that if a shelf can only get deleted if there are no more books on it (i.e. books do not just disappear), and if a patron gets removed from the data set that the corresponding attribute is set to NULL.

Explanation / Answer

To ensure that if a shelf can only get deleted if there are no more books on it (i.e., books do not just disappear) we need not specify any integrity constraint, why because the default integrity constraint with respect to the FOREIGN KEY(shelfID) is ON DELETE NO ACTION(you can also specify the same which makes no difference), which means the deletion of a tuple from table Shelves will be rejected if there are some tuples in table Books which refer to that shelfid.

If you want to set the corresponding attribute(borrowerid) in Books to NULL if the Patron id gets removed from the dataset, you should specify the integrity constraint ON DELETE SET NULL for the FOREIGN KEY(borrowerID) in table Books.

Leaving other tables intact, the Books table creation should be modified as below:
CREATE TABLE Books (
isbn CHAR(10),
title VARCHAR,
author VARCHAR,
currentStatus CHAR(10),
borrowerID INTEGER,
shelfID INTEGER,
CHECK (currentStatus IN ('on-shelf' , 'on-loan')),
PRIMARY KEY (isbn) ,
FOREIGN KEY (borrowerID) REFERENCES Patrons (ID) ON DELETE SET NULL,
FOREIGN KEY (shelfID) REFERENCES Shelves (shelfID) ON DELETE NO ACTION,
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.