Database Design Homework: Normalization, Queries, and Anomalies

This database design assignment delves into the core concepts of database organization and management. The student's work begins with an exploration of database normalization, explaining its importance in structuring data for transactional databases and data warehouses, and detailing how it prevents data redundancy and various anomalies. The assignment also covers writing SQL queries to retrieve specific data from a database, with examples provided for various scenarios. Furthermore, the student identifies and describes insertion, update, and transitive anomalies within a sample table, and proposes solutions to prevent these anomalies through normalization techniques. The student has provided tables and queries to demonstrate the application of these techniques.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University

1DATABASE DESIGN
Table of Contents
1. Answer to Question 1:.................................................................................................................2
2. Answer to Question 2:.................................................................................................................3
3. Answer to Question 3:.................................................................................................................4
3. a. Anomalies Description:.......................................................................................................5
3. b. Solution to Prevent Anomalies:...........................................................................................5
Bibliography:...................................................................................................................................7

2DATABASE DESIGN
1. Answer to Question 1:
The database normalization method or data normalization approach is the process of
organizing the attributes of an entity regarding a transactional databases and data warehouses. If
a successful database design needs to be normalized. If the database system is not properly
normalized, then the database will be slow, ineffective and improper. These kinds of database
experiences data redundancy and anomalies.
1) Each phase of normalization process take out a specific kind of undesirable
reliance
2) It features imperatives and reliance in the information and consequently help
the understanding the idea of the information
3) Normalization characterizes productive information structures
4) Normalization controls information excess to lessen capacity necessity and
standard support
5) Normalization dispense with pointless reliance relationship inside a database
document
6) Normalization give one of a kind distinguishing proof to records in a database
7) Normalization licenses basic information recovery because of reports and
questions
8) Standardized information structures are utilized for record and database
outline
9) The third normalization shape delivers very much planned database which
gives a higher level of independency

3DATABASE DESIGN
The normalization of database is considered as the table modification method for making
the database more realistic and accurate. The modification is significant as the data resides
within the database will be stored and retrieved in proper manner and prevent anomalies if
properly normalized. The data that are interrelated forces the database designer to normalize the
database so that the data can reside in proper fashion. The efficiency and affectivity of the
website determines the level of accurate processing of normalization. The normalization is
essential to prevent the data replication.
In particular it serves to expel duplication from the database records. For instance in the
event that you have more than one place (tables) where the name of a man could come up you
move the name to a different table and reference it wherever else. Along these lines in the event
that you have to change the individual name later you just need to transform it in one place.
The database normalization is crucial for appropriate design of the database in the aspect
of the theory. In order to maintain the data integrity, the database normalization processes is
very effective.
2. Answer to Question 2:
Query 1: SELECT branch.branch_name
FROM branch
WHERE (((branch.branch_city)="Ringwood"));
Query 2: SELECT account.account_number, account.balance
FROM account
WHERE (((account.balance)>2000) AND ((account.branch_name)="Chase shopping"));

4DATABASE DESIGN
Query 3: SELECT account.account_number
FROM account INNER JOIN branch ON account.branch_name = branch.branch_name
WHERE (((branch.branch_name)="Chase_shopping") AND
((branch.branch_city)="Ringwood"));
Query 4: SELECT Count(customer.customer_name) AS CountOfcustomer_name,
branch.branch_name
FROM customer INNER JOIN ((branch INNER JOIN account ON branch.branch_name
= account.branch_name) INNER JOIN depositor ON account.account_number =
depositor.account_number) ON customer.customer_name = depositor.customer_name
GROUP BY branch.branch_name
HAVING (((branch.branch_name)="Chase_shopping"));
Query 5: The create table is not done in access through query. Figure 1 shows the table
called loans with customer_name, branch_name and amount.
Figure 1: The Loans Table
(Source: Created by Author)
3. Answer to Question 3:
Emp_I Contract_ HoursAssig Hotel_contact_ HotelN HotelLocati

5DATABASE DESIGN
D No ned
PerWeek
No o on
117756
7
MIT1009 17 045566790 H12 Ringwood
125678
8
MIT10010 20 056678954 H40 Melbourne
CBD
224676
9
MIT10011 30 045566790 H12 Ringwood
225467
8
MIT10012 30 056678954 H40 Melbourne
CBD
375895
6
MIT10013 25 045566790 H12 Ringwood
423789
0
MIT10014 36 056678954 H40 Melbourne
CBD
3. a. Anomalies Description:
At the time of insertion, the hotel related data must be provided accurately every time a
new employee will be inserted into the table. This leads high potentiality of data error.
At time of update of hotel related data, three of the columns must be updated. Suppose
employee with 2254678 id is transferred from H40 hotel to H12 hotel, then all the hotel related
data must be updated.
The dependency of the tables is as following.
Emp_ID ->( Emp_ID, Contract_No, HoursAssigned_PerWeek, HotelNo,
Hotel_contact_No, HotelLocation)
HotelNo -> (HotelNo, Hotel_contact_No, HotelLocation)
This leads to transitive dependency.
3. b. Solution to Prevent Anomalies:
Employee Table:

6DATABASE DESIGN
Emp_ID Contract_No HoursAssigned_Per
Week
HotelNo
1177567 MIT1009 17 H12
1256788 MIT10010 20 H40
2246769 MIT10011 30 H12
2254678 MIT10012 30 H40
3758956 MIT10013 25 H12
4237890 MIT10014 36 H40
Hotel Tables:
HotelNo Hotel_contact_No HotelLocation
H12 045566790 Ringwood
H40 056678954 Melbourne CBD
H12 045566790 Ringwood
H40 056678954 Melbourne CBD
H12 045566790 Ringwood
H40 056678954 Melbourne CBD

7DATABASE DESIGN
Bibliography:
Chatr-Aryamontri, A., Breitkreutz, B.J., Oughtred, R., Boucher, L., Heinicke, S., Chen, D.,
Stark, C., Breitkreutz, A., Kolas, N., O'donnell, L. and Reguly, T., 2014. The BioGRID
interaction database: 2015 update. Nucleic acids research, 43(D1), pp.D470-D478.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Ferreira, B., Faria, L., Ramalho, J.C. and Ferreira, M., 2016, October. Database Preservation
Toolkit: A relational database conversion and normalization tool. In iPRES: 13th International
Conference on Digital Preservation.
Ryberg, M., Vieira, M.D., Zgola, M., Bare, J. and Rosenbaum, R.K., 2014. Updated US and
Canadian normalization factors for TRACI 2.1. Clean Technologies and Environmental Policy,
16(2), pp.329-339.
Zhu, X., Lei, Z., Yan, J., Yi, D. and Li, S.Z., 2015. High-fidelity pose and expression
normalization for face recognition in the wild. In Proceedings of the IEEE Conference on
Computer Vision and Pattern Recognition (pp. 787-796).