Transcript Introduction to Database System

Introduction to Database System
Chapter 4:
Relational Database
Management System
(RDBMS)
Adisak Intana
Lecturer
Contents
Definition and usages of DBMSs
RDBMS and relational database
Kinds of key
Cardinality of associations
Introduction to Database System
2
Contents
Definition and usages of DBMSs
RDBMS and relational database
Kinds of key
Cardinality of associations
Introduction to Database System
3
Database management
system (DBMS)

An extremely complex set of programs that
process database manipulation :
– Defines and manipulates database structure
– Manipulates data in the database
– Stores the data
– Supports query language
etc.
Introduction to Database System
4
DBMS main functions

Define and manipulate database structure :
– Define database structure and constraints
– Modify database structure and constraints
– Delete database structure and constraints
– Display database structure and constraints
Introduction to Database System
5
DBMS main functions (cont.)

Manipulate data in the database :
– Insert data into database
– Delete data from database
– Amend or update data in database
– Retrieve data from database
Introduction to Database System
6
Classification of DBMSs

The database structure implementation criteria :
– Network DBMS
– Hierarchical DBMS
– Relational DBMS (RDBMS)
Introduction to Database System
7
Classification of DBMSs

The number of concurrent users criteria :
–
–

Multi-user DBMS
Single-user DBMS
The number of sites criteria :
–
–
Centralized DBMS
Distributed DBMS
Introduction to Database System
8
Standard comercial DBMSs

Hierarchical DBMS
Logical database structure is a tree data structure
Network DBMSs

Logical database structure is a graph data
structure
Relational DBMSs

Logical database structure is a collection of
two- dimensional table (list data structure)
Introduction to Database System
9
Contents
Definition and usages of DBMSs
RDBMS and relational database
Kinds of key
Cardinality of associations
Introduction to Database System
10
Relational DBMS (RDBMS)

A type of DBMS that stores data in the form of related two
dimensional tables.

The structure of the database is based on the “ Relational
data model “ which was introduced by Dr. E.F. Codd in
1970.
Introduction to Database System
11
Relational DBMS (RDBMS)


Logical database is called relational
database.
Data in the logical database is represented as
a collection of relations ( the same
terminology used in mathematics ) or tables.
Introduction to Database System
12
A Sample of relational database
Student
Stud-id
Enroll
Name
Sex Age
Stud_id
Subj_id Grade
4702777 Amporn
F
18
4702777 Math102
B+
4703666 Narin
M
19
4702777 Phy101
C
4702888 Sasin
M
19
4703666 Math102
A
4702888 Phy101
B
4702888 Math101
C
Subject
Subj-id
Sjame
Credit
Phy101
Physics
3
Math102
Math
4
Introduction to Database System
13
Relational database

Domain of an attribute X is the set of all possible values
of X.
Student
Stud-id
Name
Sex
Age
4702777 Amporn
F
18
Sex = {F, M}
4703666 Narin
M
19
Age= {x  I|0 < x < 60}
4702888 Sasin
M
19
Introduction to Database System
Domain of
14
Relational database

Each normalized relation (table) has two
parts :
–
–
The structure part ( intension ) consists of a
relation name and its attributes.
The data part ( extension ) consists of a two
dimensional table of atomic values.
Introduction to Database System
15
A Sample of relational database
Student
Stud-id
Name
Sex
Age
4702777
Amporn
F
18
4703666
Narin
M
19
4702888
Sasin
M
19
structure part ( intension )
data part ( extension )
Subject
Subj-id
Phy101
Sjame
Physics
Credit
3
Math102
Math
4
Introduction to Database System
16
Relational database

Each attribute value in each tuple of a relation must be
atomic or null value.

Atomic value is the smallest meaningful value that
cannot be divisible.
Introduction to Database System
17
A relation in a relational database
Attributes
Relation name
Emp EmpNo
Ename
Job
Sal
Comm
DeptNo
E1
USA
SALESMAN
5000
0
D1
E2
PRANEE
SALESMAN
5000
1000
D1
E3
SUTAT
MANAGER
2000
2000
D2
E4
WATTANA
ANALYST
8000
D1
E5
BOON
CLERK
4000
D2
tuple
Introduction to Database System
atomic value
null value
18
Relational database

Null value is a missing value of an
attribute that can mean either
–
–
value exists but unknown, or
value does not exist or attribute value is
not applicable for this tuple.
Introduction to Database System
19
Contents
Definition and usages of DBMSs
RDBMS and relational database
Kinds of key
Cardinality of associations
Introduction to Database System
20
Candidate key and primary key

An attribute (or set of attributes) of a relation whose
values can be used to identified each tuple in the
relation is called a candidate key.

If a relation has only one candidate key, it will be the
primary key of that relation.
Introduction to Database System
21
Primary key and secondary key

If there is more than one candidate key in a relation,
one candidate key must be selected to be the primary
key of that relation and the rest will become the
secondary key(s) or alternate key(s).
Introduction to Database System
22
Primary key and secondary key
Dept
DeptNo
Dname
D1
MARKETING
D2
PERSONNEL
D3
COMPUTER

Both DeptNo and Dname are candidate keys

If DeptNo is chosen to be the primary key, thus Dname
must be the secondary key
Introduction to Database System
23
Primary key and secondary key
Dept

Dname
DeptNo
D1
MARKETING
D2
PERSONNEL
D3
COMPUTER
Relation “Dept” structure (intension part) is represented as :
Dept(DeptNo, Dname)
Primary key
Introduction to Database System
Secondary key
24
Primary key

Each relation, there is always an attribute (or a set of
attributes) has been set to be the primary key.
–
–
–
student(std-id, name, sex, age)
subject(sj-id, sjname, credit)
enroll(std-id, sj-id, grade)
Introduction to Database System
25
Primary key


Each tuple of a relation, the attribute value of the primary
key (primary key value) can be used to identify that tuple.
Primary key value of each tuple of a relation must satisfy
the following two time-independent properties
– Not null
– Unique
Introduction to Database System
26
Compound primary key

A key which consists of more than one attribute is called
a compound key.
Enroll
Stud_id
Compound key
Subj_id Grade
4702777 Math102
B+
4702777 Phy101
C
4703666 Math102
A
4702888 Phy101
B
4702888 Math101
C
Introduction to Database System
27
Compound primary key

In each tuple, the compound value of attributes of the
compound keys must be unique and can be used to be
the representation of that tuple.
Enroll
Stud_id
Subj_id
Grade
4702777 Math102
B+
4702777 Phy101
C
4703666 Math102
A
4702888 Phy101
B
4702888 Math101
C
Introduction to Database System
28
Foreign keys

An attribute (or set of attributes) X of a relation R2 is
a foreign key if it is the primary key of some other
relation R1 in the same database.
– For all time, each current value of X in the relation R2
must be a current value of X in the relation R1.
– R2 is called referencing relation
– R1 is called referenced relation
Introduction to Database System
29
Foreign key
Emp
EmpNo
Dept
Ename
Job
Sal
Comm
DeptNo
E1
USA
SALESMAN
5000
0
D1
E2
PRANEE
SALESMAN
5000
1000
D1
E3
SUTAT
MANAGER
2000
2000
D2
E4
WATTANA
ANALYST
8000
D1
E5
BOON
CLERK
4000
D2
DeptNo
[Dname]
D1
MARKETING
D2
ACCOUNTING
D3
COMPUTER
Introduction to Database System
DeptNo is a foreign key of
relation Emp
30
Foreign key
Emp
EmpNo
E1
Ename
USA
Job
Sal
Comm
DeptNo
SALESMAN
5000
0
D1
Dept
DeptNo
D1
[Dname]
MARKETING
Each DeptNo value in relation Emp must refer to an existed DeptNo
value in relation Emp
Introduction to Database System
31
Contents
Definition and usages of DBMSs
RDBMS and relational database
Kinds of key
Cardinality of associations
Introduction to Database System
32
Relational database

Each relation in a ralational database has the
following properties :
– Every relation has a unique name.
– Attributes in a relation have unique names
– Every relation must have an attribute or a set
attributs
designated to be the primary key.
– Every attribute value is atomic.
Introduction to Database System
33
Relational data structure

Most flexible
May not be efficient for high transaction
volumes
 Easy to modify
 Most appropriate for business users

Introduction to Database System
34
Supplier-supply part database
S
P
S#
P#
Sname
Status
City
SP
S1
Mana
20
Bangkok
S# P#
Qty
S2
Sanan
10
Suphan
S1 P1
100
S3
Prakorn
10
Trang
S1 P2
200
S4
Khomsan
30
Songkla
S1 P3
100
S2 P1
200
S2 P2
100
S3 P1
100
S3 P2
300
S3 P3
200
Pname
Colour Weight
City
P1 Screw
red
15
Bangkok
P2 Bolt
green
12
Trang
P3 Screw
blue
17
Trad
Introduction to Database System
35
Supplier-part database

Intension part (database structure) :



S( s#, sname, status, city)
P(p#, pname, colour, weight, city)
SP(s#, p#, qty)
Introduction to Database System
36
Supplier-supply-part relationship


Semantics :
– Each supplier may supply many parts
– Each part may be supplied by many
suppliers
Cardinality of association is many-to-many
Introduction to Database System
37
Emp-workin-Dept database
Emp
EmpNo
Ename
Job
Sal
Comm
DeptNo
E1
USA
SALESMAN
5000
0
D1
E2
PRANEE
SALESMAN
5000
1000
D1
E3
SUTAT
MANAGER
2000
2000
D2
E4
WATTANA
ANALYST
8000
D1
E5
BOON
CLERK
4000
D2
Dept DeptNo
Dname
D1
MARKETING
D2
ACCOUNTING
D3
COMPUTER
Introduction to Database System
38
Emp-workin-Dept database

Intension part (database structure) :
– Emp( EmpNo, Ename, Job, Sal, Comm, DeptNo)
– Dept(DeptNo, Dname)
Introduction to Database System
39
Emp-workin-Dept relationship

Semantics :
–
–

Each employee must in one and only one
department.
Each department may have none, one, or many
employees work in
Cardinality of association is one-to-many
Introduction to Database System
40
Emp-manage-Dept database
Emp
Dept
EmpNo
Ename
Job
Sal
Comm
DeptNo
E1
USA
SALESMAN
5000
0
D1
E2
PRANEE
SALESMAN
5000
1000
D2
E3
SUTAT
MANAGER
2000
2000
D3
E4
WATTANA
ANALYST
8000
E5
BOON
CLERK
4000
DeptNo
Dname
D1
MARKETING
D2
ACCOUNTING
D3
COMPUTER
Introduction to Database System
41
Emp-manage-Dept database

Intension part (database structure) :
– Emp( EmpNo, Ename, Job, Sal, Comm, DeptNo)
– Dept(DeptNo, Dname)
Introduction to Database System
42
Emp-manage-Dept relationship

Semantics :
–
–

Each employee may manage none, or one
department.
Each department must be managed by one and only
one employee.
Cardinality of association is one-to-one
Introduction to Database System
43
ขอให้ ถือประโยชน์ ส่วนตน
เป็ นที่สอง
ประโยชน์ ของเพือ่ นมนุษย์
เป็ นกิจทีห่ นึ่ง
ลาภ ทรัพย์ และเกียรติยศ
จะตกแก่ท่านเอง
ถ้ าท่ านทรงธรรมมะแห่ งอาชีพ
ไว้ ให้ บริสุทธิ์
พระอนุศาส์ นของสมเด็จพระบรมราชชนก
Introduction to Database System
44