【文档说明】[理学]数据库原理课件lecture2-Relational-Data-Model.ppt，共(51)页，1.340 MB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-6044.html

以下为本文档部分文字说明：

1©2009XiaojieYuanTheRelationalDataModel•Tables•Schemas•ConversionfromE/RtoRelations2©2009XiaojieYuanDatabaseModelin

g&ImplementationDatabaseModel(E/R,ODL)IdeasPhysicalstorageDiagrams(E/R)Tables:columnnames:attributesrows:tuplesComplexfileorganizationandindexstru

ctures.RelationalSchema3©2009XiaojieYuanERModelvs.RelationalModel•Bothareusedtomodeldata•ERmodelhasmanyconcepts–entities,relation

s,attributes,etc.–well-suitedforcapturingtheapp.requirements–notwell-suitedforcomputerimplementation–(doesnotevenhaveoperatio

nsonitsstructures)•Relationalmodel–hasjustasingleconcept:relation–worldisrepresentedwithacollectionoftables–well-suitedforefficientma

nipulationsoncomputers4©2009XiaojieYuanAnExampleofaRelationNamePriceCategoryManufacturergizmo$19.99gadgetsGizmoWorksPowergizmo$29.99

gadgetsGizmoWorksSingleTouch$149.99photographyCanonMultiTouch$203.99householdHitachituplesAttributenamesTablenameProducts:

5©2009XiaojieYuanDomains•Eachattributehasatype•Mustbeatomictype(why?seelater)•Calleddomain•Examples:–Integer–String

–Real–…6©2009XiaojieYuanSchemasTheSchemaofaRelation:–Relationnameplusattributenames–E.g.Product(Name,Price,Category,Manufac

turer)–InpracticeweaddthedomainforeachattributeTheSchemaofaDatabase–Asetofrelationschemas–E.g.Product(Name,Price,Category,Manufacturer

),Vendor(Name,Address,Phone),.......7©2009XiaojieYuanInstances•Relationalschema=R(A1,…,Ak):Instance=re

lationwithkattributes(of“type”R)–valuesofcorrespondingdomains•Databaseschema=R1(…),R2(…),…,Rn(…)Instance=nrelations,oftypesR1,R2,...,RnSchema

svs.instances(veryimportant,makesureyouknowthedifference)8©2009XiaojieYuanExampleNamePriceCategoryManufacturergizmo$19.99gadgetsGizmoWorksPowergizmo

$29.99gadgetsGizmoWorksSingleTouch$149.99photographyCanonMultiTouch$203.99householdHitachiRelationalschema:Product(Name,Price,C

ategory,Manufacturer)Instance:9©2009XiaojieYuanUpdatesThedatabasemaintainsacurrentdatabasestate.Updat

estothedata:1)addatuple2)deleteatuple3)modifyanattributeinatupleUpdatestothedatahappenveryfrequently.U

pdatestotheschema:relativelyrare.Ratherpainful.Why?10©2009XiaojieYuanSchemasandInstances•Analogywithprogramminglangua

ges:–Schema=type–Instance=value•Importantdistinction:–DatabaseSchema=stableoverlongperiodsoftime–DatabaseInstance=changesconstantly,asdat

aisinserted/updated/deleted11©2009XiaojieYuanTwoMathematicalDefinitionsofRelationsRelationasCartesianproduct•Tuple=elementofstring

xintxstringxstring•E.g.t=(gizmo,19,gadgets,GizmoWorks)•Relation=subsetofstringxintxstringxstring•Orderinthetupleisimportant!–(gizmo,19,gadgets,GizmoW

orks)–(gizmo,19,GizmoWorks,gadgets)•Noattributes12©2009XiaojieYuanRelationasasetoffunctions•Fixthesetofattributes–A={name,price,c

ategory,manufacturer}•Atuple=functiont:ADomains•Relation=setoftuples•E.g.•Orderinatupleisnotimportant•Attribut

enamesareimportant{namegizmo,price19,categorygadgets,manufacturergizmoWorks}13©2009XiaojieYuanTranslatingERDiagramtoRel

.Design•Basiccases–entitysetE=relationwithattributesofE–relationshipR=relationwithattributesbeingkeysofrelatedentit

ysets+attributesofR•Specialcases–combiningtworelations–translatingweakentitysets–translatingis-arelationsh

ipsandsubclasses14©2009XiaojieYuanStockpriceaddressnamessnPersonbuysmakesemploysCompanyProductnamecategorynamepriceAnExample15©20

09XiaojieYuanEntitySetstoRelationsProductnamecategorypriceProduct:NameCategoryPricegizmogadgets$19.9916©2009XiaojieYuanRel

ationshipstoRelationsmakesCompanyProductnamecategoryStockpricenameRelationMakes(watchoutforattributenameconflicts)Product-nameProduct-Catego

ryCompany-nameStarting-yeargizmogadgetsgizmoWorks1963StartYearprice17©2009XiaojieYuanRelationshiptoRelation:AnotherExampleDrinkersBeersL

ikesLikes(drinker,beer)FavoriteFavorite(drinker,beer)MarriedhusbandwifeMarried(husband,wife)nameaddrnamemanfBuddies12Buddies(name1

,name2)18©2009XiaojieYuanCombiningTwoRelationsmakesCompanyProductnamecategoryStockpricenameNoneedforMakes.JustmodifyProduct:nam

ecategorypriceStartYearcompanyNamegizmogadgets19.991963gizmoWorksStartYearprice19©2009XiaojieYuanCombi

ningRelations•ItisOKtocombinetherelationforanentity-setEwiththerelationRforamany-onerelationshipfromEtoa

notherentityset.•Example:Drinkers(name,addr)andFavorite(drinker,beer)combinetomakeDrinker1(name,addr,fav

oriteBeer).20©2009XiaojieYuanRiskwithMany-ManyRelationships•CombiningDrinkerswithLikeswouldbeamistake.Itleadstoredundancy,as:namea

ddrbeerSally123MapleBudSally123MapleMillerRedundancy21©2009XiaojieYuanHandlingWeakEntitySetsUniversityTeamaffili

ationnumbersportnameRelationTeam:SportNumberAffiliatedUniversitymudwrestling15MontezumaStateU.-needalltheattributesthatcontributetothek

eyofTeam-don’tneedaseparaterelationforAffiliation.(why?)22©2009XiaojieYuanHandlingWeakEntitySets•Relationforaweakentitysetmustincludeatt

ributesforitscompletekey(includingthosebelongingtootherentitysets),aswellasitsown,nonkeyattributes.•Asupporting(double-diamond)rel

ationshipisredundantandyieldsnorelation.23©2009XiaojieYuanAnotherExampleLoginsHostsAtnamenameHosts(hostName)Logins

(loginName,hostName,time)At(loginName,hostName,hostName2)MustbethesametimeAtbecomespartofLogins24©2009X

iaojieYuanTranslatingSubclassEntitiesProductEducationalProductSoftwareProductEduc-softwareProductageGrouptopicPlat

formsrequiredmemoryEducational-methodisaisaisaisa25©2009XiaojieYuanOption#1:theOOApproach4tables:eachobject

canonlybelongtoasingletableProduct(name,price,category,manufacturer)EducationalProduct(name,price,category,manufa

cturer,ageGroup,topic)SoftwareProduct(name,price,category,manufacturer,platforms,requiredMemory)EducationalSoftwareProduct(name,price,category,m

anufacturer,ageGroup,topic,platforms,requiredMemory)Allnamesaredistinct26©2009XiaojieYuanOption#2:the

E/RApproachProduct(name,price,category,manufacturer)EducationalProduct(name,ageGroup,topic)SoftwareProduct(

name,platforms,requiredMemory)NoneedforarelationEducationalSoftwareProductUnless,ithasaspecializedattr

ibute:EducationalSoftwareProduct(name,educational-method)Samenamemayappearinseveralrelations27©2009XiaojieYuanOption#

3:TheNullValueApproachHaveonetable:Product(name,price,manufacturer,age-group,topic,platforms,required-memor

y,educational-method)SomevaluesinthetablewillbeNULL,meaningthattheattributenotmakesenseforthespecificproduct.ToomanymeaningsforNULL28©2

009XiaojieYuanTranslatingSubclassEntities:TheRulesThreeapproaches:1.Object-oriented:eachentitybelongstoexactlyoneclass;createarelationforeachclass

,withallitsattributes.2.E/Rstyle:createonerelationforeachsubclass,withonlythekeyattribute(s)andattributesattachedtothatE.S.;entityrepresentedina

llrelationstowhosesubclass/E.S.itbelongs.3.Usenulls:createonerelation;entitieshavenullinattributesthatdon’tbelon

gtothem.29©2009XiaojieYuanExampleBeersAlesisanamemanfcolor30©2009XiaojieYuanObjectOrientednamemanfBudA

nheuser-BuschBeersnamemanfcolorSummerbrewPete’sdarkAlesBeersAlesisanamemanfcolor31©2009XiaojieYuanE/RStylenamemanfBudAnheuser-BuschSummerbrewPete’sBe

ersnamecolorSummerbrewdarkAlesBeersAlesisanamemanfcolor32©2009XiaojieYuanUsingNullsnamemanfcolorBudAnheuser-BuschNULLSummerbrewPete’sdarkBeers

BeersAlesisanamemanfcolor33©2009XiaojieYuanComparisons•O-Oapproachgoodforquerieslike“findthecolorofalesmadebyPete’s.”–JustlookinAlesre

lation.•E/Rapproachgoodforquerieslike“findallbeers(includingales)madebyPete’s.”–JustlookinBeersrelation.•Usingnullssavesspaceunlesstherearelot

sofattributesthatareusuallynull.34©2009XiaojieYuan数据模型三要素•数据结构–所研究的对象类型的集合。–通常按数据结构的类型来命名数据模型。•数据操作–是对数据库中各种对象（型）的实

例允许执行的操作集合•数据的约束条件–数据及其联系应具有的制约和依赖规则35©2009XiaojieYuan层次模型(HierarchicalModel)•层次模型的数据结构–用树形结构来表示实体之间联系的模型叫层次模型–层次数据库系统的典型代表是IBM公司的IMS系统36©2009Xiaojie

Yuan层次数据库实例D9计算机王平D2机械系张风S22材料S21机制S92系统结构S91软件080许超教授077正刚副教授076刘同讲师070杨岭教授109秦涛教授082戴月副教授045高风讲师83

2583178306834083218316c26...c24...c25...c22.....................系专业学生教师37©2009XiaojieYuan层次模型的数据操作•基本操作是记录的插入、删除、修改和检索

–层次模型支持三种检索•按层次顺序检索•按层次路径检索•在当前父记录内检索–插入操作•指定插入路径•指定插入记录的父记录–删除操作•指定删除记录–修改操作•在I/O区中准备好修改记录，然后找到被修改记录替换38©2009XiaojieYuan层次模型的数据约束•记

录之间的关联关系只限于一对多联系–采用虚拟记录型来反映多对多联系，例如学生与选课之间的多对多联系•它的全部数据必须以有序树的形式组织起来，任一记录型至多只能有一个父记录型，只有一个记录型没有父结点–如

何表达父亲与子女和母亲与子女之间的关系39©2009XiaojieYuan网状模型(NetworkModel)•网状模型的数据结构:–用有向图结构表示实体类型及实体间联系的数据模型–网状数据模型的典型代表是DBTG系统学号姓名年龄性别课程号名称学号课程号成绩学生s课程c学生成绩单s-

sc课程成绩单c-sc学生选课sc40©2009XiaojieYuan系的概念网状模型以系结构为基础，它是系的集合41©2009XiaojieYuan网状数据库实例S1C1AS1C2AS2C1AS2C3AS3C1AS3C2AS4C1AS4C2AS4C3

AS1S2S3S4C1C2C342©2009XiaojieYuan网状模型的数据操作•网状模型的数据操作分为对于数据记录的操作和对于系(set)的操作–对于记录的操作包括检索、插入、删除与修改–对于系的操作包括•把一个记录连入一个系值中•使一个记录脱离它所在的某个系值•将一个记录从一个系值

转入另一个系值43©2009XiaojieYuan网状模型的数据约束•支持记录码的概念•在任何时候任何一个系值中至多只有一个主记录•任何一个记录在同一系型中至多只能参与其中的一个系值–某个学生记录属于计算机系，就不能再属于物理系•可以支持双亲记录和子女记

录之间的某些约束条件44©2009XiaojieYuan关系模型概述•单一的数据结构--关系–任何一个关系数据库都是由若干张互相关联的表组成–关系模式和关系•关系操作–查询操作：选择、投影、连接、除、并、交、差–更新操作：增加、删除、修改–关系操作的特点是集合操作

方式•关系的三类完整性约束–实体完整性、参照完整性和用户定义的完整性45©2009XiaojieYuan实体完整性(EntityIntegrity)•若属性A是基本关系R的主属性，则属性A不能取空值•例如：–选修（学号，课程号，成绩）–人（身份证号

,姓名,家庭住址，出生日期，...）•实体应该是可区分的，主码是区分实体的唯一性标识，因此不能为空（即不能不知道）。46©2009XiaojieYuan参照完整性(ReferentialIntegrity)•设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S

的主码KS相对应，则称F是基本关系R的外码(foreignKey).–S(sno,name,age,sex,deptno),D(deptno,deptname)–S(sno,name,age),C(cno,cn

ame),SC(sno,cno,grade)•外码或者取空值，或者取参照关系中的主码值47©2009XiaojieYuan用户定义完整性(User-definedIntegrity)•用户定义的完整性就是针对某一具体关系数据库的约束条件。它反映某一具体应用所设计的数据必须满足的语义

要求。•例如：某个属性必须取唯一值，某些属性值之间应满足一定的函数关系，某个属性的取值范围等等。48©2009XiaojieYuanLevelsofAbstraction•Physicalleveldescribeshowarecord(e.g

.,customer)isstored.•Logicallevel:describesdatastoredindatabase,andtherelationshipsamongthedata.typecustomer=recordname:string;street:s

tring;city:integer;end;•Viewlevel:applicationprogramshidedetailsofdatatypes.Viewscanalsohideinformation(e

.g.,salary)forsecuritypurposes.49©2009XiaojieYuanViewofDataAnarchitectureforadatabasesystem50©2009XiaojieYuanTranslationReview•Basicc

ases–entitytotable,relationtotable–selectingattributesbasedonkeys•Specialcases–many-onerelationcanbemerged–mergingmany-many

isdangerous–translatingweakentitysets–translatingisahierarchy•3choices,withtrade-offs51©2009XiaojieYuanloan(loan_number,amount)p

ayment(loan_number,payment_number,payment_date,payment_amount)customer(name,address)borrows(name,loan_number)