Consider the below schema for a library database and write relational algebra ex
ID: 3669930 • Letter: C
Question
Consider the below schema for a library database and write relational algebra expressions for the following
1. Author ( authorname, citizenship, birthyear)
2. Book (isbn, title, authorname)
3. Topic (isbn, subject)
4. Branch (libname, city)
5. Instock (isbn, libname, quantity)
1. Give all authors’ names born after 1940. [0.25 Point]
2. Give the names of libraries in Sydney. [0.25 Point]
3. Give the cities where each book is held. [0.25 Point]
4. Give the title of each book on the topic of either alcohol or drugs. [0.25 Point]
5. Give the title and author of each book of which at least two copies are held in a branch located in Melbourne. [0.50 Point]
Explanation / Answer
CREATE TABLE Book (
Book id Int PRIMARY KEY,
Title Varchar(200),
Publisher name Varchar(200),
FOREIGN KEY (Publisher name) REFERENCES Publisher(Name)
ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE Book Authors (
Book id Int NOT NULL,
Author name Varchar(200) NOT NULL,
PRIMARY KEY ( Author name,citizenship, birthyear),
FOREIGN KEY (Book id) REFERENCES Book(Book id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Publisher (
Name Varchar(200) PRIMARY KEY,
Address Varchar(400),
Phone Decimal(20)
);
CREATE TABLE Book Copies (
Book id Int NOT NULL,
Branch id Char(4) NOT NULL,
No of copies Int DEFAULT 1,
PRIMARY KEY (Book id, Branch id),
FOREIGN KEY (Book id) REFERENCES Book(Book id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Branch id) REFERENCES Library Branch(Branch id)
ON DELETE CASCADE ON UPDATE CASCADE
);
relational algebra expressions
(1){t|r(t){author}={authername, citizenship,birthyear}
(2){t|r(t){book},{isbn,subject}
(3){t|r(t){branch },{isbn, libname, quantity}
(4) ){t|r(t) {Instock| R(t)},{ isbn, libname, quantity}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.