数据库习题精讲2
数据库基础课后应用题作业讲解
•·
使用参数化查询或预编译语句，确保用 户输入被正确地转义，以防止SQL注入 攻击。
问题一：如何避免SQL注入？
防止SQL注入的关键在于对用户输入的 验证和转义。
使用最小权限原则，即数据库连接使用 的账户只有执行必要操作的权限，避免 使用高权限账户。
对用户输入进行严格的验证，只接受预 期的数据格式，例如，限制长度、类型 和范围。
优化查询语句，避免使用 全表扫描，尽量使用限制 条件来缩小查询范围。
问题三：如何处理大量数据？
使用分布式数据库系统，如
Hadoop、Spark等，将数据分散
到多个节点上进行处理。
使用数据分片技术，将数据分散
•·
到不同的数据库或服务器上，以
提高处理速度和可扩展性。
处理大量数据需要使用大数据处 理技术和工具。
作业目的和要求
目的
通过完成应用题作业，学生可以进一步巩固和拓展所学的数据库基础知识，提高自己的问题解 决能力和实践能力，为后续的学习和工作打下坚实的基础。
要求
学生需要在规定的时间内完成作业，并按照要求提交作业。作业需要包含详细的步骤和结果， 以及必要的分析和解释。学生还需要参加作业讲解，与其他同学分享自己的解题思路和经验。
03 关系型数据库的优点
数据完整性、结构化查询语言（SQL）的标准化、 易于维护和扩展。
数据表与字段
01 数据表
关系型数据库中的基本存储单位，用于存储特定 类型的数据。
02 字段
数据表中的列，表示数据的某个属性或特征。
03 数据表和字段的设计原则
明确性、规范性、可扩展性、完整性。
数据表与字段
01 数据表
•·
2. 准备数据：确保要插入的数据是准确和完整的，并 且与表结构相匹配。
数据库系统基础教程(第二版)课后习题答案2
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
4.30 设有关系模式R(ABCD)，其上的FD集为 ② F={AB→C，C→A，C→D}， ρ＝{ACD, BC} ⑤ F={A→B，B→C，C→D}， ρ＝{AB,AD,CD}
解② ：(1)R的关键码 AB、BC (2) ρ中有R1(ACD)， R2(BC)
R1∩R2=C， R1－R2=AD， 由F经合并性推理得： C→AD，ρ无损
F2={职工编号→部门名，部门名→部门经理} Key2: 职工编号, R2无部分依赖，达2NF
(3)分解成3NF R1无传递依赖，达3NF R2中：职工编号→部门经理 是传递依赖关系
将R2分解为3NF模式集 R21(职工编号,部门名) R22(部门名，部门经理 )
ρ＝{(职工编号,日期,日营业额)， (职工编号,部门名)，(部门名,部门经理)}
R2(CNO, TNAME, TADDR) } F2={CNO→TNAME,TNAME→TADDR}, KEY=(CNO) ρ中R1、R2的所有属性都是原子的，且没有非 主属性对候选键的部分函数依赖，达到2NF;
(3) 试把R分解成3NF模式集，并说明理由;
ρ={ R1(SNO, CNO, GRADE) , F1={{SNO,CNO}→GRADE}, KEY=(SNO, CNO) R21(CNO, TNAME) , F21={CNO→TNAME}, KEY=(CNO)
F={ { SNO,CNO }→GRADE ， CNO→TNAME ， TNAME→TADDR }
KEY={ SNO,CNO }
(2) 试把R分解成2NF模式集，并说明理由;
ρ={ R1(SNO, CNO, GRADE) , F1={{ SNO,CNO }→GRADE}, KEY=(SNO, CNO)
数据库原理复习练习题含答案(二)
数据库原理复习练习题含答案泰山学院信息科学技术学院计算机科学与技术专业数据库系统概论本科试卷（试卷共6页，答题时间120分钟）题号一二三四五总分统分人复核人得分得分阅卷人一、选择题（每小题2分，共20 分。
请将答案填在下面的表格内）题号 1 2 3 4 5 6 78910答案1、数据库管理系统是管理控制数据库的主要软件，简称（）。
A、DBB、DBMSC、DBSD、DBT2、反映现实世界中实体及实体间联系的信息模型是()。
A.关系模型B.层次模型C.网状模型D.E-R模型3、关系数据模型的三个组成部分中，不包括()。
A. 数据结构B. 数据操作C.数据控制D. 完整性规则4、下列语句中，（）不属于SQL中DML的语句A.SELECTB.CREATEC.INSERTD.DELETE5、一个关系模式属于3NF，是指( )。
A.每个非主属性都不传递依赖于主键B.主键唯一标识关系中的元组C.关系中的元组不能重复D.每个属性都是不可分解的6、设有关系R（A，B，C）R上的函数依赖集F={A→B，A→C}。
则关系R属于( )A.1NFB.2NFC.3NFD.BCNF7、DBMS在运行过程中建立的日志文件，主要用于对数据库的（）A．安全性控制 B. 并发调度控制C．数据库恢复 D. 完整性控制8、设关系模式R是3NF模式，那么下列说明不正确的是（）。
A．R必是2NF模式 B.R必定不是BCNFC．R可能不是BCNF D.R必定是1NF模式9、.若事务T1已经给数据Q加上了S锁，则事务T2对Q可以（）A. 加S锁B. 加X锁C. 加S锁，或X锁D.不能再给Q加任何锁10、SQL提供的触发器机制是对数据库系统采取的一种（）措施。
A、完整性控制B、安全性控制C、数据库恢复D、事务并发控制得分阅卷人二、填空题（每题2分，共20分）1、数据模型的三个组成部分是___________ 、数据操作和完整性约束规则。
2、事务故障包括事物内部故障、_________、介质故障、计算机病毒。
数据库原理及应用习题课2
已知每个职工可以参加多个项目,每个项目可以由多名职工参加;每 个项目使用多种零件,每种零件可被多个项目使用. 其中,职工有:职工号,职工名; 零件有:零件号,零件名; 项目有:项目号,项目名; 每个项目使用某种部件有相应数量,职工都有参加项目的日期. (1)据上述语义设计E-R模型,要求标注联系类型,可省略属性.(5 分) (2)将E-R模型转换成关系数据模型,并指出每一个关系的主键和外 键(如果存在).(5分) 41.设有关系模式R(A,B,C,D,E),R的函数依赖集F={A→D, E→D, D→B,BC→D,CD→A },求 (1) R的候选关键字; (2) 将R分解为3NF的关系模式集.
B,C),下列结论错误的是( C ). A.若A→B, B→C,则A→C B.若A→B, A→C,则A→BC C.若BC→A,则B→A,C→A D.若B→A ,C→A,则BC→A 存在非主属性对码的部分依赖的关系模式是( A ) . A.INF B.2NF C.3NF D.BCNF 设关系模式R<U,F>,其中U={A,B,C,D,E},F={A→BC,C→D, BC→E,E→A},则分解P={R1(ABCE),R2(CD)}满足( ). A.具有无损连接性,保持函数依赖 B.不具有无损连接性,保持函数依赖 C.具有无损连接性,不保持函数依赖 D.不具有无损连接性,不保持函数依赖
关系模式R属性集为{A,B,C},函数依赖集F={AB→C,AC→B, B→C},则R属于( ). A.1NF B.2NF C.3NF D.BCNF 给定关系R(A1,A2,A3,A4)上的函数依赖集F={A1A3->A2,A2>A3},R候选关键字为( ).分解ρ={(A1,A2),(A1,A3)} ( ). A.A1A3 B.A1A2A3 C.A1A3A4 D.A2和A1A3 A.是无损联接的 B.是保持函数依赖的 C.既是无损联接又保持函数依赖 D.既是有损联接又不保持函数依赖 若R∈1NF,且R中只有一个主属性,则R必然满足( ) A.2NF B.3NF C.4NF D.BCNF 设有关系模式R(A,B,C,D),F是R上成立的FD集,F={B→C, C→D},则属性C的闭包C+为( ) A.BC B.BCD C.BD D.CD
数据库第二章课后习题解答
第3部分习题及其解答第一章的两道题3-2 习题22.6 分别把习题1.10、习题1.11的ER图转换成关系模型数据结构。
【参考答案】1．习题1.10的ER图可转换成如下的关系模型数据结构。
①程序员(编号，，性别，年龄，单位，职称)，其中编号是关键字；②程序(程序名称，，专利号，价格)，其中程序名称是关键字；③设计(编号，程序名称，开始时间，结束时间)，其中(编号，程序名称)是关键字。
2．习题1.11的ER图可转换成如下的关系模型数据结构。
①工厂(工厂名称，厂址，联系)，其中工厂名称是关键字；②产品(产品号，产品名，规格，单价)，其中产品号是关键字；③工人(工人编号，，性别，职称，工厂名称，雇用期，月薪)，其中工人编号是关键字，工厂名称是外关键字，雇用期和月薪是联系属性；④生产(工厂名称，产品号，月产量)，其中(工厂名称，产品号)是关键字,生产关系是表示联系的。
2.8 判断下列情况，分别指出它们具体遵循那一类完整性约束规则？1．用户写一条语句明确指定月份数据在1～12之间有效。
2．关系数据库中不允许主键值为空的元组存在。
3．从A关系的外键出发去找B关系中的记录，必须能找到。
【解答】1．用户用语句指定月份数据在1～12之间有效，遵循用户定义的完整性约束规则。
2．关系数据库中不允许主键值为空的元组存在，遵循实体完整性约束规则；3．从A关系的外键出发去找B关系的记录,必须能找到，遵循引用完整性约束规则。
2.9 判断下列情况，分别指出他们是用DML还是用DDL来完成下列操作？1．创建“学生”表结构。
2．对“学生”表中的学号属性，其数据类型由“整型”修改为“字符型”。
3．把“学生”表中学号“021”修改为“025”。
【解答】1．创建“学生”表结构，即定义一个关系模式，用DDL完成。
2．修改“学生”表中学号属性的数据类型,即修改关系模式的定义，用DDL完成。
3．修改“学生”表中学号属性的数据值,即对表中的数据进行操作，用DML完成。
数据库附录2习题ppt课件
1． 图书出版管理数据库中有两个基本表： 图书 (书号，书名，作者编号，出版社，出版日 期) 作者 (作者编号，作者名，年龄，地址) 试用SQL语句写出下列查询：检索年龄低于作者平 均年龄的所有作者的作者名、书名和出版社。
SELECT 作者名，书名，出版社 FROM 图书，作者 WHERE 图书.作者编号 = 作者.作者编号 AND 年龄 < = (SELECT AVG (年龄) FROM 作者)；
12. SQL中，下列涉及空值的操作，不正确的是 【 】 A. AGE IS NULL B. AGE IS NOT NULL C. AGE = NULL D. NOT (AGE IS NULL) C
7
13.下列聚合函数中不忽略空值 (null) 的是 【 】 A. SUM (列名) B. MAX (列名) C. COUNT ( * ) D. AVG (列名)
D
4.用树型结构表示实体类型及实体间联系的数据模型称为 () A.关系模型 B.层次模型 C.网状模型 D.E-R
B
3
5.关系数据模型的三个组成部分中，不包括( )
A.完整性规则 C.数据操作
B.数据结构 D.
D
6．数据库系统的体系结构是【 】 A. 二级模式结构和一级映像 B. 三级模式结构和一级映像 C. 三级模式结构和二级映像 D. 三级模式结构和三级映像
33
2． 现有关系数据库如下： 学生(学号，姓名，性别，专业，奖学金) 课程(课程号，名称，学分) 学习(学号，课程号，分数) 用关系代数表达式实现下列1—4 (1).检索“英语”专业学生所学课程的信息，包括学 号、姓名、课程名和分数。
П学号，姓名，课程名，分数(σ专业='英语'(学生∞学习∞课程))
数据库学习课程第二章习题和答案
数据库学习课程第二章习题和答案一.单项选择1.SQL语言是 B 的语言,易学习.A 过程化B 非过程化C 格式化D 导航式提示:SQL是一种介于关系代数与关系演算之间的结构化查询语言,它是高度非过程化的.2.SQL语言是 C 语言.A 层次数据库B 网络数据库C 关系数据库D 非数据库提示:SQL是关系数据库标准语言.3.SQL语言具有 B 的功能.A 关系规范化,数据操纵,数据控制B 数据定义,数据操纵,数据控制C 数据定义,关系规范化,数据控制D 数据定义,关系规范化,数据操纵提示:SQL语言自身不具备关系规范化功能.4.在SQL中,用户可以直接操作的是 D .A 基本表B 视图C 基本表和视图D 基本表和视图5.在SQL语言中,实现数据检索的语句是 A .A SELECTB INSERTC UPDATED DELETE6.SELECT语句执行结果是 C .A 数据项B 元组C 表D 数据库7.在SQL语句中,对输出结果排序的语句是 B .A GROUP BYB ORDER BYC WHERED HA VING8.在SELECT语句中,需对分组情况满足的条件进行判断时,应使用 D .A WHEREB GROUP BYC ORDER BYD HA VING9.在SELECT语句中使用*表示 B .A 选择任何属性B 选择全部属性C 选择全部元组D 选择主码10.在SELECT语句中,使用MAX(列名)时,该”列名”应该 D .A 必须是数值型B 必须是字符型C 必须是数值型或字符型D 不限制数据类型11.使用CREATE TABLE语句创建的是 B .A 数据库B 表C 试图D 索引12.下列SQL语句中,修改表结构的是 A .A ALTERB CREATEC UPDATED INSERT13.在SQL中使用UPDATE语句对表中数据进行修改时,应使用的语句是 D .A WHEREB FROMC V ALUESD SET14.视图建立后,在数据库中存放的是 C .A 查询语句B 组成视图的表的内容C 视图的定义D 产生视图的表的定义15.以下叙述中正确的是 B .A SELECT命令是通过FOR子句指定查询条件B SELECT命令是通过WHERE子句指定查询条件C SELECT命令是通过WHILE子句指定查询条件D SELECT命令是通过IS子句指定查询条件16.与WHERE AGE BETWEEN 18 AND 23完全等价的是 D .A WHERE AGE>18 AND AGE<23B WHERE AGE<18 AND AGE>23C WHERE AGE>18 AND AGE<=23 D WHERE AGE>=18 AND AGE<=2317.在查询中统计记录(元组)的个数时,应使用 C 函数.A SUMB COUNT(列名)C COUNT(*)D A VG18.在查询中统计某列中值的个数应使用 B 函数.A SUMB COUNT(列名)C COUNT(*)D A VG19.已知基本表SC(S#,C#,GRADE),其中S#为学号,C#为课程号,GRADE为成绩.则”统计选修了课程的学生人数”的SQL—SELECT语句为 A .A SELECT COUNT(DISTINCT S#)FROM SCB SELECT COUNT(S#)FROM SC C SELECT COUNT()FROM SCD SELECT COUNT(DISTINCT *)FROM SC20.在数据库中有如图所示的两个表,若职工表的主码是职工号,部门表的主码是部门号,SQL 操作 B 不能执行.A 从职工表中删除行(‘025’,’王芳’,’03’,720)B 将行(‘005’,’乔兴’,’04’,750)插入到职工表中C 将职工号为”001”的工资改为700D 将职工号为”038”的部门号改为’03’提示:由于职工表中的职工号为主码,不能向其中插入同主码的记录.21.若用如下SQL语句创建一个表studentCREATE TABLE student(NO CHAR(4) NOT NULL,NAME CHAR(8) NOT NULL,SEX CHAR(2),AGE INT)可以插入到student 表中的是 B .A (‘1031’,’曾华’,男,23)B (‘1031’,’曾华’,NULL,NULL)C (NULL,’曾华’,’男’,’23’)D (‘1031’,NULL,’男’,23)提示:A 中性别SEX 属性值格式不正确,C 中NO 属性值不能为空,D 中NAME 属性值不能为空.22.假设学生关系是S(S#,SNAME,SEX,AGE),课程关系是C(C#,CNAME,TEACHER),学生选课关系是SC(S#,C#,GRADE).要查询选修”COMPUTER ”课程的”女”同学的姓名,将涉及关系 D .A SB SC,C C S,SCD S,SC,C 二.填空1.SQL 语言的数据定义功能包括 定义数据 、 定义基本表 、 定义视图 、 定义索引 。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第四章习题
1.填空题
（1）设F是基本关系R的一个属性或属性组，F不是R的码（主码或候选码），但F是关系S的主码，则称F是R的____外码_______，R为____参照关系_____表，S为_____被参照_____表。
（2）设关系R和S的属性个数分别是3和4，元组个数分别是100和300，关系T是R和S的广义笛卡尔积，则T的属性个数是_____7_________，元组个数是___30000_________。
2.单选题
（1）在通常情况下，下面的关系中不可以作为关系数据库的关系是（ D ）。
A、R1（学生号，学生名，性别）
B、R2（学生号，学生名，班级号）
C、R3（学生号，学生名，宿舍号）
D、R4（学生号，学生名，简历）
（2）一个关系数据库文件中的各条记录（ B ）。
A、前后顺序不能任意颠倒，一定要按照输入的顺序排列
B、前后顺序可以任意颠倒，不影响库中的数据关系
C、前后顺序可以任意颠倒，但排列顺序不同，统计处理的结果就可能不同
D、前后顺序不能任意颠倒，一定要按照码段值的顺序排列
（3）同一个关系模型的任意两个元组值（ A ）。
A、不能全同
B、可全同
C、必须全同
D、以上都不是
（4）设属性A是关系R的主属性，则属性A不能取空值（NULL）。
这是（ A ）。
A、实体完整性规则
B、参照完整性规则
C、用户自定义完整性规则
D、域完整性规则（5）下列关系中的基本名词，能唯一地标识一个元组的是（ A、C ）。
A、主属性
B、属性
C、候选码
D、以上三都均可
（6）外码必须为空值或等于被参照表中某个元组的主码。
这是（ B ）。
A、实体完整性规则
B、参照完整性规则
C、用户自定义完整性规则
D、域完整性规则（7）当对关系R和S使用自然连接时，要求R和S含有一个或多个共有的（ D ）。
A、元组
B、行
C、记录
D、属性
（8） R-(R-S)等同于（ A ）。
A、R∩S
B、R∪S
C、R－S
D、R×S
（9）在关系代数运算中，不属于基本运算的是（ D）。
A、差
B、交
C、并
D、乘积
3.判断题
（1）关系操作的方式是一次一集合。
（√）
（2）笛卡尔积就是关系。
（√）
（3）在一个关系模式中，候选码可有多个，主码只能有一个，主码可以不属于候选码。
（×）【主码属于候选码】
（4）主码中的属性叫做主属性。
（×）[候选码中的属性叫做主属性]
4.简答
（1）简述关系的五个特征。
答：
（2）何种情况下，外码的值不能为空？
答：设F是基本关系R的一个或一组属性，但不是关系R的主码（或候选码）。
如果F与基本关系S的主码Ks相对应，则称F是基本关系R的外码，并称基本关系R为参照关系，基本关系S为被参照关系或目标关系。
所以被参照关系的主码非空时，外码值不能为空。
（3）已知三个关系模式图书（书号，书名，出版社，负责人），读者（读者号，身份证号，姓名，性别，年龄），借阅（读者号，书号，借阅日期，归还日期）。
指出每个关系模式的候选码，主码，外码，主属性。
答：图书关系模式的主码为：书号。
候选码：书号。
主属性:书号。
外码：无。
、
读者关系模式的主码为：读者号。
候选码：读者号，身份证号。
主属性：读者号，身份证号。
外码：无。
借阅关系模式的主码为：书号。
候选码：书号。
主属性：书号。
外码：读者号。
（4）简述连接、等值连接和自然连接的定义。
答：连接运算【Join】是从两个关系的笛卡尔积中选取属性间满足一定条件的远足。
自然连接：要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。
（一种特殊的等值连接）
等值连接：是从关系R和S的广义笛卡尔积中选取A和B 属性值相等的那些元组。
5.综合题
（1）已知域D1=零件号集合={P1,P2,P3}；D2=颜色集合={红色，蓝色}；D3=产地集合={北京，上海}用二维表表示D1×D2×D3的笛卡尔积，并计算笛卡尔积的基数。
笛卡尔的基数为：12
（2）已知关系R1，R2分别是
（3）
求等值连接：选课∞课程
选课．课程号＝课程．课程号选课∞课程 =
选课．课程号＝课程．课程号
自然连接：选课∞课程
6.语法题
（1）对于学生选课关系，其关系模式如下：
学生（学号，姓名，年龄，所在系）
课程（课程名，课程号，先行课）
选课（学号，课程号，成绩）
试用关系代数完成下列查询：
①求成绩不及格的学生学号和姓名
∏学号、姓名（σ成绩<60(选课)∝（学生））
②求学过数据库课程的学生学号和姓名
∏学号、姓名（σ课程名=‘数据库’（课程）∝（选课）∝（学生））
③求数据库成绩不及格的学生学号和姓名
∏学号、姓名（σ课程名=‘数据库’∧成绩<60(（课程）∝（选课）)∝（学生））
④求学过数据库和数据结构课程的学生学号和姓名
∏学号、姓名（（σ课程名=‘数据库’（课程）∩σ课程名='数据结构’（课程））∝（选课）∝（学生））
⑤求学过数据库或数据结构课程的学生学号和姓名
∏学号、姓名（（σ课程名=‘数据库’（课程）∪σ课程名='数据结构’（课程））∝（选课）∝（学生））
⑥求没学过数据库课程的学生学号
∏学号（学生）-∏学号（σ课程名=‘数据库’（课程）∝（选课））
⑦求学过数据库的先行课的学生学号
∏学号（∏先行课（σ课程名=‘数据库’（课程））∝（课程）∝（选课））
⑧求选修了全部课程的学生学号和姓名
∏学号、姓名（（选课）÷(课程））∝（学生））。