Introduction to Database System

Introduction to Database System Adisak Intana Lecturer Chapter 4: Relational Database Management System (RDBMS)

Contents Definition and usages of DBMSs RDBMS and relational database Kinds of key Cardinality of associations 2

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

Classification of DBMSs • The database structure implementation criteria : • Network DBMS • Hierarchical DBMS • Relational DBMS (RDBMS) 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 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) 9

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

Stud-id Name Sex Age Stud_id Subj_id Grade 4702777 Math102 B+ 4702777 Amporn F 18 4702777 Phy101 C 4703666 Narin M 19 4703666 Math102 A 4702888 Sasin M 19 4702888 Phy101 B Subj-id Sjame Credit 4702888 Math101 C Phy101 Physics 3 Math102 Math 4 A Sample of relational database Student Enroll Subject 13

Stud-id Name Sex Age 4702777 Amporn F 18 4703666 Narin M 19 4702888 Sasin M 19 Relational database • Domain of an attribute X is the set of all possible values of X. Student Domain of Sex = {F, M} Age= {x I|0 < x < 60} 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. 15

A Sample of relational database Student structure part ( intension ) data part ( extension ) Subject 16

Relational database • Each attribute value in each tuple of a relation must be atomicor null value. • Atomic value is the smallest meaningful value that cannot be divisible. 17

Attributes Relation name EmpNo Ename Job Sal Comm DeptNo Emp 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 atomic value null value A relation in a relational database 18

Relational database • Null valueis a missing value of an attribute that can meaneither • value exists but unknown, or • value does not exist or attribute value is not applicable for this tuple. 19

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. 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). 22

DeptNo Dname Dept D1 MARKETING D2 PERSONNEL D3 COMPUTER Primary key and secondary key • BothDeptNo and Dnameare candidate keys • If DeptNo is chosen to be the primary key, thus Dname must be the secondary key 23

Primary key and secondary key Dname DeptNo Dept D1 MARKETING D2 PERSONNEL D3 COMPUTER • Relation “Dept” structure (intension part) is represented as : Dept(DeptNo, Dname) Secondary key Primary key 24

Primary key • Each relation, there is always an attribute (or a set of attributes) has been set to be theprimary key. • student(std-id, name, sex, age) • subject(sj-id, sjname, credit) • enroll(std-id, sj-id, grade) 25

Primary key • Each tuple of a relation, theattribute value of the primary key (primary key value) can be used to identify that tuple. • Primary key value of each tuple of a relation mustsatisfy the following two time-independent properties • Not null • Unique 26

Stud_id Subj_id Grade 4702777 Math102 B+ 4702777 Phy101 C 4703666 Math102 A 4702888 Phy101 B 4702888 Math101 C Compound primary key • A key which consists of more than one attribute is called a compound key. Enroll Compound key 27

Stud_id Subj_id Grade 4702777 Math102 B+ 4702777 Phy101 C 4703666 Math102 A 4702888 Phy101 B 4702888 Math101 C 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 28

Foreign keys • An attribute (or set of attributes) X of a relation R2 is a foreign keyif 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 29

Foreign key Emp Dept DeptNois a foreign key of relation Emp 30

Foreign key Emp Dept Each DeptNo value in relation Emp must refer to an existed DeptNo value in relationEmp 31

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

Relational data structure • Most flexible • May not be efficient for high transaction volumes • Easy to modify • Most appropriate for business users 34

Supplier-supply part database S SP P 35

Supplier-part database • Intension part (database structure) : • S( s#, sname, status, city) • P(p#, pname, colour, weight, city) • SP(s#, p#, qty) 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 37

Emp-workin-Dept database Emp Dept 38

Emp-workin-Dept database • Intension part (database structure) : • Emp( EmpNo, Ename, Job, Sal, Comm, DeptNo) • Dept(DeptNo, Dname) 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 40

EmpNo Ename Job Sal Comm DeptNo Emp 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 Dept Emp-manage-Dept database 41

Emp-manage-Dept database • Intension part (database structure) : • Emp( EmpNo, Ename, Job, Sal, Comm, DeptNo) • Dept(DeptNo, Dname) 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 43

ขอให้ถือประโยชน์ส่วนตน เป็นที่สอง ประโยชน์ของเพื่อนมนุษย์ เป็นกิจที่หนึ่ง ลาภ ทรัพย์ และเกียรติยศ จะตกแก่ท่านเอง ถ้าท่านทรงธรรมมะแห่งอาชีพ ไว้ให้บริสุทธิ์ พระอนุศาส์นของสมเด็จพระบรมราชชนก 44

Introduction to Database System