Question

Read the following scenario and answer the question below:

The Faculty Commons, a faculty run organization, maintains a library of hard-to-find books on pedagogy and these books are available for faculty to borrow. The Faculty Commons would like to track this loan process including the books, the date a book is loaned out, the date a book is due to be returned, and the actual date the book is returned. One faculty member may borrow many books and one book may be borrowed by many faculty members, over time. The Faculty Commons wants to track the full name, email address, and SID* for each faculty member. The Faculty Commons has contracted you to design a database that meets these requirements.

*A SID is the system identification number issued by the College to all members of the College, including faculty, as a unique identifier. The Faculty Commons is an entity that is part of, but separate from, the College, and thus does not issue and has no control over SIDs.

Question: What tables and fields of each table that needed for the requirements of the scenario?

Answer 1

Following are the tables and the fields required for the scenario.

1. The table FACULTY is storing the details of Faculty. Primary key is SID.

FACULTY(SID VARCHAR(5), FULLNAME VARCHAR(20), EMAIL VARCHAR(20), PRIMARY KEY(SID))

2. The table BOOK is for storing the deatils of each book in the library. Primary key is BOOK_ID.

BOOK(BOOK_ID VARCHAR(15),BOOK_NAME VARCHAR(50),AUTHOR VARCHAR(30),PUBLISHER VARCHAR(50),YEAR_OF_PUBLICATION INT, PRIMARY KEY(BOOK_ID))

3. The table BOOK_LOAN is for storing the details of book issued. Here primary key is a combination of (BOOK_ID,SID, DATE_OF_ISSUE). The reason is a book can be issued to same faculty. So combination of BOOK_ID and SID is not enough. Hence we have taken the combination (BOOK_ID,SID, DATE_OF_ISSUE) for making it unique and the primary key. BOOK_ID is foreign key from BOOK and SID is foreign key from FACULTY.

BOOK_LOAN(BOOK_ID VARCHAR(15),SID VARCHAR(5),DATE_OF_ISSUE DATE,DATE_DUE DATE,DATE_OF_RETURN DATE, PRIMARY KEY(BOOK_ID,SID,DATE_OF_ISSUE), FOREIGN KEY BOOK_ID REFERENCES BOOK(BOOK_ID), FOREIGN KEY SID REFERENCES FACULTY(SID))