DBMS Course Project

Project report

The DBMS Course Project report must contain the following items

1. Introduction

• Give a description of the application and the database system

     2. ER data model design

• Design the ER data model of the database in detail. o List the entities and their attributes.

o  Specify the domain of each attribute.

o Specify the properties of each attribute (i.e., key, composite/simple, single-valued/multi-valued, derived, incomplete with different nulls, roles, weak/strong entity type, etc.).

o  Specify the relationships and their attributes.

o List the properties of each relationship (i.e., degree of the relationship, cardinality ratios (1-1, 1-N, N-M), participation constraints (total, partial), other application-specific constraints, etc.).

• Draw the E-R diagram, and incorporate everything discussed above into the E-R diagram.

3. Logical design of the database

• Map the ER diagram into a relational model following the ER to Relational model algorithm.

o  Each entity should map to a (entity) relation.

o Each relationship should be accounted for either represented in an entity relation or as a separate relation

o Each entity/relationship attribute should be accounted for in the transformation.

o List for each relation the primary/candidate keys, foreign keys. o Specify the entity and referential integrity constraints

4. Relational Database Design

• Perform normalization: if the relations are not in BCNF or 3NF, apply the algorithms to decompose and make them BCNF/3NF.

o Please note that your decompositions should be lossless and dependency preserving.

5. Implementation of Database and SQL Query

• A description of the creation of the database schema and instance

• Provide the SQL statements that create your tables and populate your tables.

• Provide the SQL statements that query the database

6. Application design

• A description of the application programs and all tasks.

• Provide source code

7. User guide
8. Window snapshots of the use of the program for each function

Project presentation: You will give the demo of the system in the class

Grading:

• Project report: 35%

• Design: 30%

• Implementation: 35%

Project Description:

This DBMS course project provides you with an opportunity to have some experience in programming with a real database system. In the project, you will create a database with large volume of data using a DBMS of your choice, and write SQL programs to populate, manipulate and query the database, and create a user interface for the application.

Consider the operations of a library system in the different neighborhoods of a city. The library has many readers who borrow books from one of its many branches. The following are descriptions for a simplified library system.

1. The library records information about books that are available in its system. Each book is identified by a unique number (BookId.) It also has a title, author, an ISBN, a publisher, and a publication date.
2. Each book has a single publisher and the publisher address is also recorded.
3. Information about the authors of books is maintained. An author is identified by a number () The name of each author is also recorded.
4. The library system contains several branches, which are identified by a number (LibId). We also need to store the name and the location of each branch. Each branch of the library holds a number of copies of a particular book. Each copy of the same book kept by the same library branch is numbered from 1 to n. The total number of copies of each book in the library is needed.
5. The library system keeps track of all readers who are uniquely identified by ReaderId. Each reader has a name, an address, and a phone number. A reader has to be registered in the database before borrowing a document.
6. Readers have access to the online catalogue of books and may reserve books by title if they are available. A reserved book has to be picked up before 6 pm; otherwise, the reservation is cancelled. A reader cannot borrow or reserve more than 10 books.

7. Borrowing is defined as taking out a copy of a book on one date and time (BDateTime) and returning it a maximum of 20 days later. RDateTime is the date and time on which the copy of the borrowed book is actually returned. (RDateTime is NULL if the document has not yet been returned). Books have to be returned to the branch from which they are borrowed.

8. The same copy of a book can be reserved and/or borrowed by the same reader several times.
9. Books that are not returned on time are fined at a rate of 20 cents for each day after the due date.
10. A copy of a book cannot be lent to more than one reader at a time, but a reader can borrow multiple copies of books.

Your task is to design the database and application programs that will help manage the book inventory and the day-to-day processing. You can make further assumptions but: (a) they should not be in contradiction with the assumptions described above, and (b) they have to be clearly stated in your report. Note that many functions are left out in order to reduce the size and the complexity of the project.

You are to develop a menu driven application system for the public library system database. The following are the menus to be developed. All applications described below must be implemented. If you want, you may add more functions and menus.

1) Main menu:

Reader functions (ask for card number, and then show sub-menu). Administrative Functions (ask for ID and password, and then show submenu). Quit.

2) Reader Functions Menu:

Search a book by ID, title, or publisher name. Book checkout

Book return Book reserve

Compute fine for a book copy borrowed by a reader based on the current date. Print the list of book reserved by a reader and their status.

Print the book id and titles of books published by a publisher. Quit.

3) Administrative Functions Menu: Add a book copy.

Search book copy and check its status. Add new reader.

Print branch information (name and location).

Print top 10 most frequent borrowers in a branch and the number of books each has borrowed.

Print top 10 most borrowed books in a branch. Find the average fine paid per reader.

Quit