What is an elegant solution to the following database design problem?

Suppose we have the following scheme for our library database (assuming some simplifications). Old loan data are preserved. For every book, we only have one occurrence.





Book(bno, title, author, publisher)


Reader(rno, name, address, city)


Loan(bno, rno, loan-date, return-date)





This scheme of our library database does not support the possibility to have several occurrences of the same book. Extend the scheme to do so, using SQL/DDL.





Please also add some detail why the solution you porposed is elegant.|||Michael B's solution would introduce a common problem in designing relational databases - duplicating data. You would have multiple rows regarding the same book with its details - title, author and publisher. This would lead to database denormalization and possibly loosing data integrity.





Let's assume that bno is PK for Book table. It is therefore unique. It could probably be a natural key, such as ISBN. A suggested solution would be to introduce a new table into the schema which would link books with their details (let's name the new table Books):





Books(BookID, bno)


where BookID is PK and bno is FK to Book table, bno column.





Then, assuming you have:





Book


978-0684833637, 'A Moveable Feast', 'Ernest Hemingway', 'Whatever'


978-0684822761, 'In Our Time', 'Ernest Hemingway', 'Whatever'


...





Books


1, 978-0684833637


2, 978-0684833637


3, 978-0684833637


4, 978-0684822761


5, 978-0684822761


...





Each row in Books table would represent a single book in library's set.





The bno key in Loan table would then have to be changed to reference BookID in Books table.|||The new fastest database solution have also launched...





it shows the search results just within 0.1-0.5 seconds, regardless of the number of rows in the huge tables? In addition, one server can handle nearly 1200 tasks at the same time, which is a unique feature of this database.





Read more at web

Report Abuse

|||The elegant solution is to NOT have several occurrences of the same book. Databases should be normalized, and having several occurrences of the same item is a violation of normalization.





If you mean to have several copies of the same title, that's not several entries for the same book. (It's why software designers have to communicate with the people they write the software for.)





Just add a "copy number" field, so you can have booka/copy1 and booka/copy2 both in the database with no duplication and no violation of normalization.|||You could extend the book object to include a Title-ID. I'm assuming BNO is currently unique and therefore represents the physical book. Title-ID would extend that to a logical value for each particular title. So the schema entries would become something like:





BNO Title-ID Title Author Publisher


1 1 Frogs Me xxx


2 1 Frogs Me xxx


3 2 Cats etc





The Loan details would remain the same - though clearly they could be extended to have other details - but the elegance of the solution is that no other aspects of the schema would need to change and you have allowed the library to hold multiple copies of each book title.|||I think you need another entity, e.g.





bookcopy(id, bno)





where there combination of id and bno is the unique key.