Database System Technology: ER Modeling, Normalization, and Design

This assignment solution explores database system technology, focusing on Entity Relationship (ER) modeling and the normalization process. The document includes an ER diagram illustrating the relationships within an auto seller database. It then delves into the normalization process, breaking down the steps to achieve the First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). The solution demonstrates how to eliminate multivalued attributes and partial and transitive dependencies to create efficient database tables. The document provides example tables at each normal form, illustrating how data is restructured to meet the normalization requirements. Finally, the solution includes a bibliography with relevant sources on database design and modeling.

Running head: DATABASE SYSTEM TECHNOLOGY
Database System Technology
Name of the Student
Name of the University
Author’s Note:

1DATABASE SYSTEM TECHNOLOGY
Table of Contents
Entity Relationship Modeling:.........................................................................................................2
Normalization Process:....................................................................................................................2
Bibliography:...................................................................................................................................8

2DATABASE SYSTEM TECHNOLOGY
Entity Relationship Modeling:
Figure 1: Entity Relationship Diagram of Auto Seller Database
(Source: Created by Author)
Normalization Process:
1st Normal Form:
buyer_id car_number purchase_date sold_date seller_id from_date to_date
101 201 2015-07-22 2015-07- 101 2015-07- Present

3DATABASE SYSTEM TECHNOLOGY
22 22
101 2015-08-15 2015-08-
15
201 2015-07-
22
Present
201 301 2015-07-22 2015-07-
22
101 2015-07-
22
Present
401 2015-08-15 2015-08-
15
201 2015-07-
22
Present
The above database table has multivalued attributes for sales data. As per the rule of first
normal form, the attributes must not have multiple values. For this reason, the table is expanded
to meet the requirement of making every attribute consisting of atomic values.
buyer_id car_number purchase_date sold_date seller_id from_date to_date
101 201 2015-07-22 2015-07-
22
101 2015-07-
22
Present
101 101 2015-08-15 2015-08-
15
201 2015-07-
22
Present
201 301 2015-07-22 2015-07-
22
101 2015-07-
22
Present
201 401 2015-08-15 2015-08-
15
201 2015-07-
22
Present

4DATABASE SYSTEM TECHNOLOGY
2nd Normal Form: The tables can only be in second normal form if those tables are in
first normal form and non-key attributes are only dependent on key-attribute. The below
dependency diagram shows the partial dependency among the attributes of first normal form
table.
buyer_id  first_name, last_name, address, contact_number, from_date, to_date
seller_id  last_name, first_name, address, contact_number
car_number  make, model, color, price, year, mileage, body_type, fuel_type,
engine_size, fuel_consumption, CO2_emission
sales_number  car_number, seller_id, buyer_id, purchase_date, sold_date, sold_price
In order to remove the partial dependencies, the partially dependent attributes are moved
to another table. This creating the following tables of second normal form.
buyer_
id
first_na
me
last_na
me
address contact_num
ber
car_num
ber
from_d
ate
to_da
te
101 Ethan Umbaga
i
81 Darwinia
Loop
NULLAGIN
E WA 6758
(08) 9015
4021
201 2015-
07-22
2015-
08-15
201 Jett Joyner 85 Mt
Berryman
Road
CARPENDA
LE QLD
(07) 4533
0533
101 2015-
07-22
2015-
07-22

5DATABASE SYSTEM TECHNOLOGY
4344
301 Jamie Fitch 59 South
Street
MARGATE
TAS 7054
(03) 6253
5955
301 2015-
07-22
2015-
08-16
seller_id first_name last_name address contact_numbe
r
101 Mary Barak 83 Cherry
Grove
LILEAH TAS
7330
[phone]
201 Georgia Kavel 96 Jones Road
KENMORE
DC QLD 4069
[phone]
301 Sophia Fergusson 42 Weigall
Avenue
CAVENAGH
SA 5422
[phone]
car_numbe
r
make model color price year mileag
e
body_type
101 alfa- Sports red 150000.00 2018 10 convertible

6DATABASE SYSTEM TECHNOLOGY
romero
201 mercedes-
benz
s102 royal blue 250000.00 2017 7 wagon
301 alfa-
romero
SUV red 150000.00 2016 12 convertible
sales_number car_number seller_i
d
seller_i
d
purchase_date sold_date sold_price
101 101 201 201 2015-08-15 2015-08-
15
1200.00
201 201 101 101 2015-07-22 2015-07-
22
2200.00
301 301 301 301 2015-08-16 2015-08-
16
3200.00
3rd Normal Form: The third normal form is associated with the transitive dependency.
The transitive dependency means that y is dependent on x and z is dependent on y but z is not
dependent on x. In order to make the tables in third normal form, the transitive dependency is
removed by creating a new table. The below dependencies shows transitive dependency.
buyer_id  car_number
car_number ! buyer_id
car_number  from_date, to_date

7DATABASE SYSTEM TECHNOLOGY
buyer_id first_name last_name address contact_number
101 Ethan Umbagai 81 Darwinia Loop
NULLAGINE WA
6758
[phone]
201 Jett Joyner 85 Mt Berryman
Road
CARPENDALE
QLD 4344
[phone]
buyer_id car_number from_date to_date
101 201 2015-07-22 2015-08-15
201 101 2015-07-22 2015-07-22
301 301 2015-07-22 2015-08-16

8DATABASE SYSTEM TECHNOLOGY
Bibliography:
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Sorokin, A., Le Novère, N., Luna, A., Czauderna, T., Demir, E., Haw, R., ... & Villéger, A.
(2015). Systems Biology Graphical Notation: entity relationship language level 1 version
2. Journal of integrative bioinformatics, 12(2), 281-339.
Zhang, F., Ma, Z. M., & Cheng, J. (2016). Enhanced entity-relationship modeling with
description logic. Knowledge-Based Systems, 93, 12-32.
Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., ... & Pei, S. (2017). Probabilistic
Entity-Relationship Diagram: A correlation between functional connectivity and
spontaneous brain activity during resting state in major depressive disorder. PloS
one, 12(6), e0178386.