Overview Of Relational DBMS Presented by Satrio Agung Wicaksono.

Overview Of Relational DBMSPresented by

Satrio Agung Wicaksono

Relational Database Concepts A database is a repository of data, designed to support

efficient data storage, retrieval and maintenance Relational databaseis a database modeled by relations RelationR defined over n sets D1, D2, …Dnwhere Di

represents some domain. n-tuple(tuple) is a set < d1, d2, …, dn> where d1εD1,

d2εD2, …

Sample Database Scheme

The relation schemas for this database can be defined as follows: EMP(ENO, ENAME, TITLE, SAL, PNO, RESP, DUR) PROJ(PNO,PNAME, BUDGET)

Key Super Key :

uses keys to define identifiers for a relation’s tuples

used to enforce rules and/or constraints on database data.

Candidate Key is a unique identifier for the tuples of a relation

most relations have multiple candidate keys

Primary Key candidate key that is chosen to represent the relation in the database and to provide a way

to uniquely identify each tuple of the relation

Alternate Key the remaining candidate keys

The problem of redundancy

Data redundancy implies finding the same data in more than one location within deatabase tables

The following problems Repetition anomaly Insertion Anomalies Deletion Anomalies Update Anomalies

Cont’d….

Repetition anomaly

Repetition anomaly : Certain information may be repeated unnecessarily This is obviously a waste of storage and is contrary to the spirit of

databases

NIM NAMA PRODI K_MK THN_MK THN_AKADEMIK SEMESTER NAMA_MK SKS NILAI

10509xxx Wira Ilkom SIF15012 2012 2012 Ganjil BDT 3 A

10509xxx Wira Ilkom SIF15011 2012 2012 Ganjil ABD 3 A

Insertion Anomalies

insertion anomaly : happens when the insertion of a data record is not possible unless we also add some additional unrelated data to the record




Deletion Anomalies

deletion anomaly happens when deletion of a data record results in losing some unrelated information that was stored as part of the record that was deleted from a table

NIM NAMA PRODI K_MK THN_MK THN_AKADEMIK SEMESTER NAMA_MK

SKS NILAI


10506xxx Wiri TIF SIF15012 2012 2012 Ganjil BDT 3 A

Update Anomalies

An update anomaly occurs when updating data for an entity in one place may lead to inconsistency, with the existing redundant data in another place in the table




Decompositions

Decomposition in relational database design implies breaking down a relational schema into smaller and simpler relations that avoid redundancy.

The idea is to be able to query the smaller relations for any information that we were previously able to retrieve from the original relational schema



10506xxx Wiri TIF SIF15012 2012 2012 Ganjil BDT 3 A

NIM NAMA PRODI

10509xxx Wira Ilkom

10506xxx Wiri TIF

NIM K_MK THN_MK THN_AKADEMIK SEMESTER NAMA_MK SKS NILAI

10509xxx SIF15012 2012 2012 Ganjil BDT 3 A

10506xxx SIF15012 2012 2012 Ganjil BDT 3 A

Functional Dependencies Functional Dependency (FD) i:

a type of integrity constraint that extends the idea of a super key.

It defines a dependency between subsets of attributes of a given relation

Functional Dependency can be understood as “A determines B”, “B is dependent on A” or “A implies B” and denoted as “A → B”.

Functional Dependencies Example

Set Of Functional Dependecies …?

NIM NAMA PRODI K_MK THN_KURIKULUM THN_AKADEMIK SEMESTER NAMA_MK NILAI

10509xxx Wira Ilkom SIF15012 2012 2012 Ganjil BDT A

10509xxx Wira Ilkom SIF15011 2012 2012 Ganjil ABD A

Normal Forms Normalization is a procedure in relational database design that

aims at converting relational schemas into a more desirable form The goal is to remove redundancy in relations and the problems

that follow from it, namely insertion, deletion and update anomalies.

Type of Normal Form: First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF

First Normal Form (1NF) A relation is considered to be in first normal form if all of its attributes have

domains that are indivisible or atomic

A table is in 1NF if and only if it satisfies the following five conditions : There is no top-to-bottom ordering to the rows.

There is no left-to-right ordering to the columns.

There are no duplicate rows

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Cont’d….

First Normal Form (1NF)NIM NAM

ANO_HP FAK PROD

IK_MK THN_KURIK

ULUMTHN_AKADEMIK

SEMESTER

NAMA_MK

SKS NILAI

10509xxx

Wira 0821xxx08775xx

PTIIK Ilkom SIF15012

2012 2012 Ganjil BDT 3 A

10506xxx

Wiri 08555xx0888xxx

PTIIK TIF SIF15012


NIM NAMA

NO_HP FAK PRODI

K_MK THN_KURIKULUM

THN_AKADEMIK

SEMESTER

NAMA_MK

SKS NILAI

10509xxx

Wira 0821xxx PTIIK

Ilkom SIF15012


10509xxx

Wira 08775xx PTIIK

Ilkom SIF15012


10506xxx

Wiri 08555xx PTIIK

TIF SIF15012


10506xxx

Wiri 0888xxx PTIIK

TIF SIF15012


1NF transformation

First Normal Form (1NF)NIM NAM

ANO_HP FAK PRO

DIK_MK THN_

MKTHN_AKADEMIK

SEMESTER

NAMA_MK

SKS NILAI

10509xxx

Wira 0821xxx08775xx

PTIIK Ilkom SIF15012


10506xxx

Wiri 08555xx0888xxx

PTIIK TIF SIF15012


NIM NAMA FAK PRODI

10509xxx Wira PTIIK Ilkom

10506xxx Wiri PTIIK TIF NIM NO_HP

10509xxx 0821xxx

10509xxx 08775xx

10506xxx 08555xx

10506xxx 0888xxx

NIM K_MK THN_MK

THN_AKADEMIK

SEMESTER

NAMA_MK

SKS NILAI

10509xxx

SIF15012


10506xxx

SIF15012


Second Normal Form (2NF)

A relation is in second formal form when it is in 1NF and there is no such non-key attribute that depends on part of the candidate key, but on the entire candidate key

NIM NAMA FAK PRODI

10509xxx Wira PTIIK Ilkom

10506xxx Wiri PTIIK TIF

NIM NO_HP

10509xxx 0821xxx

10509xxx 08775xx

10506xxx 08555xx

10506xxx 0888xxx

NIM K_MK THN_MK THN_AKADEMIK SEMESTER NILAI

10509xxx SIF15012 2012 2012 Ganjil A

10506xxx SIF15012 2012 2012 Ganjil A

K_MK THN_MK NAMA_MK SKS

SIF15012

2012 BDT 3

SIF15012

2012 BDT 3

Third Normal Form (3NF) A relation is in third normal form if it is in 2NF and there is no such non-key

attribute that depends transitively on the candidate key.

That is every attribute depends directly on the primary key and not through a transitive relation where an attribute Z may depend on a non-key attribute Y and Y in turn depends on the primary key X

Transitivity means that when X→Y and Y→ Z, then X→Z.

Cont’d…

Third Normal Form (3NF)

NIM NAMA PRODI

10509xxx Wira Ilkom

10506xxx Wiri TIFNIM NO_HP

10509xxx 0821xxx

10509xxx 08775xx

10506xxx 08555xx

10506xxx 0888xxx

NIM K_MK THN_MK THN_AKADEMIK SEMESTER NILAI

10509xxx SIF15012 2012 2012 Ganjil A

10506xxx SIF15012 2012 2012 Ganjil A

K_MK THN_MK NAMA_MK SKS

SIF15012

2012 BDT 3

SIF15012

2012 BDT 3

FAK PRODI

PTIIK Ilkom

PTIIK TIF

Boyce-Codd Normal Form (BCNF) Boyce-Codd Normal Form is a stricter version of 3NF that applies to relations

where there may be overlapping candidate keys.

A relation is said to be in Boyce-Codd normal form if it is in 3NF and every non-trivial FD given for this relation has a candidate key as its determinant.

That is, for every X → Y, X is a candidate key.

Boyce-Codd Normal Form (BCNF)

K_MK THN_MK THN_AKADEMIK SEMESTER KELAS PRODI HARI_KE

SIF15012 2012 2012 Ganjil A ILKOM 1

SIF15012 2012 2012 Ganjil A TIF 2

PTI15007 2012 2012 Ganjil A TIF 2

PTI15007 2012 2012 Ganjil A TIF 5

Relational Algebra

Relational algebra is a set of operators to manipulate relations

Defined 8 such operators, two groups of 4 each: The traditional set operations: union, intersection,

difference and Cartesian product The special relational operations: select, project, join and

divide

Union The union of two union-compatible relations R1 and R2, R1 UNION R2, is the set of all tuples

t belonging to either R1 or R2 or both

The formal notation for a union operation is U

Intersection The intersection of two union-compatible relations R1 and R2, R1

INTERSECT R2, is the set of all tuples t belonging to both R1 and R2.

The formal notation for an intersect operation is ∩.

Difference The difference between two union-compatible relations R1 and R2, R1

MINUS R2, is the set of all tuples t belonging to R1 and not to R2.

The formal notation for a difference operation is -

Cartesian product The Cartesian product between two relations R1 and R2, R1 TIMES R2, is the set of all tuples t

such that t is the concatenation of a tuple r belonging to R1 and a tuple s belonging to R2. The concatenation of a tuple r = (r1, r2, …, rm) and a tuple s = (sm+1, sm+2, …, sm+n) is the tuple t = (r1, r2, …, rm, sm+1, sm+2, …, sm+n).

R1 and R2 don’t have to be union-compatible.

The formal notation for a Cartesian product operation is ×

Selection The select operation selects a subset of tuples from a relation.

It is a unary operator, that is, it applies on a single relation.

The tuples subset must satisfy a selection condition or predicate.

The formal notation for a select operation is: σ <select condition> (<relation>)

where <select condition> is

<attribute> <comparison operator> <constant value>/<attribute> [AND/OR/NOT <attribute> <comparison operator> <constant value>/<attribute>…]

The comparison operator can be <, >, <=, >=, =, <> and it depends on attribute domain or data type constant value

Selection

Projection The project operation builds another relation by selecting a subset of

attributes of an existing relation.

Duplicate tuples from the resulting relation are eliminated. It is also a unary operator.

The formal notation for a project operation is: π <attribute list> (<relation>)

where <attribute list> is the subset attributes of an existing relation

Projection

JOIN The join operation concatenates two relations based on a joining condition

or predicate.

The relations must have at least one common attribute with the same underlying domain, and on such attributes a joining condition can be specified.

The formal notation for a join operation is: R <join condition> S ►◄

where <join condition> is

<attribute from R> <comparison operator> < <attribute from S>

The comparison operator can be <, >, <=, >=, =, <> and it depends on attributes domain.

Division The division operator divides a relation R1 of degree (n+m) by a relation R2

of degree m and produces a relation of degree n.

The (n+i)th attribute of R1 and the ith attribute from R2 should be defined on the same domain.

The result of a division operation between R1 and R2 is another relation, which contains all the tuples that concatenated with all R2 tuples are belonging to R1 relation.

The formal notation for a division operation is ÷.

Division

Overview Of Relational DBMS Presented by Satrio Agung Wicaksono.

Documents

Transcript of Overview Of Relational DBMS Presented by Satrio Agung Wicaksono.