dbms study material1

8/3/2019 Dbms Study Material1

1/42

1. What is database?A database is a logically coherent collection of data with some inherent meaning,representing some aspect of real world and which is designed, built and populatedwith data for a specific purpose.

2. What is DBMS?It is a collection of programs that enables user to create and maintain a database.In other words it is general-purpose software that provides the users with theprocesses of defining, constructing and manipulating the database for variousapplications.

3. What is a Database system?The database and DBMS software together is called as Database system.

4. Advantages of DBMS? Redundancy is controlled. Unauthorized access is restricted.

Providing multiple user interfaces. Enforcing integrity constraints. Providing backup and recovery.

5. Disadvantage in File Processing System? Data redundancy and inconsistency. Difficulty in accessing data. Data isolation. Data integrity. Concurrent access is not possible. Security Problems.6. Describe the three levels of data abstraction?

The are three levels of abstraction: Physical level: The lowest level of abstraction describes how data are stored. Logical level: The next higher level of abstraction, describes what data are storedin database and what relationship among those data.

View level: The highest level of abstraction describes only part of entire database.

11. What is Data Independence?Data independence means that the application is independent of the storagestructure and access strategy of data. In other words, The ability to modify theschema definition in one level should not affect the schema definition in the nexthigher level.

Two types of Data Independence:

Physical Data Independence: Modification in physical level should not affect thelogical level. Logical Data Independence: Modification in logical level should affect the viewlevel.NOTE: Logical Data Independence is more difficult to achieve

12. What is a view? How it is related to data independence?


2/42

A view may be thought of as a virtual table, that is, a table that does not really existin its own right but is instead derived from one or more underlying base table. Inother words, there is no stored file that direct represents the view instead adefinition of view is stored in datadictionary.Growth and restructuring of base tables is not reflected in views. Thus the view can

insulate users from the effects of restructuring and growth in the database. Henceaccounts for logical data independence.13. What is Data Model?A collection of conceptual tools for describing data, data relationships,datasemantics and constraints.14. What is E-R model?

This data model is based on real world that consists of basic objects called entitiesand of relationship among these objects. Entities aredescribed in a database by a set of attributes.15. What is Object Oriented model?

This model is based on collection of objects. An object contains values stored ininstance variables with in the object. An object also contains

bodies of code that operate on the object. These bodies of code are called methods.Objects that contain same types of values and thesame methods are grouped together into classes.

16. What is an Entity?It is a 'thing' in the real world with an independent existence.

17. What is an Entity type?It is a collection (set) of entities that have same attributes.

18. What is an Entity set?It is a collection of all entities of particular entity type in the database.

19. What is an Extension of entity type?The collections of entities of a particular entity type are grouped together into anentity set.

20. What is Weak Entity set?An entity set may not have sufficient attributes to form a primary key, and itsprimary key compromises of its partial key and primary key ofits parent entity, then it is said to be Weak Entity set.

21. What is an attribute?It is a particular property, which describes the entity.

23. What is degree of a Relation?

It is the number of attribute of its relation schema.

24. What is Relationship?It is an association among two or more entities.


3/42


4/42

hierarchy, where the employee data represents the parent segment

and the children data represents the child segment. If an employee

has three children, then there would be three child segments

associated with one employee segment. In a hierarchical database

the parent-child relationship is one to many. This restricts a childsegment to having only one parent segment.

Hierarchical Model.

Advantages of Hierarchical model

Simplicity,Data Security,Data Integrity,Efficiency

Disadvantages of Hierarchical model

1. Implementation complexity: Although it is simple and easy to design, it is quite complex to implement.

2. Database Management Problem: If you make any changes in the database structure, then you need to makechanges in the entire application program that access the database.

3. Lack of Structural Independence: there is lack of structural independence because when we change the

structure then it becomes compulsory to change the application too.

4. Operational Anomalies: Hierarchical model suffers from the insert, delete and update anomalies, also retrieval

operation is difficult.

Some data were more naturally modeled with more than one parent

per child.

Network modelThe network model is a variation on the hierarchical model, to the extent that it is built on the concept of

multiple branches (lower-level structures) emanating from one or more nodes (higher-level structures),

while the model differs from the hierarchical model in that branches can be connected to multiple nodes.

The network model is able to represent redundancy in data more efficiently than in the hierarchical model.

the network model permitted the modeling of many-to-many

relationships in data. A member record type can have that role in
http://en.wikipedia.org/wiki/File:Hierarchical_Model.jpghttp://en.wikipedia.org/wiki/File:Hierarchical_Model.jpg


5/42

more than one set, hence the multiparent concept is supported. An

owner record type can also be a member or owner in another set.

The data model is a simple network, and link and intersection record

types (called junction records by IDMS) may exist, as well as sets

between them .

DISADVANTAGES

System complexity

In a network model, data are accessed one record at a time. This males it essential for the databasedesigners, administrators, and programmers to be familiar with the internal data structures to gainaccess to the data. Therefore, a user friendly database management system cannot be created usingthe network model

Lack of Structural independence.

Making structural modifications to the database is very difficult in the network database model as the

data access method is navigational. Any changes made to the database structure require the

application programs to be modified before they can access data. Though the network model achieves

data independence, it still fails to achieve structural independence.

Relational model

The basic data structure of the relational model is the table, where information about a particular entity

(say, an employee) is represented in rows (also called tuples) and columns. Thus, the "relation" in

"relational database" refers to the various tables in the database; a relation is a set of tuples. The columns

enumerate the various attributes of the entity (the employee's name, address or phone number, for

example), and a row is an actual instance of the entity (a specific employee) that is represented by the

relation. As a result, each tuple of the employee table represents various attributes of a single employee.

All relations (and, thus, tables) in a relational database have to adhere to some basic rules to qualify as

relations. First, the ordering of columns is immaterial in a table. Second, there can't be identical tuples or

rows in a table. And third, each tuple will contain a single value for each of its attributes.
http://en.wikipedia.org/wiki/Tuplehttp://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Tuplehttp://en.wikipedia.org/wiki/Relation_(database)


6/42

Example of a Relational Model.

The disadvantage: You can lose information if the keys over lap in the requests made by the creator.Sometimes this is true and sometimes it is not.

SQL is a type of database, is called structure query language. Which commonly is know to form thestructure of the backbone for most relations database query systems. The popularity of this language

is because the American Standard Institute standardized it. Another reason is that it was originally

developed and marketed by IBM and has thus benefited from a high level of exposure

Object-Oriented Model

Integrity

Database integrity ensures that data entered into the database is accurate, valid, and consistent.

Three basic types of database integrity constraints are:

Entity integrity

The entity integrity constraint states that no primary key value can be null. This is because the primarykey value is used to identify individual tuples in a relation . Having null value for the primary key impliesthat we cannot identify some tuples.This also specifies that there may not be any duplicate entries inprimary key column.

Referential Integrity
http://en.wikipedia.org/wiki/Datahttp://en.wikipedia.org/wiki/Databasehttp://en.wikipedia.org/wiki/Consistency_(database_systems)http://en.wikipedia.org/wiki/Consistency_(database_systems)http://en.wikipedia.org/wiki/File:Relational_Model_2.jpghttp://en.wikipedia.org/wiki/File:Relational_Model_2.jpghttp://en.wikipedia.org/wiki/Datahttp://en.wikipedia.org/wiki/Databasehttp://en.wikipedia.org/wiki/Consistency_(database_systems)


7/42

The referential integrity constraint is specified between two relations and is used to maintain theconsistency among tuples in the two relations. Informally, the referential integrity constraint states that atuple in one relation that refers to another relation must refer to an existing tuple in that relation.

Domain Integrity

The domain integrity states that every element from a relation should respect the type and restrictions ofits corresponding attribute. A type can have a variable length which needs to be respected. Restrictionscould be the range of values that the element can have, the default value if none is provided, and if theelement can be NULL.

ACIDAtomicity. Either all operations of the transaction are reflected properly inthedatabase, or none are. Consistency. Execution of a transaction in isolation (that is, with no othertransaction executing concurrently) preserves the consistency of thedatabase. Isolation. Even though multiple transactions may execute concurrently,thesystem guarantees that, for every pair of transactions Ti and Tj , it appearsto Ti that either Tj finished execution before Ti started, or Tj startedexecutionafter Ti finished. Thus, each transaction is unaware of other transactionsexecuting concurrently in the system. Durability. After a transaction completes successfully, the changes it hasmadeto the database persist, even if there are system failures.These properties are often called the ACID properties

DefinitionsSuperkey

Asuperkeyis a combination of attributes that can be used to uniquely identify a database record.

A table might have many superkeys.

Candidate key

Acandidate keyis a special subset of superkeys that do not have any extraneous information in

them: it is a minimal superkey.

Examples: Imagine a table with the fields , , and . This table

has many possible superkeys. Three of these are , and .
http://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_key


8/42

Of those listed, only is a candidate key, as the others contain information not necessary to

uniquely identify records ('SSN' here refers to Social Security Number, which is unique to each person).

Non-prime attribute

A non-prime attribute is an attribute that does not occur in any candidate key. Employee Address

would be a non-prime attribute in the "Employees' Skills" table.

Prime attribute

A prime attribute, conversely, is an attribute that does occur in any candidate key.

Primary key

MostDBMSsrequire a table to be defined as having a single unique key, rather than a number of

possible unique keys. A primary keyis a key which the database designer has designated for this

purpose.

Functional dependency

In a given table, an attribute Yis said to have a functional dependencyon a set of

attributesX(writtenX Y) if and only if eachXvalue is associated with precisely one Yvalue. For

example, in an "Employee" table that includes the attributes "Employee ID" and "Employee Date of Birth",

the functional dependency {Employee ID} {Employee Date of Birth} would hold. It follows from the

previous two sentences that each {Employee ID} is associated with precisely one {Employee Date of

Birth}.

Trivial functional dependency

A trivial functional dependency is a functional dependency of an attribute on a superset of itself.

{Employee ID, Employee Address} {Employee Address} is trivial, as is {Employee Address}

{Employee Address}.

Full functional dependency

An attribute is fully functionally dependent on a set of attributes X if it is

functionally dependent on X, and

not functionally dependent on any proper subset of X. {Employee Address} has a functional

dependency on {Employee ID, Skill}, but not a fullfunctional dependency, because it is also

dependent on {Employee ID}.

Transitive dependency

Atransitive dependencyis an indirect functional dependency, one in whichXZonly by virtue

ofXYand YZ.

Multivalued dependency
http://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Primary_keyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Primary_keyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependency


9/42

Amultivalued dependencyis a constraint according to which the presence of certain rows in a

table implies the presence of certain other rows.

Join dependency

A table Tis subject to ajoin dependencyifTcan always be recreated by joining multiple tables

each having a subset of the attributes ofT.

Database Anomalies

Anomaly in software, anything that differs from expectations, whether the

expectations resulted from

documentation,experiences, or otherwise.

Insertion Anomaly

It is a failure to place information about a new database entry into all the places in

the database where

information about the new entry needs to be stored. In a properly normalizeddatabase, information about a

new entry needs to be inserted into only one place in the database, in an

inadequatly normalized database,

information about a new entry may need to be inserted into more than one place,

and human fallibility being

what it is, some of the needed additional insertions may be missed.
http://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/File:Insertion_anomaly.svghttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Join_dependency


10/42

An insertion anomaly. Until the new faculty member, Dr. Newsome, is assigned to teach at least one course, his details

cannot be recorded.

Eg: There are circumstances in which certain facts cannot be recorded at all. For

example, each record

in a "Faculty and Their Courses" table might contain a Faculty ID, Faculty Name,

Faculty Hire Date, and

Course Codethus we can record the details of any faculty member who teaches at

least one course, but we

cannot record the details of a newly-hired faculty member who has not yet been

assigned to teach any

courses except by setting the Course Code to null. This phenomenon is known as an

insertion anomaly.

Deletion Anomaly

It is a failure to remove information about an existing database entry when it is time

to remove that entry.

In a properly normalized database, information about an old, to-be-gotten-rid-of

entry needs to be deleted

from only one place in the database, in an inadequatly normalized database,

information about that old entry

may need to be deleted from more than one place.

A deletion anomaly. All information about Dr. Giddens is lost when he temporarily ceases to be assigned to any courses.
http://en.wikipedia.org/wiki/File:Deletion_anomaly.svghttp://en.wikipedia.org/wiki/File:Deletion_anomaly.svghttp://en.wikipedia.org/wiki/File:Insertion_anomaly.svg


11/42

Eg: There are circumstances in which the deletion of data representing certain facts necessitates the

deletion of data representing completely different facts. The "Faculty and Their Courses" table described

in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to

be assigned to any courses, we must delete the last of the records on which that faculty member appears,

effectively also deleting the faculty member. This phenomenon is known as a deletion anomaly.

Update Anomaly

An update of a database involves modifications that may be additions, deletions, or both. Thus update

anomalies can be either of the kinds discussed above.

An update anomaly. Employee 519 is shown as having different addresses on different records.

Eg: The same information can be expressed on multiple rows; therefore updates to the table may result

in logical inconsistencies. For example, each record in an "Employees' Skills" table might contain an

Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will

potentially need to be applied to multiple records (one for each of his skills). If the update is not carried

through successfullyif, that is, the employee's address is updated on some records but not othersthen the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the

question of what this particular employee's address is. This phenomenon is known as an update

anomaly.

Normalization

In the design of a relational database management system (RDBMS), the process of organizing data to

minimize redundancy is called normalization. The goal of database normalization is to decompose

relations with anomalies in order to produce smaller, well-structured relations. Normalization usually

involves dividing large tables into smaller (and less redundant) tables and defining relationships between

them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made

in just one table and then propagated through the rest of the database via the defined relationships.
http://en.wikipedia.org/wiki/Relational_database_management_systemhttp://en.wikipedia.org/wiki/Relational_database_management_systemhttp://en.wikipedia.org/wiki/File:Update_anomaly.pnghttp://en.wikipedia.org/wiki/File:Update_anomaly.pnghttp://en.wikipedia.org/wiki/Relational_database_management_system


12/42

Edgar F. Codd, the inventor of therelational model, introduced the concept of normalization and what we

now know as the First Normal Form (1NF) in 1970.[1]Codd went on to define the Second Normal Form

(2NF) and Third Normal Form (3NF) in 1971,[2] and Codd and Raymond F. Boycedefined the Boyce-

Codd Normal Form (BCNF) in 1974.[3]Higher normal forms were defined by other theorists in subsequent

years, the most recent being the Sixth Normal Form (6NF) introduced by Chris Date, Hugh Darwen,

andNikos Lorentzosin 2002.[4]

Informally, a relational databasetable(the computerized representation of a relation) is often described

as "normalized" if it is in the Third Normal Form.[5]Most 3NF tables are free of insertion, update, and

deletion anomalies, i.e. in most cases 3NF tables adhere to BCNF, 4NF, and 5NF (but typically not6NF).

Advantages

Free the database of modification anomalies

Minimize redesign when extending the database structure

Make the data model more informative to users
http://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Relational_modelhttp://en.wikipedia.org/wiki/Relational_modelhttp://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-0http://en.wikipedia.org/wiki/Database_normalization#cite_note-0http://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-CoddBCNF-2http://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Christopher_J._Datehttp://en.wikipedia.org/wiki/Hugh_Darwenhttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Date6NF-3http://en.wikipedia.org/wiki/Database_normalization#cite_note-Date6NF-3http://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Database_normalization#cite_note-DateIntroDBSys-4http://en.wikipedia.org/wiki/Database_normalization#cite_note-DateIntroDBSys-4http://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Relational_modelhttp://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-0http://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-CoddBCNF-2http://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Christopher_J._Datehttp://en.wikipedia.org/wiki/Hugh_Darwenhttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Date6NF-3http://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Database_normalization#cite_note-DateIntroDBSys-4http://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Sixth_normal_form


13/42

Normal form Defined by Brief definition

First normal

form (1NF)

Two versions: E.F. Codd (1970), C.J. Date

(2003)[11]Table faithfully represents a relationand has

no repeating groups

Second normalform (2NF)

E.F. Codd (1971)[2]No non-prime attribute in the table

isfunctionally dependent on aproper subset of

acandidate key

Third normal

form (3NF)

E.F. Codd (1971);[2]see +also Carlo Zaniolo's

equivalent but differently-expressed definition(1982)[12]

Every non-prime attribute is non-transitively

dependent on every candidate key in the table

BoyceCodd

normal

form(BCNF)

Raymond F. Boyce and E.F. Codd (1974)[13]Every non-trivial functional dependency in the

table is a dependency on a superkey

Fourth normal

form (4NF)Ronald Fagin (1977)[14]

Every non-trivialmultivalued dependencyin

the table is a dependency on a superkey

Fifth normal

form (5NF)Ronald Fagin (1979)[15]

Every non-trivialjoin dependency in the table is

implied by the superkeys of the table

Domain/key normal

form(DKNF)Ronald Fagin (1981)[16]

Every constraint on the table is a logical

consequenceof the table's domain constraints

and key constraints

Sixth normal

form (6NF)

C.J. Date, Hugh Darwen, andNikos

Lorentzos (2002)[4]

Table features no non-trivial join dependencies

at all (with reference to generalized join

operator)
http://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-10http://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Proper_subsethttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Database_normalization#cite_note-11http://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-12http://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-13http://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-14http://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Domain/key_normal_formhttp://en.wikipedia.org/wiki/Domain/key_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-15http://en.wikipedia.org/wiki/Logical_consequencehttp://en.wikipedia.org/wiki/Logical_consequencehttp://en.wikipedia.org/wiki/Logical_consequencehttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Christopher_J._Datehttp://en.wikipedia.org/wiki/Hugh_Darwenhttp://en.wikipedia.org/wiki/Hugh_Darwenhttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Date6NF-3http://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-10http://en.wikipedia.org/wiki/Relation_(database)http://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Proper_subsethttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Codd.2C_E.F_1971-1http://en.wikipedia.org/wiki/Database_normalization#cite_note-11http://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-12http://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-13http://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-14http://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Domain/key_normal_formhttp://en.wikipedia.org/wiki/Domain/key_normal_formhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Database_normalization#cite_note-15http://en.wikipedia.org/wiki/Logical_consequencehttp://en.wikipedia.org/wiki/Logical_consequencehttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Sixth_normal_formhttp://en.wikipedia.org/wiki/Christopher_J._Datehttp://en.wikipedia.org/wiki/Hugh_Darwenhttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Nikos_Lorentzoshttp://en.wikipedia.org/wiki/Database_normalization#cite_note-Date6NF-3


14/42

1st Normal Form (1NF)

First normal form (1NF or Minimal Form) is a normal formused in database normalization. Arelational

databasetable that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are

basically concerned with ensuring that the table is a faithful representation of arelation[1] and that it is

free ofrepeating groups.[2]

The concept of a "repeating group" is, however, understood in different ways by different theorists. As a

consequence, there is no universal agreement as to which features would disqualify a table from being in

1NF. Most notably, 1NF as defined by some authors (for example, Ramez Elmasri and Shamkant B.

Navathe,[3]following the precedent established byEdgar F. Codd) excludes relation-valued

attributes (tables within tables); whereas 1NF as defined by other authors (for example, Chris Date)

permits them.

Suppose a novice designer wishes to record the names and telephone numbers of customers. He defines

a customer table which looks like this:

Customer

Custome

r ID

First

Name

Surnam

e

Telephone

Number

123 Robert Ingram 555-861-2025

456 Jane Wright 555-403-1659

789 MariaFernand

ez555-808-9633

The designer then becomes aware of a requirement to record multiple telephone numbers for some

customers. He reasons that the simplest way of doing this is to allow the "Telephone Number" field in any

given record to contain more than one value:

Customer
http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Relational_databasehttp://en.wikipedia.org/wiki/Relational_databasehttp://en.wikipedia.org/wiki/Relational_databasehttp://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Relation_(mathematics)http://en.wikipedia.org/wiki/Relation_(mathematics)http://en.wikipedia.org/wiki/Relation_(mathematics)http://en.wikipedia.org/wiki/First_normal_form#cite_note-DateReln-0http://en.wikipedia.org/wiki/First_normal_form#cite_note-DateReln-0http://en.wikipedia.org/wiki/First_normal_form#cite_note-Kent-1http://en.wikipedia.org/wiki/First_normal_form#cite_note-Kent-1http://en.wikipedia.org/wiki/First_normal_form#cite_note-Elmasri-2http://en.wikipedia.org/wiki/First_normal_form#cite_note-Elmasri-2http://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Chris_Datehttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Relational_databasehttp://en.wikipedia.org/wiki/Relational_databasehttp://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/Relation_(mathematics)http://en.wikipedia.org/wiki/First_normal_form#cite_note-DateReln-0http://en.wikipedia.org/wiki/First_normal_form#cite_note-Kent-1http://en.wikipedia.org/wiki/First_normal_form#cite_note-Elmasri-2http://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Chris_Date


15/42

Custome

r ID

First

Name

Surnam

e

Telephone

Number


456 Jane Wright555-403-1659

555-776-4100

789 MariaFernand

ez555-808-9633

Assuming, however, that the Telephone Number column is defined on some Telephone Number-like

domain (e.g. the domain of strings 12 characters in length), the representation above is not in 1NF. 1NF

(and, for that matter, the RDBMS) prevents a single field from containing more than one value from its

column's domain.

Repeating groups across columns

The designer might attempt to get around this restriction by defining multiple Telephone Number columns:

Customer

Custome

r ID

First

Name

Surnam

eTel. No. 1 Tel. No. 2 Tel. No. 3

123 Robert Ingram555-861-

2025

456 Jane Wright555-403-

1659

555-776-

4100

555-403-

1659

789 MariaFernand

ez

555-808-

9633

This representation, however, makes use of nullable columns, and therefore does not conform to Date's

definition of 1NF (in violation to condition 4). Even if the view is taken that nullable columns are allowed,

the design is not in keeping with the spirit of 1NF (in violation to condition 2). Tel. No. 1, Tel. No. 2., and
http://en.wikipedia.org/wiki/RDBMShttp://en.wikipedia.org/wiki/RDBMS


16/42

Tel. No. 3. share exactly the same domain and exactly the same meaning; the splitting of Telephone

Number into three headings is artificial and causes logical problems. These problems include:

Difficulty in querying the table. Answering such questions as "Which customers have telephone

numberX?" and "Which pairs of customers share a telephone number?" is awkward.

Inability to enforce uniqueness of Customer-to-Telephone Number links through the RDBMS.

Customer 789 might mistakenly be given a Tel. No. 2 value that is exactly the same as her Tel. No. 1

value.

Restriction of the number of telephone numbers per customer to three. If a customer with four

telephone numbers comes along, we are constrained to record only three and leave the fourth

unrecorded. This means that the database design is imposing constraints on the business process,

rather than (as should ideally be the case) vice-versa.

[edit]Repeating groups within columns

The designer might, alternatively, retain the single Telephone Number column but alter its domain,

making it a string of sufficient length to accommodate multiple telephone numbers:

Customer

Custome

r ID

First

Name

Surnam

eTelephone Numbers


456 Jane Wright555-403-1659, 555-

776-4100

789 MariaFernand

ez555-808-9633

This design is consistent with 1NF, but still presents several design issues. The Telephone Number

heading becomes semanticallynon-specific, as it can now represent either a telephone number, a list of

telephone numbers, or indeed anything at all. A query such as "Which pairs of customers share a

telephone number?" is more difficult to formulate, given the necessity to cater for lists of telephone

numbers as well as individual telephone numbers. Meaningful constraints on telephone numbers are also

very difficult to define in the RDBMS with this design.
http://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=5http://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=5http://en.wikipedia.org/wiki/Semanticshttp://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=5http://en.wikipedia.org/wiki/Semantics


17/42

[edit]A design that complies with 1NF

A design that is unambiguously in 1NF makes use of two tables: a Customer Name table and a Customer

Telephone Number table.

Customer Name

Custome

r ID

First

Name

Surnam

e

123 Robert Ingram

456 Jane Wright

789 MariaFernand

ez

Customer Telephone

Number

Custome

r ID

Telephone

Number

123 555-861-2025

456 555-403-1659

456 555-776-4100

789 555-808-9633
http://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=6http://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=6http://en.wikipedia.org/w/index.php?title=First_normal_form&action=edit&section=6


18/42

2nd Normal Form (2NF)

A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key. Since a

partial dependency occurs when a non-key attribute is dependent on only a part of the composite key,

the definition of 2NF is sometimes phrased as, A table is in 2NF if it is in 1NF and if it has no partial

dependencies.

Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined

byE.F. Coddin 1971.[1]A table that is in first normal form (1NF) must meet additional criteria if it is to

qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate

keyK and any attribute A that is not a constituent of a candidate key, A depends upon the whole

of K rather than just a part of it.

In slightly more formal terms: a 1NF table is in 2NF if and only if all its non-prime attributes

are functionally dependenton the whole of every candidate key. (A non-prime attribute is one that does

not belong to any candidate key.)

Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than

one attribute), the table is automatically in 2NF.

Consider a table describing employees' skills:

Employees' Skills

Employee Skill Current Work Location

Jones Typing 114 Main Street

Jones Shorthand 114 Main Street

Jones Whittling 114 Main Street

Bravo Light Cleaning 73 Industrial Way

Ellis Alchemy 73 Industrial Way
http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/Second_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/Second_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/First_normal_formhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Candidate_key


19/42

Ellis Flying 73 Industrial Way

Harrison Light Cleaning 73 Industrial Way

Neither {Employee} nor {Skill} is a candidate key for the table. This is because a given Employee might

need to appear more than once (he might have multiple Skills), and a given Skill might need to appear

more than once (it might be possessed by multiple Employees). Only the composite key {Employee, Skill}

qualifies as a candidate key for the table.

The remaining attribute, Current Work Location, is dependent on only part of the candidate key, namely

Employee. Therefore the table is not in 2NF. Note the redundancy in the way Current Work Locations are

represented: we are told three times that Jones works at 114 Main Street, and twice that Ellis works at 73

Industrial Way. This redundancy makes the table vulnerable to update anomalies: it is, for example,

possible to update Jones' work location on his "Typing" and "Shorthand" records and not update his

"Whittling" record. The resulting data would imply contradictory answers to the question "What is Jones'

current work location?"

A 2NF alternative to this design would represent the same information in two tables: an "Employees" table

with candidate key {Employee}, and an "Employees' Skills" table with candidate key {Employee, Skill}:


20/42

Employees

Employee Current Work Location

Jones 114 Main Street

Bravo 73 Industrial Way

Ellis 73 Industrial Way

Harrison 73 Industrial Way

Employees' Skills

Employee Skill

Jones Typing

Jones Shorthand

Jones Whittling

Bravo Light Cleaning

Ellis Alchemy

Ellis Flying

Harrison Light Cleaning


21/42

3rd Normal Form (3NF)

A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

In computer science, the third normal form (3NF) is anormal form used in database normalization. 3NF

was originally defined by E.F. Codd in 1971.[1]Codd's definition states that a table is in 3NF if and only

ifboth of the following conditions hold:

The relation R (table) is in second normal form (2NF)

Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on

everycandidate keyof R.

A non-prime attribute of R is an attribute that does not belong to any candidate key of R.[2]Atransitive

dependencyis afunctional dependencyin whichX Z(Xdetermines Z) indirectly, by virtue of

X Yand Y Z(where it is not the case that YX).[3]

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in

1982. This definition states that a table is in 3NF if and only if, for each of its functional

dependenciesXA, at least one of the following conditions holds:

XcontainsA (that is,XA is trivial functional dependency), or

Xis a superkey, or

A-X, the set difference between A and X is a prime attribute (i.e.,A-Xis contained within

acandidate key)

An example of a 2NF table that fails to meet the requirements of 3NF is:

Tournament Winners

Tournament Year Winner Winner Date of Birth

Indiana Invitational 1998 Al Fredrickson 21 July 1975

Cleveland Open 1999 Bob Albertson 28 September 1968
http://en.wikipedia.org/wiki/Computer_sciencehttp://en.wikipedia.org/wiki/Computer_sciencehttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-Codd2-1http://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-2http://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Computer_sciencehttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/E.F._Coddhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-Codd2-1http://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Third_normal_form#cite_note-2http://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_key


22/42

Des Moines Masters 1999 Al Fredrickson 21 July 1975

Indiana Invitational 1999 Chip Masterson 14 March 1977

Because each row in the table needs to tell us who won a particular Tournament in a particular

Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely

identify a row. That is, {Tournament, Year} is a candidate key for the table.

The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively

dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact

that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to

logical inconsistencies, as there is nothing to stop the same person from being shown with

different dates of birth on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into two:

Tournament Winners

Tournament Year Winner

Indiana Invitational 1998 Al Fredrickson

Cleveland Open 1999 Bob Albertson

Des Moines Masters 1999 Al Fredrickson

Indiana Invitational 1999 Chip Masterson

Player Dates of Birth

Player Date of Birth

Chip Masterson 14 March 1977


23/42

Al Fredrickson 21 July 1975

Bob Albertson 28 September 1968

Update anomalies cannot occur in these tables, which are both in 3NF.

Boyce-Codd Normal Form (BCNF)

A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

BoyceCodd normal form (orBCNF or3.5NF) is a normal formused in database normalization. It is a

slightly stronger version of thethird normal form (3NF). A table is in BoyceCodd normal form if and only

iffor every one of its nontrivialdependenciesX Y,Xis a superkeythat is,Xis either acandidate

keyor a superset thereof.

BCNF was developed in 1974 byRaymond F. Boyceand Edgar F. Coddto address certain types of

anomaly not dealt with by 3NF as originally defined.

[1]

Chris Datehas pointed out that a definition of what we now know as BCNF appeared in a paper by Ian

Heath in 1971.[2]Date writes:

Only in rare cases does a 3NF table not meet the requirements of

BCNF. A 3NF table which does not have multiple overlapping candidate

keys is guaranteed to be in BCNF.[4]Depending on what its functional

dependencies are, a 3NF table with two or more overlapping candidate

keys may or may not be in BCNF

An example of a 3NF table that does not meet BCNF is:

Today's Court Bookings
http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Nontrivialhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/Chris_Datehttp://en.wikipedia.org/wiki/Chris_Datehttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Heath-1http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Heath-1http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Vincent-3http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Nontrivialhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Raymond_F._Boycehttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Codd-0http://en.wikipedia.org/wiki/Chris_Datehttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Heath-1http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Vincent-3


24/42

Cou

rt

Start

Time

End

Time

Rate

Type

1 09:30 10:30 SAVER

1 11:00 12:00 SAVER

1 14:00 15:30STANDAR

D

2 10:00 11:30PREMIUM-

B

2 11:30 13:30PREMIUM-

B

2 15:00 16:30PREMIUM-

A

Each row in the table represents a court booking at a tennis club

that has one hard court (Court 1) and one grass court (Court 2)

A booking is defined by its Court and the period for which the Court

is reserved

Additionally, each booking has a Rate Type associated with it.

There are four distinct rate types:

SAVER, for Court 1 bookings made by members

STANDARD, for Court 1 bookings made by non-members

PREMIUM-A, for Court 2 bookings made by members PREMIUM-B, for Court 2 bookings made by non-members

The table's superkeys are:

S1 = {Court, Start Time}


25/42

S2 = {Court, End Time}

S3 = {Rate Type, Start Time}

S4 = {Rate Type, End Time}

S5 = {Court, Start Time, End Time}

S6 = {Rate Type, Start Time, End Time}

S7 = {Court, Rate Type, Start Time}

S8 = {Court, Rate Type, End Time}

ST = {Court, Rate Type, Start Time, End Time}, the trivial

superkey

Note that even though in the above table Start Time and End

Time attributes have no duplicate values for each of them, we still

have to admit that in some other days two different bookings on court1 and court 2 could start at the same time orend at the same time.

This is the reason why {Start Time} and {End Time} cannot be

considered as the table's superkeys.

However, only S1 to S4 are candidate keys(that is, minimal superkeys

for that relation) because e.g. S1 S5, so S5 cannot be a candidate

key.

Recall that 2NF prohibits partial functional dependencies of non-prime

attributes (ie an attribute that does not occur in ANY candidate key)on candidate keys, and that 3NF prohibits transitive functional

dependenciesof non-prime attributes on candidate keys.

In Today's Court Bookings table, there are no non-prime attributes:

that is, all attributes belong to some candidate key. Therefore the

table adheres to both 2NF and 3NF.

The table does not adhere to BCNF. This is because of the

dependency Rate Type Court, in which the determining attribute

(Rate Type) is neither a candidate key nor a superset of a candidatekey.

Dependency Rate Type Court is respected as a Rate Type should

only ever apply to a single Court.

The design can be amended so that it meets BCNF:
http://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Transitive_dependencyhttp://en.wikipedia.org/wiki/Transitive_dependency


26/42

Rate Types

Rate

Type

Cour

t

Member

Flag

SAVER 1 Yes

STANDAR

D1 No

PREMIUM

-A2 Yes

PREMIUM

-B2 No

Today's Bookings

RateType

StartTime

EndTime

SAVER 09:30 10:30

SAVER 11:00 12:00

STANDAR

D

14:00 15:30

PREMIUM

-B10:00 11:30

PREMIUM 11:30 13:30


27/42

-B

PREMIUM

-A15:00 16:30

The candidate keys for the Rate Types table are {Rate Type} and

{Court, Member Flag}; the candidate keys for the Today's Bookings

table are {Rate Type, Start Time} and {Rate Type, End Time}. Both

tables are in BCNF. Having one Rate Type associated with two

different Courts is now impossible, so the anomaly affecting the

original table has been eliminated.

Achievability of BCNF

In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the

dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set

of functional dependencies {AB C, C B} cannot be represented by a BCNF schema.[5]Thus, unlike

the first three normal forms, BCNF is not always achievable.

Consider the following non-BCNF table whose functional dependencies follow the {AB C, C B}

pattern:

Nearest Shops

Perso

n

Shop

Type

Nearest

Shop

Davids

onOptician Eagle Eye

Davids

on

Hairdress

erSnippets

Wright Bookshop Merlin Books
http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Beeri-4http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Beeri-4http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#cite_note-Beeri-4


28/42

Fuller Bakery Doughy's

FullerHairdress

er

Sweeney

Todd's

Fuller Optician Eagle Eye

For each Person / Shop Type combination, the table tells us which shop of this type is geographically

nearest to the person's home. We assume for simplicity that a single shop cannot be of more than one

type.

The candidate keys of the table are:

{Person, Shop Type}

{Person, Nearest Shop}

Because all three attributes are prime attributes (i.e. belong to candidate keys), the table is in 3NF. The

table is not in BCNF, however, as the Shop Type attribute is functionally dependent on a non-superkey:

Nearest Shop.

The violation of BCNF means that the table is subject to anomalies. For example, Eagle Eye might have

its Shop Type changed to "Optometrist" on its "Fuller" record while retaining the Shop Type "Optician" on

its "Davidson" record. This would imply contradictory answers to the question: "What is Eagle Eye's Shop

Type?" Holding each shop's Shop Type only once would seem preferable, as doing so would prevent

such anomalies from occurring:

Shop Near Person

Perso

nShop

Davids

onEagle Eye

Davids

onSnippets


29/42

Wright Merlin Books

Fuller Doughy's

FullerSweeney

Todd's

Fuller Eagle Eye

Shop

ShopShop

Type

Eagle Eye Optician

SnippetsHairdress

er

Merlin Books Bookshop

Doughy's Bakery

Sweeney

Todd's

Hairdress

er

In this revised design , the "Shop Near Person" table has a candidate key of {Person, Shop}, and the

"Shop" table has a candidate key of {Shop}. Unfortunately, although this design adheres to BCNF, it is

unacceptable on different grounds: it allows us to record multiple shops of the same type against the


30/42

same person. In other words, its candidate keys do not guarantee that the functional dependency

{Person, Shop Type} {Shop} will be respected.


31/42

4th Normal Form (4NF)

A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

Fourth normal form (4NF) is a normal form used indatabase normalization. Introduced by Ronald

Faginin 1977, 4NF is the next level of normalization afterBoyceCodd normal form(BCNF). Whereas

the second,third, and BoyceCoddnormal forms are concerned withfunctional dependencies, 4NF is

concerned with a more general type of dependency known as amultivalued dependency. A table is in

4NF if and only if, for every one of its non-trivial multivalued dependenciesX Y,Xis asuperkey

that is,Xis either a candidate keyor a superset thereof.[1]

Multivalued dependencies

If the column headings in a relational database table are divided into three disjoint groupingsX, Y, and Z,

then, in the context of a particular row, we can refer to the data beneath each group of headings asx, y,

and zrespectively. A multivalued dependencyX Ysignifies that if we choose anyxactually

occurring in the table (call this choicexc), and compile a list of all thexcyzcombinations that occur in the

table, we will find thatxc is associated with the same yentries regardless of z.

A trivial multivalued dependencyX Yis one where eitherYis a subset ofX, orXand Ytogetherform the whole set of attributes of the relation.

Afunctional dependencyis a special case of multivalued dependency. In a functional dependencyX Y,

everyxdetermines exactly one y, never more than one.

[edit]Example

Consider the following example:

Pizza Delivery Permutations

RestaurantPizza

Variety

Delivery

Area

A1 Pizza Thick CrustSpringfield
http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Fourth_normal_form#cite_note-Fagin-0http://en.wikipedia.org/wiki/Fourth_normal_form#cite_note-Fagin-0http://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/w/index.php?title=Fourth_normal_form&action=edit&section=2http://en.wikipedia.org/w/index.php?title=Fourth_normal_form&action=edit&section=2http://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Ronald_Faginhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Second_normal_formhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Fourth_normal_form#cite_note-Fagin-0http://en.wikipedia.org/wiki/Multivalued_dependencyhttp://en.wikipedia.org/wiki/Functional_dependencyhttp://en.wikipedia.org/w/index.php?title=Fourth_normal_form&action=edit&section=2


32/42

A1 Pizza Thick CrustShelbyville

A1 Pizza Thick CrustCapital City

A1 PizzaStuffed

CrustSpringfield

A1 PizzaStuffed

CrustShelbyville

A1 PizzaStuffed

CrustCapital City

Elite Pizza Thin Crust Capital City

Elite PizzaStuffed

CrustCapital City

Vincenzo's

PizzaThick CrustSpringfield

Vincenzo's

PizzaThick CrustShelbyville

Vincenzo's

PizzaThin Crust Springfield

Vincenzo's

Pizza

Thin Crust Shelbyville

Each row indicates that a given restaurant can deliver a given variety of pizza to a given area.

The table has no non-key attributes because its only key is {Restaurant, Pizza Variety, Delivery Area}.

Therefore it meets all normal forms up to BCNF. If we assume, however, that pizza varieties offered by a

restaurant are not affected by delivery area, then it does not meet 4NF. The problem is that the table


33/42

features two non-trivial multivalued dependencies on the {Restaurant} attribute (which is not a superkey).

The dependencies are:

{Restaurant} {Pizza Variety}

{Restaurant} {Delivery Area}

These non-trivial multivalued dependencies on a non-superkey reflect the fact that the varieties of pizza a

restaurant offers are independent from the areas to which the restaurant delivers. This state of affairs

leads to redundancy in the table: for example, we are told three times that A1 Pizza offers Stuffed Crust,

and if A1 Pizza starts producing Cheese Crust pizzas then we will need to add multiple rows, one for each

of A1 Pizza's delivery areas. There is, moreover, nothing to prevent us from doing this incorrectly: we

might add Cheese Crust rows for all but one of A1 Pizza's delivery areas, thereby failing to respect the

multivalued dependency {Restaurant} {Pizza Variety}.

To eliminate the possibility of these anomalies, we must place the facts about varieties offered into a

different table from the facts about delivery areas, yielding two tables that are both in 4NF:

Varieties By

Restaurant

RestaurantPizza

Variety

A1 Pizza Thick Crust

A1 PizzaStuffed

Crust

Elite Pizza Thin Crust

Elite PizzaStuffed

Crust
http://en.wikipedia.org/wiki/Data_redundancyhttp://en.wikipedia.org/wiki/Data_redundancy


34/42

Vincenzo's

PizzaThick Crust

Vincenzo's

Pizza Thin Crust

Delivery Areas By

Restaurant

RestaurantDelivery

Area

A1 Pizza Springfield

A1 Pizza Shelbyville

A1 Pizza Capital City

Elite Pizza Capital City

Vincenzo's

PizzaSpringfield

Vincenzo's

PizzaShelbyville

5th Normal Form (5NF)

A table is in 5NF, also called Projection-join Normal Form (PJNF), if it is in 4NF and if every join

dependency in the table is a consequence of the candidate keys of the table.


35/42

Fifth normal form (5NF), also known as Project-join normal form (PJ/NF) is a level ofdatabase

normalizationdesigned to reduce redundancy in relational databases recording multi-valued facts by

isolatingsemanticallyrelated multiple relationships. A table is said to be in the 5NF if and only

ifeveryjoin dependencyin it is implied by the candidate keys.

Ajoin dependency*{A, B, Z} on R is implied by the candidate key(s) of Rif and only ifeach of A, B, ,

Z is a superkeyfor R.

Example

Consider the following example:

Travelling Salesman Product

Availability By Brand

Travelling

SalesmanBrand Product Type

Jack Schneider AcmeVacuum

Cleaner

Jack Schneider Acme Breadbox

Willy LomanRobust

o

Pruning

Shears

Willy LomanRobust

o

Vacuum

Cleaner

Willy LomanRobust

oBreadbox

Willy LomanRobust

o

Umbrella

Stand

Louis FergusonRobust

o

Vacuum

Cleaner
http://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Semanticallyhttp://en.wikipedia.org/wiki/Semanticallyhttp://en.wikipedia.org/wiki/Semanticallyhttp://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Superkeyhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Semanticallyhttp://en.wikipedia.org/wiki/Table_(database)http://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/Join_dependencyhttp://en.wikipedia.org/wiki/Candidate_keyhttp://en.wikipedia.org/wiki/If_and_only_ifhttp://en.wikipedia.org/wiki/Superkey


36/42


oTelescope

Louis Ferguson AcmeVacuum

Cleaner

Louis Ferguson Acme Lava Lamp

Louis Ferguson Nimbus Tie Rack

The table's predicate is: Products of the type designated by Product Type, made by the brand designated

by Brand, are available from the travelling salesman designated by Travelling Salesman.

In the absence of any rules restricting the valid possible combinations of Travelling Salesman, Brand, and

Product Type, the three-attribute table above is necessary in order to model the situation correctly.

Suppose, however, that the following rule applies:A Travelling Salesman has certain Brands and certain

Product Types in his repertoire. If Brand B is in his repertoire, and Product Type P is in his repertoire,

then (assuming Brand B makes Product Type P), the Travelling Salesman must offer products of Product

Type P made by Brand B.

In that case, it is possible to split the table into three:

Product Types By

Travelling Salesman

Travelling

Salesman

Product

Type

Jack SchneiderVacuum

Cleaner

Jack Schneider Breadbox

Willy LomanPruning

Shears


37/42

Willy LomanVacuum

Cleaner

Willy Loman Breadbox

Willy LomanUmbrella

Stand

Louis Ferguson Telescope

Louis FergusonVacuum

Cleaner

Louis Ferguson Lava Lamp

Louis Ferguson Tie Rack

Brands By Travelling

Salesman

Travelling

SalesmanBrand

Jack Schneider Acme

Willy LomanRobust

o


o

Louis Ferguson Acme


38/42

Louis Ferguson Nimbus

Product Types By

Brand

BrandProduct

Type

Acme VacuumCleaner

Acme Breadbox

Acme Lava Lamp

Robus

to

Pruning

Shears

Robus

to

Vacuum

Cleaner

Robus

toBreadbox

Robusto UmbrellaStand

Robus

toTelescope


39/42

Nimbu

sTie Rack

Note how this setup helps to remove redundancy. Suppose that Jack Schneider starts selling Robusto's

products. In the previous setup we would have to add two new entries since Jack Schneider is able to sell

two Product Types covered by Robusto: Breadboxes and Vacuum Cleaners. With the new setup we need

only add a single entry (in Brands By Travelling Salesman).

Usage

Only in rare situations does a 4NFtable not conform to 5NF. These are situations in which a complex

real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit inthe structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical

consistency of the data within the table must be carried partly by the application responsible for insertions,

deletions, and updates to it; and there is a heightened risk that the data within the table will become

inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.

Domain-Key Normal Form (DKNF)

A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and

domains.

omain/key normal form (DKNF) is anormal form used indatabase normalization which requires that the

database contains no constraints other than domain constraints and key constraints.

A domain constraint specifies the permissible values for a given attribute, while a key constraint specifies

the attributes that uniquely identify a row in a given table .

The third normal form, BoyceCodd normal form, fourth normal form and fifth normal formare special

cases of the domain/key normal form. All have either functional, multi-valued or join dependencies thatcan be converted into (super)keys.

Example

A violation of DKNF occurs in the following table:
http://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Data_domainhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Database_normalization#Normal_formshttp://en.wikipedia.org/wiki/Database_normalizationhttp://en.wikipedia.org/wiki/Data_domainhttp://en.wikipedia.org/wiki/Third_normal_formhttp://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_formhttp://en.wikipedia.org/wiki/Fourth_normal_formhttp://en.wikipedia.org/wiki/Fifth_normal_form


40/42

Wealthy Person

Wealthy

Person

Wealthy Person

Type

Net Worth in

Dollars

SteveEccentric

Millionaire124,543,621

Roderick Evil Billionaire 6,553,228,893

KatrinaEccentric

Billionaire8,829,462,998

Gary Evil Millionaire 495,565,211

(Assume that the domain for Wealthy Person consists of the names of all wealthy people in a pre-defined

sample of wealthy people; the domain for Wealthy Person Type consists of the values 'Eccentric

Millionaire', 'Eccentric Billionaire', 'Evil Millionaire', and 'Evil Billionaire'; and the domain for Net Worth in

Dollars consists of all integers greater than or equal to 1,000,000.)

There is a constraint linking Wealthy Person Type to Net Worth in Dollars, even though we cannot deduceone from the other. The constraint dictates that an Eccentric Millionaire or Evil Millionaire will have a net

worth of 1,000,000 to 999,999,999 inclusive, while an Eccentric Billionaire or Evil Billionaire will have a

net worth of 1,000,000,000 or higher. This constraint is neither a domain constraint nor a key constraint;

therefore we cannot rely on domain constraints and key constraints to guarantee that an inconsistent

Wealthy Person Type / Net Worth in Dollars combination does not make its way into the database.

The DKNF violation could be eliminated by altering the Wealthy Person Type domain to make it consist of

just two values, 'Evil' and 'Eccentric' (the wealthy person's status as a millionaire or billionaire is implicit in

their Net Worth in Dollars, so no useful information is lost).

Wealthy Person

Wealthy

Person

Wealthy Person

Type

Net Worth in

Dollars


41/42

Steve Eccentric 124,543,621

Roderick Evil 6,553,228,893

Katrina Eccentric 8,829,462,998

Gary Evil 495,565,211

Wealthiness Status

Status Minimum Maximum

Millionai

re1,000,000 999,999,999

Billionair

e

1,000,000,0

00

999,999,999,

999

6th Normal Form

6NF if and only if it satisfies no nontrivial join dependencies at all


42/42

Codds RulesCodd's twelve rules are a set of thirteen rules (numbered zero to twelve) proposed byEdgar F. Codd, a

pioneer of the relational model fordatabases, designed to define what is required from adatabase

management systemin order for it to be considered relational, i.e., a relational database management

system (RDBMS).[1][2]They are sometimes jokingly referred to as "Codd's Twelve Commandments".
http://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Relational_modelhttp://en.wikipedia.org/wiki/Databasehttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/RDBMShttp://en.wikipedia.org/wiki/Codd's_12_rules#cite_note-0http://en.wikipedia.org/wiki/Codd's_12_rules#cite_note-0http://en.wikipedia.org/wiki/Codd's_12_rules#cite_note-1http://en.wikipedia.org/wiki/Edgar_F._Coddhttp://en.wikipedia.org/wiki/Relational_modelhttp://en.wikipedia.org/wiki/Databasehttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/Database_management_systemhttp://en.wikipedia.org/wiki/RDBMShttp://en.wikipedia.org/wiki/Codd's_12_rules#cite_note-0http://en.wikipedia.org/wiki/Codd's_12_rules#cite_note-1

dbms study material1

Documents