Unit 4

Relational database design

Informal Design Guidelines for Relational
Schemas:
• Informal guidelines that may be used as measures to determine that
quality of relation schema design are listed below:
Making sure that the semantics of the attributes is clear in the
schema.
Reducing the redundant information in tuples.
Reducing the NULL values in tuples.
Disallowing the possibility of generating spurious(false) tuples.
Making sure that the semantics of the attributes is
clear in the schema.
• Whenever we are going to form relational schema there should be some
meaning among the attributes. This meaning is called semantics.T his
Semantics relates one attribute to another with some relation.
Guideline:
• Informally,each tuple in a relation should represent one entity or
relationship instance.
• Attributes of different entities(relations )Should not be mixed in the same
relation.
• Only foreign key should be used to refer to other entities.
• Entity and relationship attributes should be kept apart as much as possible.
Reducing the redundant information in tuples
• If a table containing attributes of multiple entities may cause
redundant information problems. Information is stored redundantly
wasting storage. Problems with update anomalies.
• Insertion anomalies
• Deletion anomalies
• Modification anomalies
• We can reduce redundant information problem by using
normalization. Normalization is the process of reducing a single table
into a multiple simple tables.
Reducing the NULL values in tuples
• In some schema designs we may group many attributes together into
a “fat” relation . If many of the 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.
• Reasons for nulls:
• Attribute not applicable or invalid.
• Attribute value unknown .
• Value known to exit, but unavailable.
Disallowing the possibility of generating
spurious tuples
• Bad design for a relational database may result in erroneous results
for certain JOIN operations the “lossless join "property is used to
guarantee meaningful results for join operations. The relations should
be designed to satisfy the lossless join condition. No spurious tuples
should be generated by doing a natural join of any relations.
• Design relation schemas so they can be joined with equality
conditions on attributes that are appropriately related pairs in a way
that guarantees that no spurious tuples are generated.
Functional Dependencies:
• Functional dependency plays a key role in differentiating good database design
from bad database design . Functional dependency is a relationship that exists
when one attribute uniquely determines another attribute. If R is a relation with
attribute x and y , a functional dependency between the attributes is represented
as X Y which specifies Y is a functionality dependent on X . Here X is a
determinate set an Y is a dependent attribute. Each values of X is associated with
precisely one Y value.
• Functional dependency in a database serves as a constraint between two sets of
attributes. Defining functional dependency is an important part of relational
database design and contributes to aspect normalization.Eg suppose we have a
student table with attributes: Stuid,stuname,stuage.
• Here stuid attribute uniquely identifies the stuname attribute of student table
because if we know the student id we can tell the student name associated with
it.so this is called functional dependency.
• Eg X Y where X is determinant and Y is Dependent Attribute.
Advantages of Functional Dependencies

• 1. Data Normalization
• Data normalization is the process of organizing data in a database in
order to minimize redundancy and increase data integrity. Functional
dependencies play an important part in data normalization. With the
help of functional dependencies we are able to identify the primary
key, candidate key in a table which in turns helps in normalization.
• 2. Query Optimization
• With the help of functional dependencies we are able to decide the
connectivity between the tables and the necessary attributes need to
be projected to retrieve the required data from the tables. This helps
in query optimization and improves performance.
• 3. Consistency of Data
• Functional dependencies ensures the consistency of the data
by removing any redundancies or inconsistencies that may exist in
the data. Functional dependency ensures that the changes made in
one attribute does not affect inconsistency in another set of
attributes thus it maintains the consistency of the data in database.
• 4. Data Quality Improvement
• Functional dependencies ensure that the data in the database to be
accurate, complete and updated. This helps to improve the overall
quality of the data, as well as it eliminates errors and inaccuracies
that might occur during data analysis and decision making, thus
functional dependency helps in improving the quality of data in
database.
Types of functional dependency
• Trivial and Non-trivial dependencies.
• Fully function dependency
• Partial function dependency
• Transitive dependency
• Multivalued dependency.
Trivial and Non-trivial dependencies
• The dependency of an attribute on a set of attributes is known as
trivial functional dependency if the set of attributes includes that
attributes. Symbolically:A->B is trivial functional dependency if B is a
subset of A.The following dependencies are also trivial:
•A A
•B B

• If a functional dependency X Y holds true where Y is not a subset of

X then this dependency is called non trivial functional dependency.
Fully function dependency
• An attribute is fully functionally dependent on a second attribute if
and only if it is functionally dependent on the second attribute but
not on any subset of the second attribute. Mathematically, for a
relation schema R, in a functional dependency X Y, Y is said to be
fully functional dependent on X if Z Y is false for all Z ⊂ X.
OR
• In full functional dependency an attribute or a set of attributes
uniquely determines another attribute or set of attributes. If a
relation R has attributes X, Y, Z with the dependencies X->Y and X->Z
which states that those dependencies are fully functional.
Partial function dependency

• An attribute is partial functionally dependent on a second attribute if

and only if it is functionally dependent on the second attribute and
also dependency occur on any subset of the second attribute.This is
the situation that exists if it is necessary to only use a subset of the
attributes of the composite determinant to identify its
object.Mathematically,for a relation schema R, in a functional
dependency X Y, Y is said to be partial functional dependent on X if
by removal of some attributes from X and the dependency still holds.
• Example:The dependency {EmpId,Projectno} {EmpName} is partial
because EmpId EmpName also holds.
Transitive dependency
• A functional dependency is said to be transitive if it is indirectly formed by
two functional dependencies.For example,X Z is a transitive dependency if
the following three functional dependencies hold true:
•X Y
• Y X MovieID ListingID ListingType DVD_Price

• Y Z M08 L09 Crime 180

M03 L05 Drama 250
M05 L09 Crime 180

• MovieID ListingID
• ListingID ListingType
• Also,MovieID ListingType
Multivalued dependency
• Multivalued dependency occurs when there are more than one
independent multivalued attributes in a table.For examples:Consider a bike
manufacture company, which produces two colors(Black and White)in each
model every year.
Bike model Manuf year Color
M1001 2007 Black
M1001 2007 Red
M2012 2008 Black
M2012 2008 Red
M2222 2009 Black

• Here columns manuf year and color are independent of each other and
dependent on Bike model.In this case these two columns are said to be
multivalued dependent on Bike model.These dependencies can be
represented like this: Bike model>>Manuf year.
Properties of Functional Dependency
• Reflexivity:If Y is a subset of X, then X Y holds by reflexivity rule.
• Agumentation:If X Y is valid, then , then XZ YZ is also valid.
• Transitivity:If X Y and Y Z both are valid,then X Z is also valid by this
rule.
• Union:If X Y and X Z then X YZ is also valid.
• Decomposition:If X YZ is valid, then X Y and X Z is also valid.
Normal Forms Based on Primary Keys
• The normal forms first, second and third are called primary key based
normal forms. Here each of the non-prime attributes of the relation
depends upon the prime attribute of the relation.
• First Normal Form: If a relation contain composite or multi-valued
attribute , it violates first normal form or a relation is in first normal
form if it does not contain any composite or multi-valued attribute.
• A relation R is in 1NF if it holds following two properties:
If R has no multivalued attributes.
If R has no composite attributes i.e it must have simple attributes.
General Definitions of Second and Third
Normal Forms:
• Second normal forms: A relation R(table) is said to be in 2NF if both
the following conditions hold:
Table is in 1NF.
No non-prime attributes of R fully dependents upon the proper
subset of any candidate key of table.
Third Normal Forms:
• A table design is said to be in 3NF if both the following conditions
hold:
Table must be in 2NF.
No transitive functional dependency between the attributes of given
table.
Boyce-Codd Normal Form:
• It is an advance version of 3NF that’s why it is also referred as
3.5NF.BCNF is stricter than 3NF.A table complies with BCNF if it is in
3NF and for every functional dependency X Y, X should be the super
key of the table.
Fourth Normal Form:
• A table R is in fourth normal form if and only if it satisfied following
two conditions simultaneously:
R is already in BCNF or in 3NF.
If it contains no multi-valued attributes.
The goal of fourth normal form is to eliminate nontrivial multi-valued
dependencies from a table by projecting them onto separate smaller
tables,thus eliminating the update anomalies associated with the multi-
valued dependencies.
Decomposition:
• Decomposition is the process of breaking down in parts or
elements.It replaces a relation with a collection of smaller relations.It
breaks the table into multiple tables in a database.It should always be
lossless,because it confirms that the information in the original
relation can be accurately reconstructed based on the decomposed
relations.If there is no proper decomposition of the relation, then it
may lead to problems like loss of information.
Properties of Relational Decomposition:
• Lossless Decomposition
• Dependency Preservation
• Lack of Data Redundancy.
Lossless Decomposition
• Decomposition must be lossless. It means that the information should
not get lost from the relation that is decomposed. It gives a guarantee
that the join will result in the same relation as it was decomposed.
Dependency Preservation
• Dependency is an important constraint on the database . Every
dependency must be satisfied by at least one decomposed table.
• If {A B} holds , then two sets are functional dependent . And, it
becomes more useful for checking the dependency easily if both sets
in a same relation.
• This decomposition property can only be done by maintaining the
functional dependency.
• In this property it allows to check the updates without computing the
natural join of the database structure.
Lack of Data Redundancy
• Lack of Data Redundancy is also known as a Repetition of
Information.
• The proper decomposition should not suffer from any data
redundancy.
• The careless decomposition may cause a problem with the data.
• The lack of data redundancy property may be achieved by
Normalization process.