Transcript Seminar4

Advanced Databases
Seminar 6: Implementing
Relational Algebra Data
Model using SQL
Seminar #6
CG096 Advanced Database Technologies
1
Purpose



To understand how the relational
operations are implemented in
SQL
To investigate relational
databases using queries which
can be expressed as algebraic
operations
To compare the actual output
produced by an SQL interpreter
from a database with the result of
applying operations to its
relational model
Seminar #6
CG096 Advanced Database Technologies
2
Scenario




The Company database contains data
about the departments with their
employees, as well as for the
department projects with the
employees working on them.
The set of relations in the Company
relational schema is provided on the
Figure 1.
Data for one particular instance of
database Company is provided on
Figure 2.
The SQL script for creating of relational
database with the above test data
preloaded is provided in the file
Company.sql which you can
download from Blackboard.
Seminar #6
CG096 Advanced Database Technologies
3
Company Relational Schema
DEPT: stores data about company departments,
including their managers

EMP: stores data about people working in the
company

PROJ: stores data about the projects on which
the company currently works

ALLOC: stores data about the people allocated
to different projects

Figure 1
Seminar #6
CG096 Advanced Database Technologies
4
Database Schema Instance
DEPT
DEPT_NO DEPT_NAME
D1
D2
D3
D4
D5
EMP
EMP_NO EMP_NAME
E1
E2
E3
E4
E5
E6
E7
E8
E9
PROJ
ALLOC
Production
Sales
Accounts
Admin
Personnel
Smith
Jones
Roberts
Evans
Brown
Green
McDougal
McNally
Fletcher
MANAGER_NO BUDGET
E5
E9
E8
E7
100000
250000
95000
156000
196000
DEPT_NO SALARY MARITAL_STATUS
D1
D2
D2
D3
D3
D3
D4
D5
D5
9900
13200
11000
16500
27500
13200
17600
12100
13200
PROJ_NO DEPT_NO START_DATE
DEADLINE
P1
P2
P3
P4
P5
P6
23-FEB-98
14-MAY-97
03-MAR-99
01-JAN-99
31-DEC-99
30-DEC-99
EMP_NO
E1
E1
E2
E2
E4
E5
E6
E9
E5
E7
D1
D1
D2
D3
D4
-
20-APR-94
21-JAN-95
02-FEB-96
11-DEC-95
08-OCT-95
09-OCT-95
W
M
M
S
S
M
D
M
S
PROJ_NO
P1
P2
P1
P5
P4
P4
P4
P4
P3
P3
Figure 2
Seminar #6
CG096 Advanced Database Technologies
5
Tasks for the Seminar
1.
Write the following queries against Company
database using both relational algebra
operators and SQL
i.
ii.
iii.
iv.
v.
vi.
vii.
viii.
ix.
x.
xi.
Get a list of employee numbers
Get details of employees with salaries in excess
of 15,000 pounds
Get a list of departments which have projects or
employees
Get a list of departments which have both
projects and employees
Get a list of departments that have projects but
no employees are working on them
Get information on all possible combinations of
employees and departments
Find information on those employees and their
departments who are working for them but are
not their managers
Find information about employees who are
currently working on projects
Find information about the projects of the
departments
Part (ix) including registered projects which are
not assigned to any department
Part (x) including departments without registered
projects
Seminar #6
CG096 Advanced Database Technologies
6
2.
Connect to the Oracle database using SQL*Plus,
load the Company database contained in the file
Company.sql and execute the SQL scripts you
have written against it

Connect to the Oracle database using
SQL*Plus

Load the Company database
> @Company

Execute the queries
Execute your own queries in SQL which
implement exactly the same algebra queries
as the ones written in the algebra before

Compare the results
Why the results received as an output of SQL
interpreter with the expected results in the
case of applying algebraic operations on the
relational schema differ (if they really differ)?
Seminar #6
CG096 Advanced Database Technologies
7