Relational Algebra and Relational Calculus

1
Lecture 11 (09/27/2005)

• Relational Algebra and Relational Calculus

2
Assignment 1

• Highest 18, Lowest 8, and AVG 14.65
• Most 14-16
• Organization helps me understand better
• Might mean more points!
• If you dont know how, please use the format that
  I gave you

3
Exercise 1

• The video rental company has several branches
• Each branch is allocated staff (each limited to a
  single branch), which includes a Manager (along
  with the start date of the Manager).
• Each branch has a stock of videos.
• The data held on a video is the video number,
  copy number, title, category, daily rental,
  purchase cost, status, and the names of the main
  actors, and the director.
• The video number uniquely identifies each video
  however, in most cases, there are several copies
  of each video, and the individual copies are
  identified using the copy number
• Note that different copies could be purchased at
  different prices but they all rent for the same
  daily cost.

4
Exercise 1

• A video is given a category such as Action,
  Adult, Children, Drama, Horror, or Sci-Fi. The
  status indicates whether a specific copy of a
  video is available for rent
• Before hiring a video from the company, a
  customer must first register as a member of the
  company.
• Registration requires the following information
  the first and last name, address, and the
  membership date.
• Each member is also given a member number, which
  is unique throughout all branches of the company.
  
• The first time a customer rents a video from a
  new branch, s/he is automatically registered with
  the branch (along with the registration date).

5
Exercise 1

• new company customers register with the company
  at some branch and also with the branch itself
  thereafter, they only register with any new
  braches with which they deal.
• The data held on each rented video is the rental
  number, the full name and number of the member,
  the video and copy numbers, title, and daily
  rental, and the dates the video is rented out and
  date returned. The rental number is unique
  throughout the company.

6
Exercise 1
WORKS_FOR
BRANCH
STAFF
MANAGES
REGISTERS
CUSTOMER
HAS_STOCK
RENTS
VIDEO
IS_A_COPY_OF
COPY
7
Exercise 1

• No need for a COMPANY entity type
• CSBSJU listed in university records?
• VIDEO and VIDCOPY
• VIDEO title
• like PROJECT and STAGE example with EMPLOYEEs
  working on STAGEs
• Some made VIDCOPY a subclass of VIDEO
• Cant have a different subclass key

8
Exercise 1

• Some made RENTED_VID a subclass of VIDEO
• YOU INTRODUCED NEW KEYS AND SUBCLASSES CANT HAVE
  KEYS
• COPY unique per branch or through out company
  (latter is meant)
• 1 unique only through (VIDCOPY is a weak entity)
• One video entity title
• 2 unique only per branch
• Need to have different video entities for the
  same video one per branch
• Each has many copies

9
Exercise 2

• The University has many teachers and offers
  different advanced technology courses, each of
  which is taught by a teaching team of one or more
  teachers, who is fully responsible for giving all
  the training sessions of an assigned course.
• Each teacher is assigned to a limited number of
  teaching teams and/or may be assigned to work on
  an individual research project.
• Trainees register and attend training sessions
  for the courses they are interested in.

10
(No Transcript)
11
Exercise 2

• No need for a University entity type
• Trains professionals so no students and grades
• The University has many teachers and offers
  different advanced technology courses,
• each of which is taught by a teaching team of one
  or more teachers, which is fully responsible for
  giving all the training sessions of an assigned
  course.
• Each teacher is assigned to a limited number of
  teaching teams and/or may be assigned to work on
  an individual research project.

12
Exercise 2

• Entity-type and relationship-type names
• Caps Singular
• Teacher teaches Course is wrong
• Trainees register and attend training sessions
  for the courses they are interested in
• Session Information must be there
• STUDENTs attend SESSIONs of COURSES or STUDENT
  attend COURSes that have SESSIONs

13
Exercise 2

• Teachers are divided into categories by their
  rank
• Professors, Associate Professors, Assistants
  Professors and Instructors where a teacher has
  only one rank
• Teachers at the rank of Professors or Associate
  Professors have been previously tenured by the
  university
• We need to store the date on which they were
  tenured
• For instructors, store the last evaluation date.
• Only instructors are not assigned any research
  duties
• Store, for every teacher name, rank, salary, and
  hire date

14
Comments

• The specification is given from the users point
  of view but
• We have to figure out the best way to group
  entity types into super/subclasses
• We still have a rank attribute
• Dealing with user requirements is hard
• Part of OUR JOB as computer specialists

15
So Far

• Select ? ltselection conditiongt(R)
• Project ?ltattribute_listgt(R)
• Rename
• Union R?S
• Intersection R ? S
• Difference R - S

16
Operations From Set Theory

• CARTESIAN (or cross product) Operation
• Combine tuples from two relations in a
  combinatorial fashion
• R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a
  relation Q with
• degree n m attributes Q(A1, A2, . . ., An, B1,
  B2, . . ., Bm)
• if R has nR tuples (cardinality of R denoted as
  R nR ), and S has nS tuples, then R x S
  will have nR nS tuples
• The two operands do NOT have to be "type
  compatible
• Find the cross product between female employees
  (FName and SSN) and dependents
• FEMALE_EMPS ? ? SEXF(EMPLOYEE)
• EMPNAMES ? ? FNAME, LNAME, SSN (FEMALE_EMPS)
• EMP_DEPENDENTS ? EMPNAMES x DEPENDENT

17
To get employees dependants, SSNs must match
The rest are spurious
18
Binary Relational Operations

• JOIN Operation
• The CARTESIAN PRODUCT operation is rarely used on
  its own because of
• Spurious tuples
• Efficiency
• Usually followed by a SELECT operation to get
  actual tuples
• The sequence of cartesian product followed by
  select is used quite commonly to identify and
  select related tuples from two relations, a
  special operation, called JOIN
• Very important for any relational database with
  more than one relation
• Allows us to process relationships among
  relations
• Makes the Pk,Fk combinations effective (like a
  physical link)
• The general form of a join operation on two
  relations R(A1, A2, . . ., An) and S(B1, B2, . .
  ., Bm) is
• R ltjoin conditiongtS where R and S can be any
  relations

19
Binary Relational Operations

• Retrieve the manager of each department
• we need to combine each DEPARTMENT tuple with the
  EMPLOYEE tuple whose SSN value matches the MGRSSN
  value in the department tuple
• We do this by using the join operation
• DEPT_MGR ? DEPARTMENT MGRSSNSSN EMPLOYEE

20
Binary Relational Operations

• Get the locations of every department
• DEPT_LOCS ? DEPARTMENT DEPT_LOCATIONS
  DNUMBERDNUMBER

21
Joins and Efficiency

• DBMSs efficiency can be affected by the way we
  do joins
• Get me SSN of any Research department in in
  Houston
• Join Department and Dept_Locations and then
  select SSN from tuples where DnameResearch and
  Location Houston
• Select Departments with DnameResearch and and
  Dept_Locations where Dlocation Houston and
  then join results