Alper VAHAPLAR
2014 – 2015 ©

is a base that data are kept.

A database is a collection of information that
is organized so that it can easily be accessed,
managed, and updated.

A collection of regular and related data
cleared from mistakes and redundancies, and
stored to serve some applications.
Alper VAHAPLAR
Database Management - 3
2
1
Data Independency,
Application Development,
Data Integrity and Security,
Uniform Data Administration,
Data Sharing, Concurrent Access,
Backup and Recovery.
Alper VAHAPLAR
Database Management - 3
3
Alper VAHAPLAR
Database Management - 3
4
2
is a collection of software which
 describes the data structure,
 collects the related data,
 operates on the data,
 provides consistency,
 prevents misusage,
 defines the users and their authorizations,
 allows concurrent access and usage of data.
Alper VAHAPLAR
Database Management - 3
5
Application #2
Application #1
Application #3
Database
Application #n
Alper VAHAPLAR
Database Management - 3
6
3









MS Access
Oracle
MS SQL
Informix
Dbase
DB-II
MySql
Sybase
etc.
Alper VAHAPLAR







Database Management - 3
designing,
implementing,
maintaining the database system;
establishing policies and procedures,
security,
maintenance, and use of the DBMS;
training employees in database
management and use.
Alper VAHAPLAR
Database Management - 3
7






Recoverability
Integrity
Security
Availability
Performance
Development and
testing support
8
4




New and Expert Personnel
Operations on Shared Data
Organizational Conflicts
Backup and Recovery
Alper VAHAPLAR

Database Management - 3
9
1960’s
 Data Collections, Hierarchical DBMS, network DBMS

1970’s
 Relational Data Models

1980’s
 RDBMS, Object Oriented Databases,

1990 – 2000’s
 XML Databases, Data Warehouses, Multimedia
Databases, Data Mining
Alper VAHAPLAR
Database Management - 3
10
5

A data model,
 is used to express the data in an understandable manner.
 is a collection of concepts used to describe the structure of a




database.
simple visualization of complex real world data.
helps the interaction among database designer, programmer
and the end user.
provides different views for end users.
organizes the data for various types of users.
Alper VAHAPLAR
Database Management - 3
11
Alper VAHAPLAR
Database Management - 3
12
6





Hierarchical
Network
Relational
Entity Relationship
Object Oriented
Alper VAHAPLAR
Database Management - 3
13
Alper VAHAPLAR
Database Management - 3
14
7
Alper VAHAPLAR






Database Management - 3
15
The Relational Model,
1969 – Edgar Frank Codd (IBM),
“Very ingenious, but impractical” – 1970 ,
Conceptually simple and plain,
Microcomputers can successfully implement,
Hides the complexity of the model from the
user.
Alper VAHAPLAR
Database Management - 3
16
8

Entity
 An entity is any individual object or event in the
system that we want to model and store information
about .

Attribute
 A recordable property of an entity,

Key
 A key is a data item/property that uniquely identifies
individual occurrences or an entity type.
Alper VAHAPLAR

Database Management - 3
17
Examples :
 Entity
▪ Student
 Attribute
▪ Student number, name, surname, birthdate, address, …
 Key
▪ Student Number
Alper VAHAPLAR
Database Management - 3
18
9

Relationships:
 A relationship is an association of two or more
entities where the association includes one entity
from each participating entity type.

Types of relationships:
 One – to – one  (1:1)
 One – to – many) (1:M)
 Many – to – many  (M:N)
Alper VAHAPLAR
Database Management - 3
19
Alper VAHAPLAR
Database Management - 3
20
10

Find pairs of entities for each type of relation
 1–1
 1 – Many
 Many – Many

Define the attributes for these entities.
Alper VAHAPLAR



Database Management - 3
21
Relations: viewed as 2-dimensional tables
rows x columns
Properties:
 Each column has a unique name. Order of
columns are unimportant.
 Each column contains values about the same
attribute.
 Each row is unique. No repeating rows.
 Order of the rows is unimportant.
Alper VAHAPLAR
Database Management - 3
22
11

Tuple :
 Values constructing a row, (record)

Degree :
 Number of attributes in a tuple

Cardinality :
 Number of tuples in a relation

Domain :
 Set of possible values for an attribute
Alper VAHAPLAR
Database Management - 3
23
Alper VAHAPLAR
Database Management - 3
24
12
Alper VAHAPLAR
Database Management - 3
25
Attribute
Öğrenciler
No
AdSoyad
Sınıf
DogYili
DogYeri
123
Ali Veli
3
1982
Muğla
456
Hasan Hüseyin 3
1981
Afyon
789
Ayşe Fatma
2
1983
İzmir
102
Ahmet Mehmet 3
1981
Aydın
260
Fuat Murat
2
1983
Manisa
Lale Jale
3
1982
İzmir
Tuple 346
Alper VAHAPLAR
Database Management - 3
26
13

Normalization is the process of organizing
data in a database. This includes creating
tables and establishing relationships between
those tables according to rules designed both
to protect the data and to make the database
more flexible by eliminating redundancy and
inconsistent dependency.
Alper VAHAPLAR


Database Management - 3
27
Process of assigning attributes to entities.
Aims:
 reducing data redundancy,
 eliminating anomalies,
 producing controlled redundancy to link tables.
Alper VAHAPLAR
Database Management - 3
28
14

There are a few rules for database normalization.
Each rule is called a "normal form." If the first rule is
observed, the database is said to be in "first normal
form." If the first three rules are observed, the
database is considered to be in "third normal form."
Although other levels of normalization are possible,
third normal form is considered the highest level
necessary for most applications.
Alper VAHAPLAR
Database Management - 3
29
Alper VAHAPLAR
Database Management - 3
30
15
Alper VAHAPLAR
Database Management - 3
31
RPT_FORMAT (PROJ_NUM, PROJ_NAME, EMP_NUM,
EMP_NAME, JOB_CLASS, CHG_HOUR, HOURS)
Alper VAHAPLAR
Database Management - 3
32
16

Rules:
 Each relation (table) must represent one and only
one entity.
 No data must repeat redundantly in different
relations.
 All the attributes must be dependant on the key
attribute.
Alper VAHAPLAR

Database Management - 3
33
First Normal Form
 Eliminate repeating groups in individual tables.
 Create a separate table for each set of related data.
 Identify each set of related data with a primary key.

Second Normal Form
 Create separate tables for sets of values that apply to
multiple records.
 Relate these tables with a foreign key.

Third Normal Form
 Eliminate fields that do not depend on the key.
Alper VAHAPLAR
Database Management - 3
34
17

First Normal Form
 Eliminate repeating groups in individual tables.
 Create a separate table for each set of related
data.
 Identify each set of related data with a primary
key.
Alper VAHAPLAR

Database Management - 3
35
First Normal Form
 RPT_FORMAT (Proj_num, Proj_name,{ Emp_num,
Emp_name, Job_class, Chg_hour, Hours)}
▪ PROJ (Proj_num, Proj_name)
▪ EMPLOYEE (Emp_num, Emp_name, Job_class, Chg_hour,
Hours)
Alper VAHAPLAR
Database Management - 3
36
18

Second Normal Form
 Remove Partial Dependencies
 Create separate tables for sets of values that apply to
multiple records.
 Relate these tables with a foreign key.
 Each attribute must be FULLY DEPENDANT to the
key attribute.
Alper VAHAPLAR

Database Management - 3
37
Second Normal Form
▪ PROJ (Proj_num, Proj_name)
▪ EMPLOYEE (Emp_num, Emp_name, Job_class,
Chg_hour, Hours)
▪ EMPLOYEE (Emp_num, Emp_name, Job_class, Chg_hour)
▪ ASSIGN (Emp_num, Proj_num, Hours)
Alper VAHAPLAR
Database Management - 3
38
19

Third Normal Form
 Remove Transitive Dependencies.
 Eliminate fields that do not depend on the key.
 Search for another attribute to identify better
than the key attribute.
Alper VAHAPLAR

Database Management - 3
39
Third Normal Form
 EMPLOYEE (Emp_num, Emp_name, Job_class, Chg_hour)
 ASSIGN (Emp_num, Proj_num, Hours)
▪ EMPLOYEE (Emp_num, Emp_name, Job_class)
▪ JOB (Job_class, Chg_hour)
Alper VAHAPLAR
Database Management - 3
40
20
1st NF
Convert
2nd NF
1st
3rd NF
2nd
to Table Format,
No repeating fields,
Define Key attributes.
NF
Eliminate partial dependencies
NF
Eliminate transitive dependencies
Alper VAHAPLAR
Database Management - 3
41
Alper VAHAPLAR
Database Management - 3
42
21
Film (filmno, title, dir_no, dir_name, year,
type, pl_no, pl_name, pl_dob, order)
 1st Normalization – Remove repeating
groups

 Film (filmno, title, dir_no, dir_name, year, type)
 Player (pl_no, pl_name, pl_dob, order)
Alper VAHAPLAR
Database Management - 3
43
 Film (filmno, title, dir_no, dir_name, year, type)
 Player(pl_no, pl_name, pl_dob, order)

2nd Normalization – Remove partial
dependencies
 Player (pl_no, pl_name, pl_dob)
 Cast (filmno, pl_no, order)
Alper VAHAPLAR
Database Management - 3
44
22
 Film (filmno, title, dir_no, dir_name, year, type)
 Player (pl_no, pl_name, pl_dob)
 Cast (filmno, pl_no, order)

3rd Normalization – Remove transitive
dependencies
 Film (filmno, title dir_no, year, type)
 Director(dir_no, dir_name)
Alper VAHAPLAR

Database Management - 3
45
Film (filmno, title, dir_no, dir_name, year,
type, pl_no, pl_name, pl_dob, order)
 Film (filmno, title dir_no, year, type)
 Player (pl_no, pl_name, pl_dob)
 Cast (filmno, pl_no, order)
 Director(dir_no, dir_name)
Alper VAHAPLAR
Database Management - 3
46
23
Normalize the following data collection
Kütüphane
(kitapno, kitapadı, konusu, rafno, yazarno,
yazaradı, sayfasayısı, kullanıcıno, kullanıcıadı,
adres, telefon, alıştarihi, iadetarihi)


Alper VAHAPLAR
Database Management - 3
47


Kütüphane
(kitapno, kitapadı,{konusu, rafno, yazarno,
yazaradı, sayfasayısı,}kullanıcıno, kullanıcıadı,
adres, telefon, alıştarihi, iadetarihi)

1st Normalization – Remove repeating groups
Kitaplar (kitapno, kitapadı, konusu, rafno,
yazarno, yazaradı, sayfasayısı)
 Kullanıcılar(kullanıcıno, kullanıcıadı, adres,
telefon, alıştarihi, iadetarihi)

Alper VAHAPLAR
Database Management - 3Database Management - 4
Alper VAHAPLAR
4848
24
Kitaplar (kitapno, kitapadı, konusu, rafno,
yazarno, yazaradı, sayfasayısı)
 Kullanıcılar (kullanıcıno, kullanıcıadı, adres,
telefon, alıştarihi, iadetarihi)


2nd Normalization – Remove partial
dependencies
 Kullanıcılar (kullanıcıno, kullanıcıadı, adres,
telefon)
 Kira (kullanıcıno, kitapno, alıştarihi, iadetarihi)
Alper VAHAPLAR




Database Management - 3Database Management - 4
Alper VAHAPLAR
4949
Kitaplar (kitapno, kitapadı, konusu, rafno,
yazarno, yazaradı, sayfasayısı)
Kullanıcılar (kullanıcıno, kullanıcıadı, adres,
telefon)
Kira (kullanıcıno, kitapno, alıştarihi, iadetarihi)
3rd Normalization – Remove transitive
dependencies
 Kitaplar (kitapno, kitapadı, konusu, rafno, yazarno,
sayfasayısı)
 Yazar (yazarno, yazaradı)
Alper VAHAPLAR
Database Management - 3Database Management - 4
Alper VAHAPLAR
5050
25

(kitapno, kitapadı, konusu, rafno, yazarno,
yazaradı, sayfasayısı, kullanıcıno, kullanıcıadı,
adres, telefon, alıştarihi, iadetarihi)
 Kitaplar (kitapno, kitapadı, konusu, rafno,
yazarno, sayfasayısı)
 Kullanıcılar (kullanıcıno, kullanıcıadı, adres,
telefon)
 Yazar (yazarno, yazaradı)
 Kira (kullanıcıno, kitapno, alıştarihi, iadetarihi)
Alper VAHAPLAR


Database Management - 3Database Management - 4
Alper VAHAPLAR
5151
Araba Kiralama veri tabanı
Kira (plaka, marka, model, yıl, motorhacmi,
yakıttipi, müş_no, müş_adi, müş_adresi,
müş_tel, kira_tarihi, kaçgün, günlükücret)
Alper VAHAPLAR
Database Management - 3
52
26
Kira (plaka, marka, model, yıl, motorhacmi,
yakıttipi, müş_no, müş_adi, müş_adresi,
müş_tel, kira_tarihi, kaçgün, günlükücret)
 1st Normalization

 Araba (plaka, marka, model, yıl, motorhacmi,
yakıttipi)
 Müşteri (müş_no, müş_adi, müş_adresi, müş_tel,
kira_tarihi, kaçgün, günlükücret)
Alper VAHAPLAR
Database Management - 3
53
Araba (plaka, marka, model, yıl, motorhacmi,
yakıttipi)
 Müşteri (müş_no, müş_adi, müş_adresi,
müş_tel, kira_tarihi, kaçgün, günlükücret)
 2nd Normalization

 Müşteri (müş_no, müş_adi, müş_adresi, müş_tel)
 Kiralama (müş_no, plaka, kira_tarihi, kaçgün,
günlükücret)
Alper VAHAPLAR
Database Management - 3
54
27
Araba (plaka, marka, model, yıl, motorhacmi,
yakıttipi)
 Müşteri (müş_no, müş_adi, müş_adresi,
müş_tel)
 Kiralama (müş_no, plaka, kira_tarihi, kaçgün,
günlükücret)
 3rd Normalization

 Not detected…
Alper VAHAPLAR

Database Management - 3
55
Kira (plaka, marka, model, yıl, motorhacmi,
yakıttipi, müş_no, müş_adi, müş_adresi,
müş_tel, kira_tarihi, kaçgün, günlükücret)
 Araba (plaka, marka, model, yıl, motorhacmi,
yakıttipi)
 Müşteri (müş_no, müş_adi, müş_adresi, müş_tel)
 Kiralama (müş_no, plaka, kira_tarihi, kaçgün,
günlükücret)
Alper VAHAPLAR
Database Management - 3
56
28

Bir firmada çalışan işçilerin kimlik bilgileri (isim,
adres, tel, vs…), çalıştığı departmanın kodu,
departmanın adı, çalışanın görevi, çalışanların hangi
gün saat kaçta giriş ve çıkış yaptıkları bilgisi
tutulmak istenmektedir. Bu verilere göre tabloları
oluşturup normalizasyon işlemini gerçekleştiriniz
Alper VAHAPLAR


Database Management - 3Database Management - 4
Alper VAHAPLAR
5757
Firma (çalışan_no, isim, adres, tel, vs, dep_kod,
dep_adi, görevi, tarih, giriş, çıkış)
1st Normalization
 Kimlik (çalışan_no, isim, adres, dep_kod, dep_adi, görevi,
tel, vs)
 Mesai (tarih, giriş, çıkış)
Alper VAHAPLAR
Database Management - 3Database Management - 4
Alper VAHAPLAR
5858
29



Kimlik (çalışan_no, isim, adres, dep_kod, dep_adi,
görevi, tel, vs)
Mesai (tarih, giriş, çıkış)
2nd Normalization
 Mesai (çalışan_no, tarih, giriş, çıkış)
Alper VAHAPLAR



Database Management - 3Database Management - 4
Alper VAHAPLAR
5959
Kimlik (çalışan_no, isim, adres, dep_kod, dep_adi,
görevi, tel, vs)
Mesai (çalışan_no, tarih, giriş, çıkış)
3rd Normalization
 Kimlik (çalışan_no, isim, adres, dep_kod, görevi, tel, vs)
 Departman (dep_kod, dep_adi)
Alper VAHAPLAR
Database Management - 3Database Management - 4
Alper VAHAPLAR
6060
30

Firma (çalışan_no, isim, adres, tel, vs, dep_kod,
dep_adi, görevi, tarih, giriş, çıkış)
 Kimlik (çalışan_no, isim, adres, dep_kod, görevi, tel,
vs)
 Departman (dep_kod, dep_adi)
 Mesai (çalışan_no, tarih, giriş, çıkış)
Alper VAHAPLAR
Database Management - 3Database Management - 4
Alper VAHAPLAR
6161
31
Download

Presentation