376a. Database Design

1
376a. Database Design

• Dept. of Computer Science
• Vassar College
• http//www.cs.vassar.edu/cs376
• Class 4 Relational Data Model and Relational
  Algebra

2
SELECT operation (s)

• sltconditiongt(RELATION) - select a subset of
  tuples from RELATION where ltconditiongt is
  satisfied.
• e.g. sSSN333445555(EMPLOYEE),
  sDEPT5(EMPLOYEE)
• Degree of result is same as R.
• of relations returned lt of relations in R.
• All relations in R are evaluated.
• SELECT is commutative.

3
PROJECT operation (s)

• pltlist of attributesgt(RELATION)
• -selects ltlist of attributesgt columns from
  RELATION
• e.g. pSEX, SALARY(EMPLOYEE)
• PROJECT removes duplicate tuples from result set.
• Degree of result is length of ltlist of
  attributesgt.
• If one attribute is a key of RELATION, result
  will have same number of relations as RELATION.

4
Relational Algerbra expression

• Created by nesting operations or applying
  operations one at a time.
• pFNAME,LNAME(sDNO5(EMPLOYEE))
• Or as a sequence of operations
• DEP5_EMPL lt-sDNO5(EMPLOYEE)
• RESULT lt- pFNAME,LNAME(DEP5_EMPL)

5
Rename operation ( r ) rho

• rS(B1, B2, .. Bn) ( R ) - attributes of R (A1,
  A2, An) are mapped to their new attribute names
  B1, B2, Bn.
• For example
• RESULT(FIRSTNAME, LASTNAME, SALARY) lt-
  pFNAME,LNAME,SALARY(EMPLOYEE)

6
Set theory operations

• Union, intersection and set difference.
• Sets must contain same type tuples (union
  compatibility constraint).
• Union - R1 ? R2 includes all tuples in R1, R2 and
  the intersection of R1 and R2 without
  duplication.
• Intersection - R1 ? R2 tuples in both R1 and R2.
• Set difference - R1 - R2 tuples in R1 but not in
  R2.

7
Set theory cont.

• By convention, attributes of resulting relation
  have the same name as first argument.
• Notes
• - UNION and INTERSECTION are commutative and
  associative
• R1 (op) R2 R2 (op) R1
• R1 (op) ( R2 (op) R3 ) (R1 (op) R2) op R3

8
Set theory cont.

• Set difference is not commutative

9
Cartesian Product (x)

• Also known as cross product or cross join.
• No union compatibility requirement.
• Q R1 x R2 where R1 (A1..An), R2(B1..Bm)
• Performs all pairs match between the two
  relations.
• Q(A1..An,B1Bm)
• Total number of instances in Q? R1R2

10
Use for these operations?

• Generate a list of all the employees of
  department 5 and their children.
• DEPT5EMPL lt- sDNO5(EMPLOYEE)
• EMPNAMES lt- pFNAME,LNAME,SSN(DEP5EMPL)
• EMPCHILDREN lt- EMPNAMES x DEPENDENT
• ACTUALDEPEND lt- sSSNESSN(EMPCHILDREN)
• RESULT lt- pFNAME,LNAME,DEPENDENT_NAME(ACTUALDEPEND
  )

11
Join operations (?)

• Product followed by select is given a special
  name, join, indicated by ?
• Joins tuples satisfying condition.
• Q R1 ? condition R2 if R1(A1..An) and R2
  (B2..Bm)
• Q(A1..An,B1..Bm) where each tuple satisfies
  condition.
• DEP EMPLOYEE ? SSNESSN DEPENDENT
• A tuple should only appear once in the output
  set. (preserving the set constraint).
• Tuples with null join attributes do not appear in
  Q.

12
Theta join (?)

• If the join constraint can be specified as
• cond1 and cond2 and condn
• and each condition, condi can be represented as
• A1 ? A2
• where ? ? , lt, ?, ?, gt, ?
• Then join is called a theta join.

13
Join operations cont.

• Join operation using only condition are called
  EQUIJOINS. (Always have one duplicate field)
• Natural join () removes duplicate by requiring
  the attributes to the have the same name.
• PROJ_DEPT lt- PROJECTr(DNAME, DNUM,MGRSSN,MGRSTART
  DATE) (DEPARTMENT)
• DNUM is join attribute.
• Join selectivity expected size of
  join/(R1R2)

14
Complete set of relational algebra operations.

• s, p, r, ?, -, x
• Intersection can be represented in terms of union
  and -.
• While not all of the joins are necessary, they
  make database operations easier.

15
Non relational algebra functions.

• Aggregate functions (?)- describe a trait of a
  set of tuples.
• SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT
• ltgrouping attributesgt ? ltfunction listgt ( R )
• ltgrouping attributesgt - attributes of R (or none)
• ltfunction listgt - list of ltfunctiongt ltattributegt
  pairs
• Rename called to give attributes names.
• For example
• DNO F COUNT SSN, AVERAGE SALARY (EMPLOYEE)
• Would result in ltDNO, COUNT_SSN, AVERAGE_SALARYgt
  tuples.

16
Recursive closure

• Find everyone reporting to John Borg
• 1. Find Johns SSN
• 2. Find all SSN, SUPERSSN relations
• 3. Find cross 2 and 1 and project to get all SSN
  of Johns supervisees.
• To get next level down
• 4. Find cross of 3 and 2 to get all supervisees
  supervisees.

17
Left Outer Join ( )

• Keeps every tuple in the first, or left relation
  R in R S.
• Example
• EMPLOYEE SSNMGRSSNDEPARTMENT
• EMPLOYEES with no matches are padded with NULLs.

18
Right outer join ( )and full outer join ( )

• Keep respective tuples accordingly.

19
Outer Union

• Subset of attributes from both relations are
  compatible (expected that compatible attributes
  include a key for both relations).

20
Lets try a few queries

• Select the names and addresses of all employees
  in department 4.
• For all projects not in Houston, list the
  project, project managers name and address
• Make a list of the names of the children whose
  parent report to a manager with the last name
  Smith

21
More queries

• How many employees have more than 1 child.
• How many employees have no children?

22
Now to chapter 9, mapping ER and EER to
relational model

• How to convert from an ER or EER model (a graph)
  to a relational model (using tables)?

23
1. Convert strong types

• Create relation R that includes all simple
  attributes of E.
• Select key of E to be key of R. Key may include
  several attributes.

24
2. Convert weak entities

• Create relation R include foreign key of E (the
  owner of weak entity W)
• Create new primary key for R using E foreign key
  and weak key

25
3. Convert 11 relations

• For entities S and T that participate in R.
• If S participates totally in R, use Ss primary
  key and T as foreign key in R.
• If both participate totally, combine
  relationships into single relation. (merge two
  entity types.)

26
4. Convert 1N relations

• S - n - R - 1 - T
• Use T as foreign key in S.
• Each instance in S is related to at most 1 T

27
5. Convert MN relations

• Create new relation S to represent R. Use
  primary keys of both relations as super key in S.
• Attach any attributes to relation to this
  relation.

28
6. Convert multi-valued attributes

• Create a new relation.
• Primary key of R and attribute, A, is primary key
  of new relation.

29
7. Convert n-ary relations

• Create new relation S.
• Primary key of S contains as foreign key keys of
  individual entities.
• If cardinality constraint of any of relations is
  1, dont add its key to the super key.

30
8. For Enhanced ER

• Create a relationship for parent and each child.
• Add primary key of parent to each child
• Primary key of each child is primary key of
  parent.