Tutorial for CSC 343 Introduction to Databases Fall

Tutorial for CSC 343 Introduction to Databases Fall 2006 Week 2 CSC 343: Intro. to Databases 1

T. A. and Tutorials • Yuan An • yuana@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/open. Access/third. Edit ion/supporting_material. htm CSC 343: 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. CSC 343: 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. CSC 343: Intro. to Databases 4

Exercise • Superkeys: • Candidate keys: • Primary key: CSC 343: Intro. to Databases 5

Exercise • Superkeys: • Candidate keys: • Primary key: CSC 343: Intro. to Databases 6

Exercise • Superkeys: • Candidate keys: • Primary key: CSC 343: 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 R 1 must appear as values for of. CSC 343: 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. CSC 343: Intro. to Databases 9

Exercise CSC 343: 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 Table. Name ( Attribute. Name Domain [ Default. Value ] [ Constraints ] {, Attribute. Name Domain [ Default. Value ] [ Constraints ] } [ Other. Constraints ] ) CSC 343: Intro. to Databases 11

Example of create table Employee ( Reg. No character(6) primary key, First. Name character(20) not null, Surname character(20) not null, Dept character (15) references Department(Dept. Name) on delete set null on update cascade, Salary numeric(9) default 0, City character(15), unique(Surname, First. Name) ) CSC 343: Intro. to Databases 12

Example create table Employee ( Reg. No char(6), First. Name char(20) not null, Surname char(20) not null, Dept char(15), Salary numeric(9) default 0, City char(15), primary key(Reg. No), foreign key(Dept) references Department(Dept. Name) on delete set null on update cascade, unique(First. Name, Surname) ) CSC 343: 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) CSC 343: Intro. to Databases 14

Exercise • Q 1. 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? CSC 343: Intro. to Databases 15

Exercise A 1: CREATE TABLE ( , , , ) CSC 343: 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. CSC 343: Intro. to Databases 17

Exercise • Q 2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints. CSC 343: Intro. to Databases 18