DATABASE ANOMALIES AND FUNCTIONAL DEPENDENCY

ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 1
Copyright © 2015 – 2018, Victorian Institute of Technology.
The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT,
other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology.
DATABASE ANOMALIES AND FUNCTIONAL
DEPENDENCY
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 2
Topics
• Brief Description of Relation/ Table
• Informal Design Guidelines for Relation Schemas
• Database Design Process
• Mapping EERD model to Relational model
• Primary Key
• Referential Integrity – Foreign Key
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 3
Brief Description of Relation/ Table

Columns / fields
Primary Key Values
rows / tuples /
records
• Example
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 4
Informal Design Guidelines for Relation
Schemas
• We discuss four informal measures of quality for relation
schema design in this section:
– Semantics of the attributes
– Reducing the redundant values in tuples
– Reducing the null values in tuples
– Disallowing the possibility of generating spurious tuples
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 5
Informal Design Guidelines for Relation
Schemas (Cont…)
• Semantics of the attributes
– Whenever we group attributes to form a relation schema, we assume
that attributes belonging to one relation have certain real-world
meaning and a proper interpretation associated with them.
– In other words it specifies, how the attribute values in a tuple relate
to one another.
– In general, the easier it is to explain the semantics of the relation, the
better the relation schema design will be.
– Example
Employee: Ename, SSN, Bdate, Address, Dnumber
Department: Dname, Dnumber, DmgrSSN
– 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
implicit relationship between Employee and Department
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 6
Informal Design Guidelines for Relation
Schemas (Cont…)
• Semantics of the attributes
– 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.
• Intuitively, 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 and relationships, semantic ambiguities will result and
the relation cannot be easily explained.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 7
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples
– One goal of schema design is to minimize the storage space used by
the base relations (and hence the corresponding files). Grouping
attributes into relation schemas has a significant effect on storage
space.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 8
Informal Design Guidelines for Relation
Schemas (Cont…)

• Redundant Information in Tuples
– Consider following sample data
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 9
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples
– Problems with previous database structure
– what happens if,
• We want to insert a new programme name into database.
• All the students in a programme drop out ?
• We want to alter a programme name, in this case we have to check that
– Each and every entry of programme name to be changed are up-to-date or
not (consistent).
– It will lead to following
• An Inconsistency.
• A database structure that will be prone to errors in the data.
• Not necessarily wrong, but will promote erroneous data rather than
prevent it.
• Will usually also involve more effort in programs/users that maintain the
file.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 10
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples and Insert Anomalies
– need to insert new value more than once
• To insert the details of a new student for a particular programme
into the table shown above, we must enter the correct details of
programme and department, so that the programme and
department details are consistent with the values in the other
rows.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 11
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples and Update Anomalies
– Change of value must be done multiple times to each copy of
the value.
– If not done correctly then there will be a multiple copies of the
same value, means we can’t know which is correct.
• If we want to change the address of a particular department in
the table, we must update the all rows having that particular
department. If this modification is not carried out on all the
relevant rows, the database will become inconsistent.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 12
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples and Delete Anomalies
– A value is deleted “accidentally” due to an unrelated event
occurring
• If we delete a row from the table having ProgCode “168” that
represents the last student allocated to program Mechanical then
the details about that programme will also lost from the
Database.
– need to delete value more than once
• If we want to delete the programme “Fine Arts” then we should
delete it at multiple locations.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 13
Informal Design Guidelines for Relation
Schemas (Cont…)
• Redundant Information in Tuples
– 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, overcome it and make
sure that the programs that update the database will operate
correctly.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 14
Informal Design Guidelines for Relation
Schemas (Cont…)
• 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.
– Moreover, nulls can have multiple interpretations, such as the
following:
• The attribute does not apply to this tuple.
• The attribute value for this tuple is unknown.
• The value is known but absent; that is, it has not been recorded
yet.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 15
Informal Design Guidelines for Relation
Schemas (Cont…)

• Reducing the null values in tuples
– Consider following sample data
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 16
Informal Design Guidelines for Relation
Schemas (Cont…)
• Reducing the null values in tuples
– Insert null values or must wait for a un-related event to occur
first
• To insert the details of a new programme that currently has no
students into the table, it is necessary to enter nulls for the
student details but which is not allowed as StudentNo is the
primary key.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 17
Informal Design Guidelines for Relation
Schemas (Cont…)
• Reducing the null values in tuples
– 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.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 18
Informal Design Guidelines for Relation
Schemas (Cont…)
• Disallowing the possibility of generating spurious tuples
– Those unwanted tuples are generated because of join (We are not
covering this topic now)
– Example
Employee: Ename, SSN, Bdate, Address, Dnumber
Department: Dname, Dnumber, DmgrSSN
• Dnumber attribute value in Employee table should match with at
least a single value of Dnumber attribute in Department table.
• In other words, Dnumber attribute contains only those values that
are in the Dnumber attribute of Department table.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 19
Informal Design Guidelines for Relation
Schemas (Cont…)
• Disallowing the possibility of generating spurious tuples
– Guideline 4:
• Avoid relations that contain matching attributes that are not
(foreign key, primary key) combinations
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 20
Informal Design Guidelines for Relation
Schemas (Cont…)
• We informally discussed situations that lead to problematic
relation schemas, and we proposed informal guidelines for a
good relational design. The problems we pointed out are:
– Anomalies that cause redundant work to be done during insertion
into and modification of a relation, and that may cause accidental
loss of information during a deletion from a relation
– Waste of storage space due to nulls.
– Generation of invalid and spurious data during joins (We are not
covering this topic now) on improperly related base relations.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 21
Informal Design Guidelines for Relation
Schemas (Cont…)
• Desired Database Structure when insert, update and delete
occurs.
– INSERT
• Insert once when required in a single table.
– UPDATE
• Update once, in a single table.
• Only one copy of each piece of data.
– DELETE
• Delete once, in a table.
• Deletions should not cause by unrelated events.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 22
Database Design Process
• Functional Dependency
– A functional dependency is a constraint between two sets of
attributes from the database.
– A functional dependency, denoted by X Y, between two sets of
attributes X and Y.
– This means that the values of the Y component of a tuple in r depend
on, or are determined by, the values of the X component.
– Alternatively, the values of the X component of a tuple uniquely (or
functionally) determine the values of the Y component.
– We can also say that there is a functional dependency from X to Y,
or that Y is functionally dependent on X.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 23
Database Design Process (Cont…)
• Types of Functional Dependency
– Trivial functional dependency
– non-trivial functional dependency
– Multivalued dependency
– Transitive dependency
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 24
Database Design Process (Cont…)
• Trivial functional dependency
– The dependency of an attribute on a set of attributes is known as
trivial functional dependency if the set of attributes includes that
attribute.
– Symbolically: A ->B is trivial functional dependency if B is a subset
of A.
– The following dependencies are also trivial: A->A & B->B
– Example
• Student_Id, Student_Name -> Student_Id is a trivial functional
dependency as Student_Id is a subset of Student_Id,
Student_Name.
• Also, Student_Id -> Student_Id & Student_Name ->
Student_Name are trivial dependencies too.
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 25
Database Design Process (Cont…)
• Non trivial functional dependency
– 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.
– For example:
• An employee table with three attributes: emp_id, emp_name,
emp_address.
• The following functional dependencies are non-trivial:
• emp_id -> emp_name (emp_name is not a subset of emp_id)
• emp_id -> emp_address (emp_address is not a subset of
emp_id)
• On the other hand, the following dependencies are trivial:
• emp_id, emp_name -> emp_name [emp_name is a subset of
emp_id, emp_name]
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 26
Database Design Process (Cont…)
• Multivalued dependency
– It occurs when there are more than one independent multivalued
attributes in a table.
– For example: Consider a bike manufacture company, which
produces two colors (Black and white) in each model every year.
• 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
bike_model ->> color
bike_model manuf_year color
M1001 2007 Black
M1001 2007 Red
M2012 2008 Black
M2012 2008 Red
M2222 2009 Black
M2222 2009 Red
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 27
Database Design Process (Cont…)
• Transitive dependency
– A functional dependency is said to be transitive if it is indirectly formed by
two functional dependencies.
– X -> Z is a transitive dependency if the following three functional
dependencies hold true:
• X->Y
• Y does not ->X
• Y->Z
– Example
Book ->Author (if we know the book, we knows the author name)
Author does not ->Book
Author -> Author_age
• Therefore as per the rule of transitive dependency:
Book -> Author_age should hold, that makes sense because if we know
the book name we can know the author’s age.
Book Author Author_age
Game of Thrones George R. R. Martin 66
Harry Potter J. K. Rowling 49
Dying of the Light George R. R. Martin 66
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 28
Database Design Process (Cont…)
• Functional Dependency Example
– If field A is dependent on field B,
• A cannot exist until B exists.
– Surname, Given, DOB and Email
• depend on Student No
– What does ProgName and DeptName
• depend on ????
STUDENT
Surname
Given
DOB
Email
ProgCode
ProgName
DeptName
Address
StudentNo
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 29
Database Design Process (Cont…)
• Functional Dependency Example
– The field Surname stores surnames.
– Every person in the city has a surname. Would you put all those
surnames in your database ?
– We say that surname depends on student number, because a
surname value would not exist unless a corresponding student
number existed.
– A field must depend on the key, the whole key, and nothing but the
key,
• Every field in a table should be dependent on the whole primary
key
• If it is not, it should be in another table !
• If there is no other table to fit it, create a new table!!
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 30
Database Design Process (Cont…)
• Data Design Aims/Steps
– Identify Entities/Tables
– Put Fields where they belong – where they depend on the whole
primary key.
• If a field cannot be placed, usually signifies a missing entity/table.
– ELIMINATE ANOMALIES
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 31
Database Design Process (Cont…)
STUDENT
Surname
Given
DOB
. . . .
ProgCode
PROGRAMME
ProgName
DeptName
StudentNo ProgCode
• Is this is a solution?
• Like most design activities, data design is not an exact
science. Performing it involves experience and there is no
single answer for any problem.
• For small tasks, I encourage you to use an “intuitive”,
bit by bit approach but may not work for large tasks!
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 32
Database Design Process (Cont…)
• Go through the problem specification (if there is one),
picking out any possible attribute/field etc. you think might
be important. – Create a big list.
– Work on both an E-R model and a Relational model together, at the
same time!
• Make sure they always correspond.
• Start by identifying any “easy” entities.
– People are usually easy (STAFF, STUDENT, CUSTOMER etc.)
– Fill in the obvious attributes.
– Look for straightforward relationships, keeping both E-R model and
relational model corresponding (relationships in E-R become foreign
keys!)
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 33
Database Design Process (Cont…)
• Continue an iterative process until you have filled in as
much as required.
• Part of the design process is deciding what will not be
stored. You have to define the scope of the database.
• Continually ensure fields are dependent on their keys and
there are no anomalies.
• Ask yourself for each field you place in a table:
– What happens if I insert ?
– What happens if I update ?
– What happens if I delete ?
– Does this field belong here ?
– Does this field depend on the whole key?
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 34
Mapping EERD model to Relational model
• There are FOUR options
– Option 1 – Create table having field status (not good if sub entities
have attributes).
– Option 2 – Create table with extra fields for each sub entities (not
work if sub entities have attributes).
– Option 3 – Create table with all the attributes of super and sub
entities (not good for managing space).
– Option 4 – Create three tables and connect them using foreign key
references (the best option if sub entities and support entity has
attributes).
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 35
Option 1 – Create one table
Student(StudentNo, Sname, DOB, email, Status)
This option is good since sub entities and super entities are free with
attributes
Status attribute could store “grad” for graduate students and ”post” for post
graduate students
studentNo
studentName DOB
email

gradstudent postgradstudent
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 36
Option 2 – Create one table
Student(StudentNo, Sname, DOB, email, GradStud, PostGrad)
This option is good since sub entities and super entities are free with attributes
For GradSud and PostGrad columns could be indicated with ”Y” if based on
the Student group
studentNo
studentName DOB
email

gradstudent postgradstudent
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 37
Option 3 – Create one Table
Student(StudentNo, Sname, DOB, email, gpa, thesisTitle)
This option is good since sub entities and super entities are limited with some
attributes.
studentNo
studentName DOB
email

GPA Thesis Title
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 38
Option 4 – Having multiple tables
Student(StudentNo, Sname, DOB, email)
PostGradStudent(StudentNo, ThesisTitle) and GradStudent(StudentNo, gpa)
This option is good when super entity and sub entities have attribute.
studentNo
studentName DOB
email

GPA Thesis Title
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 39
Primary Key
• There should be a proper way of uniquely identifying records
of a table and their relationships
• Entity Integrity : Primary Key
– Each table has a column with unique values
– There cannot be missing values (No Values – NULL)
– This ensures traceable entries in a table
• Example: In the Students table (previous slide) the StdNo is
unique.
• Composite Primary key – Primary Key consists of more than
one field
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 40
Referential Integrity – Foreign Key
• Values of a column in one table match values of a source
table
• This ensures valid references among tables (there is a valid
connection)
• Values in foreign key must exist in primary key of related file.
• Example – StudentNo in the GradStudent and PostGradStudent
table must match StudentNo of Student table.
STUDENT
StudentNo
sname
DOB
email
GradStudent
StudentNo
gpa
PostGradStudent
StudentNo
ThesisTitle
ITDA1001 [Lesson 3] Copyright © 2018 VIT, All Rights Reserved 41
Summary
• Revision of Key Concepts
• Class Work
– Lesson 3 – Activity 3 (Lesson_3 Activity.docx)
• Questions and Answer

The post DATABASE ANOMALIES AND FUNCTIONAL DEPENDENCY appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms