MN405 - Data & Information Management: SQL, ER Diagrams & Big Data

This assignment solution for MN405 Data and Information Management explores database design and management using MS Access, including creating tables, inserting records, and writing SQL queries. It covers designing tables with attributes, inserting data using datasheet view, and performing various SQL queries to retrieve specific data. The assignment also delves into Entity Relationship (ER) diagrams, relational schema creation, composite attributes, and relationship cardinality between entities like Book, Author, Publisher, and Warehouse. Furthermore, it provides a brief overview of Big Data technologies such as Hadoop and MapReduce, discussing their capabilities and limitations in handling large datasets and parallel data processing. Desklib offers a wealth of similar resources to aid students in mastering these concepts.

MN405- Data and Information
Management
Assignment 1 (T1 2018) - Managing Data in Databases

Table of Contents
Part A.....................................................................................................................................................3
1-a) Create Designs of Table with Attributes in MS- ACCESS.............................................................3
1-b) Insert Records into the tables using Datasheet View.................................................................4
1-c) SQL Query 1................................................................................................................................5
1-d) SQL Query 2................................................................................................................................5
1-e) SQL Query 3................................................................................................................................5
1-f) SQL Query 4................................................................................................................................6
1-g) SQL Query 5- Borrower Table.....................................................................................................6
Part B.....................................................................................................................................................7
2-a) Entity Relationship Diagram to Relational Schema.....................................................................7
2-b) Composite Attributes in the ER Diagram....................................................................................7
2-c) Relationship Cardinality..............................................................................................................8
Book- Author.................................................................................................................................8
Book- Publisher..............................................................................................................................9
Book- Warehouse..........................................................................................................................9
3) A brief note on Big Data Technologies.........................................................................................11
Hadoop........................................................................................................................................11
Capabilities and Limitations.........................................................................................................11
Map Reduce.................................................................................................................................12
Capabilities and Limitations.........................................................................................................12
References...........................................................................................................................................14

List of Figures
Figure 1- Borrow Table Design.....................................................................................................................4
Figure 2- Book Table Design........................................................................................................................4
Figure 3- Person Design...............................................................................................................................4
Figure 4- Publisher Design...........................................................................................................................5
Figure 5- Borrow Table Data........................................................................................................................5
Figure 6- Book Table Data............................................................................................................................5
Figure 7- Person Table Data.........................................................................................................................5
Figure 8- Publisher Table Data.....................................................................................................................6
Figure 9- Query 1-c SQL...............................................................................................................................6
Figure 10- Query 1-c Result.........................................................................................................................6
Figure 11- Query 1-d SQL.............................................................................................................................7
Figure 12- Query 1-d Result.........................................................................................................................7
Figure 13- Query 1-e SQL.............................................................................................................................7
Figure 14- Query 1-e Result.........................................................................................................................7
Figure 15- Query 1-f SQL.............................................................................................................................8
Figure 16- Query 1-f Result..........................................................................................................................8
Figure 17- Query 1-g SQL.............................................................................................................................8
Figure 18- Query 1-g Result- Borrower Table..............................................................................................9
Figure 19- Name composite attribute........................................................................................................10
Figure 20- Author composite attribute......................................................................................................10
Figure 21- Relationship between Book and Author...................................................................................11
Figure 22- Relationship between Book and Publisher...............................................................................11
Figure 23- Relationship between Book and Warehouse............................................................................12

Part A
1-a) Create Designs of Table with Attributes in MS- ACCESS
The tables are created in MS-ACCESS by clicking on Create tab and then clicking on Create Table. The
tables can be created in Design View [1].
Figure 1- Borrow Table Design
Figure 2- Book Table Design
Figure 3- Person Design

Figure 4- Publisher Design
1-b) Insert Records into the tables using Datasheet View
The data can be inserted into tables in Datasheet view [2].
Figure 5- Borrow Table Data
Figure 6- Book Table Data
Figure 7- Person Table Data

Figure 8- Publisher Table Data
1-c) SQL Query 1
Figure 9- Query 1-c SQL
Figure 10- Query 1-c Result
1-d) SQL Query 2
Figure 11- Query 1-d SQL
Figure 12- Query 1-d Result

1-e) SQL Query 3
Figure 13- Query 1-e SQL
Figure 14- Query 1-e Result
1-f) SQL Query 4
Figure 15- Query 1-f SQL
Figure 16- Query 1-f Result
1-g) SQL Query 5- Borrower Table
Figure 17- Query 1-g SQL

Figure 18- Query 1-g Result- Borrower Table
Part B
2-a) Entity Relationship Diagram to Relational Schema
The relations have been modeled from the given Entity Relationship Diagram by checking all the
functional dependencies. We must make sure that all the parts and the transitive dependencies have
been eliminated [3].
Customer (email: short text; name: short text; phone: number; address: long text)
Book (ISBN: number; title: long text; name (author)*: short text; name (publisher)*: short text; year:
date/time; price: number)
Publisher (URL: long text; name: short text; phone: number (int); address: long text)
Author (URL: long text; name: short text; address: long text)
Shopping-basket (basketID: number)
Shopping-list (basketID*: number; number: number; ISBN*: number; email*: short text)
Warehouse (code: number; phone: number (int); address: long text)
Stocks (code*: number; ISBN*: number; number: number)
Note- The asterisk sign in the relational schema represent the foreign keys. The underlined and
bold represent the primary keys.
2-b) Composite Attributes in the ER Diagram
The composite attribute is an attribute which can possibly have multiple components.
The two keys which can be considered as composite attributes in the ER diagram are:
 Name
 Address

The attribute name, if wanted to be divided can be in three parts- first name, middle name, and last
name. Therefore, this attribute being non-atomic is a composite attribute.
Figure 19- Name composite attribute
The attribute address, if wanted to be divided can be in parts- street number, street name, suburb,
region, and postcode. Therefore, this attribute being non-atomic is a composite attribute.
Figure 20- Author composite attribute
2-c) Relationship Cardinality
Book- Author
The cardinality of the relationship between Book and Author is M:1.

Figure 21- Relationship between Book and Author
Business Rule:
One author can write many books. It can also be versed as many books can be written by one
author.
Book- Publisher
The cardinality of the relationship between Book and Publisher is M:1.
Figure 22- Relationship between Book and Publisher
Business Rule:
One publisher can publish many books.
Book- Warehouse
The cardinality of the relationship between Book and Warehouse is M: M.

Figure 23- Relationship between Book and Warehouse
Business Rule:
Many books can be stored in many warehouses.

3) A brief note on Big Data Technologies
Hadoop
Introduction
This is a framework supporting Big Data technology. It is stated as a framework which can be used by
anyone and is written in JAVA language, compatible with all platforms. It allows large data sets to
undergo distributed processing and storing of the data under the environment that is distributive.
The modules that constitute Hadoop are as follows:
 The Java utility libraries and functions are the first components that together comprise the
Hadoop Common. These facilitate the starting of the Hadoop system due to the way they
are scripted.
 The second component is the YARN which acts as a task scheduler and is responsible for the
dataset management in clusters.
 The third component is Hadoop Distributed Filesystem (HDFS) which is the distributed file
system.
 The fourth component is the Map-Reduce which is used for parallel data processing [4].
Capabilities and Limitations
Hadoop Capabilities
 It has the capability of storing massive data (due to HDFS).
 Due to the undefined schema of HDFS, the data from different scattered sources can be
stored in Hadoop.
 The bulk of data can be ingested with maximum velocity not interrupting the queuing and
data scale.
 Data processing and pattern identification is facilitated.
 Data integrity is maintained.
Hadoop Limitations
 Real-time processing of data is not yet successful.
 Coding for developers can be a tough task.
 Highly complex
 Iterative model is not supported [5].

Conclusion
Hadoop thus remains the best option when you are dealing with big data. Despite its limitations,
Hadoop’s framework supports fault tolerance and bulk data storage plus analysis and processing of
big data. Hadoop remains the platform which excels in compatibility and distributed storage. Thus, it
is best when it comes to the handling of big data.
Map Reduce
Introduction
Map Reduce is a component of Hadoop which is responsible for the processing big data. It is a Java
written computing model which is used for data processing. The architecture consists of two parts
which are responsible for carrying out map reduce:
 Job Tracker: It is normally referred to as master node whose job is to assign the job/ tasks.
 Task Tracker: It is normally referred to as the slave node whose job is to carry out the tasks.
There are two functions which collectively constitute the map reduce:
1. Map: It includes mapping of the functions (converting data into name-value pairs).
2. Reduce: It uses the reducing technique (combining the name-value pairs into reduced
datasets).
Capabilities and Limitations
Map Reduce Capabilities
 The task scheduling is easy.
 The tasks monitoring is easy.
 The re-execution of the tasks once when failed is smoothly carried out.
 Proper task allocation is done.
MapReduce Limitations
 Failure of tasks once the job tracker shuts down [6].

Conclusion
Thus, Map Reduce helps in the smooth processing of the large datasets. It facilitates a parallel
processing of the tasks while the storage of the files is being carried out. All the steps from allocation
to processing are based on map-reduce algorithms which help in proper pattern identification and
catching of metadata. Thus, it is an important and significant component of Hadoop.

References
[1]Bayacangroup, "Creating Microsoft Access Tables", Baycongroup.com, 2018. [Online]. Available:
http://www.baycongroup.com/access2007/02_access.html. [Accessed: 06- May- 2018].
[2] J. Brownell, "Add Records to a Table in Datasheet View in Access", TeachUcomp, Inc., 2018.
[Online]. Available: https://www.teachucomp.com/add-records-to-a-table-in-datasheet-view-in-
access-tutorial/. [Accessed: 06- May- 2018].
[3]"Relational Schemas - Understanding Relational Databases | Coursera", Coursera, 2018. [Online].
Available: https://www.coursera.org/learn/analytics-mysql/lecture/hDLIS/relational-schemas.
[Accessed: 06- May- 2018].
[4]"Hadoop Tutorial", www.tutorialspoint.com, 2018. [Online]. Available:
https://www.tutorialspoint.com/hadoop/index.htm. [Accessed: 06- May- 2018].
[5]"Hadoop – Advantages and Disadvantages | Java J2EEBrain", J2eebrain.com, 2018. [Online].
Available: http://www.j2eebrain.com/java-J2ee-hadoop-advantages-and-disadvantages.html.
[Accessed: 06- May- 2018].
[6] J. Weets, M. Kakhani and A. Kumar, "Limitations and challenges of HDFS and MapReduce", 2015
International Conference on Green Computing and Internet of Things (ICGCIoT), 2015.