What is a Database System?
Download
Report
Transcript What is a Database System?
Introduction to Relational Database Systems
Introduction to
Relational Database Systems
Lecture 4
1
Introduction to Relational Database Systems
Relational database systems
data objects
relations / tables
operators
applied to tables
generate tables
2
Introduction to Relational Database Systems
Relations / tables
explicit data values
extensionally defined
atomic
keys
integrity
design
includes how to organise data in tables
3
Introduction to Relational Database Systems
Data about departments
Depts
Dept_id
Dept_name
Budget
D1
D2
D3
Marketing
Development
Research
10M
12M
5M
4
Introduction to Relational Database Systems
Data about employees
Emps
E_id
E1
E2
E3
E4
E_name
Smith
John
Stella
Art
Dept_id
D1
D1
D2
D3
Salary
40K
42K
30K
35K
5
Introduction to Relational Database Systems
Atomic values
E_id
E_name
Dept_id Salary Address
E1
John Smith
D1
25 18A Cowley SE14 6AJ
E2
John Wiles
D1
E3
Mary Kent
D2
E4
Jenny Wolf
D3
45 7 Big Road, Kent, US25
7BB
30 149 Rue d’Enfent,
13556 Paris, France
35 4 New Cross Road
no access to
individual items
6
Introduction to Relational Database Systems
Primary and foreign keys
E_id
E1
E2
E3
E4
E_name
Smith
John
Stella
Art
primary
Dept_id
D1
D1
D2
D3
Salary
40K
42K
30K
35K
foreign
Dept_id
Dept_name
Budget
D1
D2
D3
Marketing
Development
Research
10M
12M
5M
7
Introduction to Relational Database Systems
Integrity
restrictions on data defined by users
on individual tables
• age > 18; salary < 100k
on more than one table
• if budget < 10M then salary < 50k
implicit in the data model
8
Introduction to Relational Database Systems
Primary key integrity
E_id
E100A
E120A
E120A
E110C
E_name
Smith
John
Stella
Art
Dept_id
D1
D1
D2
D3
Salary
40K
42K
30K
35K
incorrect model
9
Introduction to Relational Database Systems
Foreign key integrity
E_id
E1
E2
E3
E4
E_name
Smith
John
Stella
Art
?
Dept_id
D1
D1
D5
D3
Salary
40K
42K
30K
35K
incorrect
model
Dept_id
Dept_name
Budget
D1
D2
D3
Marketing
Development
Research
10M
12M
5M
10
Introduction to Relational Database Systems
Relational operators
characteristics
set at a time
base and derived tables
‘closed’ with respect to relations / tables
• nested expressions
include
RESTRICT
PROJECT
JOIN
11
Introduction to Relational Database Systems
RESTICT
RESTRICT Depts WHERE Budget > 8M
Dept_id
Dept_name
Budget
D1
D2
Marketing
Development
10M
12M
12
Introduction to Relational Database Systems
PROJECT
PROJECT Depts OVER Dept_id, Budget
Dept_id
Budget
D1
D2
D3
10M
12M
5M
13
Introduction to Relational Database Systems
JOIN
JOIN Depts AND Emps OVER Dept_id
Dept_id Dept_name
Budget E_id
E_name
Salary
D1
D1
D2
D3
10M
10M
12M
5M
Smith
John
Stella
Art
40K
42K
30K
35K
Marketing
Marketing
Development
Research
E1
E2
E3
E4
14
Introduction to Relational Database Systems
Nested statements
“the members of all departments that have the budget greater than 7M”
JOIN (RESTRICT Depts WHERE Budget > 7M) AND Emps OVER Dept_id
Dept_id Dept_name
D1
D1
D2
Budget E_id
Marketing
10M
Marketing
10M
Development 12M
E1
E2
E3
E_name
Salary
Smith
John
Stella
40K
42K
30K
15
Introduction to Relational Database Systems
Relational model
a data model in which all data is modelled as
relations
a way of looking at data
a prescription for a way of
representing data
manipulating data
representing integrity constraints
16
Introduction to Relational Database Systems
Relational database systems
relational DBMS
implements the relational model
• not in its entirety
• may add new features
relational database system
a database application developed in the relational
model and implemented in a relational DBMS
physical details hidden from the user
17
Introduction to Relational Database Systems
Relational DBMS - features
views
security
the optimiser
the data catalogue / data dictionary
18
Introduction to Relational Database Systems
Views (in relational systems)
named derived table
the definition stored in the catalogue
evaluated only when used
optimisation
used as if it were a real table
problems with updates
views
ANSI/SPARC
relational
19
Introduction to Relational Database Systems
Views
CREATE VIEW TopEmp AS
PROJECT
(SELECT Emps WHERE Salary > 33K)
OVER E_name, Salary
20
Introduction to Relational Database Systems
Views - usage
SELECT TopEmp WHERE Salary <= 40
SELECT
PROJECT
SELECT Emps WHERE Salary > 33
OVER E_name, Salary
WHERE Salary <= 40
21
Introduction to Relational Database Systems
22
Introduction to Relational Database Systems
Security and views
how would you use the view mechanism in
conjunction with the security system?
DEFINE SECURITY RULE AS ...
23
Introduction to Relational Database Systems
The optimiser
operators - set level
the DBMS decides how to best perform the
operations, based on
• strategies of evaluation
• information about the DB (in the catalogue)
24
Introduction to Relational Database Systems
The best evaluation strategy
PROJECT
SELECT Emps WHERE E_id = E2
OVER E_name, Salary
25
Introduction to Relational Database Systems
The catalogue
information about the database
•
•
•
•
•
schemas
mappings
integrity rules
views definition
security rules ...
other modules that need it
• the optimiser
• the security system ...
26
Introduction to Relational Database Systems
The system table “Tables”
Tabname Colcount Rowcount …
Depts
3
3
…
Emps
4
4
…
…
Tables
…
…
…
…
…
…
PROJECT Tables OVER Coulcount, Rowcount
27
Introduction to Relational Database Systems
The system table “Columns”
Tabname
Depts
Depts
Depts
Emps
Emps
Emps
Emps
…
Tables
Colname
Dept_id
Dept_name
Budget
E_id
E_name
Dept
Salary
…
Tabname
… …
… …
… …
… …
… …
28
Introduction to Relational Database Systems
Summary
relational model
• relations
• operators
• integrity
relational DBMSs
•
•
•
•
implement the relational model
views + security
the optimiser
the catalogue
next lecture : SQL
29