- Consider the following a
very simple way
to "design"
a database schema:
- Put every attribute that you need to store into one single (huge) relation.
- Example: The
company database model will become
as follows:
Company(SSN, fname, ..., DNo, DName, ..., PNo, PName, ..., DepName,..)
This relation (containing every attribute is called the universal relation
- So what is so bad about this relation ???
- Early in the
semester, I gave you an
example of a
bad relation:
Name Course Course Name Semester Grade James Bond CS170 Intro to CS Fall 1997 B- James Bond CS255 Assembler programming Fall 1998 B+ James Bond CS355 Computer Architecture Spring 1999 C+ John Doe CS170 Intro to CS Spring 2000 A- John Doe CS255 Assembler programming Fall 2001 C+ John Doe CS355 Computer Architecture Spring 2002 D+ See: click here
I gave you the following reason why the relation was bad:
- Some information is being stored multiple times
- Fact:
- Duplication of
information will
cause
unwanted
anomalies
(The universal relation usually store duplicate information and will therefore have unwanted anomalies)
- Duplication of
information will
cause
unwanted
anomalies
- There are 3 types
of database anomalies:
- Insert operation:
- The
normal behavior :
- The insertion of
one information item
should
result in
inserting of
one tuple
into the database
- The insertion should not need to use NULL values
- The insertion of
one information item
should
result in
inserting of
one tuple
into the database
- The abnormal behavior:
- The insertion of
one information item
should
result in
inserting of
multiple tuple
into the database
- The insertion requires the use NULL values
- The insertion of
one information item
should
result in
inserting of
multiple tuple
into the database
- The
normal behavior :
- Delete operation:
- The
normal behavior :
- The deletion of
one information item
should
result in
deleting
one tuple
from the database
- The deletion should only delete the intended information and must not cause lost of other information
- The deletion of
one information item
should
result in
deleting
one tuple
from the database
- The abnormal behavior:
- The deletion of
one information item
result in
deleting
multiple tuples
from the database
- The deletion can delete other unintended information and result in lost of other information !!!
- The deletion of
one information item
result in
deleting
multiple tuples
from the database
- The
normal behavior :
- Update operation:
- The
normal behavior :
- The update of one information item should result in updating one tuple in the database
- The abnormal behavior:
- The update of one information item result in updating multiple tuples in the database
- The
normal behavior :
- Insert operation:
- Consider the following
"Employee" relation
containing
information on
employees
and the department they work in:
Employee (SSN, FName, LName, BDate, DNum, DName, MgrSSN)
- This relation is used to store information about the employees AND the departments in a company
- Example of
some tuples in this
relation:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777 These tuples represent the following information:
- Employee
111-11-1111
has fname= John and
lname = Smith,
borned
on Jan-1-78, and
work in
dept 5
which is the Research dept
that
is managed
by employee
123-45-6789
- (and so on....)
- Employee
111-11-1111
has fname= John and
lname = Smith,
borned
on Jan-1-78, and
work in
dept 5
which is the Research dept
that
is managed
by employee
123-45-6789
-
Insert Anomaly
in this relation:
- If we insert
a new department,
say:
dnumber=6, dname='Human Resources'that does not have any employees yet, then:
- We must use NULL values
Example:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777 NULL NULL NULL NULL 6 Hum Resources NULL
- If we insert
a new department,
say:
-
Delete Anomaly
in this relation:
Consider:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777 - Suppose we
fire (= delete)
the
employee
"Jack Rabbit"
(who is the
only employee in
Payroll):
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 Result:
- We have lost the all information on the Payroll department in the database !!!!
Here you witness the undesirable (= dangerous) behavior of the delete anomaly:
- The delete operation has deleted
additional information
than was not intended !!!!
(Information on the "Payroll" department is also deleted !!! )
-
Update Anomaly in this
relation:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777
- Suppose
we update
the manager
of the Research
department:
- From the employee 123-45-6789 to the employee 888-88-8888
The correct result is the following database content:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 888-88-8888 222-22-2222 Jane Doe Apr-1-76 5 Research 888-88-8888 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777 Notice:
- Logically speaking,
we update
only one item
of information:
- Change employee 123-45-6789 to employee 888-88-8888
- But physically, the
database has been
changed
multiple times:
- Two value of 123-456-6789 have been changed to 888-88-8888 !!!!!
Consequence of an update anomaly:
- If some tuples are updated while other tuples are not updated, the database will become incosnsistent !!!
Example:
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 888-88-8888 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777 Data inconsistency: the Research department now has two different managers !!!!
- Consider these
2 methods to store
the same information:
- Method 1
(with
anomalies):
SSN FName LName BDate DNum DName MgrSSN 111-11-1111 John Smith Jan-1-78 5 Research 123-45-6789 222-22-2222 Jane Doe Apr-1-76 5 Research 123-45-6789 333-33-3333 Jack Rabbit May-4-79 1 Payroll 777-77-7777
- Method 2: using
2 relations
SSN FName LName BDate DNum 111-11-1111 John Smith Jan-1-78 5 222-22-2222 Jane Doe Apr-1-76 5 333-33-3333 Jack Rabbit May-4-79 1 DNum DName MgrSSN 5 Research 123-45-6789 1 Payroll 777-77-7777 No anomalies !!!
Query:
- Find the
department name of
the employee
John Smith
- Using storage method 1:
- Access only one relation to find department number
- Using storage method 2:
- We must join 2 relations before we can find department number
This way is more difficult and slower...
- Using storage method 1:
Method 1 provide a more efficient (= faster) way to access the information
- Method 1
(with
anomalies):
-
Performance considerations
- A database designer
may
decide to use
"bad" relations
in a database
because:
- "Bad" relations are often more effcient !!!
(This subject is in the area of data warehousing - a separate area from database management)
-
For
performance reasons,
the database adminstrator may
choose
to have
"bad" relations
in the database
But the database administrator must:
- Take precaution to make sure "bad" things (= data inconsistency, data loss, etc) do not happen with the data stored in the database !!!
- A database designer
may
decide to use
"bad" relations
in a database
because:
- A bit of history:
- The relational database model
was introduced by
E.F. Codd
(then working for IBM Zurich) in 1970
E. F. Codd page on Wikipedia: click here
- A couple of years after
Codd published the
relational data model,
he described the
anomalies in the classical paper:
- "Further Normalization of the Data Base Relational Model" - in Data Base System, 1972
This paper started the entire subject of database normalization
- Codd defined
3 "normal forms" in his 1972 paper.
- Each "Normal Form" defines a set of properties
that relations must satisfy.
- When relations possess these properties, they exhibit less anonalies
Codd's 3 NFs are all based on the concept of functional dependency
- Each "Normal Form" defines a set of properties
that relations must satisfy.
- Codd's original definition of the 3rd Normal Form (3NF)
had some problems in a special circumstance.
In 1974, Boyce & Codd revised the 3NF and the new and stronger 3NF is called Boyce-Codd Normal Form or BCNF
- Later,
Fagin introduced the concept of "multi-valued dependencies"
and introduced the 4NF in 1977.
- Fagin (again) introduced the concept of "join dependencies"
and introduced the 5NF (projection-join normal form)
in 1979.
- Relationship among the normal forms:
- The concentrical rectangles represent all possible relations
- The picture says that: a relation that is "in the 5th normal form" will also possess all properties of the 4th normal form, and the BCNF normal form, etc.
- The picture also says that: a relation that is "in the 4th normal form" will also possess all properties of the BCNF normal form, the 3th normal form, etc.
- The relational database model
was introduced by
E.F. Codd
(then working for IBM Zurich) in 1970
- The general ideas of
normalization are:
- Database designer should aim for relations in the "ultimate" 5NF
- However, this recommendation should not be
construed as law
Typically, designers stop the decomposition at 3NF or BCNF
- Designing good database (for a large system) is a complex task.
Normalization is a useful aid in the process.
It should not be a panacea.
- Indeed, sometimes ,
normal forms are violated deliberately
to achieve
better performance (less join operations)
- Database designer should aim for relations in the "ultimate" 5NF
- What is the cause of this "badness" in some relations
We will now study in more detail why some relations are bad
More importantly:
|
|