Title: INFS 1200 7900 Tutorial 3
1INFS 1200 / 7900Tutorial 3
2Website for Tutorial Materials
- http//itee.uq.edu.au/ruopeng/infs.htm
3Suggested Study Plan for Week 5
- Review lecture notes on Relational Model
- Check solution of Tutorial 2 exercises
- Attempt tutorial exercises and go there (Tutorial
3, Relational Model) and bring along your handout
with you - Do MS-Access practicals either in the lab or at
home (Chapter 5 - 6) - Preview lecture notes on Functional Dependencies
4Database Schema and Instance
- Relational Database Schema
- Set of relational schema R1, R2,,Rn
- Set of integrity constraints
- Relational Database Instance
- Set of relation instances r1, r2,,rn such that
each Ri is an instance of ri - The ri relation satisfy the integrity constraints
- Relational Database Schema Instances
5Relations
Attribute
Movie
Title
Movie No
Year
RunTime
Walk the Line
32118752
2006
123
Cry Wolf
42887653
2006
115
6Domains
- A Domain D is a set of atomic values
- An atomic value is indivisible (as far as the
relational data model is concerned) - Each domain has a data type or format
- Examples
- Domain of Names
- Will Smith, Nicole Kidman, Zhang Ziyi , Tom
Cruise , ... - Domain of Movie Types
- Horror, Romance, Comedy, Thriller, Fantasy,
7Attributes
- Each attribute A is the name of a role played by
some domain D in the relation named R - The number of attributes in a relation R is
called the degree of R
Name
Phone No
Sex
Pay in USD in millions
Will Smith
1 302 82023
M
25
Zhang Ziyi
86 128 256
F
2
8Tuples
- Each Tuple t is an ordered list of n values
- t
- where each value vi (1 ? i ? n) is an element of
the corresponding domain of attribute Ai or a
special value called null - t is called an n-tuple
- Example
- (254, Jolie, Angelina, 18M, 1 321 786,F)
9Constraints
- Restrictions on data that can be specified on a
relational database schema - Structural, Static, Single Relation
- Domain Constraints
- Key Constraints
- Entity Integrity Constraints
- Structural, Static, Multiple Relations
- Referential Integrity Constraints
10Key Constraints
- All tuples in a relation must be distinct, that
is no two tuples can have same values for all
attributes ?uniqueness constraint
Address
Age
Name
B Pitt
23 Milton Rd
44
Violation of Uniqueness Constraint ?
B Pitt
23 Milton Rd
44
11Super Key
- A Superkey is a subset of attributes (SK) of a
relation schema R, such that for any two tuples,
ti and tj in a relation state r of R - ti SK ? tjSK
- Every relation has at least one superkey - the
set of all its attributes - Superkey can have redundant attributes, that is,
by removing some attributes, the uniqueness
constraint is still maintained
12Superkey Example
- Superkey for the Relation IPOD
- (SongID, Title, Album, Artist, Composer , Genre,
Duration)
Which of these attributes are redundant ?
Is (SongID) the smallest set of attributes that
uniquely identify a tuple in the relation IPOD ?
IPOD
13Notion of Key
- K is a key in a relation schema R iff
- K is a Superkey of R, and
- removing any attribute from K leaves a set of
attributes K', where K' is not a superkey of R,
that is, K does NOT maintain the uniqueness
constraint - Key is a minimal Superkey
- smallest set of attributes that uniquely identify
a tuple
14Example Key
- Possible Keys for the Relation WORKS-ON
- (DEPT)
- (EMPNO)
- (DEPT, EMPNO)
- (DEPT, EMPNO, DATE, HOURS)
- (EMPNO, DATE)
Not a Superkey, many employees work in the same
department
Not a Superkey, the same employee of the same
department will work on several days
Not a Superkey, one employee will work several
times
Superkey, but not a key since removing Hours,
still maintains uniqueness
Correct Key for relation WORKS-IN
WORKS-ON
DEPT
EMPNO
DATE
HOURS
Printing
606
1/1/99
3
Printing
607
1/1/99
6
Printing
606
2/1/99
9
Sales
319
1/1/99
6
Sales
321
1/1/99
8
15Tutorial Question 3
Suppose each of the following update operations
is applied directly to the database of Figure
7.7. Discuss all integrity constraints violated
by each operation, if any, and the different ways
of enforcing these constraints.
16Tutorial Question 3Figure 7.7
17Tutorial Question 3Figure 7.6
18Tutorial Question 3a
- Insert '21-JUN-42', '2365 Newcastle Rd,Bellaire, TX', M,
58000, '888665555', 1 into EMPLOYEE.
19Tutorial Solution 3a
- No constraint violations.
20Tutorial Question 3b
21Tutorial Solution 3b
- Violates referential integrity because DNUM2 and
there is no tuple in the DEPARTMENT - relation with DNUMBER2.
- We may enforce the constraint by
- rejecting the insertion of the new PROJECT tuple,
- (changing the value of DNUM in the new PROJECT
tuple to an existing DNUMBER value in the
DEPARTMENT relation, or - inserting a new DEPARTMENT tuple with DNUMBER2.
22Tutorial Question 3c
- Insert '01-OCT-88' into DEPARTMENT.
23Tutorial Solution 3c
- Violates both the key constraint and referential
integrity. Violates the key constraint - because there already exists a DEPARTMENT tuple
with DNUMBER4. We may enforce this constraint
by - rejecting the insertion, or
- (ii) changing the value of DNUMBER in the new
DEPARTMENT tuple to a value that does not violate
the key constraint. - Violates referential integrity because
MGRSSN'943775543' and there is no tuple in the
EMPLOYEE relation with SSN'943775543'. We may
enforce the constraint by - rejecting the insertion,
- changing the value of MGRSSN to an existing SSN
value in EMPLOYEE, or - inserting a new EMPLOYEE tuple with
SSN'943775543'.
24Tutorial Question 3d
25Tutorial Solution 3d
- Violates both the entity integrity and
referential integrity. Violates entity integrity
because PNO, which is part of the primary key of
WORKS_ON, is null. - We may enforce this constraint by
- rejecting the insertion, or
- changing the value of PNO in the new WORKS_ON
tuple to a value of PNUMBER that exists in the
PROJECT relation. - Violates referential integrity because
ESSN'677678989' and there is no tuple in the
EMPLOYEE relation with SSN'677678989'. We may
enforce the constraint by - rejecting the insertion,
- changing the value of ESSN to an existing SSN
value in EMPLOYEE, or - inserting a new EMPLOYEE tuple with
SSN'677678989'.
26Tutorial Question 3e
- Insert 'SPOUSE' into DEPENDENT.
27Tutorial Solution 3e
- No constraint violations.
28Tutorial Question 3f
- Delete the WORKS_ON tuples with ESSN
'333445555'.
29Tutorial Solution 3f
- No constraint violations.
30Tutorial Question 3g
- Delete the EMPLOYEE tuple with SSN '987654321'.
31Tutorial Solution 3g
- Violates referential integrity because several
tuples exist in the WORKS_ON, DEPENDENT,
DEPARTMENT, and EMPLOYEE relations that reference
the tuple being deleted from EMPLOYEE. - We may enforce the constraint by
- rejecting the deletion, or
- deleting all tuples in the WORKS_ON, DEPENDENT,
DEPARTMENT, and EMPLOYEE relations whose values
for ESSN, ESSN, MGRSSN, and SUPERSSN,
respectively, is equal to'987654321'.
32Tutorial Question 3h
- Delete the PROJECT tuple with PNAME 'ProductX'.
33Tutorial Solution 3h
- Violates referential integrity because two tuples
exist in the WORKS_ON relations that reference
the tuple being deleted from PROJECT. - We may enforce the constraint by
- rejecting the deletion, or
- deleting the tuples in the WORKS_ON relation
whose value for PNO1 (the value for the primary
key PNUMBER for the tuple being deleted from
PROJECT).
34Tutorial Question 3i
- Modify the MGRSSN and MGRSTARTDATE of the
DEPARTMENT tuple with DNUMBER 5 to - '123456789' and '01-OCT-88', respectively.
35Tutorial Solution 3i
- No constraint violations.
36Tutorial Question 3j
- Modify the SUPERSSN attribute of the EMPLOYEE
tuple with SSN '999887777' to '943775543'.
37Tutorial Solution 3j
- Violates referential integrity because the new
value of SUPERSSN'943775543' and there is no
tuple in the EMPLOYEE relation with
SSN'943775543'. - We may enforce the constraint by
- rejecting the deletion, or
- inserting a new EMPLOYEE tuple with
SSN'943775543'.
38Tutorial Question 3k
- Modify the HOURS attribute of the WORKS_ON tuple
with ESSN '999887777' and PNO 10 to '5.0'.
39Tutorial Solution 3k
- No constraint violations.
- Note however, that if the domain of PNO was given
to be limited to integer values, then this could
be a domain constraint violation.
40Key Concepts
- Relation, Relational Schema
- Keys
- Super Key, Minimal Super Key
- Primary Key, Partial Key
- Foreign Key
- Constraints
- Domain Constraints
- Key Constraints
- Entity Integrity Constraints
- Referential Integrity Constraints
- Update Abnormity