DATABASE NORMALIZATION

NORMALIZATION

Process Device Database (review)

- Gather user needs / business

- Develop a needs-based ER Model user / business

- Convert E-R model to the set of relations (table)

- Normalize the relations to remove anomalies

- Implemented to create a database with a table for every relationship that has been normalize

Normalization Bases Data

Normalization is process forming of data bases structure so that most ambiguity can be eliminated. Phase Normalization started from lightest phase ( 1NF) tightest till ( 5NF). Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.

Normalization is done to :

- Optimizing table structures

- Improving speed

- The income data is the same

- More efficient in usage of storage media

- Reduce redudancy

- Avoiding anomaly (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 the decomposition (Lossless-Join Decomposition). That is, after the table is described a new table-table, the table-table can generate a new table with the same exact.

- The looking after of depended functional at the (time) of change of data (Dependency Preservation).

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

If the three criteria (BCNF) can’t be meet, then at least the table doesnt violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Functional Dependency (1)

Functional Dependency depict relation of attributes in a relationship. An attribute said functionally dependant on the other, if we use the value attribute to determine the value of the other attributes. Symbol the used is,for representing dependency functional, read functionally determine.

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 grade of A, then B is also the same grade.

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

It is the opposite of the previous notation.

EXAMPLE :

Functional Dependency:

NRP -> Name

(Course, NRP) -> Grade

Non Functional Dependency:

Course -> NRP

NRP -> Grade

Functional Dependency From Table Grade :

NRP -> Name

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

(Course, NRP) -> Grade

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

Course -> NRP

NRP -> Grade

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

Is not allowed there :

- Many attributes of value (Multivalued attributes).

- Attributes composite or a combination of both.

So :

- Price is the domain attribute must be atomic rates

Example the following Student Data :

Or

Table-the table above doesnt meet the requirements 1NF

Decomposition to be :

Student Table :

Hobby Table :

Second Normal Form (2NF)

Normal form 2NF met in a table if it meet 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 (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 dependency X -> Y is full if it is said to delete an attribute A from X means that Y is no longer dependent functional.

- Functional dependency X -> Y said if deleting a partial 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 primary key functional R.

Example 1

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

Doesnt meet 2NF, because (NIM, Course Code) is regarded as the primary key:

(NIM, Course Code) -> Student Name

{NIM, Course Code} -> Address

{NIM, Course Code} -> Course

{NIM, Course Code } -> Sks

{NIM, Course Code } -> Value of the letter

Table needs to be some table decomposition eligible 2NF

Functional dependency as follow:

– {NIM, Course Code } ->Value of the letter (fd1)

– NIM -> (Student Name, Address) (fd2)

– Course Code -> (Course, Sks) (fd3)

So :

– fd1 (NIM, Course Code, Value of the letter) -> Value Table

– fd2 (NIM, Student Name, Address) -> Student Table

– fd3 (Course Code, Course, Sks) -> Course Table

Third Normal Form (3NF) (1)

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

Table following students eligible 2NF, 3NF, but doent meet

Because there are non-primary key attribute (City and Provincial), which has a dependence on non-primary key attributes of the other (postal code)

Postal Code -> (City,Province)

Example 2

So that the table should be decomposition:

Student (NIM,Student Name, Road, Postal Code)

- Postal Code (Postal Code, Province, City)

Boyce-Codd Normal Form (BNCF)

Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in first normalize type and forced each of the attributes depends on the function in the super key attributes. In the example below there is a relationship seminar, is the Primary Key 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 show a Seminar preceptor.

Relations Seminar is a form of Third Normal, but not BCNF Seminar Code because the function depends on the Preceptor, if any Preceptor can only teach a seminar. Seminar depend on one key attribute is not as super requirement by BCNF. So Seminar relations must be parsed into two namely:

Normal form of the fourth and fifth

- Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency not 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.

Refference :

Agus Sanjaya ER, S.Kom, M.Kom, Slide Part 6 - Database and ER-Diagram

DATABASE AND ER-DIAGRAM

DATABASE AND ER-DIAGRAM

DATABASE DEFINITIONS
Database is a group of on file data in disk magnetic, disk optical or is depository of other sekunder. Inwrought collection of data-data which each other is interconnected the than a enterprise (company, governmental institution or private sector). Example:
- Manufacturing business (production planning data, data produce aktual, data ordering of material, etc)
- Hospital (patient data, doctor, nurse, etc)

DATABASE MANAGEMENT SYSTEM
Database management system represent database aliance or corps with software of application being based on database. This application programs is used to access and look after database. Especial target of DBMS is to provide a efficient and easy environment for the usage of, to withdrawal and is depository of information and data.

BIT, BYTE, FIELD
- Bit representing part of containing smallest data of value 0 or 1.
- Byte representing corps of beets which of a kind.
- Field representing a group of byte-byte which of a kind, in data bases used by attribute term.

FIELD OR ATTRIBUTE
Field or attribute representing the nature of or characteristic from entitas providing to provide clarification of detail about entitas. A relationship also can have attribute. Example: STUDENT (NIM,NAMA,ADDRESS),CAR(PLAT_NUMBER,COLOUR,TYPE)

TYPE-TYPE ATTRIBUTE
- Single vs Multivalue:
- Single, only can fill at most one value
- Multivalue, can fill with interest from one value with same type

- Atomic vs Composition:
- Atomic, indiscrete into smaller attribute
- Composition, representing aliance from some smaller attribute

- Derived Attribute:
- Attribute which its value can be which yielded from other attribute value, for example age which yielded from attribute birthday
- Null Value Attribute:
- Attribute which don’t have value to a record

- Mandatory Value Attribute:
- Attribute which must have value

RECORD/TUPLE
Record or Tuple representing data line in a relationship. Consist of attributes corps where attributes of interaction to inform entitas or relationship completely.

ENTITAS/FILE
File represent corps of record which of a kind and have is same element, is same attribute but different each other its data.
- Type File
In application process, file can be categorized as follows:
- File Mains
- File Transaction
- File Report
- File History
- File Protector
- File Job

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

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

TYPES OF KEY
- Superkey represent one or more attribute from tables of able to be used to identify entity/ record of tables uniquely (not all attribute can become superkey)
- Cadidate Key is super of key with minimum attribute. Key Candidate may not contain attribute of other tables so that key candidate beyond question superkey but not yet of course on the contrary.
- Primary Key
One of the attribute of key candidate can be selected/ to be determined to become key primary with three the following criterion:
- The Key more natural to be used as reference
- The Key more simple
- The Key well guaranted it

- Alternate Key is attribute of key candidate which is not chosen become key primary
- Foreign Key represent any attribute subjecting to key primary at other tables. Key Foreign will happened at one particular relationship owning many to one cardinality ( one to many) or many to many ( many to many). Key Foreign usually is always put down by at tables of which is flange to many
- External of Key represent attribute lexical ( or gathering of lexical attribute) which is it values always identify one object of instance

ERD (ENTITY RELATIONSHIP DIAGRAM)
ERD is network model using wording which is kept in system abstractionly.
Difference between DFD and ERD:
- DFD represent a[n function network model to be executed by system
- ERD represent data network model emphasizing at and structure of relationship data

ELEMENTS OF ERD
- Entity
At ER Diagram of Entity depicted with square form of length. Entity is something that there is in real system and also abstraction where on file data or where there are data
- Relationship
At ER Diagram of relationship can be depicted with a is lozenged. Relationship is natural relation that happened between entitas. In general called with elementary verb so that facilitate to do read of its relationship
- Relationship Degree
Relationship Degree is the amount of entitas participating in one relationship. Degree of which is often weared in ERD
- Attribute
Attribute is the nature of or characteristic of each and also entitas of relationship
- Cardinality
Showing optimum of tuple able to relationship with entitas at other entitas

DEGREE OF RELATIONSHIP
- Unary Relationship
Unary Relationship is model of relationship that happened between entity coming from entity set same.
- Binary Relationship
Binary Relationship is model of relationship that happened between 2 entitas.
- Ternary Relationship
Ternary Relationship representing relationship between instance from 3 type of entitas unilaterally.

CARDINALITY
There are 3 relationship cardinality that is:
- One to One: Level relation one to one expressed with one occurence at first entitas, only having one relation with one occurence at second entitas conversely.
- One to Many or Many to One: Level relation one to many is equal to many to one depended from which direction of the relation seen. To one occurence at first entitas can have many relation with occurence at second entitas, if on the contrary one occurence at second entitas can only have relation with one occurence at first entitas.
- Many To Many: Happened if every occurence at a entitas have many relation with occurence at other entitas.

EXAMPLE OF CARDINALITY

NOTATION (DIAGRAM E-R)
Symbolic notation in diagram of ER is:
- Square of length express gathering of entitas
- Radian express attribute
- Rhombic of relationship gathering expressing
- Mark with lines as link between relationship gathering with gathering of entitas gathering and of Entitas with its attribute

DFD Quiz

DATA FLOW DIAGRAM (DFD)

Data flow diagram or DFD used to depicting the division of system into smaller module, and can facilitate less comprehending user of computer area to understand system to be done.

Diagram Context

This diagram is diagram of level highest of DFD depicting system relation with external environment.

- Consist of one process and depicting scope from a system

- System limited by boundary ( depicted by dash line)

- There may not be any is depository ( storage)

Diagram Zero

This diagram is dekomposisi of context diagram.

- Depicting process of DFD

- Give a fully regarding system handled , showing existing especial process or function , data stream and of eskternal entity

- At this level is enabled by the existence of data of storage

- For the process which isn’t detailed again at level hereinafter hence enhanced by symbol ‘*’ or ‘ P’ by the end of process number

- Balance input and of output ( balancing) between diagram 0 with context diagram have to be looked after

Diagram Detailed

Diagram detailed to represent diagram elaborating process what there is in diagram zero or level above

- Level numbering on DFD

NAME of LEVEL	NAME of DIAGRAM	PROSES NUMBER
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 process and maximal 9, if more hence must be done dekomposisi

Specification of Process

Every process at DFD have to have the specification of process. At method level top used to depict process earn by using descriptive sentence. At more level detailed that is at process most under (primitive functional) requiring the specification of more structure. Specification of process will become guidance to programmer in making program (coding). Method which is used in specification of process: breakdown of process in the form of story, table decision, tree decision.

External Unity

External unity is something that beyond system, but he give data into system or give data of system

- Symbol with notation box

- External entity not the including the part of system

Naming:

- Name of terminal in the form of noun

- Terminal may not have the name of same except its same object it is true

Data Current

Data current Represent place emit a stream of information.

- Depicted with connective straight line component of system

- Data current shown with direction bow and line called of data current emiting a stream

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

Data Current 2

Naming Guiding:

- Name of data stream which consist of some word stream attributed to continued line

- There may not be any data stream which its same name and giving of name have to express its contents

- Data stream which consist of some element can be expressed with element group

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

- As possible the name of data stream written is complete

Data Current 3

Other Rule:

- Name of data stream which come into a process may not is equal to name of secretory data stream of process

- Data of flow which step into or go out from data of storage needn't be called by if:

- Comprehended easy and simple data stream

- Data stream depicting all data item

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

PROCESS

Process represent what done by system, process can process data stream or data enter to become exit data stream.

- The process fucntion is transform one or some data become one or some output data as according to specification of which is wanted

- Every process have one or some input and also yield one or some output

- Process often is also referred as by bubble

Guidance giving process name:

- Name of process consist of noun and vb. expressing process function

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

- There may not be any some process owning the name of is same

- Process have to give number. Number sequence as possible follow process sequence or stream, but that way meaningless number sequence absolutely represent sequence process chronologically

DATA DEPOSIT

Data of Storage represent existing data repository in system. Symbol with a couple of parallel line or two line wrongly one side from other side openly. Process can take data from or give data to database.

- Naming Guiding:

- Name have to express data of storage

- If the name more than one word hence have to say the word to joint

SYMBOL DATA

DATA DICTIONARY

Function to assist perpetrator of system to interpret application in detail and organizational all used by data element is system precisely so that user and system analyst have is same congeniality base about input, output, depository and process. At analysis phase, data dictionary used as by communication means between system analyst with user. At phase scheme of system, data dictionary used to design input, database and report. Data current at DAD have the character of globally, boldness more detailed can be seen by at data dictionary.

Data dictionary load the followings:

- Name of data current: have to be noted reader to needing furthermore clarification about a data current can look for it easily

- Alias: name or alias of differ from data can be written if there are any

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

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

- Clarification: giving clarification about meaning of data current

BALANCING 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 at level / under level. Name of data stream which come into and go out from one process have to is equal to name of data stream which enter into and go out from detail of process. Amount and name of entitas external from a process have to is equal to name and amount of entitas external from detail of process.

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

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

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

- Name of data stream, data of storage terminal and at every level have to be is same if its is same object

PROHIBITION ORDER IN DFD

- Data current may not from direct external entitas go to other external entitas without passing a process

- Data current may not from direct data deposit go to external entitas without passing a process

- Data current may not from direct data deposit go to other data deposit without passing a process

- Data current from one direct process go to other process without passing a data deposit better / can possible avoided

Refference:

ER Ngurah Agus Sanjaya. Slide Part 4 - DATA FLOW DIAGRAM