Transcript sid: integer, bid: integer, day: date

Dr. Hassan TOUT
Office: 234
Office Hours
Day
Time
Tuesday
11:30 – 13:00
Friday
11:30 – 13:00
This course is available from internet:
http://coursefs1.ul.edu.lb
Intranet:
http://172.16.2.198
DataBase – Info 300
Faculty of Sciences I
2008-2009
Textbooks

Database management Systems, Raghu
Ramakrishman / Johannes Gehrke, MC
GRAW HILL

Fundamentals of DATABASE
SYSTEMS, ELMASRI / NAVATHE,
ADDISON-WESLEY
2008-2009
DataBase
H. TOUT
2
Outline
Introduction
 Entity/Relationships model
 The relational model
 The SQL query language
 Normal Forms

2008-2009
DataBase
H. TOUT
3
DataBase (info 300)
2008-2009
4
Chapter III
THE RELATIONAL MODEL
2008-2009
DataBase
H. TOUT
5
Outline
Introduction
 The relational model concepts:

Relation
 Keys and primary key
 Foreign keys

SQL: Data Definition language
 SQL: Data Manipulation language

2008-2009
DataBase
H. TOUT
6
INTRODUCTION
Relational model is by far the dominant
data model;
1. Very simple and elegant:

DB: collection of one or more relations;
 Relation: table with rows and columns.

2.
Permits the use of simple, high-level
(declarative) languages to query the
data;
2008-2009
DATABASE
TOUT
H.
7
The relational model concepts
Attributes, fields, columns
Students
Relation
Name
Records,
Rows
Sid
Name
Login
Age
Average
22000
Pascal
pascal@info
19
42.5
23404
Sami
sami@ge
21
66.0
22010
Saly
saly@ge
18
12.3
33320
Mado
mado@info
18
65.6
Schema
Relation
Instance
Students(Sid: text, Name: text, Login: text, Age: integer, Average: real)
Domain name
2008-2009
DATABASE
H. TOUT
8
Table creation (SQL)
Text of 20 characters
CREATE TABLE Students (
Sid
CHAR(20),
Name
CHAR(30),
Login
CHAR(20),
Age
INTEGER,
Average REAL)
2008-2009
DATABASE
H. TOUT
9
Data types (the standard SQL92)
Text types:
CHARACTER (or CHAR) : fixed length text (CHAR(9))
 CHARACTER VARYING (or VARCHAR or CHAR
VARYING) : text with fixed maximal length.
2 octets (EBCDIC ou ASCII).
 NATIONAL CHARACTER (or NCHAR or NATIONAL
CHAR) : fixed length text
 NATIONAL CHARACTER VARYING (or NCHAR
VARYING or NATIONAL CHAR VARYING) : text with
fixed maximal length.
4 octets (UNICODE).

2008-2009
DATABASE
H. TOUT
10
Numeric types:






NUMERIC (or DECIMAL or DEC) : PI in
DECIMAL(16,4) = 3.1416
INTEGER (or INT): long integer
SMALLINT : small integer
FLOAT , REAL, DOUBLE PRECISION
BIT : chain of bits of fixed length
BIT VARYING : chain of bits of fixed
maximal length.
2008-2009
DATABASE
H. TOUT
11
Temporal types



DATE : date of the format AAAA-MM-DD
TIME : 24 hours time of the format
hh:mm:ss.nnn (nnn represent the number of
milliseconds)
TIMESTAMP : date and time
Example : 1999-03-26 22:54:28.123
represents 26 march 1999, 22h 54m, 28s 123
milliseconds.
2008-2009
DATABASE
H. TOUT
12
Relation keys





Key (candidate key): minimal subset of the
fields of a relation that uniquely identify
records.
Example: Sid is a key for the table Students
Each table is guaranteed to have (minimum) a
key.
If no subset of the relation fields may form a
key, the set of all fields is a key.
Out of all available candidate keys, a DB
designer identifies one as primary key.
2008-2009
DATABASE
H. TOUT
13
Relation key

A key must satisfy three conditions:
1.
Each record has a value of the key (obligatory);
2.
Distinct records cannot have identical values in
all the fields of a key (unique);
3.
No set of fields with a number of fields < the
number of fields in the key is a unique identifier
for a record.
2008-2009
DATABASE
H. TOUT
14
Composed key
COMMAND DETAILS
Command
Product id
id
C001
C001
C002
C003
C003
C003
P001
P004
P001
P004
P001
P008
Quantity
10
25
45
15
10
15
Is « command id» a key ?
Is « product id» a key ?
Is « Quantity» a key ?
Details (Command id, Product id, Quantity)
2008-2009
DATABASE
H. TOUT
15
Primary Key and unique
constraints in SQL
CREATE TABLE Students (
CREATE TABLE Details(
Sid
CHAR(20),
commandId
CHAR(20),
Name
CHAR(30),
productId
INT,
Login
CHAR(20),
Quantity
DEC(8,1),
Age
INTEGER,
PRIMARY KEY (CommandId,
ProductId)
Average
REAL,
)
PRIMARY KEY (Sid),
UNIQUE (login),
Unique Constraints
UNIQUE (Name, Age)
Primary key constraint
)
2008-2009
DATABASE
H. TOUT
16
Naming a constraint : optional
CREATE TABLE Students (
Sid
CHAR(20),
Name
CHAR(30),
Login
CHAR(20),
Age
INTEGER,
Average REAL,
CONSTRAINT Studentkey PRIMARY KEY (Sid),
UNIQUE (login),
UNIQUE (Name, Age)
)
2008-2009
DATABASE
H. TOUT
17
Foreign Key

Information in some table may be related to information from another
table.
Foreign
Key
 CLIENT ( clientid , Name , Address )



COMMANDS ( commandId, clientid
, Date)
DETAIL S(commandeId , productId , Quantity )
PRODUCT (productid , Name, Price )

clientid in command is a Foreign key that references clientid in the table
client.
A foreign key (FK) always references a primary key (PK) in another table

A foreign key satisfies the following two rules:



The attributes in FK have the same domain(s) as the referenced primary key
Each value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for
some tuple t2 in the current state r2(R2) or is null.
2008-2009
DATABASE
H. TOUT
18
Structure Diagram
CLIENT
clientid
PRODUCT
Name
Address
productId
Name
Price
COMMANDS
commandid
clientid
Date
DETAILS
commandId
2008-2009
DATABASE
H. TOUT
productId
Quantity
19
Foreign Key in SQL
CREATE TABLE Commands(
Optional: PK and FK
commandId
CHAR(20),
Have the same name
clientId
INT,
CDate
DATE,
PRIMARY KEY (commandId),
FOREIGN KEY (clientId) REFERENCES Client (clientId))
CLIENT
clientid
Name
Address
COMMANDS
commandid
2008-2009
clientid
Date
DATABASE
H. TOUT
20
ER to Relational mapping






STEP 1: For each regular (strong) entity type E in the ER schema, create a relation R that includes all the
attributes of E. Choose one of the key attributes of E as primary key for R.
STEP 2: For each weak entity type W in the ER schema with owner entity type E, create a relation R, and
include all attributes of W as attributes of R. In addition, include as foreign key attributes of R the primary
key attribute(s) of the relation(s) that correspond to the owner entity type(s); this takes care of the identifying
relationship type of W. The primary key of R is the combination of the primary key(s) of the owner(s) and the
partial key of the weak entity type W, if any.
STEP 3: For each binary (1,1) (1,1) relationship type R in the ER schema, identify the relations S and T that
correspond to the entity types participating in R. Choose one of the relations—S, say—and include as foreign
key in S the primary key of T.
STEP 4: For each regular binary (1,1) (1,N) relationship type R, identify the relation S that represents the
participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of
the relation T that represents the other entity type participating in R. And include in S any attributes of the
relationship type
STEP 5: For each binary (1,N) (1,N) relationship type R, create a new relation S to represent R. Include as
foreign key attributes in S the primary keys of the relations that represent the participating entity types;
their combination will form the primary key of S. Also include any attributes of the relationship type as
attributes of S.
STEP 7: For each n-ary relationship type R, where n > 2, create a new relation S to represent R. Include as
foreign key attributes in S the primary keys of the relations that represent the participating entity types.
Also include any simple attributes of the n-ary relationship type (or simple components of composite
attributes) as attributes of S. The primary key of S is usually a combination of all the foreign keys that
reference the relations representing the participating entity types.
2008-2009
DataBase
H. TOUT
21
Foreign key constraint
violations: Update
PRODUCTS
Productid
Price
P001
P004
P006
P008
10
25
45
DETAILS
Commandid
Productid
Quantity
C001
C001
C001
C002
C003
C003
C003
P006
P008
P001
P004
P001
P004
P001
P008
P006
12
10
25
45
15
10
12
2008-2009
DATABASE
H. TOUT
1st solution:
stop the update
ON UPDATE NO ACTION
2nd solution:
Automatic Update
ON UPDATE CASCADE
22
Foreign key constraint
violations: Delete
PRODUCTS
Productid
Price
P001
P004
P006
10
25
45
DETAILS
DETAILS
Commandid
Commandid
Productid
Productid
Quantity
Quantity
C001
C001
C001
C001
C001
C002
C002
C003
C003
C003
C003
C003
P006
P001
P001
P004
P004
P001
P001
P004
P004
P001
P001
P006
12
10
10
25
25
45
45
15
15
10
10
12
2008-2009
DATABASE
H. TOUT
1st solution:
stop theaction
ON DELETE NO ACTION
2nd solution:
Automatic deletion
ON DELETE CASCADE
23
Referential integrity in SQL
CREATE TABLE Details(
Commandid
CHAR(20),
Productid
INT,
Quantity
DEC(10,2),
PRIMARY KEY (Commandid, Productid),
FOREIGN KEY (Productid) REFERENCES Produit,
ON DELETE CASCADE,
ON UPDATE NO ACTION,
FOREIGN KEY (Commandid) REFERENCES Commande,
ON DELETE NO ACTION,
ON UPDATE CASCADE
)
2008-2009
DATABASE
H. TOUT
24
Other integrity constraints :
CHECK
CREATE TABLE Students (
Sid
CHAR(20),
Name
CHAR(30),
Comparison
signification
Login
CHAR(20),
operators
=
Equal to
Age
INTEGER,
<>
Different
<
Less than
Average REAL,
>
Greater than
PRIMARY KEY (Sid),
<=
Less than or equal to
>=
Greater than or equal to
UNIQUE (login),
UNIQUE (Name, Age),
CHECK (Average>=0 AND Average<=20)
)
2008-2009
DATABASE
H. TOUT
25
SQL
DATA MANIPULATION
LANGUAGE
2008-2009
26
Data Manipulation
Insertion
(INSERT INTO…VALUES)
 Suppression
(DELETE… FROM… WHERE)
 Update
(UPDATE… SET…WHERE)

2008-2009
DATABASE
H. TOUT
27
Insertion

(INSERT INTO…VALUES)
Optional if values respect the creation order
of the table
INSERT
INTO Students (sid, name, login, age, average)
VALUES (‘334455’, ‘Smith’, ‘smith@eee’, 19, 3.5)

Text between ‘ ’
2008-2009
DATABASE
H. TOUT
28
Suppression

(DELETE… FROM…WHERE)
DELETE
FROM Students E
WHERE E.name=‘smith’



OR E.age<18
You can give a name for a table.
DELETE delete all records respecting the condition in
WHERE clause
The WHERE condition may be composed:
condition AND condition
condition OR condition
2008-2009
DATABASE
H. TOUT
29
Update

(UPDATE… SET…WHERE)
UPDATE Students E
SET E.age=22
WHERE E.name=‘smith’
2008-2009
DATABASE
H. TOUT
30
Update

(UPDATE… SET…WHERE)
UPDATE Etudiants E
SET E.age=E.age+2
WHERE E.age <= 16
2008-2009
DATABASE
H. TOUT
31
Querying language
RELATIONAL ALGEBRA
2008-2009
32
Relational algebra





A basic set of relational model operations constitute
the relational algebra.
These operations enable the user to specify basic
retrieval requests.
The result of a retrieval is a new relation, which may
have been formed from one or more relations.
The algebra operations thus produce new relations,
which can be further manipulated using operations of
the same algebra.
A sequence of relational algebra operations forms a
relational algebra expression, whose result will
also be a relation.
2008-2009
DATABASE
H. TOUT
33
Relational Operators



Selection
Projection
Set operations:





UNION,
INTERSECTION,
SET DIFFERENCE, and
CARTESIAN PRODUCT
Division
2008-2009
DATABASE
H. TOUT
34
Selection and projection
Sid
Name
Login
Age
Average
22000
Pascal
pascal@info
19
42.5
23404
Sami
sami@ge
21
66.0
22010
Saly
saly@ge
18
12.3
33320
Mado
mado@info
18
65.6
age>20Students
Condition
2008-2009
Table’s name
Πname Students
Attribute
DATABASE
H. TOUT
Table’s name
35
Selection and projection
Eid
Nom
Login
Age
Average
22000
Pascal
pascal@info
19
42.5
23404
Sami
sami@ge
21
66.0
22010
Saly
saly@ge
18
12.3
33320
Mado
mado@info
18
65.6
age>20 OR Name=‘Saly’
2008-2009
Students
Πname,login Students
DATABASE
H. TOUT
36
Set Operators



Union
Intersection,
Set Difference,
Tables must be union
compatible: R(A1, A2, . . .,


Cartesian product
2008-2009

An) and S(B1, B2, . . ., Bn) are
said to be union compatible
if :
they have the same degree n,
and
domain(Ai) = domain(Bi) for
1≤i≤n.
DATABASE
H. TOUT
37
Set operators
Students
Sid
Name
Login
Age
22000
Pascal
pascal@info
19
23404
Sami
sami@ge
21
22010
Saly
saly@ge
18
33320
Mado
mado@info
18
Teachers
2008-2009
Union compatible Tables
Tid
Name
Login
Office
1515
Dany
Dany@info
9
1507
Saly
saly@ginfo
2
22010
Saly
saly@ge
18
DATABASE
H. TOUT
38
Students
UNION
Sid
Name
Login
22000
Pascal
pascal@info 19
23404
Sami
sami@ge
22010
Saly
33320
Mado
Teachers
Age
Tid
Name
Login
Office
21
1515
Dany
Dany@info 9
saly@ge
18
1507
Saly
saly@ginfo
2
mado@info
18
22010
Saly
saly@ge
18
Students U Teachers
Sid
Name
Login
Age
22000
Pascal
pascal@info
19
23404
Sami
sami@ge
21
33320
Mado
mado@info
18
22010
Saly
saly@ge
18
1515
Dany
Dany@info
9
1507
Saly
saly@ginfo
2
2008-2009
DATABASE
H. TOUT
Students schema
With no duplication
39
Intersection
Teachers
Tid
Name
Login
1515
Dany
Dany@info 9
1507
Saly
saly@ginfo
2
22010
Saly
saly@ge
18
Students
Students ∩ Teachers
Sid
22010
2008-2009
Name
Saly
Login
saly@ge
Office
Age
18
DATABASE
H. TOUT
Sid
Name
Login
Age
22000
Pascal
pascal@info 19
23404
Sami
sami@ge
21
22010
Saly
saly@ge
18
33320
Mado
mado@info
18
40
Teachers
Difference
Tid
Name
Login
1515
Dany
Dany@info 9
1507
Saly
saly@ginfo
2
22010
Saly
saly@ge
18
Students
Students - Teachers
Eid
Sid
Nom
Name
Login
Age
22010
22000
Saly
Pascal
saly@ge
pascal@info
18
19
23404
Sami
sami@ge
21
33320
Mado
mado@info
18
2008-2009
Office
Sid
Name
Login
22000
Pascal
pascal@info 19
23404
Sami
sami@ge
21
22010
Saly
saly@ge
18
33320
Mado
mado@info
18
Teachers - Students
Tid
Name
Login
1515
Dany
Dany@info 9
1507
Saly
saly@ginfo 2
DATABASE
H. TOUT
Age
Office
41
Cartasian Product
PRODUCTS
DETAILS
Productid
Price
P001
P004
P006
10
25
45
Commandid
Productid
Quantity
C001
C001
C002
C003
P006
P004
P001
P001
12
25
45
10
PRODUCTS X DETAILS
Productid
Price
Commandid
Productid
Quantity
P001
P001
P001
P001
P004
P004
P004
P004
P006
P006
P006
P006
10
10
10
10
25
25
25
25
45
45
45
45
C001
C001
C002
C003
C001
C001
C002
C003
C001
C001
C002
C003
P006
P004
P001
P001
P006
P004
P001
P001
P006
P004
P001
P001
12
25
45
10
12
25
45
10
12
25
45
10
2008-2009
DATABASE
H. TOUT
42
Join: details + price
PRODUCTS
DETAILS
Productid
Price
P001
P004
P006
10
25
45
PRODUCTS X DETAILS
Commandid
Productid
Quantity
C001
C001
C002
C003
P006
P004
P001
P001
12
25
45
10
DETAILS.Productid=PRODUCTS.Productid
DETAILS X PRODUCTS
Productid
Price
Commandid
Productid
Quantity
P001
P001
P001
P001
P004
P004
P004
P004
P006
P006
P006
P006
10
10
10
10
25
25
25
25
45
45
45
45
C001
C001
C002
C003
C001
C001
C002
C003
C001
C001
C002
C003
P006
P004
P001
P001
P006
P004
P001
P001
P006
P004
P001
P001
12
25
45
10
12
25
45
10
12
25
45
10
2008-2009
DATABASE
H. TOUT
43
Join: détails + price
PRODUCTS
DETAILS
Productid
Price
P001
P004
P006
10
25
45
DETAILS
DETAILS
Commandid
Productid
Quantity
C001
C001
C002
C003
P006
P004
P001
P001
12
25
45
10
productid=productid
productid=productid PRODUCTS
Commandid
Commandid
Productid
Quantity
Productid
Price
Price
C002
C002
C003
C003
C001
C001
C001
C001
P001
P001
P004
P006
45
10
25
12
P001
10
P001
10
P004
25
P006
45
10
10
25
45
Note that sometimes a join may be
specified between a relation and itself
2008-2009
DATABASE
H. TOUT
44
Natural join



A further special case of the join operation
R S is an equijoin in which equalities are
specified on all fields having the same name in
R and S. In this case, we can simply omit the
join condition;
The default is that the join condition is a
collection of equalities on all common fields.
R R.sid=S.sidS can simply be denoted as R S
2008-2009
DataBase
H. TOUT
45
Division
the DIVISION operation is applied to two relations A(Z) /B(X), where X  Z
Let Y = Z - X (and hence Z = X  Y);
that is, let Y be the set of attributes of R that are not attributes of S.
The result of DIVISION is a relation T(Y) that includes a tuple t
if tuples tR appear in R with tR[Y] = t, and with tR[X] = tS for every tuple tS in S
A
B
X
Y
Y
x1
y1
y1
x2
y2
y2
x1
y2
A/B= for a tuple t to appear in the result T of
the DIVISION, the values in t must appear in
A in combination with every tuple in B
A/B
X
x1
2008-2009
DATABASE
H. TOUT
46
Division
A
X
B
Y
Z
x1 y1 z1
x2 y1 z2
x1 y2 z2
A/B
Z
z1
X
Y
x1
y1
x2
y1
z2
x1 y1 z2
x2 y1 z1
2008-2009
DATABASE
H. TOUT
47
Example of division

Find the ids of products ordered in all
commands:
Πcommandid,productid DETAILS/ Πcommandid COMMANDS
PRODUCT
CLIENT
clientid
Name
Address
productId
Name
productId
Quantity
Price
COMMANDS
commandid
clientid
Date
DETAILS
commandId
2008-2009
DataBase
H. TOUT
48
Example of division

Find the names of products ordered in
all commands:
ΠName(PRODUCTS (Πcommandid,productid DETAILS/
Πcommandid COMMANDS))
PRODUCT
CLIENT
clientid
Name
Address
productId
Name
productId
Quantity
Price
COMMANDS
commandid
clientid
Date
DETAILS
commandId
2008-2009
DataBase
H. TOUT
49
Example of division

Find the ids of commands in which all
products are ordered:
Πcommandid,productid DETAILS/ Πproductid PRODUCT
PRODUCT
CLIENT
clientid
Name
Address
productId
Name
productId
Quantity
Price
COMMANDS
commandid
clientid
Date
DETAILS
commandId
2008-2009
DataBase
H. TOUT
50
Exercise I
CUSTOMER(Cust#, Cname, City)
ORDER(Order#, Odate, Cust#, Ord_Amt)
ORDER_ITEM(Order#, Item#, Qty)
ITEM(Item#, Unit_price)
SHIPMENT(Order#, Warehouse#, Ship_date)
WAREHOUSE(Warehouse#, City)
Ord_Amt refers to total dollar amount of an order;
Odate is the date the order was placed;
Ship_date is the date an order is shipped from the
warehouse.
Assume that an order can be shipped from several
warehouses.
2008-2009
DataBase
H. TOUT
51
CUSTOMER(Cust#, Cname, City)
ORDER(Order#, Odate, Cust#, Ord_Amt)
ORDER_ITEM(Order#, Item#, Qty)
ITEM(Item#, Unit_price)
SHIPMENT(Order#, Warehouse#, Ship_date)
WAREHOUSE(Warehouse#, City)
Specify the following queries in relational algebra:
 List all customers
 List all Beirut's Warehouses
 List the customers that have at minimum one order
 List the customers that have at minimum 2 orders
 List the orders including more than 4 units of the item number 44
 List the Order# and Ship_date for all orders shipped from Warehouse
number ‘W2’.
 List the Warehouse information from which the Customer named ‘Jose
Lopez’ was supplied his orders. Produce a listing: Order#, Warehouse#.
 List the orders that were not shipped within 30 days of ordering.
 List the Order# for orders that were shipped from all warehouses that
the company has in New York.
2008-2009
DataBase
H. TOUT
52
Exercise II: relational algebra
RESTAURANTS (Rid, Rname, Raddr)
OPENING (Rid*, Day, OpeningHour, Closinghour)
MEALS (Mid, Mname, Place, recommandedWine *, preferredWineColor)
WINES (Wid, Wname, harvestingdate , Region, Color)
SERVMEALS (Rid*, Mid*, Mprice)
SERVWINES (Rid*, Wid*, WPrice)
1. Names and addresses of all restaurants ?
2. Names of all red wines ?
3. Names and addresses of restaurants that sells the meal "Cassoulet" for
no more than 8 euro ?
4. Names, colors and harvesting dates of the wine recommended with
"shrimps" ?
5. Names and harvesting dates of wines of the same color as the wine
recommended with "Foie gras" ?
2008-2009
DataBase
H. TOUT
53
RESTAURANTS (Rid, Rname, Raddr)
OPENING (Rid*, Day, OpeningHour, Closinghour)
Exercise II:
relational algebra
7.
8.
9.
10.
11.
12.
13.
MEALS (Mid, Mname, Place, recommandedWine *, preferredWineColor)
WINES (Wid, Wname, harvestingdate , Region, Color)
SERVMEALS (Rid*, Mid*, Mprice)
SERVWINES (Rid*, Wid*, WPrice)
Names and addresses of restaurants proposing "Pizza" at 4:00 am?
Names of meals which recommended wine is recommended with
"shrimps" ?
Names and addresses of restaurants proposing "Pizza" and "shrimps" ?
Names and addresses of restaurants proposing "shrimps" but didn't
propose the wine recommended with this meal?
Names, colors and harvesting dates of wines that are not recommended
with any meal ?
Names and addresses of restaurants proposing all the wines ?
Names, colors and harvesting dates of wines proposed in all the
restaurants ?
2008-2009
DataBase
H. TOUT
54
RESTAURANTS (Rid, Rname, Raddr)
OPENING (Rid*, Day, OpeningHour, Closinghour)
Question 12
MEALS (Mid, Mname, Place, recommandedWine *, preferredWineColor)
WINES (Wid, Wname, harvestingdate , Region, Color)
SERVMEALS (Rid*, Mid*, Mprice)
SERVWINES (Rid*, Wid*, WPrice)
SERVWINES (Rid*, Wid*, WPrice)
WINES (Wid, Wname, harvestingdate , Region, Color)
Ids of restaurants proposing all the wines
ΠRid (SERVWINES/(ΠWid WINES))
(ΠRid, Wid SERVWINES)/(ΠWid WINES)
Q12
ΠRname,Raddr(RESTAURANTS
WINES))
2008-2009
Rid=Rid
((ΠRid, Wid SERVWINES)/(ΠWid
DataBase
H. TOUT
55
RESTAURANTS (Rid, Rname, Raddr)
OPENING (Rid*, Day, OpeningHour, Closinghour)
Question 13
MEALS (Mid, Mname, Place, recommandedWine *, preferredWineColor)
WINES (Wid, Wname, harvestingdate , Region, Color)
SERVMEALS (Rid*, Mid*, Mprice)
SERVWINES (Rid*, Wid*, WPrice)
Ids of wines proposed in all the restaurants
(Πwid,RidSERVWINES)/ (ΠRidRESTAURANTS)
Names, colors and harvesting dates of wines proposed in all
the restaurants
ΠWname,color,harvestingdate (WINES
(ΠRidRESTAURANTS)))
2008-2009
((Πwid,RidSERVWINES)/
DataBase
H. TOUT
56
SQL: DATA QUERY
LANGUAGE
2008-2009
DataBase
H. TOUT
57
EXAMPLE
Sailors(sid: integer, sname: string,
rating: integer, age: real)
 Boats(bid: integer, bname: string, color:
string)
 Reserves(sid: integer, bid: integer, day:
date)

2008-2009
DataBase
H. TOUT
58
Instance of boats
Instance of Sailors
Instance of Reserves
2008-2009
DataBase
H. TOUT
59
BASIC SQL QUERY
SELECT [ DISTINCT ] select-list
FROM from-list
WHERE qualification



Every query must have a SELECT clause, which
specifies columns to be retained in the result, and
a FROM clause, which specifies a cross-product of
tables.
The optional WHERE clause species selection
conditions on the tables mentioned in the FROM
clause.
2008-2009
DataBase
H. TOUT
60
Projection
Find the names and ages of all sailors.
Instance of Sailors
Πsname, age Sailors
SELECT sname, age
FROM Sailors
SELECT Sailors.sname,
S.sname, S.age Sailors.age
FROM Sailors S
2008-2009
DATABASE
H. TOUT
61
Use of DISTINCT
Instance of Sailors
SELECT S.sname,
DISTINCT
S.age
S.sname, S.age
FROM Sailors S
2008-2009
DATABASE
H. TOUT
62
Selection
Find all sailors with a rating above 7
Instance of Sailors
SELECT *sid, sname, rating, age
FROM Sailors
WHERE rating >7
2008-2009
DATABASE
H. TOUT
63
Query with Selection and
Projection Find all sailors with a rating above 7
SELECT sname
FROM Sailors
WHERE rating >7
2008-2009
DataBase
H. TOUT
64
Query with join, Selection and
the names of sailors who have reserved
Projection Find
boat number 103
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT S. sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
2008-2009
DataBase
H. TOUT
65
Query with join, Selection
and Projection
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the sid of sailors who have reserved
red boats
SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
2008-2009
DataBase
H. TOUT
66
Query with join, Selection and
the names of sailors who have reserved
Projection Find
red boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND B.color=‘red’ AND R.sid=S.sid
2008-2009
DataBase
H. TOUT
67
UNION, INTERSECT, EXCEPT
SET OPERATORS
2008-2009
68
Condition of use set operators
Tables must be union compatible: R(A1, A2,
. . ., An) and S(B1, B2, . . ., Bn) are said to
be union compatible if :
 they have the same degree n, and
 domain(Ai) = domain(Bi) for 1≤i≤n.
2008-2009
DataBase
H. TOUT
69
UNION
Find the names of sailors who have reserved
red or green boat
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND (B.color=‘red’ OR
B.color=‘green’)
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND B.color=‘red’
UNION
SELECT sname
FROM Reserves R2, Boats B2, Sailors S2
WHERE B2.bid=R2.bid AND R2.sid=S2.sid AND B2.color=‘green’
2008-2009
DataBase
H. TOUT
70
Query with UNION
Find the names of sailors who have reserved
red boat or having rating >7
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND B.color=‘red’
UNION
SELECT sname
FROM Sailors S2
WHERE S2.rating > 7
2008-2009
DataBase
H. TOUT
71
Query with INTERSECT
Find the names of sailors who have reserved
red and green boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT sname
FROM Reserves R1, Reserves R2, Boats B1, Boats B2, Sailors S
WHERE B1.bid=R1.bid AND R1.sid=S.sid AND B1.color=‘red’ AND
B2.bid=R2.bid AND R2.sid=S.sid AND B2.color=‘green’
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND B.color=‘red’
INTERSECT
SELECT sname
FROM Reserves R2, Boats B2, Sailors S2
WHERE B2.bid=R2.bid AND R2.sid=S2.sid AND B2.color=‘green’
2008-2009
DataBase
H. TOUT
72
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND
B.color=‘red’
INTERSECT
SELECT sname
FROM Reserves R2, Boats B2, Sailors S2
WHERE B2.bid=R2.bid AND R2.sid=S2.sid AND
B2.color=‘green’
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
sname
22
222
2007/02/28
Paris
2008-2009
DataBase
H. TOUT
73
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
22
222
2007/02/28
2008-2009
SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
INTERSECT
SELECT sid
FROM Reserves R2, Boats B2
WHERE B2.bid=R2.bid AND B2.color=‘green’
sid
Find the sids of sailors who have reserved
red and green boats
DataBase
H. TOUT
74
Nested
Queries
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
22
222
2007/02/28
2008-2009
SELECT sname
FROM Sailors S
WHERE S.sid =
(SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
INTERSECT
SELECT sid
FROM Reserves R2, Boats B2
WHERE B2.bid=R2.bid AND B2.color=‘green’)
sname
Find the snames of sailors who have reserved
red and green boats
DataBase
H. TOUT
75
Query with EXCEPT (MINUS)
Find the names of sailors who have reserved
red boats but not green boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND B.color=‘red’
EXCEPT
SELECT sname
FROM Reserves R2, Boats B2, Sailors S2
WHERE B2.bid=R2.bid AND R2.sid=S2.sid AND B2.color=‘green’
2008-2009
DataBase
H. TOUT
76
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
22
222
2007/02/28
2008-2009
SELECT sname
FROM Reserves R, Boats B, Sailors S
WHERE B.bid=R.bid AND R.sid=S.sid AND
B.color=‘red’
EXCEPT
SELECT sname
FROM Reserves R2, Boats B2, Sailors S2
WHERE B2.bid=R2.bid AND R2.sid=S2.sid AND
B2.color=‘green’
sname
DataBase
H. TOUT
77
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
EXCEPT
SELECT sid
FROM Reserves R2, Boats B2
WHERE B2.bid=R2.bid AND B2.color=‘green’
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
sid
22
222
2007/02/28
11
2008-2009
Find the sids of sailors who have reserved
red boats but not green boats
DataBase
H. TOUT
78
Nested
Queries
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
SELECT sname
FROM Sailors S
WHERE S.sid =
(SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
EXCEPT
SELECT sid
FROM Reserves R2, Boats B2
WHERE B2.bid=R2.bid AND B2.color=‘green’)
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
sname
22
222
2007/02/28
Paris
2008-2009
Find the snames of sailors who have reserved
red boats but not green boats
DataBase
H. TOUT
79
Nested
Queries
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sid
sname
rating
age
11
Paris
5
25
22
Paris
3
34
33
Bred
4
25
bid
bname
color
111
Basel
red
222
Soccer
green
sid
bid
day
11
111
2005/01/01
22
222
2007/02/28
33
111
2006/02/03
2008-2009
SELECT sname
FROM Sailors S
WHERE S.sid =
(SELECT sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’
EXCEPT
SELECT sid
FROM Reserves R2, Boats B2
WHERE B2.bid=R2.bid AND B2.color=‘green’)
Find the snames of sailors who have
reserved red boats but not green boats
DataBase
H. TOUT
80
Nested queries
Find the names of sailors who have not reserved
red boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN
(SELECT R.sid
FROM Reserves R, Boats B
WHERE B.bid=R.bid AND B.color=‘red’)
2008-2009
DataBase
H. TOUT
81
EXISTS and NOT EXISTS

The EXISTS function in SQL is used to
check whether the result of a correlated
nested query is empty (contains no
tuples) or not.
2008-2009
DataBase
H. TOUT
82
Example
Find the names of sailors who have reserved
the boat 103
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT S.sname
FROM Sailors S
WHERE EXISTS
( SELECT R.sid
FROM Reserves R
WHERE R.sid=S.sid AND R.bid=103)
2008-2009
DataBase
H. TOUT
83
Division
Find the names of sailors who have reserved
all boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
S.snameReserves(sid: integer, bid: integer, day: date)
SELECT
FROM Sailors S
WHERE NOT EXISTS
(
SELECT B.bid
FROM Boats
WHERE B.bid NOT IN
(
SELECT R.bid
FROM Reserves R
WHERE R.sid = S.SID ))
2008-2009
DataBase
H. TOUT
84
Another Solution
Find the names of sailors who have reserved
all boats
SELECT S.sname Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
FROM Sailors S Reserves(sid: integer, bid: integer, day: date)
WHERE NOT EXISTS
(( SELECT B.bid
FROM Boats)
EXCEPT
(
SELECT R.bid
FROM Reserves R
WHERE R.sid = S.SID ))
2008-2009
DataBase
H. TOUT
85
Solution 3 Find the names of sailors who have reserved
all boats
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
(
SELECT B.bid
FROM Boats
WHERE NOT EXISTS
(
SELECT R.bid
FROM Reserves R
WHERE R.sid = S.SID AND R.bid=B.bid))
2008-2009
DataBase
H. TOUT
86
Aggregate operators
SUM ([DISTINCT] A)
 COUNT ([DISTINCT] A)
 AVG ([DISTINCT] A)
 MIN (A)
 MAX (A)

2008-2009
DataBase
H. TOUT
87
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the average age of all sailors
SELECT AVG(age)
FROM Sailors
Find the maximum age of sailors with rating >4
SELECT MAX(age)
FROM Sailors
WHERE rating>4
Count the number of sailors with rating >4
* )
SELECT COUNT( sid
FROM Sailors
WHERE rating>4
2008-2009
DataBase
H. TOUT
88
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Count the number of red boats
SELECT COUNT(*)
FROM Boats
WHERE color=‘red’
Find the first reservation date
SELECT MIN(day)
FROM Reserves
How many color of boats exist in the Database?
SELECT COUNT( DISTINCT color )
FROM Boats
2008-2009
DataBase
H. TOUT
89
Example
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the oldest sailor
SELECT sname, MAX(age)
FROM Sailors
SELECT S.sname
FROM Sailors S
WHERE S.age =
(SELECT MAX(S1.age)
FROM Sailors S1)
2008-2009
SELECT S.sname
FROM Sailors S
WHERE S.age IN
(SELECT MAX(S1.age)
FROM Sailors S1)
DataBase
H. TOUT
90
GROUP BY and HAVING
Thus far, we have applied aggregate
operations to all (qualifying) rows in a
relation.
 Often we want to apply aggregate
operations to each of a number of
groups of rows in a relation.

2008-2009
DataBase
H. TOUT
91
GROUP BY and HAVING
SELECT [ DISTINCT ] select-list
FROM from-list
WHERE qualication
GROUP BY grouping-list
HAVING group-qualication
2008-2009
DataBase
H. TOUT
92
EXAMPLE
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the age of the youngest sailor for each rating level
SELECT rating, MIN(age)
FROM sailor
GROUP BY rating
2008-2009
DataBase
H. TOUT
93
EXAMPLE
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the age of the youngest sailor for each rating level with at least 3
Sailors
SELECT rating, MIN(age)
FROM sailor
GROUP BY rating
HAVING COUNT(*)>=3
2008-2009
DataBase
H. TOUT
94
EXAMPLE
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the age of the youngest sailor for each rating level with no
under age sailors.
SELECT rating, MIN(age)
FROM sailor
GROUP BY rating
HAVING MIN(age)>17
2008-2009
DataBase
H. TOUT
95
EXAMPLE
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Find the age of the youngest sailor who is eligible to vote
for each rating level with at least 3 sailors
SELECT S.rating, Min (S.age)
FROM Sailor S
WHERE S.age >=21
GROUP BY S.rating
HAVING COUNT(*)>=3
2008-2009
DataBase
H. TOUT
96
DATA DEPENDENCIES
AND NORMAL FORMS
2008-2009
97
Problems Caused by
Redundancy
Storing the same information redundantly, that is, in more than
one place within a database, can lead to several problems:




Redundant storage: Some information is stored
repeatedly.
Update anomalies: If one copy of such repeated data is
updated, an inconsistency is created unless all copies are
similarly updated.
Insertion anomalies: It may not be possible to store some
information unless some other information is stored as well.
Deletion anomalies: It may not be possible to delete some
information without losing some other information as well.
2008-2009
DataBase
H. TOUT
98
Example of redundancy
The salary is determined by the rating
Employee
SSN
Ename
Rating
Salary
112
Fadi
3
2000 $
113
Lamia
43
2600 $
114
Fares
3
2000 $
115
Inas
3
2000 $
116
117
Hana
Salim
54
3500
$
??????
Update anomalies
2008-2009
Deletion anomalies
Insertion anomalies
What salary
correspond to the
rating 5?
What salary
correspond to the
rating 4?
DataBase
H. TOUT
99
Solution: Decomposition
Employee
Salaries
SSN
Ename
Rating
Salary
Rating
Salary
112
Fadi
3
20001 $
1400 $
113
Lamia
4
26002 $
1650 $
114
Fares
3
20003 $
2000 $
115
Inas
3
20004 $
2600 $
116
Hana
5
35005 $
3210 $
2008-2009
DataBase
H. TOUT
6
3500 $
7
4000 $
100
FUNCTIONAL
DEPENDENCIES
A set of attributes X determines a set Y
XY
If end only if:
to each value of X correspond one value
of Y
We say : X functionally determines Y, or
X determines Y
2008-2009
DataBase
H. TOUT
101
Examples of dependencies
SSN  Ename
Ename  SSN
Sid, Cid  grade
Classroom, StartHour, Day  Cid,
EndHour
2008-2009
DataBase
H. TOUT
102
Closure of a Set of FDs


The set of all FDs implied by a given set F of FDs is
called the closure of F and is denoted as F+.
To obtain the closure of F, The following three
rules,called Armstrong's Axioms, can be applied
repeatedly:




Reflexivity: If X  Y, then X Y.
Augmentation: If X Y, then XZ YZ for any Z.
Transitivity: If X Y and Y Z, then X Z.
Additional rules :


Union: If X Y and X Z, then X YZ.
Decomposition: If X YZ, then X Y and X  Z.
2008-2009
DataBase
H. TOUT
103
Closure of a Set of FDs:
example



Contracts(contractid, supplierid, projectid,
deptid, partid, qty, value)
We denote the schema for Contracts as
CSJDPQV:
The contract with contractid C is an agreement
that supplier S (supplierid) will supply Q items
of part P (partid) to project J (projectid)
associated with department D (deptid); the
value V of this contract is equal to value.
2008-2009
DataBase
H. TOUT
104
Closure of a Set of FDs:
example

1.
2.
3.
Contracts(contractid, supplierid, projectid, deptid,
partid, qty, value)
C  CSJDPQV
A project purchases a given part using a single
contract JP  C.
A department purchases at most one part from a
supplier SD  P.
2008-2009
DataBase
H. TOUT
105
Some
Inferred FDs





1. C  CSJDPQV
2. JP  C.
3. SD  P.
From JP  C, C  CSJDPQV and transitivity, we
infer JP  CSJDPQV.
From SD P and augmentation, we infer SDJ  JP.
From SDJ JP, JP  CSJDPQV and transitivity, we
infer SDJ  CSJDPQV.
From C  CSJDPQV, using decomposition we can
infer: C  C, C  S, C J, C  D, etc.
Finally, we have a number of trivial FDs from the
reflexivity rule.
2008-2009
DataBase
H. TOUT
106
Normal Forms
To decide whether a relation schema is a
good design or whether we need to
decompose it into smaller relations,
several normal forms have been
proposed:




1st normal form (1FN)
2nd normal form (2FN)
3rd normal form (3FN)
Boyce-Codd normal form (BCNF)
2008-2009
DataBase
H. TOUT
107
Normal Forms


A relation R is in 1NF if every field contains
only atomic values, that is, not lists or sets.
A relation R is in 2NF if:



It is in the1NF and
there is no a part of key that determines a non-key
attribute.
A relation R is in 3NF if:


It is in the 2NF and
there is no a non-key that determines a non-key
attribute.
2008-2009
DataBase
H. TOUT
108
Examples
Not in 2NF
Invoices-Details
InvoiceId
Invoices
InvoiceId
2008-2009
ItemId
ItemName
Quantity
Unit-price
In 2NF but not in 3NF
CustomerId
CustomerName
DataBase
H. TOUT
109
Decomposition into 3NF
To decompose a relation R into 3NF:
1. Find the minimal cover G of the R’s FDs.
2. Find the keys of R.
3. Decompose R by:
a)
b)
2008-2009
Creating a relation for each determinant in G with
this determinant as key and all the attributes it
determines as attributes.
If no relation in the schema contains a key of R,
create a relation that contains the attributes of
this key… all compose the key of this relation
DataBase
H. TOUT
110
Minimal cover

A set of functional dependencies F is minimal
if it satisfies the following conditions:



Every dependency in F has a single attribute for its
right-hand side.
We cannot replace any dependency X A in F with
a dependency Y A, where Y is a subset of X, and
still have a set of dependencies that is equivalent to
F.
We cannot remove any dependency from F and still
have a set of dependencies that is equivalent to F.
2008-2009
DataBase
H. TOUT
111
Finding a minimal cover G for
F
1.
2.
3.
4.
Set G := F.
Replace each functional dependency X 
{A1,A2,...An} in G by the n functional dependencies
XA1, XA2, . . ., XAn. Transform G into standard Form
For each functional dependency X A in G
Minimize the left-sides
for each attribute B that is an element of X
if ((G - {X A}) D {(X - {B}) A}) is equivalent to G,
then replace X A with (X - {B})A in G.
For each remaining functional dependency X A in G
if (G - {X A}) is equivalent to G,
then remove X A from G. Eliminate redundant dependencies
2008-2009
DataBase
H. TOUT
112
Finding the keys of R
1.
2.
3.
Each attribute not in the right-side of
the R’s FDs is a part of all R’s keys
If the set of these attributes determines
all attributes of R, this set is the key of
R
Else, add successively attributes to this
set to find the keys of R.
2008-2009
DataBase
H. TOUT
113