A university library database records information about books; for each book, it

Question

A university library database records information about books; for each book, it records the book isbn number

(which is unique), and the book name. In addition, it records which books have been checked out : the

information it keeps for the books which have been checked out is the student id of the student who checked it

out, and the date on which the book was checked out. Some books may not be checked out. You can assume

that there is only one copy of each book.

Each of the following situations concerns how we capture the information in dierent situations.

Situation 1:

For each book, we only keep track of the student who has currently checked out the book i.e. we don't keep

track of who checked out the book in the past.

Situation 2:

For each book, we keep track of the student who has currently checked out the book and we also keep track

of who checked out the book in the past. Here you can assume that no student ever checks out the same

book more than once.

Situation 3:

For each book, we keep track of the student who has currently checked out the book and we also keep track

of who checked out the book in the past. Here it is possible that the same student has checked out the

same book more than once; for example, once on June 4, 2013 and another time on July 19, 2013. You can

assume that no student can check out the same book twice on the same day.

Explanation / Answer

Situation 1:

It can be handled by two tables.

//this table will keep track of all books

isbn will work as primary key

Table 1

ISBN BookName

//this table will keep track of all books which are currently checked out of the system.

Table 2

Stu_Id ISBN IssueDate

//here StuId and ISBN both are primary key together

Situation 2:

case 1:

here i am assuming that we are keeping track of only last student who checked out the book not all the students who checked out that particular book.

Table 1:

ISBN BookName LastStu_ID

//here LastStu_ID will keep track who issued this book last time

//primary key is ISBN

Table 2

Stu_Id ISBN IssueDate

//ame as in previous table

Case 2:

If you want to keep track all student who checked out that particular book and assume as says "no student ever checks out the same

book more than once.".

Then use three tables

Table 1 :

ISBN BookName

Table 2 ://keep record of history. Who had checked out and returned the book in the past. here both are primary key

ISBN STU_ID

Table 3:///this table will keep track of all books which are currently checked out of the system.//here StuId and ISBN both are primary key together

Stu_Id ISBN IssueDate

Situation 3:

Table 1

ISBN BookName

Table 2:////this table will keep track of all books which are currently checked out of the system.

Stu_Id ISBN IssueDate

//table 3 will keep track of all books which checked out previously and returned by student

//here all hthree attribute will create Primarykey

Table 3:

Stu_ID ISBN CheckedOutDate

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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