Design a schema for a library system containing the following data.
i. the name, unique Id and number of books on loan for each patron
ii. the unique isbn number, title, author (each book has a single author), current status
(possible values are on-shelf or on-loan), borrower Id (if book is on-loan), and shelf-Id of each book
iii. the unique shelf-Id and capacity (number of books) of each shelf Show all primary and foreign keys.
CREATE TABLE Patrons (
Id INTEGER,
Name CHAR(20),
NumBorrowed INTEGER,
PRIMARY KEY (Id) )
CREATE TABLE Books (
Isbn CHAR(20),
Title CHAR(40),
Author CHAR(20),
Status CHAR(5),
BorrowerId INTEGER,
ShelfId INTEGER,
CHECK (Status IN ('on-loan', 'on-shelf') )
FOREIGN KEY (ShelfId)
REFERENCES Shelves (ShelfId)
FOREIGN KEY (BorrowerId)
REFERENCES Patrons (Id) )
CREATE TABLE Shelves (
ShelfId INTEGER,
Capacity INTEGER
PRIMARY KEY (ShelfId) )
You might also like to view...
You can use the Charts group on the _____ tab to chart or graph data in your worksheet.
A. REVIEW B. PAGE LAYOUT C. INSERT D. HOME
^ is the symbol for ________
Fill in the blank(s) with correct word
________ laws protect the public, human life, or private property
Fill in the blank(s) with correct word
What syntax is used to link key/value pairs in search strings?
A. action+purchase B. action=purchase C. action | purchase D. action equal purchase