Q1. An Online Shop data model (15 Marks)

Consider an ONLINE_SHOP database which allows customers to purchase items  from the shop on the web . The basic data requirements are summarized as follows:

•    Each customer of the shop is identified by a unique customer number, first and last name, gender, age, address and phone number .

•    Each item sold by the shop is identified by a unique barcode number, item description , unit price, and quantity in stock .

•    Each order placed by a customer is given a unique order number , and contains specified quantities of one or more items to be purchased .

•    Each order also has a delivery address, and records the date of receipt of the payment and an expected shipping date . The actual delivery date is recorded after the customer receives the shopping order .

Design an Entity Relationship (ER) diagram for the online shop database . Based on the real world scenarios, state any unspecified requirements and make appropriate  assumptions to complete the specification .

Q2. A Library System data model (15 Marks)

Consider a LIBRARY database in which data is recorded about a city library system . The basic data requirements are summarized as follows:

•    There are different library branches located in different locations of the city .

•    A book in the library records the book ID code, title, author list, publisher information, ISBN, date published, etc .

•    Each book may have a number of copies located in different library branches .

•    A publisher has detailed information, such as name, address and phone numbers, etc .

•    A member of the library, who is allowed to borrow books, has the information such as the member ID number, name, gender, birth date, address, phone         number, etc .

•    When a book is borrowed, it records the book ID code, branch ID, member ID, the date borrowed and the due date of returning the book, etc .

Design an Entity Relationship (ER) diagram for the library system application . Based on the real world scenarios, state any unspecified requirements and make appropriate assumptions to complete the specification .

Q3. Converting ER diagram into Relational Date Model (12 Marks)

Using the algorithm taught in the lectures (i .e . , Topic 05 “Relational Database Design

by ER to Relational Mapping” algorithm steps 1-7) , map the following simplified    'RECRUITMENT' system ER diagram into a relational database schema . Specify all primary keys and foreign keys , and provide detailed transformation steps and the      intermediate results in your answer .

Q4. Converting ER diagram into Relational Date Model (18 Marks)

Using the algorithm taught in the lectures (i .e . , Topic 05 “Relational Database Design

by ER to Relational Mapping” algorithm steps 1-7) , map the following AIRPORT      system ER diagram into a relational database schema . Specify all primary keys and    foreign keys , and provide detailed transformation steps and the intermediate results in your answer .