Page 116 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE
P. 116
Unit 7: Relational Database Design
The process proceeds in a top-down fashion by evaluating each relation against the criteria for Notes
normal forms and decomposing relations as found necessary during analysis.
Therefore, normalisation is looked upon as a process of analysing the given relation schemas
based on their condition (FDs and Primary Keys) to achieve the desirable properties:
1. Firstly, Minimizing redundancy, and
2. Secondly, Minimizing the insertion, deletion update anomalies.
Thus, the normalisation provides the database designer with:
1. A formal framework for analysing relation schemas.
2. A series of normal form tests that can be normalised to any desired degree.
The degree of normal forms to which a relation schema has been normalised through
decomposition confirm the existence of additional properties that the relational schemas should
possess. It could include any or both of two properties.
1. The lossless join and non-additive join property, and
2. The dependency preservation property.
Based on performance reasons, relations may be left in a lower normalisation status. It is not
mandatory that the database designer must normalise to the highest possible normal form. The
process of storing the join of higher normal form relations, as a base relation (which is in a lower
normal form) is known as denormalisation).
7.5 Multi-valued Dependencies
In database modeling using the E-R Modeling technique, we usually face known difficulties that
may arise when an entity has multivalued attributes. In the relational model, if all of the
information about such entity is to be represented in one relation, it will be necessary to repeat
all the information other than the multivalue attribute value to represent all the information. It
will result in multi-tuples about the same instance of the entity in the relation and the relation
having a composite key (the entity id and the mutlivalued attribute). This situation becomes
much worse if an entity has more than one multivalued attributes and these values are represented
in one relation by a number of tuples for each entity instance such that every value of one of the
multivalued attributes appears with every value of the second multivalued attribute to maintain
consistency. The multivalued dependency relates to this problem when more than one
multivalued attributes exist. Let us consider the same through an example relation that represents
an entity ‘employee’.
emp (e#, dept, salary, job)
We have so far considered normalisation based on functional dependencies that apply only to
single-valued facts. For example, e# dept implies only one dept value for each value of e#.
Not all information in a database is single-valued, for example, job in an employee relation may
be the list of all projects that the employee is currently working on. Although e# determines the
list of all the projects that an employee is working on, yet, e# job is not a functional dependency.
The fourth and fifth normal forms deal with multivalued dependencies. Before discussing the
4NF and 5NF we will discuss the following example to illustrate the concept of multivalued
dependency.
programmer (emp_name, projects, languages)
LOVELY PROFESSIONAL UNIVERSITY 109
