Transcript SQL: Queries, Constraints, Triggers

Relational Operators
1
Relational
Operators
Properties

Relational operations are specified using
Structured Query Language (SQL) -- a
standard for relational database access.

Relational operations are set level, meaning
that they operate on multiple rows, rather
than one record at a time.

SQL is non-procedural, meaning that the
user specifies what data is to be retrieved
rather than how to retrieve the data.
2
Relational
Operators
Properties

Each operator takes one or more tables
as it operand(s) and produces a table as
its result.

Any column value in a table can be
referenced, not just keys.

Operations can be combined to form
complex operations.
3
Operations on a DBMS
Can be specified using
 Relational Algebra operations (what we learn
now)
 Are usually divided into two groups
• Set theory operations
• Operations specifically developed for relational databases
 But are considered too technical for ordinary users,
hence the birth of SQL
 They are written as a sequence of steps, when
executed produce the results
 Hence the user must give say ”how” and not “what”
is needed
4
Operations on a DBMS
Can be specified using
 Relational calculus
• Another formal query language which gives ‘what’ is
required, and not how.
• Eg:- {t.FNAME,t.LNAME|EMPLOYEE(t) and
t.SALARY>500}
SELECT T.FNAME, T.LNAME
FROM EMPLOYEE AS T
WHERE T.SALARY>500

SQL
5
Relational Operators
Selection: horizontal subset of a table
Employee
E-No
E-Name
179
857
342
Silva
Perera
Dias
Sales Employee
D-No
E-No
E-Name
7
4
7
179
342
Silva
Dias
D-No
7
7
Sales-Emp = D-No=7 (Employee)
6
Projection: vertical subset of a table
Employee
E-No
E-Name
179
857
342
Silva
Perera
Dias
Employee Names
D-No
E-No
E-Name
7
4
7
179
857
342
Silva
Perera
Dias
Emp-Names = E-No, E-Name (Employee)
7
Cartesian Product: Creates a single table from two tables.
Employee
Department
E-No
E-Name
179
857
342
Silva
Perera
Dias
D-No
D-No D-Name
7
4
7
4
7
Finance
Sales
M-No
857
179
Emp-Info
E-No
E-Name
179
857
342
179
857
342
Silva
Perera
Dias
Silva
Perera
Dias
D-No D-No
7
4
7
7
4
7
4
4
4
7
7
7
D-Name
M-No
Finance
Finance
Finance
Sales
Sales
Sales
857
857
857
179
179
179
Emp-Info = Employee  E.D-No=D.D-No Department
8
Join: Creates a single table from two tables.
Department
Employee
E-No
E-Name
179
857
342
Silva
Perera
Dias
D-No
D-No D-Name
7
4
7
4
7
Finance
Sales
M-No
857
179
EquiJoin
Emp-Info
E-No
E-Name
179
857
342
Silva
Perera
Dias
D-No D-No
7
4
7
7
4
7
D-Name
M-No
Sales
Finance
Sales
179
857
179
Emp-Info = Employee
E.D-No=D.D-No
Department
9
Joins…
The most common join is where we only use
the ‘equal’ operator , and is known as
equijoin.
 We can use other operator (=,<,>,<=, etc…)
for the join condition also
 The natural join (*) can be used to get rid of
the additional attribute in an equijoin
condition.

10
Joins…

In a natural join only the matching tuples are
displayed. The ‘left outer join’ and ‘right
outer join’ and ‘full outer join’can be used to
find even non matching tuples
11
Natural Join: Creates a single table from two tables.
Department
Employee
E-No
E-Name
179
857
342
Silva
Perera
Dias
D-No
D-No D-Name
7
4
7
4
7
Finance
Sales
M-No
857
179
Emp-Info
E-No
E-Name
179
857
342
Silva
Perera
Dias
D-No
D-Name
M-No
7
4
7
Sales
Finance
Sales
179
857
179
Emp-Info = Employee  E.D-No=D.D-No Department
12
Relational Operators
Other operators
Union
Intersection
Difference
Set operations from mathematical set theory
13
Student
Fname
Lname
Kapila
Nimal
Ajith
Rohan
Dias
Perera
Silva
Mendis
Instructor
FN
LN
Sunil
Kamal
Saman
Kapila
Nimal
De Silva
Soysa
Silva
Dias
Perera
Set
Operators
Union
Stu-Inst
Fname
Lname
Kapila
Nimal
Ajith
Rohan
Sunil
Kamal
Saman
Dias
Perera
Silva
Mendis
De Silva
Soysa
Silva
Stu-Inst = Student  Instructor
14
Student
Fname
Lname
Kapila
Nimal
Ajith
Rohan
Dias
Perera
Silva
Mendis
Set
Operators
Intersection
Stu-Inst
Instructor
Fname
Lname
FN
LN
Sunil
Kamal
Saman
Kapila
Nimal
De Silva
Soysa
Silva
Dias
Perera
Kapila
Nimal
Dias
Perera
Stu-Inst = Student  Instructor
15
Student
Fname
Lname
Kapila
Nimal
Ajith
Rohan
Dias
Perera
Silva
Mendis
Instructor
FN
LN
Sunil
Kamal
Saman
Kapila
Nimal
De Silva
Soysa
Silva
Dias
Perera
Set
Operators
Stu-Inst
Difference
Fname
Lname
Ajith
Rohan
Silva
Mendis
Stu-Inst = Student - Instructor
Inst-Stu = Instructor - Student
Inst-Stu
Fname
Lname
Sunil
Kamal
Saman
De Silva
Soysa
Silva
16
Complete Set of Relational Algebra
Operations
It has been proved that {, , , , } is a complete set.
Any other relational algebra operator can be expressed
in terms of the above operators.
E.g.
R  S = (R  S)  ( ( R  S)  (S  R) )
17
Division operator
Rename operator
R(FirstName,LastName,Salary) = Fname,Lname,Sal (Employee)
Can be useful for set related operations.
18
Relational Operators
Because the result of every relational operation is a
table, operators can be combined to create complex
operations. For example:
Select + Project
B
A
+
A B
Project + Select + Join
19
Relational Operators
Get course names thought by lecturer ‘Dr Kodikara’
course(cno, cname, lecturer)
employee(empno, ename, designation)
Emp_Kodi
 eneme=’Dr. Kodikara’ Employee
Courses
 cname, lecturer Course
Kodi_courses
Emp_Kodi * empno = lecture Courses
Employee
Course
+
Kodi_courses
Select + Project + N-Join
20
Logical Database Design Normalisation
21
Normalisation
Is derivation of data as a set of
Non-Redundant,
Consistent and
Inter-Dependent Relations
22
Normalisation
 Normalisation
is a set of data design
standards.
 It is a process of decomposing unsatisfactory
relations into smaller relations.
 Like entity–relationship modelling were
developed as part of database theory.
23
Normalisation - Advantages
Reduction of data redundancy within tables:
- Reduce data storage space
- Reduce inconsistency of data
- Reduce update cost
- Remove many-to-many relationship
- Improve flexibility of the system
24
Normalisation - Disadvantages
Reduction in efficiency of certain data retrieval
as relations may be joined during retrieval.
-
Increase join
Increase use of indexes: storage (keys)
Increase complexity of the system
25
Normal Forms
A state of a relation that results from applying
simple rules regarding functional dependencies
(or relationships between attributes) to that
relation.
0NF multi-valued attributes exists
1NF any multi-valued attributes have been removed
2NF any partial functional dependencies have been
removed
3NF any transitive dependencies have been removed
26
Functional Dependencies and Keys
Functional dependency:A constraint between two
attributes or two sets of attributes
The functional dependency of B on A is
represented by an arrow: A  B
e.g.
NID (SSN)  Name, Address, Birth date
VID  Make, Model, Colour
ISBN  Title, First Author
27
Functional Dependencies and Keys
Functional dependency (definition)
For any relation R (e.g. book), attribute B (e.g. title) is
functionally dependent on attributes A (e.g. ISBN),
if for every valid instance of A (e.g. 981-235-996-6),
that value of A uniquely determines the value of B
(e.g. Modern Database Management)
28
Input for the Normalisation Process
Database Design process (phase 1)
data requirements and data analysis
entity types (e.g. Supplier, Order)
attributes describing each entity type with its
meaning (e.g. supplier name and part name)
attributes relationships to other attributes.
(e.g.supplier no of Supplier to supplier no of purchase Order)
29
Purchase Order - Attribute Analysis
ATTRIBUTE TYPE LEN- DESCRIPTION
GTH
PO-NO
N
3
Unique purchase order (PO) number.
Many parts can be ordered in one PO
PO-DATE
D
8
DDMMYYYY date when PO written
EMP-CODE
C
2
Unique code of employee who wrote
the PO
SUPP-NO
N
3
Unique number assigned to supplier
SUPP-NAME
C
20 Supplier name
PART-NO
N
2
Unique number assigned to each part
PART-DESC
C
10 Part description
PART-QTY
N
2
Quantity of parts ordered in given PO
Key PO-NO
30
Purchase Order Relation in 0NF
PO- PO-DATE EMP- SUPP
SUPPNO
CODE -NO
NAME
111 01012001 M2
222 AC Stores
112
01012001
S3
105
I Hardware
113
02012001
S1
111
BC Trading
114
115
116
02012001
03012001
04012001
M2
S1
S1
150
222
100
DO Service
AC Stores
LM Centre
PARTNO
P1
P2
P3
P5
P2
P5
P1
P3
P6
P7
P8
PART- PARTDESC QTY
Nut
10
Bolt
5
Nail
3
Screw
6
Bolt
2
Screw
1
Nut
3
Nail
4
Plug
5
Pin
8
Fuse
2
31
Normalisation Process
Apply a set of
normalisation
rules to all the
attributes of the
entity types
identified in the
data
requirement
step.
0NF Relations
1NF Relations
2NF Relations
3NF Relations
Optimised
Relations
32
Output of the Normalisation Process

A list of normalised entity types in at
least third normal form (3NF), such that
all non-key attributes of each entity type
fully depend on the whole key and
nothing but the key
33
First Normal Form - 1NF
A relation is in First Normal Form (1NF) if
ALL its attributes are ATOMIC.
ie.
If there are no repeating groups.
If each attribute is a primitive.
e.g. integer, real number, character string,
but not lists or sets
non-decomposable data item
single-value
34
Purchase Order Relation in 0NF
PO( PO-NO, PO-DATE, EMP-CODE, SUPP-NO,
SUPP-NAME, PARTS-ORDERED{PARTNO, PART-DESC, PART-QTY})
Within a single purchase order we could
find several part numbers, part descriptions
and part quantities. Hence, parts ordered
can be decomposed.
35
Purchase Order Relation in 0NF
PO- PO-DATE EMP- SUPP
SUPPNO
CODE -NO
NAME
111 01012001 M2
222 AC Stores
112
01012001
S3
105
I Hardware
113
02012001
S1
111
BC Trading
114
115
116
02012001
03012001
04012001
M2
S1
S1
150
222
100
DO Service
AC Stores
LM Centre
PARTNO
P1
P2
P3
P5
P2
P5
P1
P3
P6
P7
P8
PART- PARTDESC QTY
Nut
10
Bolt
5
Nail
3
Screw
6
Bolt
2
Screw
1
Nut
3
Nail
4
Plug
5
Pin
8
Fuse
2
36
First Normal Form - 1NF



1NF deals with the shape of a record type
All occurrences of a record type must
contain the same number of fields
A relational schema is at least in 1NF
37
1NF - Actions Required
1) Examine for repeat groups of data
2) Remove repeat groups from relation
3) Create new relation(s) to include
repeated data
4) Include key of the 0NF to the new
relation(s)
5) Determine key of the new relation(s)
38
Purchase Order Relations in 1NF
PO-PART
PO
PONO
111
112
113
114
115
116
PODATE
01012001
01012001
02012001
02012001
03012001
04012001
EMP- SUP
SUPPCODE P-NO
NAME
M2
222 AC Stores
S3
105 I Hardware
S1
111 BC Trading
M2
150 DO Service
S1
222 AC Stores
S1
100 LM Centre
PO- PAR PARTNO T-NO DESC
111
P1 Nut
111
P2 Bolt
111
P3 Nail
111
P5 Screw
112
P2 Bolt
112
P5 Screw
113
P1 Nut
113
P3 Nail
114
P6 Plug
115
P7 Pin
116
P8 Fuse
PART
-QTY
10
5
3
6
2
1
3
4
5
8
2
39
Problems - 1NF
1. INSERT PROBLEM
cannot know available parts until an order is placed
(e.g. P4 is bush)
2. DELETE PROBLEM
loose information of part P7 if we cancel purchase
order 115 (i.e. Delete PO-PART for Part No P7)
3. UPDATE PROBLEM:
to change description of Part P3 we need to change
every tuple in PO-PART containing Part No P3
40
Second Normal Form - 2NF
A relation is in 2NF if it is in 1NF and every
non-key attribute is dependent on the
whole key
i.e.
Is not dependent on part of the key
only.
41
PO-PART Relation (Parts Ordered)
in 1NF
PO-PART( PO-NO, PART-NO, PART-DESC,
PART-QTY)
Part Description is depended only on Part
No, which is part of the key of PO-PART.
42
Parts Ordered Relation in 1NF
PO- PARTNO
NO
111
P1
111
P2
111
P3
111
P5
112
P2
112
P5
113
P1
113
P3
114
P6
115
P7
116
P8
PART- PARTDESC QTY
Nut
10
Bolt
5
Nail
3
Screw
6
Bolt
2
Screw
1
Nut
3
Nail
4
Plug
5
Pin
8
Fuse
2
43
Second Normal Form - 2NF
Deals with the relationship between non-key
and key fields
A non-key field cannot be a fact about a subset
of a key
It is relevant when the key is composite, i.e.
consists of several fields
44
2NF - Actions Required
If entity has a concatenated key
1) Check each attribute against the whole
key
2) Remove attribute and partial key to new
relation
3) Optimise relations
45
Parts Ordered Relations in 2NF
PO-PART
PO- PART- PARTNO
NO
QTY
111
P1
10
111
P2
5
111
P3
3
111
P5
6
112
P2
2
112
P5
1
113
P1
3
113
P3
4
114
P6
5
115
P7
8
116
P8
2
PART
PARTNO
P1
P2
P3
P5
P6
P7
P8
PARTDESC
Nut
Bolt
Nail
Screw
Plug
Pin
Fuse
46
Purchase Order Relations in 2NF
PART
PO
PONO
111
112
113
114
115
116
PODATE
01012001
01012001
02012001
02012001
03012001
04012001
PAR
T-NO
P1
P2
P3
P5
P6
P7
P8
PARTDESC
Nut
Bolt
Nail
Screw
Plug
Pin
Fuse
EMP- SUP
SUPPCODE P-NO
NAME
M2
222 AC Stores
S3
105 I Hardware
S1
111 BC Trading
M2
150 DO Service
S1
222 AC Stores
S1
100 LM Centre
PO-PART
PO- PAR PART
NO T-NO -QTY
111
P1
10
111
P2
5
111
P3
3
111
P5
6
112
P2
2
112
P5
1
113
P1
3
113
P3
4
114
P6
5
115
P7
8
116
P8
2
47
Problems - 2NF
1. INSERT PROBLEM
cannot know available suppliers until an order is placed
(e.g. 200 is hardware stores)
2. DELETE PROBLEM
loose information of supplier 100 if we cancel purchase
order 116 (i.e. Delete PO for Supplier No 100)
3. UPDATE PROBLEM
to change name of Supplier 222 we need to change
every tuple in PO containing Supplier No 222
48
Third Normal Form - 3NF
A relation is in 3NF if it is in 2NF and each
non-key attribute is only dependent on the
whole key, and not dependent on any nonkey attribute.
i.e. no transitive dependencies
49
PO Relation in 2NF
PO( PO-NO, PO-DATE, EMP-CODE, SUPP-NO,
SUPP-NAME)
Supplier name is a non-key field depended
on another non-key field (i.e. the supplier
no) in addition to be depended on the key
purchase order no
50
Third Normal Form - 3NF
Deals with the relationship between non-key
fields
A non-key field cannot be a fact about another
non-key field
51
3NF - Actions Required
1) Check each non-key attribute for dependency
against other non-key fields
2) Remove attribute depended on another nonkey attribute from relation
3) Create new relation comprising the attribute
and non-key attribute which it depends on
4) Determine key of new relation
5) Optimise
52
PO and SUPPLIER Relations in 3NF
PO
PONO
111
112
113
114
115
116
PO-DATE EMP- SUPP
CODE -NO
01012001 M2
222
01012001
S3
105
02012001
S1
111
02012001 M2
150
03012001
S1
222
04012001
S1
100
SUPPLIER
SUPP
-NO
100
105
111
150
222
SUPPNAME
LM Centre
I Hardware
BC Trading
DO Service
AC Stores
53
Purchase Order Relations in 3NF
SUPPLIER SUP
PO
PONO
111
112
113
114
115
116
SUPPP-NO
NAME
222 AC Stores
105 I Hardware
111 BC Trading
150 DO Service
222 AC Stores
100 LM Centre
POEMP- SUP
DATE
CODE P-NO
01012001
M2
222
01012001
S3
105
02012001
S1
111
02012001
M2
150
03012001
S1
222
04012001
S1
100
PO-PART
PART
PAR
T-NO
P1
P2
P3
P5
P6
P7
P8
PARTDESC
Nut
Bolt
Nail
Screw
Plug
Pin
Fuse
PO- PAR PART
NO T-NO -QTY
111
P1
10
111
P2
5
111
P3
3
111
P5
6
112
P2
2
112
P5
1
113
P1
3
113
P3
4
114
P6
5
115
P7
8
116
P8
2
54
Further Normalization




BCNF or Boyce–Codd Normal form
4th Normal form
5th Normal form
In a normal situation normalization up-to 3NF is
quite sufficient.Certain relations may even be denormalized on account of efficiency. The
Normalizations which are discussed next are not
practically enforced most of the time.
But a relation in 3NF does not guarantee that all
anomalies have been removed, hence the additional
normalizations.
55