Question

Programming Assignment

This is a database course using Open Office Database.

Assume that you have been given the task of creating a system for a library to keep track of their books, the borrowers of the books, and the books that are currently lent out. Your first step will be to create the relations necessary for this system.

Book will need information such as a unique identifier for each book, title, author, ISBN number, date of publication, cost.

Borrower will need information such as a unique identifier for the person such as a library card number, name, address, phone number, date of membership

BooksLended will need information such as the book checked out, the date the book was checked out, when it must be returned, the borrower of the book, etc.

Librarian will need information on the librarians who can perform the check out of a book. This will include a way to identify each librarian, their name, and extension phone number.

Be sure to identify what are candidate, primary, alternate keys and the degree of each relation. It would be recommended that you document your relations in the manner used in the Sharma et al.text.

Assignment Grading Rubric

•Assignment documents the relations required for the library system and includes each of the following:

o Book

o Borrower

o BookLended

o Librarian

•Each relation must define an identifier and its data types that uniquely identifies each tuple

•The degree of each relation must be described

•Each relation must include appropriate information other than identifiers such as attributes and the data type of attributes

o The book relation includes at least title, author, ISDN number, date of publication, and cost along with the data types that are appropriate to the data

o The borrower relation includes at least library card #, name, address, postal code, phone number, and date of membership along with the data types that are appropriate to the data

o The booklended relation includes at least borrower, check out date, return date, book id, librarian

o The librarian relation includes at least librarian id, name, phone number

Answer 1

Please find the Tables for Library Management System:-

CREATE TABLE Books (

ISBN INT NOT NULL ,

book_name VARCHAR( 100 ) NOT NULL ,

book_status CHAR( 1 ) NOT NULL ,

book_type INT NOT NULL,

book_edition INT NOT NULL ,

book_publisher INT NOT NULL ,

PRIMARY KEY ( ISBN )

);

CREATE TABLE Book_Type (

book_type_id INT NOT NULL ,

book_type_name VARCHAR( 50 ) NOT NULL

PRIMARY KEY ( book_type_id )

);

CREATE TABLE Author (

author_id INT NOT NULL,

author_fname VARCHAR ( 50) NOT NULL ,

author_fname VARCHAR ( 50) NOT NULL ,

author_email VARCHAR ( 100) NOT NULL

);

CREATE TABLE Publisher (

publisher_id INT NOT NULL,

publisher_name VARCHAR (100) NOT NULL,

PRIMARY KEY ( publisher_id )

);

CREATE TABLE Book_Author (

book_id INT NOT NULL ,

author_id INT NOT NULL

);

CREATE TABLE Member (

member_id INT NOT NULL ,

member_fname VARCHAR( 50 ) NOT NULL ,

member_lname VARCHAR( 50 ) NOT NULL,

member_phone VARCHAR( 25 ) NOT NULL ,

member_email VARCHAR( 100 ) NOT NULL ,

member_email VARCHAR( 100 ) NOT NULL ,

member_type INT NOT NULL ,

member_address INT NOT NULL ,

PRIMARY KEY (member_id )

);

CREATE TABLE Member_Type (

member_type_id INT NOT NULL ,

member_type_name VARCHAR( 50 ) NOT NULL ,

member_type_allowed_day INT NOT NULL ,

member_type_fine INT NOT NULL ,

PRIMARY KEY (member_type_id)

);

CREATE TABLE Address (

address_id INT NOT NULL ,

address_desc TEXT NOT NULL ,

postcode INT NOT NULL ,

PRIMARY KEY (address_id )

);

CREATE TABLE Postcode (

postcode_id INT NOT NULL,

postcode_description VARCHAR( 10 ) NOT NULL ,

PRIMARY KEY (postcode_id )

);

CREATE TABLE Librarian (

librarian_id INT NOT NULL ,

librarian_fname VARCHAR( 50 ) NOT NULL ,

librarian_lname VARCHAR( 50 ) NOT NULL ,

librarian_address INT NOT NULL ,

librarian_phone VARCHAR( 25 ) ,

librarian_email VARCHAR( 100 ) ,

PRIMARY KEY ( `librarian_id` )

);

CREATE TABLE Borrow (

borrow_id INT NOT NULL ,

member_id INT NOT NULL ,

ISBN INT NOT NULL ,

librarian_id INT NOT NULL ,

borrow_date DATE NOT NULL ,

return_date DATE ,

expected_return_date DATE NOT NULL ,

PRIMARY KEY (borrow_id )

);

CREATE TABLE Fine (

fine_id INT NOT NULL ,

borrow_id INT NOT NULL ,

librarian_id INT NOT NULL ,

fine_amount FLOAT NOT NULL

fine_paid CHAR( 1 ) NULL ,

PRIMARY KEY ( `fine_id` ),

INDEX (borrow_id)

);

ALTER TABLE Books ADD FOREIGN KEY (book_type) REFERENCES Book_Type (book_type_id) ;

ALTER TABLE Books ADD FOREIGN KEY ( `book_publisher` ) REFERENCES Publisher( publisher_id ) ;

ALTER TABLE Book_Author ADD FOREIGN KEY ( ISBN ) REFERENCES books( ISBN ) ;

ALTER TABLE Book _ Author ADD FOREIGN KEY ( author_id ) REFERENCES Author( author_id ) ;

ALTER TABLE Member ADD FOREIGN KEY ( member_address ) REFERENCES Address( address_id ) ;

ALTER TABLE Member ADD FOREIGN KEY ( member_type) REFERENCES Member_Type( member_type_id ) ;

ALTER TABLE Librarian ADD FOREIGN KEY ( librarian_address ) REFERENCES Address( address_id ) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( member_id ) REFERENCES Member( member_id ) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( ISBN ) REFERENCES books(ISBN) ;

ALTER TABLE Borrow ADD FOREIGN KEY ( librarian_id) REFERENCES Librarian( librarian_id );

ALTER TABLE fine ADD FOREIGN KEY ( borrow_id) REFERENCES Borrow( borrow_id );

ALTER TABLE fine ADD FOREIGN KEY ( librarian_id ) REFERENCES Librarian( librarian_id );

ALTER TABLE address ADD FOREIGN KEY ( postcode ) REFERENCES Postcode( postcode_id);

Please let me know in case of any clarifications required. Thanks!