Scribd
Upload
141 views5 pages

Normalization Rules The Need For Normalization: DBMS - Unit 1 Database Design Concept 1

The document discusses normalization rules and converting a database design to first, second, and third normal forms (1NF, 2NF, 3NF) to eliminate anomalies. It provides an example of a database tracking building project data. The database is initially converted to 1NF by eliminating repeating groups and defining primary keys. It is then converted to 2NF by splitting tables based on primary key components. Finally, it is converted to 3NF by moving attributes dependent on non-key attributes to separate tables to remove transitive dependencies. The document also discusses Boyce-Codd normal form and how 3NF and BCNF can be equivalent if a table only has one candidate key.

Uploaded by

DeepakGupta
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
141 views5 pages

Normalization Rules The Need For Normalization: DBMS - Unit 1 Database Design Concept 1

The document discusses normalization rules and converting a database design to first, second, and third normal forms (1NF, 2NF, 3NF) to eliminate anomalies. It provides an example of a database tracking building project data. The database is initially converted to 1NF by eliminating repeating groups and defining primary keys. It is then converted to 2NF by splitting tables based on primary key components. Finally, it is converted to 3NF by moving attributes dependent on non-key attributes to separate tables to remove transitive dependencies. The document also discusses Boyce-Codd normal form and how 3NF and BCNF can be equivalent if a table only has one candidate key.

Uploaded by

DeepakGupta
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

DBMS – Unit 1 Database Design Concept 1

Normalization Rules

The Need for Normalization


 The simplified case illustration – for a construction company.
 Manages several building projects.
 Each project contains project number, name, employee, and so on.
 Each employee has ID, name, job title, and so on.
 The clients are charged by billing hours spent on each contract. The rate depends
on employee’s position, i.e., salary.
 The total charge is shown on the figure below.
DBMS – Unit 1 Database Design Concept 2

Conversion to 1NF
 Repeating groups must be eliminated
 Repeating groups, for example, any project number (PROJ_NUM) in the Figure,
can have a group of several data entries.
 Make sure that each row defines a single entity.
 Proper primary key developed; for example, uniquely identifies attribute values
(rows), combination of PROJ_NUM and EMP_NUM.

 Dependencies can be identified with help of the figure above, which is the dependency
diagram shown in first normal form (1NF).
 Desirable dependencies based on primary keys, which are bold, underlined, and
shared in a different color in the figure.
 Less desirable dependencies, which is indicated with arrows below the dependency
diagram. Two types of dependencies exist:
 Partial dependencies – based on part of composite primary key.
 Transitive dependencies – one nonprime attribute depends on another
nonprime attribute.
 The table structure can shown in the format:
TABLE_NAME(PRIMARY_KEY_ATTRIBUTE(S), DEPENDENT
ATTRIBUTES)
 Prime attribute (or key attribute), any attribute that is at least part of a key, e.g.,
PROJ_NUM and EMP_NUM.
 Nonprime attribute (or nonkey attribute), is not even part of a key.
 1NF Summarized
 All key attributes defined
 No repeating groups in table
 All attributes dependent on primary key
DBMS – Unit 1 Database Design Concept 3

Conversion to 2NF
 Start with 1NF format:
 Write each key component on separate line
 Write original (composite) key on last line
PROJ_NUM
EMP_NUM
PROJ_NUM EMP_NUM
 Each component will become the key new table
 Write dependent attributes after each key. For example herein, the three new tables,
PROJECT, EMPLOYEE, and ASSIGN, are described by
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR)
ASSIGN (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
 The conversion results of this operation are displayed in next page. (It still shows a
transitive dependency, which can generate anomalies – the charge per hour changes for a
job classification that is held by many employees.)
 2NF Conversion Results
 In 1NF
 Includes no partial dependencies
 No attribute dependent on a portion of primary key
 Still possible to exhibit transitive dependency
 Attributes may be functionally dependent on nonkey attributes
DBMS – Unit 1 Database Design Concept 4

Conversion to 3NF
 Break off the piece(s) that are identified by the transitive dependency arrow(s) below the
dependency diagram and storing them in separate table(s) to eliminate transitive
functional dependencies
PROJECT (PROJ_NUM, PROJ_NAME)
ASSIGN (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)

 3NF Summarized
 In 2NF
 Contains no transitive dependencies
 Improve the database’s ability to provide information and to enhance it operational
characteristics for a completed database shown in previous page.
 This conversion has eliminated the original EMPLOYEE table’s transitive
dependency; the tables are now said to be in third normal form (3NF).
DBMS – Unit 1 Database Design Concept 5

Boyce-Codd Normal Form (BCNF)


 Every determinant in the table is a candidate key
 Determinant is attribute whose value determines other values in row.
 3NF table with one candidate key is already in BCNF
 3NF Table Not in BCNF, they both can be equivalent ONLY IF the table contains only
one candidate key.
 Most designers consider the BCNF as a special case of the 3NF. To determine a
table in 3NF but not in BCNF, note that
 A transitive dependency exists when one nonprime attribute is dependent on
another nonprime attribute.
 A table is in 3NF if it is in 2NF and there are no transitive dependencies.

You might also like