DATABASE NORMALIZATION

Design Database Process (review)
The process includes:
• Gathering user or business's requirement
• Develop E-R Model bases user or business's requirements
• Convert E-R Model go to relationship gatherings (table)
• Relationship normalization to remove anomaly
• Implementation goes to database by make table for each relationship already most normalization

Database Normalization
Normalization is process of database structure forming so mainly part of ambiguity can be removed. Normalization phase is begun from lightest phase (1NF) until tightest (5NF). Usually, only coming up with level 3NF or BCNF because has adequate enough to yield the tables of which with quality goodness.
Why done by normalization?
• Optimizing table structures
• Increasing speed
• Removing same data inclusion
• More efficient in storage media purpose
• Reducing redundancy
• Avoiding anomaly (insertion anomalies, deletion anomalies, and anomaly's update).
• Increased data integrity

One table is said well (efficient) or normal if accomplish 3 criterions as follows:
If there is decomposition (parsing) table, therefore the decomposition shall be secured safe (Lossless Join Decomposition). It’s mean, after that table is untied / at decomposition becomes new tables, that new tables can result original table equally exactly
Its preserve dependency functional at the moment data change (Dependency Preservation).
Don't breach Boyce-Code Normal Form (BCNF)

If the third criterion (BCNF) can't be accomplished, therefore at least that table not breach the third Normal Form (3rd Normal Form/ 3NF).

Functional Dependency
Functional Dependency is describe attributes interaction 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 be used is –> to represent the functional dependency. –> read the functional determining.
• Notation : A  B
A and B are attributes of a table. Means that the functional A determining 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.




Functional Dependency:
NRP -> Name
Mata_Kuliah, NRP -> Value
Non Functional Dependency:
Mata_Kuliah -> NRP
NRP -> Value
Functional Dependency of the table value
Nrp -> Name
Because for each value Nrp the same, then the value of the same name
(Mata_kuliah, NRP) -> Value
Because the value of attributes depending on the NRP and Mata_kuliah together.
In another sense Mata_kuliah for the NRP and the same, they also rated the same,
because Mata_kuliah and the NRP is a key (is unique).
Mata_kuliah -> 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 allowed:
Many attributes of value (Multivalued attributes).
Attributes composite or a combination of both.
So: Price is the domain attribute must be atomic rates
Eg Student Data as follows:

The table above does not meet the requirements 1NF
Decomposition as follows :

Second Normal Form - 2NF
2NF's normal form is accomplished in one table if have accomplished 1NF's form, and all attribute besides primary key, as whole to have Functional Dependency on primary key. One table will not accomplishes 2NF, if there is attribute which its dependency (Functional Dependency) just have partial character only (just depend on a part of primary key). If anything attribute that have no dependency for primary key, therefore that attribute have to move or is removed. Functional dependency X Y is said full if erasing an attribute A of X its mean that Y no longer functional dependent. Functional dependency X Y is said partial if erases an A's attribute of X matter Y still functional dependent. Relationship scheme r on 2NF form if each attribute non primary key A R full dependent functionally on primary key R.
The following table meet 1NF, but not meet 2NF :

Does not meet 2NF, because (NIM, KodeMk) is regarded as the primary key:
(NIM, KodeMk) -> NamaMhs
(NIM, KodeMk) -> Address
(NIM, KodeMk) -> Matakuliah
(NIM, KodeMk) -> SKS
(NIM, KodeMk) -> NilaiHuruf
Table needs to be some table decomposition eligible 2NF
Functional dependency as follows:
(NIM, KodeMk) -> NilaiHuruf (fd1)
NIM -> (NamaMhs, Address) (fd2)
KodeMk -> (Matakuliah, SKS) (fd3)
So that:
fd1 (NIM, KodeMk, NilaiHuruf) -> Value Table
fd2 (NIM, NamaMhs, Address) -> Table Student
fd3 (KodeMk, Matakuliah, SKS) -> Table MataKuliah
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).
The following table meet 2NF, but not meet 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 KodePos):
KodePos -> (City, Province)
So that the table should be dicomposition as follows:
Student (NIM, NamaMhs, Road, KodePos)
KodePos (KodePos, Province, City)

Boyce-Codd Normal Form (BNCF)
Boyce Codd Normal Form has stronger compulsion of third Normal form. To become BNCF, relationship shall in first Normal form and each attribute is forced dependent on function on attribute super key. On example hereunder is exists Seminar relationship, Prime key is NPM + seminar.
Student may take one or two seminar. Each seminar needs 2 counselors and each student led by one of between 2 that seminar counselor. Each counselor just may take one seminar only. On this example NPM and seminar points out a Counselor.


Seminar Relationship
NPM Seminar Counselor
1000 S100 Arya
1001 S100 Ayu
1002 S101 Mezi
1001 S101 Mezi
1003 S101 Edi
Seminar Relationship form is third Normal Form, but not BCNF since Seminar Code is still dependent function on Counselor, if each Counselor gets to teach just one seminar. Dependent seminar on one attribute is not super key as presupposed by BCNF. Therefore Seminar relationship shall be broken down as two which is:

Counselor Relationship
Counselor Seminar
Arya S100
Ayu S100
Mezi S101
Edi S101

Seminar-Counselor Relationship
NPM Counselor
1000 Arya
1001 Ayu
1002 Mezi
1001 Mezi
1003 Edi




Fourth and fifth Normal Form
Relationship in fourth normal form (4 NF) if relationship in BCNF and not contains of multi value dependency. To remove multi value dependency from one relationship, we are divide relationship become two new relationships. Each relationship contains of two attributes that have multi value relationship.
Relationship in fifth normal form (5NF) get business with property is called join without marks sense information loss (lossless join). The fifth normal Form (5 NF also called PJNF (projection join normal form). This case is very rare to appearance and hard to be detected practically.
References:
1. Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Database and ER-Diagram

DATABASE AND ER-Diagram

DATABASE DEFINITION
Database can be definition by collection of data which stored in magnetic disk, optical disk or other secondary storage. In other word database is collection of data which is linked one to other of an enterprise (factory, governmental institution, or private section). he database can be a collection of integrated data-related data of an enterprise (company, government or private). For example:
1. In Company data à manufacturing production planning, actual production data, data, material ordering, and so forth.
2. In hospital patient data, doctor, nurse, and so forth.
Database Management System
Database management System is database collection with software application in based of database. This application programs used to access and protected database. The purpose DBMS is to provide an environment for using, collecting and storing data and information.

Definition of bit, byte, field
- Bit: is the smaller part of data, which contain of value 1 or 0
- Byte is a collection of same bit. 1 byte = 8 bit.
- Field set of byte-byte similar, in the database used the term attribute

ATTRIBUTE / FIELD
Attribute or field is characteristic from an entity o provide description about entity. An relation can have attribute too. Attribute example:

Table BOOK : ID, NAME, PUBLISHER, COMPOSER
Table CAR : NOMOR_PLAT, COLOR, KIND, CC

ATTRIBUTE TYPES
• Single attribute vs multivalue attribute
Single attribute is an attribute that can only be filled at most one value.
Multivalue attribute is an attribute that can be filled with more than one value with the same type.
• Atomic vs composition
Atomic attribute is an attribute that can not be divided into smaller attributes.
Composite attribute is a combination of several attributes of a smaller.


• Derived attribute is an attribute which its value can be produced from other attribute
• Null Value attribute is an attribute that has no value to a record.
• Value attribute is a mandatory attribute that must have a value.




RECORD or TUPLE
Record is a data line in a relationship. Record consists of a set of attributes where the attribute is an attribute-related entity or to inform the full relationship.

ENTITY / FILE
Entity is the object or objects in a certain mini world represented in the database and file is a group of same record and have same element, same attribute but different of each data value. Type File In application process, category of file such as:
- Master File
- File Transaction
- File Report
- File History
- File Protected
- File Activity
- File Work

Domain Domain is collection of values which enabled to stay in one or more attribute. Every attribute in a relational database is defined as a domain

Key of element data
Key is the element of record which used to find the record when accessing or can also used to identify every entity / record / row.

Type of KEY
• Superkey is one or more attributes of a table that can be used to identify entityty / record of the table are unique (not all attributes can be superkey)
• Cadidate Key is super key with minimum attribute. Candidate key may not contain attribute of other table so that the candidate is super key, but not the contrary
• Primary Key One of the key attributes of the candidate can be selected / specified a primary key with the three criteria, namely:
1. Key is more natural to use as reference
2. Key is more simple
3. Key is guaranteed unique
• Alternate Key is the attribute of the candidate key is not selected to be primary key.
• Foreign Key is any attribute that points to the primary key in another table. Foreign key will be going on a relationship that has cardinality one to many or many to many. Foreign key usually put at the table which direct to many cardinality.
• External Key is a lexical attribute (or set of lexical attributes) that values are always identify an object instance.

ERD (ENTITY RELATIONSHIP DIAGRAM)
ERD is a network model using word to keep in system abstract or an abstract and conceptual representation of data. Difference between DFD and of ERD: DFD represent a function network model to be executed by system and ERD represent data network model emphasizing at structure and relationship data.
Elements in ERD
- Entity
In the ER Diagram, entity is described with the form of a rectangle. Entity is something that exists in the real system and the abstract where the data stored or where there are data.
- Relationship
In the ER diagram, relationship can be described with a lozenge. Relationship is a natural relationship that occurs between entities. In general, the name given to the verb base making it easier to do the reading it relations.
- Relationship degree
Relationship degree is the number of entities which participating in a relationship. Degree is unit which is often used in the ERD.
- Attribute
Attribute is the nature or characteristics of each entity and relationship
- Cardinality
Cardinality indicates the maximum number of record/tuple that can be relation with the entity on the other entity.
Degree of relationship
- Unary relationship is the relationship model between the entity originating from the same entity set.
- Binary relationship is the relationship model between 2 entities
- Ternary relationship is a relationship between the instances of 3 types of entities is unilateral

Cardinality
There are three cardinality relations, thas is:
• One to One: Level one to one relationship stated with one event in the first entity, only had one relationship with one event in the second entities and conversely.
• One to Many or Many to One: Level one to many relationship is the same as the one to many depending on the direction from which the relationship view. For an incident on the first entity can have any relationship with many incident on the second entity, if the one incident on the second entity can only have one relation with the incident on the first entity.
• Many To Many: happening if each event on one entities has a many relationship with event on another entities.
Notation in ER-Diagram



The notation symbol of ER-Diagram is:
1. Rectangle express gathering of entity
2. Radian express attribute
3. Rhombic express relationship gathering
4. Line as link between relationship gathering with gathering of entity Gathering and Entity with its attribute




References:
- Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Database and ER-Diagram
- http://en.wikipedia.org/wiki/Database

PART V: ANALYSIS AND DESIGN OF INFORMATION SYSTEM

DATA FLOW DIAGRAM (DFD)


DFD is a logical data model or process that is made to describe the origin of the data and the destination where the data out of the system, where data is stored, the process that produces the data and the interaction between the data stored and the process is imposed on data.
DFD usually use to figured a system division into smaller module and can make user to easy that insufficiently understand computer area for understands system who will be worked. A data-flow diagram can be used for the visualization of data processing (structured design).
There is even symbol Data Flow Diagrams:
1. External Entity
2. Process
3. Data flow
4. Data Storage

CONTEXT DIAGRAM
System Context Diagram are diagrams used in systems design to represent all external entities that may interact with a system. This diagram pictures the system at the center, with no details of its interior structure, surrounding by all its interacting systems, environment and activities. The objective of a system context diagram is to focus attention on external factors and events that should be considered in developing a complete set of system requirements and constraints.
Context diagrams are used early in a project to get agreement on the scope under investigation. Context diagrams are typically included in a requirements document. These diagrams must be read by all project stakeholders and thus should be written in plain language, so the stakeholders can understand items within the document.

ZERO DIAGRAM
Zero Diagram is a diagram to describe process of data flow diagram. Zero Diagram is Providing views on the overall system in which, showing the main function or process that is, the flow of data and external entity.in this level may be existence storage data. For process which not detailed again in next level added by symbol ‘*’ or ‘ P’ in the end process number. Input balance and output ( balancing) between diagram 0 with context diagram have to be maintain.


DETAIL DIAGRAM
• Detail Diagram is a Diagram to describe process in zero diagram or high level.




• In one advisable level has no is more than 7 processes and maximal 9, if more therefore has to be done by decomposition




SPECIFICATION OF PROCESS
Each process in the DFD must have a specification. Process At the top level method is used to describe the process can use a sentence with descriptive At a more detailed level, namely on the bottom (functional primitive) requires specification of a more structured.
Specification process will be the guideline for the programmer to make this program (coding). Method used in the specification process: the process of disintegration in the form of a story, decision table, decision tree
SYMBOL in DFD:
1. External entity
2. Data Flow
3. Process
4. Data Store

A. EXTERNAL ENTITY
Something in outside system, but its give data into system or give data for system, symbol with a notation box. External entity not include of part system. one that included outer unity amongst those:
1. An office, department or division in corporate but system open air, which is developed.
2. Person or a group person at organization but outside sys tem which being developed.
3. An organization or person of outside organization as e.g. subscription, provider.
4. Outdoor other information system, system being developed.
5. Original source of a transactions


B.DATA FLOW

Symbol with the dart, where is data flow is flow between process, data trove.. Data flow symbol with straight line to connective component of system. Data flow direction is indicated with arrows and lines give the name on the flow of data that flows. Flow data flow between processes, data storage and data flow indicates that the form of data input for the system.
Guide of Data Flow name:
• Name of data flow which consist of some word flow attributed with continued line.
• There may not be any data flow which its same name and gift of name have to express its contents
• Data stream which consist of some element can be expressed with element grup

C.PROCESS
Process is activity or job that did by person, machine or computer of result a data flow that turns in at to process to been resulted data flow that will come out from process. Function of process is transformation one or some input data become one or some output data as according to specification. Each process have one or some input and also result one or some output data. Frequent process may be called by bubble. Symbols used : circle
Name of the process consists of a verb and noun, which reflects the function of the process
Do not use the process as part of the name of a bubble. May not have some process that has the same name. The process should be given a number. Order number wherever possible to follow the flow of the process or sequence, but the sequence number does not mean that the absolute is a process in chronological order.

D.DATA STORAGE
Data store is storage place for data that exists in the system. Data store symbol with couple two parallel line or two line wrongly one side from other side openly.
The process can retrieve data from or provide data to the database as follow:
1. file or database in the computer system
2. manual record keeping or
3. the data in a table
4. table reference manual
5. agenda book
Guide of Data Store name:
• The name should reflect the data storage
• When his name more than one word must be marked with the number







Dictionary of Data (Catalog Data)
Is a catalog (storage) of the elements that are in a system. Data dictionary is also called with a system data dictionary is a catalog of facts and data information needs of an information system. In function to help system agent to interpreting application in detail and organization all of data element that utilized by system exactly so user and system analyst have same understanding basic about entry, output, storage and process.
On analysis stage, data dictionary constitute communication device among user and system analyst about data which is flow in system, which is about in going data to system and about information which needed by user. In the meantime, on lexical system scheme stage data is utilized to design input, reporting and database. Data flow in DFD have the character of globally, boldness more detailed can be seen in data dictionary.
Data dictionary load the followings :
• Name of data current: must note that readers who need further explanation about a flow of data can find it easily
• Alias: alias or other name of the data can be written when there is
• Forms of data: used to segment the data dictionary to use when designing the system
• Flow data: indicates from which data flows and where the data
• Description: to give an explanation of the meaning of the data flow

Balancing In DFD
Data flow into and out of a process must be the same as the flow of data into and out of the details of the process on the level / levels below. Number and the name of an entity outside the process must be equal to the number of names and entities outside of the details of the process. We must be considered in the DFD which have more than one level:
• Shall available input balance and output among one level and next level
• Balance among level 0 and level 1 is seen on input / output of data flow goes to or of terminal on level 0, meanwhile balance among level 1 and level 2 are seen on input / output of data flow goes to / of process that pertinent
• Data streaming name, storage data and terminal on every level shall same object.

Prohibition in DFD
• Data flow may not from external entity directly to wend another external entity without passing a process.
• Data flow may not from direct data deposit go to external entity without passing a process
• Data flow may not from direct data deposit go to other data deposit without passing a process
• Data flow from one direct process go to other process without passing a data deposit better possible avoided.


References:
• ER Ngurah Agus Sanjaya, S.KOM, M.KOM. Slide Part 4 - DATA FLOW DIAGRAM.
• HM, Jogiyanto. 2005. Analisis & Desain Sistem Informasi. Yogyakarta: ANDI.
• http://id.wikipedia.org/wiki/Data_flow_diagram