DATABASE NORMALIZATION

NORMALIZATION

Process The Database Device ( Review )

· Collecting requirement user / business

· Developing E-R Model pursuant to requirement user / business

· Converting E-R Model to relationship gathering ( tables )

· Relationship normalization to eliminate the anomali

· Implementation to database by making table to 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.

Why is conducted normalization ?

Normalization Conducted bcause :

• Optimalisasi Structure of is tables of
• Improving speed
• Eliminating same data inclusion
• More efficient in storage media use
• Lessening redundansi
• Avoiding anomali ( insertion anomalies, deletion anomalies, update the anomalies).
• improved Data integrity

A tables told by goodness ( efisien) or normal if fulfilling 3 criterion :

· If there is decomposition of is tables of, hence decomposition have to be secured ( Lossless-Join Decomposition). Its Meaning, after the tables elaborated / decompositioni become the new tabless, the new tabless can yield the tables from the beginning with is of equal precisely.

· 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 read functionally determine

• 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

Example :

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 can is existence of :

· valuable attribute a lot of ( Multivalued Attribut).

· Composite Attribut or combination from both.

Becoming :
Price of Domain attribute have to represent the price atomic

EXAMPLE (1)

Or

• Tabless of is above ineligibility 1NF

EXAMPLE (2)

• Tables of student

• Tables of Hobby

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 ( Functional Dependency) 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 an 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 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) (1)

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 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. Every seminar require 2 counsellor and every student guided by one of among 2 the seminar counsellor. 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 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 tdak 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

DATABASE AND ER-DIAGRAM QUIZ

DATABASE AND ER-DIAGRAM

DEFINITION of DATA BASES

Definition of data Bases:

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

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

- Manufacturing business : production planning data, data produce the aktual, data of material ordering, dll

- Hospital : patient data, doctor, nurse, dll

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 an efficient and easy environment for the use of, withdrawal and depository of data and information

BEET, BYTE, FIELD

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

ATRIBUT/FIELD

Representing the nature of or characteristic from an entitas providing clarification detail of about the entitas

An relationship also can own the attribute

Follow the example of the attribute:

- STUDENT: NIM, NAME, ADDRESS

- CAR: NOMOR_PLAT, COLOUR, TYPE, CC

ATTRIBUTE TYPE

- 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

- Atomic Vs composition

Atomic : indiscrete into smaller attribute

Composition : representing merger from some smaller attribute

-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

-Null Value Attribute

Attribute which do not own the value to an record

-Mandatory Value Attribute

Attribute which must own value

RECORD / TUPLE

Representing an data line in an 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 an bases of data relasional defined by as an domain

LOCK THE DATA ELEMENT

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

TYPE KEY

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

- 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.

- 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.

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

- Foreign Key represent any attribute subjecting to primary key of at other tables. Foreign Key will be became of by an 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.

- Key represent a[n lexical attribute ( or gathering of lexical attribute) what its values always identify one

object instance

ERD ( ENTITY RELATIONSHIP DIAGRAM)

ERD is an network model using wording kept in system in abstraction.

Difference of between DFD and ERD :

- DFD represent an model of function network to be executed by system

- ERD represent the model of data network emphasizing at structure and relationship data

ELEMENT ERD

- ENTITY

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

- Relationship

At ER of Diagram relationship can be depicted with a lozenged. Relationship is natural relation that happened between entitas. Generally called with the elementary verb. so that facilitate to do the its relationship read.

- Relationship Degree

is amount entitas participating in one relationship. Degree which is often weared in ERD.

- Attribute

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

- Kardinalitas

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

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 of looking.For one occurence of at first entitas can have the many relation with the occurence of at secondary entitas, if on the contrary one occurence of at secondary entitas can only have the relationwith one occurence of at first entitas.

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

NOTATION ( DIAGRAM E-R)

Symbolic Notation in diagram ER is :

- Long sided square express the gathering entitas

- Radian express the attribute

- Rhombic express the relationship gathering

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

DFD QUIZ

DATA FLOW DIAGRAM

FLOW CHART DATA

- Referred as also Data of Flow Diagram ( DFD)

- Depicting system division into smaller module

- Facilitating wearer which less comprehend the computer area to understand the system to be done

CONTEXT DIAGRAM

- Consist of by one process and depict the scope from an system

- Representing highest level from DFD depicting entire/all input to system and outpur from system

- System limited by boundary ( depicted by dash line)

- There may not be any depository ( storage)

DIAGRAM of ZERO

Depicting process from DFD

Opening on to by totally hit the system handled, showing existing especial process or function, stream of data and eskternal entity

At this level is enabled by the existence of data storage

For the process of which is not detailed again at level hereinafter is hence enhanced by a symbol ‘*’ or ‘ P’ by the end of number process the

balance of Input and output ( balancing) of between diagram 0 with the context diagram have to be looked after

DIAGRAM DETAILED THE

Representing Diagram elaborating process of what there is in diagram of zero or level of above his

Nama Level	Nama Diagram	Nomor Proses
0	Context
1	Diagram 0	1.0, 2.0, 3.0, ...
2	Diagram 1.0	1.1, 1.2, 1.3, ...
3	Diagram 1.1	1.1.1, 1.1.2, ...

In one level shall not there are more than 7 fruit process and maximal 9, if/when more hence must be done dekomposisi.

SPECIFICATION OF PROCESS

Each Every process of at DFD have to own the specification of process

At top level method used to depict the process earn by using descriptive sentence

At more level detailed that is at process most under ( functional primitive) requiring more specification is structure

Specification of process will become the guidance for programmer in making program ( coding)

Method used in specification of process: breakdown of process in the form of story, decision table, decision tree

EXTERNAL UNION

Something that of beyond system, but he give the data into system or give the data from system

Symbol : notation box

External of entity do not the inclusive of the part of system

Naming:

Terminal name in the form of noun

Terminal may not own the same name except its object (it) is true is of equal

DATA CURRENT

Representing place emit a stream of it information

Depicted with the connective straight line ofcomponent from system

Data current shown with the direction bow and mark with lines called by for data current emiting a stream of

Data current emit a stream of among process, data storage and show the data current from data which is in the form of input for the system of.

DATA CURRENT 2

guidance of name Gift:

name of data Stream consisted of by some word stream attributed to by a continued line

There may not be any data stream which its name is of equal, name gift have to mirror the its contents

Data stream consisted of by some element can be expressed by grup is element

Avoid the word use ‘ data’ and ‘ information’ to give the name of at data stream

name of data Stream writed complete.

DATA CURRENT 3

Rule of is other dissimilar:

name of data Stream which come into a[n process may not is equal to secretory data stream name from the process

incoming data Flow, go out from data storage needn't be called by if:

- simple Data stream, is easy to comprehended,

- Data stream depicting entire/all data item

There may not be any data stream from terminal to data storage or on the contrary because terminal of non part of system, terminal relation with the data storage have to passing process

PROCESS

Process represent what done by system

Process earn the mengolah of data or incoming data stream become the data stream go out the

Process function the mentransformasikan one or some input data become one or some output data as according to specification wanted

Each Every process own one or some input and also yield one or some output

Process is often referred as by bubble.

PROCESS 2

guidance of name Gift process the:

Name process consisted of by the verb and noun mirroring the process function

Don't use the word process as part of name of an bubble

There may not be any some process owning same name

Process have to be given by number.

DATA DEPOSIT

Data Storage represent the existing data repository in system

Symbol : a couple of parallel line / two line wrongly one side from other side opened

Process can take the data from or give the data to database

guidance of name Gift:

- Name have to mirror the data storage,

- If its name more than one word hence have to be said the word to by joint

DATA DICTIONARY

Functioning assist the system perpetrator to interpret the application in detail and organizational all data element used by a system precisely so that wearer and system analyst have the same congeniality base about input, output, depository and process

At phase analyse the, data dictionary used as by communication means of between system analyst with the wearer

At phase of system scheme, data dictionary used to design the input, report and database

Data current of at DAD have the character of global, boldness more detailed visible at data dictionary

Data dictionary load the followings:

- name of data Current: have to be noted by reader to needing furthermore clarification about a[n data current can look for it easily

- name of is differend from data can be writed by if (there are) any

- Data form: used to group the data dictionary into its use time of system scheme

- Data current: showing where from data emit a stream of and where data go to the

- Clarification: giving clarification of about meaning from data current

BALANCING of IN DFD

Data Stream which come into and go out from one process have to is equal to data stream which come into and go out from detail of process of at level / level of under.

Name of data Stream which come into and go out from one process have to is equal to incoming data stream name into and go out from detail of the process

Sum up and external name entitas from a[n process have to is equal to amount and external name entitas from detail of the process

Things which must be gave attention to by DFD owning more than one level:

- Have to there are balance of input and output of between one next level and level

- Balance of between level 0 and level 1 seen at input / output from data stream to or from terminal of at level 0, while balance of between level 1 and level 2 seen at input / output from data stream to / from pertinent process

- name of data Stream, data of storage and terminal of at every level have to is of equal if its object is of equal

INTERDICT of IN DFD

Data Current may not from direct external entitas go to the other external entitas without through an process

Data current may not from direct data deposit go to the external entitas without through an process

Data current may not from direct data deposit go to the other data deposit without through an process

Data current from one direct process go to the other process without through an data deposit better / can is possible avoided