Database Normalization

Normalization

There are several processes that must be done to design a database, namely:

1. Gathering user needs / business

2. Develop the ER model based on user needs / business

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

4. Relationship normalization to eliminate the anomaly

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 tables which good with quality.

Normalization should be performed for the optimal structure-structure of the table, Increase speed, remove the same income data, more efficient use of storage media, Reducing redundancy, Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies), improved data integrity.

A tables told by goodness 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).

· Don’t impinge the Normal Boyce-Code of Form ( BCNF)

If the three criteria (BCNF) can’t 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 attributes describe the relationship 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 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)

·

NRP -> Name

· Mata Kuliah, NRP-> Value

Non Functional Dependency:

· Mata Kuliah -> NRP

· NRP -> Value

Functional Dependency from tables of 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 (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:

• Attribute values, many (Multivalued attributes).
• Attribute a composite or a combination of both.

So:

• Price is the domain attribute must be atomic rates

Example:

• Ex Student Data as follows:

Ex Student Data as follows:

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

Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of Normal To 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 l

ed by one of the 2 seminar. Each leader can only take one seminar course. NPM in this example and show the seminar.

Relations Seminar is a form of Third Normal, but not BCNF. Seminar Code because the function depends on the teach, if any teach can only teach a seminar. Depending on the seminar isn’t a super key attributes such as required by BCNF. So seminar relations must be parsed into two namely:

Fifth and fourth Normal form

• Relations in fourth normal form (NF 4) if the relation in BCNF and does not contain a lot of dependence 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.

References:

Er Ngurah Agus Sanjaya. Slide Part 6 - Normalisasi.

DATABASE AND ER-DIAGRAM

DATABASE AND ER-DIAGRAM

DEFINITION OF THE DATABASE:

A set of data stored in the magnetic disk, optical disk or other secondary storage. Collection of integrated data-related data of an enterprise (company, government or private). for example:

1. Company data > manufacturing production planning, actual production data, data ordering materials, etc.

2. Hospital > patient data, doctor, nurse, etc.

DATABASE MANAGEMENT SYSTEM

DBMS is a system and the data base on the computer. This system is designed to be able to do a variety of data with some reference data are the same. DBMS is able to access various applications.

BIT, BYTE, FIELD

1. >>Bit is the smallest pieces of data that contains the value 0 or 1

2. >>Byte is a set of bit-bit similar

3. >>Field is a set of byte-byte similar, in the database used the term attribute

ATRIBUT/FIELD

It is the nature or characteristics of an entity that provides provide detail on these entities. A relationship can also have attributes.

Examples for attributes:

STUDENTS: NIM, NAME, ADDRESS

CAR: NOMOR_PLAT, COLOR, TYPE, CC

TIPE-TIPE ATRIBUT

Single vs multivalue

Single > can only be filled at most one value

Multivalue > can be filled with more than one value with the same type of

Atomic vs composition

Atomic > can’t be divided into the attributes of smaller

composition > is a combination of several attributes of a smaller

Derived Attribute

attribute value can be derived from other attribute values, for example: age of the attributes generated from the date of birth.

Null Value Attribute

Attributes that have no value to a record

Mandatory Value Attribute

Attributes must have values

RECORD/TUPLE

It is a line of data in a relationship, and consists of a set of attributes where the attribute is to inform each other entity / relationship fully.

ENTITY/FILE

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

File Type

File is a collection of notes and similar elements have the same, the same attributes but different data values. In the process of application, the file can be in the category with some of the following types

1. Master File

2. Transaction File

3. File Report

4. File History

5. File Protection

6. File Work

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.

KEY DATA ELEMENT

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

Cadidate 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 with the three following criteria:

1. Key is more natural to use as reference

2. Key is more simple

3. Key is guaranteed unique

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

ERD (ENTITY RELATIONSHIP DIAGRAM)

ERD is a model of a network that uses word order is stored in the abstract system.

Differences between the DFD and ERD:

- DFD is a model of network functions that will be implemented by the system

- ERD is a model that emphasizes the network data on the structure and relationship data

ELEMENTS OF THE 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

ER diagram on the 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 readings relationships.

Relationship Degree

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

Attribute

is the nature or characteristics of each entity and relationship

Kardinalitas

tupel indicates the maximum number that can be berelasi with entities on the other entity

RELATIONSHIP DEGREE

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.

KARDINALITAS

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 is viewed. For one incident in the first entity can have many relationships with the incident on the second entity, if the one incident in which two entities can have only one incident hubugan with the first entity.

Many To Many:

if any incident occurs in an entity has many relationships with other entities in the incident.

EXEMPLARY KARDINALITAS

NOTATION (E-R DIAGRAM)

Symbolic notation in the ER diagram is:

1. Rectangle represent the collective entity

2. Circle represent the attributes

3. Rhomb represent collective relationships

4. Line as the set of relations between the Association and the collective entity with entity attributes

Reference:

ER Ngurah Agus Sanjaya. Slide Part 5 - Database Dan Er-Diagram.

DATA FLOW DIAGRAM

DATA FLOW DIAGRAM

Data flow diagram is also called the Data Flow Diagram (DFD).DFD function to describe the existing system or the new system will be developed without considering the logic of the physical environment where the data flows, or where data disimpan.DFD can also explain the flow of data in the system with a clearly structured.

Context Diagram

1. Consists of a process and describe the scope of a system

2. Is the highest level of the DFD that describes the entire system to input and output of the system

3. System is limited by Boundary (depicted by broken lines)

4. There may not be storage (storage)

Zero Diagram

Zero diagram is a chart that describes the process of DFD. This diagram provides a view of the overall system shows that the main function of the process or the flow of data and the external entity. At this level there is a data storage.

Detailed Diagram

Is a diagram that decipher what is the process in the diagram zero level or above.

Numbering level in the DFD:

In one level there should be no more than 7 units and the maximum of 9, when more should be done in the decomposition.

Specification Process

Each process in the DFD must have a top-level specification process . method that is used to describe the process can use a sentence with descriptive, and on a more detailed level, namely on the bottom (functional primitive) require a more structured specification, process specification will be the guidelines for a programmer to create the program.

External Entity

Unit outside is something that is outside the system, but provide data in the system or to provide data from an external system. Entity not including part of the system, the symbols with the notation.

Rules for naming:

1. The name of the form of noun

2. Terminal may not have the same name unless the object is the same

Data Flow

Data flow is the information flow is depicted with a straight line that connects the components of the system. Data flow direction is indicated with arrows and lines give the name on the flow of data flow. Flow data that flows between processes, data storage and data flow indicates that the form of data input to the system.

Guidelines of the name:

1. Name of the flow of data that consists of some words associated with the flow lines connect

2. No flow data for the same and the name should reflect its content

3. The flow of data that consists of several elements can be expressed with the group element

4. Avoid using the word 'data' and 'information' to give a name to the flow of data

5. Wherever possible the complete flow of data is written

Other provisions:

1. Name of the flow of data into a process may not be the same as the name of the data flow out of the process

2. Data flow into or out of data storage doesn’t need to be given a name if:

a. The flow of data simple and easy to understand

b. Describes the data flow of all data items

3. There can be no flow of data from the terminal to the data storage, or vice versa because the terminal is not part of the system, the relationship with the terminal data storage must be through a process

Process

The process is what is done by the system, can process data streams or input data into output data stream. Each process has one or more inputs and produce one or more output.

Transform the process of working one or more of the input data into one or more of the output data in accordance with the desired specifications.

Guidelines of the process:

1. Name of the process consists of a verb and noun, which reflects the function of the process

2. Do not use the process as part of the name of a bubble

3. May not have some process that has the same name

4. The process should be given a number. Order number wherever possible to follow the flow of the process or sequence, but the sequence number doesn’t mean that the absolute is a process in chronological order

Data Storage

Data storage is a storage place for data that exists in the system, which symbol with a pair of parallel lines or two lines with one of the side open. The process can retrieve data from or provide data to the database.

Guidelines of the name:

1. The name should reflect the data.

2. When his name more than one word must be marked with the numbers.

Data Dictionary

Data Dictionary functions to help the system to interpret the application in detail and manage all elements of the data used in the system right so that the system analyst and have a basic understanding of the same input, output, storage and processing.

At analysis, the data dictionary is used as a means of communication between the systems analyst with the user. At the system design, data dictionary is used to design input, reports and databases.

Data dictionary contains the following:

1. Name of data flow: must note that readers who need further explanation about a flow of data can find it easily

2. Alias: initials or other name of the data can be written when there is

3. Forms of data: used to segment the data dictionary to use when designing the system

4. Flow data: indicates from which data flows and where the data

5. Description: to give an explanation of the meaning of the data flow

Balancing In DFD

The flow of data 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 or levels below it. 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.

The issues that must be considered in the DFD which have more than one level:

1. There must be a balance between input and output of one level and next level

2. Balance between level 0 and level 1 at input output of the flow of data to or from the terminal on level 0, while the balance between level 1 and level 2 is seen on the input / output of stream data to and from the process concerned

3. Name of the flow of data, data storage and terminals at each level must be the same if the same object

Restrictions In DFD

1. Flow data may not be from outside the entity directly to other outside entities without going through a process

2. Flow data may not be from the savings directly to the data to outside entities without going through a process

3. Flow data may not be saving the data directly from the savings and other data without going through a process

4. Flow data from one process directly to the other without going through the process of saving data should be avoided as much as possible

Reference:

ER, Ngurah Agus Sanjaya. Slide presentation Part 4 - Data Flow Diagram.