KPU Mobile Library Service (ER Diagram and Normalization)

KPU Mobile Library Service has several buses spread throughout campus at KPU. Each bus is identified by its unique registration plate. Each bus is allocated staff, which includes a Driver. The Driver is responsible for the day-to-day running of a given bus. The data held on a member of staff is name, position, and salary. Each member of staff is given a unique staff number. Each bus has a number of library resources available for rent including CDS, DVDs, audio tapes etc. The data held on a library resource is its unique resource number, title, author, level, daily rental, cost, and status. A resource is given a level describing the degree of difficulty such as Introduction, Basic, and Advanced etc. The status indicates whether a specific copy of a resource is available for rent. Before renting a resource, a student must first register on a bus as a member of the mobile library service. Each student is given a unique student mobile service number upon registration. The data held on a student is the student's mobile service number, full name(first and last name), address, and the date that the student registered. Once registered, a student is free to rent up to a maximum of five resources at any one time. The data held on each resource rented is the resource number, student mobile service number, daily rental, the date the resource is rented out and the date returned.

Design Requirements

You are required to produce an entity-relationship model describing the entities and relationships, and the relevant attributes required to support the above-mentioned scenario:

Your entity-relationship model must include

• any business rules that you have assumed whilst preparing your model.
• a list of the attributes for each of the entities and the relationships
• the primary key for each of the entities
• the foreign keys needed to represent the relationships
• Validate your data (Normalize in 1st,2nd and third form)