CS580 Advanced Database Topics

1
CS580Advanced Database Topics

• Chapter 7
• Relational Data Model, Relational Constraints,
  Relational Algebra
• Irena Pevac

2
Key

• SuperKey is any set of attributes that has unique
  value for each tuple and determines unique value
  of all other attributes.
• Key is one or more attributes that uniquely
  identifies one tuple in a relation. It is a
  minimal superkey.
• Candidate key is each of the possible keys.

3
Car example
4
Key Examples

• Possible superkeys
• LicenceNumber, State
• EngineSerialNumber
• EngineSerialNumber, Make
• EngineSerialNumber, Make, year
• LicenceNumber, State, Make
• EngineSerialNumber, State, Make

5
Candidate Keys

• LicenceNumber, State
• EngineSerialNumber
• We remove attributes from the superkey that can
  be removed without affecting the uniqueness
  constraint. Each minimal set of attributes that
  uniquely identifies one tuple is candidate key.

6
Primary Key

• Primary Key is one selected candidate key.
• Designer selects one key from the possible
  candidates using criteria
• Use key with single attribute
• Use key that is easier to use
• LicenceNumber, State pair may be more practical
  to use than EngineSerialNumber

7
STUDENT example

• STUDENT(StudID, ssn, LName, FName, Phone, Age,
  gpa)
• Candidate keys StudID, ssn,
  LName,FNmae,Phone
• StudId is the best to use since it is single
  attribute
• ssn is single attribute but ssn should not be
  used for privacy reasons
• LName,FNmae,Phone not suitable has 3 attributes

8
Car example

• Candidate key
• LicenceNumber, State
• EngineSerialNumber
• Use LicenceNumber, State because it is more
  practical
• Single attribute EngineSerialNumber is not good
  choice because it harder to reach serial number
  on the engine.

9
Course-Section example

• COURSE-SECTION ( CourseCode, SectionNumber,
  Semester, Year, Title, Instructor, Credits)
• CourseCode, SectionNumber, Semester, Year
• has four attributes but is best choice
• Title, Semester, Year
• has three attribute, but exact title is hard to
  remember

10
Null Values

• Some attributes may have null values.
• Primary key attributes must be non-null.
• PERSON relation

11
Company example

• Page 204 Fig 7.6
• Relational Database Schema S is
• set of relation schemas R1,R2,R3,,Rn
• set of integrity constraints
• Relational Database State
• Set of states for all individual relations Ri
  which is current set of tuples for all relations

12
Attribute renaming

• Same attribute may appear in two relations with
  the same name or renamed
• Department Number appears in EMPLOYEE as DNO
• Department Number appears in PROJECT as DNUM

13
Renaming

• Renaming should be done
• ssn for employee used in recursive relationship
• Once ssn is used for supervisor and the other
  time ssn is used for supevisee
• EmpSSN SuperSSN renaming distinguishes the
  two roles

14
ENTITY INTEGRITY CONSTRAINT

• No primary key value should be null.
• This is to ensure that primary key value can be
  used to identify individual tuples in a relation.

15
REFERENTIAL INTEGRITY CONSTRAINT

• REFERENTIAL INTEGRITY CONSTRAINT is specified
  between two relations.
• A tuple in one relation refers to an existing
  tuple in another relation
• Page 205 Fig 7.6
• DNO in EMPLOYEE gives dept number where that
  employee works.
• DNOEMPLOYEE is a subset of DNODEPARTMENT

16
Employee is responsible for - Car
17
Foreign key

• EMPLOYEE( EmpID, FirstName, LastName, Title)
• CAR(CarId, Make, Model, EmpId)
• In EMPLOYEE key is EmpID
• In CAR key is CarID
• In CAR foreign key is EmpID
• Set of values for EmpID in CAR is subset of EmpID
  values in EMPLOYEE.

18
Foreign key

• Foreign key of one tuple refers to tuple in
  another relation that has relationship instance
  with that tuple.
• In recursive relationships foreign key of one
  tuple refers to tuple of the same relation that
  has relationship instance with that tuple.

19
Foreign key

• EMPLOYEE( EmpID, FirstName, LastName, Title,
  CarID)
• CAR(CarId, Make, Model)
• In EMPLOYEE key is EmpID
• In CAR key is CarID
• In EMPLOYEE foreign key is CarID
• Set of values for CarID in EMPLOYEE is subset of
  CarID values in CAR.
• A value of CarID in EMPLOYEE might be null or
  value from domain of CarId in CAR denoted
  CarIDCAR

20
Referential integrity

• The attributes in foreign key have the same
  domain as the primary key attributes Foreign key
  attributes refer to relation in which primary key
  resides.
• A value of FK in a tuple is either the same as
  some value of primary key in that other relation
  or is null.

21
(No Transcript)
22
Semantic Integrity Constraints

• Salary of one employee should not exceed the
  salary of the employees supervisor.
• Max number of hours an employee can work on all
  projects per week is 56.
• Such constraints can be specified in general
  purpose constraint specification language.

23
Triggers

• Triggers and assertions can be used to enforce
  constraints.
• In SQL CREATE ASSESRTION statement is used

24
Transition constraint

• Transition constraint deals with a change of
  state in the database.
• Example
• Salary of one employee can only increase
• Transition constraints are usually specified by
  active rules and triggers.

25
Valid State Constraint

• Valid State Constraint ensures that all
  constraints are enforced that make a valid state
  of the database.
• Key constraint
• Integrity constraint
• Referential Integrity constraint

26
OPERATIONS OF THE RELATIONAL MODEL

• RETRIEVAL
• UPDATES
• Insert
• Delete
• Modify

27
Insert

• Insert provides a list of attribute values for a
  new tuple t that is to be inserted into a
  relation R.
• Insert can violate any of the constraints
  discussed before.

28
Insert - Contraint Violations

• Domain Constraint
• Can be violated if an attribute value does not
  belong to corresponding domain
• EmployeeAge has domain 16, ,70 and value
  appears to be 79.
• Key Constraint
• Key constraint can be violated if a key value in
  a new tuple equals to some key value of an
  existing tuple.

29
Insert - Contraint Violations

• Entity Integrity Constraint
• can be violated if the primary key of the new
  tuple is null
• Referential Integrity Constraint
• Can be violated if the value of a foreign key in
  tuple t refers to a tuple that does not exist in
  the referenced relation.
• An insert operation that violates any of the
  above contraints is rejected.

30
Delete Operation

• The Delete Operation can only violate
  referential integrity constraint if a tuple being
  deleted is referenced by a foreign keys from
  another tuples in the DB.
• EMPLOYEE WORKS-ON

31
Delete that causes Violation

• If delete causes violation
• Reject the deletion
• Do not delete Bill from EMPLOYEE relation
• Attempt to cascade (or propagate) the deletion by
  deleting the tuples that reference the tuple that
  is being deleted.
• Delete also two rows in WORKS-ON that refer to
  Bill
• Modify the referencing attribute values that
  causes violation
• Assign Bills projects to another employee and
  then delete Bill from EMPLOYEE

32
Update OPERATION

• The Update operation is used to change one or
  more attribute values in a tuple.
• Update is
• Delete
• Insert
• So, all rules from delete and insert apply

33
Update - Violations

• Update can violate referential integrity
• If a tuple with foreign key is updated and new
  value of foreign key does not refer to any
  primary key value
• Update can violate domain constraint
• If a tuple has one atrribute value that is not
  from the domain for that attribute

34
RELATIONAL ALGEBRA

• Set of operations to manipulate relations
• Relational algebra Operations
• Set Operations
• Union
• Intersection
• Set Difference
• Cartesian Product
• DB specific Relational Algebra Operations
• Select
• Project
• Join

35
UNION

• Union is applicable to two relations that are
  UNION compatible, which means that both relations
  have corresponding attributes defined on the same
  domain.
• Union of two relations are all rows from the
  first relations plus all rows from the second
  that are not in the first relation.

36
UNION
37
Intersection

• Intersection is applicable to two relations that
  are compatible, which means that both relations
  have corresponding attributes defined on the same
  domain.
• Intersection of the two relations is new relation
  with all rows that appear in both relations.

38
INTERSECTION
39
SET DIFFERENCE

• Set Difference is applicable to two relations
  that are compatible, which means that both
  relations have corresponding attributes defined
  on the same domain.
• Set Difference of the two relations is new
  relation with all rows that appear in the first
  relation but do not appear in the second
  relation.

40
SET DIFFERENCE
41
CARTESIAN PRODUCT

• Cartesian Product or Cross Product combines
  tuples from two relations.
• Each tuple from the first relation is joined with
  each tuple from the second relation.
• R1(a1,a2) R2(b1,b2,b3)
• R1xR2 R(a1,a2,b1,b2,b3)

42
Cartesian Product
43
Select Operator

• The select operation is used to select a subset
  of tuples from a relation that satisfy a
  selection condition.
• Condition can include boolean operators AND, OR,
  NOT applied on relational operators lt, gt lt,gt,
  !,

44
Selection
45
Selection

• EXAMPLE Select all tuples from Student that
  have gpa gt 3.2
• s gpagt3.2 (STUDENT)
• EXAMPLE Select all tuples from Student that
  have gpa gt 3.0 or SID 123
• s gpagt3.0 OR SID 123 (STUDENT)

46
Selection Criteria in Selection Operator

• ltattribute namegt ltrelational operatorgt ltconstant
  valuegt
• age gt 25
• Constant value should be from the domain of the
  attribute on the left side
• ltattribute namegt ltrelational operatorgt ltattribute
  namegt
• S1 AND S2 S1 OR S2 Not S
• S1, S2, S are simple selection criteria

47
Projection Operator

• The project operator is used to select a subset
  of columns from the existing relation.
• The resulting relation has only specified
  attributes.
• It has all the rows from the original relation or
  a subset of them if there is repetition.

48
Projection

• P Name, gpa (STUDENT)
• Name gpa
• Bill Pierce 4.0
• Sue Jones 3.3
• Sue Smith 3.2

49
Join Operator

• Combines related tuples from two relations into
  single tuples.
• STUDENT JOIN PROFESOR
• AdvisorName

50
Join

• In join we take some rows from the first relation
  concatenated by some rows from the second
  relation such that they satisfy the condition
  criteria.
• In R1 x R2 we have all tuples mn where m
  belongs to R1 and N belongs to R2.
• In R1 JOIN R2 we have those tuples mn that
  satisfy the condition.

51
Join types

• Q Theta Join
• Tuples whose join attributes are null do not
  appear in the result
• EQUIJOIN
• Join with condition involving only relational
  operator
• In equijoin one attribute is redundant since its
  value is equal to another attribute
• NATURAL JOIN
• It is equijoin without the redundant attribute
• Natural join requires the same name for the two
  attributes in different tables

52
General Natural Join

• A more general but nonstandard defintion for
  natural join
• R S
• (ltlist1gt), (ltlist2gt)
• ltlist1gt specifies list of n attributes from R
• ltlist2gt specifies list of n attributes from S
• The lists are used to form equality comparison
  between pairs of corresponding attributes. These
  conditions are then AND-ed together. Only the
  list1 attributes are kept in the result.

53
Join

• Result of Join may be an empty relation if no
  tuples satisfy the condition
• The number of tuples in Join ranges from
• 0 to NumTuplesR NumTuplesS

54
Complete Set of Operatons

• Set of relational algebra operations
• s, P, U, -, x is a complete set of operations
  because ny of the other operations can be
  expressed as a sequence of operations from this
  set.
• R JOIN S s cond (R x S)
• cond
• R INTERSECTION S R U S ((R-S)U(S-R))

55
Division Operator

• Requirement Set of attributes in R A,B
  contains set of attributes in S A
• R( A, B) S(A)

56
Division R S

• Result are all x values of attribute B such that
  tuples bellow belong to R(A,B)

57
Aggregate Functions

• Relational Algebra with basic operations does not
  allow requests with aggregate functions on
  collections of values from the database such as
• Get average salary for all employees
• Get average salary for employees where DNO2

58
Numeric Functions

• Functions applied to a collection of numeric
  attribute values include
• Sum
• Average
• Maximum
• Minimum
• Count (count tuples)

59
Grouping

• Request may require grouping of tuples in a
  relation by the value of some of their
  attributes, for instance Major for STUDENTS, and
  then applying aggregate function independently to
  each group (with the same Major)
• Average or Max gpa for students in each Major

60
STUDENT Example

• STUDENT

61
Grouping Requests

• ltgrouping attributesgt F ltfunction listgt(R)
• Major F Average Gpa(STUDENT)

62
Example

• Major F MIN Gpa, MAX Gpa (STUDENT)

63
No attribute

• If no grouping attribute is specified, the
  functions are applied to the attribute values of
  all the tuples in the relation.
• The resulting relation has single tuple only.
• F Count Gpa, Average Gpa (STUDENT)

64
Recursive Closure Operators

• Another operation that cannot be specified in the
  basic relational algebra is recursive closure.
• It si applied to recursive relationship between
  tuples of the same type.
• EMPLOYEE entity
• SUPERVISION relationship

65
Example

• Find all supervisees of an employee e at all
  levels.
• This includes
• All employees E1 directly supervised by e.
• All employees E2 directly supervised by some
  employee from E1.
• All employees E3 directly supervised by some
  employee from E2.
• and so on

66
Example
67
Supervisees

• P SubID, SuperId (EMPLOYEE) ? SUPERVISION
• P SubID ( s SuperID e (SUPERVISION)) ?
• RESULT1
• P SubID ( RESULT1 Join SUPERVISION ) ?
• SubIdSuperId
• RESULT2
• RESULT1 U RESULT2? RESULT

68
Outer Join

• L Left Outer join R
• All tuples from left relation L joined with
  matching tuples from the right relation R.
• For tuples from L with no matching tuples in R
  second tuple is filled with null values

69
Right Outer join

• L Right Outer join R
• All tuples from right relation R joined with
  matching tuples from the left relation L.
• For tuples from Rwith no matching tuples in L
  tuple is filled with null values

70
Full Outer Join

• Keeps all tuples in both left and right relation.
  When no matching tuples are found padding tuples
  with null values as needed.

71
Outer UNION

• The outer UNION is designed fro two relations
  that are not union compatible, but are partially
  compatible (only some of their attributes are
  union compatible)
• The list of union compatible attributes includes
  key(s) of both relations.

72
Example

• STUDENT(Nmae, SSN, Dept, Advisor)
• FACULTY(Name, SSN, Dept, Rank)
• STUDENT OuterU FACULTY ? RESULT(Name, SSN, DEPT,
  Advisor, Rank)

73
Example

• Each student row will have value for Advisor and
  null for Rank.
• Each faculty row will have value for Rank and
  Null for Advisor.
• All tuples from both relations are included in
  result.
• A tuple that exists in both relations will have
  values for both Advisor and Rank.