Hello Experts,
DB Versions: Oracle Database 12.1.0.2.0
I have this question from an interview.
How do you design and develop the database of a library to know the interests of a particular person if he consistently borrows books from the library ?
I said something like:
A draft of DB Design:
I will have 4 tables.
PERSONS, CATEGORY, BOOKS and BORROW
PERSONS will have columns like
PERSON_ID, NAME, SDOR, EDOR
Where
PERSON_ID is the Primary Key
NAME is the name of the person borrowing the books
SDOR is the Start Date of Registration
EDOR is the End Date of Registration
CATEGORY will have columns like
CATEGORY_ID, CATEGORY_NAME
Where
CATEGORY_ID is the Primary Key
CATEGORY_NAME is the name of the category that a book belongs to
BOOKS will have columns like
BOOK_ID, BOOK_NAME, DOA, DOS, AUTHOR, CATEGORY_ID
Where
BOOK_ID is the Primary Key
BOOK_NAME is the name of the book
DOA is the date on which this book is added to the library
DOS is the date on which this book is shredded
AUTHOR is the name of the person who wrote the book
CATEGORY_ID is the Foreign Key that references the Primary Key of CATEGORY Table
BORROW will have columns like
ID, CATEGORY_ID, PERSON_ID, BOOK_ID, BROW_DATE, POSSIBLE_RETURN_DATE, ACTUAL_RETURN_DATE
Where
ID is the Primary Key
CATEGORY_ID is the Foreign Key that references the Primary Key of CATEGORY Table
PERSON_ID is the Foreign Key that references the Primary Key of PERSONS Table
BOOK_ID is the Foreign Key that references the Primary Key of BOOKS Table
BORROW_DATE is the date on which a book is borrowed
POSSIBLE_RETURN_DATE is the date on which this book is supposed to be returned (Say, a 15 days of time after the book is borrowed)
ACTUAL_RETURN_DATE is the date on which the borrower returns the book
(I also told him about the additional details like BORROW_DATE can be defaulted to sysdate. And POSSIBLE_RETURN_DATE can be defaulted to sysdate + 15 and the like...)
And to answer the original question to find out the interests of a person when his name is given. (Let's assume person's name is unique at this point of time)
SQL:
select person_name, listagg (category_name, ',') within group (order by category_name) as his_interests
from
(
select distinct p.person_name, c.category_name
from CATEGORY c, BORROW b, PERSONS p
where p.person_name = 'Ranagal'
and p.person_id = b.person_id
and b.category_id = c.category_id
)
group by person_name
Question:
Did I answer it correctly ? Or how else would I have answered it in a better way ?
Regards,
Ranagal