Transcript Tutorial for CSC343 Introduction to Databases Fall 2006 Week 2

Tutorial for CSC343
Introduction to Databases
Fall 2006
Week 2
CSC343: Intro. to Databases
1
T.A. and Tutorials
• Yuan An
• [email protected]
• Tutorials: Week 2, 3, 4, 5, 7, 9, 10, 11, 12, 13 (10
times).
• Week 6: Postgres SQL by Kiran Gollu.
• Week 8: Midterm (no tutorial).
• Some even-numbered questions.
• Answers for odd-numbered questions:
– http://www.cs.wisc.edu/~dbbook/openAccess/thirdEdit
ion/supporting_material.htm
CSC343: Intro. to Databases
2
Exercise
• E. 3.2 How many distinct tuples are in a
relation instance with cardinality 22?
• Answer: Since a relation is formally
defined as
, if the
cardinality is 22 (i.e., there are
there must be
),
distinct tuples.
CSC343: Intro. to Databases
3
Exercise
• E. 3.4 What is the difference between a
candidate key and the primary key for a
given relation? What is a superkey?
Answer: The primary key is the
selected
by the DBA from among the
. A candidate key
. A superkey is
that
.
CSC343: Intro. to Databases
4
Exercise
RegNum
284328
296328
587614
934856
965536
Surname
Smith
Smith
Smith
Black
Black
FirstName
Luigi
John
Lucy
Lucy
Lucy
BirthDate
29/04/59
29/04/59
01/05/61
01/05/61
05/03/58
DegreeProg
Computing
Computing
Engineering
Fine Art
Fine Art
• Superkeys:
• Candidate keys:
• Primary key:
CSC343: Intro. to Databases
5
Exercise
RegNum
NULL
587614
934856
NULL
Surname
Smith
Smith
Black
Black
FirstName
John
Lucy
Lucy
Lucy
BirthDate
01/05/61
DegreeProg
Computing
Engineering
NULL
NULL
05/03/58
Engineering
NULL
• Superkeys:
• Candidate keys:
• Primary key:
CSC343: Intro. to Databases
6
Exercise
RegNum
643976
587614
934856
735591
Surname
Smith
Smith
Black
Black
FirstName
John
Lucy
Lucy
Lucy
BirthDate
01/05/61
DegreeProg
Computing
Engineering
NULL
NULL
05/03/58
Engineering
NULL
• Superkeys:
• Candidate keys:
• Primary key:
CSC343: Intro. to Databases
7
Exercise
• E. 3.6 What is a foreign key constraint?
Why are such constraints important?
What is referential integrity?
Answer: A foreign key constraint requires
that the values on a set X of attributes of a
relation R1 must appear as values for
of
.
CSC343: Intro. to Databases
8
Exercise
E. 3.6 Answer: Foreign key constraints are
important because they provide safeguards
for insuring the
of data.
Referential integrity means all
are enforced.
CSC343: Intro. to Databases
9
Exercise
Offences
Code
143256
987554
987557
630876
539856
Officers
RegNum
567
456
638
Cars
Registration
6544 XY
7122 HT
5694 FR
6544 XY
Date
Officer Dept
75
25/10/1992 567
75
26/10/1992 456
75
26/10/1992 456
47
15/10/1992 456
47
12/10/1992 567
Surname
Brun
Larue
Larue
Dept
75
75
75
47
Registration
5694 FR
5694 FR
6544 XY
6544 XY
6544 XY
FirstName
Jean
Henri
Jacques
Owner
Cordon Edouard
Cordon Edouard
Latour Hortense
Mimault Bernard
CSC343: Intro. to Databases
…
…
…
…
…
10
Table Definition
• An SQL table consists of an ordered set of
attributes, and a (possibly empty) set of
constraints
• Statement create table defines a relation
schema, creating an empty instance.
• Syntax:
create table TableName
( AttributeName Domain [ DefaultValue ]
[ Constraints ]
{, AttributeName Domain [ DefaultValue ]
[ Constraints ] }
[ OtherConstraints ] )
CSC343: Intro. to Databases
11
Example of create table
create table Employee
(
RegNo
character(6) primary key,
FirstName character(20) not null,
Surname
character(20) not null,
Dept
character (15)
references Department(DeptName)
on delete set null
on update cascade,
Salary
numeric(9) default 0,
City
character(15),
unique(Surname,FirstName)
)
CSC343: Intro. to Databases
12
Example
create table Employee
(
RegNo char(6),
FirstName char(20) not null,
Surname char(20) not null,
Dept char(15),
Salary numeric(9) default 0,
City char(15),
primary key(RegNo),
foreign key(Dept)
references Department(DeptName)
on delete set null
on update cascade,
unique(FirstName,Surname)
)
CSC343: Intro. to Databases
13
Exercise
• E. 3.8 Answer each of the following
questions briefly. The questions are based
on the following relational schema:
– Emp (eid: integer, ename: string, age: integer,
salary: real)
– Works (eid: integer, did: integer, pcttime:
integer)
– Dept (did: integer, dname: string, budget: real,
managerid: integer)
CSC343: Intro. to Databases
14
Exercise
• Q1. Give an example of a foreign key
constraint that involves the Dept relation.
What are the options for enforcing this
constraint when a user attempts to delete
a Dept tuple?
CSC343: Intro. to Databases
15
Exercise
A1: CREATE TABLE
(
,
,
,
,
,
)
CSC343: Intro. to Databases
16
Exercise
•
1.
2.
3.
4.
When a user attempts to delete a Dept tuple,
there are four options:
Also delete all
tuples that refer to it.
Disallow the
of the Dept tuple if some
tuple refers to it.
For every
tuple that refers to it, set the
did field to the did of some
.
For every
tuple that refers to it, set the
did field to
.
CSC343: Intro. to Databases
17
Exercise
• Q2. Write the SQL statements required to
create the preceding relations, including
appropriate versions of all primary and
foreign key integrity constraints.
CSC343: Intro. to Databases
18