Normalization

Database design process

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

Database normalization
Normalization process is the establishment of the database structure 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
• The income data is the same
• 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 (Lossless-Join Decomposition). That is, after the table is described / didecomposition 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 three 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 is used to represent the functional dependency.  read 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.




Functional Dependency:
• NRP  Nama
• Mata_Kuliah, NRP  Nilai
Non Functional Dependency:
• Mata_Kuliah  NRP

• NRP  Nilai

– 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  Nilai

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 the can:
• Many attributes of value (Multivalued attributes).
• Attributes composite or a combination of both.
So:
• Price is the domain attribute must be atomic rates

Example Student Data as follows:





Table-table above does not meet the requirements 1NF

Decomposition becomes:

Student Table:

Or
Table Hobbies:


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 suspended (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

Full functional X  Y dependency if it is said to delete an attribute A from X means that Y is no longer dependent functional.

Full functional 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 primary key fungsioanl R.



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



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

{NIM, KodeMk}  NamaMhs
{NIM, KodeMk}  Alamat
{NIM, KodeMk}  Matakuliah
{NIM, KodeMk}  Sks
{NIM, KodeMk}  NilaiHuruf
Table needs to be some table decomposite eligible 2NF



• Functional dependencynya they are:
– {NIM, KodeMk}  NilaiHuruf (fd1)
– NIM  {NamaMhs, Alamat} (fd2)
– KodeMk  {Matakuliah, Sks} (fd3)
• So :
– fd1 (NIM, KodeMk, NilaiHuruf)  Tabel Nilai
– fd2 (NIM, NamaMhs, Alamat)  Tabel Mahasiswa
– fd3 (KodeMk, Matakuliah, Sks)  Tabel 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).

Table following students eligible 2NF, 3NF, but does not meet



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  {Kota, Provinsi}
So that the table should be decomposite:
• Mahasiswa (NIM, NamaMhs, Jalan, KodePos)
– KodePos (KodePos, Provinsi, Kota)

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 the form of Normal the one 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.

Reference :

• NGURAH AGUS SANJAYA ER, S.KOM, M.KOM. Slide presentasi : Part 6 - DATABASE NORMALISASI

Entity Relationship Diagram (ERD)

1.1 Definition

Database a set of data stored in the magnetic disk, optical disk or other secondary storage.

1.2 DATABASE MANAGEMENT SYSTEM

· Collection / database combined with software-based database applications.

· Application programme are used to access and maintain databases

· The main purpose DBMS is to provide an environment that is efficient and easy to use, with draw and storage of data and information

1.3 Attribute / Field

• It is the nature or characteristics of an entity that provides provide detail on these entities
• A relationship can also have attributes
• Example attributes:
  STUDENTS: NIM, NAME, ADDRESS
  CAR: NOMOR_PLAT, COLOR, TYPE, CC

Type-Type Attribute :

• Single vs. multivalue
  Single àcan be filled only at most one valueà
  Multivalue à can be filled with more than one value with the same type.
• Atomic vs. composition
  Atomic à can not be divided into the attributes of smallerà
  composition àis a combination of several attributes of a smaller.
• Derived attribute
  attributes resulting from the date of birthàattribute value can be derived from the value of other attributes, example age
• Null Value attribute
  Attributes that have no value to a record
• Mandatory attribute Value
  Attributes must have values

1.4 Record / Tuple

• It is a line of data in a relationship
• Consists of the set of attributes where the attribute-attribute-attribute is to inform each other entity / relationship fully

1.5 Entity / File

File is a collection of similar records and have the same elements, the same attributes but different data value.

1.6 Domain
Domain is the set of values that are allowed to reside in one or more attributes. Each attribute in a database relasional is defined as a domain

1.7 Element Key Data
Key elements of record which is used to find these records at the time of access, or can also be used to identify each entity / record / line.

Species 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)
• Candidate Key is a super key with minimal attributes. Candidate must not contain a key attribute of the table so that the other candidate key is certain superkey but not necessarily vice versa.
• Primary Key
  One of the key attributes of the candidate can be selected / specified a primary key
• Alternate Key is an 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 kardinalitas one to many (one to many) or many to many (many to many). Foreign key is usually always put on the table that point to many.
• External Key is a lexical attribute (or set of lexical attributes) that values are always identify an object instance.

1.8 ERD

Entity relationship is a network that uses the order of the data stored in the abstract of the system. Entity-relationship model consists of elements of the entity and entity-relationship between these entitiy.

Component Entity Relationship :
1. Entity, a collection of object or something that can be identified or can be unique.
Collection of similar entities called entity set.
2. Relationship, the relationship between one entity or more.
3. Attribute, the collection of data elements that form a entity

4. Relationship Degree is the number of entities participating in a relationship. Degree which is often used in the ERD.

Degree of relationship:

• Unary Relationship
  model is the relationship between the entity originating from the same entity set.
• Binary Relationship
  model is the relationship between 2 entities.
• Ternary Relationship
  is a relationship between the instance of 3 types of entities are unilateral.

5. Cardinalitas tupel indicates the maximum number that can be entity with entities on the other entity.

There are 3 kardinalitas relations, namely :

• One to One: Level one to one relationship with the one stated in the entity's first event, only had one relationship with one incident in which the two entities and vice versa.
• 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 dilihat.Untuk an incident on the first entity can have any relationship with many incident on the second entity, if the one incident on the entity the second can only have one hubugan with the incident on the first entity.
• Many To Many: if any incident occurs in an entity most have relationships with other entities in the incident.

1.9 Notation (E-R diagram)
Symbolic notation, notation in the diagram in the E-R can be used are:

• Square long, shows the basic object / set entity
• Circle / ellipse, declares attributes (attributes do work as the key is underlined)
• rhomb (diamon), states Himpunan Relations
• Lines, as the relations between the Association
• Association with the Association and the Entity Entity with Atribute.

1.10 Phase Development E-R Diagram

• There are 2 groups of phasing the usual place, namely:
  1. Phase Diagram of making E-R initial (preliminary design).
  2. Phase Diagram otimasi E-R (final design)

• The first stage is to obtain the design minimal database that can accommodate the needs data storage to the system that is being reviewed.
• The second phase, attention to aspects of efficiency,performance and flexibility.

1.11 Making E-R Diagram Start
Technical steps to create ER Diagram Start is:
1. Identify and define the entire set of entity that will be involved.
2. Determining attribute-key attribute of each collective entities.
3. Identify and define the entire set of relations between the collective entity, the collective entity with no foreign-key it.
4. Determine the degree / kardinalitas relation to each the collective.
5. Complete set of entities and collective relations with attribute-attribute descriptive (non-key).

1.12 Case Studies
In the scope of the system indeed lecture (real word) there are many entities that we can Involve such as Student, College, Pratikum, Lecturer, Assistant, Room, Department, Literarur and others.

• To step-1, Identify and define all set of entities that will be involved.

• To Step-2, Determining attribute-key attribute of each set of entities.

• To step-3, Identify and define whole set of relationships between the collective entity collective entities that have foreign-key with it.

• To step-4, Determining degrees / kardinalitas relations for each set of relationships.

• To step-5, Complete set of entities and set of relations with the attribute-descriptive attributes (non key).

1.13 E-R Diagram with the data dictionary

E-R Diagram with the data dictionary is The bolt main objective of E-R diagram is to show the objects (the collective entity) is just want to be involved in a data base and What's going on in the relationship between objects it.

• In the space of a system-wide and lingkupnya complex, drawing attributes-attributes in a E-R diagram often even with the objective who want to achieve it.
• separation attribute-attribute of ER diagram can be expressed in a Data Dictionary
• The data dictionary contains a list of attributes that diapit brackets brace ((and)).
• Attribute that functions as a key to be the non-key attribute is the underline.

Data Dictionary:

• Mahasiswa = { nim, nama_mhs, alamat_mhs, tgl_lahir }
• Kuliah = { kode_kul, nama_kul, sks, semester }
• Dosen = { nama_dos, alamat_dos }
• Mempelajari = { nim, kode_kul, indeks_nil }
• Mengajar = { kode_kul, nama_dos, waktu, tempat }

DATA FLOW DIAGRAM

Data Flow Diagram (DFD) is a diagram using the notation-notation to describe the flow of data from the system, which is very helpful to understand the logic of the system, and structure clear.
DFD is a tool in help describe or explain the system that is running logical.

DFD are often used to describe a system that already exists or the new system will be developed logically without considering the physical environment where the data flow or save.DFD is a methodology used in the development of a structured system.

DFD symbols used:
1.external entity

external entity is an entity in the environment outside the system that can be a person, organization or other system that is in the outside environment that will provide input or receive output from the system.
most of this external entity is:

-an organization, department within the company but outside the system under development
-person or a group of people in the organization but outside of that system is being developed
-an organization located outside the organization
information system-the other outside the system under development
-original source of a transaction
end-recipient of a report produced by the system

2.data flow

Data flow through the system, starting with some input and modified or processed into output. Flow data (Data Flow) with a given symbol the arrow.

in drawing data flow in DFD needs to draft a few to note are:

• The concept of packet-data
  when two or more data flows from a source that should be considered the same as a single flow
• The concept of data-flow spread
  flow of data spread shows a number of copies of the same data flow from the same source to a different destination
• The concept of data-gather
  gather flow data shows that some of the different data flows from different sources join together towards the same goal
• The concept of source-destination flow data and
  all the flow data must be generated from a process to a process

3.process

Is the activity or work done by the person, machine or computer from a flow of incoming data in the process to produce data that will flow out of the process or to change the input into output. A process can be indicated with the symbol of the circle.

Each process must be given the explanation that is:
1.Identifikasi process
included a number that indicates the number of reference and the process is written at the top of the symbol in the process

2.Nama process
shows what is done by the process

3.process

4.data store

Data stored for the next. Savings in the DFD data symbol with a pair of parallel horizontal lines that closed in one of the edges.

form of data flow diagram
There are two forms of the DAD data flow diagrams and physical data flow diagram data flow logikadiagram used to describe the physical system that ada.Sedangkan logical data flow diagram is used to delineate the proposed system.

guidelines drawing DAD
1.identificate first all outside the unit involved in the system
2.identificate all input and output involved in union with the outside
3.draw first a context diagram.
4.draw chart tiered process for all existing DAD

5.draw sketches for the overview diagram on the chart based on the tiered
6.draw DAD-level to the next level, for each process is broken-parsed according to the chart state
7.after all levels drawn DAD, DAD is drawing to a management reporting drawn separately

data dictionary is a catalog of facts and data information needs of an information system.
content data dictionary:
1.nama flow data
data dictionary is based on the data that flows in the name of the DAD data flow should also be noted in the data dictionary

2.Alias
alias is needed because the data have the same name that is different to the one with the other

3. form of data
dictionary is used to segment data into the system uses during the design

4.data flow
shows the data from which to goal

5.penjelasan
explanation can be charged with the particulars about the flow of data

6.periode
indicates when the occurrence of flow data

7.volume
noted that volume was about average and peak volume of flow data

8.data structure
shows the flow of data recorded data dictionary consists of items of data what