Relational Data Model Relational Algebra

1
Relational Data ModelRelational Algebra

• Irena Pevac
• CS501

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,FName,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
Foreign key

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

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

13
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

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

15
OPERATIONS OF THE RELATIONAL MODEL

• RETRIEVAL
• UPDATES
• Insert
• Delete
• Modify

16
RELATIONAL ALGEBRA

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

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

18
UNION
19
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.

20
INTERSECTION
21
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.

22
SET DIFFERENCE
23
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)

24
Cartesian Product
25
Selection Operator

• The selection 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,
  !,

26
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
• agegt25 AND agelt40
• NameJones OR NameBrown
• Not (NameJones OR NameBrown )

27
Selection ?

• EXAMPLE Select all tuples from Student that
  have gpa gt 3.2
• ? 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)

28
Select students with gpa equal 3.3 or above
29
Select Students with gpa above 3.2
30
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.

31
Projection

• P Name, gpa (STUDENT)

32
Join Operator

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

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

34
Join types

• 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

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

36
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

37
Join
38
Complete Set of Operatons

• Set of relational algebra operations
• s, P, U, -, x is a complete set of operations
  because any 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))