DOC PREVIEW
UD CISC 637 - The Rela,onal Model

This preview shows page 1-2-3-4-5 out of 15 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 15 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UD CISC 637 - The Rela,onal Model

Download The Rela,onal Model
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view The Rela,onal Model and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view The Rela,onal Model 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?