Tutorial for CSC343 Introduction to Databases

1
Tutorial for CSC343Introduction to Databases

• Fall 2006
• Week 2

2
T.A. and Tutorials

• Yuan An
• yuana_at_cs.toronto.edu
• 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/thirdEdi
  tion/supporting_material.htm

3
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.

4
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
  .

5
Exercise

• Superkeys
• Candidate keys
• Primary key

6
Exercise

• Superkeys
• Candidate keys
• Primary key

7
Exercise

• Superkeys
• Candidate keys
• Primary key

8
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
  .

9
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.

10
Exercise
11
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 )

12
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))

13
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))

14
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)

15
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?

16
Exercise

• A1 CREATE TABLE
• (
  ,
• 
  ,
• 
  ,
• 
  ,
• 
  ,
• 
  
• )

17
Exercise

• 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 .

18
Exercise

• Q2. Write the SQL statements required to create
  the preceding relations, including appropriate
  versions of all primary and foreign key integrity
  constraints.