376a' Database Design

1
376a. Database Design

• Dept. of Computer Science
• Vassar College
• http//www.cs.vassar.edu/cs376
• Class 6 Converting ER and EER diagrams to
  Relational Mapping

2
Housekeeping

• Homework for Oct 2nd.
• 4.19, 7.20, 7.26, 9.11, 9.15, 9.20 (except h)
• Potential midterm for October 14?

3
More queries

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

4
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)?
• 7 steps (2 extra for EER)
• Have a graph describing the entities and
  relationships (also the subclasses and categories
  in the case of EER) gt tables

5
1. Convert strong types

• Create relation R that includes all simple
  attributes of E.
• Flatten composite attributes into simple
  attributes.
• Do NOT add mutivalued attributes yet.
• Select key of E to be key of R. Key may include
  several attributes.
• E.g. EMPLOYEE

6
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 key of weak entity.
• E.g. DEPENDENTS

7
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 entity types
  and relationship into single relation. (merge two
  entity types.)
• E.g. EMPLOYEE MANAGES DEPT

8
4. Convert 1N relations

• S - n - R - 1 - T
• Make relation S
• Use T as foreign key in S since each instance in
  S is related to at most one T.
• E.g. EMPLOYEE - n - WORKS_FOR - 1 - DEPT

9
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.
• E.g. EMPLOYEE-n-WORKS_ON-m-PROJECT (with HOURS
  attribute)

10
6. Convert multi-valued attributes

• A - Multi-valued attribute
• S - entity A is associated with.
• Create a new relation R has K the primary key of
  S and A as attributes.
• Primary key of S is A and K.
• E.g. DEPARTMENT_LOCATIONS

11
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 relation is
  1, dont add its key to the super key (fully
  specified by other keys)
• E.g. SUPPLIES for (SUPPLIER, PART, PROJNAME)

12
8. For subclasses

• Create a relationship for superclass ( C(k, a1,
  a2an ) )and each child Si(bi1,..bim)
• 4 choices
• 1. create L(k, a1, a2an ) and Li (k ? bi1..b1m
  ) PK(Li) k
• 2. create Li(bi1..bim ? k, a1..an)and PK(Li)
  k
• 3. create L(b11..b1m ?b21..b2mbh1..bhm
  ?k, a1..an ?t)and PK(Li) k
• 4. same as above t1, t2tm

13
8. Observations

• 1 and 2 are multiple relations.
• 3 and 4 are single relations.
• 1 works in disjoint and overlapping and total and
  partial constraint situations.
• 2 works only when disjoint and total
  participation (why?)
• 3 and 4 have NULLs for unused attributes.
• 3 disjoint one t for type.
• 4 overlapping, multiple ts for inclusion.

14
9. Mapping categories

• Create new relation R.
• Create surrogate key, K in R.
• Create a type attribute A.
• Add surrogate key K as foreign key in each
  relation S in the union.

15
Relational Calculus

• Declarative expressions similar to SQL.
• Do not specify how to retrieve data (unlike
  relational algebra) only what data is retrieved.
• Non procedural, declaritive
• Same expressive power as RA (if can find mapping
  between language and RC then language is
  relationally complete)
• Cant express ? -operations

16
Definitions

• Tuple variable - a variable taking the value of
  any tuple in a range.
• Range relation - R(t) where R is a relation where
  tuple t is drawn from.

17
A relational calculus query

• t COND (t) returns tuples which satisfy
  condition COND.
• E.g. x EMPLOYEE(x) and x.salarygt20000
• Range relation EMPLOYEE(x) indicates that x takes
  on the value of any tuple in EMPLOYEE relation.
• Every tuple is evaluated against both conditions
  to identify selected valid (TRUE) combinations.

18
Example RC query

• Retrieve Name and Address of employees in
  department number 5
• e.name, e.address EMPLOYEE(e) and e.DNO 5

19
How to construct expressions and formulas

• Expressions are
• t1..A1, t2.A2, tn.An COND (t1, t2..tn..tnm)
• t1..tn are tuple variables and Ai is an attribute
  of ti
• Left side are requested attributes.
• Right side is a condition or formula.

20
Rules for making formulas

• Valid atoms
• 1. Can be an atom R(ti) - R is relation and t is
  a tuple variable. Tuples are assigned a member
  of the relation R.
• 2. Can be an atom ti..Ai op tj..Aj where op ?
  , gt , ?, lt, ?, ?. Assigned tuples which make
  atom evaluate to true.
• 3. Can be an atom ti..Ai op c where op ? , gt ,
  ?, lt, ?, ? Assigned tuples which make atom
  evaluate to true.

21
Create valid formulas

• Atoms are formulas
• If F1 F2 are formulas -gt (F1 and F2), (F1 or
  F2), not (F1) and not (F2) are formulas.
• Normal truth tables apply to these evaluations.

22
Existential and Universal quantifiers

• Existential and Universal quantifiers are bound
  tuple variables.
• Add two more valid formulas
• If F is a valid formula and x is a tuple
  variable, then (?x)(F) is a valid formula. It is
  true if there exists at least one tuple x which
  make F true.
• If F is a valid formula and t is a tuple
  variable, then (?x)(F) is a valid formula. True
  if any tuple assigned to x makes F true.

23
Examples from book

• Retrieve names and address of employees from
  research department.

24
Design in two halfs

• First, what are the desired attributes
• E.name and e.address

25
Next design the formula

• Only free tuple variables should be on left side
  of bar. Other variables should be bound on right
  side of bar.
• EMPLOYEE(e) and (?d) (DEPARTMENT(d)) and
  d.DNAMEresearch and d.DNUMBERe.DNO

26
Finally

• e.name, e.address EMPLOYEE(e) and (?d)
  (DEPARTMENT(d) and d.DNAMEresearch and
  d.DNUMBERe.DNO)
• Only free tuple variables should be on left side
  of bar.

27
Another example

• For every project in Stafford, list the project
  number, the controlling department, the
  department managers last name, birthdate and
  address.

28
Another example

• For every project in Stafford, list the project
  number, the controlling department, the
  department managers last name, birthdate and
  address.
• Left side
• p.PNUMBER, p.DUM, e.LNAME, e.BDATE, e.ADDRESS
• Right side
• What are the free variables and what are the
  bound variables?

29
Example cont.

• P is free and e is free. (both on left side) what
  else is needed to solve the problem?
• PROJECT(p) and EMPLOYEE(e) and DEPARTMENT(d) and
  p.DNUM d.DNUMBER and d.MGRSSN e.SSN
• Problem d is not bound in formula
• Correct this.

30
Query 3

• Find the name of all employees who work on some
  project controlled by department number 5.
• Returned value is e.LNAME, e.FNAME
• Use existential quantifiers for PROJECT and
  WORKS_ON

31
Converting between Universal and Existential
quantifiers

• (? x) (P(x)) not (? x) (not P(x))
• (? x) (P(x)) not (? x) (not P(x))
• (? x) (P(x) or Q(x)) not (? x) (not (P(x)) and
  not (Q(x)))
• Most important relationship to remember is
• If x then y gt (not (x) or y)
• And many more on page 305.

32
Using the Universal quantifier

• Find the names of employees who work on all
  projects in department number 5.
• e.FNAME, e.LNAME EMPLOYEE(e) and ( (? x) (not
  (PROJECT(x) or not (x.DNUM5) or ((? w)
  (WORKS_ON(w) and w.ESSNe.SSN and
  x.PNUMBERw.PNO))))
• Remember x must be true for all tuples in the
  universe! (need to cover whole space).

33
Can transform it to using only existential
quantifiers

• Use (? x) (P(x) or Q(x)) not (? x) (not (P(x))
  and not (Q(x)))

34
Safe expressions

• Expressions should return finite number of
  results.
• t not (EMPLOYEE(t)) is not safe.
• Only considered safe if the results are from the
  domain of the range relation (right side).
• Not (EMPLOYEE(t)) has tuples from outside the
  EMPLOYEE(t) relation.

35
One more calculus Domain Relational Calculus

• Domain relational calculus used in query by
  example.
• Variables range of domains of attributes (instead
  of tuples.)
• E.g.
• x1, x2..xn COND(x1, x2..xn, ..xnm
• xi range of domain of attribute Ai

36
Atoms are different

• Atom may be
• 1. R(x1, x2, xn) where r is a relation with
  degree n and each xi is a domain variable. In
  short hand R(x1 x2 xn) no commas
• 2. xi. op xj. where op ? , gt , ?, lt, ?, ?.
  xs are domain variables.
• 3. xi. op c where op ? , gt , ?, lt, ?, ? and
  xi is a domain variable.
• Normally use lowercase l-z for domain vars

37
Example

• Get birthdate and address of person named John
  B. Smith
• uv (? q) (? r) (? s) (? t) (? w) (? x) (? y)
  (? z) (EMPLOYEE (qrstwxyz) and q John and
  rB. and sSmith)
• Every attribute of EMPLOYEE is assigned a domain
  var. only U and V are free.

38
Another way

• q EMPLOYEE( John,B.,Smith,t, u, v, w, x,
  y, z)
• All variables are free.

39
Example

• Name and address of everyone in research
  department
• qsv (? z) (? l) (? m) (EMPLOYEE(qrstuvwxyz)
  and DEPT(lmno) and lresearch and mz)

40
Try a few

• For every project in Stafford, list the
  controlling managers name and birthdate.
• Find employees with no dependents.
• List names of all managers with one dependent.