Read and understand the scenario carefully.

The Ryerson Library requires a database to support its activity. There are several branches in the library systems but it only handles books. Each branch obviously retains many book copies. Each branch has a unique branch no, a name, a location and a phone #. For each book in its collection it retains an ISBN, Title, Author(s) information and date of publication. Each book is also assigned to one or more subject categories. A set of standard subject categories is stored in the database to be used in member searches.

Each subject category can be assigned too many books and can be classified as being of either a major or a minor type for any particular book. A book may have one or more authors. Each author is assigned an author id and also retained is their first and last name, date of birth and date of death. For each book there may be 1 or more physical copies. For each physical copy there is a copy number assigned for that book and the date the copy was purchased. That copy number is unique within that ISBN for the whole system but each copy has a "home" branch. Also retained is a status field to record special situations such as "M" (missing) or "T" (in transit). Book copies are available for members to borrow.

When a book loan is made from a branch the date loaned and due date are recorded. When it is returned that date is also recorded as well as any fines accruing. When an outstanding fine on a loan is paid the date of payment is also noted in that loan record. Not just any current active loans but a history of all past loans need to be kept in the database. A person is a member of the Ryerson library as a whole and may borrow books from any branch. Each member of the library has a member id, first and last name, address, email address and phone number. In addition to borrowing books members may reserve a book (but not a specific physical copy).

When a reservation is made the reservation date is recorded as well as the branch where the member will pick it up. When a copy of the reserved book is available it will be allocated to the reservation and linked to the branch that has been designated for pickup. The member is notified and the notification date is entered into the database against the reservation. The member has 5 days to pick up the book and check out a loan on the book or the reservation expires and the copy is returned to its home branch. Only active reservations are kept in the database.

A member can also bring in and register their kids as members (under their same memberID account), however this information relationship is to be stored in the database (as a recursive relationship).

Part 1:Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify the different entities, the attributes of each entity including the Primary key, and the relationship between the entities. (100 marks)

Part 2:Write the SQL statements of the following: (100 marks)

1. Using SQL Server create a database called Project2_GRP## (where ## represents your group number: ie 01, 02, etc)
2. Define all primary keys, foreign keys and data types (20 marks)
3. Populate the tables with sufficient data (at least five records to each table) to adequately test all aspects of your queries. (See instructions below). Failure to do so will lose you marks
4. Develop the SQL queries to satisfy the below - 80 marks.

• Write at least three different SQL SELECT statements with WHERE clauses that will show different result.
• Write one SQL statement that uses any one of the aggregate functions with group by clause.
• Write one update statement that will modify the data inserted in task C.

Note:Queries that do not return sufficient data will be marked as wrong

1. All queries must generate some output
2. Queries with explicit sequencing must return a minimum of 3 rows
3. An aggregate query must have at least one row of output that is using at least 2 input rows (e.g. a count of 2, not 0 or 1)
4. Use column aliases where appropriate.
5. Suppress duplicate rows where appropriate for output based on your actual data even if not specifically requested in the question.