Analysis and Design Information System - Normalization : An Exercise

NORMALIZATION OF THE ERD

ERD provided the following :

And then my duty is to normalization of the ER-Diagram, and each table.

For the first we find the problems that exist in the ERD.

ERD above , there are 2 that confusion can lead to an error occurs, namely :

· On the relationship between the entity and the project does not have a clear cardinality.

· There is a ternary relationship have on the relationship.

From confusion above, i try to improve the ERD above the following :

From the ERD above, the relationship between project entity and eliminates the burden based on business logic, the seal was found by an employee is not a project.

Mapping

From the ERD above, we can do process Mapping to can make the normalization of the ERD and each table, the following :

After we make the mapping, we can make the normalization as follows :

Normalization

· 1NF

Explanation of First Normal Form above :

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

§ A relational database table 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 a relation and that it is free of repeating groups.

§ Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

· 2NF

Explanation of Second Normal Form above :

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

§ A 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.

§ A 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

§ As note, when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

§ Create relationships between these new tables and their predecessors through the use of foreign key( a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables) .

· 3NF

Explanation of Third Normal Form above :

§ Normal form 3NF fufilled if have fulfilled form 2NF.

§ And there is attribute of is non key primary owning depended to attribute of is non the other key primary.

Analysis and Design Information System - DATABASE NORMALIZATION

DATABASE NORMALIZATION

Database design process (review)

• Gather user needs / business.
• Develop a needs-based ER Model user / business.
• Convert E-R model to the set of relations (tables).
• Normalizationing relations, to remove anomalies.
• Implemented to create a database with a table for each relationship that is have normalizationed.

Database normalization

• Normalization is the establishment of the database structure process so that most of the ambiguity can be removed.
• Normalization stage, starting from the most mild (1NF) to most stringent (5NF)
• Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.
• Why do normalization?

Ø Optimizing table structures

Ø Increase speed

Ø To remove income of same data.

Ø More efficient in the use of storage media

Ø Reduce redundancy

Ø Avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).

Ø Improved data integrity

• A table saying good (efficient) or if the normal 3 to meet the following criteria :

Ø If there is decomposition (decomposition) table, it must be guaranteed safe decomposition it (Lossless-Join Decomposition). That is, after the table is described / decompositioned a new table-table, the table-table can generate a new table with the same exact.

Ø Maintain dependence on the functional changes in data (Dependency preservation).

Ø Does not violate Boyce-Code Normal Form (BCNF)

• If the third criteria (BCNF) can not be met, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Functional Dependency

• Functional Dependency describes a relationship attributes in relation
• An attribute said functionally dependant on the other, if we use the value attribute to determine the value of the other attributes.
• Symbols used are -> to represent the functional dependency. -> reads determine the functional
• Notation : A -> B

A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same value of A, then B is also the same value

• Notation : A ->/ B or A x-> B

It is the opposite of the previous notation

• For example :

Functional Dependency:

• NRP -> Name
• Study_lecture, NRP -> grade/value

Non Functional Dependency:

• Study_lecture -> NRP
• NRP -> grade/value
• Functional Dependency from the value table :

Ø NRP -> Name

Because for each value Nrp the same, then the value of the same name.

Ø {Study_lecture, NRP } -> grade/value

Because the value of attributes depending on the NRP and Study_lecture together. In another sense Study_lecture for the NRP and the same, they also rated the same, because Study_lecture and the NRP is a key (is unique).

Ø Study_lecture ->/ NRP

Ø NRP ->/ value

First Normal Form - 1NF

A table on the form said to be normal if I did not reside in the unnormalized form of a table, where there is a kind of field multiplication and field that allows a null (empty).

not be allowed :

• Attributes which many valuable (Multivalued attributes).
• Attributes composite or a combination of both.

So that :

• Price of the attribute domain must be atomic rates.

v Example Student Data as follows:

v Or :

v the tables above does not meet the requirements 1NF.

v Decomposition becomes:

Ø Table of student :

Ø Table of hobby :

Second Normal Form - 2NF

• Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes than the primary key, have a full Functional Dependency on primary key
• A table does not meet 2NF, if there are attributes that dependence it (Functional Dependency) are only partial (only depending on the part of the primary key)
• If there are attributes that have no dependence on the primary key, then the attributes must be moved or removed
• Functional dependence X -> Y , said full if it is said to delete an attribute A from X means that Y is no longer dependent functional.
• Functional dependence X -> Y , said partial if a delete attribute A from X means that Y is functionally dependent.
• Relation scheme R in the form 2NF if every non-primary key attribute A Î R depend on the full functional primary key R.
• For example :

Ø The following table meet 1NF, but not including 2NF

Ø Does not meet 2NF, because (NIM, CodeMk) is regarded as the primary key whereas:

{NIM, CodeMk} à NameMhs

{NIM, CodeMk} à Address

{NIM, CodeMk} à LectureStudy

{NIM, CodeMk} à Sks

{NIM, CodeMk} à LetterValue

Ø The table needs to decomposition to become some table eligible 2NF.

Ø Functional dependency it as follows:

o {NIM, CodeMk} à LetterValue (fd1)

o NIM à {NameMhs, Address} (fd2)

o CodeMk à {LectureStudy, Sks} (fd3)

Ø So that :

o fd1 (NIM, CodeMk, LetterValue) à Table of Value

o fd2 (NIM, NameMhs, Address) à Table of Student

o fd3 (CodeMk, LectureStudy, Sks) à Table of LectureStudy

Third Normal Form - 3NF

• Normal form 3NF fulfilled if the form meets 2NF, and if there are no non-primary key attribute that has a dependence on non-primary key attributes of the other (transitive dependencies).
• For example :

Ø This following table of students eligible 2NF, but does not meet to 3NF :

Ø Because there are non-primary key attribute (ie, City and Provincial), which has a dependence on non-primary key attributes of the other (ie PostCode):

PostCode à {City, Province}

Ø So that the table need to decomposition to becomes

Ø Student (NIM, NameMhs, Street, PostCode)

o PostCode (PostCode, Province, City)

Boyce-Codd Normal Form (BNCF)

• Boyce-Codd Normal Form constraint has a stronger form of the third Normal. To be BNCF, relations must be in the form of first Normal and forced each of the attributes depends on the function in the super key attributes.
• In the example below there is a relationship seminar, Primary Key is NPM + Seminar.

Students may take one or two seminars. Each seminar requires 2 each of the students and led by one of the 2 seminar. Each leader can only take one seminar course. NPM in this example and the Seminar show a Preceptor.

Seminar Relationship

Relations Seminar is a form of Third Normal, but not BCNF Seminar Code because the function depends on the preceptor, if every preceptor can only teach a seminar. Depending on the seminar is not a super key attributes such as required by BCNF. But relations Seminar should be parsed into two namely :

Preceptor Relationship

Seminar - Preceptor Relationship

The fourth and fifth Normal form

• Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency tdak contain many values. To remove the dependency of many values from a relation, we divide the relationship into two new relations. Each relation contains two attributes that have a lot of relationship value.
• Relations in fifth normal form (5NF) deal with the property called the join without any loss of information (lossless join). Fifth normal form (also called the 5 NF PJNF (projection join normal form). The case is very rare and appear difficult to detect in practice.