Assignment 3 Due on April 24, 2018 (5:00 pm) For the following questions, use th
ID: 3720109 • Letter: A
Question
Assignment 3 Due on April 24, 2018 (5:00 pm) For the following questions, use the relational schema defined as follow. (ISBN, title, author, edition, year) BookCopy copyNo, ISBN, available) Borrower(borrowerNo, borrowerName, borrowerAddress) BookLoan copyNo, dateOut, dateDue, borrowerNo) contains details of book titles in the library and the ISBN is the primary key contains details of the individual copies of books in the library and copyNo is the primary key. ISBN is a foreign key identifying the book title contains details of library members who can borrow books and borrowerNo is the primary key contains details of the book copies that are borrowed by library members and copyNo/dateOut forms the primary key. borrowerNo is a foreign key identifying the borrower where Book BookCopy Borrower BookLoan 1. Create the Library schema and all corresponding tables in the schema. Include statements to drop the schema if they already exist. Include any constrains or default value that you think are necessary 2. For each table you just created, add 5 rows to that 3. Write an ALTER TABLE statement that adds one new column that provides phone number of the borrower to the Borrower table you already created. 4. Write an ALTER TABLE statement that modifies the Book table you already created so that the book title in each row has to be unique 5. What happen when you try to insert a row with a duplicate book title to the Book table?Explanation / Answer
ABSWER
1. Create the Library schema and all matching tables in the schema. Include statement to drop the schema if they already exist. comprise any constrains or default value that you think are necessary.
Drop table Book;
Drop table BookCopy;
Drop table Borrower;
Drop table BookLoan;
Create table Book (
ISBN varchar(20) NOT NULL PRIMARY KEY,
Title varchar(20),
Author varchar(20),
Edition int
Year int
)
Create table BookCopy (
CopyNo int NOT NULL PRIMARY KEY,
ISBN varchar(20),
Available varchar(1),
FOREIGN KEY (ISBN) REFERENCES Book (ISBN)
)
Create table Borrower (
BorrowerNo int NOT NULL PRIMARY KEY,
BorrowerName varchar(20),
BorrowerAddress varchar(20),
)
Create table BookLoan (
CopyNo int NOT NULL PRIMARY KEY,
dateOut date,
dateDue date,
BorrowerNo varchar(20),
FOREIGN KEY (BorrowerNo) REFERENCES Borrower (BorrowerNo)
)
2. For each bench you just shaped, add 5 rows to that.
Insert into Book values (“AUTH1234”, “The Myth”,”Radison Blu”,2,2016)
Insert into Book values (“AUTH2345”, “How to train your dragon”,”Chung Lee”,1,2015)
Insert into Book values (“AUTH3456”, “Harry potter and the prizinor of Azkaban”,”J.K Rowling”,1,2015)
Insert into Book values (“AUTH4567”, “Born wild”,”Ruskin Bond”,2,2013)
Insert into Book values (“AUTH5678”, “The Myth”,”Radison Blu”,2,2016)
Insert into BookCopy values(235,” AUTH1234”,Y)
Insert into BookCopy values(123, “AUTH2345”, Y)
Insert into BookCopy values(898, “AUTH3456”, Y)
Insert into BookCopy values(12, “AUTH4567”, N)
Insert into BookCopy values(5, “AUTH5678”, N)
Insert into Borrower values(1, “Jhon Smith”, Colins Street”)
Insert into Borrower values(2, “Thomas Riedel”,”Pattinson sqaure”)
Insert into Borrower values(3, “Sabine Gompel”, “Eiendhoven”)
Insert into Borrower values(4, “Eric van”, “Times Square”)
Insert into Borrower values(5, “Kenan Lee”, “MG Rad”)
Insert into BookLoan values(235,01-01-2017, 01-02-2017,1)
Insert into BookLoan values(123,20-11-2017, 20-12-2017,2)
Insert into BookLoan values(898,03-05-2017, 03-06-2017,3)
Insert into BookLoan values(12,30-10-2017, 30-11-2017,4)
Insert into BookLoan values(5,05-02-2016, 05-02-2016,5)
3. Write an ALTER TABLE declaration that adds one new column that provide phone number of the borrower to the
Borrower bench you already created.
Alter table Borrower ADD phone_number figure (10,0);
4. Write an ALTER TABLE declaration that modifies the Book table you already shaped so that the book title in each row has to be sole.
Alter bench Book ADD UNIQUE(title);
5. What occur when you try to insert a row with a duplicate volume title to the Book table?
The database will prompt price already exists, and will not let the line to be insert to the table.