Aim: Design a database schema for an application with ER diagram for a problem description. 

The requirement: A library wants to maintain the record of books, members, book issue, book return, and fines collected for late returns, in a database. The database can be loaded with book information. Students can register with the library to be a member. Books can be issued to students with a valid library membership. A student can keep an issued book with him/her for a maximum period of two weeks from the date of issue, beyond which a fine will be charged. Fine is calculated based on the delay in days of return. For 0-7 days: Rs 10, For 7 – 30 days: Rs 100, and for days above 30 days: Rs 10 will be charged per day. 

Sample Database Design 

BOOK (Book_Id, Title, Language_Id, MRP, Publisher_Id, Published_Date, Volume, Status) // Language_Id, Publisher_Id are FK (Foreign Key) 

AUTHOR(Author_Id, Name, Email, Phone_Number, Status) 

BOOK_AUTHOR(Book_Id, Author_Id) // many-to-many relationship, both columns are PKFK (Primary Key and Foreign Key)

PUBLISHER(Publisher_id, Name, Address) MEMBER(Member_Id, Name, Branch_Code, Roll_Number, Phone_Number, Email_Id, Date_of_Join, Status) 

BOOK_ISSUE(Issue_Id, Date_Of_Issue, Book_Id, Member_Id, Expected_Date_Of_Return, Status) // Book+Id and Member_Id are FKs 

BOOK_RETURN(Issue_Id, Actual_Date_Of_Return, LateDays, LateFee) // Issue_Id is PK and FK 

LANGUAGE(Language_id, Name) //Static Table for storing permanent data LATE_FEE_RULE(FromDays, ToDays, Amount) // Composite Key

Introduction to ER diagram:

An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is an object, a component of data. An entity set is a collection of similar entities. These entities can have attributes that define its properties.

By defining the entities, their attributes, and showing the relationships between them, an ER diagram illustrates the logical structure of databases.

Components of ER diagram:

ER diagrams or ERD’s are composed of three main elements: entities, attributes, and relationships.

    Entities - typically displayed in a rectangle, entities can be represented by objects, persons, concepts, or events that contain data.
    Attributes - displayed in a circle or an oval, the attributes refer to the characteristics of an entity. They can be categorized as simple, composite, or derived, and an object can have one or multiple attributes.
    Relationships - illustrate how two or more entities interact with each other. They are displayed as labels placed on the lines connecting the objects.

ER diagram:
Paste your ER Diagram here

RESULT:

The ER diagram has been completed on Lucid chart and the output has been verified. CO1 is attained