TheRelationalModel Chapter3 - - PDF document

▶
the relational model
SMART_READER_LITE
LIVE PREVIEW

TheRelationalModel Chapter3 - - PDF document

Aug 19, 2022 461 likes •605 views

TheRelationalModel Chapter3 DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1 WhyStudytheRelationalModel?

slide-1
SLIDE 1

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1

TheRelationalModel

Chapter3

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 2

WhyStudytheRelationalModel?

  • Mostwidelyusedmodel.
✁

Vendors:IBM,Informix,Microsoft,Oracle, Sybase,etc.

  • “Legacysystems”inoldermodels
✁

E.G.,IBM’sIMS

  • Recentcompetitor:object-orientedmodel
✁

ObjectStore,Versant, Ontos

✁

Asynthesisemerging:object-relationalmodel

  • InformixUniversalServer, UniSQL,O2,Oracle,DB2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 3

RelationalDatabase:Definitions

  • Relationaldatabase: asetofrelations
  • Relation: madeupof2parts:
✁

Instance :atable, withrowsandcolumns. #Rows=cardinality,#fields=degree/ arity.

✁

Schema: specifies nameofrelation,plusnameand typeofeachcolumn.

  • E.G.Students(sid:string,name:string,login:string,

age:integer, gpa:real).

  • Canthinkofarelationasaset ofrowsor

tuples (i.e.,allrowsaredistinct).

slide-2
SLIDE 2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 4

ExampleInstanceofStudentsRelation

sid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8

  • Cardinality=3,degree=5,allrowsdistinct
  • Doallcolumnsinarelationinstancehaveto

bedistinct?

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 5

RelationalQueryLanguages

  • Amajorstrengthoftherelationalmodel:

supportssimple,powerfulquerying ofdata.

  • Queriescanbewrittenintuitively,andthe

DBMSisresponsibleforefficientevaluation.

✁

Thekey:precisesemanticsforrelationalqueries.

✁

Allowstheoptimizertoextensivelyre-order

  • perations,andstillensurethattheanswerdoes

notchange.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 6

TheSQLQueryLanguage

  • DevelopedbyIBM(systemR)inthe1970s
  • Needforastandardsinceitisusedbymany

vendors

  • Standards:
✁

SQL-86

✁

SQL-89(minorrevision)

✁

SQL-92(majorrevision)

✁

SQL-99(majorextensions,currentstandard)

slide-3
SLIDE 3

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 7

TheSQLQueryLanguage

  • Tofindall18yearoldstudents,wecanwrite:

SELECT * FROM StudentsS WHERE S.age=18

  • Tofindjustnamesandlogins,replacethefirstline:

SELECT S.name,S.login sid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@ee 18 3.2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 8

QueryingMultipleRelations

  • Whatdoesthefollowingquerycompute?

SELECT S.name,E.cid FROM StudentsS,EnrolledE WHERE S.sid=E.sid AND E.grade=“A” S.name E.cid Smith Topology112

  • sid

cid grade 53831 Carnatic101 C 53831 Reggae203 B 53650 Topology112 A 53666 History105 B

Giventhefollowinginstance

  • fEnrolled(isthispossibleif

theDBMSensuresreferential integrity?): weget:

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 9

CreatingRelationsinSQL

  • CreatestheStudents

relation.Observethatthe type(domain)ofeachfield isspecified,andenforcedby theDBMSwhenever tuples areaddedormodified.

  • Asanotherexample,the

Enrolledtableholds informationaboutcourses thatstudentstake.

CREATETABLEStudents

(sid:CHAR(20), name:CHAR(20), login:CHAR(10), age:INTEGER, gpa:REAL)

CREATETABLEEnrolled

(sid:CHAR(20), cid:CHAR(20), grade:CHAR(2))

slide-4
SLIDE 4

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 10

DestroyingandAlteringRelations

  • DestroystherelationStudents.Theschema

informationand the tuples aredeleted.

DROPTABLEStudents

  • TheschemaofStudentsisalteredbyaddinga

newfield;every tuple inthecurrentinstance isextendedwithanull valueinthenewfield.

ALTERTABLEStudents ADDCOLUMN firstYear:integer

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 11

AddingandDeleting Tuples

  • Caninsertasingle tuple using:

INSERTINTOStudents(sid,name,login,age, gpa) VALUES (53688,‘Smith’,‘smith@ee’,18,3.2)

  • Candeleteall tuples satisfyingsome

condition(e.g.,name=Smith):

DELETE FROM StudentsS WHERE S.name=‘Smith’

* Powerfulvariantsofthesecommandsareavailable;morelater!

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 12

IntegrityConstraints(ICs)

  • IC: conditionthatmustbetrueforanyinstance
  • fthedatabase;e.g.,domainconstraints.
✁

ICsarespecifiedwhenschemaisdefined.

✁

ICsarecheckedwhenrelationsaremodified.

  • Alegal instanceofarelationisonethatsatisfies

allspecifiedICs.

✁

DBMSshouldnotallowillegalinstances.

  • IftheDBMSchecksICs,storeddataismore

faithfultoreal-worldmeaning.

✁

Avoidsdataentryerrors,too!

slide-5
SLIDE 5

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 13

PrimaryKeyConstraints

  • Asetoffieldsisakey forarelationif:

1.Notwodistinct tuples canhavesamevaluesinall keyfields,and 2.Thisisnottrueforanysubsetofthekey.

✁

Part2false?A superkey.

✁

Ifthere’s>1keyforarelation,oneofthekeysis chosen(byDBA)tobetheprimarykey.

  • E.g., sid isakeyforStudents.(Whatabout

name?)Theset{sid, gpa}isa superkey.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 14

PrimaryandCandidateKeysinSQL

  • Possiblymanycandidatekeys (specifiedusing

UNIQUE),oneofwhichischosenastheprimarykey.

CREATETABLE Enrolled

(sid CHAR(20) cidCHAR(20), gradeCHAR(2),

PRIMARYKEY(sid,cid))

  • “Foragivenstudentandcourse,

thereisasinglegrade.”vs. “Studentscantakeonlyone course,andreceiveasinglegrade forthatcourse;further,notwo studentsinacoursereceivethe samegrade.”

  • Usedcarelessly,anICcanprevent

thestorageofdatabaseinstances thatariseinpractice!

CREATETABLE Enrolled

(sid CHAR(20) cidCHAR(20), gradeCHAR(2),

PRIMARYKEY(sid), UNIQUE (cid,grade))

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 15

ForeignKeys,ReferentialIntegrity

  • Foreignkey :Setoffieldsinonerelationthatisused

to`refer’toa tuple inanotherrelation.(Must correspondtoprimarykeyofthesecondrelation.) Likea`logicalpointer’.

  • E.g. sid isaforeignkeyreferringtoStudents:
✁

Enrolled(sid:string,cid:string,grade:string)

✁

Ifallforeignkeyconstraintsareenforced,referential integrity isachieved,i.e.,nodanglingreferences.

✁

Canyounameadatamodelw/oreferentialintegrity?

  • LinksinHTML!
slide-6
SLIDE 6

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 16

ForeignKeysinSQL

  • OnlystudentslistedintheStudentsrelationshould

beallowedtoenrollforcourses.

CREATETABLE Enrolled

(sid CHAR(20),cidCHAR(20),gradeCHAR(2),

PRIMARYKEY(sid,cid), FOREIGNKEY(sid)REFERENCES Students) sid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8 sid cid grade 53666 Carnatic101 C 53666 Reggae203 B 53650 Topology112 A 53666 History105 B

Enrolled Students

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 17

EnforcingReferentialIntegrity

  • ConsiderStudentsandEnrolled; sid inEnrolledisa

foreignkeythatreferencesStudents.

  • WhatshouldbedoneifanEnrolled tuple witha

non-existentstudentidisinserted?(Rejectit!)

  • WhatshouldbedoneifaStudents tuple isdeleted?
✁

AlsodeleteallEnrolled tuples thatrefertoit.

✁

DisallowdeletionofaStudents tuple thatisreferredto.

✁

Set sid inEnrolled tuples thatrefertoittoadefault sid.

✁

(InSQL,also:Set sid inEnrolled tuples thatrefertoittoa specialvaluenull,denoting`unknown’ or`inapplicable’.)

  • SimilarifprimarykeyofStudents tuple isupdated.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 18

ReferentialIntegrityinSQL

  • SQL/92andSQL:1999

supportall4optionson deletesandupdates.

✁

DefaultisNOACTION (delete/updateisrejected)

  • CASCADE (alsodelete

all tuples thatreferto deleted tuple)

  • SETNULL/ SETDEFAULT

(setsforeignkeyvalue

  • freferencing tuple)

CREATETABLE Enrolled

(sid CHAR(20), cidCHAR(20), gradeCHAR(2),

PRIMARYKEY(sid,cid), FOREIGNKEY(sid) REFERENCES Students ONDELETECASCADE ONUPDATESETDEFAULT)

slide-7
SLIDE 7

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 19

WheredoICsComeFrom?

  • ICsarebaseduponthesemanticsofthereal-

worldenterprisethatisbeingdescribedinthe databaserelations.

  • Wecancheckadatabaseinstancetoseeifan

ICisviolated,butwecanNEVER inferthat anICistruebylookingataninstance.

✁

AnICisastatementaboutallpossibleinstances!

✁

Fromexample,weknowname isnotakey,butthe assertionthat sid isakeyisgiventous.

  • KeyandforeignkeyICsarethemost

common;moregeneralICssupportedtoo.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 20

LogicalDBDesign:ERtoRelational

  • Entitysetstotables:

CREATETABLEEmployees

(ssn CHAR(11), nameCHAR(20), lotINTEGER,

PRIMARYKEY(ssn)) Employees ssn name lot

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 21

RelationshipSetstoTables

  • Intranslatingarelationship

settoarelation,attributesof therelationmustinclude:

✁

Keysforeach participatingentityset (asforeignkeys).

  • Thissetofattributes

formsa superkey for therelation.

✁

Alldescriptiveattributes.

CREATETABLEWorks_In(

ssn CHAR(1), didINTEGER, sinceDATE,

PRIMARYKEY(ssn,did), FOREIGNKEY(ssn) REFERENCES Employees, FOREIGNKEY(did) REFERENCES Departments)

slide-8
SLIDE 8

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 22

Review:KeyConstraints

  • Eachdepthasat

mostonemanager, accordingtothe keyconstraint on Manages. Translationto relationalmodel?

Many-to-Many 1-to-1 1-toMany Many-to-1 dname budget did since lot name ssn Manages Employees Departments

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 23

TranslatingERDiagramswithKeyConstraints

  • Maprelationshiptoa

table:

✁

Notethatdid is thekeynow!

✁

Separatetablesfor Employeesand Departments.

  • Sinceeach

departmenthasa uniquemanager,we couldinstead combineManages andDepartments.

CREATETABLEManages( ssn CHAR(11), didINTEGER, sinceDATE, PRIMARYKEY(did), FOREIGNKEY(ssn)REFERENCES Employees, FOREIGNKEY(did)REFERENCESDepartments) CREATETABLEDept_Mgr( didINTEGER, dname CHAR(20), budgetREAL, ssn CHAR(11), sinceDATE, PRIMARYKEY(did), FOREIGNKEY(ssn)REFERENCES Employees)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 24

Review:ParticipationConstraints

  • Doeseverydepartmenthaveamanager?
✁

Ifso,thisisaparticipationconstraint:theparticipationof DepartmentsinManagesissaidtobetotal (vs.partial).

  • Everydid valueinDepartmentstablemustappearina

rowoftheManagestable(withanon-null ssn value!)

lot name dname budget did since name dname budget did since Manages since Departments Employees ssn Works_In

slide-9
SLIDE 9

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 25

ParticipationConstraintsinSQL

  • Wecancaptureparticipationconstraintsinvolving
  • neentitysetinabinaryrelationship,butlittleelse

(withoutresortingtoCHECK constraints).

CREATETABLEDept_Mgr(

didINTEGER, dname CHAR(20), budgetREAL, ssn CHAR(11)NOTNULL, sinceDATE,

PRIMARYKEY(did), FOREIGNKEY(ssn)REFERENCES Employees, ONDELETENOACTION)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 26

Review:WeakEntities

  • Aweakentitycanbeidentifieduniquelyonlyby

consideringtheprimarykeyofanother(owner)entity.

✁

Ownerentitysetandweakentitysetmustparticipateina

  • ne-to-manyrelationshipset(1owner,manyweakentities).
✁

Weakentitysetmusthavetotalparticipationinthis identifyingrelationshipset.

lot name age pname Dependents Employees ssn Policy cost

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 27

TranslatingWeakEntitySets

  • Weakentitysetandidentifyingrelationship

setaretranslatedintoasingletable.

✁

Whentheownerentityisdeleted,allownedweak entitiesmustalsobedeleted.

CREATETABLE Dep_Policy(

pname CHAR(20), ageINTEGER, costREAL, ssn CHAR(11)NOTNULL,

PRIMARYKEY(pname, ssn), FOREIGNKEY(ssn)REFERENCES Employees, ONDELETECASCADE)

slide-10
SLIDE 10

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 28

Review:ISAHierarchies

Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked

  • AsinC++,orother PLs,

attributesareinherited.

  • IfwedeclareAISA B,everyA

entityisalsoconsideredtobeaB entity.

  • Overlapconstraints:CanJoebeanHourly_Emps aswellas

aContract_Emps entity?(Allowed/disallowed)

  • Coveringconstraints:DoeseveryEmployeesentityalsohave

tobeanHourly_Emps oraContract_Emps entity? (Yes/no)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 29

TranslatingISAHierarchiestoRelations

  • Generalapproach:
✁

3relations:Employees,Hourly_Emps andContract_Emps.

  • Hourly_Emps:Everyemployeeisrecordedin

Employees.Forhourly emps,extrainforecordedin Hourly_Emps (hourly_wages,hours_worked, ssn);must deleteHourly_Empstuple ifreferencedEmployees tuple isdeleted).

  • Queriesinvolvingallemployeeseasy,thoseinvolving

justHourly_Emps requireajointogetsomeattributes.

  • Alternative:JustHourly_Emps andContract_Emps.
✁

Hourly_Emps: ssn,name,lot,hourly_wages,hours_worked.

✁

Eachemployeemustbeinoneofthesetwosubclasses.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 30

Review:Binaryvs.Ternary Relationships

  • Whatarethe

additional constraintsin the2nd diagram?

age pname Dependents Covers name Employees ssn lot Policies policyid cost Beneficiary age pname Dependents policyid cost Policies Purchaser name Employees ssn lot

Baddesign Betterdesign

slide-11
SLIDE 11

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 31

Binaryvs.TernaryRelationships(Contd.)

  • Thekey

constraintsallow ustocombine Purchaserwith Policiesand Beneficiarywith Dependents.

  • Participation

constraintsleadto

NOTNULL

constraints.

  • WhatifPoliciesis

aweakentityset?

CREATETABLEPolicies(

policyidINTEGER, costREAL, ssnCHAR(11)NOTNULL,

PRIMARYKEY(policyid). FOREIGNKEY(ssn)REFERENCES Employees, ONDELETECASCADE) CREATETABLEDependents (

pnameCHAR(20), ageINTEGER, policyidINTEGER,

PRIMARYKEY(pname,policyid). FOREIGNKEY(policyid)REFERENCES Policies, ONDELETECASCADE)

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 32

Views

  • Aview isjustarelation,butwestorea

definition,ratherthanasetoftuples.

CREATEVIEWYoungActiveStudents(name,grade) AS SELECTS.name,E.grade FROM StudentsS,EnrolledE WHERE S.sid=E.sidandS.age<21

  • ViewscanbedroppedusingtheDROPVIEWcommand.
✁

HowtohandleDROPTABLEifthere’saviewonthetable?

  • DROPTABLEcommandhasoptionstolettheuserspecify

this.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 33

ViewsandSecurity

  • Viewscanbeusedtopresentnecessary

information(orasummary),whilehiding detailsinunderlyingrelation(s).

✁

GivenYoungStudents,butnotStudentsor Enrolled,wecanfindstudentsswhohaveare enrolled,butnotthecid’softhecoursestheyare enrolledin.

slide-12
SLIDE 12

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 34

RelationalModel:Summary

  • Atabularrepresentationofdata.
  • Simpleandintuitive,currentlythemostwidelyused.
  • IntegrityconstraintscanbespecifiedbytheDBA,

basedonapplicationsemantics.DBMSchecksfor violations.

  • TwoimportantICs:primaryandforeignkeys
  • Inaddition,wealways havedomainconstraints.
  • Powerfulandnaturalquerylanguagesexist.
  • RulestotranslateERtorelationalmodel