Information Systems Analysis and Design [Part-6]

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 figuring relationship attributes in one relationship. An attribute is said functionally dependant on the other if we utilize the attributes value to determine the other attribute value. Symbol that is utilized is for representing functional dependency.
Read functionally determines.

Notation: A B

A and B is attribute of one table. It means that functionally A determines B or B depend on A, if and only if available 2 rows data with same value of A, therefore value of B also same.

Notation : AB or A x --> B are opposite of previous notation.

From the table above, we can explain that:

Functional Dependency:

• NRP name

• Subject, NRP Grade

Non Functional Dependency:

• Subject NRP

• NRP Grade

Functional Dependency from the table value

NRP Name

Because of each value of NRP that same, then the value of Name also same.

{Subject, NRP} Grade

Because of attribute Grade is depend on Subject and NRP jointly. In other mean for Subject and same NRP, therefore Grade also same, because Subject and NRP are key (get unique character)

– Subject NRP

– NRP Grade

First Normal Form - 1NF

A table is said exist on normal form I if it don't exist on unnormalized form table, where is happening multiplexing a sort field and enabling available field that null (empty).

For example College Student Data as follows:

ID	NAME	HOBBY
12020001	Feri Wijantara	Football, reading comics, swimming
12020013	Leoni WS	Cooking, singing
12020015	Rion Saputra	tailor, making bread

Or

ID	NAME	HOBBY1	HOBBY2	HOBBY3
12020001	Feri Wijantara	Football	reading comics	swimming
12020013	Leoni WS	Cooking	singing
12020015	Rion Saputra	Tailor	making bread

The tables above are ineligible for 1NF. Decomposition becomes:

- Collegian table:

ID	NAME
12020001	Feri Wijantara
12020013	Leoni WS
12020015	Rion Saputra

- Hobby table:

ID	HOBBY
12020001	Football
12020001	reading comics
12020001	swimming
12020013	Cooking,
12020013	singing
12020015	tailor
12020015	making bread

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.

This following table accomplishes 1NF, but excluding 2NF:

ID	Name	Address	Subject_code	Subject	Sks	Grade
0608605087	Leoni WS	Jl. Celagi Basur	DM	Data Mining	3	A
0608605013	Feri Wijantara	Jl. Sriwijaya	GIS	Sistem Informasi Geografis	3	B
0608605087	Leoni WS	Jl. Celagi Basur	GIS	Sistem Informasi Geografis	3	A
0608605200	Rion Saputra	Jl. Penguin	GIS	Sistem Informasi Geografis	3	C
0608605087	Leoni WS	Jl. Celagi Basur	MP	Metodologi Penelitian	2	A
0608605200	Rion Saputra	Jl. Penguin	MP	Metodologi Penelitian	2	B

That table is Not accomplishing in 2NF's form, because {ID, Subject_Code} that is looked on as primary key meanwhile:

{ID, Subject_code} Name

{ID, Subject_code}Address

{ID, Subject_code} Subject

{ID, Subject_code} Sks

{ID, Subject_code} Grade

That table needs to decomposition becomes many table that measures up 2NF.

The Functional dependency as follows:

{ID, Subject_code} Grade (fd1)

ID{Name, Address} (fd2)

Subject_code {Subject, Sks} (fd3)

So:

fd1 (ID, Subject code, Grade) Appreciative table

fd2 (ID, Name, Address) Collegian table

fd3 (Subject code, Subject, Sks) Subject's table

Third Normal Form - 3NF

3NF's normal form is accomplished if have accomplished 2NF's form, and if no attribute non primary key that have dependency to attribute non primary key another (transitive dependency).

This Following collegian table measures up 2NF, but doesn't accomplish 3NF:

ID	Name	Address	City	Province	PostalCode
0608605087	Leoni WS	Jl. Celagi Basur	Badung	Bali	80978
0608605013	Feri Wijantara	Jl. Sriwijaya	Gianyar	Bali	80999
0608605139	Fernindya Putri	Jl. Kura-kura	Denpasar	Bali	81233
0608605200	Rion Saputra	Jl. Penguin	Bangli	Bali	80612

Since is still exist the attribute non primary key (namely City and Province) that have dependency to attribute non primary key another one (namely PostalCode):

PostalCode {City, Province}

So that table need to be decomposition becomes:

Collegian (ID, Name, Street, PostalCode)

PostalCode( PostalCode, 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	Leoni
1001	S100	Feri
1002	S101	Rion
1001	S101	Rion
1003	S101	Nelly

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
Leoni	S100
Feri	S100
Rion	S101
Nelly	S101

Seminar-Counselor Relationship

NPM	Counselor
1000	Leoni
1001	Feri
1002	Rion
1001	Rion
1003	Nelly

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

Information Systems Analysis and Design [Part-5]

Database and entity relationship diagram

The definition of database

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). For example is in manufacture company, the database include production planning data, actual production data, data ordering material. And in hospital database include of data of patient, doctor, nurse, etc.

Database management system (DBMS)

Database management System is database collection with software application in based of database. This application programs is use for accessing and maintaining the database. The first aim of DBMS is to preparing an easy and efficient 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: collection of bits that of a kind

>> filed: collection of byte that of a kind, which in database is called "attribute".


Attribute or field

Attribute or field is a characteristic of entity, which preparing detailed explanation about that's entity. A relation also could have attributed.

Example of attribute:

- Attribute of motor cycle is number plate, color, kind, etc.

- Attribute of book is title, kind, total page, author, etc.


Type of attribute

1. Single vs. multivalued

Single: only can fill by one value

multivalued: can fill with more one value with the same of kind

2. Atomic vs. composition

Atomic: can not divided into smaller attribute

Composition: boundless from some smaller attribute

3. Derived attribute

Derived attribute is an attribute which its value can be produced from other attribute

4. Null value attribute

Null value attribute is an attribute with no value for a record

5. Mandatory value attribute is an attribute which must have value


Record or Tuple

Record is a line of data in a relationship. It does consist of collection attribute, where the attributes is related one to other to give detailed information of the entity or relation.


Entity or file

File is a collection of record that of a kind and having same element, same attribute however different in their data value.

Type of file:

In processing application, file can be categorized such as:

- Parented file

- Transaction file

- Report file

- History file

- Protector file

- Work file


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.

Kind of key:

* Super key is one or more attribute from a table which can be used for identifying entity / record from table uniquely

* Candidate 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 attribute of candidate key can be selected / determined into primary key with 3 criteria:

- The key is more natural for use as reference

- The key is simpler

- The key is unique

* Alternate key

Alternate key is attribute of candidate key which is not chosen become primary key

* Foreign key

Foreign key is any attribute subjecting to primary key at other tables. Foreign key will be happened in a relation which have one to many cardinality or many to many cardinality. Foreign key usually put at the table which direct to many cardinality.

* External key

External key is lexical attribute or collection of lexical attribute which values always identify one object instance


ERD (entity relationship diagram)

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

The different between DFD and ERD:

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

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


Elements in ERD

Elements in ERD include:

* 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, the relationship can be described with a rhomb. Relationship is a natural relation that occurs between entities. In a general way given by name by vocabulary so that making it easier to do the reading of relation.

* 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 of cardinality relationship, which 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.

Example:



*One to many or many to one

Level one to many relationship is the same as the many to one depending on the direction from which the relationship have to seen. For an event on the first entity, it can have many relationships with the event on the second entity, if conversely the event in second entities only gets a relationship with one event on the first entity.

Example:



* Many to many

happening if each event on one entities has a many relationship with event on another entities.

Example:


Notation in ER-Diagram

The notation symbol of ER-Diagram is:


• Length square declare for entities gathering

• Circle declares for attribute

• Rhomb declares for relationship gathering

• Lining as connecting among relationship gathering with entities gathering and Entities Gathering with its attribute

References:
1. Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Database and ER-Diagram

BRISINGR

Ini novel yang baru aja selesai ku baca setelah bergumul dengan tugas-tugas kuliah yang numplek akhirnya bisa menikmati baca niy novel ;P!

Brisingr adalah buku ketiga dari trilogi Warisan yang ditulis oleh Christopher Paolini, buku pertamanya adalah ERAGON kemudian ELDEST (Tapi ternyata gw salah!!! Setelah tak baca sampai akhir masih ada lanjutannya, berarti niy novel bukan trilogi toh???)

Dalam novelnya yang ketiga ini masih menceritakan tentang perjuangan eragon si penunggang naga dengan naganya yang bernama Saphira. masih juga tentang perlawanan kaum varden yang menentang kekuasaan raja kejam bernama Galbatorix.

Dalam novel ini diceritakan Eragon bersama Roran pergi mencari Katrina yang ditawan oleh para Ra'zackemudian ia menikahkan Roran dan Katrina. Eragon juga menepati janjinya untuk menghapuskan kutukan pada elva namun tidak sepenuhnya berhasil. Kemudian ia mengikuti pemilihan raja kurcaci karena raja sebelumnya yaitu Hrothgar telah tewas dalam pertempuran di daratan membara, ia bersama saphira memperbaiki Ishdar Mitrim di Farthen dur saat penobatan Orik menjadi raja kurcaci ke 44.

Setelah ritual penobatan raja kurcaci, eragon dan saphira balik ke Elesmera untuk bertemu dengan gurunya yang juga penunggang naga yaitu Oromis si cacat yang utuh. Oromis memberitahukan bahwa ayah Eragon bukanlah Morzan, melainkan Brom. Setelah Zar'oc diambil oleh saudara tiri Eragon yaitu Murtagh ia berniat membuat pedang baru yang sesuai dengan dirinya untuk itu ia mencoba untuk bertemu Rhunon si pandai besi elf. Eragon tidak mempunyai pedang yang layak untuk seorang penunggang. Maka ia teringat nasehat Solembum (kucing jadi-jadian) bahwa ketika ia membutuhkan senjata ia dapat mencarinya dibawah akar pohon Menoa. Setelah sempat bersitegang dengan penunggu pohon akhirnya eragon mendapatan brightsteel yang digunakan sebagai bahan membuat pedang tersebut, Rhunon-elda membantunya membuat pedang penunggang yang kemudian diberi nama BRISINGR. Pedang ini menyala setiap Eragon menyebut namanya. Hari terakhir Glaedr memberitahukan kelemahan Golbatorix dan rahasia tentang eldunari yaitu jantungnya jantung naga. Eldunari milik Glaedr diserahkan kepada Saphirauntuk membantu mereka melawan galbatorix. Oromis pun memutuskan untuk memperlihatkan diri di depan Golbatorix dan ikut berperang melawannya secara langsung.

Oromis dan Glaedr melawan Murtagh bersama naganya Thorn di Gil'ead, tiba-tiba Galbatorix menampakan diri melalui Murtagh dan membunuh Oromis. Eragon yang saat itu bertarung bersama Arya melawan Shade melihat pembunuhan itu dalam benaknya. Akhirnya Eragon dan Saphira menjadi penunggang terakhir dan sendirian yang harus melawan Golbatorik.

Novel ini masih berlanjut ke bukunya yang ke-empat...
Duwh.. jadi penasaran...

...buku yang membuat saya rela bergadang.
----The Washington Post

Penuh dengan adegan pertempuran seru yang mengombinasikan sihir dan ilmu perang tradisional...
----Children's Literature

Judul: Brisingr
Pengarang : Christopher Paolini
Penerbit edisi Bahasa Indonesia: Gramedia Pustaka Utama