Data Management Systems Design

1
Data Management Systems Design

• CIS 631 Lecture Notes 6
• Cyril S. Ku

2
SQL
SQL - Structured Query Language -
Originally SEQUEL ( Structured English Query
Language ) - IBM
Research - for
SYSTEM R SQL - DB2 ( IBM )
SQL is based on set and relational operations
with certain modifications and enhancements. A
typical SQL query has the form SELECT A1 , A2
, , An FROM r1 , r2 , , rm WHERE
P Ai represents attributes, ri represents
relations, and P is a predicate. This query is
equivalent to the relational algebra expression
? A1 , A2 , , An (?P ( r1 ? r2 ? ?
rm ) )
3
e.g. Project ( PName, PNumber)
Query Find the names of all the projects in the
Project relation. SELECT PName FROM
Project ?PName (Project) Note No WHERE
clause gt apply to all tuples
Query Find the names of all the projects in the
Project relation, and remove
duplicates. SELECT DISTINCT PName FROM Project I
n general, an SQL table (relation) is not a set
of tuples. (Duplicate tuples can exist in a
table.) Get a copy of the Project relation
SELECT ( gt all the
attributes) FROM Project
4
Let R(A, B, C, N) S(D, E, N) Cross
Product SELECT FROM R, S Cross Product
followed by Projection SELECT B, E FROM R,
S Cross Product followed by Selection SELECT
FROM R, S WHERE D dumb
5
The COMPANY Relational Database Schema
EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE,
ADDRESS, SEX, SALARY,
SUPERSSN,
DNO) DEPARTMENT (DNAME, DNUMBER, MGRSSN,
MGRSTARTDATE) DEPT_LOCATI
ONS (DNUMBER, DLOCATION) PROJECT (PNAME,
PNUMBER, PLOCATION, DNUM) WORKS_ON (ESSN, PNO,
HOURS) DEPENDENT (ESSN, DEPENDENT_NAME, SEX,
BDATE, RELATIONSHIP)
6
Query Retrieve the birthdate and address of the
employee whose name is John B.
Smith SELECT BDATE, ADDRESS FROM
EMPLOYEE WHERE FNAME John AND MINIT B
AND LNAME
Smith Query Retrieve the name and address of
all employees who work for the
Research department. SELECT FNAME, LNAME,
ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE
DNAMEResearch AND DNUMBER DNO
Selection condition
Join condition
7
R_DEPT ? ? DNAMEResearch(DEPARTMENT) R_DEPT_E
? (R_DEPT DNUMBERDNO EMPLOYEE) RESULT ? ?
FNAME, LNAME, ADDRESS (R_DEPT_E) In general, any
number of select and join conditions may be
specified in a single SQL query.
Query For every project located in Stafford,
list the project number, the
controlling department number, and the
department managers last name, address, and
birthdate.
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM
PROJECT, DEPARTMENT, EMPLOYEE WHERE
DNUMDNUMBER AND MGRSSNSSN AND
PLOCATIONStafford
8
S_PROJ ? ? PLOCATIONStafford(PROJECT) C_DEPT ?
(S_PROJ DNUMDNUMBER DEPARTMENT) P_DEPT_M ?
(C_DEPT MGRSSNSSN EMPLOYEE) RESULT ? ?
PNUMBER, DNUM, LNAME, ADDRESS, BDATE(P_DEPT_M)
Qualification and Aliases Qualification
Relation-Name . Attribute-Name R(A, B, C,
N) S(D, E, N) SELECT A, B, E FROM
R, S WHERE R.N S.N
9
Set operations of union, except (difference), and
intersect . Query Make a list of project numbers
for projects that involve an
employee whose last name is Smith, either as a
worker or as a manager of the
department that controls the
project. (SELECT PNUMBER FROM PROJECT,
DEPARTMENT, EMPLOYEE WHERE DNUMDNUMBER AND
MGRSSNSSN AND
LNAMESmith) UNION (SELECT
PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBERPNO AND ESSNSSN AND
LNAMESmith)
Note union compatible of the two SELECT
statements.
10
SMITHS (ESSN) ? ? SSN (? LNAMESmith
(EMPLOYEE)) SMITHS_WORKER_PROJ ? ? PNO (WORKS_ON
SMITHS) MGRS ? ? LNAME, DNUMBER (EMPLOYEE
SSNMGRSSN
DEPARTMENT) SMITH_MGRS ? ? LNAMESmith
(MGRS) SMITH_MANAGED_DEPTS (DNUM) ? ? DNUMBER


(SMITH_MGRS) SMITH_MGR_PROJS (PNO) ? ?
PNUMBER
(SMITH_MANAGED_DEPTS PROJECT) RESULT ?
(SMITH_WORKER_PROJS ?
SMITH_MGR_PROJS)
11
Nested Queries and Set Comparisons SELECT
DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN
(SELECT PNUMBER FROM PROJECT,
DEPARTMENT,
EMPLOYEE WHERE
DNUM DNUMBER AND MGRSSN SSN
AND LNAME Smith )
OR PNUMBER IN (SELECT PNO
FROM WORKS_ON, EMPLOYEE WHERE
ESSN SSN AND LNAME Smith )
Outer Query
Nested Query
12
IN - compares a value v with a set (or multi-set)
of values V and evaluates to TRUE if v
is one of the elements in V. v IN V
v op ANY V (v op SOME V) v op
ALL V op ?, ?, ?, ?, ?, ? ? e.g.
List the names of employees whose salary is
greater than the salary of all the
employee in department 5 SELECT LNAME,
FNAME FROM EMPLOYEE WHERE
SALARY gt ALL (SELECT SALARY
FROM
EMPLOYEE
WHERE DNO 5 )
13
Attribute Qualification e.g. Retrieve the name
of each employee who has a dependent
with the same first name and same sex as the
employee. SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E WHERE E.SSN IN
(SELECT ESSN
FROM DEPENDENT
WHERE ESSNE.SSN AND

E.FNAME
DEPENDENT_NAME AND
SEX
E.SEX
Refers to SEX in DEPENDENT (Innermost nested
query)
Must qualify
In general, a query written with nested SELECT
FROM WHERE blocks and using the or IN
comparison operators can always be expressed as a
single block query. E.g. from previous
example SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E, DEPENDENT D WHERE
E.SSND.ESSN AND E.SEXD.SEX AND
E.FNAMED.DEPENDENT_NAME
14
Built-In Functions These functions operate in
the set of values of a column of a relation,
and return a value. SUM sum of
values AVG average value MAX
maximum value MIN minimum value COUNT
number of values e.g. SELECT SUM
(SALARY), MAX (SALARY),
MIN(SALARY), AVG(SALARY) FROM
EMPLOYEE
15
GROUP BY Clause Query For each department,
retrieve the department number, the
number of employees in the department, and their
average salary. SELECT DNO,
COUNT(), AVG(SALARY) FROM
EMPLOYEE GROUP BY DNO
HAVING Clause For groups that satisfy certain
condition. SELECT DNO, COUNT (), AVG (SALARY)
FROM EMPLOYEE GROUP BY DNO HAVING COUNT
() gt 1
16
View in SQL A view is a single table that is
derived from other tables. Table can be base
table or virtual table Base table - tuples are
actually stored in the Database. Virtual table -
not in physical form. Why a View is Needed ?
- Provide a way of specifying a table that we
need to reference frequently, even though
it may not exist physically. - Simplify the
specification of certain queries. - Used as
security mechanism. (Make a view on certain
attributes and grant security rights).
17
Virtual table name
e.g. CREATE VIEW WORKS_ONV AS SELECT FNAME,
LNAME, PNAME, HOURS FROM
EMPLOYEE, PROJECT, WORKS_ON WHERE
SSNESSN AND PNOPNUMBER - Query WORKS_ONV as
ordinary base table. - View is always up to date
- the view is not realized at the
time of view definition but rather at the time we
specify a query on the view.
defining tables of the view
18
Updating Views - A view with a single
defining table is updatable if the view
attributes contain the primary key or some other
candidate key of the base relation (because
this maps each (virtual) view tuple to a
single base tuple). - Views defined on
multiple tables using joins are generally
not updateable. - Views defined using
grouping and aggregate functions are not
updateable.