数据库系统概念习题课

习 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 单选题：10分填空题：10分简答题：12分关系代数运算：12分SQL：30分E-R图与转换：16分模式分解：10分上次考试各题型所占分数：Chapter7RelationalDatabaseDesign**SingleChoice Thedatabasesystemprovidesanabstractviewofthedata,thisisachievedthroughseverallevelofabstraction,amongtheselevels,the(________)describespartofthelogicalstructureofthedatabase.PhysicallevelLogicallevelViewlevelApplicationlevelC**SingleChoice Witchoneofthefollowingisnottrueforschemanormalization?(_________)Inrelationalmodel,anylegalrelationschemamustsatisfies1NFForanyrelationschema,thereisalwaysalossless-join,dependencypreservingdecompositioninto3NFsRelationswithonly3attributesareautomaticallyinBCNFIf,thenisatrivialfunctionaldependencyC**FillIntheFollowingBlanks DatabasesystemprovidesDataDefinitionLanguage(DDL)tospecifydatabaseschema,and(__DML_____)toexpressdatabasequeriesandupdates. Collectionofoperationsthatformasinglelogicalunitofworkindatabasesystemarecalled(_Transaction____).**AnswerTheFollowingQuestions Brieflydescribethedifferencebetweendatabaseschemaanddatabaseinstance. Brieflydescribethedifferencebetweentablesandviews.**RelationalAlgebraR4=ΠA(R)—(R÷S)**ComposeSQL Considerthedatabaseofaclinicdescribedbelow(theprimarykeysareunderlined): Patient(PatCode,Name,Age,Sex,Street,City,Occupation) Operation(OperCode,PatCode,DoctCode,OperDate,OperTime,Detail) Doctor(DoctCode,Name,Specialty,Age) Diagnosed_By(PatCode,DoctCode,DiagDate,DiagTime,Prescription)**ComposeSQLDefinetherelationschemaOperationinSQL(indicatesPrimaryKeyandForeignKey)Listallpatientsdiagnosedoroperatedbydoctor“smith”Findalldoctorswhohavediagnosedallpatientswho’sagearebelow12.**CreateTableOperation(OperCodeChar(30),PatCodeChar(15),DocCodeChar(15),DiagDateDate,DiagTimeTime,DetailVarChar(255),PrimaryKey(OperCode),ForeignKey(PatCode)ReferencesPatient,ForeignKey(DocCode)ReferencesDoctor)1.DefinetherelationschemaOperationinSQLChapter7RelationalDatabaseDesign**2.UsingUnion(SelectPatient.NameFromDoctor,Operation,PatientWhereDoctor.DocCode=Operation.DocCodeANDOperation.PatCode=Patient.PatCodeANDDoctor.Name=‘Smith’)Union(SelectPatient.NameFromDoctor,Diagnosed_by,PatientWhereDoctor.DocCode=Diagnosed_by.DocCodeANDDiagnosed_by.PatCode=Patient.PatCodeANDDoctor.Name=‘Smith’)Listallpatientsdiagnosedoroperatedbydoctor“smith”Chapter7RelationalDatabaseDesign**2.UsingSubquerySelectPatient.NameFromPatientWhereExists(Select*FromDoctor,OperationWhereDoctor.DocCode=Operation.DocCodeANDDoctor.Name=‘Smith’ANDOperation.PatCode=Patient.PatCode)ORExists(Select*FromDoctor,Diagnosed_byWhereDoctor.DocCode=Diagnosed_by.DocCodeANDDoctor.Name=‘Smith’ANDDiagnosed_by.PatCode=Patient.PatCode)Listallpatientsdiagnosedoroperatedbydoctor“smith”Chapter7RelationalDatabaseDesign**3.UsingSubquerySelectDoctor.NameFromDoctorDWhereNotExists(Select*FromPatientPWhereAge<12ANDNotExists(Select*FromDiagnosed_byWhereDiagnosed_by.DocCode=D.DocCodeANDDiagnosed_by.PatCode=P.PatCode))Findalldoctorswhohavediagnosedallpatientswho’sagearebelow12.Chapter7RelationalDatabaseDesign**3.UsingSetOperationsSelectDoctor.NameFromDoctorDWhereNotExists((SelectPatient.PatCodeFromPatientWherePatient.Age<12)EXCEPT(SelectDiagnosed_by.PatCodeFromDiagnosed_byWhereDiagnosed_by.DocCode=D.DocCode))Findalldoctorswhohavediagnosedallpatientswho’sagearebelow12.Chapter7RelationalDatabaseDesign**E-RModel TheChineseFilmInstitutecollectsstatisticsaboutmoviesandmoviegoersinchina.Thesestatisticsisstoredinadatabase. Thisdatabasehasthefollowingentitysets-Movies,withattributesmovie_id,movie_name,production_year,country.-MovieTheaters,withattributestheater_id,thearter_name,city.-Screen,withattributesscreen_id,screen_name.Itisaweakentityset,itsidentifyingentitysetisMovieTheaters,itsdiscriminatorisscreen_id.(movietheatercontainsoneormorescreens,wherethemoviesareshown.)**E-RModel Inadditiontherearethefollowingrelationshipsets:-In,TheidentifyingrelationshipsetbetweenScreenandMovieTheaters.-Shows,Themoviesareshowedondifferentscreens,andweneedtostoredataaboutthemovieshowsthathaveoccurred:whichmoviethatwasshown,onwhichscreen,atwhattime,andhowmanyvisitorstherewere.**E-RModelDesignanEntity-Relationshipdiagramwiththegivenentitysetsandrelationshipsets.TranslatetheE-Rdiagramintorelationschemas.*Chapter7RelationalDatabaseDesign*Movie1.ShowChapter7RelationalDatabaseDesign**Movie(Movie_id,Movie_name,production_year,country)Theater(theater_id,theater_name,city)Screen(theater_id,screen_id,screen_name)Show(movie_id,theater_id,screen_id,date,visitor_number)2.Chapter7RelationalDatabaseDesign**SchemaNormalizationGivenisthefollowingsetoffunctionaldependenciesFforarelationschemaR:R={A,B,C,D,E,G}F={ED,CB,CEG,BA}Attributeset(C,E)istheonlycandidatekeyofR.DecidewhetherrelationschemaRisinBCNF.Ifitisnot,describewhyDecomposerelationsR,ifnecessary,intoacollectionofrelationsthatareinBCNF.Makesuretoindicatewhichdependencyyouapplytoeachdecomposition.**R={A,B,C,D,E,G}F={ED,CB,CEG,BA}RisNotinBCNF∵EDisnotatrivialfunctionaldependency,andEisnotasuperkeyED:(E,D),(A,B,C,E,G)CB:(C,B),(A,C,E,G)RCB,BA∴CA:(C,A),(C,E,G)FinalResult:(E,D),(C,B),(C,A),(C,E,G)1.2.Chapter7RelationalDatabaseDesign*********************