Database Management Systems(BCS403)

MODULE 3
 Module – 3 Introduction to SQL
Contents:
Database Design Theory
 Introduction.
 Informal design guidelines for relation schema.
 Functional Dependencies.
Normalization
 Introduction to Normalization.
 Normal Forms based on Primary Keys.
 Second and Third Normal Forms.
 Boyce-Codd Normal Form.
 Multivalued Dependency and Fourth Normal Form.
 Join Dependencies and Fifth Normal Form.
 Examples on normal forms.
 Module – 3 Introduction to SQL
Contents:
Introduction to SQL
 SQL data definition and data types
 Specifying constraints in SQL
 Retrieval queries in SQL.
 INSERT, DELETE, and UPDATE statements in SQL.
 Schema change statements in SQL.
 Additional features of SQL.
Module – 3 Database Design Theory
Introduction
There are two approaches for database design
Bottom-up approach:
 A bottom-up design methodology (also called design by synthesis)
considers the basic relationships among individual attributes as the
starting point and uses those to construct relation schemas.
 This approach is not very popular in practice because it suffers from
the problem of having to collect a large number of binary
relationships among attributes as the starting point. For practical
situations, it is next to impossible to capture binary relationships
among all such pairs of attributes.
Top-down approach:
 Atop-down design methodology (also called design by analysis)
starts with a number of groupings of attributes into relations that
exist together naturally.
Module – 3 Database Design Theory
Introduction
 The relations are then analyzed individually and collectively, leading
to further decomposition until all desirable properties are met.
Two implicit goals of the database design activity are
1. Information preservation.
2. Minimum redundancy.
Information preservation:
 Information preservation in terms of maintaining all concepts,
including attribute types, entity types, and relationship types as well
as generalization/specialization relationships, which are described
using a model such as the EER(Enhanced-ER) model.
 The relational design must preserve all of these concepts, which are
originally captured in the conceptual design after the conceptual to
logical design mapping.
Module – 3 Database Design Theory
Introduction
Minimum redundancy.
 Minimizing redundancy implies minimizing redundant storage of the
same information and reducing the need for multiple updates to
maintain consistency across multiple copies of the same information
in response to real-world events that require making an update.
Specialization
 Specialization is the process of defining a set of subclasses of an
entity type; this entity type is called the superclass of the
specialization.
 The set of subclasses that forms a specialization is defined on the
basis of some distinguishing characteristic of the entities in the
superclass.
Module – 3 Database Design Theory
Introduction
For example, the set of subclasses {SECRETARY, ENGINEER,
TECHNICIAN} is a specialization of the superclass EMPLOYEE that
distinguishes among employee entities based on the job type of each
employee.

Figure: 1 Instances of a specialization.

Module – 3 Database Design Theory
Introduction
Generalization
We can think generalization as a reverse process of abstraction in which
we suppress the differences among several entity types, identify their
common features, and generalize them into a single superclass of which
the original entity types are special subclasses.

Figure: 2 (a) Two entity types, CAR and TRUCK

Module – 3 Database Design Theory
Introduction

Figure: 3 (b) Generalizing CAR and TRUCK into the superclass VEHICLE.
Note: Functional dependency and a formal constraint among attributes
that is the main tool for formally measuring the appropriateness of
attribute groupings into relation schemas.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
There are four informal guidelines that may be used as measures to
determine the quality of relation schema design:
1. Making sure that the semantics of the attributes is clear in the
schema.
2. Reducing the redundant information in tuples.
3. Reducing the NULL values in tuples.
4. Disallowing the possibility of generating spurious tuples.

Imparting Clear Semantics to Attributes in Relations

 Attributes belonging to one relation should have certain real-world
meaning and a proper interpretation associated with them.
 The semantics of a relation refers to its meaning, which is resulting
from the interpretation of attribute values in a tuple.
 Consider a simplified COMPANY relational database schema…..
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
The meaning of the
EMPLOYEE relation schema is
simple:
 Each tuple represents an
employee, with values for the
employee‟s name (Ename),
Social Security number (Ssn),
birth date (Bdate), and address
(Address), and the number of
the department that the
employee works for
(Dnumber).
 The Dnumber attribute is a
foreign key that represents an Figure: 4
implicit relationship between
EMPLOYEE and
DEPARTMENT.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
 From the above example COMPANY database, we found that it is
very easy to explain the meaning of each attribute in each relation.
 Thus the following informal guideline can be formulated..
Guideline 1:
 Design a relation schema so that it is easy to explain its meaning.
 Do not combine attributes from multiple entity types and relationship
types into a single relation.
 If a relation schema corresponds to one entity type or one
relationship type, it is straightforward to explain its meaning.
 Otherwise, if the relation corresponds to a mixture of multiple
entities types and relationships, semantic ambiguities will result and
the relation cannot be easily explained.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Consider the following EMP_DEPT schema which has sematic
ambiguity since it combines attributes from more than one entity
types.

 A tuple in the EMP_DEPT relation schema shown above represents

a single employee but includes, along with the Dnumber (the
identifier for the department he/she works for), additional
information—namely, the name (Dname) of the department for
which the employee works and the Social Security number
(Dmgr_ssn) of the department manager.
 This violate Guideline 1 by mixing attributes from distinct real-
world entities: EMP_DEPT mixes attributes of employees and
departments.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
 Similarly the following EMP_PROJ schema which has sematic
ambiguity since it combines attributes from more than one entity
types.

 This violate Guideline 1 by mixing attributes different relations.

 EMP_PROJ mixes attributes of employees and projects and the
WORKS_ON relationship.
 Hence, they fare poorly against the above measure of design quality.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Redundant Information in Tuples and Update Anomalies
 One goal of schema design is to minimize the storage space used by
the base relations(tables stored as files in storage media).
 Consider the following state of EMP_DEPT relation which has
redundant data since it combines attributes(natural join of base
relations) from more than one entity types.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Storing natural joins of base relations leads to an additional problem
referred to as update anomalies.
Update anomalies can be classified into
1. Insertion anomalies.
2. Deletion anomalies.
3. Modification anomalies.
Insertion anomalies
By considering EMP_DEPT schema insertion anomalies occurs in two
cases;
Case – 1: It is difficult to insert a new department that has no
employees as yet in the EMP_DEPT relation. The only way to do this is
to place NULL values in the attributes for employee. This violates the
entity integrity for EMP_DEPT because its primary key Ssn cannot be
null.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Case – 2: Consistency problem. For example, to insert a new tuple for
an employee who works in department number 5, we must enter all the
attribute values of department 5 correctly so that they are consistent
with the corresponding values for department 5 in other tuples in
EMP_DEPT.
Deletion Anomalies.
 If we delete an employee (he/she is the only one employee working
for particular department) from EMP_DEPT, then we loose the
information concerning that department from the database.
 This problem does not occur in the database of Figure 4 because
DEPARTMENT tuples are stored separately.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Deletion Anomalies.
 If we delete an employee (he/she is the only one employee working
for particular department) from EMP_DEPT, then we loose the
information concerning that department from the database.
 This problem does not occur in the database of Figure 4 because
DEPARTMENT tuples are stored separately.
Figure 4
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Modification Anomalies:
 In EMP_DEPT, if we change the value of one of the attributes of a
particular department say, the manager of department 5,we must
update the tuples of all employees who work in that department;
otherwise, the database will become inconsistent.
 If we fail to update some tuples, the same department will be shown
different managers for different employee in the same department,
which would be wrong.
 The three anomalies mentioned above are undesirable and cause
difficulties to maintain consistency of data as well as require
unnecessary updates.
 Hence the following guideline can be formulated…..
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Guideline 2:
 Design the base relation schemas so that no insertion, deletion, or
modification anomalies are present in the relations.
 If any anomalies are present, note them clearly and make sure that
the programs that update the database will operate correctly.
NULL Values in Tuples
 In some schema designs we may group many attributes together into
a “fat” relation.
 If many of the cases some attributes do not apply to all tuples in the
relation, we end up with many NULLs in those tuples.
 This can waste space at the storage level and may also lead to
problems with understanding the meaning of the attributes and with
specifying JOIN operations at the logical level.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
 Another problem with NULLs is how to account for them when
aggregate operations such as COUNT or SUM are applied.
 SELECT and JOIN operations involve comparisons; if NULL values
are present, the results may become unpredictable.
 Hence the following guideline can be formulated…..
Guideline 3:
 As far as possible, avoid placing attributes in a base relation whose
values may frequently be NULL.
 If NULLs are unavoidable, make sure that they apply in exceptional
cases only and do not apply to a majority of tuples in the relation.
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Generation of Spurious Tuples
 Spurious Tuples are those rows in a table, which occur as a result of
joining two tables in the wrong manner.
 They are extra tuples (rows) that might not be required.

Relation EMP_PROJ is
decomposed into two relations
EMP_LOCS and EMP_PROJ1
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
 Suppose that we used EMP_PROJ1 and EMP_LOCS as the base
relations instead of EMP_PROJ and attempt a NATURAL JOIN
operation on EMP_PROJ1 and EMP_LOCS, the result produces
many more tuples than the original set of tuples in EMP_PROJ.
 Additional tuples that were not in EMP_PROJ are called spurious
tuples.
 Decomposing EMP_PROJ into EMP_LOCS and EMP_PROJ1 is
undesirable because when we JOIN them back using NATURAL
JOIN, we do not get the correct original information.
 This is because in this case Plocation is the attribute that relates
EMP_LOCS and EMP_PROJ1, and Plocation is neither a primary
key nor a foreign key in either EMP_LOCS or EMP_PROJ1.
 Hence the following guideline can be formulated…..
Module – 3 Database Design Theory
Informal Design Guidelines for Relation Schemas
Guideline 4:
 Design relation schemas so that they can be joined with equality
conditions on attributes that are appropriately related (primary key,
foreign key) pairs in a way that guarantees that no spurious tuples
are generated.
 Avoid relations that contain matching attributes that are not (foreign
key, primary key) combinations because joining on such attributes
may produce spurious tuples.
Module – 3 Database Design Theory
Normal Forms Based on Primary Keys
 Each relation is associated with set of functional dependencies and
has designated primary key.
 This information is used to test for normal forms which drives
normalization process for relational schema design.
Most practical relational design projects take one of the following
two approaches:
1. Perform a conceptual schema design using a conceptual model such
as ER or EER and map the conceptual design into a set of relations.
2. Design the relations based on external knowledge derived from an
existing implementation of files or forms or reports.
Once the relations are designed using any one of the above approaches,
evaluate the relations for goodness and decompose them further as
needed to achieve higher normal forms using the normalization process.
Functional Dependencies
Module – 3 Database Design Theory
Normalization of Relations
 The normalization process was first proposed by Codd (1972).
 Normalization process takes a relation schema through a series of
tests to certify whether it satisfies a certain normal form.
 The process, which proceeds in a top-down fashion by evaluating
each relation against the criteria for normal forms and decomposing
relations as necessary.
 Thus this process can be considered as relational design by
analysis.
Module – 3 Database Design Theory
Normalization of data
 This can be considered a process of analyzing the given relation
schemas based on their FDs and primary keys to achieve the
following goals;
1. Minimizing redundancy.
2. Minimizing the insertion, deletion, and update anomalies.
 It can be considered as a “filtering” or “purification” process to make
the design have successively better quality.
 An unsatisfactory relation schema that does not meet the
condition for a normal form then that is decomposed into
smaller relation schemas that contain a subset of the attributes and
meet the test for normal form.
Module – 3 Database Design Theory
Normalization of data
Thus, the normalization procedure provides database designers
with the following:
1. A formal framework for analyzing relation schemas based on
their keys and on the functional dependencies among their
attributes
2. A series of normal form tests that can be carried out on
individual relation schemas so that the relational database can be
normalized to any desired degree.

Definition. The Normal Form(NF) of a relation refers to the highest

normal form condition that it meets, and hence indicates the degree to
which it has been normalized.
Module – 3 Database Design Theory
Normalization of data
Each normalized relation should meet the following two properties:
1. The nonadditive join or lossless join property, which
guarantees that the spurious tuple generation problem does not
occur with respect to the relation schemas created after
decomposition.
2. The dependency preservation property, which ensures that
each functional dependency is represented in some individual
relation resulting after decomposition.
Module – 3 Database Design Theory
Definition:
 An attribute of relation schema R is called a prime attribute of R if
it is a member of some candidate key of R.
 An attribute is called nonprime attribute if it is not a prime
attribute, that is, if it is not a member of any candidate key.
First Normal Form(1NF)
 1NF was defined to disallow multivalued attributes, composite
attributes, and their combinations.
 It states that the domain of an attribute must include only atomic
(simple, indivisible) values and that the value of any attribute in a
tuple must be a single value from the domain of that attribute.
 Hence, 1NF disallows having a set of values, a tuple of values, or a
combination of both as an attribute value for a single tuple.
Module – 3 Database Design Theory
First Normal Form(1NF)

The relation DEPARTMENT is not in 1NF because Dlocations is not

an atomic attribute.
Module – 3 Database Design Theory
First Normal Form(1NF)
There are two ways we can look at the Dlocations attribute:
1. The domain of Dlocations contains atomic values, but some tuples
can have a set of these values. In this case, Dlocations is not
functionally dependent on the primary key Dnumber.
2. The domain of Dlocations contains sets of values and hence is
nonatomic. In this case, Dnumber → Dlocations because each set is
considered a single member of the attribute domain.
There are three main techniques to achieve first normal form for
such a relation:
Technique – 1:
 Remove the attribute Dlocations that violates 1NF and place it in a
separate relation DEPT_LOCATIONS along with the primary key
Dnumber of DEPARTMENT.
Module – 3 Database Design Theory
First Normal Form(1NF)
 The primary key of this newly formed relation is the combination
{Dnumber, Dlocation}.
 A distinct tuple in DEPT_LOCATIONS exists for each location of
a department.
 This decomposes the non-1NF relation into two 1NF relations.
Module – 3 Database Design Theory
First Normal Form(1NF)
Technique – 2:
 Expand the key so that there will be a separate tuple in the original
DEPARTMENT relation for each location of a DEPARTMENT.
 In this case, the primary key becomes the combination {Dnumber,
Dlocation}.
 This solution has the disadvantage of introducing redundancy in
the relation and hence is rarely adopted.
Module – 3 Database Design Theory
First Normal Form(1NF)
Technique – 3:
 If a maximum number of values is known for the attribute, for
example, if it is known that at most three locations can exist for a
department, then replace the Dlocations attribute by three atomic
attributes: Dlocation1, Dlocation2, and Dlocation3.
 This solution has the disadvantage of introducing NULL values if
most departments have fewer than three locations.
 It further introduces spurious semantics about the ordering among
the location values; that ordering is not originally intended.
Dname Dnumber Dmgr_ssn Dlocation-1 Dlocation-2 Dlocation-3
Module – 3 Database Design Theory
First Normal Form(1NF)
 First normal form also disallows nested relations because each
tuple can have a relation within it.

 EMP_PROJ(Ssn, Ename, {PROJS(Pnumber, Hours)})

 In the above schema relation PROJS(Pnumber, Hours) within each
tuple represents the employee‟s projects and the hours per week that
employee works on each project.

 Ssn is the primary key of the EMP_PROJ relation, whereas

Pnumber is the partial key of the nested relation;
Module – 3 Database Design Theory
First Normal Form(1NF)
Module – 3 Database Design Theory
First Normal Form(1NF)
To normalize the relation EMP_PROJ into 1NF,
 Remove the nested relation attributes into a new relation and
propagate the primary key into it,
 The primary key of the new relation will combine the partial key
with the primary key of the original relation.
Module – 3 Database Design Theory
First Normal Form(1NF)
Summary:
 A relation will be 1NF if it contains an atomic value. It states that an
attribute of a table(relation) cannot hold multiple values. It must hold
only single-valued attribute.
 First normal form disallows the multi-valued attribute, composite
attribute, and their combinations.
 First normal form disallows nested relations.
 Module – 3 Database Design Theory
Full functional dependency
 A functional dependency X → Y is a full functional dependency if
removal of any attribute A from X means that the dependency does
not hold anymore;
 That is, for any attribute A ε X, (X − {A}) does not functionally
determine Y.
 Example: {Ssn, Pnumber} → Hours is a full dependency (neither Ssn
→ Hours nor Pnumber → Hours holds).
 Partial functional dependency
 A functional dependency X → Y is a partial dependency if some
attribute A ε X can be removed from X and the dependency still holds;
that is, for some A ε X, (X − {A}) → Y.
 Example: The dependency {Ssn, Pnumber} → Ename is partial
because Ssn → Ename holds (mean without Pnumber, Ename can be
determined by using Ssn).
Module – 3 Database Design Theory
Second Normal Form(2NF)
 Second normal form (2NF) is based on the concept of full functional
dependency.
Definition: A relation schema R is in 2NF if every nonprime attribute
A in R is fully functionally dependent on the primary key of R.

Figure: Relation EMP_PROJ is

in 1NF but not in 2NF, so this
relation is decomposed as EP1,
EP2 and EP3 which satisfies 2NF
definition/test condition.
Module – 3 Database Design Theory
Second Normal Form(2NF)
 The relation EMP_PROJ is in 1NF but not in 2NF because
functional dependencies FD2 and FD3 violates 2NF, Ename can be
functionally determined by only Ssn, and both Pname and Plocation
can be functionally determined by only Pnumber.
 Means these three non prime attributes(Ename, Pname and
Plocation) are not fully functionally dependent on prime
attributes(Ssn and Pnumber) of relation EMP_PROJ.
 To convert into 2NF, decompose the relation such that all non prime
attributes should fully dependent on prime attribute of a relation.
 Prime attributes in DBMS are identified through primary key
components and functional dependencies.
 A non-prime attribute instead of depending on entire Candidate
key, depends on part of it.
Module – 3 Database Design Theory
Transitive dependency
 A functional dependency X → Y in a relation schema R is a
transitive dependency if there exists a set of attributes Z in R that is
neither a candidate key nor a subset of any key of R(super key),
and both X→ Z and Z → Y hold.
 The dependency Ssn → Dmgr_ssn is transitive through Dnumber in
EMP_DEPT.
 Means, ssn  Dnumber, Dnumber  Dmgr_SSn

Figure: Relation EMP_DEPT is

not in 3NF because this has
transitive functional dependency.
Module – 3 Database Design Theory
Third Normal Form(3NF)
 Third normal form (3NF) is based on the concept of transitive
dependency.
Definition: According to Codd‟s original definition, a relation schema
R is in 3NF if it satisfies 2NF and no nonprime attribute of R is
transitively dependent on the primary key.
Module – 3 Database Design Theory
Summary
Normal Form Test Remedy (Normalization)
First (1NF) Relation should have no multivalued Form new relations for each
attributes or nested relations. multivalued attribute or nested
relation.
Second (2NF) For relations where primary key contains Decompose and set up a new
multiple attributes, no nonkey attribute relation for each partial key
should be functionally dependent on a part with its dependent attribute(s).
of the primary key. Make sure to keep a relation
with the original primary key
and any attributes that are fully
functionally dependent on it.
Third (3NF) Relation should not have a nonkey attribute Decompose and set up a
functionally determined by another nonkey relation that includes the
attribute (or by a set of nonkey attributes). nonkey attribute(s) that
That is, there should be no transitive functionally determine(s) other
dependency of a nonkey attribute on the nonkey attribute(s).
primary key.
Module – 3 Database Design Theory
General definitions of 2NF and 3NF
Module – 3 Database Design Theory