Entity-Relationship Models

ER Diagrams

Transcript

1. ICS  321  Data  Storage  &  Retrieval   High  Level  Database  Models   Prof.    Lipyeow  Lim   InformaCon  &  Computer  Science  Department   University  of  Hawaii  at  Manoa   1   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
2. Database  Design  &  Deployment     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   2   Requirements  Analysis   Conceptual  Database  Design   Logical  Database  Design   Physical  Database  Design   (DDL/DML)   Business  Processes   SQL  OperaCons  &   program  code   TesCng   ProducCon   iterate
3. Overview  Database  Design   •  Conceptual  Design   – Use  enCty-­‐relaConship  (aka  ER)  model   represented  pictorially  as  ER  diagrams   – Map  ER  model  to  relaConal  schema   •  QuesCons  to  ask  yourself   – What  are  the  enCCes  and  relaConships  in  the   applicaCon?   – What  informaCon  about  these  enCCes  and   relaConships  should  we  store  in  the  database?   – What  are  the  integrity  constraints  or  business   rules  that  hold?     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   3  
4. ER  Model  Basics:   EnCCes   !  En#ty:    Real-­‐world  object  disCnguishable  from   other  objects.  An  enCty  is  described  (in  DB)   using  a  set  of  a)ributes.     !  En#ty  Set:    A  collecCon  of  similar  enCCes.     E.g.,  all  employees.       !  All  enCCes  in  an  enCty  set  have  the  same  set  of   aXributes.    (UnCl  we  consider  ISA  hierarchies,   anyway!)   !  Each  enCty  set  has  a  key.   !  Each  aXribute  has  a  domain.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   4   Employees ssn name lot
5. ER  Model  Basics:     RelaConships   !  Rela#onship:    AssociaCon  among  two  or  more   enCCes.       !  Rela#onship  Set:    CollecCon  of  similar   relaConships.   !  An  n-­‐ary  relaConship  set    R  relates  n  enCty  sets  E1  ...  En;   each  relaConship  in  R  involves  enCCes  e1        E1,  ...,  en          En   !  Same  enCty  set  could  parCcipate  in  different  relaConship   sets,  or  in  different  “roles”  in  same  set.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   5   lot dname budget did since name Works_In Departments Employees ssn
6. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   6   Cardinality  RaCos  of  RelaConships   !  Consider  binary  relaConships,  i.e.,  between   two  enCty  sets   !  Alternate  notaCon:  1:1,  1:M,  M:1,  M:N   Many-to-Many 1-to-1 1-to Many Many-to-1
7. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   7   Key  Constraints   !  Consider  Works_In:    An  employee  can  work  in  many   depts;  a  dept  can  have  many  employees  :  m-­‐to-­‐m   !  Consider  Manages:  each  dept  has  at  most  one  manager     !  Dept  has  a  key  constraint  on  Manages:    each  instance  of   dept  appears  in  at  most  one  instance  of  manages   !  Denoted  by  an  arrow:  given  a  dept  enCty  we  can  uniquely   idenCfy  the  manages  relaConship  in  which  it  appears   dname budget did since lot name ssn Manages Employees Departments since Works_In
8. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   8   ParCcipaCon  constraints   !  Does  every  dept  have  a  manager?   !  If  so,  this  is  a  par#cipa#on  constraint:    the   parCcipaCon  of  dept  in  Manages  is  said  to  be  total   (vs.  par#al).  Denoted  by  thick/double  line   !  Meaning  that  every  Dept  enCty  must  appear  in  an   instance  of  the  Manages  relaConship   lot name dname budget did since name dname budget did since Manages Departments Employees ssn
9. Set  TheoreCc  FormulaCon   •  ParCal  ParCCcipaCon:  Not  all  members  of  the  Employees   enCty  set  take  part  in  the  manages  relaCons   •  Total  ParCCcipaCon:  All  members  of  the  Dept  enCty  set   take  part  in  the  manages  relaConship   •  Dept  has  a  key  constraint  on  Manages:    each  member  of   the  dept  enCty  set  takes  part  in  at  most  one  member  of  the   manages  relaConship  set   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   9   Employees Manages (1:m) Departments B A C D 1 2 3
10. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   10   Weak  EnCCes   !  A  weak  en#ty  can  be  idenCfied  uniquely  only  by   considering  the  primary  key  of  another  (owner)  enCty.   !  Owner  enCty  set  and  weak  enCty  set  must  parCcipate  in  a   one-­‐to-­‐many  relaConship  set  (one  owner,  many  weak   enCCes).   !  Weak  enCty  set  must  have  total  parCcipaCon  in  this   iden#fying  relaConship  set.   !  Denoted  by  a  box  with  double  or  thick  lines       lot name age pname Dependents Employees ssn Policy cost
11. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   11   Design  Choices   !  Should  a  concept  be  modeled  as  an  enCty  or   an  aXribute?   !  Should  a  concept  be  modeled  as  an  enCty  or  a   relaConship?   !  IdenCfying  relaConships:  Binary  or  ternary?   AggregaCon?   !  How  much  semanCcs  to  capture  in  the  form   of  constraints  ?  
12. Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   12   EnCty  vs.  AXribute   !  Depends  upon  how  we  want  to  use  the  address   informaCon,  and  the  semanCcs  of  the  data:   -  If  we  have  several  addresses  per  employee,  address  must   be  an  enCty  (since  aXributes  cannot  be  set-­‐valued).     -  If  the  structure  (city,  street,  etc.)  is  important,  e.g.,  we   want  to  retrieve  employees  in  a  given  city,  address  must   be  modeled  as  an  enCty  (since  aXribute  values  are   atomic).     Employee address Employee has address
13. Logical  DB  Design:  ER  to  RelaConal   •  EnCty  sets  to  tables:   CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) Employees   ssn   name   lot   13   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
14. RelaConship  Sets  to  Tables   •  AXributes  of  the   relaCon  must  include:   –  Keys  for  each   parCcipaCng  enCty  set     (as  foreign  keys).   •  This  set  of  aXributes   forms  a  superkey  for  the   relaCon.   –  All  descripCve   aXributes.   CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) 14   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   lot dname budget did since name Works_In Departments Employees ssn
15. TranslaCng  ER  Diagrams  with  Key  Constraints   •  Map  relaConship  to  a   table:   –  Note  that  did  is  the   key  now!   •  Since  each   department  has  a   unique  manager,  we   could  instead  combine   Manages  and   Departments.   CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees) 15   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   Emp. Manages (1:m) B A C D Depts 1 2 3 Employees   Manages   Departments   ssn   did   since  
16. ParCcipaCon  Constraints  in  SQL   •  We  can  capture  parCcipaCon  constraints  involving   one  enCty  set  in  a  binary  relaConship,  but  liXle  else   (without  resorCng  to  CHECK  constraints).   CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION) 16   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
17. TranslaCng  Weak  EnCty  Sets   •  Weak  enCty  set  and  idenCfying  relaConship   set  are  translated  into  a  single  table.   –  When  the  owner  enCty  is  deleted,  all  owned  weak   enCCes  must  also  be  deleted.   CREATE TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE) 17   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
18. ISA  Hierarchies           •  Overlap  constraints:    Can  Joe  be  an  Hourly_Emps  as  well  as  a   Contract_Emps  enCty?    (Allowed/disallowed)   •  Covering  constraints:    Does  every  Employees  enCty  also  have  to   be  an  Hourly_Emps  or  a  Contract_Emps  enCty?  (Yes/no)     Contract_Emps   name   ssn   Employees   lot   hourly_wages   ISA   Hourly_Emps   contrac9d   hours_worked   •  As  in  C++,  or  other  PLs,   aXributes  are  inherited.   •  If  we  declare  A  ISA  B,  every   A  enCty  is  also  considered  to   be  a  B  enCty.     18   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
19. TranslaCng  ISA  Hierarchies  to  RelaCons   •  General  approach:   –  3  relaCons:  Employees,  Hourly_Emps  and   Contract_Emps.   •  Hourly_Emps:    Every  employee  is  recorded  in  Employees.    For   hourly  emps,  extra  info  recorded  in  Hourly_Emps   (hourly_wages,  hours_worked,  ssn);  must  delete  Hourly_Emps   tuple  if  referenced  Employees  tuple  is  deleted).   •  Queries  involving  all  employees  easy,  those  involving  just   Hourly_Emps  require  a  join  to  get  some  aXributes.   •  AlternaCve:    Just  Hourly_Emps  and  Contract_Emps.   –  Hourly_Emps:    ssn,  name,  lot,  hourly_wages,   hours_worked.   –  Each  employee  must  be  in  one  of  these  two  subclasses.           19   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
20. Unified  Modeling  Language   •  Standardized  general-­‐purpose  modeling  language   for  sonware  design   •  Based  on  object-­‐oriented  model   •  Class  diagrams   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   20  
21. UML  Classes   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   21   Movies   Ctle   year   length   genre   ER Entity Set UML Class Class  name   Methods   secCon   typically  not   used  in  data   modeling  
22. AssociaCons   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   22   Cardinality   constraints  :  one   instance  of  Stars   can  be  connected   to  at  least  0   instance  of  movies   and    at  most   inifinite  instances  of   movies  
23. ReferenCal  Integrity   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   23   AggregaCon:  Must  be   0..1    (includes  1..1)   ComposiCon  :  Must  be  1..1   Every  president  runs  exactly  one   studio   AggregaCon  never  named  
24. AssociaCon  Classes   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   24  
25. Sub-­‐Class  Hierarchies   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   25  
26. Modeling  Tips   •  Faithful  to  the  semanCcs  of  the  applicaCon   •  Model  only  what  is  needed  in  the  applicaCon   •  Minimize  redundancy  (why?)   •  Simple  is  good   •  If  the  model  is  geong  too  complicated,  take  a   step  back  and  ask   –  Am  i  conceptualizing  the  right  enCCes  ?   –  Am  i  thinking  of  the  right  relaConships  ?   –  Should  some  relaConships  become  enCCes  ?  Vice   versa  ?   –  Should  some  aXributes  become  enCCes  ?  Vice  versa  ?   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   26