ISYS114 Assignment One: Database Modeling and Normalization Techniques

This assignment solution for ISYS114 covers key database design concepts. It begins with an overview of ER diagrams, explaining entities, attributes, relationships, and various entity symbols. The solution then progresses to EER diagrams, detailing specialization, generalization, supertypes, subtypes, and inheritance. Next, it explores the relational model, explaining relations, tables, columns, rows, keys (primary and foreign), and provides relational model diagrams for service and payment, part-time mechanics and branch, and manufacturer and car scenarios. The assignment concludes with a discussion of normalization, including 1NF, 2NF, and 3NF, along with a decomposition analysis and identified assumptions related to the database design, such as assumptions about manufacturer-car relationships, customer loyalty programs, and service transactions. References to supporting materials are also included.

ISYS114 2018 – ASSIGNMENT ONE
StudentID:
StudentName:
Workshop Number:
Tutor Name:
Workshop Day and Time:

Task 1: ER Diagram
Entity relationship diagram denotes to diagram essential to modelling of databases from
basic notifications to complex and confusing database. ER diagram are used as the
foundation of for logical data modelling. Additionally, they are used as the formation of
commonality as the basis of data modelling integration.
ER Diagram uses the following entity symbols:
An entity denotes to a concept or object that represents an idea
Entity symbol Name Description
Attribute An attribute is a feature/
characteristic of an object.
Derived attribute These are attributes whose
final value can be
calculated. For instance in
the diagram below, net
salary can be arrived at by
minus taxes from gross
salary and adding grief
allowances.
Multivalued attribute These are attributes that
can take more than one
value. For instance name
can take first name and last
name
Relationship This is an association among
objects.
Weak relationship This is an association of
weak entity with another
weak object.
Strong entity This is a parent entity as it
has weak entities that
depend on it.
Weak entity This is an entity that is
dependent on another
entity called strong entity
Associative entity An associative entity
contains features specific to
relationship among the
entities.

It should be noted that primary key attributes are indicated by use of underline. Whereas
foreign keys are represented with being in italic.
Task 2: EER Diagram
EER (enhanced entity relationship diagram) denotes to specialized Entity relationship
diagram that be used to model our database. This is higher level model that can be
deployed to represent most complex requirements of a database. EER diagrams
encompasses all attributes of an ERD together with the following:
 Union type or category
 Specialization and generalization: generalization denotes to the technique of reference of
a general entity whereas specialization is the vice versa.
 Super types and subtypes: a super type relates to one or more subtypes whereas a subtype
is a small group of entities with exceptional attributes.
 Relationship and attribute inheritance: is a concept of subtype entities inheriting values of
all super type.


(Boetticher, 2018)
Task 3: Relational Model
A relational model denotes to the conceptual background of the relational database. This is
a method of structuring information by use of relations. Relations are compared to a
mathematical grid structures that consist of row and columns. Manifestation of relational
model is commonly known to us as a table.
In relational modelling, all information has to be stored in tables also known as relations.
Every table encompasses columns and rows. Every relation ought to have a body and a
header. The header is basically the listed columns in the table. Body is the group of data
which in the real sense occupies the table, arranged in rows.
A relation has a second feature which is the usage of keys. These are uniquely designed
fields within a relation. It is deployed to relate data with data from other entities and / or
order data with respect to other tables. Hence the term a relation. The most vital key is the

primary key. The primary key is deployed to uniquely identify every row of data. This makes
querying and for information a little bit easier. Foreign keys on the other hand, relate data in
a single relation to the primary key of the other relation. Below are our relational model
diagrams.
1) Question 1: Service and payment
2) Question 2: Part-time mechanics and branch
3) Question 3: Manufacturer and car

Task 4: Normalisation
1) The given table is in this normal form:
First Normal Form
2) Decomposition into normal form:
Normalization Process.
Normalization refers to the process of arrangement data in a database to eliminate
deletion, updation, redundancy and insertion anomalies (Fong, 2015 ).
Looking at our table above, table is un-normalized 1NF since it qualifies the features of
1NF, that is, when columns are brought together make unique row every single moment
and there exists fields that can be used primarily to identify the every row. Our original
dependency table can be diagrammatically represented as below:
Second Normal Form
A database table qualifies to be I 2NF if and only if it has passed 1NF test and every
primary key is not dependent on any other Primary key. Decomposing our above
dependency table, we will have the following new tables

Looking at the above diagram our table is in 2NF.
Third Normal Form.
A table in a database qualifies to be in 3NF if it is in the second normal form and its
columns do not have transitive functionality, that is, column M determines column N
which in turn determines column O. by keen observation of above dependency table
diagrams, we realise that there is no any form of transitive dependency. This implies
that our database tables are in 3NF.
3) Relational model

Task 4
Assumptions, if any:
An assumption refers to that which is believed to true by the society but not yet proven. In
our database design, the following are the assumptions made.
Task 1:
 Manufacturer manufactures only one kind of car, which in the real sense is that
a manufacturer can manufacture more than one kind of a car.
 A car is owned / possessed by one and only one customer. This case may not
true because may occur as we can have a group of individuals possessing a single
car.
Task 2:
 A full time mechanic serves one and only one branch
 Only customers who have registered with loyalty program are the ones legible
for discounting.
Task 3:
 Payment is fixedly done as a single transaction. This may not uphold at some
instances.
Task 4:
 A branch may offer a number of services.
 A car can only be serviced at one branch.
 It is assumed that a branch can service more than one cars.

References
Boetticher, G. (2018, September 12). DBMS 04 Unit 02 ER and EER Diagrams Part 02.
Retrieved from Youtube: https://www.youtube.com/watch?v=fZL7XtQb4lw EER
Fong, J. S. (2015 ). Information Systems Reengineering, Integration and Normalization. New
York City: Springer.