Relational model ppt

relational database model ppt and relational data model ppt
Prof.EvanBaros Profile Pic
Prof.EvanBaros,United Kingdom,Teacher
Published Date:26-07-2017
Your Website URL(Optional)
Lecture  16 The  Relational  ModelLecture  16 Today’s  Lecture 1. The  Relational  Model  &  Relational  Algebra 2. Relational  Algebra  Pt.  II    Optional:  may  skip 2Lecture  16        Section  1 1.  The  Relational  Model  &   Relational  Algebra 3Lecture  16        Section  1 What  you  will  learn  about  in  this  section 1. The  Relational  Model 2. Relational  Algebra:  Basic  Operators 3. Execution 4. ACTIVITY:  From  SQL  to  RA  &  Back 4Lecture  16        Section  1        The  Relational  Model Motivation The  Relational  model  is  precise,   implementable,  and  we  can  operate  on  it   (query/update,  etc.) Database  maps  internally  into  this   procedural  language.Lecture  16        Section  1        The  Relational  Model A  Little  History Won  Turing   • Relational  model  due  to  Edgar  “Ted”  Codd,   award  1981 a  mathematician  at  IBM  in  1970 • A  Relational  Model  of  Data  for  Large  Shared   Data  Banks".Communications  of  the   ACM13(6):  377–387 • IBM  didn’t  want  to  use  relational  model   (take  money  from  IMS) • Apparently  used  in  the  moon  landing…Lecture  16        Section  1        The  Relational  Model The  Relational  Model:  Schemata • Relational  Schema: Students(sid: string, name: string, gpa: float) Relation  name String,  float,  int,  etc.   Attributes are  the  domainsof   the  attributesLecture  16        Section  1        The  Relational  Model The  Relational  Model:  Data Student sid name gpa An  attribute(or   001 Bob 3.2 column)  is  a  typed   002 Joe 2.8 data  entry  present   in  each  tuple  in   003 Mary 3.8 the  relation 004 Alice 3.5 The  number  of   attributes  is  the  arityof   the  relation 8Lecture  16        Section  1        The  Relational  Model The  Relational  Model:  Data Student sid name gpa 001 Bob 3.2 The  number  of   002 Joe 2.8 tuples  is  the   cardinality of   003 Mary 3.8 the  relation 004 Alice 3.5 A  tupleor  row (or  record)  is  a  single   entry  in  the  table  having  the   attributes  specified  by  the  schema 9Lecture  16        Section  1        The  Relational  Model The  Relational  Model:  Data Student sid name gpa Recall:  In  practice   001 Bob 3.2 DBMSs  relax  the  set   requirement,  and   002 Joe 2.8 use  multisets.     003 Mary 3.8 004 Alice 3.5 A  relational  instanceis  a  setof  tuples   all  conforming  to  the  same  schema 10Lecture  16        Section  1        The  Relational  Model To  Reiterate • A  relational  schemadescribes  the  data  that  is  contained  in  a   relational  instance Let  R(f :Dom ,…,f :Dom )  be  a  relational  schema then,   1 1 m m an  instance  of  R  is  a  subset  of  Dom x  Dom x  …  x  Dom 1 2 n In  this  way,  a  relational  schema R  is  a  total  function  from  attribute   names to  typesLecture  16        Section  1        The  Relational  Model One  More  Time • A  relational  schemadescribes  the  data  that  is  contained  in  a   relational  instance I.e.  returns  whether  or  not  a  tuple   A  relation  R  of  arity tis  a  function:   of  matching  types  is  a  member  of  it R  :  Dom x  …  x  Dom à0,1 1 t Then,  the  schema  is  simply  the  signature  of  the  function Note  here  that  order  matters,  attribute  name  doesn’t… We’ll  (mostly)  work  with  the  other  model  (last  slide)  in   which  attribute  name  matters,  order  doesn’tLecture  16        Section  1        The  Relational  Model A  relational  database • A  relational  database  schema is  a  set  of  relational  schemata,  one  for   each  relation • A  relational  database  instance is  a  set  of  relational  instances,  one  for   each  relation Two  conventions:   1.We  call  relational  database  instances  as  simply  databases 2.We  assume  all  instances  are  valid,  i.e.,  satisfy  the  domain  constraintsLecture  16        Section  1        The  Relational  Model Remember  the  CMS Note  that  the  schemas   impose  effective  domain  /   type  constraints,  i.e.  Gpa • Relation  DB  Schema can’t  be  “Apple” • Students(sid:  string,  name:  string,  gpa:  float) • Courses(cid:  string,  cname:  string,  credits:  int) • Enrolled(sid:  string,  cid:  string,  grade:  string) Sid Name Gpa cid cname credits Relation   101 Bob 3.2 564 564-­‐2 4 Instances 123 Mary 3.8 308 417 2 sid cid Grade Students Courses 123 564 A 14 EnrolledLecture  16        Section  1        The  Relational  Model nd 2 Part  of  the  Model:  Querying SELECT We  don’t  tell  the  system how  or   FROM Students S where to  get  the  data-­‐ just  what  we   WHERE S.gpa 3.5; want,  i.e.,  Querying  is  declarative To  make  this  happen,  we  need  to   “Find  names  of  all  students   translate  the  declarative  query  into   with  GPA    3.5” a  series  of  operators…  we’ll  see  this   next Actually,  I  showed  how  to  do  this   translation  for  a  much  richer  languageLecture  16        Section  1        The  Relational  Model Virtues  of  the  model • Physical  independence  (logical  too),  Declarative • Simple,  elegant  clean:  Everything  is  a  relation • Why  did  it  take  multiple  years?   • Doubted  it  could  be  done  efficiently.Lecture  16        Section  1    Relational  Algebra Relational  AlgebraLecture  16        Section  1    Relational  Algebra RDBMS  Architecture How  does  a  SQL  engine  work  ? Relational   SQL   Optimized Algebra  (RA)   Execution Query RA  Plan Plan Declarative   Translate  to   Find  logically   Execute  each   query  (from   relational  algebra   equivalent-­‐ but   operator  of  the   user) expresson more  efficient-­‐ RA   optimized  plan expressionLecture  16        Section  1    Relational  Algebra RDBMS  Architecture How  does  a  SQL  engine  work  ? Relational   SQL   Optimized Algebra  (RA)   Execution Query RA  Plan Plan Relational  Algebra  allows  us  to  translate  declarative  (SQL)   queries  into  precise  and  optimizableexpressionsLecture  16        Section  1    Relational  Algebra Relational  Algebra  (RA) • Five  basic  operators: We’ll  look  at  these  first 1. Selection:σ 2. Projection:  Π 3. Cartesian  Product:  × 4. Union:  ∪ 5. Difference:  -­‐ • Derived  or  auxiliary  operators: • Intersection,  complement And  also  at  one  example  of  a   • Joins  (natural,equi-­‐join,  theta  join,  semi-­‐join) derived  operator  (natural   • Renaming:ρ join)  and  a  special  operator   • Division (renaming)

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.