2/24/10&1&The&Rela-onal&Model&CISC437/637,&Lecture&#4&Ben&Cartere@e&1&Copyright&©&Ben&Cartere@e&Rela-onal&Model&• The&rela-onal&model&is&the&most&widely&used&in&modern&DBMS&– IBM’s&DB2,&Informix,&Oracle,&Sybase,&MicrosoO&Access&and&SQL&Server,&MySQL,&PostgreSQL&• Introduced&by&Edgar&Codd&in&1970&• Differences&from&ER&model:&– Only&one&structure&(rela-ons)&rather&than&two&(en--es&and&rela-onships)&– Logical&rather&than&conceptual&– Based&on&mathema-cal&framework&that&formally&describes&queries&of&rela-ons&2&Copyright&©&Ben&Cartere@e&2/24/10&2&Rela-ons&• Rela%ons&are&the&main&construct&in&the&rela-onal&model&• Two&components:&– Rela%on)instance&is&a&table&with&rows&and&columns&– A)rela%on)schema&describes&the&columns&of&the&table)Copyright&©&Ben&Cartere@e& 3&Rela-on&Schema&• The&schema&defines&a&rela-on&in&terms&of:&– The&rela-on&name&– The&name&of&each&column&(or&field)&– The&domain&of&each&field&• Example:&– Students(sid:&integer,&name:&string,&login:&string,&age:&!integer,&gpa:&real)&Copyright&©&Ben&Cartere@e& 4&2/24/10&3&Rela-on&Instance&• An&instance&of&a&rela-on&is&a&set&of&records&or&tuples&– Each&record&has&the&same&fields&as&defined&in&the&schema&– Rela-on&instance&=&rela-on&table&• Every&record&in&a&table&is&unique&– No&two&records&can&have&every&field&value&equivalent&• Strictly&speaking,&no&ordering&on&records&or&fields&– In&prac-ce,&ordering&fields&is&oOen&useful&Copyright&©&Ben&Cartere@e& 5&Domain&Constraints&• The&domains&of&the&fields&specify&condi-ons&that&a&rela-on&must&sa-sfy&– A&record&is&valid&only&if&its&values&are&in&the&specified&domains&• More&formally:&– R(f1:D1,&…,&fn:Dn)&is&a&rela-on&schema&– Di&is&a&name&for&a&set&of&values&Domi/– {&<f1:d1,&…,&fn:dn>&|&d1&∈&Dom1,&…,&dn&∈&Domn&}&is&a&valid&rela-on&instance&of&R&– Any&record&<f1:d1,&…,&fn:dn>&for&which&there&is&some&i&such&that&di&∉&Domi&is&invalid&Copyright&©&Ben&Cartere@e& 6&2/24/10&4&A&Few&More&Defini-ons&• The&degree&or&arity&of&a&rela-on&is&the&number&of&fields/columns&• The&cardinality&of&a&rela-on&instance&is&the&number&of&tuples/records&in&it&Copyright&©&Ben&Cartere@e& 7&Rela-onal&Databases&• A&rela%onal)database&is&a&collec-on&of&rela-ons&with&unique&names&• The&rela%onal)database)schema&is&the&collec-on&of&schemata&for&its&rela-ons&• An&instance&of&the&RDB&is&a&collec-on&of&rela-on&instances&Copyright&©&Ben&Cartere@e& 8&2/24/10&5&SQL:&&A&Brief&Overview&• SQL:&&Structured&Query&Language&• Developed&by&IBM&in&the&1970s&(separately&from&Codd)&• Standard&language&for&DBMS&– SQL:2008&is&the&current&standard&– Many&DBMS&include&their&own&addi-ons&and&modifica-ons&to&the&SQL&language&• SQL&comprises&– A&Data&Defini-on&Language&(DDL)&for&defining&rela-ons&– A&Data&Manipula-on&Language&(DML)&for&querying&rela-ons&• Today:&&a&brief&look&at&the&DDL&(and&very&brief&look&at&the&DML)&9&Copyright&©&Ben&Cartere@e&Crea-ng&Rela-ons&in&SQL&• Rela-ons&are&always&called&“tables”&in&SQL&CREATE TABLE Students ( sid INTEGER,! name CHAR(30),! login CHAR(20),! age INTEGER,! gpa REAL )!Copyright&©&Ben&Cartere@e& 10&2/24/10&6&Adding&Records&in&SQL&• Insert&a&record&into&an&exis-ng&table&INSERT!INTO Students (sid, name, login, age, gpa)!VALUES (53688, ‘Smith’, ‘smith@cis’, 18, 3.2)&Copyright&©&Ben&Cartere@e& 11&Dele-ng&Records&in&SQL&• Delete&records&that&sa-sfy&some&logical&condi-on&DELETE !FROM Students!WHERE name = ‘Smith’&Copyright&©&Ben&Cartere@e& 12&2/24/10&7&Modifying&Records&in&SQL&• Change&specified&field&values&in&all&records&that&sa-sfy&some&logical&condi-on&UPDATE Students S!SET S.age = S.age+1, S.gpa = S.gpa-1!WHERE S.sid = 53688&Copyright&©&Ben&Cartere@e& 13&Querying&Tables&in&SQL&• Find&all&records&matching&some&logical&condi-on&SELECT *!FROM Students S!WHERE S.age > 18 AND S.gpa <= 3!Copyright&©&Ben&Cartere@e& 14&2/24/10&8&Integrity&Constraints&• Integrity)constraints&are&used&to&ensure&a&database&instance&is&valid&– Defined&on&the&database&schema&– Restrict&the&data&that&can&be&stored&in&the&database&instance&• A&DBMS&enforces&the&integrity&constraints&by&only&ever&storing&legal&instances&• Example:&&domain&constraints&– No&DBMS&will&store&any&data&that&violates&a&domain&constraint&(MySQL&just&typecasts&everything)&Copyright&©&Ben&Cartere@e& 15&Key&Constraints&• An&integrity&constraint&that&ensures&that&all&records&are&uniquely&iden-fiable&• A&set&of&fields&is&a&candidate)key)for&a&rela-on&if:&– No&two&dis-nct&records&can&have&the&same&values&in&all&candidate&key&fields&– Any&strict&subset&of&the&candidate&key&fields&can&have&the&same&values&in&two&different&records&• If&this&is&false,&the&set&of&fields&is&a&superkey&• If&more&than&one&key&exists,&one&is&chosen&as&the&primary)key&Copyright&©&Ben&Cartere@e& 16&2/24/10&9&Key&Constraints&in&SQL&CREATE TABLE Students ( sid INTEGER,! name CHAR(30),! login CHAR(20),! age INTEGER,! gpa REAL,! UNIQUE (name, age),! CONSTRAINT StudentsKey PRIMARY KEY (sid) )!Copyright&©&Ben&Cartere@e& 17&Foreign&Key&Constraints&• A&foreign)key&is&a&set&of&fields&in&one&rela-on&that&refers&to&a&record&in&a&different&rela-on&– The&foreign&key&in&R1&should&map&to&the&primary&key&in&R2&• Enforcing&foreign&key&constraints&ensures&referen%al)integrity&– Every&foreign&key&points&to&data&that&exists&and&is&valid&– No&“dangling&references”&Copyright&©&Ben&Cartere@e& 18&2/24/10&10&Foreign&Key&Constraints&in&SQL&Copyright&©&Ben&Cartere@e& 19&CREATE TABLE Enrolled ( ! student INTEGER,! course CHAR(20),! grade CHAR(10),! PRIMARY KEY (student, course)! FOREIGN KEY (student) REFERENCES Students )!General&Constraints&• Data&format&constraints&– Ensure&data&maintains&a&specified&format&– E.g.&dates&must&be&strings&with&format&“MM/DD/YYYY”&• Value&constraints&– Values&for&a&field&must&fall&within&a&specified&subset&of&the&domain&– E.g.&month&may&be&defined&with&integer&domain,&but&restricted&to&1,&2,&…,&12&•