Understanding Data Representation, Integrity Constraints, and Manipulation in a Database, Lecture notes of Database Management Systems (DBMS)

Download Understanding Data Representation, Integrity Constraints, and Manipulation in a Database and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

TheRelationalModel

3– 1 TheRelationalModel( 1. 1. 1 ) CSC 370 K( 2003 )[email protected] UVicCSC 370 Dr.DanielM.German DepartmentofComputerScience May 19 , 2003 Version: 1. 1. 1

Overview

✥ Howisdatarepresentedintherelationalmodel ✥ Whatintegrityconstrainscanbeexpressed? ✥ Howcandatabecreatedandmodified? ✥ Howcandatabemanipulatedandqueried? ✥ Howcanwecreate,modify,andquerytablesusingSQL? ✥ HowcanweobtainarelationaldatabasefromanERdiagram? Chapter3oftextbook

3– 4 TheRelationalModel( 1. 1. 1 ) CSC 370 K( 2003 )[email protected]

Relation

✥ A relationc onsistsof: ✦ A relationschema :describesthe nameo fthetable,the namea nd domaino feachfield(orcolumn,orattribute) Students(sid:string,name:string,age:integer,gpa:real) ✦ A relationinstance ,anactualrelation sid name login age 53666 53668 53650 53831 53832 Jones SmithSmithMadaya nGuldy Jonescs smitheesmithmathmadayan@musi cguldu@music 18 18 19 11 12 ✦ Theorderisirrelevant ✦ Intheory,notworowscanbeidentical

DomainConstraints

✥ The domainconstraintsi ndicatethevalidsetofvaluesfrom whichcolumnscantaketheirvalue ✥ The domaini slikea type ✥ Formally: {(f 1 : d 1 , ....,fn : dn ) |d 1 ∈ Dom 1 , ...,dn ∈ Domn } ✥ Aninstanceoftherelationsstudentscanbeexpressed: (sid: 53666 ,name:Jones,login:Jones@cs,age: 18 ,gpa:7.4) ✥

MoreTerminology

✥ The degreeo r arityo farelationisitsnumberoffields ✥ The cardinalityo farelationisthenumberoftuplesinit. ✥ A relationaldatabasei sacollectionofrelationswithdistinctrelatio nnames ✥ An instanceofarelationaldatabasei sacollectionofrelationinstanc es,oneperrelationschemainthedatabaseschema.

SQL

✥ InSQLa tabled enotesarelation ✥ Weusethe DataDefinitionLanguage–DDL– t ocreate,modifyordeletetables ✥ ThecommandCREATE TABLEisusedtodefineatable CREATETABLEstudents( sid char(20),name char(30), login char(20), age integer,gpa real ); ✥ Toaddatupletotherelation: INSERT INTOStudents(sid,name,login,age,gpa) VALUES(53688,’Smith’,’smith@ee’, 18 ,3.2); ✥ Todeleteatuplefromtherelation:

3– 8 TheRelationalModel( 1. 1. 1 ) CSC 370 K( 2003 )[email protected]

IntegrityConstraintsoverRelations

✥ An integrityconstraint(IC)i saconditiononthedbschemathatrestricts thedatathatcanbestoredinaninstanceofthedb ✥ AninstancethatsatisfiesallitsICsissaidtobea legalinstance ✥ ICsarespecifiedatdefinitiontime ✥ ICsareenforcedatruntime

KeyConstraints

✥ A keyconstrainti sastatementthatcertain minimals ubsetofthe fieldsofarelationisauniqueidentifierofthetuple ✥ Asetoffieldsthatuniquelyidentifiesatupleiscalleda candidatekeyo rjustakey: ✦ Twodistincttuplesoftherelationcannothavethesamevaluesinallth efieldsofthekey ✦ Nosubsetofthesetoffieldsinakeyisauniqueidentifierof thekey ✥ A superkeyi sasetoffieldsthatcontainsa key ✥ Question, whataretherisksofwronglydefiningakey?

KeyConstraintsinSQL

✥ WecandeclareakeyusingtheUNIQUEconstraint ✥ Atmostoneofthese candidatek eyscanbedeclaredasa primarykeyu singPRIMARYKEY createtablestudents( sid char(20),name char(30), login char(20), age integer,gpareal, UNIQUE (name,age), CONSTRAINTStudentsKeyPRIMARYKEY(sid) );

ForeignKeyConstraints

✥ Sometimestheinformationstoredinarelationislinkedtothe informationstoredinanotherone ✥ Ifoneismodified,theothermustbecheckedandpotentiallymodifi edinordertokeepthedataconsistent ✥ Example: Enrolled( studid :string, cid :string, grade :string) ✥ Weneedtoinsurethatonlystudentsinthe Studentsr elationshipcanbea ddedtothe Enrolledo ne ✥ The studidfi eldof Enrolledi scalleda foreignkeya nd referst o

sid name login age gpa 53666 53668 53650 53831 53832 Jones SmithSmi thMadaya nGuldy Jonescs smitheesmithmat hmadayan@musi cguldu@music 18 18 19 11 12

cid grade studid Donuts 101 C 53831 Ballroom 203 B 53832 Topology 114 A 53650 History 105 B 53666 (a)Enrolled (b)Students ✥ Ifwetrytoinsert( 55555 ,Art 104 ,A)intoEnrolled,theICis violated(itshouldberejectedbytheDBMS) ✥ But,whatshoulditdoifwewanttodelete ( 53666 ,Jones,Jones@cs, 18 ,7.4)?

ForeignKeyConstraints...

✥ Aforeignkeycanalsorefertothesamerelation ✥ Example,adda partnerfi eldtotherelation Students ✥ Whatisthepotentialproblem?

ForeignKeyConstraintsinSQL

CREATETABLEEnrolled(studid char(20),cid char(20),grade char(10), PRIMARYKEY (studid,cid), FOREIGNKEY (studid)REFERENCESStudents );

EnforcingIntegrityConstraints

Operation Domain PrimaryKey UNIQUE ForeignKey Insert Delete Modify

YES

NO

YES

YES

NO

YES

YES

NO

YES

YESSpe cial Special ✥ ExceptforForeignKeyIC,alltheothersaretrivial ✥ TheDBMSshouldtakeasequenceof ReferentialIntegrity EnforcementStepst oguaranteeICsarenotbroken