Normalization: An Exercise

Is given by the following ERD :

Normalization from ERD of above :

Mapping of ER Diagram above is :

Normaslisasi 1NF

Above field got from attribute in entitas of except field of KONTROLPEG and PIMPINAN. This Field is got from relationship 1:1 from entitas PEGAWAI and from entitas officer to entitas BAGIAN.

Functional Dependency from attribute of where sign bow its meaning that attribute showed by arrow sign, its value is depended from attribute showing, the example of at FD1 attribute showing is NOPEG so that all the attribute value depend on NOPEG. Where a officer control a lot of officer and a officer is only controlled by a just just officer. A officer can lead the shares and part of is only led by a just just officer.

From form 1NF above and by Functional is Dependency that happened is hence got by a second Normalization form 2NF

(NOPEG, NAPEG, ALM1, KDPOS, TGLLAH, UMUR,NOBAG, NOPEG) -> Pegawai Table

(NOBAG, NABAG, LOKASI, NOPEG) -> Bagian Table

(NOPEG, NOPRO, JAM) -> Kerja Table

(NOPRO, NAPRO, LOKPRO, NOBAG) -> Proyek Table

(NOPEG, NAMA, JNKELT, HUBUNGAN) -> Tanggungan Table

Table PEGAWAI have NOPEG as primary key, NOBAG and NOPEG as foreign key. NOBAG as the foreign key of this table because, according the ERD above, between PEGAWAI and BAGIAN have ANGGOTA relationship with many to one cardinality. So, primary key BAGIAN is become foreign key at table PEGAWAI. Table BAGIAN, have NOBAG as primary key, and NOPEG as foreign key. According to ERD above, between PEGAWAI and BAGIAN have PIMPIN relation with one to one cardinality. So, each other get foreign key from primary key each table. Table PROYEK have NOPRO as primary key, and NOBAG as foreign key. Table TANGGUNGAN have NOPEG as foreign key. As we see the ER above, TANGGUNGAN is weak entity, so, TANGGUNGAN have NOPEG as foreign key from table PEGAWAI. Table KERJA, is come from KERJA relation. As we see at the ER Diagram above, KERJA relation is many to many cardinality, so the relation KERJA is become a table, which have NOPEG and NOPRO as the foreign key

Form 3NF is equal to form 2NF because it is true when made to form 2NF entire all attribute of at tables only depend on one attribute of at the tables. This matter as according to loaded form 3NF that is in one tables of all attribute have to it depends on the one functioning attribute as primary key.

Database Normalization

NORMALIZATION.

Process The Database Device

1. Collecting requirement user / business

2. Developing E-R Model pursuant to requirement user / business

3. Converting E-R Model to relationship gathering ( of tables )

4. Relationship normalization to eliminate the anomali

5. Implementation to database by making table to each;every relationship is normalization

Normalization of Data Bases

Normalization is process of forming of structure of data bases so that most ambiguity can be eliminated. Normalization Phase started from lightest phase ( 1NF) tightest till ( 5NF). Usually only come up with the storey;level 3NF or BCNF of because have adequate enough to yield the tabless which good with quality.

Normalization done by because to Optimalisasi Structure of is tables, Improving speed, Eliminating same data inclusion, More efficient in storage media use, Lessening redundans, Avoiding anomali ( insertion anomalies, deletion anomalies, update the anomalies) and improved Data integrity.

A tables told by goodness of if fulfilling 3 criterion :

· If there is decomposition of is tables of, hence decomposition have to be secured ( Lossless-Join Decomposition

· The looking after of functional depending at the (time) of data change ( Dependency Preservation).

· Do not impinge the Normal Boyce-Code of Form ( BCNF)

If third criterion ( BCNF) cannot be fullfiled, hence at least the tables do not impinge the Normal Form of third phase ( 3rd Normal of Form / 3NF )

Functional Dependency

Functional Dependency depict the relation attributes in a relationship. A attribute told by functionally dependant of at other if we use the the attribute value to determine the other attribute value.

· Symbol used is (→) to deputize the functional dependency

· Notation: A → B

A And B is attribute from a tables. Mean functionally A determine the B or B of depend on A, if and only if there is 2 data line with the same value A, hence assess the B also is of equal

· Notation: A /→ B Or A x→ B

Is reverse from previous notation.

Functional Dependency (FD)

Functional Dependency from tables of value

· Nrp → Name

Because to each;every same value Nrp, hence assess the Name also is of equal

· { Mata_Kuliah, NRP } → Value

Because attribut Assess depend on Mata_Kuliah and NRP by together. In other;dissimilar meaning for the Mata_Kuliah of same and NRP, hence Assess also is of equal, because Mata_Kuliah and NRP represent the key ( having the character of unique ).

· Mata_Kuliah ∕→ NRP

· NRP /→ Assess

First Normal Form - 1NF

A tables told to be at the normal form of I of if he do not be at the form of unnormalized table, where happened [by] the field duplication which of a kind and enable there is field which null ( empty ).

Do not diperboleh of is existence of : valuable attribute a lot of, Composite Attribut or combination from both. Becoming : Price of Domain attribute have to represent the price atomic.

Second Normal Form - 2NF

Normal form 2NF fullfiled in a tables of if have fulfilled the form 1NF, and all attribute of besides primary key, intactly own the Functional Dependency of at primary key. A tables do not fulfill 2NF, if there is attribute which its depending only have the character of the just just parsial ( only depend on some of primary key) . If there are attribute which do not own the depending to primary key, hence the attribute have to be moved or eliminated.

· Functional depending of X → Y told by full of if vanishing a attribute A from X mean the Y shall no longger hinge functional.

· Functional depending of X →Y told by partial of if vanishing a attribute A from X mean the Y still hinge functional.

· Scheme of Relationship R in the form of 2NF if each every attribute of is non primary key A € R hinge full of by fungsioanl at primary key R.

· Tables in the following is fulfilling 1NF, but [do] not the inclusive of 2NF :

{NIM, KodeMk} → NameMhs

{NIM, KodeMk} → Address

{NIM, KodeMk} → Matakuliah

{NIM, KodeMk} → Sks

{NIM, KodeMk} → LetterValue

· The tables require to decomposition become some up to standard tables 2NF

· Functional dependency:

{NIM, KodeMk} → LetterValue (fd1)

NIM → {NameMhs, Address} (fd2)

KodeMk → {Matakuliah, Sks} (fd3)

· becoming :

fd1 (NIM, KodeMk, LetterValue) → Tables Value

fd2 (NIM, NameMhs, Address) → Tables of student

fd3 (KodeMk, Matakuliah, Sks) → Tables MataKuliah

Third Normal Form - 3NF

Normal form 3NF fullfiled by if have fulfilled the form 2NF, and otherwise there is attribute of is non primary key owning depending to attribute of is non primary key the other ( depending transitif)

· So that the tables require to didekomposisi become.:

· Student (NIM, NameMhs, Jalan, KodePos)

KodePos (KodePos, Provinsi, Town)

.

Boyce-Codd Normal Form (BNCF)

Normal Boyce-Codd of Form have the constraint which is stronger than Normal form third. To become the BNCF, relationship have to in the form of Normal of First and each;every attribute forced to base on the function of at super attribute of key.

At example of hereunder there are Seminar relationship, Lock the Primary is NPM + Seminar.

Student may take one or two seminar. Each Every seminar require 2 counsellor and each every student guided by one of among 2 the seminar counsellor. Each Every counsellor only may take one just just seminar. At this example of is NPM And Seminar show a Counsellor.

Form the Seminar Relationship is Normal form Third, but do not BCNF. Because Seminar Code still hinge the function of at Counsellor, if each;every Counsellor can teach only one seminar. Seminar base on one attribute of non super of key such as those which disayaratakan by BCNF. Hence Seminar relationship have to be broken to become two that is :

Fifth and fourth Normal form

Relationship in the form of fourth normal ( 4 NF) if relationship in BCNF and do not contain the depending of a lot of value. To eliminate the depending of a lot of value from one relationship, we divide the relationship become two new relationship. Each relationship contain two attribute having relation of a lot of value. Relationship in the form of fifth normal ( 5NF) dealing by properti is so-called join without existence of information loss ( lossless join). fifth Normal form ( 5 NF is also referred as by PJNF ( normal projection join of form). This case very rare emerge and difficult to be detected practically

ER-Diagram

DATABASE AND ER-DIAGRAM

DEFINITION of DATA BASES

o A group of on file data in magnetic disk, optical disk or depository of other sekunder.

o Inwrought collection from data which each other is interconnected the than a enterprise ( company, governmental institution or private sector)

· Manufacturing business : production planning data, newest production data, data of material ordering

· Hospital : patient data, doctor, nurse

DATABASE MANAGEMENT SYSTEM

Corps / database merger with the application software being based on database. This application program is used to access and look after the database. Especial target of DBMS is provide a efficient and easy environment for the use of, withdrawal and depository of data and information.

Beet : representing part of containing smallest data assess 0 or 1

Byte : corps from beet which of a kind

Field : a group of byte-byte which of a kind, in data bases used by a attribute term

ATTRIBUTE TYPE

o Single Vs multivalue

Single : only can be filled by at most one value

Multivalue : can be filled with interest from one value with the same type

o Atomic Vs composition

Atomic : indiscrete into smaller attribute

Composition : representing merger from some smaller attribute

o Derived Attribute

Attribute which its value can be yielded from other;dissimilar attribute value, for example : age yielded from attribute of is date of delivering birth

o Null Value Attribute

Attribute which do not own the value to a record

o Mandatory Value Attribute

Attribute which must own value

RECORD/TUPLE

Representing a data line in a relationship. Consist of by attribute corps of where the attribute interact to inform the entitas / relationship completely.

ENTITAS/FILE

File represent the corps from record which of a kind and have the same element, attribute of is of equal but different each other data value. File Type in application process : Mains File, Transaction File, Report File, History File, Protector File, File Work.

DOMAIN

Domain represent the corps from values enabled to stay in one or more attribute. Each Every attribute in a bases of data relasional defined by as a domain

LOCK THE DATA ELEMENT

Key is element record weared to find the the record when accessing or can is also used to identify each every entity/record/line.

TYPE KEY

o Superkey represent one or more attribute from a tables which can be used to identify the entityty / record from tables uniquely ( do not all attribute can become the superkey)

o Cadidate Key is super of key with the minimum attribute. Candidate Key may not contain the attribute from other tables so that candidate key beyond question superkey but not yet of course on the contrary.

o Primary Key

One of attribute from candidate key can be selected / determined to become the primary key with three criterion :

1. The Key more natural to be used as reference

2. The Key more simple

3. The Key well guaranted unique.

o Alternate Key is attribute from candidate key which is not chosen become the primary key.

o Foreign Key represent any attribute subjecting to primary key of at other tables. Foreign Key will be became of by a relationship owning kardinalitas one to many ( one to a lot of) or many to many ( a lot of to a lot of). Foreign Key usually is always placed at tables aiming to a lot of.

o Key represent a lexical attribute ( or gathering of lexical attribute) what its values always identify one object instance

ERD ( ENTITY RELATIONSHIP DIAGRAM)

ERD is a network model using wording kept in system in abstraction. DFD represent a model of function network to be executed by system of while ERD represent the model of data network emphasizing at structure and relationship data.

ELEMENT ERD

o Entity

At ER of Diagram Entity depicted with form the long sided square. Entity is something that there is in real system and also abstraction of where on file data or where there are data.

o Relationship

At ER of Diagram relationship depicted with a lozenged. Relationship is natural link that happened between entitas. Generally called with the elementary so that facilitate to conduct the its relationship read. Relationship Degree is amount entitas participating in one relationship. Degree which is often weared in ERD.

o Attribute is the nature of or characteristic of each entitas and also relationship

o Kardinalitas is showing optimum tupel which can berelasi by entitas at other entitas

DEGREE RELATIONSHIP

Unary Relationship is model relationship that happened between entity coming from entity set same.

Binary Relationship is model relationship that happened between 2 entitas.

Ternary Relationship is representing relationship of between instance from 3 type entitas unilaterally.

KARDINALITAS

There are 3 kardinalitas relationship that is :

One To One : Mount the relation one to one expressed with one occurence of at first entitas, only have one relation with one occurence of at secondary entitas conversely.

One To Many or Many to One : Mount the relation one to a lot of is equal to a lot of to one depended from which direction of the relation seen. To one occurence of at first entitas can have a lot of relation with the occurence of at secondary entitas, if on the contrary one occurence of at secondary entitas can only have the relation with one occurence of at first entitas.

Many To Many : happened by if each every occurence of at a entitas have the a lot of relation with the occurence of at other entitas

NOTATION ( DIAGRAM E-R)

Symbolic Notation in diagram ER is :

1. Long sided square express the gathering entitas

2. Radian express the attribute

3. Rhombic express the relationship gathering

4. Mark with lines as link of between relationship gathering with the gathering of entitas and Gathering Entitas with its attribute