INFS 1200 7900 Tutorial 3 - PowerPoint PPT Presentation

1 / 39
About This Presentation
Title:

INFS 1200 7900 Tutorial 3

Description:

Review lecture notes on Relational Model. Check solution of Tutorial 2 exercises ... (254, Jolie, Angelina, $18M, 1 321 786,F) Constraints ... – PowerPoint PPT presentation

Number of Views:132
Avg rating:3.0/5.0
Slides: 40
Provided by: ITEE
Category:

less

Transcript and Presenter's Notes

Title: INFS 1200 7900 Tutorial 3


1
INFS 1200 / 7900Tutorial 3
  • Relational Model

2
Website for Tutorial Materials
  • http//itee.uq.edu.au/ruopeng/infs.htm

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

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

5
Relations
Attribute
Movie
Title
Movie No
Year
RunTime
Walk the Line
32118752
2006
123
Cry Wolf
42887653
2006
115
6
Domains
  • 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,

7
Attributes
  • 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
8
Tuples
  • 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)

9
Constraints
  • 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

10
Key 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
11
Super 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

12
Superkey 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
13
Notion 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

14
Example 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
15
Tutorial 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.
16
Tutorial Question 3Figure 7.7
17
Tutorial Question 3Figure 7.6
18
Tutorial Question 3a
  • Insert '21-JUN-42', '2365 Newcastle Rd,Bellaire, TX', M,
    58000, '888665555', 1 into EMPLOYEE.

19
Tutorial Solution 3a
  • No constraint violations.

20
Tutorial Question 3b
  • Insert into
    PROJECT.

21
Tutorial 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.

22
Tutorial Question 3c
  • Insert '01-OCT-88' into DEPARTMENT.

23
Tutorial 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'.

24
Tutorial Question 3d
  • Insert into
    WORKS_ON.

25
Tutorial 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'.

26
Tutorial Question 3e
  • Insert 'SPOUSE' into DEPENDENT.

27
Tutorial Solution 3e
  • No constraint violations.

28
Tutorial Question 3f
  • Delete the WORKS_ON tuples with ESSN
    '333445555'.

29
Tutorial Solution 3f
  • No constraint violations.

30
Tutorial Question 3g
  • Delete the EMPLOYEE tuple with SSN '987654321'.

31
Tutorial 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'.

32
Tutorial Question 3h
  • Delete the PROJECT tuple with PNAME 'ProductX'.

33
Tutorial 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).

34
Tutorial Question 3i
  • Modify the MGRSSN and MGRSTARTDATE of the
    DEPARTMENT tuple with DNUMBER 5 to
  • '123456789' and '01-OCT-88', respectively.

35
Tutorial Solution 3i
  • No constraint violations.

36
Tutorial Question 3j
  • Modify the SUPERSSN attribute of the EMPLOYEE
    tuple with SSN '999887777' to '943775543'.

37
Tutorial 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'.

38
Tutorial Question 3k
  • Modify the HOURS attribute of the WORKS_ON tuple
    with ESSN '999887777' and PNO 10 to '5.0'.

39
Tutorial 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.

40
Key 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
Write a Comment
User Comments (0)
About PowerShow.com