Develop an Entity-Relationship model of the information requirements for the above system. Translate your model into an equivalent set of relations. Specify all relation headings, indicating primary and foreign keys.

ASSESSMENT TASK

Problem

You are required to design and implement various database systems for a library. Find the description of scenario below. Each customer has a unique membership number. For each customer, you keep his/her name, address, the total number of items the customer is currently borrowing, and the customer’s registration date. You also keep information about the items in the library. Items may be books, magazines or e-books. Each item is assigned a unique number. For each book its title, author(s), publisher, year, genre and number of pages are stored. For magazines, title, issue numbers are stored. Finally, ebooks have the same attributes as simple books and additionally a URL. A library may hold more than one copy of a same book or magazine. Customers may borrow up to 5 books or magazines at the same time for up to 2 weeks, but can download an unlimited number of e-books. You want to keep track of all items borrowed, so you need to store the borrowing and return dates of every item. You also want to keep the e-book download dates. In addition, you need to record the reservations for the books or magazines which are currently not available. Reservations include the customer, the item and the number in the queue. Each item can be reserved multiple times.

Task Description

Based on the above requirements, you are required to carry out the following tasks:

A Develop an Entity-Relationship model of the information requirements for the above system. Translate your model into an equivalent set of relations. Specify all relation headings, indicating primary and foreign keys. Make sure that all relations are in the Third Normal Form.

B Using Neo4J, generate sample data for the above case. Write the following queries in Cypher, briefly explain how they work and provide screenshots of the results (make sure that there is enough data that all the queries return results).

1. Show all customers who have not borrowed a single item.

2. Count the number of customers who have loaned an item in 2018.

3. Show all the items borrowed by a customer with .

4. Show the most downloaded e-book.

5. Show all authors whose books have not been borrowed by a customer with .

C Generate sample data which includes three or more customers and three or more items borrowed by some of those customers (including return deadlines). Present this data in XML form, along with accompanying XSD with KEY/KEYREF type constraint checking. Validate your XML and XSD using https://www.corefiling.com/opensource/schemaValidate/ and provide the screenshot of validation.

D Using MongoDB, generate sample data for the above case. Write the following queries in MongoDB briefly explain how they work and provide screenshots of the results (make sure that there is enough data that all the queries return results).

1. Show all customers who have not borrowed a single item.

2. Count the number of customers who have loaned an item in 2018.

3. Show all the items borrowed by a customer with .

4. Show the most downloaded e-book.

5. Using MapReduce in MongoDB, count how many books of each genre are there in the library. Explain how MapReduce works for this particular example.