下载文档原格式
第2章关系数据库一、选择题1、关于关系模型，下列叙述不正确的是（）。
A. 一个关系至少要有一个候选码B。
列的次序可以任意交换C。
行的次序可以任意交换 D. 一个列的值可以来自不同的域2、下列说法正确的是（）。
A。
候选码都可以唯一地标识一个元组B。
候选码中只能包含一个属性C. 主属性可以取空值D. 关系的外码不可以取空值3、关系操作中,操作的对象和结果都是（）。
A. 记录B。
集合 C. 元组D。
列4、假设存在一张职工表,包含“性别”属性，要求这个属性的值只能取“男”或“女”，这属于（）.A。
实体完整性B。
参照完整性 C. 用户定义的完整性D。
关系不变性5、有两个关系R(A，B, C)和S（B, C, D），将R和S进行自然连接,得到的结果包含几个列（）A. 6 B。
4 C。
5 D. 2二、判断题1、关系模型的一个特点是，实体以及实体之间的联系都可以使用相同的结构类型来表示。
（）2、关系模型中，非主属性不可能出现在任何候选码中。
（）3、关系模式是对关系的描述，关系是关系模式在某一时刻的状态或内容。
（）三、填空题1、在关系模型中，关系操作包括查询、____________、____________和_____________等。
2、关系模型的三类完整性约束是指______________、_______________和_____________。
3、关系模型包括8种查询操作,其中__________、_________、并、________和笛卡儿积是5种基本操作，其他操作可以用基本操作定义和导出。
4、职工（职工号，姓名，年龄，部门号）和部门（部门号，部门名称）存在引用关系，其中________________是参照关系，____________是外码。
四、综合题假设有一个数据库包含以下关系模式：Teacher（Tno, Tname, Tage，Tsex）Department(Dno, Dname，Tno）Work（Tno，Dno，Year, Salary)教师表Teacher由教师代码Tno、教师名字Tname、教师年龄Tage、教师性别Tsex组成. 系表Department由系代码Dno、系名Dname、系主任代码Tno组成工作表Work由教师代码Tno、系代码Dno、入职年份Year、工资Salary组成使用关系代数表示每个查询（1）列出工资超过5000的教师的不同年龄;（2）查找不在计算机系工作的教师代码；（3）系主任T1管辖范围内的所有教师姓名。
数据库系统2-7：关系代数的等价变换规则前面介绍的三种关系运算的能力是等价的，它们之间都可以相互等价转换，也都可以转换成关系代数表达式，所以研究关系运算等价变换原则可以从关系代数表达式开始。
关系代数的变换规则记为：E1oE2。
关系代数表达式经过等价变换后，其结果与变换前的关系表达式等价。
常用等价变换规则：1．连接、笛卡儿积的交换律E1XE2o E2XE1E1 >< E2o E2 >< E1 自然连接E1 >F< E2o E2 >F< E1 其中F为连接运算条件2．连接、笛卡儿积结合律设E1、E2、E3为关系代数表达式，F1、F2为连接运算条件。
则（E1XE2）XE3o E1X（E2 XE3）（E1 >< E2）>< E3o E1 >< （ E2 >< E3）（E1 > < F1 E2）>< F2E3o E1 >< F1（ E2 >< F2E3）3．投影的串接定律设E为关系代数表达式，Ai（i=1,2,3….n），Bj（j=1,2,3,….m）是属性名，且AiíBj 则 ?A1,A2,…An（?B1,B2,….Bm（E））o?A1,A2,….An（E）4．选择的串接律设E为关系代数表达式，F1、F2为选择条件。
σ-F1(σ-F2( E ) ) o σ-F1ùF2( E )5．选择和投影的交换律a)选择条件只涉及属性Ai（i=1,2,3….n）σ-F(?A1,A2,…An ( E ) ) o?A1,A2,…An（σ-F( E ) )b)选择条件涉及的属性有不属于A1,A2,…An的属性B1,B2,….Bm ，则规则为：?A1,A2,…An( σ-F( E ) ) o?A1,A2,…An( σ-F(?A1,A2,…An，B1,B2,….Bm( E )) ) ?A1,A2,…An（σ-F( E ) )不能等于σ-F(?A1,A2,…An ( E ) )，因为投影时属性A1,A2,…An不包含B1,B2,….Bm ，致使选择时缺乏有关属性B1,B2,….Bm 。
数据库系统,数据库管理系统,数据库的关系-概述说明以及解释1.引言1.1 概述概述部分的内容可以从以下方面展开：引言：数据库系统是现代信息管理的重要组成部分，它以存储、检索、管理和操作数据的方式来帮助组织有效地管理和利用数据。
数据库管理系统（DBMS）作为数据库系统的核心，提供了高效的数据管理和访问功能。
数据库系统是指由数据库、数据库管理系统和数据库应用程序共同组成的系统。
它是在计算机上组织和存储数据的一种方式，可以帮助用户方便地访问和操作各种类型的数据。
数据库系统具有许多显著的优点，包括数据共享、数据一致性、数据安全和数据独立性。
数据库管理系统是数据库系统的核心组件，它负责处理数据库的存储、检索、管理和保护等任务。
它提供了一系列的功能和工具，使得用户可以轻松地对数据进行查询、更新、删除和插入等操作。
数据库管理系统的发展经历了多个阶段，从最早的层次数据库到现在的关系数据库，并逐渐演变为面向对象数据库和NoSQL数据库等新型数据库技术。
数据库系统与数据库管理系统之间存在密切的关系。
数据库系统是一个更为广泛的概念，包括了数据库以及相关的管理系统和应用程序。
而数据库管理系统则是数据库系统的核心，负责管理和操作数据库。
数据库系统与数据库管理系统的关系紧密相连，二者相互依赖，共同构成了一个完整的数据库系统。
此外，数据库系统还与数据模型、数据操作和数据安全等方面有着紧密的关系。
数据模型是数据库系统中描述数据的方式，常见的数据模型有层次模型、网状模型和关系模型等。
数据操作是对数据库进行的常见操作，包括查询、更新和删除等。
数据安全是保护数据库中数据的机密性、完整性和可用性。
总之，数据库系统、数据库管理系统以及数据库的关系是一个重要的研究领域，它对于现代信息管理具有重要的作用。
本文将对数据库系统、数据库管理系统以及数据库的关系进行深入探讨，并在结论部分对数据库系统的重要性、数据库管理系统的评价以及数据库与其他技术的发展趋势进行总结和展望。
Database Systems: The Complete BookSolutions for Chapter 2Solutions for Section 2.1Exercise 2.1.1The E/R Diagram.Exercise 2.1.8(a)The E/R DiagramKobvxybzSolutions for Section 2.2Exercise 2.2.1The Addresses entity set is nothing but a single address, so we would prefer to make address an attribute of Customers. Were the bank to record several addresses for a customer, then it might make sense to have an Addresses entity set and make Lives-at a many-many relationship.The Acct-Sets entity set is useless. Each customer has a unique account set containing his or her accounts. However, relating customers directly to their accounts in a many-many relationship conveys the same information and eliminates the account-set concept altogether.Solutions for Section 2.3Exercise 2.3.1(a)Keys ssNo and number are appropriate for Customers and Accounts, respectively. Also, we think it does not make sense for an account to be related to zero customers, so we should round the edge connecting Owns to Customers. It does not seem inappropriate to have a customer with 0 accounts;they might be a borrower, for example, so we put no constraint on the connection from Owns to Accounts. Here is the The E/R Diagram，showing underlined keys andthe numerocity constraint.Exercise 2.3.2(b)If R is many-one from E1 to E2, then two tuples (e1,e2) and (f1,f2) of the relationship set for R must be the same if they agree on the key attributes for E1. To see why, surely e1 and f1 are the same. Because R is many-one from E1 to E2, e2 and f2 must also be the same. Thus, the pairs are the same.Solutions for Section 2.4Exercise 2.4.1Here is the The E/R Diagram.We have omitted attributes other than our choice for the key attributes of Students and Courses. Also omitted are names for the relationships. Attribute grade is not part of the key for Enrollments. The key for Enrollements is studID from Students and dept and number from Courses.Exercise 2.4.4bHere is the The E/R Diagram Again, we have omitted relationship names and attributes other than our choice for the key attributes. The key for Leagues is its own name; this entity set is not weak. The key for Teams is its own name plus the name of the league of which the team is a part, e.g., (Rangers, MLB) or (Rangers, NHL). The key for Players consists of the player's number and the key for the team on which he or she plays. Since the latter key is itself a pair consisting of team and league names, the key for players is the triple (number, teamName, leagueName). e.g., JeffGarcia is (5, 49ers, NFL).Database Systems: The Complete BookSolutions for Chapter 3Solutions for Section 3.1Exercise 3.1.2(a)We can order the three tuples in any of 3! = 6 ways. Also, the columns can be ordered in any of 3! = 6 ways. Thus, the number of presentations is 6*6 = 36.Solutions for Section 3.2Exercise 3.2.1Customers(ssNo, name, address, phone)Flights(number, day, aircraft)Bookings(ssNo, number, day, row, seat)Being a weak entity set, Bookings' relation has the keys for Customers and Flights and Bookings' own attributes.Notice that the relations obtained from the toCust and toFlt relationships are unnecessary. They are:toCust(ssNo, ssNo1, number, day)toFlt(ssNo, number, day, number1, day1)That is, for toCust, the key of Customers is paired with the key for Bookings. Since both include ssNo, this attribute is repeated with two different names, ssNo and ssNo1. A similar situation exists for toFlt.Exercise 3.2.3Ships(name, yearLaunched)SisterOf(name, sisterName)Solutions for Section 3.3Exercise 3.3.1Since Courses is weak, its key is number and the name of its department. We do not have arelation for GivenBy. In part (a), there is a relation for Courses and a relation for LabCourses that has only the key and the computer-allocation attribute. It looks like:Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)For part (b), LabCourses gets all the attributes of Courses, as:Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)And for (c), Courses and LabCourses are combined, as:Depts(name, chair)Courses(number, deptName, room, allocation)Exercise 3.3.4(a)There is one relation for each entity set, so the number of relations is e. The relation for the root entity set has a attributes, while the other relations, which must include the key attributes, have a+k attributes.Solutions for Section 3.4Exercise 3.4.2Surely ID is a key by itself. However, we think that the attributes x, y, and z together form another key. The reason is that at no time can two molecules occupy the same point.Exercise 3.4.4The key attributes are indicated by capitalization in the schema below:Customers(SSNO, name, address, phone)Flights(NUMBER, DAY, aircraft)Bookings(SSNO, NUMBER, DAY, row, seat)Exercise 3.4.6(a)The superkeys are any subset that contains A1. Thus, there are 2^{n-1} such subsets, since each of the n-1 attributes A2 through An may independently be chosen in or out.Solutions for Section 3.5Exercise 3.5.1(a)We could try inference rules to deduce new dependencies until we are satisfied we have them all.A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have A+ = A, B+ = B, C+ = ACD, and D+ = AD. Thus, the only new dependency we get with a single attribute on the left is C->A.Now consider pairs of attributes:AB+ = ABCD, so we get new dependency AB->D. AC+ = ACD, and AC->D is nontrivial. AD+ = AD, so nothing new. BC+ = ABCD, so we get BC->A, and BC->D. BD+ = ABCD, giving usBD->A and BD->C. CD+ = ACD, giving CD->A.For the triples of attributes, ACD+ = ACD, but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC->D, ABD->C, and BCD->A.Since ABCD+ = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above is: C->A, AB->D, AC->D, BC->A, BC->D, BD->A, BD->C, CD->A, ABC->D, ABD->C, and BCD->A.Exercise 3.5.1(b)From the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.5.1(c)The superkeys are all those that contain one of those three keys. That is, a superkey that is not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD.Exercise 3.5.3(a)We must compute the closure of A1A2...AnC. Since A1A2...An->B is a dependency, surely B is in this set, proving A1A2...AnC->B.Exercise 3.5.4(a)Consider the relationThis relation satisfies A->B but does not satisfy B->A.Exercise 3.5.8(a)If all sets of attributes are closed, then there cannot be any nontrivial functional dependenc ies. For suppose A1A2...An->B is a nontrivial dependency. Then A1A2...An+ contains B and thus A1A2...An is not closed.Exercise 3.5.10(a)We need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: A+ = AB+ = BC+ = ACEAB+ = ABCDEAC+ = ACEBC+ = ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC are: C->A and AB->C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted from our list.Solutions for Section 3.6Exercise 3.6.1(a)In the solution to Exercise 3.5.1 we found that there are 14 nontrivial dependencies, including the three given ones and 11 derived dependencies. These are: C->A, C->D, D->A, AB->D, AB-> C, AC->D, BC->A, BC->D, BD->A, BD->C, CD->A, ABC->D, ABD->C, and BCD->A.We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are: C->A, C->D, D->A, AC->D, and CD->A.One choice is to decompose using C->D. That gives us ABC and CD as decomposed relations. CD is surely in BCNF, since any two-attribute relation is. ABC is not in BCNF, since AB and BC are its only keys, but C->A is a dependency that holds in ABCD and therefore holds in ABC. We must further decompose ABC into AC and BC. Thus, the three relations of the decomposition are AC, BC, and CD.Since all attributes are in at least one key of ABCD, that relation is already in 3NF, and no decomposition is necessary.Exercise 3.6.1(b)(Revised 1/19/02) The only key is AB. Thus, B->C and B->D are both BCNF violations. The derived FD's BD->C and BC->D are also BCNF violations. However, any other nontrivial, derived FD will have A and B on the left, and therefore will contain a key.One possible BCNF decomposition is AB and BCD. It is obtained starting with any of the four violations mentioned above. AB is the only key for AB, and B is the only key for BCD.Since there is only one key for ABCD, the 3NF violations are the same, and so is the decomposition.Solutions for Section 3.7Exercise 3.7.1Since A->->B, and all the tuples have the same value for attribute A, we can pair the B-value from any tuple with the value of the remaining attribute C from any other tuple. Thus, we know that R must have at least the nine tuples of the form (a,b,c), where b is any of b1, b2, or b3, and c is any of c1, c2, or c3. That is, we can derive, using the definition of a multivalued dependency, that each of the tuples (a,b1,c2), (a,b1,c3), (a,b2,c1), (a,b2,c3), (a,b3,c1), and (a,b3,c2) are also in R.Exercise 3.7.2(a)First, people have unique Social Security numbers and unique birthdates. Thus, we expect the functional dependencies ssNo->name and ssNo->birthdate hold. The same applies to children, so we expect childSSNo->childname and childSSNo->childBirthdate. Finally, an automobile has a unique brand, so we expect autoSerialNo->autoMake.There are two multivalued dependencies that do not follow from these functional dependencies. First, the information about one child of a person is independent of other information about that person. That is, if a person with social security number s has a tuple with cn,cs,cb, then if there isany other tuple t for the same person, there will also be another tuple that agrees with t except that it has cn,cs,cb in its components for the child name, Social Security number, and birthdate. That is the multivalued dependencyssNo->->childSSNo childName childBirthdateSimilarly, an automobile serial number and make are independent of any of the other attributes, so we expect the multivalued dependencyssNo->->autoSerialNo autoMakeThe dependencies are summarized below:ssNo -> name birthdatechildSSNo -> childName childBirthdateautoSerialNo -> autoMakessNo ->-> childSSNo childName childBirthdatessNo ->-> autoSerialNo autoMakeExercise 3.7.2(b)We suggest the relation schemas{ssNo, name, birthdate}{ssNo, childSSNo}{childSSNo, childName childBirthdate}{ssNo, autoSerialNo}{autoSerialNo, autoMake}An initial decomposition based on the two multivalued dependencies would give us {ssNo, name, birthDate}{ssNo, childSSNo, childName, childBirthdate}{ssNo, autoSerialNo, autoMake}Functional dependencies force us to decompose the second and third of these.Exercise 3.7.3(a)Since there are no functional dependencies, the only key is all four attributes, ABCD. Thus, each of the nontrvial multivalued dependencies A->->B and A->->C violate 4NF. We must separate out the attributes of these dependencies, first decomposing into AB and ACD, and then decomposing the latter into AC and AD because A->->C is still a 4NF violation for ACD. The final set of relations are AB, AC, and AD.Exercise 3.7.7(a)Let W be the set of attributes not in X, Y, or Z. Consider two tuples xyzw and xy'z'w' in the relation R in question. Because X ->-> Y, we can swap the y's, so xy'zw and xyz'w' are in R. Because X ->-> Z, we can take the pair of tuples xyzw and xyz'w' and swap Z's to get xyz'w and xyzw'. Similarly, we can take the pair xy'z'w' and xy'zw and swap Z's to get xy'zw' and xy'z'w.In conclusion, we started with tuples xyzw and xy'z'w' and showed that xyzw' and xy'z'w must also be in the relation. That is exactly the statement of the MVD X ->-> Y-union-Z. Note that the above statements all make sense even if there are attributes in common among X, Y, and Z.Exercise 3.7.8(a)Consider a relation R with schema ABCD and the instance with four tuples abcd, abcd', ab'c'd, and ab'c'd'. This instance satisfies the MVD A->-> BC. However, it does not satisfy A->-> B. For example, if it did satisfy A->-> B, then because the instance contains the tuples abcd and ab'c'd, we would expect it to contain abc'd and ab'cd, neither of which is in the instance.Database Systems: The Complete BookSolutions for Chapter 4Solutions for Section 4.2Exercise 4.2.1class Customer {attribute string name;attribute string addr;attribute string phone;attribute integer ssNo;relationship Set<Account> ownsAcctsinverse Account::ownedBy;}class Account {attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts}Exercise 4.2.4class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemalerelationship Person fatherOfinverse Person::childrenOfMalerelationship Set<Person> childreninverse Person::parentsOfrelationship Set<Person> childrenOfFemaleinverse Person::motherOfrelationship Set<Person> childrenOfMaleinverse Person::fatherOfrelationship Set<Person> parentsOfinverse Person::children}Notice that there are six different relationships here. For example, the inverse of the relationship that connects a person to their (unique) mother is a relationship that connects a mother (i.e., a female person) to the set of her children. That relationship, which we call childrenOfFemale, is different from the children relationship, which connects anyone -- male or female -- to their children.Exercise 4.2.7A relationship R is its own inverse if and only if for every pair (a,b) in R, the pair (b,a) is also in R. In the terminology of set theory, the relation R is ``symmetric.''Solutions for Section 4.3Exercise 4.3.1We think that Social Security number should me the key for Customer, and account number should be the key for Account. Here is the ODL solution with key and extent declarations.class Customer(extent Customers key ssNo){attribute string name;attribute string addr;attribute string phone;attribute integer ssNo;relationship Set<Account> ownsAcctsinverse Account::ownedBy;}class Account(extent Accounts key number){attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts}Solutions for Section 4.4Exercise 4.4.1(a)Since the relationship between customers and accounts is many-many, we should create a separate relation from that relationship-pair.Customers(ssNo, name, address, phone)Accounts(number, type, balance)CustAcct(ssNo, number)Exercise 4.4.1(d)Ther is only one attribute, but three pairs of relationships from Person to itself. Since motherOf and fatherOf are many-one, we can store their inverses in the relation for Person. That is, for each person, childrenOfMale and childrenOfFemale will indicate that persons's father and mother. The children relationship is many-many, and requires its own relation. This relation actually turns out to be redundant, in the sense that its tuples can be deduced from the relationships stored with Person. The schema:Persons(name, childrenOfFemale, childrenOfMale)Parent-Child(parent, child)Exercise 4.4.4Y ou get a schema like:Studios(name, address, ownedMovie)Since name -> address is the only FD, the key is {name, ownedMovie}, and the FD has a left side that is not a superkey.Exercise 4.4.5(a,b,c)(a) Struct Card { string rank, string suit };(b) class Hand {attribute Set theHand;};For part (c) we have:Hands(handId, rank, suit)Notice that the class Hand has no key, so we need to create one: handID. Each hand has, in the relation Hands, one tuple for each card in the hand.Exercise 4.4.5(e)Struct PlayerHand { string Player, Hand theHand };class Deal {attribute Set theDeal;}Alternatively, PlayerHand can be defined directly within the declaration of attribute theDeal. Exercise 4.4.5(h)Since keys for Hand and Deal are lacking, a mechanical way to design the database schema is to have one relation connecting deals and player-hand pairs, and another to specify the contents of hands. That is:Deals(dealID, player, handID)Hands(handID, rank, suit)However, if we think about it, we can get rid of handID and connect the deal and the player directly to the player's cards, as:Deals(dealID, player, rank, suit)Exercise 4.4.5(i)First, card is really a pair consisting of a suit and a rank, so we need two attributes in a relation schema to represent cards. However, much more important is the fact that the proposed schema does not distinguish which card is in which hand. Thus, we need another attribute that indicates which hand within the deal a card belongs to, something like:Deals(dealID, handID, rank, suit)Exercise 4.4.6(c)Attribute b is really a bag of (f,g) pairs. Thus, associated with each a-value will be zero or more (f,g) pairs, each of which can occur several times. We shall use an attribute count to indicate the number of occurrences, although if relations allow duplicate tuples we could simply allow duplicate (a,f,g) triples in the relation. The proposed schema is:C(a, f, g, count)Solutions for Section 4.5Exercise 4.5.1(b)Studios(name, address, movies{(title, year, inColor, length,stars{(name, address, birthdate)})})Since the information about a star is repeated once for each of their movies, there is redundancy. To eliminate it, we have to use a separate relation for stars and use pointers from studios. That is: Stars(name, address, birthdate)Studios(name, address, movies{(title, year, inColor, length,stars{*Stars})})Since each movie is owned by one studio, the information about a movie appears in only one tuple of Studios, and there is no redundancy.Exercise 4.5.2Customers(name, address, phone, ssNo, accts{*Accounts})Accounts(number, type, balance, owners{*Customers})Solutions for Section 4.6Exercise 4.6.1(a)We need to add new nodes labeled George Lucas and Gary Kurtz. Then, from the node sw (which represents the movie Star Wars), we add arcs to these two new nodes, labeled direc tedBy and producedBy, respectively.Exercise 4.6.2Create nodes for each account and each customer. From each customer node is an arc to a node representing the attributes of the customer, e.g., an arc labeled name to the customer's name. Likewise, there is an arc from each account node to each attribute of that account, e.g., an arc labeled balance to the value of the balance.To represent ownership of accounts by customers, we place an arc labeled owns from each customer node to the node of each account that customer holds (possibly jointly). Also, we placean arc labeled ownedBy from each account node to the customer node for each owner of that account.Exercise 4.6.5In the semistructured model, nodes represent data elements, i.e., entities rather than entity sets. In the E/R model, nodes of all types represent schema elements, and the data is not represented at all. Solutions for Section 4.7Exercise 4.7.1(a)<STARS-MOVIES><STAR starId = "cf" starredIn = "sw, esb, rj"><NAME>Carrie Fisher</NAME><ADDRESS><STREET>123 Maple St.</STREET><CITY>Hollywood</CITY></ADDRESS><ADDRESS><STREET>5 Locust Ln.</STREET><CITY>Malibu</CITY></ADDRESS></STAR><STAR starId = "mh" starredIn = "sw, esb, rj"><NAME>Mark Hamill</NAME><ADDRESS><STREET>456 Oak Rd.<STREET><CITY>Brentwood</CITY></ADDRESS></STAR><STAR starId = "hf" starredIn = "sw, esb, rj, wit"><NAME>Harrison Ford</NAME><ADDRESS><STREET>whatever</STREET><CITY>whatever</CITY></ADDRESS></STAR><MOVIE movieId = "sw" starsOf = "cf, mh"><TITLE>Star Wars</TITLE><YEAR>1977</YEAR></MOVIE><MOVIE movieId = "esb" starsOf = "cf, mh"><TITLE>Empire Strikes Back</TITLE><YEAR>1980</YEAR></MOVIE><MOVIE movieId = "rj" starsOf = "cf, mh"><TITLE>Return of the Jedi</TITLE><YEAR>1983</YEAR></MOVIE><MOVIE movieID = "wit" starsOf = "hf"><TITLE>Witness</TITLE><YEAR>1985</YEAR></MOVIE></STARS-MOVIES>Exercise 4.7.2<!DOCTYPE Bank [<!ELEMENT BANK (CUSTOMER* ACCOUNT*)><!ELEMENT CUSTOMER (NAME, ADDRESS, PHONE, SSNO)> <!A TTLIST CUSTOMERcustId IDowns IDREFS><!ELEMENT NAME (#PCDA TA)><!ELEMENT ADDRESS (#PCDA TA)><!ELEMENT PHONE (#PCDA TA)><!ELEMENT SSNO (#PCDA TA)><!ELEMENT ACCOUNT (NUMBER, TYPE, BALANCE)><!A TTLIST ACCOUNTacctId IDownedBy IDREFS><!ELEMENT NUMBER (#PCDA TA)><!ELEMENT TYPE (#PCDA TA)><!ELEMENT BALANCE (#PCDA TA)>]>Database Systems: The CompleteBookSolutions for Chapter 5Solutions for Section 5.2Exercise 5.2.1(a)PI_model( SIGMA_{speed >= 1000} ) (PC)Exercise 5.2.1(f)The trick is to theta-join PC with itself on the condition that the hard disk sizes are equal. That gives us tuples that have two PC model numbers with the same value of hd. However, these two PC's could in fact be the same, so we must also require in the theta-join that the model numbers be unequal. Finally, we want the hard disk sizes, so we project onto hd.The expression is easiest to see if we write it using some temporary values. We start by renaming PC twice so we can talk about two occurrences of the same attributes.R1 = RHO_{PC1} (PC)R2 = RHO_{PC2} (PC)R3 = R1 JOIN_{PC1.hd = PC2.hd AND PC1.model <> PC2.model} R2R4 = PI_{PC1.hd} (R3)Exercise 5.2.1(h)First, we find R1, the model-speed pairs from both PC and Laptop. Then, we find from R1 those computers that are ``fast,'' at least 133Mh. At the same time, we join R1 with Product to connect model numbers to their manufacturers and we project out the speed to get R2. Then we join R2 with itself (after renaming) to find pairs of different models by the same maker. Finally, we get our answer, R5, by projecting onto one of the maker attributes. A sequence of steps giving the desired expression is: R1 = PI_{model,speed} (PC) UNION PI_{model,speed} (Laptop)R2 = PI_{maker,model} (SIGMA_{speed>=700} (R1) JOIN Product)R3 = RHO_{T(maker2, model2)} (R2)R4 = R2 JOIN_{maker = maker2 AND model <> model2} (R3)R5 = PI_{maker} (R4)Exercise 5.2.2Here are figures for the expression trees of Exercise 5.2.1 Part (a)Part (f)Part (h). Note that the third figure is not really a tree, since it uses a common subexpression. We could duplicate the nodes to make it a tree, but using common subexpressions is a valuable form of query optimization. One of the benefits one gets from constructing ``trees'' for queries is the ability to combine nodes that represent common subexpressions.Exercise 5.2.7The relation that results from the natural join has only one attribute from each pair of equated attributes. The theta-join has attributes for both, and their columns are identical.Exercise 5.2.9(a)If all the tuples of R and S are different, then the union has n+m tuples, and this number is the maximum possible.The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Surely the union has at least as many tuples as the larger of R and that is, max(n,m) tuples. However, it is possible for every tuple of the smaller to appear in the other, so it is possible that there are as few as max(n,m) tuples in the union.Exercise 5.2.10In the following we use the name of a relation both as its instance (set of tuples) and as its schema (set of attributes). The context determines uniquely which is meant.PI_R(R JOIN S) Note, however, that this expression works only for sets; it does not preserve the multipicity of tuples in R. The next two expressions work for bags.R JOIN DELTA(PI_{R INTERSECT S}(S)) In this expression, each projection of a tuple from S onto the attributes that are also in R appears exactly once in the second argument of the join, so it preserves multiplicity of tuples in R, except for those thatdo not join with S, which disappear. The DELTA operator removes duplicates, as described in Section 5.4.R - [R - PI_R(R JOIN S)] Here, the strategy is to find the dangling tuples of R and remove them.Solutions for Section 5.3Exercise 5.3.1As a bag, the value is {700, 1500, 866, 866, 1000, 1300, 1400, 700, 1200, 750, 1100, 350, 733}. The order is unimportant, of course. The average is 959.As a set, the value is {700, 1500, 866, 1000, 1300, 1400, 1200, 750, 1100, 350, 733}, and the average is 967. H3>Exercise 5.3.4(a)As sets, an element x is in the left-side expression(R UNION S) UNION Tif and only if it is in at least one of R, S, and T. Likewise, it is in the right-side expressionR UNION (S UNION T)under exactly the same conditions. Thus, the two expressions have exactly the same members, and the sets are equal.As bags, an element x is in the left-side expression as many times as the sum of the number of times it is in R, S, and T. The same holds for the right side. Thus, as bags the expressions also have the same value.Exercise 5.3.4(h)As sets, element x is in the left sideR UNION (S INTERSECT T)if and only if x is either in R or in both S and T. Element x is in the right side(R UNION S) INTERSECT (R UNION T)if and only if it is in both R UNION S and R UNION T. If x is in R, then it is in both unions. If x is in both S and T, then it is in both union. However, if x is neither in R nor in both of S and T, then it cannot be in both unions. For example, suppose x is not in R and not in S. Then x is not in R UNION S. Thus, the statement of when x is in the right side is exactly the same as when it is in the left side: x is either in R or in both of S and T.Now, consider the expression for bags. Element x is in the left side the sum of the number of times it is in R plus the smaller of the number of times x is in S and the number of times x is in T. Likewise, the number of times x is in the right side is the smaller ofThe sum of the number of times x is in R and in S.The sum of the number of times x is in R and in T.A moment's reflection tells us that this minimum is the sum of the number of times x is in R plus the smaller of the number of times x is in S and in T, exactly as for the left side.Exercise 5.3.5(a)For sets, we observe that element x is in the left side(R INTERSECT S) - T。
数据库系统原理第2阶段测试题(总13页)--本页仅作为文档封面，使用时请直接删除即可----内页可以根据需求调整合适字体及大小--江南大学现代远程教育第二阶段测试卷考试科目:《数据库系统概论》第3章至第4章（总分100分）时间：90分钟______________学习中心（教学点）批次：层次：专业：学号：身份证号：姓名：得分：一、单选题(本题共12小题，每小题2分，共24分)1． SQL语言的数据操纵语句包括 SELECT，INSERT，UPDATE和DELETE等。
其中最重要的，也是使用最频繁的语句是______。
A．SELECT B．INSERT C．UPDATE D．DELETE2．SQL语言具有两种使用方式，分别称为交互式SQL和______。
A．提示式SQL B，多用户SQLC．嵌入式SQL D．解释式SQL3．假定学生关系是S（S＃，SNAME，SEX，AGE），课程关系是C（C＃，CNAME，TEACHER），学生选课关系是SC（S＃，C＃，GRADE）。
要查找选修“COMPUTER”课程的“女”学生姓名，将涉及到关系______。
A．S B．SC，C C．S，SC D．S，C，SC4．规范化过程主要为克服数据库逻辑结构中的插入异常，删除异常以及______的缺陷。
A．数据的不一致性 B．结构不合理C．冗余度大 D．数据丢失5．关系数据库规范化是为解决关系数据库中______问题而引人的。
A．插入、删除和数据冗余 B．提高查询速度C．减少数据操作的复杂性 D．保证数据的安全性和完整性第 6到第9题基于这样的三个表：即学生表 S、课程表 C和学生选课表 SC，它们的结构如下：S（S＃，SN，SEX，AGE，DEPT）C（C＃， CN）SC（S＃，C＃，GRADE）其中：S＃为学号，SN为姓名，SEX为性别，AGE为年龄，DEPT为系别，C＃为课程号，CN11为课程名，GRADE为成绩。
实验二以图形界面方式进行数据库和表的创建实验目的:掌握使用图形界面的方式进行库和表的创建，以及数据的插入方法。
实验内容及要求:1、利用图形界面方式创建数据库；2、利用图形界面方式创建一个模式；3、利用图形界面方式在模式中创建表；4、利用图形界面方式在表中插入数据。
实验工具：企业管理器——可以运行在多种操作系统平台上的图形界面总控管理平台。
它允许用户、程序员和管理员进行管理和配置数据库服务器、管理各种数据库对象、管理数据安全、监视数据库服务活动、诊断修改和优化数据库等操作。
企业管理器的总的设计思想是记录下用户通过图形方式进行的操作，并转换成相应的SQL语句。
实验过程及步骤：一、创建TEST数据库创建步骤：打开企业管理器→在企业管理器的【数据库】节点，点击鼠标右键→点击【新建数据库】→弹出【新建数据库窗口】，在该窗口中的“数据库名称”后面输入要创建的数据库名，其他选项默认即可→点击【确定】。
图1 新建数据库二、在TEST数据库中创建SCOT模式实验一中已将TEST数据库创建完成，接下来需要在该数据库中创建SCOT 模式。
模式（Schema）实际上是一个名字空间，它包含命名对象(表，视图，存储过程，函数和序列)。
创建步骤：打开企业管理器→在企业管理器的【模式】节点，点击鼠标右键→点击【新建模式】→弹出【新建模式窗口】，在该窗口中的“模式名”后面输入要创建的模式名，点击【确定】。
图2 新建模式三、创建表在SCOT模式中创建三张表，分别为DEPT部门表、EMP员工表和SALGRADE工资等级表。
其中各表的结构为：DEPT表结构EMP表结构SALGRADE表结构创建步骤：打开企业管理器→在企业管理器的【表】节点，点击鼠标右键→点击【新建表】→弹出【新建表窗口】，在该窗口中的设置列名、数据类型、主键、精度等，点击【保存】，在窗口中输入表名。
图3 创建表四、在表中插入数据DEPT表数据EMP表数据SALGRADE表数据创建步骤：打开企业管理器→在企业管理器的【表】节点中找到插入数据的表名→点击鼠标右键→点击【打开表】下的【返回所有行】→弹出【打开表窗口】，在该窗口中的输入具体数据。
关系模型有如下优点
1．数据结构简单
在关系模型中，数据模型是⼀些表格的框架，实体通过关系的属性（即表格的栏⽬）表⽰，实体之间的联系通过这些表格中的公共属性（可以不同属性名，但必须同域）表⽰。
结构⾮常简单，即使⾮专业⼈员也能⼀看就明⽩。
2．查询与处理⽅便
在关系模型中，数据的操作较⾮关系模型⽅便，它的⼀次操作不只是⼀个元组，⽽可以是⼀个元组集合。
特别在⾼级语⾔的条件语句配合下，⼀次可操作所有满⾜条件的记录。
3．数据独⽴性很⾼
在关系模型中，⽤户对数据的操作可以不涉及数据的物理存储位置，⽽只须给出数据所在的表、属性等有关数据⾃⾝的特性即可，具有较⾼的数据独⽴性。
4．坚实的理论基础
与状模型和层次模型不同，关系模型⼀开始便注重理论研究。
在数据库领域专家的不懈努⼒下，关系系统的研究⽇趋完善，⽽且也促进了其它软件分⽀如软件⼯程的发展。
关系模型也存在的不⾜的地⽅：
1．查询效率低
关系模型的数据库管理系统提供了较⾼的数据独⽴性和⾮过程化的查询功能，因此系统的负担很重，直接影响查询速度和查询效率。
2．关系DBMS实现较困难
由于关系数据库管理系统的效率⽐较低，必须对关系模型的查询进⾏优化，这⼀⼯作相当复杂，实现难度⽐较⼤。
