2/17/11%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,%MicrosoN%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/17/11%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/17/11%3%Rela,on%Instan ce%• 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%oNen%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%(or%NULL)%• 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/17/11%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/17/11%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/17/11%6%Dele,ng%Rela,ons%in%SQL%• Dele,ng%a%table%results%in%dele,ng%all%records%in%that%table%Copyright%©%Ben%Cartere?e% 11%DROP TABLE Students %Modifying%Rela,ons%in%SQL%• Rename%tables;%add/delete/rename%columns;%modify%field%domains%ALTER TABLE Students ADD dateOfBirth CHAR(10)!ALTER TABLE Students DROP age!ALTER TABLE Students MODIFY dateOfBirth DATE!ALTER TABLE Students RENAME RegisteredStudents!Copyright%©%Ben%Cartere?e% 12%2/17/11%7%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% 13%Dele,ng%Records%in%SQL%• Delete%records%that%sa,sfy%some%logical%condi,on%DELETE !FROM Students!WHERE name = ‘Smith’%Copyright%©%Ben%Cartere?e% 14%2/17/11%8%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% 15%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% 16%2/17/11%9%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% 17%Key%Constrai nts%• 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% 18%2/17/11%10%Key%Constrai nts%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% 19%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% 20%2/17/11%11%Foreign%Key%Constraints%in%SQL%Copyright%©%Ben%Cartere?e% 21%CREATE TABLE Enrolled ( ! student INTEGER,! course CHAR(20),! grade
View Full Document