1 / 40

376a. Database Design

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. Housekeeping. Homework for Oct 2nd. 4.19, 7.20, 7.26, 9.11, 9.15, 9.20 (except h) Potential midterm for October 14?. More queries.

leatherman
Download Presentation

376a. Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Prof. Billibon Yoshimi

  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? Prof. Billibon Yoshimi

  3. More queries • How many employees have more than 1 child. • How many employees have no children? Prof. Billibon Yoshimi

  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) => tables Prof. Billibon Yoshimi

  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 Prof. Billibon Yoshimi

  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 Prof. Billibon Yoshimi

  7. 3. Convert 1:1 relations • For entities S and T that participate in R. • If S participates totally in R, use S’s 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 Prof. Billibon Yoshimi

  8. 4. Convert 1:N 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 Prof. Billibon Yoshimi

  9. 5. Convert M:N 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) Prof. Billibon Yoshimi

  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 Prof. Billibon Yoshimi

  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, don’t add its key to the super key (fully specified by other keys…) • E.g. SUPPLIES for (SUPPLIER, PART, PROJNAME) Prof. Billibon Yoshimi

  12. 8. For subclasses • Create a relationship for superclass ( C(k, a1, a2…an ) )and each child Si(bi1,..bim) • 4 choices: 1. create L(k, a1, a2…an ) 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..b2m}…{bh1..bhm} {k, a1..an} {t})and PK(Li) = k 4. same as above + {t1, t2…tm} Prof. Billibon Yoshimi

  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 t’s for inclusion. Prof. Billibon Yoshimi

  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. Prof. Billibon Yoshimi

  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) • Can’t express  -operations Prof. Billibon Yoshimi

  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. Prof. Billibon Yoshimi

  17. A relational calculus query • {t | COND (t)} returns tuples which satisfy condition COND. • E.g. {x | EMPLOYEE(x) and x.salary>20000} • 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. Prof. Billibon Yoshimi

  18. Example RC query Retrieve Name and Address of employees in department number 5 {e.name, e.address | EMPLOYEE(e) and e.DNO = 5} Prof. Billibon Yoshimi

  19. How to construct expressions and formulas Expressions are {t1..A1, t2.A2, tn.An | COND (t1, t2..tn..tn+m) } t1..tn are tuple variables and Ai is an attribute of ti Left side are requested attributes. Right side is a condition or formula. Prof. Billibon Yoshimi

  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  { =, > , , <, , }. Assigned tuples which make atom evaluate to true. 3. Can be an atom ti..Ai op cwhere op  { =, > , , <, , } Assigned tuples which make atom evaluate to true. Prof. Billibon Yoshimi

  21. Create valid formulas • Atoms are formulas • If F1 & F2 are formulas -> (F1 and F2), (F1 or F2), not (F1) and not (F2) are formulas. • Normal truth tables apply to these evaluations. Prof. Billibon Yoshimi

  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. Prof. Billibon Yoshimi

  23. Examples from book • Retrieve names and address of employees from research department. Prof. Billibon Yoshimi

  24. Design in two halfs • First, what are the desired attributes • E.name and e.address Prof. Billibon Yoshimi

  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.DNAME=“research” and d.DNUMBER=e.DNO Prof. Billibon Yoshimi

  26. Finally {e.name, e.address | EMPLOYEE(e) and (d) (DEPARTMENT(d) and d.DNAME=“research” and d.DNUMBER=e.DNO) • Only free tuple variables should be on left side of bar. Prof. Billibon Yoshimi

  27. Another example • For every project in ‘Stafford’, list the project number, the controlling department, the department manager’s last name, birthdate and address. Prof. Billibon Yoshimi

  28. Another example • For every project in ‘Stafford’, list the project number, the controlling department, the department manager’s 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? Prof. Billibon Yoshimi

  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. Prof. Billibon Yoshimi

  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 Prof. Billibon Yoshimi

  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 => (not (x) or y) • And many more on page 305. Prof. Billibon Yoshimi

  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.DNUM=5) or (( w) (WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)))) } • Remember x must be true for all tuples in the universe! (need to cover whole space). Prof. Billibon Yoshimi

  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))) Prof. Billibon Yoshimi

  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. Prof. Billibon Yoshimi

  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, ..xn+m} xi range of domain of attribute Ai Prof. Billibon Yoshimi

  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  { =, > , , <, , }. x’s are domain variables. 3. xi. op c where op  { =, > , , <, , } and xi is a domain variable. *Normally use lowercase l-z for domain vars Prof. Billibon Yoshimi

  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 r=‘B.’ and s=‘Smith’)} Every attribute of EMPLOYEE is assigned a domain var. only U and V are free. Prof. Billibon Yoshimi

  38. Another way {q | EMPLOYEE( ‘John’,’B.’,’Smith’,t, u, v, w, x, y, z) } All variables are free. Prof. Billibon Yoshimi

  39. Example Name and address of everyone in research department {qsv | ( z) ( l) ( m) (EMPLOYEE(qrstuvwxyz) and DEPT(lmno) and l=‘research’ and m=z) Prof. Billibon Yoshimi

  40. Try a few For every project in Stafford, list the controlling manager’s name and birthdate. Find employees with no dependents. List names of all managers with one dependent. Prof. Billibon Yoshimi

More Related