数据库Database Systems(2)

2TheRelationalModelofDataAnOverviewofDataModelsBasicsoftheRelationalModelDefiningaRelationSchemainSQL*AnAlgebraicQueryLanguageConstraintsonRelations2.1AnOverviewofDataModelsAdatamodelisanotation(符号)fordescribingdataorinformation.Conceptualmodel(概念模型)Databasedatamodel:thedescriptiongenerallyconsistsofthreeparts:–Structureofthedatarelationalmodel=tables;networkandhierarchicalmodels=graphs/trees.–Operationsonthedata–Constraints(约束)onthedata2.1AnOverviewofDataModelsImportantDataModels:–TheRelationalModel,includingobject-relationalextensions;–TheSemistructured-dataModel,includingXML(扩展标记语言)andrelatedstandards;–NetworkandHierarchicaldataModelsHigh-LevelDatabaseModel–TheEntity/RelationshipModel–ObjectDefinitionLanguagetitleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedy2.2BasicsoftheRelationalModelRelation:–Therelationalmodelgivesusasinglewaytorepresentdata.–Atwo-dimensionaltablecalledarelation.–Eachrelationhasaname.Movies2.2BasicsoftheRelationalModelWhyRelations?–Providesasimple,limitedapproachtostructuringdata.–Providesalimited,yetuseful,collectionofoperationsondata.–Theyallowustoimplementlanguages,suchasSQL,thatenabletheprogrammertoexpresstheirwishesataveryhighlevel.2.2.1AttributesAttribute(属性):–Thecolumnsofarelationarenamedbyattributes.–Eachattributeofarelationhasaname,describesthemeaningofentriesinthecolumnbelow.–Anytwoattributesofarelationcan’thavesamename.titleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedyMoviesAttributes(columnheaders)2.2.2Schemas(模式)Relationschema=relationnameandthesetofattributes.–Example:Movies(title,year,length,genre)orMovies(title:string,year:integer,length:integer,genre:string)Wecanreordertheattributesofarelation,withoutchangingtherelation.–Example:Movies(year,title,length,genre)2.2.2Schemas(模式)Relationaldatabase=collectionofrelations.Relationaldatabaseschema=setofallrelationschemasinthedatabase.titleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedy2.2.3TuplesTuples(元组):–Therowsofarelation,otherthantheheaderrowcontainingtheattributenames,arecalledtuples.–Theremaybenotupleinarelation.–Atuplehasonecomponent(分量)foreachattributeoftherelation.Tuples(rows)Movies2.2.3TuplesHowtodescribeatuple?–Usecommastoseparatecomponents,anduseparentheses(圆括号)tosurroundthetuple.–Example:(‘StarWars’,1977,124,‘sciFi’)–Weshouldalwaysusetheorderinwhichtheattributeswerelistedintherelationschema.2.2.4DomainsDomains(域):–Thesetofallowedvaluesforeachattributeiscalledthedomainoftheattribute.–Attributevaluesare(normally)requiredtobeatomic,thatis,indivisible.–Adomainmustbeanelementarytype,suchasinteger,char(n),date,time.–Wecanrepresentaschemaas:Movies(title:string,year:integer,length:integer,genre:string)2.2.5EquivalentRepresentationsofaRelationRelationsaresetsoftuples,notlistsoftuples.–Orderoftuplesisirrelevant(无关的).–Anytuplecannotappearmorethanonceinarelation.Wecanreorderthetuplesofarelation,withoutchangingtherelation.Wecanreordertheattributesofarelation,withoutchangingtherelation.2.2.6RelationInstancesTheschemaofarelationisrelativelystatic,whilethetupleschangeovertime.Relationinstances(实例):–Asetoftuplesforagivenrelationisaninstanceofthatrelation.–Theinstanceofarelationchangesovertime.–Thesetoftuplesthatareintherelation“now”is“currentinstance(当前实例)”.2.2.7KeysofRelationsKey(键):–Asetofattributesformsakeyforarelationifwedonotallowtwotuplesinarelationinstancetohavethesamevaluesinalltheattributeofthekey.–Movies(title,year,length,genre)–employee-ID,Social-Securitynumber,student-ID,drivers’licensenumbersandautomobileregistrationnumber2.2.7KeysofRelationsKeyoftherelationMovies(title,year,length,genre,studioName,starName):–{title,year}?–{title,year,starName}2.2.8AnExampleDatabaseSchemaThedatabaseschemainthisbook:–Movies(title,year,length,genre,studioName,producerC#)–MovieStar(name,address,gender,birthdate)–StarsIn(movieTitle,movieYear,starName)–MovieExec(name,address,cert#,netWorth)–Studio(name,address,presC#)–cert#--Certificatenumber–MovieExec–Movieexecutant2.2.8AnExampleDatabaseSchemaWe'llbuildamarketingdatabasesystemforasalecompany(supermarket).Itwillmanageallthefollowinginformation:1.Managealldepartmentinformationinthecompany(suchasShanghaisaledepartment,JiangSusaledepartment).Alsomanageeverysalesmaninformationinthosedepartmentsincludingexclusiveemployeenumber,IDcardnumber,andsomeprivateinformation(suchasname,gender,birthdayandphonenumber).Bythewayoneofsalesmenwillactasthedepartmentmanagerinhisdepartment.2.2.8AnExampleDatabaseSchema2.Manageagroupofcustomers:name,province,city,companyname,phonenumber.3.Managealltheproductinformation:manufacturers(e.g.Chunlan,Hailer),types(e.g.motorcycle,airconditioner),specifications(e.g.MT125,RE1500),prices,descriptions.4.Managesalesorderwhichrecordeachdealhasbeendone.Notes:everysalesordercontainsanuniqueorderNo.,signdate,acorrespondingcustomer,asalesman,andatleastonekindofproducts.Anyproductintheordershouldhaveitsquantityandunitpricewhichwill