2/25/10&1&The&Rela-onal&Model&CISC437/637,&Lecture&#5&Ben&CartereAe&Copyright&©&Ben&CartereAe& 1&ER&and&Rela-onal&• The&ER&model&is&conceptual*– Focus&is&on&clearly&indica-ng&en--es&and&rela-onships&between&them&– Nota-on&for&represen-ng&different&types&of&constraints&and&higherPorder&rela-onships&• The&rela-onal&model&is&logical&– Focus&is&on&represen-ng&the&data&– No&dis-nc-on&between&en--es&and&rela-onships;&no&visualiza-on&• How&do&these&two&models&relate&to&each&other?&Copyright&©&Ben&CartereAe& 2&2/25/10&2&Transla-ng&ER&to&Rela-onal&• En-ty&sets&to&tables:&– Each&en-ty&set&in&the&ER&becomes&a&rela-on&• En-ty&set&aAributes&become&rela-on&fields&• En-ty&set&key&becomes&rela-on&key&and&a&primary&key&is&chosen&• Define&domains&as&necessary&• En--es&become&records&with&field&values&set&to&aAribute&values&Copyright&©&Ben&CartereAe& 3&Transla-ng&ER&to&Rela-onal&• Rela-onship&sets&to&tables:&– Each&rela-onship&set&in&the&ER&becomes&a&rela-on&• The&primary&key&aAributes&of&the&en-ty&sets&in&the&rela-onship&become&fields&in&the&rela-on&defined&as&foreign&keys&• The&descrip-ve&aAributes&of&the&rela-onship&set&become&fields&in&the&rela-on&– And&defined&domains&• The&primary&key&is&defined&as&the&set&of&foreign&key&fields&Copyright&©&Ben&CartereAe& 4&2/25/10&3&Transla-ng&ER&Model&Features&• Recall&features&of&ER&model:&– Key&constraints&– Par-cipa-on&constraints&– Weak&en-ty&sets&– Class&hierarchies&– Aggrega-on&• All&of&these&can&translate&to&the&rela-onal&model;&all&can&be&formulated&in&the&SQL&DDL&– Some&are&preAy&difficult&though&Copyright&©&Ben&CartereAe& 5&Transla-ng&Key&Constraints&• Rela-onship&sets&with&1:1&or&1:n&constraints&• First&translate&the&rela-onship&set&to&a&rela-on&• Then&you&can&either:&1. Reduce&the&rela-on&primary&key&set&to&only&the&key&of&the&mostPconstrained&en-ty&set,&or&2. Consolidate&the&new&rela-on&and&the&rela-on&represen-ng&the&mostPconstrained&en-ty&set&Copyright&©&Ben&CartereAe& 6&2/25/10&4&Transla-ng&Par-cipa-on&Constraints&• Rela-onships&that&require&total&par-cipa-on&• In&general,&transla-ng&these&requires&asser/ons,&which&we&have&not&covered&yet&• If&rela-onship&has&both&key&and&total&par-cipa-on&constraints:&– Transla-on&rela-onship&set&to&rela-on&– Consolidate&the&rela-on&and&the&constrained&en-ty&set&– Constrain&foreign&keys&to&nonPnull&values&– Enforce&referen-al&integrity&in&foreign&keys&Copyright&©&Ben&CartereAe& 7&Transla-ng&Weak&En-ty&Sets&• Weak&en-ty&sets&are&always&onePtoPmany&and&total&in&par-cipa-on&• Weak&en--es&are&iden-fiable&only&in&conjunc-on&with&an&iden-fying&owner&• They&can&be&deleted&when&the&iden-fying&owner&is&deleted&• So:&– Translate&en-ty&set&+&rela-onship&to&a&rela-on&– Primary&key&is&key&of&iden-fying&owner&+&par-al&key&of&weak&en-ty&set&– When&the&iden-fying&owner&is&deleted,&cascade&delete&records&Copyright&©&Ben&CartereAe& 8&2/25/10&5&Transla-ng&Class&Hierarchies&• Think&of&class&hierarchy&as&a&set&of&binary,&manyPtoPone,&total&par-cipa-on&rela-onships&• Then&translate&each&subclass&to&a&rela-on&similarly&to&the&transla-on&of&a&weak&en-ty&set&and&iden-fying&owner&• An&alternate&approach:&– Create&one&rela-on&for&each&subclass&– Duplicate&superclass&aAributes&across&rela-ons&Copyright&©&Ben&CartereAe& 9&Transla-ng&Aggrega-on&• Translate&the&en--es&and&rela-onship&within&the&aggrega-on&as&usual&• Translate&the&rela-onship&the&aggrega-on&is&involved&in&to&a&new&rela-on&– Use&aggregated&en-ty&set&keys&plus&“outer”&en-ty&set&key&as&primary&key&– Aggregated&en-ty&set&keys&become&a&foreign&key&– “Outer”&en-ty&set&key&becomes&a&foreign&key&Copyright&©&Ben&CartereAe& 10&2/25/10&6&Views&• A&view&is&a&virtual&rela-on&defined&in&terms&of&a&query&• Views&help&support&logical&data&independence&– They&can&be&used&to&define&“external&schema”&that&hide&changes&in&the&underlying&logical&or&conceptual&schema&• Views&also&help&support&security&– A&view&can&restrict&informa-on&access&to&certain&fields&within&a&rela-on&Copyright&©&Ben&CartereAe& 11&SQL&Querying&Revisited&• Queries&can&span&mul-ple&tables&SELECT S.sid, S.gpa!FROM Students S, Enrolled E!WHERE S.sid = E.student AND E.grade = ‘A’!Copyright&©&Ben&CartereAe& 12&2/25/10&7&Views&in&SQL&• A&view&is&a&virtual&rela-on&defined&in&terms&of&a&query&CREATE VIEW YoungActiveStudents(name,grade)! AS SELECT S.name,E.grade! FROM Students S, Enrolled E! WHERE S.sid = E.sid AND S.age < 21&Copyright&©&Ben&CartereAe& 13&Dele-ng&Rela-ons&in&SQL&• Views&and&tables&can&be&deleted—all&records&are&deleted&along&with&the&schema&• RESTRICT or&CASCADE keywords&instruct&DBMS&how&enforce&referen-al&integrity&DROP TABLE Students [RESTRICT|CASCADE]!DROP VIEW YoungActiveStudents!Copyright&©&Ben&CartereAe&