Managing Data in Databases
Purpose of the assessment - The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Model organisational information requirements using conceptual data modelling techniques.
b. Convert the conceptual data models into relational data modeland verify their structural characteristics with normalisation techniques.
1. The schema for the LibraryDBdatabase is given below. LibraryDB is a database that keeps track of information about the books and their circulation in a library.
The primary keys are underlined. The foreign keys are denoted by asterisks (*).
Borrow(transactionID:text, personID*:text ;borrowdate:date, returndate:date)
Book(ISBN:number, title:text, Volnumber:number, year_published:numeric, NoofCopies:number; Price:number; PublisherID:text)
Person(personID:text, firstname:text, lastname:text, address:text, city:text,
postcode:txt, phonenumber:number)
publisher(publisherID:text,publisherfullname:txt)
Description of the schema
borrow -- keeps track of the check-ins and check-outs of the books.
person -- keeps track of the people who borrow books from the library.
book -- contains information about the books that are available in the library.
publisher -- keeps track of the publisher information.
a. Implement the above described database in MS Access. You are required to create the above 4tables with the given attributes. Name the database as ‘LibraryDB' .
b. Populate all tables with suitable data (at least 3 records per table- suitable to answer given queries) using the following SQL statements or data sheet view in MS Access.
INSERT into TableName
VALUES ("..","..",.....)
Write SQL queries for the following questions and execute the queries on the ‘LibraryDB' database you created in MS Access.
c. Prepare a list of ISBN number, title, Volnumber, NoofCopiesof all Books in the library.
d. Assume that you want to count how many books in the library which are older than 10 years (i.eyear_published is before 2008). Write a query to find the number.
e. Display details of all books that cost more than the average price of the books in the library.
(Hint: use sub-query).
f. Display details of all bookswhich are published in year 2017 and published by "ACER Press" publishers (publisherfullname is "ACER Press")
(Hint: you may need to join Book table and publisher table)
g. Create a new table named "Borrower" that includes personID, firstname, lastname, address of the borrower, borrowdate and returndate.
Hint : you may need to join 2 tables Person and Borrow.
2. Sales tracking is important in many online stores. Figure 1 shows the ER diagram that captures important information in the tracking of sales in a Bookstore. Specifically, the ER diagram captures relationships between authors, Books, customers, publishers, warehouses and shopping-basket.
Figure 1: Bookstore ER Diagram
a. Convert the ER diagram into a relational database schema. Be certain to indicate primary keys (underline).
EgBook (ISBN: NUMBER; title:text,.....)
b. Identify 2 attributes in the ER diagram of figure 1 that might represent a composite attribute, and explain why/how it might represent a composite attribute.
c. Determine the cardinality of the relationships(listed below) in the above ER diagram. The cardinality shows how much of one side of the relationship belongs to how much of the other side of the relationship. Write the business rule that you would use to mark the cardinality, as shownin the example given in figure 2.
i. Book-Author
ii. Book - publisher
iii. Book - Warehouse
Example:
Relationship between shopping-basket --- Customer
Business rule:
A customer may submit any number of orders on to the shopping-basket. However, each order must be submitted by exactly one customer.
Figure 2: Example of cardinality of business rule for the ‘shopping-basket --- Customer' relationship
3. Prepare a brief report on the following "Big data technologies". (6 Marks)
a. Hadoop
b. MapReduce
Use the following heading for writing the report for each topic:
1. Introduction
2. Capabilities and Limitations
3. Conclusion