Part 1: Entity-Relationship Diagram (ERD) (10 Points):

Draw the Entity-Relationship Diagram (ERD) for your application to show the entities and relationships. Make sure to clearly show the following:

1- All entities (i.e., objects) that are needed to implement your application 2- Each entity must be identified by a primary key and described by a set of attributes. 3- List clearly all relationships between entities (Business Rules). For example, the relationship between Customer and Invoice entities is described as Each customer can have more than one invoice, however, an invoice can belong to only one customer. 4- Relationship cardinalities for all relationships in your ERD (1-to-1, 1-to-M, or M-to-M).

Part 2: Relational Schema (10 Points):

Convert the ERD that you designed in Part 1 into the corresponding relational schema. The relational schema should include the following:

Table names Attribute names and data types Primary keys Foreign keys Note: Use the tables that you identified in Project Step 1

Below mentioned things are the project step one :-

Library Management System

The Database Library System is intended to Automate the library activities such as creating a new borrower, giving books to the borrowers, maintaining the details of all the item that were available in the books . This also helps the librarians by providing information such as total copies available each book, list of books that belong to a particular category (Short, Long Loan, Reference items, etc).

Tables

Book_Details Binding_Details Category_Details Borrower_Details Student_Details Staff_Details Student_Details Shelf_Details

Attributes of each table

Book_Details :- ISBN, Book_Title, Publication_year, Language, Category_Type, Binding_Id , No_Of_Copies_Actual, No_Of_Copies_Current Binding_Details :- Binding_ID, Binding_Name Category_Details :- Category_ID, Category_Name Borrower_Details :- Borrower_ID, Book_Id, Borrowed_From, Borrowed_TO, Actual_Return_Date, Issued_by Staff_Details :- Staff_Id, Staff_Name, Is_Admin, Designation Student_Details :- Student_Id, Student_Name, Sex, Date_of_Birth, Borrower_Id, Department, Contact_Number Shelf_Details :- Shelf_Id, Shelf_No

Queries

Display the student details whose ISBN is 1024 Display the borrower(s) id who borrowed fiction books Display Student_ID, Student_Name , Book_Title where staff name is Ahmed. Display the staff table where the staff is an admin. Display the Borrower details whose due date os before 30th October, 2018.