Normalization

Normalization

Database Design process

Gathering user / business's requirement. Develop e r Model bases user / business's requirements. Conversion e r Model goes to relationship gatherings (table). Normalization is relationship to remove anomaly. Implementation goes to database by make table for each relationship already most normalization.

Data Base Normalization

· Normalization is data base structure formation process so that a large part of ambiguity can be caused.

· Normalization step is begun from lightest step (1nf) up to tightest (5nf)

· Usually only until 3nf level or bcnf because that is enough proper to produce tables that have a good quality.

· Why normalization done?

o Table structures optimalization

o Increase speed

o Cause the loss of the same data entering

o Efficienter in storage media use

o Decrease redundancy

o Avoid anomaly (insertion anomalies, deletion anomalies, update anomalies).

o Data integrity that increased

· A table is said good (efficient) or normal if fulfil 3 criterias as follows:

o If there table decomposition, so decomposition must be guaranteed safe (lossless-join decomposition). That's mean, after table elaborated / decomposited be the new tables,the new tables can produce the first table with same exactly.

o Functional dependence when data change (dependency preservation) was kept.

o Doesn't break Boyce-Code Normal Form (BCNF)

· If third criteria (BCNF) can not be fulfilled, so at least that table doesn't break normal form 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 led 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:

Normal Form Fourth And Fifth

Relation in the form of normal fourth (4NF) if relation in BCNF and not full multivalue dependence . To cause the loss of multivalue dependence from one relation, we divide relation be two new relations. Each full relation has two attributes that has multivalue connection.

Relation in the form of normal fifth (5NF) deal with property that called join without existence loses information (lossless join). Normal form fifth (5NF also called PJNF (Projection Join Normal Form). This case is very rare appears and difficult to detected according to practise.

References:

Er Ngurah Agus Sanjaya. Slide Part 6 - Normalisasi.

DATABASE & ER-DIAGRAM

DATABASE & ER-DIAGRAM

Definition

§ The database is 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 instance or private).
Company a manufacturing : production planning data, actual production data, data ordering materials, etc.
Hospital : patient data, doctor, nurse, etc.

DBMS

§ System that is specifically created to make it easier to manage users in the database. The main purpose DBMS is to provide an environment that is efficient and easy to use, withdrawal and storage of data and information.

§ The main purpose DBMS is to provide an environment that is efficient and easy to use, withdrawal and storage of data and information

Bit, Byte, Field

§ Bit : Data bit is the part that contains the smallest value of 0 or 1.

§ Byte : Byte is a set of bit-bit similar

§ Field : Field is a set of byte-byte similar, in the database used the term attribute

ATTRIBUTE

• 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

ATTRIBUTE TYPE

§ 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 not be divided into the attributes of smaller
Composition: is a combination of several attributes of a smaller

§ Derived attribute
Attributes are derived attribute whose value can be derived from the value of other attributes, age attributes resulting from the date of birth

§ Null Value attribute
Attributes that have no value to a record

§ Mandatory attribute Value
Attribute should have a value

RECORD/TUPLE

§ Record is a row of data in a relationship. Consists of the set of attributes where the attribute-attribute-attribute is to inform each other entity / relationship fully

ENTITY

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

§ In processing applications, files can categorized as follows:
- Master File
- Transaction Files
- File Reports
- File History
- File Protection
- 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

DATA ELEMENT KEY

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

KEY TYPES

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

ERD

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

ERD ELEMENT

§ Entity
In the ER Diagram Entity is describe 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 read the relation.

§ Relationship Degree
is the number of entities participating in a relationship. Degree which is often use in the ERD.

§ Attribute
Attribute is the nature or characteristics of each entity and relationship.

§ Kardinalitas
Tupel indicates the maximum number that can be relation 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 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.

EXAMPLES KARDINALITAS

NOTATION (ERD)

§ Symbolic notation in the ER diagram is :
1. Rectangle represent the collective entity
2. Circle represent the attributes
3. Rhomb express collective relationships
4. Line as the set of relationships between the collective relation whit the collective entity and collective entity with attribute.

(ENTITY COLLECTIVE, ATTRIBUTE A AS KEY, RELATION COLLECTIVE, AND LINK)

DATA FLOW DIAGRAM (DFD)

DATA FLOW DIAGRAM (DFD)

Depicting the division of system into smaller module
Facilitating less comprehending user of computer area to understand system to be done.

DIAGRAM CONTEXT
Consist of one process and depict scope from a system
Representing highest level of DFD depicting all input to the system and output of system
System limited by boundary (depicted by dash line)
There may not be any is depository ( storage)

ZERO DIAGRAM
Depicting process of DFD
Opening on by totally regarding system handled, showing existing especial process or function, data stream and external entity
At this level there enabled by the existence of data storage For process which not detailed again at level hereinafter hence enhanced by symbol ‘*’ or ‘ P’ by the end of process number
Input balance and output ( balancing) among diagram 0 with context diagram have to be looked after

DETAILED DIAGRAM
Representing diagram elaborating process there is in zero diagram or level above him
Level Numbering at DFD :




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

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

EXTERNAL UNITY
Something that beyond system, but it's given data into system or give data of system
Symbol with notation box
External entity do not including the part of system.
Naming :
● Name of terminal in the form of noun
● Terminal may not have the name of [is] same except its [is] same object (it) is true

CURRENT DATA
Representing place a stream of information
Depicted with connective straight line component of system
Data current shown with direction bow and line called a stream of data current
Data current stream among of process, data storage and show data current of data which in the form of input for the system

Guidance gift of name :
● 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 gift 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.

Other rule:
Name of data stream which entered into a process may not equal with the name of secretory of process data stream
Data Flow which entered or go out from data storage needn't be called if :
● simple data stream and easy to comprehended
● Data stream depict all data item
There may be not any data stream from terminal to data storage or on the contrary because terminal non part of system, relation of terminal with data 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
Functioning of process to transformation one or some input data become one or some output data as according to specification which wanted
Every process have one or some input and also yield one or some output
Process often is also referred by bubble
Guidance of gift of process name:
● Name of process consist of noun and vb. expressing process function
● Don't use word process as part of the name of bubble
● There may not be any some process owning the name is same
● Process must have number. Number sequence as possible follow process sequence or stream, but that way meaningless number sequence absolutely represent sequence process chronologically

DEPOSIT DATA
Data 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
Guidance to gift of name:
● Name have to express data of storage
● If its name more than one word hence have to say the word to joint.

DICTIONARY DATA
Functioning assist perpetrator of system to interpret application in detail and organizational all used by data element system precisely so that user and system analyst have same congeniality base about input, output, depository and process
At analysis phase, data dictionary used by communication means among of 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, more detailed can be seen at data dictionary
Data dictionary load the followings :
● Name of data current : have to be noted so that reader which needing furthermore clarification about a data current can look for it easily
● Alias : alias or the other name can be written
● 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 destination of data
● Clarification: giving clarification about meaning of data current

BALANCING IN DFD
Stream Data which entered and go out from one process have to same with data stream which entered and go out from detail process of level / level below
Name of data stream which entered and go out from one process have to same with name of data stream which enter into and go out from detail of process
Amount and name of external entity from a process have to same with name and amount of external entity 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 among one and next level
Balance among level 0 and level 1 seen at input / output of data stream or from terminal at level 0, while balance among level 1 and level 2 seen at input / output of data stream from pertinent process
Name of data stream, data storage and terminal every level have to be same if its same object

PROHIBITION ORDER IN DFD
Data current may not from direct external entity go to other external entity without passing a process
Data current may not from direct data deposit go to external entity without passing a process
Data current may not from direct data deposit go to the other data deposit without passing a process
Data current from one direct process go to the other process without passing a data deposit can be possible avoided