Database Technologies BN204 Assignment 1 Part B: Queries and Research

This document presents a comprehensive solution to a Database Technologies assignment, focusing on practical database skills and theoretical understanding. The assignment begins with database creation, including the design of tables for Hotels, Rooms, Guests, Bookings, and Payments. It then delves into SQL queries, demonstrating how to retrieve and manipulate data using SELECT statements with various clauses like ORDER BY, INNER JOIN, WHERE, GROUP BY, and HAVING. The solution also explores functional dependencies and the process of third normal form normalization to ensure data integrity and minimize redundancy. Furthermore, the assignment includes research questions comparing Big Data and traditional relational databases, highlighting the differences in data organization, structure, and processing techniques. The document also provides an overview of Hadoop, including its core components, HDFS and MapReduce, explaining their roles in managing and processing large datasets in a distributed environment. The solution concludes with a list of references used in the assignment.

Database Technologies
May 6
2019
Student ID:
Student Name: Assignment 1

Part B
Contents
Part B...........................................................................................................................................................2
1) Database Creation................................................................................................................................3
2) SQL Queries........................................................................................................................................5
3 A) Functional Dependencies................................................................................................................7
3 B) Third Normalized Relations............................................................................................................8
4) Research Questions..............................................................................................................................9
Difference between Big Data and Traditional Relational Data................................................................9
Hadoop....................................................................................................................................................9
References.................................................................................................................................................11

1) Database Creation
Hotels Table
Rooms Table

Guests Table
Bookings Table
Payments Table

2) SQL Queries
Query 1
SELECT * FROM Hotels ORDER BY Hotel_Name DESC;
Query 2
SELECT Hotel_Name, Hotel_Address, Room_Number, Room_Floor, Room_Rate FROM Hotels INNER
JOIN Rooms ON Hotels.Hotel_ID=Rooms.Hotel_ID WHERE Room_Rate>=500 and Room_Rate<=1000;

Query 3
SELECT Hotel_Name, Hotel_Address, count(Room_ID) AS NumberOfRooms FROM Hotels INNER JOIN
Rooms ON Hotels.Hotel_ID = Rooms.Hotel_ID GROUP BY Hotel_Name, Hotel_Address;
Query 4
SELECT Hotel_Name, Hotel_Address, Guest_ID, Room_Number, Room_Rate, Staff_ID FROM (Hotels
INNER JOIN Rooms ON Hotels.Hotel_ID=Rooms.Hotel_ID) INNER JOIN Bookings ON
Rooms.Room_ID=Bookings.Room_ID WHERE DateFrom=#6/20/2019# ORDER BY Staff_ID;
Query 5
SELECT Payment_Date, sum(Payment_Amount) AS TotalPayment FROM Payments GROUP BY
Payment_Date HAVING Payment_Date=#3/3/2019#;

3 A) Functional Dependencies
Transitive Dependency
Customer_Name  Customer_No
Customer_ContactNo  Customer_No
Description  ItemNum [1][2]

3 B) Third Normalized Relations
The normalized relations are created by applying following rules-
- Remove all repeated groups from the tables and create new tables.
- Make primary key in all new tables.
- Ensure all fields depend upon the primary key only.
- Remove transitive dependency from the tables, if any.

4) Research Questions
Difference between Big Data and Traditional Relational Data
The Relational data use a specific way to organize the data. It is based upon the SQL (Structured
Query Language). It works on specific structure of data where the data is predefined and the
structure the data is also predefined. It works in situations where correctness is most important
than speed.
While the Big data works on the huge amount of data. It is based upon the noSQL. It works upon
the unstructured data where no structure is defined for the data. Nobody knows about which type
of data will come. It works in situations where speed is more important than correctness.
It works on social networking sites etc. where nobody knows about the tweets structure etc. it
will store the huge amount of data inform of tweets in spite of structured data which is sued in
banking etc.
Big data is a distributed file system unlike relational database. Big data is based upon the HDFC
(Hadoop Distributed File System) and MapReduce. MapReduce is a programming model which
is used to process large amount of data.
Relational Database is based upon the ACID (Atomicity, Consistency, Isolation and Durability)
property. [4]
Hadoop
Hadoop is an open source system which works upon the Big Data. It works in distributed
environment with lots of systems which works as cluster systems. There are two core
components of it-
- HDFS (Hadoop Distributed File System)
- MapReduce
HDFS (Hadoop Distributed File System) is the storage layer that stores the large amount of
data. It follows the architecture where two nodes are defined- nameNode and dataNode to
implement distributed file system.
It is the key part of the Hadoop technology because it reliably manages the pool of big data and
supports various analytical applications of Big data.

It helps in rapid transfer of data between various computer nodes and coupled with MapReduce
algorithm. It is based upon the parallel processing. It takes the data and separates it in blocks and
distribute to different nodes in a cluster. It is very efficient way of data processing.
It is very fault tolerant. It copies and stores the data on different nodes. If one node crashes, the
system will work continue because there is data backup on different nodes.
MapReduce is a programming model which is used to process large amount of data. It splits the
data into several blocks of data which are transferred to the different machines which are
connected in the computer clusters.
It is developed by Google to work on Big Data processing. It is the core part of Hadoop. It is
very difficult to precoess the large volume of data but MapReduce has solved all the problems.
To split the data into smaller chunks and then distribute to different nodes in the cluster is called
the Map and after processing it collects the results from each machine and produce the final
result.
Capabilities
There are so many capabilities of Hadoop-
- It is compatible to all platforms.
- It is a fault tolerant. In case of any node cashes, the system will work continuously
because there is backup of the database at other nodes.
- A server can be added or removed dynamically.
- It is very inexpensive.
- It finds the faults at early stages and without the help of any hardware or software.
Limitations
There are some limitations of Hadoop also-
- It does not work on small data. It is capable of only large volume of data.
- It is based upon the batch processing only.
- It focuses on the high speed only in spite of correctness.
- It is not suitable for real time applications because it cannot work on small data
applications.
- It is not suitable of banking applications etc. where corrections are much required than
speed. [3]

References
[1] Brombarg P. “SQL SERVER Database Normalization Basics for Developers”, n.d. [Online].
Available: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-
for-developers.aspx. [Accessed: 6-May-2019]
[2] Janalta Interactive Inc., “Functional Dependency”, 2015. [online]. Available:
http://www.techopedia.com/definition/19504/functional-dependency. [Accessed 6-May-
2019].
[4] TechTarget, “Hadoop Distributed File System (HDFS)”, 2018. [online]. Available:
https://searchdatamanagement.techtarget.com/definition/Hadoop-Distributed-File-
System-HDFS. [Accessed 6-May-2019].
[3] W3trainingschool.com, “Difference Between Big Data Hadoop and Traditional RDBMS”,
2018. [online]. Available: https://www.w3trainingschool.com/difference-big-data-
hadoop-traditional-rdbms. [Accessed 6-May-2019].