Managing Library Data: E-R Diagram, Schema, and SQL Implementation Essay

Following is a data description of a library:

The library provides books to borrowers. Each book is described by title, edition, and year of publication, and is uniquely identified using the ISBN. Each borrower is described by his or her name and address and is uniquely identified using a borrower number. The library provides one or more copies of each book and each copy is uniquely identified using a copy number, status indicating if the book is available for loan, and the allowable loan period for a given copy. A borrower may loan one or many books, and the date each book is loaned out and is returned is

recorded. Loan number uniquely identifies each book loan.

Design a E-R diagram represent the data used by the library.

Write down the schema of the library database:

Make sure you do:

(1) Identify all relations of the library database. (relations are tables; relation can be an entity (a rectangle) or a relationship (a diamond))

(2) Identify the attributes of each relation.  (table columns)

(3) Identify the primary key of each relation. (underline)

(4) Identify the foreign key of each relation if any. (arrows to primary keys) 

Implement the database using mySQL server. (Use company sql files as  templates).

Once the database is implemented (mySQL) create three queries, report the English equivalent of the queries, and report the queries result.

You would need to submit:

1. E-R diagram (save and submit in .erdplus format)

2. Textual  .sql files including

a) database schema (library-schema.sql)

b) data (library-data.sql) , and

c) the three queries with English description