• You are required to develop a relational database model for the University of Maryland library.  The database must support the following requirements:

1.            Include book information such as ISBN number, title of book, author/s, publisher, and date of publication.

2.            Store data of library members and their respective membership identification numbers, members’ full names, telephone numbers or emails, etc.

3.            Maintain the boro-list, including what book was taken, which member took it, the date when it was taken, and the date when it was to be returned.o Among the given case scenario, draw an entity-relationship (ER) diagram with the various entities, attributes, and relationships using the Entity Relationship (ER) model. o Describe the kind of associations between tables(such as one-to-many, many-to-many) and the kind of constraints(such as primary constraints and such like foreign constraints).support the following requirements:

1.            Board about books: ISBN, title, one or several authors, publisher, and publication year.

2.            Track library members, including unique membership IDs, names, and contact details.

3.            Record borrowing activity, noting the book borrowed, member borrowing it, date of borrowing, and return date.

Task Breakdown:

1.            Conceptual Design (30 points):

 Identify at least three entities and their relationships from the above-given scenario using the entity-relationship (ER) model,o Also state the type of relation between the entities (like one to many or many to many); cardinality and ordinality constraints like use of primary and foreign keys about the above-described scenario.o They need to define whether the relationship is one-to-many, or many-to-many and, where there are keys, whether they are primary or foreign.-relationship (ER) model. o Describe the type of relationship between the entities (like one to many, many to many) as well as the cardinality and ordinality constraints such as the use of primary key and/or foreign key. On this identify the type of relationship that is involved, one to many, many to many Then identify the constraints, keys mainly the foreign and primary keys. scenario given above by using the entity-relationship (ER) model. to describe the kind of relation is there (like one to many or many to many) and constraints used like primary key and foreign key etc. for the above scenario.

o             Specify the type of relationships (e.g., one-to-many, many-to-many) and any constraints (e.g., primary and foreign keys).

• schema design (40 point)
• Derived the Relational Schema from the ER diagram into a relational schema. It is also important to refer to and describe, tables that include columns, and primary keys. At least 3NF: This means that the database should be normalized to a level that makes it free from redundancy and dependency anomalies.? (a) To get the list of the members with borrowed books, it is necessary to acquire their full name and phone number. (ii) What is the most borrowed book title from the library regarding the list provided below find out the most repeated title

o             Normalize the database to at least 3rd Normal Form (3NF), ensuring the elimination of redundancy and dependency anomalies.

3.            Query Design (30 points):

o             Write SQL queries to perform the following operations:

?             (a) Retrieve the names and contact details of members who currently have borrowed books.

?             (b) Find the most borrowed book title in the library.

?             (c) List all books published after 2015 by a specific author, ordered by the year of publication.

Scoring Criteria:

•             Clarity and correctness of the ER diagram (10 points).

•             Accuracy in identifying relationships and constraints (20 points).

•             Normalization of the schema to 3NF without losing data integrity (20 points).

•             Correctness and efficiency of SQL queries (30 points).

•             Creativity and precision in problem-solving and adherence to requirements (20 points).