Q.11 Consider the following data requirements of a library database for keeping

Question

Q.11 Consider the following data requirements of a library database for keeping track of library members' borrowing books from a few library branches The data requirements of the library application are summarized as follows BOOK entity is identified by Book|d. it has title, publisher name, and multiple author · names PUBLISHER entity consists of Name, Address, and Phone attributes. Name is the key for the PUBLISHER. Note: Phone number is unique LIBRARY BRANCH entity has Branchld as a key and other attributes such as Library Banch Name, Branch Address, Branch Phone Number. Note: Phone number is unique BORROWER (or Library Member) entity has MemberCID as key and additionally has name, address, phone attributes Each LIBRARY BRANCH has one or more copies of the same book. In such a case, noOfCopies attribute needs to be maintained by the relationship. A book is published by only one publisher. A book can be loaned to a library member at a specific library branch When a library member checks out a book, checking out date and time and due date and time must be stored in the database

Explanation / Answer

Question 4)

Part 1- DDL statement of the given database scenario is given below. The scenario is converted into 8 tables. Each table has a primary key and associated foreign key.

CREATE TABLE Author

(

AuthId INT,

AuthName VARCHAR(50),

AuthCountry VARCHAR(15),

PRIMARY KEY (AuthId)

);

CREATE TABLE Publisher

(

Name VARCHAR(50),

Address VARCHAR(50),

Phone VARCHAR(15),

PRIMARY KEY (Name)

);

CREATE TABLE Book

(

BookId INT,

title VARCHAR(255),

publisher VARCHAR(50),

PRIMARY KEY (BookId),

FOREIGN KEY (puvlisher) REFERENCES Publisher (Name)

);

CREATE TABLE BookAuth

(

BookId INT,

AuthId INT,

PRIMARY KEY (BookId, AuthId),

FOREIGN KEY (BookId) REFERENCES Book (BookId),

FOREIGN KEY (AuthId) REFERENCES Author (AuthId)

);

CREATE TABLE Library_Branch

(

BranchId INT,

BranchName VARCHAR(50),

BranchAdd VARCHAR(50),

BranchPhone VARCHAR(15) UNIQUE,

PRIMARY KEY (BranchId)

);

CREATE TABLE Borrower

(

MemberId INT,

MemName VARCHAR(50),

MemAddress VARCHAR(50),

MemPhone VARCHAR(15),

PRIMARY KEY (MemberId)

);

CREATE TABLE Library_Book

(

BranchId INT,

BookId INT,

BookCopies INT,

PRIMARY KEY (BranchId, BookId),

FOREIGN KEY (BranchId) REFERENCES Library_Branch (BranchId),

FOREIGN KEY (Book) REFERENCES Book (BookId)

);

CREATE TABLE Loan

(

BranchId INT,

BookId INT,

MemberId INT,

checkInDate DATE,

DueDateTime DATE,

PRIMARY KEY (BranchId, BookId, MemberId),

FOREIGN KEY (BranchId) REFERENCES Library_Branch (BranchId),

FOREIGN KEY (Book) REFERENCES Book (BookId),

FOREIGN KEY (MemberId) REFERENCES Borrower (MemberId)

);

Part II- This part contains insert statement for inserting values to the created tables.

// Inserting values to the created table

INSERT INTO Author VALUES (1, 'Jack Tan', 'Singapore');

INSERT INTO Author VALUES (2, 'Glenn Yuhico', 'Sydney');

INSERT INTO Publisher VALUES ('Chegg US', 'High Street', '9898989898');

INSERT INTO Book VALUES (1, 'The Life', 'Chegg US');

INSERT INTO BookAuth VALUES (1,1);

INSERT INTO BookAuth VALUES (1,2);

INSERT INTO Library_Branch VALUES (1, 'Star Gems', 'Park Road', '8769876234');

INSERT INTO Borrower VALUES (1, 'Jones james', 'MG Road', '6767676767');

INSERT INTO Library_Book VALUES (1, 1, 20);

INSERT INTO Loan VALUES (1, 1, 1, '2018-03-13 15:56:32', '2018-03-23 15:56:33');

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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