第4章 关系数据库设计理论答案
数据库原理知识总结和期末试卷
数据库知识要点归纳第1章数据库基础知识1.数据库（DB）是一个按数据结构来存储和管理数据的计算机软件系统。
数据库是长期储存在计算机内的、有组织的、可共享的数据集合。
数据库管理数据两个特征：1.数据整体性 2.数据库中的数据具有数据共享性2.数据库管理系统（DBMS）是专门用于管理数据库的计算机系统软件3.数据库应用系统是在数据库管理系统（DBMS）支持下建立的计算机应用系统，简写为DBAS。
数据库应用系统是由数据库系统、应用程序系统、用户组成的。
例如，以数据库为基础的财务管理系统、人事管理系统、图书管理系统,成绩查询系统等等。
4.数据库系统DBS是一个实际可运行的存储、维护和应用系统提供数据的软件系统，是存储介质、处理对象和管理系统的集合体。
它通常由软件、数据库和数据管理员组成。
5.数据库中数据独立性数据和程序之间的依赖程度低，独立程度大的特性称为数据独立性高。
1、数据的物理独立性数据的物理独立性是指应用程序对数据存储结构的依赖程度。
2、数据的逻辑独立性数据的逻辑独立性是指应用程序对数据全局逻辑结构的依赖程度。
6.数据库的三级模式是模式、外模式、内模式。
1.模式（Schema）一个数据库只有一个模式 2.外模式（External Schema）一个数据库有多个外模式。
3.内模式（Internal Schema）一个数据库只有一个内模式。
7.数据库系统的二级映象技术第2章数据模型与概念模型1.实体联系的类型：一对一联系（1:1）一对多联系（1:n）多对多联系（m:n）2.E-R图描述现实世界的概念模型，提供了表示实体集、属性和联系的方法。
长方形表示实体集椭圆形表示实体集的属性菱形表示实体集间的联系3.数据模型的三要素数据结构、数据操作、数据约束条件数据结构分为：层状结构、网状结构和关系结构常见的数据模型：层次模型、网状模型和关系模型。
层次模型用树形结构来表示各类实体以及实体间的联系１第3章数据库系统的设计方法1.数据库系统设计应分6个阶段进行，这6个阶段是需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施和数据库运行和维护。
数据库习题答案
《数据库习题答案》来自五星文库点这里，有很多篇《数据库习题答案》在线阅读本文：数据库习题答案导读：第三章习题，1．关系数据库设计理论，数据依赖范式和关系模式的规范化设计方法，其中数据依赖起着核心的作用，2．关系数据库中的关系模式至少要满足第一范式，如果每个属性值都是不可再分的最小数据单位，（2）试分析模式R的数据冗余问题，关系R中的C属性会存在在数据冗余，相应地原来存储在一张二维表内的数据就要分散存储到多张二维表中，第四章习题，A删除基本表B修改基本表中的数据，A数据项B 元组，C表D数据库第三章习题一、单项选择题1．在关系模型R中，函数依赖X→Y的语义是（ B ）A．在R的某一关系中，若两个元组的X值相等，则Y值也相等B．在R的每一关系中，若两个元组的X值相等，则Y值也相等C．在R的某一关系中，X值应与Y值相等D．在R的每一关系中，X值应与Y值相等2．设学生关系模式为：学生（学号，姓名，年龄，性别，成绩，专业），则该关系模式的主键是（ B ）A．性别 B．学号C．学号，姓名 D．学号，姓名，性别3．如果X→Y（Y不包含于X，且Y不能决定X）和Y→Z成立，那么X→Z成立。
这条规则称为（ B ）A．自反律 B．传递律C．伪传递律 D．增广律4．关系模式R?2NF，则R一定是（b ）A．1NF B．3NF5．设一关系模式为：运货路径（顾客姓名，顾客地址，商品名，供应商姓名，供应商地址），则该关系模式的主键是（ C ）A．顾客姓名，供应商姓名，供应商地址 B．顾客姓名，商品名C．顾客姓名，供应商姓名，商品名 D．顾客姓名，顾客地址6．下列有关范式的叙述中正确的是（ B ）A．如果关系模式R?1NF，且R中主属性完全函数依赖于主键，则R是2NFB．如果关系模式 R?3NF，则R?2NF一定成立C．如果关系模式R?1NF，则只要消除了R中非主属性对主键的传递依赖，则R可转换成2NFD．如果关系模式R?1NF，则只要消除了R中非主属性对主键的部分依赖，则R可转换成3NF7．关系模式学生（学号，课程号，名次），若每一名学生每门课程有一定的名次，每门课程每一名次只有一名学生，则以下叙述中错误的是（ B ）A．（学号，课程号）和（课程号，名次）都可以作为候选键B．只有（学号，课程号）能作为候选键C．该关系模式属于第三范式D．该关系模式属于BCNF8．已知关系模式R（ABCD），F={A→C，B→C，C→D }，则以下成立的是（ B ）A．A→B B．A→DC．AD→BC D．AC→BD9．如果X→Y且Z?U成立，那么XZ→YZ成立，这条规则称为（ D ）A．自反律 B．传递律`C．伪传递律 D．增广律10．能够消除多值依赖引起的冗余是（ D ）A．1NF B．2NF二、填空题1．关系数据库设计理论，数据依赖范式和关系模式的规范化设计方法。
完整word版数据库系统基础教程第四章答案
SolutionsChapter 44.1.14.1.2b)c)In c we assume that a phone and address can only bel ong to a sin gle customer (1-d)In d we assume that an address can only bel ong to one customer and a phone can exist at only one address.If the multiplicity of above relati on ships were m-to-n, the en tity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the en tity set.In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design, we must con sider more efficie nt alter natives.In stead of query ing multiple tables where key values are duplicated, we can also modify attributes:(i)P hones attribute can be con verted into HomePho ne, OfficePh one and CellPh one.(ii) A multivalued attribute such as alias can be kept as an attribute where a sin gle colu mn can be used in relati onal desig n i.e. con cate nate all values. SQL4.1.34.1.4a)c)The relati on ship "played" betwee n Teams and Players is similar to relati on ship "plays" betwee n Teams and Players.b) 1 >: i .-I T mains Oo L UJT 曰 Pla.yor3uid^r 4.1.5 I ■acxnnxzres4.1.6 The in formatio n about childre n can be ascerta ined from motherOf and fatherOf relati on ships. Attribute ssNo is required since n ames are not uni que.People4.1.74.1.8a)(b)Family name Student;a 4.1.9 Assumpti ons A Professor only works in at most one departme nt. A course has at most one TA. A course is only taught by one professor and offered by one departme nt. Stude nts and professors have bee n assig ned uni que email ids. A course is uniquely identified by the course no, section no, and semester (e.g CS157-3 spring 09). ■ — Professors4.1.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one studio.But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.421Redu ndan cy: The owner address is repeated in AccSets and Addresses en tity sets. Simplicity: AccSets does not serve any useful purpose and the desig n can be more simply represe nted by creati ng man y-to-ma ny relati on ship betwee n Customers and Accou nts.Right kind of element: The entity set Addresses has a single attribute address.A customer cannot have more tha n one address.Hence address should be an attribute of en tity set Customers.Faithfulness: Customers cannot be uniquely identified by their names. In real world Customers would have a unique attribute such as ssNo or customerNo4.2.2Studios and Preside nts can be comb ined into one en tity set Studios with Preside nts beco ming an attribute of Studios un der follow ing circumsta nces: 1. The Presidents entity set only contains a simple attribute viz. presidentName. Additi onal attributes specific to Preside nts might justify making Preside nts into an en tity set.4.2.34.2.4 The en tity sets should have sin gle attribute.a)Stars: starNameb)Movies: movieNamec)Studios: studioName. However there exists a man y-to-ma ny relati on ship betwee n Studios and Con tracts. Hen ce, in additi on, we n eed more in formati on about studios in volved. If a con tract always in volves two studios, two attributes suchas produc in gStudio and starStudio can replace theStudios en tity set. If a con tact can be associated with at most five studios, it may be possible to replace the Studios en tity set by five attributes viz. studio1, studio2, studio3, studio4, and studio5. Alter nately, a composite attribute containing con cate nati on of all studio n ames in a con tact can be con sidered. A separator character such as "$" can be used. SQL allows search ing of such an attribute using query like '%keyword%'4.2.5From Augme ntati on rule of Fun cti onal Depe ndency, give nB -> M (B=Baby, M=Mother)the nBND -> M (N=Nurse, D=Doctor)Hence we can just put an arrow en ter ing mother.a) Put an arrow entering entity set Mothers for the simplest solution (As in fig.4.4, where a multi-way relati on ship was allowed, eve n though Movies alone could identify the Studio). However, we can display more accurate information with below figure.b)Mothersc)Aga in from Augme ntati on rule of Fun cti onal Depe ndency,give nBM -> Dthe nBMN -> DThus we can just add an arrow entering Doctors to fig 4.15. Below figure426a)Birthsb) Tran sitivity and Augme ntati on rules of Fun cti onal Depe ndency allow arrow en teri ng Mothers from Births. However, a new relati on ship in below figureDesig n flaws in abc above 1. As suggested above, using Tran sitivity and Augme ntati on rules of Fun cti onal Depe nden cy, much simpler desig n is possible.427In below figure there exists a many-to-one relati on ship betwee n Babies and Births and ano ther many-to-one relati on ship betwee n Births and Mothers. From tran sitivity of relati on ships, there is a many-to-one relati on ship betwee nBabies and Mothers. Hence a baby has a uni que mother while a birth can allowb)A captai n cannot exist without a team. However a player can (free age nt). A rece ntly formed (or defu net) team can exist without players or colors.c)Childre n can exist without mother and father (u432a)The keys of both E1 and E2 are required for uni quely ide ntify ing tuples in Rb)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Special Case: All en tity sets have arrows going into them i.e. all relati on ships are 1-to-1Any KiOtherwise: Comb in ati on of all Ki's where there does not exist an arrow going from R to Ei.4.4.1No, grade is not part of the key for en rollme nts. The keys of Stude nts and442It is possible to make assig nment n umber a weak key of En rollme nts but this is not good desig n (redu ndancy since multiple assig nments corresp ond to a course). A new entity set Assignment is created and it is also a weak entity set. Hence the key attributes of Assig nment will come from the str ong en tity sets to whichEn rollme nts is conn ected i.e. stude ntID, dept, and CourseNo.a)b)4444.5.1Customers(SSNo, name,addr,pho ne)Flights (n umber,day,aircraft)Booki ngs(custSSNo,flightNo,flightDay,row,seat)Relati ons for toCust and toFlt relati on ships are not required since the weak en tity set Book ings already contains the keys of Customers and Flights.⑻(b)Schema is cha nged. Since toCust is no Ion ger an ide ntify ing relati on ship, SSNo is no longer a part of Bookings relation.Booki ngs(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)The above relati ons are merged intoBooki ngs(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no lon ger a key of Book ings relati on .It becomes a foreig n key in stead.4.5.3Ships(name, yearLaunched)SisterOf(name, sisterName)4.5.4(a)Stars(name,addr)Studios(name,addr) Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram, studioName may not be required as a key of Contracts (or not even required as an attribute of Contracts).(b)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name) Courses(deptName,number)(d)Leagues(name)Teams(leagueName,teamName) Players(leagueName,teamName,playerName)4.6.1The weak relation Courses has the key from Depts along with number. Hence there is no relation for GivenBy relationship.(a)Depts(name, chair)Courses(number, deptName, room) LabCourses(number, deptName, allocation)(b)LabCourses has all the attributes of Courses.Depts(name, chair) Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)(c)Courses and LabCourses are combined into one relation.Depts(name, chair)Courses(number, deptName, room, allocation)4.6.2(a)Person(name,address)ChildOf(personName,personAddress,childName,childAddress)Child(name,address,fatherName,fatherAddress,motherName,motherAddresss)Father(name,address,wifeName,wifeAddresss)Mother(name,address)Since FatherOf and MotherOf are many-one relationships from Child, there is no need for a separate relation for them. Similarly the one-one relationship Married can be included in Father (or Mother). ChildOf is a many-many relationship and needs a separate relation.However the ChildOf relation is not required since the relationship can be deduced from FatherOf and MotherOf relationships contained in Child relation.(b)A person cannot be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and FatherOf could be added as attributes to PersonChild,PersonChildFather, and PersonChildMother relations. Similarly the Married relation can be added as attributes to PersonChildMother and PersonMother (or the corresponding father relations).(c)For the Person relation at least one of husband and wife attributes will be null. Person(personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeName,wifeAddresss,husbandName,husbandAddress)ChildOf(personName,personAddress,childName,childAddress)4.6.3(a)People(name,fatherName,motherName)Males(name)Females(name)Fathers(name)Mothers(name)ChildOf(personName,childName)(b)People(name)PeopleMale(name)PeopleMaleFathers(name)PeopleFemale(name)PeopleFemaleMothers(name)ChildOf(personName,childName)FatherOf(childName,fatherName)MotherOf(childName,motherName)People cannot belong to both male and female branch of the ER diagram.Moreover since an entity belongs to one and only one class when using object- oriented approach, no entity belongs to People relation.Again we could replace MotherOf and FatherOf relations by adding as attributes to PeopleMale,PeopleMaleFathers,PeopleFemale, and PeopleFemaleMothers relations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)4.6.4(a)Each entity set results in one relation. Thus both the minimum and maximum number of relations is e.The root relation has a attributes including k keys. Thus the minimum number of attributes is a. All other relations include the k keys from root along with their a attributes. Thus the maximum number of attributes is a+k.(b)The relation for root will have a attributes. The relation representing the whole tree will have e*a attributes.The n umber of relati ons will depe nd on the shape of the tree. A tree of e entities where only one child exists(say left child only) would have the minimum number of relations. Thus below figure will only contain 4 subtrees that contain rootE1,E1E2,E1E2E3, and E1E2E3E4. With e entity sets, minimum e relations are possible.E 1JET型The maximum n umber of subtrees result whe n all the en tities(except root) are at depth 1. Thus below figure will contain 8 subtrees that contain rootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,and E1E2E3E4. With e entity sets, maximum 2A(e-1) relati ons are possible.(C)The nulls method always results in one relation and contains attributes from all e en tities i.e. e*a attributes. Summariz ing for a,b, and c above;#Comp onents #Relati onsMin Max Min MaxMethodstraight-E/R a a e eobject-orie nted a e*a e 2A(e-1)nulls e*a e*a 1 14.7.1b)c)d)MotherOf FatherOf4.7.5Males and Females subclasses are complete. Mothers and Fathers are partial. All subclasses are disj oint.Gradesa ProfessorsShips0…0■•出4.7.7 Works+email: PKTeach4.7.6CoursesOffers +no: PK +3ection : PK +s 巳m 巳s 七巳工：PJKsi^terShip original ShipWe con vert the ternary relati on ship Con tracts into three bi nary relati on ships betwee n a new en tity set Con tracts and existi ng en tity sets.Studios+n^me: PK ±addressMothers 0」Doctors-------------------Child +patient.TC: PK+doc-torIE: E 医4-add.res 5+ A dfir R.R a1- .1Births p. >Bdby Of +1 j_meOf Bi r th DoctcrFor.,Babies --- Nurses+babyID: PK u _ _ - ------------------------------ 1—+riur3eID ；： FK+addxes s NurseFor +羁立Movies +七itle: PK+year: PK+lengthContracts4.7.10A self-associati on Pare ntOf for en tity set people has multiplicity 0..2 at pare nt role end. In a Library database, if a patr on can loa n at most 12 books, them multiplicity is 0..12.For a FullTimeStude nts en tity set, a relati on ship of multiplicity 5..* must exist with Courses (A stude nt must take at least5 courses to be classified FullTime.Mothers^-patient ID: EK+己€1£1匸吕曰sMotherOfDoctors+doc 七口H ID: PKBaby OfBabies ♦+babyID: PK +addres3Births0.+1 iineOfBijsthDoctorForNurses+nuraeID : PKNurseForc)Customers(SSNo,name,addr,phone)Flights(number,day,aircraft) Bookings(row,seat,custSSNo,FlightNumber,FlightDay)Customers("SSNo",name,addr,phone) Flights("number","day",aircraft)Bookings(row,seat,"custSSNo","FlightNumber","FlightDay")4.8.2a)Movies(title,year,length,genre)Studios(name,address)Presidents(cert#,name,address) Owns(movieTitle,movieYear,studioName)Runs(studioName,presCert#)Movies("title","year",length,genre)Studios("name",address)Presidents("cert#",name,address) Owns("movieTitle","movieYear",studioName)Runs("studioName",presCert#)b)Since the subclasses are disjoint, Object Oriented Approach is used. The hierarchy is not complete. Hence four relations are required Movies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon) Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,genre,weapon)Movies("title","year",length,genre) MurderMysteries("title","year",length,genre,weapon)Cartoons("title","year",length,genre) Cartoon-MurderMysteries("title","year",length,genre,weapon)c)Customers(ssNo,name,phone,address)Accounts(number,balance,type)Owns(custSSNo,accountNumber)Customers("ssNo",name,phone,address)Accounts("number",balance,type)Owns("custSSNo","accountNumber")Teams(name,captainName)Players(name,teamName)Fans(name,favoriteColor)Colors(colorname)For Displays association,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)Teams("name",captainName)Players("name",teamName) Fans("name",favoriteColor)Colors("colorname")For Displays association,TeamColors("teamName","colorname")RootsFor("fanName","teamName")Admires("fanName","playerName")e)People(ssNo,name,fatherSSNo,motherSSNo)People("ssNo",name,fatherssNo,motherssNo)f)Students(email,name)Courses(no,section,semester,professorEmail)Departments(name)Professors(email,name,worksDeptName) Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)Students("email",name)Courses("no","section","semester",professorEmail)Departments("name")Professors("email",name,worksDeptName) Takes(letterGrade,"studentEmail","courseNo","courseSection","courseSemester")4.8.3a)Each and every object is a member of exactly one subclass at leaf level. We have nine classes at the leaf of hierarchy. Hence we need nine relations.b)All objects only belong to one subclass and its ancestors. Hence, we need not consider every possible subtree but rather the total number of nodes in tree. Hence we need thirteen relations.c)We need all possible subtrees. Hence 218 relations are required.class Customer (key (ssNo)){attribute integer ssNo;attribute string name;attribute string addr;attribute string phone;relationship Set<Account> ownsAccts inverse Account::ownedBy;};class Account (key (number)){attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedBy inverse Customer::ownsAccts;};4.9.2a)Modify class Account to contain relationship Customer ownedBy (no Set)b)Also remove set in relationship ownsAccts of class Customer.c)ODL allows a collection of primitive types as well as structures. To classCustomer add following attributes in place of simple attributes addr and phone: Set<string phone> Set<Struct addr{string street,string city,string state}>d)ODL allows structures and collections recursively.Set<Struct addr{string street,string city,string state},Set<string phone>>Collections are allowed in ODL. Hence, Colors Set can become an attribute of Teams.class Colors(key(colorname)){attribute string colorname;relationship Set<Fans> FavoredBy inverse Fans::Favors;relationship set<Teams> DisplayedBy inverse Teams::Displays;};class Teams(key(name)){ attribute string name;relationship set<Colors> Displays inverse Colors::DisplayedBy;relationship set<Players> PlayedBy inverse Players::Plays;relationship PLayers CaptainedBy inverse Platyers::Captains;relationship set<Fans> RootedBy inverse Fans::Roots;};class Players(key(name)){attribute string name; relationship Set<Teams> Plays inverse Teams::PlayedBy;relationship Teams Captains inverse Teams::CaptainedBy;relationship Set<Fans> AdmiredBy inverse Fans::Admires;};class Fans(key(name)){attribute string name; relationship Colors Favors inverse Colors::FavoredBy;relationship Set<Teams> RootedBy inverse Teams::Roots;relationship Set<Players> Admires inverse Players::AdmiredBy;};4.9.4class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale; relationship Person fatherOfinverse Person::childrenOfMale; relationship Set<Person> childreninverse Person::parentsOf; relationship Set<Person> childrenOfFemaleinverse Person::motherOf; relationship Set<Person> childrenOfMaleinverse Person::fatherOf; relationship Set<Person> parentsOf inverse Person::children; };4.9.5The struct education{string degree,string school,string date} cannot have duplication.Hence use of Sets does not make any different as compared to bags, lists, or arrays.Lists will allow faster access/queries due to the already sorted nature.4.9.6a)class Departments(key (name)) { attribute string name;relationship Courses offersinverse Courses::offeredBy;};class Courses(key (number,offeredBy)) { attribute string number;relationship Departments offeredByinverse Departments::offers;};b)class Leagues (key (name)) { attribute name;relationship Teams containsinverse Teams::belongs;};class Teams(key (name,belongs)) {attribute name, relationship Leagues belongs inverse Leagues::contains;relationship Players playinverse Players::plays;};class Players (key(number,plays)) { attribute number, relationship Teams playsinverse Teams::play;};4.9.7class Students (key email) {attribute string email;attribute string name; relationship Courses isTAinverse Courses::TA;relationship Courses Takesinverse Courses::TakenBy;};class Professors (key email) {attribute string email;attribute string name; relationship Departments WorksForinverse Department::Works;relationship Courses Teachesinverse Courses::TaughtBy;};class Courses (key (no,semester,section)) {attribute string no;attribute string semester;attribute string section; relationship Students TA inverse Students::isTA;relationship Students TakenByinverse Students::Takes;relationship Professors TaughtByinverse Professors::Teaches;relationship Departments OfferedByinverse Departments::Offer;};class Departments (key name) {attribute name; relationship Courses Offer inverse Courses::OfferedBy;relationship Professors Worksinverse Professors::WorksFor;};4.9.8A relationship is its own inverse when for every attribute pair in the relationship, the inverse pair also exists. A relation with such a relationship is called symmetric in set theory. e.g. A relationship called SiblingOf in Person relation is its own inverse.4.10.1a)Customers(ssN o,n ame,addr,ph one)Accou nt(n umber,type,bala nee)Own s(ssNo,accou ntNumber)b)Accou nts( number,bala nce,type,ow nin gCustomerssNo)Customers(ssNo, name)Addresses(ow nerssNo,street,state,city)Phon es(ow nerssNo,street,state,city,ph on earea,ph oneno)We can remove Addresses relation since its attributes are a subset ofrelation Phon es.numberc)Fans(name,colors)RootedBy(fan_name,teamname)Admires(fan_name,playername)Players(name,teamname,is_captain) Teams(name)--remove subset of teamcolor Teamcolors(name,colorname) Colors(colorname)d)class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale;relationship Person fatherOfinverse Person::childrenOfMale;relationship Set<Person> childreninverse Person::parentsOf;relationship Set<Person> childrenOfFemaleinverse Person::motherOf;relationship Set<Person> childrenOfMaleinverse Person::fatherOf;relationship Set<Person> parentsOfinverse Person::children;};Person(name,mothername,fathername)The children relationship is many-many but the information can be deduced from Person relation. Hence below relation is redundant.Parent-Child(parent, child)4.10.2First consider each struct as if it were an atomic value i.e. key and value association pairs can be treated as two attributes. After applying normalization, the attributes can be replaced by the fields of the structs.4.10.3(a)Struct Card { string rank, string suit };(b)class Hand {attribute Set theHand;};(c)Hands(handId, rank, suit)Each tuple corresponds to one card of a hand. HandId is required key to identify a hand.(d)Hand contains an array of 5 elements class PokerHand{attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)}PokerHandS(handId,rank1,suit1,,rank2,suit2,rank3,suit3,rank4,suit4,rank5,suit5 )(e)class Deal { attribute Set <Struct PlayerHand { string Player, Hand theHand }> theDeal;}(e)P okerDeal consist of a player and array of five card deal.class PokerDeal{string Player,attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)}(f)Above can similarly be represented by key player and a value consistingof five element array.(h)dealID is a key for Deals. Thus the relations for classes Deals and Hands are: Deals(dealID, player, handID) Hands(handID, rank, suit)A simpler relation Deals below can also represents the classes:Deals(dealID, player, rank, suit)(i)The relation Deals(dealID,card) cannot identify the hand to which a card belongs. Also two attributes are required for a card;its rank and suit.Deals(dealID, handID, rank, suit)4.10.4(a)C(a, f, g)(b)C(a, f, g, count)(c)C(a, f, g, position)(d)C(a, f, g, i, j)。
数据库原理与应用---习题解析
数据库的概述1. 是存储在计算机内有结构的数据的集合。
Ａ.数据库系统Ｂ.数据库Ｃ.数据库管理系统Ｄ.数据结构2.在数据库中存储的是。
Ａ.数据库中存储的是。
Ｂ.数据模型Ｃ.数据以及数据之间的联系Ｄ.信息3.数据库中,数据的物理独立性是指。
Ａ.数据库与数据库管理系统的相互独立Ｂ.用户程序与DBMS的独立Ｃ.用户的应用程序与存储在磁盘上数据库中的数据是相互独立的Ｄ.应用程序与数据库中数据的逻辑结构相互独立4.下述关于数据库系统的正确叙述是。
Ａ.数据库系统减少了冗余Ｂ.数据库系统避免了一切冗余Ｃ.数据库系统中数据的一致性是指数据类型一致Ｄ.数据库系统比文件系统能管理更多的数据5.数据库技术中采用分级方法将数据库的结构划分成多个层次,是为了提高数据库的Ａ.数独立性Ｂ.逻辑独立性Ｃ.数据的共享Ｄ.物理独立性6.在数据库技术中,为了提高数据库的逻辑独立性和物理独立性,数据库的结构被划分成用户及级、和存存储级三个层次。
Ａ.管理员级Ｂ.外部级Ｃ.概念级Ｄ.内部级7.在数据库中,产生不致的原因是。
Ａ.数据存储量太大Ｂ.没有严格保护数据Ｃ.未对数据进行完整性控制Ｄ.数据冗余8.数据库管理系统中用于定义和描述数据库逻辑结构的语言称为。
Ａ.数据库描述语言Ｂ.数据库子语言Ｃ.数据操纵语言Ｄ.数据结构语言9.数据库系统的核心是。
Ａ.编译系统Ｂ.数据库Ｃ.操作系统Ｄ.数据库管理系统10.数据库管理系统能实现对数据库数据的查询、插入修改和删除等操作，这种功能称为。
Ａ.数据定义功能Ｂ.数据管理功能Ｃ.数据操纵功能Ｄ.数据控制功能11.在数据库的三级模式结构中,描述数据库中全体数据的全局逻辑结构和特征的是Ａ.外模式Ｂ.内模式Ｃ.存储模式Ｄ.模式12.数据库系统的独立性是指。
Ａ.不会因为数据的变化而影响应用程序Ｂ.不会因为系统数据存储结构与数据逻辑结构的变化而影响应用程序Ｃ.不会因为存储策略的变化而影响存储结构Ｄ.不会因为某些存储结构的变化而影响其他的存储结构13.实体是信息世界中的术语,与之对应的数据术语为。
数据库系统原理2018版课后答案
1.数据库系统原理2018版课后答案第一章数据库系统概述选择题B、B、A简答题1请简述数据数据库。
数据库管理系统数据库系统的概念。
P27数据是描述事物的记录符号，是指用物理符号记录下来的，可以鉴别的信息。
数据库即存储数据的仓库，严格意义上是指长期存储在计算机中的有组织的、可共享的数据集合。
数据库管理系统是专门用于建立和管理数据库的一夺软件。
介于应用程序和操作系统之间。
数据库系统是指在计算机中引入数据库技术之后的系统，包括数据库、数据库管理系统及相关实用工具、应用程序、数据库管理员和用户。
2请简述早数据库管理技术中，与人工管理、文件系统相比，数据库系统的优点。
数据共享性高数据冗余小易于保证数据一致性数据独立性高可以实施统一管理与控制减少了应用程序开发与维护的工作量3请简述数据库系统的三级模式和两层映像的含义。
P31答:数据库的三级模式是指数据库系统是由模式、外模式和内模式三级工程的。
对应了数据的三级抽象。
两层映像是指三级模式之间的映像关系，即外模式/模式映像和模式/内模式映像。
4请简述关系模型与网状模型、层次模型的区别。
P35使用二维表结构表示实体及实体间的联系建立在严格的数学概会的基础上概念单一、统一用关系表示实体和实体之间的联系，数据结构简单清晰。
用户易懂易用存取路径对用户透明，具有更高的数据独立性、更好的安全保密性。
第二章关系数据库选择题C、C、D简答题1.请简述关系数据库的基本特征。
P48答:关系数据库的基本特征是使用关系数据模型组织数据。
2.请简述什么是参照完整性约束。
P55答:参照完整性约束是指:若属性或属性组F是基本关系R的外码，与基本关系S的主码K相对应。
则对于R中每个元组在F上的取值只允许有两种可能，要么是空值，要么与S中某个元组的主码值对应。
3请简述关系规范化过程。
答:对于存在数据冗余、插入异常，删除异常问题的关系机式，应采取将一个关系模式分为多个关系模式的方法进行处理。
一个低一级范式的关系模式。
数据库习题库
第1章绪论1.数据库数据具有__________、__________和__________三个基本特点。
答案：永久存储、有组织、可共享2.试述数据、数据库、数据库系统、数据库管理系统的概念。
3.使用数据库系统有什么好处？4.数据库管理系统是数据库系统的一个重要组成部分，它的功能包括__________、__________、__________、__________。
答案：数据定义功能、数据操纵功能、数据库的事物管理和运行管理、数据库的建立和维护功能5.数据库系统是指在计算机系统中引入数据库后的系统，一般由__________、__________、__________和__________构成。
答案：数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员6.试述文件系统与数据库系统的区别和联系。
7.数据库管理技术的发展是与计算机技术及其应用的发展联系在一起的，它经历了三个阶段：__________阶段，__________阶段和__________阶段。
答案:人工管理、文件系统、数据库系统8.举出适合用文件系统而不是数据库系统的例子；再举出适合用数据库系统的应用例子。
9.数据库具有数据结构化、最小的__________、较高的__________和易扩展性等特点。
答案：冗余度、数据独立性10试述数据库系统的特点。
11.DBMS还必须提供__________保护、__________检查、__________、__________等数据控制功能。
答案：数据的安全性数据的完整性并发控制数据库恢复12.数据库管理系统的主要功能有哪些？答案：①数据库定义功能；②数据存取功能；③数据库运行管理；④数据库的建立和维护功能。
13.模式（Schema）是数据库中全体数据的__________和__________的描述，它仅仅涉及到__________的描述，不涉及到具体的值。
答案：逻辑结构、特征、型14.试述数据模型的概念、数据模型的作用和数据模型的三个要素。
郑大计算机基础试题
第一章绪论本章练习题1、数据库是在计算机系统中按照一定的数据模型组织、存储和应用的___1__；支持数据库各种操作的软件系统叫____2________；由计算机、操作系统、DBMS、数据库、应用程序及用户等组成的一个整体叫做____3____。
1 A、文件的集合B、数据的集合C、命令的集合D、程序的集合2 A、命令系统B、数据库管理系统C、数据库系统D、操作系统3 A、文件系统B、数据库系统C、软件系统D、数据库管理系统2、数据库领域常用的数据模型有_____________A、层次模型B、网状模型C、关系模型D、面向对象模型3、目前最流行的数据模型是_____________A．层次模型B．网状模型C．关系模型D．面向对象模型4、在学生选课数据库中，学生实体与课程实体之间的联系是_____________A．1：1 B．1：NC．N：1 D．M：N5、数据库管理技术经理了__________________、____________________和________________________三个阶段。
6、数据独立性可分为________________和____________________。
7、根据数据模型的应用目的不同，数据模型分为______________和_________。
8、数据模型的三个组成要素是__________________、____________________和________________________。
9、什么是数据库？10、什么是数据库的数据独立性？11、数据库管理系统有哪些功能？第二章关系数据库大家在学习本章内容时，要掌握关系的数据结构、关系的三类完整性约束（实体完整性，参照完整性，用户自定义完整性）、关系操作（关系代数，元组关系演算，域关系演算）概念；理解关系、关系模式、关系数据库的概念；理解实体完整性、参照完整性的含义；熟练掌握关系代数中的各种运算符；学会使用关系代数的运算符进行各种操作。
数据库概论参考答案
.数据库概论专科作业题参考答案第一章数据库系统导论一、选择1.C2.B3.C4.A5.A6.A7.C8.A9.D10.D11.B12.B13.B14.C15.D16.C17.A18.D19.C20.B21.A22.C23.A24.C25.D26.D27.B28.B29.A30.C31.A32.D33.A34.C35.A36. A37.C38.D39.A40.D41.D42.D43.C44.A45.D46.B二、填空1．数据2．数据库3．数据库的恢复4．概念模型5．逻辑独立性6．关系数据7．层次8．数据定义9．网状模型10．完整性约束11．关系数据12．E-R13．一对多14．信息三、名词解释1．信息：经过加工处理后具有一定含义的数据集合，它具有超出事实数据本身之外的价值。
2．数据：数据库中存储的基本对象，通常指描述事物的符号。
3．数据库：存储在计算机存储设备上，结构化的相关数据集合。
它不仅包括描述事物的数据本身，还包括相关事物之间的联系。
4．数据库管理系统：处理数据访问的软件系统，也就是位于用户与操作系统之间的一层对数据库进行管理的软件。
用户必须通过数据库管理系统来统一管理和控制数据库中的数据。
5．数据库系统：引进了数据库技术后的计算机系统，它能够有组织地、动态地存储大量数据，提供数据处理和数据共享机制，一般由硬件系统、软件系统、数据库和人员组成。
6．实体：客观存在并可相互区别的事物称为实体。
7．属性：实体所具有的某一特征或性质称为属性。
8．联系：在现实世界中，事物内部以及事物之间是有联系的，这些联系在信息世界中反映为两类：一类是实体内部的联系；另一类是实体之间的联系。
9．关键字：唯一地标识实体的属性集称为关键字。
10．实体型：用实体名及其属性名集合来抽象和描述同类实体，称为实体型。
11．实体集：同一类实体的集合称为实体集。
12．数据结构：数据结构是对数据静态特征的描述。
数据的静态特征包括数据的基本结构、数据间的联系13．数据操作：数据操作是指对数据动态特征的描述，包括对数据进行的操作及相关操作规则。
数据库系统基础教程第四章答案
SolutionsChapter 4 4.1.14.1.2a)b)c)In c we assume that a phone and address can only belong to a single customer (1-m relationship represented by arrow into customer).d)In d we assume that an address can only belong to one customer and a phone can exist at only one address.If the multiplicity of above relationships were m-to-n, the entity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the entity set.In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design,we must consider more efficient alternatives.Instead of querying multiple tables where key values are duplicated, we can also modify attributes:(i) Phones attribute can be converted into HomePhone, OfficePhone and CellPhone.(ii) A multivalued attribute such as alias can be kept as an attribute where a single column can be used in relational design i.e. concatenate all values. SQL allows a query "like '%Junius%'" to search the multiple values in a column alias.4.1.34.1.4a)c)The relationship "played" between Teams and Players is similar to relationship "plays" between Teams and Players.4.1.54.1.6 The information about children can be ascertained from motherOf and fatherOf relationships. Attribute ssNo is required since names are not unique.4.1.74.1.8a)(b)4.1.9AssumptionsA Professor only works in at most one department.A course has at most one TA.A course is only taught by one professor and offered by one department.Students and professors have been assigned unique email ids.A course is uniquely identified by the course no, section no, and semester (e.g. cs157-3 spring09).4.1.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one studio.But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.4.2.1Redundancy: The owner address is repeated in AccSets and Addresses entity sets. Simplicity: AccSets does not serve any useful purpose and the design can be more simply represented by creating many-to-many relationship between Customers and Accounts.Right kind of element: The entity set Addresses has a single attribute address. A customer cannot have more than one address.Hence address should be an attribute of entity set Customers.Faithfulness: Customers cannot be uniquely identified by their names. In real world Customers would have a unique attribute such as ssNo or customerNo4.2.2Studios and Presidents can be combined into one entity set Studios with Presidents becoming an attribute of Studios under following circumstances:1. The Presidents entity set only contains a simple attribute viz. presidentName. Additional attributes specific to Presidents might justify making Presidents into an entity set.4.2.34.2.4 The entity sets should have single attribute.a) Stars: starNameb) Movies: movieNamec) Studios: studioName. However there exists a many-to-many relationship between Studios and Contracts. Hence, in addition, we need more information about studios involved. If a contract always involves two studios, two attributes such as producingStudio and starStudio can replace theStudios entity set. If a contact can be associated with at most five studios, it may be possible to replace the Studios entity set by five attributes viz. studio1, studio2, studio3, studio4, and studio5. Alternately, a composite attribute containing concatenation of all studio names in a contact can be considered. A separator character such as "$" can be used. SQL allows searching of such an attribute using query like '%keyword%'4.2.5From Augmentation rule of Functional Dependency,givenB -> M (B=Baby, M=Mother)thenBND -> M (N=Nurse, D=Doctor)Hence we can just put an arrow entering mother.a) Put an arrow entering entity set Mothers for the simplest solution (As in fig. 4.4, where a multi-way relationship was allowed, even though Movies alone could identify the Studio). However, we can display more accurate information with below figure.c)Again from Augmentation rule of Functional Dependency,givenBM -> DthenBMN -> DThus we can just add an arrow entering Doctors to fig 4.15. Below figure represents more accurate information however.4.2.6a)b) Transitivity and Augmentation rules of Functional Dependency allow arrow entering Mothers from Births. However, a new relationship in below figure represents more accurate information.c)Design flaws in abc above 1. As suggested above, using Transitivity and Augmentation rules of Functional Dependency, much simpler design is possible.4.2.7In below figure there exists a many-to-one relationship between Babies and Births and another many-to-one relationship between Births and Mothers. From transitivity of relationships, thereis a many-to-one relationship between Babies and Mothers. Hence a baby has a unique mother while a birth can allow more than one baby.4.3.1a)b)A captain cannot exist without a team. However a player can (free agent). A recently formed (or defunct) team can exist without players or colors.c)Children can exist without mother and father (unknown).4.3.2a)The keys of both E1 and E2 are required for uniquely identifying tuples in Rb)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Special Case: All entity sets have arrows going into them i.e. all relationships are 1-to-1Any KiOtherwise: Combination of all Ki's where there does not exist an arrow going from R to Ei.4.4.1No, grade is not part of the key for enrollments. The keys of Students and Courses become keys of the weak entity set Enrollments.4.4.2It is possible to make assignment number a weak key of Enrollments but this is not good design (redundancy since multiple assignments correspond to a course). A new entity set Assignment is created and it is also a weak entity set. Hence the key attributes of Assignment will come from the strong entity sets to which Enrollments is connected i.e. studentID, dept, and CourseNo.4.4.3a)b)4.4.4a)b)Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(custSSNo,flightNo,flightDay,row,seat)Relations for toCust and toFlt relationships are not required since the weak entity set Bookings already contains the keys of Customers and Flights.4.5.2(a)Schema is changed. Since toCust is no longer an identifying relationship, SSNo is no longer a part of Bookings relation.Bookings(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)The above relations are merged intoBookings(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no longer a key of Bookings relation. It becomes a foreign key instead.4.5.3Ships(name, yearLaunched)SisterOf(name, sisterName)4.5.4(a)Stars(name,addr)Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram, studioName may not be required as a key of Contracts (or not even required as an attribute of Contracts).(b)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)Teams(leagueName,teamName)Players(leagueName,teamName,playerName)4.6.1The weak relation Courses has the key from Depts along with number. Hence there is no relation for GivenBy relationship.(a)Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)(b) LabCourses has all the attributes of Courses.Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)(c) Courses and LabCourses are combined into one relation.Depts(name, chair)Courses(number, deptName, room, allocation)4.6.2(a)Person(name,address)ChildOf(personName,personAddress,childName,childAddress)Child(name,address,fatherName,fatherAddress,motherName,motherAddresss)Father(name,address,wifeName,wifeAddresss)Mother(name,address)Since FatherOf and MotherOf are many-one relationships from Child, there is no need for a separate relation for them. Similarly the one-one relationship Married can be included in Father (or Mother). ChildOf is a many-many relationship and needs a separate relation.However the ChildOf relation is not required since the relationship can be deduced from FatherOf and MotherOf relationships contained in Child relation.(b)A person cannot be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and FatherOf could be added as attributes toPersonChild,PersonChildFather, and PersonChildMother relations.Similarly the Married relation can be added as attributes to PersonChildMother and PersonMother (or the corresponding father relations).(c) For the Person relation at least one of husband and wife attributes will be null.Person(personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeName,wife Addresss,husbandName,husbandAddress)ChildOf(personName,personAddress,childName,childAddress)4.6.3(a)People(name,fatherName,motherName)Males(name)Females(name)Fathers(name)Mothers(name)ChildOf(personName,childName)People(name)PeopleMale(name)PeopleMaleFathers(name)PeopleFemale(name)PeopleFemaleMothers(name)ChildOf(personName,childName)FatherOf(childName,fatherName)MotherOf(childName,motherName)People cannot belong to both male and female branch of the ER diagram.Moreover since an entity belongs to one and only one class when using object-oriented approach, no entity belongs to People relation.Again we could replace MotherOf and FatherOf relations by adding as attributes toPeopleMale,PeopleMaleFathers,PeopleFemale, and PeopleFemaleMothers relations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)4.6.4(a)Each entity set results in one relation. Thus both the minimum and maximum number of relationsis e.The root relation has a attributes including k keys. Thus the minimum number of attributes is a. All other relations include the k keys from root along with their a attributes. Thus the maximum number of attributes is a+k.The relation for root will have a attributes. The relation representing the whole tree will have e*a attributes.The number of relations will depend on the shape of the tree. A tree of e entities where onlyone child exists(say left child only) would have the minimum number of relations. Thus below figure will only contain 4 subtrees that contain root E1,E1E2,E1E2E3, and E1E2E3E4. With eentity sets, minimum e relations are possible.The maximum number of subtrees result when all the entities(except root) are at depth 1. Thus below figure will contain 8 subtrees that contain rootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,and E1E2E3E4. With e entity sets, maximum 2^(e-1)relations are possible.(c)The nulls method always results in one relation and contains attributes from all e entities i.e. e*a attributes.Summarizing for a,b, and c above;#Components #RelationsMin Max Min MaxMethodstraight-E/R a a e eobject-oriented a e*a e 2^(e-1)nulls e*a e*a 1 14.7.14.7.2a)b)c)d)4.7.34.7.44.7.5Males and Females subclasses are complete. Mothers and Fathers are partial. All subclasses are disjoint.4.7.64.7.74.7.8We convert the ternary relationship Contracts into three binary relationships between a new entity set Contracts and existing entity sets.4.7.9a)c)4.7.10A self-association ParentOf for entity set people has multiplicity 0..2 at parent role end.In a Library database, if a patron can loan at most 12 books, them multiplicity is 0..12.For a FullTimeStudents entity set, a relationship of multiplicity 5..* must exist with Courses(A student must take at least5 courses to be classified FullTime.4.8.1Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(row,seat,custSSNo,FlightNumber,FlightDay)Customers("SSNo",name,addr,phone)Flights("number","day",aircraft)Bookings(row,seat,"custSSNo","FlightNumber","FlightDay")4.8.2a)Movies(title,year,length,genre)Studios(name,address)Presidents(cert#,name,address)Owns(movieTitle,movieYear,studioName)Runs(studioName,presCert#)Movies("title","year",length,genre)Studios("name",address)Presidents("cert#",name,address)Owns("movieTitle","movieYear",studioName)Runs("studioName",presCert#)b)Since the subclasses are disjoint, Object Oriented Approach is used. The hierarchy is not complete. Hence four relations are required Movies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon)Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,genre,weapon)Movies("title","year",length,genre)MurderMysteries("title","year",length,genre,weapon)Cartoons("title","year",length,genre)Cartoon-MurderMysteries("title","year",length,genre,weapon)c)Customers(ssNo,name,phone,address)Accounts(number,balance,type)Owns(custSSNo,accountNumber)Customers("ssNo",name,phone,address)Accounts("number",balance,type)Owns("custSSNo","accountNumber")d)Teams(name,captainName)Players(name,teamName)Fans(name,favoriteColor)Colors(colorname)For Displays association,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)Teams("name",captainName)Players("name",teamName)Fans("name",favoriteColor)Colors("colorname")For Displays association,TeamColors("teamName","colorname")RootsFor("fanName","teamName")Admires("fanName","playerName")e)People(ssNo,name,fatherSSNo,motherSSNo)People("ssNo",name,fatherssNo,motherssNo)f)Students(email,name)Courses(no,section,semester,professorEmail)Departments(name)Professors(email,name,worksDeptName)Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)Students("email",name)Courses("no","section","semester",professorEmail)Departments("name")Professors("email",name,worksDeptName)Takes(letterGrade,"studentEmail","courseNo","courseSection","courseSemester")4.8.3a)Each and every object is a member of exactly one subclass at leaf level. We have nine classes at the leaf of hierarchy. Hence we need nine relations.b)All objects only belong to one subclass and its ancestors. Hence, we need not consider every possible subtree but rather the total number of nodes in tree.Hence we need thirteen relations.c)We need all possible subtrees. Hence 218 relations are required.4.9.1class Customer (key (ssNo)){attribute integer ssNo;attribute string name;attribute string addr;attribute string phone;relationship Set<Account> ownsAcctsinverse Account::ownedBy;};class Account (key (number)){attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts;};4.9.2a)Modify class Account to contain relationship Customer ownedBy (no Set)b)Also remove set in relationship ownsAccts of class Customer.c)ODL allows a collection of primitive types as well as structures. To class Customer add following attributes in place of simple attributes addr and phone:Set<string phone>Set<Struct addr{string street,string city,string state}>d)ODL allows structures and collections recursively.Set<Struct addr{string street,string city,string state},Set<string phone>>4.9.3Collections are allowed in ODL. Hence, Colors Set can become an attribute of Teams. class Colors(key(colorname)){attribute string colorname;relationship Set<Fans> FavoredByinverse Fans::Favors;relationship set<Teams> DisplayedByinverse Teams::Displays;};class Teams(key(name)){attribute string name;relationship set<Colors> Displaysinverse Colors::DisplayedBy;relationship set<Players> PlayedByinverse Players::Plays;relationship PLayers CaptainedByinverse Platyers::Captains;relationship set<Fans> RootedByinverse Fans::Roots;};class Players(key(name)){attribute string name;relationship Set<Teams> Playsinverse Teams::PlayedBy;relationship Teams Captainsinverse Teams::CaptainedBy;relationship Set<Fans> AdmiredByinverse Fans::Admires;};class Fans(key(name)){attribute string name;relationship Colors Favorsinverse Colors::FavoredBy;relationship Set<Teams> RootedByinverse Teams::Roots;relationship Set<Players> Admiresinverse Players::AdmiredBy;};4.9.4class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale;relationship Person fatherOfinverse Person::childrenOfMale;relationship Set<Person> childreninverse Person::parentsOf;relationship Set<Person> childrenOfFemaleinverse Person::motherOf;relationship Set<Person> childrenOfMaleinverse Person::fatherOf;relationship Set<Person> parentsOfinverse Person::children;};4.9.5The struct education{string degree,string school,string date} cannot have duplication. Hence use of Sets does not make any different as compared to bags, lists, or arrays. Lists will allow faster access/queries due to the already sorted nature.4.9.6a)class Departments(key (name)) {attribute string name;relationship Courses offersinverse Courses::offeredBy;};class Courses(key (number,offeredBy)) {attribute string number;relationship Departments offeredByinverse Departments::offers;};b)class Leagues (key (name)) {attribute name;relationship Teams containsinverse Teams::belongs;};class Teams(key (name,belongs)) {attribute name,relationship Leagues belongsinverse Leagues::contains;relationship Players playinverse Players::plays;};class Players (key(number,plays)) {attribute number,relationship Teams playsinverse Teams::play;};4.9.7class Students (key email) {attribute string email;attribute string name;relationship Courses isTAinverse Courses::TA;relationship Courses Takesinverse Courses::TakenBy;};class Professors (key email) {attribute string email;attribute string name;relationship Departments WorksForinverse Department::Works;relationship Courses Teachesinverse Courses::TaughtBy;};class Courses (key (no,semester,section)) {attribute string no;attribute string semester;attribute string section;relationship Students TAinverse Students::isTA;relationship Students TakenByinverse Students::Takes;relationship Professors TaughtByinverse Professors::Teaches;relationship Departments OfferedByinverse Departments::Offer;};class Departments (key name) {attribute name;relationship Courses Offerinverse Courses::OfferedBy;relationship Professors Worksinverse Professors::WorksFor;};4.9.8A relationship is its own inverse when for every attribute pair in the relationship, the inverse pair also exists. A relation with such a relationship is called symmetric in set theory. e.g. A relationship called SiblingOf in Person relation is its own inverse.4.10.1a)Customers(ssNo,name,addr,phone)Account(number,type,balance)Owns(ssNo,accountNumber)b)Accounts(number,balance,type,owningCustomerssNo)Customers(ssNo,name)Addresses(ownerssNo,street,state,city)Phones(ownerssNo,street,state,city,phonearea,phoneno)We can remove Addresses relation since its attributes are a subset of relation Phones.Fans(name,colors)RootedBy(fan_name,teamname)Admires(fan_name,playername)Players(name,teamname,is_captain)Teams(name)--remove subset of teamcolorTeamcolors(name,colorname)Colors(colorname)d)class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale;relationship Person fatherOfinverse Person::childrenOfMale;relationship Set<Person> childreninverse Person::parentsOf;relationship Set<Person> childrenOfFemaleinverse Person::motherOf;relationship Set<Person> childrenOfMaleinverse Person::fatherOf;relationship Set<Person> parentsOfinverse Person::children;};Person(name,mothername,fathername)The children relationship is many-many but the information can be deduced from Person relation. Hence below relation is redundant.Parent-Child(parent, child)4.10.2First consider each struct as if it were an atomic value i.e. key and value association pairs can be treated as two attributes. After applying normalization,the attributes can be replaced by the fields of the structs.4.10.3(a)Struct Card { string rank, string suit };(b)class Hand {attribute Set theHand;};(c)Hands(handId, rank, suit)Each tuple corresponds to one card of a hand. HandId is required key to identify a hand.(d) Hand contains an array of 5 elementsclass PokerHand{attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)} PokerHandS(handId,rank1,suit1,,rank2,suit2,rank3,suit3,rank4,suit4,rank5,suit5)(e)class Deal {attribute Set <Struct PlayerHand { string Player, Hand theHand } > theDeal;}(f) PokerDeal consist of a player and array of five card deal.class PokerDeal{string Player,attribute Array Hand(Card card1,Card card2,Card card3,Cardcard4,Card card5)}(g) Above can similarly be represented by key player and a value consisting of five element array.(h)dealID is a key for Deals. Thus the relations for classes Deals and Hands are:Deals(dealID, player, handID)Hands(handID, rank, suit)A simpler relation Deals below can also represents the classes:Deals(dealID, player, rank, suit)(i)The relation Deals(dealID,card) cannot identify the hand to which a card belongs. Also two attributes are required for a card;its rank and suit.Deals(dealID, handID, rank, suit)4.10.4(a)C(a, f, g)(b)C(a, f, g, count)(c)C(a, f, g, position)(d)C(a, f, g, i, j)。
关系数据库理论答案
例1：设有关系模式R(A,B,C,D,E),其上的函数依赖集：F= {A→BC,CD→E,B→D,E→A}计算B+和CD+解B+ = BDCD+ = ABCDE例2：设有依赖集F={AB→C, C→A, BC→D,ACD→B,D→EG,BE→C,CG→BD,CE→AG} 计算最小等价依赖集。
解：(1). 右边属性单一化F1= {AB→C BE→CC→A CG→BBC→D CG→DACD→B CE→AD→E CE→GD→G }(2).去掉F1中的左部多余属性F2= {AB→C BE→CC→A CG→BBC→D CG→DCD→B CE→GD→ED→G }(3). 去掉F2中的多余的依赖F3= {AB→C BE→CC→A CG→DBC→D CE→GCD→BD→ED→G }或者F3= {AB→C BE→CC→A CG→BBC→D CE→GD→ED→G }侯选码求解理论和算法（两种情况）（F min）对于给定的关系R和函数依赖集F，可将其属性分为4类：L类：仅出现在F min的函数依赖左部的属性；R类：仅出现在F min的函数依赖右部的属性；N类：在F min中函数依赖的左右两边均未出现的属性；LR类：在F min中函数依赖的左右两边均出现过的属性；定理：对于给定的关系模式R及其函数依赖集F，若X是L和N类属性，则X 必为R的任一候选码的成员。
算法1：单属性依赖集图论求解法。
(1).求F的最小依赖集F min；(2).构造函数依赖图；(3).从图中找出关键属性集X（L、N类属性）；(4).查看图中有无独立回路，若无，则输出X即为R的唯一候选码，转6；若有，则转5；(5).从各独立回路中各取一结点对应的属性与X组合成一候选码，并重复这一过程，取尽可能所有的组合，即为R的全部候选码。
(6).结束。
例3：设有R=(O, B, I, S, Q, D), F={S→D, D→S, I→B, B→I, B→O, O→B, I→O }, 求R的所有候选码。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第4章 关系数据库设计理论
选择题答案：
(1) A (2) B (3) B (4) A (5) D
(6) B (7) C (8) B (9) B (10) C
(11) D (12) A (13) D (14) D (15) B
(16) B (17) D (20) C
(21) C (23) A
(26) B (27) B (28) B (29) B (30) B
(31) D (33) B B D
一、选择题：
1. 为了设计出性能较优的关系模式，必须进行规范化，规范化主要的理论依据是（ ） 。
A. 关系规范化理论 B. 关系代数理论 C．数理逻辑 D. 关系运算理论
2. 规范化理论是关系数据库进行逻辑设计的理论依据，根据这个理论，关系数据库中的关
系必须满足：每一个属性都是（ ） 。
A. 长度不变的 B. 不可分解的 C．互相关联的 D. 互不相关的
3. 已知关系模式R（A，B，C，D，E）及其上的函数相关性集合F＝{A→D，B→C ，E→A }，
该关系模式的候选关键字是（ ） 。
A.AB B. BE C.CD D. DE
4. 设学生关系S（SNO，SNAME，SSEX，SAGE，SDPART）的主键为SNO，学生选课关系SC（SNO，
CNO，SCORE）的主键为SNO和CNO，
则关系R（SNO，CNO，SSEX，SAGE，SDPART，SCORE）的主键为SNO和CNO，其满足（ ）。
A. 1NF B.2NF C. 3NF D. BCNF
5. 设有关系模式W（C，P，S，G，T，R），其中各属性的含义是：C表示课程，P表示教师，
S表示学生，G表示成绩，T表示时间，R表示教室，根据语义有如下数据依赖集：D={ C→P，
（S，C）→G，（T，R）→C，（T，P）→R，（T，S）→R }，关系模式W的一个关键字是（ ） 。
A. （S，C） B. （T，R） C. （T，P） D. （T，S）
6. 关系模式中，满足2NF的模式（ ） 。
A. 可能是1NF B. 必定是1NF C. 必定是3NF D. 必定是BCNF
7. 关系模式R中的属性全是主属性，则R的最高范式必定是（ ） 。
A. 1NF B. 2NF C. 3NF D. BCNF
8. 消除了部分函数依赖的1NF的关系模式，必定是（ ） 。
A. 1NF B. 2NF C. 3NF D. BCNF
9. 如果A－>B ,那么属性A和属性B的联系是（ ） 。
A. 一对多 B. 多对一 C．多对多 D. 以上都不是
10. 关系模式的候选关键字可以有1个或多个，而主关键字有（ ） 。
A. 多个 B. 0个 C. 1个 D. 1个或多个
11. 候选关键字的属性可以有（ ） 。
A. 多个 B. 0个 C. 1个 D. 1个或多个
12. 关系模式的任何属性（ ） 。
A. 不可再分 B. 可以再分 C. 命名在关系模式上可以不唯一 D. 以上都不是
13. 设有关系模式W（C，P，S，G，T，R），其中各属性的含义是：C表示课程，P表示教师，
S表示学生，G表示成绩，T表示时间，R表示教室，根据语义有如下数据依赖集：D={ C→P，
（S，C）→G，（T，R）→C，（T，P）→R，（T，S）→R }，若将关系模式W分解为三个关系
模式W1（C，P），W2（S，C，G），W2（S，T，R，C），则W1的规范化程序最高达到（ ） 。
A. 1NF B.2NF C. 3NF D. BCNF
14. 在关系数据库中，任何二元关系模式的最高范式必定是（ ） 。
A. 1NF B.2NF C. 3NF D. BCNF
15. 在关系规范式中，分解关系的基本原则是（ ）。
I.实现无损连接
II.分解后的关系相互独立
III.保持原有的依赖关系
A. Ⅰ和Ⅱ B. Ⅰ和Ⅲ C. Ⅰ D. Ⅱ
16. 不能使一个关系从第一范式转化为第二范式的条件是（ ）。
A.每一个非属性都完全函数依赖主属性
B.每一个非属性都部分函数依赖主属性
C.在一个关系中没有非属性存在
D.主键由一个属性构成
17. 任何一个满足2NF但不满足3NF的关系模式都不存在（ ）。
A.主属性对键的部分依赖
B.非主属性对键的部分依赖
C.主属性对键的传递依赖
D.非主属性对键的传递依赖
20. 若关系模式R（U，F）属于3NF，则（ ）。
A. 一定属于BCNF
B. 消除了插入的删除异常
C. 仍存在一定的插入和删除异常
D. 属于BCNF且消除了插入和删除异常
21. 下列说法不正确的是（ ）。
A. 任何一个包含两个属性的关系模式一定满足3NF
B. 任何一个包含两个属性的关系模式一定满足BCNF
C. 任何一个包含三个属性的关系模式一定满足3NF
D. 任何一个关系模式都一定有码
23. 关系数据库规范化是为了解决关系数据库中（ ）的问题而引入的。
A. 插入、删除和数据冗余
B. 提高查询速度
C. 减少数据操作的复杂性
D. 保证数据的安全性和完整性
26. 学生表（id，name，sex，age，depart_id，depart_name），存在函数依赖是id→name，
sex，age，depart_id；dept_id→dept_name，其满足（ ）。
A. 1NF B. 2NF C. 3NF D. BCNF
27. 设有关系模式R（S，D，M），其函数依赖集：F＝{S→D，D→M}，则关系模式R的规范
化程度最高达到（ ）。
A. 1NF B. 2NF C. 3NF D. BCNF
28. 设有关系模式R（A，B，C，D），其数据依赖集：F＝{（A，B）→C，C→D}，则关系模
式R的规范化程度最高达到（ ）。
A. 1NF B. 2NF C. 3NF D. BCNF
29. 下列关于函数依赖的叙述中，哪一条是不正确的（ ）。
A.由X→Y，Y→Z，则X→YZ
B.由X→YZ，则X→Y， Y→Z
C.由X→Y，WY→Z，则XW→Z
D.由X→Y，Z∈Y，则X→Z
30. X→Y，当下列哪一条成立时，称为平凡的函数依赖（ ）。
A. X∈Y B. Y∈X C. X∩Y＝∮ D. X∩Y≠∮
31. 关系数据库的规范化理论指出：关系数据库中的关系应该满足一定的要求，最起码的要
求是达到1NF，即满足（ ）。
A.每个非主键属性都完全依赖于主键属性
B.主键属性唯一标识关系中的元组
C.关系中的元组不可重复
D.每个属性都是不可分解的
33. 有关系模式A（C，T，H，R，S），其中各属性的含义是：
C：课程 T：教员 H：上课时间 R：教室 S：学生
根据语义有如下函数依赖集：
F={C→T，（H，R）→C，（H，T）RC，（H，S）→R}
（1）关系模式A的码是（ ）。
A. C B.（H，S） C.（H，R） D.（H，T）
（2）关系模式A的规范化程度最高达到（ ）。
A. 1NF B. 2NF C. 3NF D. BCNF
（3）现将关系模式A分解为两个关系模式A1（C，T），A2（H，R，S），则其中A1的规
范化程度达到（ ）。
A. 1NF B. 2NF C. 3NF D. BCNF