数据库系统教程第二章部分习题解答
数据库系统教程(何玉洁 李宝安 编著)第2章习题答案
第2章数据模型与数据库结构习题答案1.解释数据模型的概念，为什么要将数据模型分成两个层次？数据模型（Data Model）是对现实世界数据特征的抽象。
根据模型应用的不同目的，分为两大类。
2.概念层数据模型和组织层数据模型分别是面对什么的数据模型？概念层数据模型面对现实世界，组织层数据模型面对信息世界。
3.实体之间的联系有哪几种，请为每一种联系举出一个例子。
一对一联系（1：1），例：部门和经理一对多联系（1：m），例：公司和员工多对多联系（m：m），例：学生和课程4.说明实体-联系模型中的实体、属性和联系的概念。
实体是具有公共性质并可相互区分的现实世界对象的集合。
属性是描述实体或联系的性质或特征的数据项。
联系是数据间的关联关系，是客观存在的应用语义链。
5.指明下列实体间联系的种类：教研室和教师（假设一个教师只属于一个教研室，一个教研室可有多名教师）1：m商店和顾客m：m 国家和首都1：1飞机（座位）和乘客1：m6.数据库包含哪三级模式，试分别说明每一级模式的作用。
内模式描述数据的存储结构。
外模式对现实系统中用户感兴趣的整体数据的局部描述，以满足数据库不同用户对数据的需求。
模式描述数据库中全体数据的逻辑结构和特征，是所有用户的公共数据视图。
7.数据库管理系统提供的两级映像的作用是什么，它带来了哪些功能？作用：在数据库内部实现对数据库三级模式的联系和转换。
功能：保证了数据库中的数据能够具有较高的逻辑独立性和物理独立性，使数据库应用程序不随数据库数据的逻辑或存储结构的变动而变动。
8.数据库三级模式划分的优点是什么，它能带来哪些数据独立性？数据库三级模式是对数据的三个抽象级别，它把数据的具体组织留给DBMS管理，使用户能逻辑、抽象地处理数据，而不必关心数据在计算机中的具体表示方式与存储方式。
外模式/模式映像保证了程序与数据的逻辑独立性，模式/内模式映像保证了数据与程序的物理独立性。
*以上内容仅供参考。
数据库系统基础教程第二章答案解析
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExercise 2.2.1bFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)Exercise 2.2.1cFor relation Accounts and the first tuple, the components are:123456 → acctNosavings → type12000 → balanceFor relation Customers and the first tuple, the components are:Robbie → firstNameBanks → lastName901-222 → idNo12345 → accountExercise 2.2.1dFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account) Exercise 2.2.1eAn example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo → Integertype → Stringbalance → IntegerfirstName → StringlastName → StringidNo → String (because there is a hyphen we cannot use Integer)account → IntegerExercise 2.2.1gAnother equivalent way to present the Account relation:Another equivalent way to present the Customers relation:Exercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.Exercise 2.2.3aWe 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.Exercise 2.2.3bWe can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880Exercise 2.2.3cWe can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!Exercise 2.3.1aCREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));Exercise 2.3.1cCREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));Exercise 2.3.1dCREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));Exercise 2.3.1eALTER TABLE Printer DROP color;Exercise 2.3.1fALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’; Exercise 2.3.2aCREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);Exercise 2.3.2bCREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);Exercise 2.3.2cCREATE TABLE Battles (name CHAR(30),date DATE);Exercise 2.3.2dCREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));Exercise 2.3.2eALTER TABLE Classes DROP bore;Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30); Exercise 2.4.1aR1 := σspeed ≥ 3.00 (PC)R2 := πmodel(R1)Exercise 2.4.1bR1 := σhd ≥ 100 (Laptop)R2 := Product (R1)R3 := πmaker (R2)Exercise 2.4.1cR1 := σmaker=B (Product PC)R2 := σmaker=B (Product Laptop)R3 := σmaker=B (Product Printer)R4 := πmodel,price (R1)R5 := πmodel,price (R2)R6: = πmodel,price (R3)R7 := R4 R5 R6Exercise 2.4.1dR1 := σcolor = true AND type = laser (Printer)R2 := πmodel (R1)Exercise 2.4.1eR1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker (R1) R4 := πmaker (R2) R5 := R3 – R4Exercise 2.4.1fR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.hd = PC2.hd AND PC1.model <> PC2.model) R2R4 := πhd (R3)Exercise 2.4.1gR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model) R2 R4 := πPC1.model,PC2.model (R3)Exercise 2.4.1hR1 := πmodel (σspeed ≥ 2.80(PC)) πmodel (σspeed ≥ 2.80(Laptop))R2 := πmaker,model (R1 Product)R3 := ρR3(maker2,model2)(R2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker (R4)Exercise 2.4.1iR1 := πmodel,speed (PC)R2 := πmodel,speed(Laptop)R3 := R1 R2 R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5(R6 Product)Exercise 2.4.1jR1 := πmaker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3R6 := πmaker (R5)Exercise 2.4.1kR1 := πmaker,model (Product PC)R2 := ρR2(maker2,model2)(R1)R3 := ρR3(maker3,model3)(R1)R4 := ρR4(maker4,model4)(R1)R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3(maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6 R8 := πmaker (R7)Exercise 2.4.2aπmodelσspeed≥3.00PCExercise 2.4.2bLaptopσhd ≥ 100 ProductπmakerExercise 2.4.2cσmaker=B πmodel,priceσmaker=B πmodel,price σmaker=Bπmodel,priceProduct PC Laptop Printer ProductProductExercise 2.4.2dPrinter σcolor = true AND type = laserπmodelExercise 2.4.2e σtype=laptop σtype=PC πmakerπmaker –Product ProductExercise 2.4.2fρPC1ρPC2 (PC1.hd = PC2.hd AND PC1.model <> PC2.model)πhdPC PCExercise 2.4.2gρPC1ρPC2PC PC(PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model)πPC1.model,PC2.modelExercise 2.4.2hPC Laptop σspeed ≥ 2.80σspeed ≥ 2.80πmodelπmodel πmaker,modelρR3(maker2,model2)(maker = maker2 AND model <> model2)makerExercise 2.4.2iPCLaptopProductπmodel,speed πmodel,speed ρR4(model2,speed2)πmodel,speed(speed < speed2 )–makerExercise 2.4.2jProduct PC πmaker,speed ρR3(maker3,speed3)ρR2(maker2,speed2)(maker = maker2 AND speed <> speed2)(maker3 = maker AND speed3 <> speed2 AND speed3 <> speed)πmakerExercise 2.4.2kπmaker(maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) (maker3 = maker AND model3 <> model2 AND model3 <> model)(maker = maker2 AND model <> model2)ρR2(maker2,model2)ρR3(maker3,model3)ρR4(maker4,model4)πmaker,modelProduct PCExercise 2.4.3aR1 := σbore ≥ 16 (Classes)R2 := πclass,country (R1)Exercise 2.4.3bR1 := σlaunched < 1921 (Ships)R2 := πname (R1)Exercise 2.4.3cR1 := σbattle=Denmark Strait AND result=sunk(Outcomes)R2 := πship (R1)Exercise 2.4.3dR1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)R3 := πname (R2)Exercise 2.4.3eR1 := σbattle=Guadalcanal(Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := πname,displacement,numGuns(R3)Exercise 2.4.3fR1 := πname(Ships)R2 := πship(Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3Exercise 2.4.3gFrom 2.3.2, assuming that every class has one ship named after the class.R1 := πclass(Classes)R2 := πclass(σname <> class(Ships))R3 := R1 – R2Exercise 2.4.3hR1 := πcountry(σtype=bb(Classes))R2 := πcountry(σtype=bc(Classes))R3 := R1 ∩ R2Exercise 2.4.3iR1 := πship,result,date(Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship(R3)No results from sample data.Exercise 2.4.4aπclass,countryσbore ≥ 16ClassesExercise 2.4.4bπnameσlaunched < 1921ShipsExercise 2.4.4cπshipσbattle=Denmark Strait AND result=sunkOutcomesExercise 2.4.4dπnameσlaunched > 1921 AND displacement > 35000Classes Ships Exercise 2.4.4eσbattle=Guadalcanal Outcomes Ships(ship=name)πname,displacement,numGunsExercise 2.4.4f Ships Outcomesπnameπship ρR3(name)Exercise 2.4.4g Classes Shipsπclass σname <> class πclass–Exercise 2.4.4hClasses Classesσtype=bb σtype=bcπcountry πcountry∩Exercise 2.4.4iBattles Outcomes (battle=name)πship,result,dateρR2(ship2,result2,date2)(ship=ship2 AND result=damaged AND date < date2)πshipExercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple.Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the originalresult and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the originalresult. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the newresult. The original tuples are included in the new result because they still satisfy the select condition. Thusthe selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples ofanother relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesianproduct operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additionalsuccessful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection onsome condition. The new tuple can only create additional tuples in the result, not less. If, however, the addedtuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.Exercise 2.4.7aIf 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. Then the union has max(m , n) tuples.Exercise 2.4.7bIf all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pairsuccessfully with all the tuples in the other relation. Then the natural join has zero tuples.Exercise 2.4.7cIf the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Exercise 2.4.7dAssuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2133.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)Exercise 2.4.10πA1,A2…An(R S)Exercise 2.5.1aσspeed < 2.00 AND price > 500(PC) = øModel 1011 violates this constraint.Exercise 2.5.1bσscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = øModel 2004 violates the constraint.Exercise 2.5.1cπmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = øManufacturers A,B,E violate the constraint.Exercise 2.5.1dThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ⊆ R3Manufacturers B,C,D violate the constraint.Exercise 2.5.1eπmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = øModels 2002,2006,2008 violate the constraint.Exercise 2.5.2aπclass(σbore > 16(Classes)) = øThe Yamato class violates the constraint.Exercise 2.5.2bπclass(σnumGuns > 9 AND bore > 14(Classes)) = øNo violations to the constraint.Exercise 2.5.2cThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = øThe Kongo, Iowa and Revenge classes violate the constraint.Exercise 2.5.2dπcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = øJapan and Gt. Britain violate the constraint.Exercise 2.5.2eThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,bat tle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = øNo violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = øwhere is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2 = øAs a containment, we can simply say:E1⊆ E2 AND E2⊆ E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库第2章习题(答案)
第二章习题一、单项选择题1、对关系模式的任何属性：A：不可再分B：可再分C：命名在该关系模式中可以不唯一D：以上都不是答案：A2、在关系R(R#，RN，S#)和S(S#，SN，SD)中，R的主键是R#，S的主键是S#，则S#在R中称为：A：外键B：候选键C：主键D：以上都不是答案：A3、取出关系的某些列，并取消重复元组的关系代数运算称为：A：取列运算B：投影运算C：连接运算D：选择运算答案：B4、关系数据库管理系统应能实现的专门关系运算包括：A：排序、索引、统计B：选择、投影、连接C：关联、更新、排序D：显示、打印、制表答案：B5、根据关系模式的实体完整性规则，一个关系的“主键”：A：不能有两个B：不能成为另一个关系的外键C：不允许为空D：可以取值答案：C6、参加差运算的两个关系：A：属性个数可以不相同B：属性个数必须相同C：一个关系包含另一个关系的属性D：属性名必须相同答案：B7、在基本的关系中，下列说法是正确的（）。
A：行列顺序有关B：属性名允许重名C：任意两个元组不允许重复D：列是非同质的答案：C8、σ4＜‘4’（S）表示（）。
A．从S关系中挑选4的值小于第4个分量的元组B．从S关系中挑选第4个分量值小于4的元组C．从S关系中挑选第4个分量值小于第4个分量的元组D．σ4＜‘4’（S）是向关系垂直方向运算答案：B9、在连接运算中如果两个关系中进行比较的分量必须是相同的属性组，那么这个连接是：A：有条件的连接B：等值连接C：自然连接D：完全连接答案：C10、关系R与S做连接运算，选取R中A的属性值和S中B的属性值相等的那些元组，则R与S的连接是：A：有条件的连接B：等值连接C：自然连接D：完全连接答案：B11、关系a1的象集是：A：{（b1）, (c1), (d1) } B：{(b1, c1), (b2, c3) }C：{（b1, c1, d1）, (b2, c3, d4) } D：{（a1, b1, c1, d1）, (a1, b2, c3, d4) }答案：C12、关系（a3，b1）的象集是：A：{（d2）, (d4) } B：{（c2）, (c3) }C：{（c2, d2）, (c3, d4) } D：{（b1, c2, d2）, (b1, c3, d4) }答案：C13、在通常情况下，下面的关系中不可以作为关系数据库的关系是：A：R1(学生号，学生名，性别) B：R2(学生号，学生名，班级号)C：R3(学生号，学生名，宿舍名) D：R4(学生号，学生名，简历)答案：D14、“年龄在15至30岁之间”这种约束属于DBS的（）功能。
数据库系统基础教程第二章答案
Exercise relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExercise relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)Exercise relation Accounts and the first tuple, the components are:123456 acctNosavings type12000 balanceFor relation Customers and the first tuple, the components are:Robbie firstNameBanks lastName901-222 idNo12345 accountExercise relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)Exercise example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)Exercise suitable domain for each attribute:acctNo Integertype Stringbalance IntegerfirstName StringlastName StringidNo String (because there is a hyphen we cannot use Integer)account IntegerExercise equivalent way to present the Account relation:Another equivalent way to present the Customers relation:Exercise of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.Exercise 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.Exercise can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880Exercise can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m! Exercise TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));Exercise TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));Exercise TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));Exercise TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));Exercise TABLE Printer DROP color;Exercise TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;Exercise TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);Exercise TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);Exercise TABLE Battles (name CHAR(30),date DATE);Exercise TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));Exercise TABLE Classes DROP bore; Exercise TABLE Ships ADD yard CHAR(30); ExerciseR1 := σspeed ≥ (PC)R2 := πmodel(R1)model100510061013Exercise R1 := σhd ≥ 100 (Laptop)R2 := Product (R1)R3 := πmaker (R2)makerEABFGExercise R1 := σmaker=B (Product PC)R2 := σmaker=B (Product Laptop)R3 := σmaker=B (Product Printer)R4 := πmodel,price (R1)R5 := πmodel,price (R2)R6: = πmodel,price (R3)R7 := R4 R5 R6model price100464910056301006104920071429 ExerciseR1 := σcolor = true AND type = laser (Printer)R2 := πmodel (R1)model30033007ExerciseR1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker(R1)R4 := πmaker(R2)R5 := R3 – R4makerFGExercise R1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 = AND <> R2R4 := πhd(R3)hd25080160Exercise R1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 = AND = AND < R2R4 := π,(R3)10041012Exercise R1 := πmodel(σspeed ≥ (PC)) πmodel(σspeed ≥ (Laptop))R2 := πmaker,model(R1 Product)R3 := ρR3(maker2,model2)(R2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker(R4)makerBEExerciseR1 := πmodel,speed(PC)R2 := πmodel,speed(Laptop)R3 := R1 R2R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5R7 := πmaker(R6 Product)makerBExercise R1 := πmaker,speed(Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3R6 := πmaker(R5)makerADEExercise R1 := πmaker,model(Product PC)R2 := ρR2(maker2,model2)(R1)R3 := ρR3(maker3,model3)(R1)R4 := ρR4(maker4,model4)(R1)R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker(R7)makerABDEExercise πmodelσspeed≥3.00PC Laptop σhd ≥ 100 Productπmakerσmaker=B πmodel,price σmaker=B πmodel,price σmaker=B πmodel,priceProduct PC Laptop Printer ProductProductPrinter σcolor = true AND type = laser πmodel σtype=laptop σtype=PC πmakerπmaker –Product ProductρPC1ρPC2 (PC1.hd = PC2.hd AND PC1.model <> PC2.model)πhd PC PC ρPC1ρPC2PC PC (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model)πPC1.model,PC2.modelPC Laptop σspeed ≥ 2.80σspeed ≥ 2.80πmodelπmodel πmaker,modelρR3(maker2,model2)(maker = maker2 AND model <> model2)makerPC LaptopProductπmodel,speed πmodel,speed ρR4(model2,speed2)πmodel,speed(speed < speed2 )–makerProduct PC πmaker,speed ρR3(maker3,speed3)ρR2(maker2,speed2)(maker = maker2 AND speed <> speed2)(maker3 = maker AND speed3 <> speed2 AND speed3 <> speed)makerProduct PC πmaker,modelρR2(maker2,model2)ρR3(maker3,model3)ρR4(maker4,model4)(maker = maker2 AND model <> model2)(maker3 = maker AND model3 <> model2 AND model3 <> model)(maker4 = maker AND (model4=model OR model4=model2 OR model4=model3))πmakerR1 := σbore ≥ 16 (Classes)R2 := πclass,country (R1)Exercise R1 := σlaunched < 1921 (Ships)R2 := πname (R1)RevengeRoyal OakRoyal SovereignTennesseeExercise R1 := σbattle=Denmark Strait AND result=sunk(Outcomes)R2 := πship (R1)shipBismarckHoodExercise R1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)R3 := πname (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoExercise R1 := σbattle=Guadalcanal(Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := πname,displacement,numGuns(R3)name displacement numGuns Kirishima320008Washington370009Exercise R1 := πname(Ships)R2 := πship(Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDuke of YorkFusoHoodKing George VExercise From assuming that every class has one ship named after the class.R1 := πclass (Classes)R2 := πclass (σname <> class (Ships))R3 := R1 – R2ExerciseR1 := πcountry (σtype=bb (Classes))R2 := πcountry (σtype=bc (Classes))R3 := R1 ∩ R2ExerciseR1 := πship,result,date (Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1) R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship (R3)No results from sample data.Exercise σbore ≥ 16 πclass,country Classes σlaunched < 1921 πname Ships Outcomes πshipσbattle=Denmark Strait AND result=sunkClasses Ships σlaunched > 1921 AND displacement > 35000πname σbattle=Guadalcanal Outcomes Classes(ship=name)πname,displacement,numGunsPrince of Wales Rodney Scharnhorst South Dakota West Virginia Yamashiro class Bismarck country Japan Gt. BritainShips Outcomesπname πship ρR3(name) ClassesShips πclass σname <> class πclass–Classes Classes σtype=bb σtype=bc πcountry πcountry ∩Battles Outcomes (battle=name)πship,result,dateρR2(ship2,result2,date2)(ship=ship2 AND result=damaged AND date < date2)πshipresult of thenatural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise Union we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we haverelations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t . However, if we add tuple t to S, then the new result will not have tuple t . Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus theprojection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new resultbecause they still satisfy the select condition. Thus the selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples.Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples.The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of theexisting tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples.The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create additionaltuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. Therenaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.Exercise 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. Then the union has max(m , n) tuples.Exercise all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.Exercise the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible. The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Exercise that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section pg. 2134.5.R – (R –πr(R S))Exercise r as the schema of R1.R - πr(R S)Exercise …An(R S)Exercise < AND price > 500(PC) =Model 1011 violates this constraint.Exercise < AND hd < 100 AND price ≥ 1000(Laptop) =Model 2004 violates the constraint.Exercise = laptop(Product)) ∩ πmaker(σtype = pc(Product)) =Manufacturers A,B,E violate the constraint.Exercise complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 = AND ≤ R2)R4(model) := πmodel(PC)The constraint is R4 R3Manufacturers B,C,D violate the constraint.Exercise > AND ≤ (PC × Laptop)) =Models 2002,2006,2008 violate the constraint.Exercise > 16(Classes)) =The Yamato class violates the constraint.Exercise > 9 AND bore > 14(Classes)) =No violations to the constraint.Exercise complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 =The Kongo, Iowa and Revenge classes violate the constraint.Exercise = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) =Japan and Gt. Britain violate the constraint.Exercise complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 =No violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = where is the antisemijoinExercise form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2 =As a containment, we can simply say:E1 E2 AND E2 E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
(完整版)数据库系统基础教程第二章答案解析
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExercise 2.2.1bFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)Exercise 2.2.1cFor relation Accounts and the first tuple, the components are:123456 → acctNosavings → type12000 → balanceFor relation Customers and the first tuple, the components are:Robbie → firstNameBanks → lastName901-222 → idNo12345 → accountExercise 2.2.1dFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account) Exercise 2.2.1eAn example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo → Integertype → Stringbalance → IntegerfirstName → StringlastName → StringidNo → String (because there is a hyphen we cannot use Integer)account → IntegerExercise 2.2.1gAnother equivalent way to present the Account relation:Another equivalent way to present the Customers relation:Exercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.Exercise 2.2.3aWe 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.Exercise 2.2.3bWe can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880Exercise 2.2.3cWe can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!Exercise 2.3.1aCREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));Exercise 2.3.1cCREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));Exercise 2.3.1dCREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));Exercise 2.3.1eALTER TABLE Printer DROP color;Exercise 2.3.1fALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’; Exercise 2.3.2aCREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);Exercise 2.3.2bCREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);Exercise 2.3.2cCREATE TABLE Battles (name CHAR(30),date DATE);Exercise 2.3.2dCREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));Exercise 2.3.2eALTER TABLE Classes DROP bore;Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30); Exercise 2.4.1aR1 := σspeed ≥ 3.00 (PC)R2 := πmodel(R1)model100510061013Exercise 2.4.1bR1 := σhd ≥ 100 (Laptop)R2 := Product (R1)R3 := πmaker (R2)makerEABFGExercise 2.4.1cR1 := σmaker=B (Product PC)R2 := σmaker=B (Product Laptop)R3 := σmaker=B (Product Printer)R4 := πmodel,price (R1)R5 := πmodel,price (R2)R6: = πmodel,price (R3)R7 := R4 R5 R6model price1004 6491005 6301006 10492007 1429Exercise 2.4.1dR1 := σcolor = true AND type = laser (Printer)R2 := πmodel (R1)model30033007Exercise 2.4.1eR1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker(R1)R4 := πmaker(R2)R5 := R3 – R4Exercise 2.4.1fR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.hd = PC2.hd AND PC1.model <> PC2.model) R2R4 := πhd(R3)Exercise 2.4.1gR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model) R2R4 := πPC1.model,PC2.model(R3)Exercise 2.4.1hR1 := πmodel(σspeed ≥ 2.80(PC)) πmodel(σspeed ≥ 2.80(Laptop))R2 := πmaker,model(R1 Product)R3 := ρR3(maker2,model2)(R2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker(R4)Exercise 2.4.1iR1 := πmodel,speed(PC)R2 := πmodel,speed(Laptop)R3 := R1 R2R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5makerBExercise 2.4.1jR1 := πmaker,speed(Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3R6 := πmaker(R5)makerFGhd25080160PC1.model PC2.model1004 1012makerBEmakerADEExercise 2.4.1kR1 := πmaker,model(Product PC)R2 := ρR2(maker2,model2)(R1)R3 := ρR3(maker3,model3)(R1)R4 := ρR4(maker4,model4)(R1)R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker(R7)makerABDEExercise 2.4.2aπmodelσspeed≥3.00PCExercise 2.4.2bπmakerσhd ≥ 100 ProductLaptopExercise 2.4.2cσmaker=B πmodel,priceσmaker=B πmodel,price σmaker=Bπmodel,priceProduct PC Laptop Printer ProductProductExercise 2.4.2dPrinter σcolor = true AND type = laserπmodelExercise 2.4.2e σtype=laptop σtype=PC πmakerπmaker –Product ProductExercise 2.4.2fρPC1ρPC2 (PC1.hd = PC2.hd AND PC1.model <> PC2.model)πhdPC PCExercise 2.4.2gρPC1ρPC2PC PC(PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model)πPC1.model,PC2.modelExercise 2.4.2hPC Laptop σspeed ≥ 2.80σspeed ≥ 2.80πmodelπmodel πmaker,modelρR3(maker2,model2)(maker = maker2 AND model <> model2)makerExercise 2.4.2iPCLaptopProductπmodel,speed πmodel,speed ρR4(model2,speed2)πmodel,speed(speed < speed2 )–makerExercise 2.4.2jProduct PC πmaker,speed ρR3(maker3,speed3)ρR2(maker2,speed2)(maker = maker2 AND speed <> speed2)(maker3 = maker AND speed3 <> speed2 AND speed3 <> speed)πmakerExercise 2.4.2kπmaker(maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) (maker3 = maker AND model3 <> model2 AND model3 <> model)(maker = maker2 AND model <> model2)ρR2(maker2,model2)ρR3(maker3,model3)ρR4(maker4,model4)πmaker,modelProduct PCExercise 2.4.3aR1 := σbore ≥ 16 (Classes)R2 := πclass,country (R1)Exercise 2.4.3bR1 := σlaunched < 1921 (Ships)R2 := πname (R1)KirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeExercise 2.4.3cR1 := σbattle=Denmark Strait AND result=sunk(Outcomes)R2 := πship (R1)shipBismarckHoodExercise 2.4.3dR1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)R3 := πname (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoExercise 2.4.3eR1 := σbattle=Guadalcanal(Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := πname,displacement,numGuns(R3)name displacement numGuns Kirishima 32000 8Washington 37000 9Exercise 2.4.3fR1 := πname(Ships)R2 := πship(Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNew JerseyExercise 2.4.3gFrom 2.3.2, assuming that every class has one ship named after the class.R1 := πclass (Classes) R2 := πclass (σname <> class (Ships)) R3 := R1 – R2Exercise 2.4.3hR1 := πcountry (σtype=bb (Classes)) R2 := πcountry (σtype=bc (Classes)) R3 := R1 ∩ R2Exercise 2.4.3iR1 := πship,result,date (Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship (R3)No results from sample data.Exercise 2.4.4aσbore ≥ 16πclass,countryClassesExercise 2.4.4bNorth Carolina Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign Tennessee Washington Wisconsin Yamato Arizona Bismarck Duke of York Fuso Hood King George V Prince of Wales Rodney Scharnhorst South Dakota West Virginia Yamashiro class Bismarck country Japan Gt. Britainπnameσlaunched < 1921ShipsExercise 2.4.4cπshipσbattle=Denmark Strait AND result=sunkOutcomesExercise 2.4.4dπnameσlaunched > 1921 AND displacement > 35000Classes Ships Exercise 2.4.4eσbattle=Guadalcanal Outcomes Ships(ship=name)πname,displacement,numGunsExercise 2.4.4f Ships Outcomesπnameπship ρR3(name)Exercise 2.4.4g Classes Shipsπclass σname <> class πclass–Exercise 2.4.4hClasses Classesσtype=bb σtype=bcπcountry πcountry∩Exercise 2.4.4iBattles Outcomes (battle=name)πship,result,dateρR2(ship2,result2,date2)(ship=ship2 AND result=damaged AND date < date2)πshipExercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple.Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the originalresult and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the originalresult. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the newresult. The original tuples are included in the new result because they still satisfy the select condition. Thusthe selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples ofanother relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesianproduct operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additionalsuccessful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection onsome condition. The new tuple can only create additional tuples in the result, not less. If, however, the addedtuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.Exercise 2.4.7aIf 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. Then the union has max(m , n) tuples.Exercise 2.4.7bIf all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pairsuccessfully with all the tuples in the other relation. Then the natural join has zero tuples.Exercise 2.4.7cIf the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Exercise 2.4.7dAssuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2133.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)Exercise 2.4.10πA1,A2…An(R S)Exercise 2.5.1aσspeed < 2.00 AND price > 500(PC) = øModel 1011 violates this constraint.Exercise 2.5.1bσscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = øModel 2004 violates the constraint.Exercise 2.5.1cπmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = øManufacturers A,B,E violate the constraint.Exercise 2.5.1dThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ⊆ R3Manufacturers B,C,D violate the constraint.Exercise 2.5.1eπmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = øModels 2002,2006,2008 violate the constraint.Exercise 2.5.2aπclass(σbore > 16(Classes)) = øThe Yamato class violates the constraint.Exercise 2.5.2bπclass(σnumGuns > 9 AND bore > 14(Classes)) = øNo violations to the constraint.Exercise 2.5.2cThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = øThe Kongo, Iowa and Revenge classes violate the constraint.Exercise 2.5.2dπcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = øJapan and Gt. Britain violate the constraint.Exercise 2.5.2eThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,bat tle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = øNo violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = øwhere is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2 = øAs a containment, we can simply say:E1⊆ E2 AND E2⊆ E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库系统原理第二章基本概念及课后习题有答案
数据库系统原理第二章基本概念及课后习题有答案一、数据库系统生存期1．数据库系统生存期：数据库应用系统从开始规划、设计、实现、维护到最后被新的系统取代而停止使用的整个期间。
2．数据库系统生存期分七个阶段：规划、需求分析、概念设计、逻辑设计、物理设计、实现、运行维护。
3．规划阶段三个步骤：系统调查、可行性分析、确定数据库系统总目标。
4．需求分析阶段：主要任务是系统分析员和用户双方共同收集数据库系统所需要的信息内容和用户对处理的需求，并以需求说明书的形式确定下来。
5．概念设计阶段：产生反映用户单位信息需求的概念模型。
与硬件和DBMS无关。
6．逻辑设计阶段：将概念模型转换成DBMS能处理的逻辑模型。
外模型也将在此阶段完成。
7．物理设计阶段：对于给定的基本数据模型选取一个最适合应用环境的物理结构的过程。
数据库的物理结构主要指数据库的存储记录格式、存储记录安排和存取方法。
8．数据库的实现：包括定义数据库结构、数据装载、编制与调试应用程序、数据库试运行。
二、ER模型的基本概念ER模型的基本元素是：实体、联系和属性。
2．实体：是一个数据对象，指应用中可以区别的客观存在的事物。
实体集：是指同一类实体构成的集合。
实体类型：是对实体集中实体的定义。
一般将实体、实体集、实体类型统称为实体。
3．联系：表示一个或多个实体之间的关联关系。
联系集：是指同一类联系构成的集合。
联系类型：是对联系集中联系的定义。
一般将联系、联系集、联系类型统称为联系。
4．同一个实体集内部实体之间的联系，称为一元联系；两个不同实体集实体之间的联系，称为二元联系，以此类推。
5．属性：实体的某一特性称为属性。
在一个实体中，能够惟一标识实体的属性或属性集称为实体标识符。
6． ER模型中，方框表示实体、菱形框表示联系、椭圆形框表示属性、实体与联系、实体与其属性、联系与其属性之间用直线连接。
实体标识符下画横线。
联系的类型要在直线上标注。
注意：联系也有可能存在属性，但联系本身没有标识符。
数据库系统教程习题答案(施伯乐)(第2版)_数据库原理和应用
第2部分各章习题解答及自测题第1章数据库概论1.1 基本内容分析1.1.1 本章的重要概念（1）DB、DBMS和DBS的定义（2）数据管理技术的发展阶段人工管理阶段、文件系统阶段、数据库系统阶段和高级数据库技术阶段等各阶段的特点。
（3）数据描述概念设计、逻辑设计和物理设计等各阶段中数据描述的术语，概念设计中实体间二元联系的描述（1:1，1:N，M:N）。
（4）数据模型数据模型的定义，两类数据模型，逻辑模型的形式定义，ER模型，层次模型、网状模型、关系模型和面向对象模型的数据结构以及联系的实现方式。
（5）DB的体系结构三级结构，两级映像，两级数据独立性，体系结构各个层次中记录的联系。
（6）DBMSDBMS的工作模式、主要功能和模块组成。
（7）DBSDBS的组成，DBA，DBS的全局结构，DBS结构的分类。
（1）教材P23的图1.24（四种逻辑数据模型的比较）。
（2）教材P25的图1.27（DB的体系结构）。
（3）教材P28的图1.29（DBMS的工作模式）。
（4）教材P33的图1.31（DBS的全局结构）。
1.2 教材中习题1的解答1.1 名词解释·逻辑数据：指程序员或用户用以操作的数据形式。
·物理数据：指存储设备上存储的数据。
·联系的元数：与一个联系有关的实体集个数，称为联系的元数。
·1:1联系：如果实体集E1中每个实体至多和实体集E2中的一个实体有联系，反之亦然，那么E1和E2的联系称为“1:1联系”。
·1:N联系：如果实体集E1中每个实体可以与实体集E2中任意个（零个或多个）实体有联系，而E2中每个实体至多和E1中一个实体有联系，那么E1和E2的联系是“1:N联系”。
·M:N联系：如果实体集E1中每个实体可以与实体集E2中任意个（零个或多个）实体有联系，反之亦然，那么E1和E2的联系称为“M:N联系”。
·数据模型：能表示实体类型及实体间联系的模型称为“数据模型”。
数据库系统教程课后答案(施伯乐)(第二版)
目录第1部分课程的教与学第2部分各章习题解答及自测题第1章数据库概论1.1 基本内容分析1.2 教材中习题1的解答1.3 自测题1.4 自测题答案第2章关系模型和关系运算理论2.1基本内容分析2.2 教材中习题2的解答2.3 自测题2.4 自测题答案第3章关系数据库语言SQL3.1基本内容分析3.2 教材中习题3的解答3.3 自测题3.4 自测题答案第4章关系数据库的规范化设计4.1基本内容分析4.2 教材中习题4的解答4.3 自测题4.4 自测题答案第5章数据库设计与ER模型5.1基本内容分析5.2 教材中习题5的解答5.3 自测题5.4 自测题答案第6章数据库的存储结构6.1基本内容分析6.2 教材中习题6的解答第7章系统实现技术7.1基本内容分析7.2 教材中习题7的解答7.3 自测题7.4 自测题答案第8章对象数据库系统8.1基本内容分析8.2 教材中习题8的解答8.3 自测题8.4 自测题答案第9章分布式数据库系统9.1基本内容分析9.2 教材中习题9的解答9.3 自测题9.4 自测题答案第10章中间件技术10.1基本内容分析10.2 教材中习题10的解答10.3 自测题及答案第11章数据库与WWW11.1基本内容分析11.2 教材中习题11的解答第12章 XML技术12.1基本内容分析12.2 教材中习题12的解答学习推荐书目1．国内出版的数据库教材（1）施伯乐,丁宝康,汪卫. 数据库系统教程(第2版). 北京:高等教育出版社,2003（2）丁宝康,董健全. 数据库实用教程(第2版). 北京:清华大学出版社,2003（3）施伯乐,丁宝康. 数据库技术. 北京:科学出版社,2002（4）王能斌. 数据库系统教程(上、下册). 北京:电子工业出版社,2002（5）闪四清. 数据库系统原理与应用教程. 北京:清华大学出版社,2001（6）萨师煊,王珊. 数据库系统概论(第3版). 北京:高等教育出版社,2000（7）庄成三,洪玫,杨秋辉. 数据库系统原理及其应用. 北京:电子工业出版社,20002．出版的国外数据库教材（中文版或影印版）（1）Silberschatz A，Korth H F，Sudarshan S. 数据库系统概念(第4版). 杨冬青,唐世渭等译. 北京:机械工业出版社,2003（2）Elmasri R A，Navathe S B. 数据库系统基础(第3版). 邵佩英,张坤龙等译. 北京:人民邮电出版社,2002（3）Lewis P M,Bernstein A,Kifer M. Databases and Transaction Processing:An Application-Oriented Approach, Addison-Wesley, 2002（影印版, 北京:高等教育出版社；中文版，施伯乐等译，即将由电子工业出版社出版）（4）Hoffer J A,Prescott M B,McFadden F R. Modern Database Management. 6th ed. Prentice Hall, 2002（中文版，施伯乐等译，即将由电子工业出版社出版）3．上机实习教材（1）廖疆星,张艳钗,肖金星. PowerBuilder 8.0 & SQL Server 2000数据库管理系统管理与实现. 北京:冶金工业出版社,2002（2）伍俊良. PowerBuilder课程设计与系统开发案例. 北京:清华大学出版社,20034．学习指导书（1）丁宝康,董健全,汪卫,曾宇昆. 数据库系统教程习题解答及上机指导. 北京:高等教育出版社,2003（2）丁宝康,张守志,严勇. 数据库技术学习指导书. 北京:科学出版社,2003（3）丁宝康,董健全,曾宇昆. 数据库实用教程习题解答. 北京:清华大学出版社,2003 （4）丁宝康. 数据库原理题典. 长春:吉林大学出版社,2002（5）丁宝康,陈坚,许建军,楼晓鸿. 数据库原理辅导与练习. 北京:经济科学出版社,2001第1部分课程的教与学1．课程性质与设置目的现在，数据库已是信息化社会中信息资源与开发利用的基础，因而数据库是计算机教育的一门重要课程，是高等院校计算机和信息类专业的一门专业基础课。
数据库系统基础教程第二章答案
数据库系统基础教程第二章答案集团标准化工作小组 #Q8QGGQT-GX8G08Q8-GNQGJ8-MHHGN#For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 acctNosavings type12000 balanceFor relation Customers and the first tuple, the components are:Robbie firstNameBanks lastName901-222 idNo12345 accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo Integertype Stringbalance IntegerfirstName StringlastName StringidNo String (because there is a hyphen we cannot use Integer)account IntegerExerciseUniversal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m! CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color; ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles ( name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ (PC)R2 := πmodel (R1)R2 := Product (R1) R3 := πmaker (R2) R1 := σmaker=B (Product PC) R2 := σmaker=B (Product Laptop) R3 := σmaker=B (Product Printer)R4 := πmodel,price (R1)R5 := πmodel,price (R2) R6: = πmodel,price (R3) R7 := R4 R5 R6model price1004 6491005 6301006 10492007 1429color = true AND type = laser R2 := πmodel (R1) R1 := σtype=laptop (Product) R2 := σtype=PC (Product) R3 := πmaker (R1)maker R5 := R3 – R4R2 := ρPC2(PC) R3 := R1 = AND <> R2 R4 := πhd (R3) R1 := ρPC1(PC) R2 := ρPC2(PC) R3 := R1 = AND = AND < R2 R4 := π,(R3) R1 := πmodel (σspeed ≥ (PC)) πmodel (σspeed ≥ (Laptop)) R2 := πmaker,model (R1 Product)R3(maker2,model2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker (R4)R1 := πmodel,speed (PC) R2 := πmodel,speed (Laptop) R3 := R1 R2 R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5model 1005 1006 1013 maker E A BFG model 3003 3007 maker F G hd 250 80 1601004 1012 maker B ER7 := πmaker (R6 Product)makerBR1 := πmaker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1) R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3R6 := πmaker (R5)R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2(maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7)R1 := σbore ≥ 16 (Classes) R2 := πclass,country (R1)class countryIowa USANorth Carolina USAYamato JapanR1 := σlaunched < 1921 (Ships)R2 := πname (R1)nameHarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeR1 := σbattle=Denmark Strait AND result=sunk (Outcomes)R2 := πship (R1)shipBismarckHoodR1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)R3 := πname (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoR1 := σbattle=Guadalcanal (Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := πname,displacement,numGuns (R3)name displacement numGunsKirishima 32000 8Washington 37000 9name R2 := πship (Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3maker A D E maker ABDEnameCaliforniaHarunaHieiIowaKirishimaKongoFrom assuming that every class has one ship named after the class. R1 := πclass (Classes) R2 := πclass (σname <> class (Ships)) R3 := R1 – R2 R1 := πcountry (σtype=bb (Classes)) R2 := πcountry (σtype=bc (Classes)) R3 := R1 ∩ R2 R1 := πship,result,date (Battles (battle=name) Outcomes) R2 := ρR2(ship2,result2,date2)(R1) R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2 R4 := πship (R3) No results from sample data. Exercise result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical. Exercise we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone. Intersection If we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone. Difference If we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t . However, if we add tuple t to S, then the new result will not have tuple t . Thus the difference operator is not monotone. ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of theoriginal result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect therows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. Ifthe added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new resultbecause they still satisfy the select condition. Thus the selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of theexisting tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples.The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create additionaltuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to theresult. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. Therenaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as manytuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n ) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible. The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Assuming that the list of attributes L makes the resulting relation πL (R) and relation S schema compatible, then the maximum possible tuples is n . This happens when all of the tup les of πL (R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL (R) appear in S. Then the difference has max(n – m , 0) tuples.Exercise r as the schema of R and s as the schema of S:1. πr (R S)2.3. R δ(πr∩s (S)) where δ is the duplicate -elimination operator in Section pg. 2134.5. R – (R – πr (R S))Exercise r as the schema of R1. R - πr (R S)πA1,A2…An (R S)σspeed < AND price > 500(PC) = MissouriMusashiNew JerseyNorth CarolinaRamillies Renown RepulseResolution Revenge Royal Oak Royal SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of WalesRodneyScharnhorstSouth DakotaWest VirginiaYamashiro classBismarckcountry Japan Gt. BritainModel 1011 violates this constraint.σscreen < AND hd < 100 AND price ≥ 1000(Laptop) =Model 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) =Manufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 = AND ≤ R2)R4(model) := πmodel(PC)The constraint is R4 R3Manufacturers B,C,D violate the constraint.πmodel(σ> AND ≤ (PC × Laptop)) =Models 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) =The Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) =No violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 =The Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) =Japan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 =No violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = where is the antisemijoinExercise form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2 =As a containment, we can simply say:E1 E2 AND E2 E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库系统基础教程第二章答案
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 → acctNosavings → type12000 → balanceFor relation Customers and the first tuple, the components are:Robbie → firstNameBanks → lastName901-222 → idNo12345 → accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo → Integertype → Stringbalance → IntegerfirstName → StringlastName → StringidNo → String (because there is a hyphen we cannot use Integer)account → IntegerExercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od C HAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ 3.00 (PC)R2 := πmodel (R1)R2 := Product (R1) R3 := πmaker (R2) R1 := σmaker=B (Product PC) R2 := σmaker=B (Product Laptop) R3 := σmaker=B (Product Printer) R4 := πmodel,price (R1) R5 := πmodel,price (R2) R6: = πmodel,price (R3) R7 := R4 R5 R6model price1004 6491005 6301006 10492007 1429color = true AND type = laser R2 := πmodel (R1)R1 := σtype=laptop (Product) R2 := σtype=PC (Product)R3 := πmaker (R1)maker R5 := R3 – R4R2 := ρPC2(PC) R3 := R1 R2 R4 := πhd (R3) R1 := ρPC1(PC) R2 := ρPC2(PC) R3 := R1 R2 R4 := πPC1.model,PC2.model (R3) R1 := πmodel (σspeed ≥ 2.80(PC)) πmodel (σspeed ≥ 2.80(Laptop))R2 := πmaker,model (R1 Product)R3 := ρR3(maker2,model2)(R2)model10051006 1013 maker E ABFG model 3003 3007 makerFGhd 250 80 160PC1.model PC2.model 1004 1012R4 := R2 (maker = maker2 AND model <> model2) R3 R5 := πmaker (R4) R1 := πmodel,speed (PC) R2 := πmodel,speed (Laptop) R3 := R1 R2 R4 := ρR4(model2,speed2)(R3) R5 := πmodel,speed (R3 (speed < speed2 ) R4) R6 := R3 – R5 R7 := πmaker (R6 Product) maker BR1 := πmaker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5) R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7) R1 := σbore ≥ 16 (Classes) R2 := πclass,country (R1) class country Iowa USA North Carolina USA Yamato JapanR1 := σlaunched < 1921 (Ships) R2 := πname (R1) name Haruna Hiei Kirishima Kongo Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign TennesseeR1 := σbattle=Denmark Strait AND result=sunk (Outcomes) R2 := πship (R1) ship Bismarck HoodR1 := Classes Ships R2 := σlaunched > 1921 AND displacement > 35000 (R1) name Iowa Missouri Musashi New Jersey North Carolina Washington Wisconsin Yamatobattle=GuadalcanalR2 := Ships (ship=name) R1R3 := Classes R2 name displacement numGuns Kirishima 32000 8 Washington 37000 9 name maker B E maker A D E maker A B D ER2 := πship (Outcomes)R3 := ρR3(name)(R2) R4 := R1 R3 From 2.3.2, assuming that every class has one ship named after the class.R1 := πclass (Classes)R2 := πclass (σname <> class (Ships))R3 := R1 – R2R1 := πcountry (σtype=bb (Classes))R2 := πcountry (σtype=bc (Classes))R3 := R1 ∩ R2R1 := πship,result,date (Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship (R3)No results from sample data.Exercise 2.4.5 The result of the natural join has only one attribute from each pair of equated attributes. On theother hand, the result of the theta-join has both columns of the attributes and their values are identical. Exercise 2.4.6 Union If we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone. IntersectionIfwe add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone. Difference If we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R – Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t . However, if we add tuple t to S, then the new result will not have tuple t . Thus the difference operator is not monotone. Projection If we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that areselected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybethe added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The originaltuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone. Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation.Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result andpossibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuplecannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The newtuple can only create additional tuples in the result, not less. If, however, the added tuple does not satisfy the selectcondition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the addedtuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operatorwill always return as many tuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n ) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.name California Haruna Hiei Iowa Kirishima Kongo Missouri Musashi New Jersey North Carolina Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign Tennessee Washington Wisconsin YamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of Wales Rodney Scharnhorst South DakotaWest VirginiaYamashiro class Bismarck country Japan Gt. BritainAssuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2134.5.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)πA1,A2…An(R S)σspeed < 2.00 AND price > 500(PC) = øModel 1011 violates this constraint.σscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = øModel 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = øManufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(mod el) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ⊆ R3Manufacturers B,C,D violate the constraint.πmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = øModels 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) = øThe Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) = øNo violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = øThe Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = øJapan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = øNo violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = øw here is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2= øAs a containment, we can simply say:E1⊆ E2 AND E2⊆ E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
大学数据库-第2章习题解答
第2章习题解答〖2.1〗定义并解释术语：实体实体型实体集属性码实体联系图（E-R图）数据模型答：①实体：现实世界中存在的可以相互区分的事物或概念称为实体。
②实体型：现实世界中，对具有相同性质、服从相同规则的一类事物（或概念，即实体）的抽象称为实体型。
③实体集：具有相同特征或能用同样特征描述的实体的集合称为实体集。
④属性：属性为实体的某一方面特征的抽象表示。
⑤码：也称为关键字，能够唯一标识一个实体。
⑥实体联系图（E-R图）：实体联系方法（E-R图法）是用来描述现实世界中概念模型的一种著名方法，提供了表示实体集、属性和联系的方法。
⑦数据模型：一组严格定义的概念集合。
这些概念精确地描述了系统的数据结构、数据操作和数据完整性约束条件。
〖2.2〗试述数据模型的概念、数据模型的作用和数据模型的三个要素。
答：①数据模型是一组严格定义的概念集合，这些概念精确地描述了系统的数据结构、数据操作和数据完整性约束条件。
数据模型是通过概念模型数据化处理得到的。
②数据库是根据数据模型建立的，因而数据模型是数据库系统的基础。
③数据模型的三要素是数据结构、数据操作和完整性约束条件。
数据结构是所研究的对象类型的集合；数据操作是指对数据库中各种数据对象允许执行的操作集合；数据约束条件是一组数据完整性规则的集合。
〖2.3〗试述信息模型的作用。
答：信息模型是对信息世界的管理对象、属性及联系等信息的描述形式。
信息模型不依赖于计算机及DBMS，它是现实世界的真实而全面的反映。
信息模型数据化处理后可得到数据模型。
〖2.4〗试给出三个实际部门的E-R图，要求实体型之间具有一对一、一对多、多对多各种不同的联系。
答：见图。
题2.4 E-R图图中：部门和负责人间的联系是一对一的联系；一个学生可以借阅多本书，一本书只能一个人借，学生和借阅间的联系为一对多的联系；一个学生可以参加多个社会团体，一个社会团体有多个学生参加，学生和社会团体间的联系为多对多的联系。
数据库系统基础教程第二章答案
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 ✍ acctNosavings ✍ type12000 ✍ balanceFor relation Customers and the first tuple, the components are:Robbie ✍ firstNameBanks ✍ lastName901-222 ✍ idNo12345 ✍ accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo ✍ Integertype ✍ Stringbalance ✍ IntegerfirstName ✍ StringlastName ✍ StringidNo ✍ String (because there is a hyphen we cannot use Integer)account ✍ IntegerExercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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. We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ 3.00 (PC)R2 := πmodel(R1)R2 := Product (R1)maker=B(Product PC)maker=B(Product Laptop)maker=B(Product Printer)model,price(R1)model,price(R2)model,price(R3)R5 R6color = true AND type = laserR2 := πmodel (R1)R1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker(R1)makerR5 := R3 – R4PC2(PC)R3 := R1R2PC2(PC)R3 := R1 R2(R3)R1 := πmodel(σspeed ≥ 2.80(PC)) πmodel(σspeed ≥ 2.80(Laptop))R2 := πmaker,model(R1 Product) R3(maker2,model2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker(R4)R1 := πmodel,speed(PC)R2 := πmodel,speed(Laptop)R3 := R1 R2R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5(R6 Product)maker,speed PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1) R4 := R1 (maker = maker2 AND speed <> speed2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5) R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7)launched < 1921battle=Denmark Strait AND result=sunk (Outcomes)R1 := Classes Ships R2 := σlaunched > 1921 AND displacement > 35000 (R1)battle=GuadalcanalR2 := Ships (ship=name) R1R3 := Classes R2nameR2 := πship (Outcomes)R3 := ρR3(name)(R2) R4 := R1 R3R1 := πclass(Classes)R2 := πclass(σname <> class(Ships))R3 := R1 – R2R1 := πcountry(σtype=bb(Classes))R2 := πcountry(σtype=bc(Classes))R3 := R1 ∩ R2R1 := πship,result,date(Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship(R3)No results from sample data.Exercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the originalresult and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior willeliminate that tuple. Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of theoriginal result and maybe the added tuple. If the added tuple does not exist in the relation that it isadded but does exist in the other relation, then the result set will include the added tuple. Thus theintersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create additional tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Assuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples. Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2134.5.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)πA1,A2…An(R S)σspeed < 2.00 AND price > 500(PC) = ?Model 1011 violates this constraint.σscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = ?Model 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = ?Manufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(make r, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ? R3Manufacturers B,C,D violate the constraint.πmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = ?Models 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) = ?The Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) = ?No violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = ?The Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = ?Japan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = ?No violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = ? where is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” metho d, we can simply say:E1– E2 = ?As a containment, we can simply say:E1 ? E2 AND E2 ? E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库系统基础教程第二章答案
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 ✍ acctNosavings ✍ type12000 ✍ balanceFor relation Customers and the first tuple, the components are:Robbie ✍ firstNameBanks ✍ lastName901-222 ✍ idNo12345 ✍ accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo ✍ Integertype ✍ Stringbalance ✍ IntegerfirstName ✍ StringlastName ✍ StringidNo ✍ String (because there is a hyphen we cannot use Integer)account ✍ IntegerExamples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ 3.00 (PC)R2 := πmodel (R1)(R1) (R2) σmaker=B (Product PC) σmaker=B (Product Laptop)σmaker=B (Product Printer)πmodel,price (R1) πmodel,price(R2) πmodel,price (R3) R5 R6color = true AND type = laser R2 := πmodel (R1) R1 := σtype=laptop (Product)R2 := σtype=PC (Product)R3 := πmaker (R1)maker R5 := R3 – R4R1 := ρPC1(PC) R2 := ρPC2(PC) R3 := R1 (PC1.hd = PC2.hd AND PC1.model <> PC2.model) R2 R4 := πhd (R3)R2 (R3) R1 := πmodel(σspeed ≥ 2.80(PC)) πmodel (σspeed ≥ 2.80(Laptop)) R2 := πmaker,model (R1 Product) R3 := ρR3(maker2,model2)(R2) R4 := R2 (maker = maker2 AND model <> model2) R3 R5 := πmaker (R4) R1 := πmodel,speed (PC) R2 := πmodel,speed (Laptop) R3 := R1 R2 R4 := ρR4(model2,speed2)(R3) R5 := πmodel,speed (R3 (speed < speed2 ) R4) R6 := R3 – R5 (R6 Product)maker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5) R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2(maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7)launched < 1921battle=Denmark Strait AND result=sunk (Outcomes) R1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)battle=GuadalcanalR2 := Ships (ship=name) R1R3 := Classes R2nameR2 := πship(Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3From 2.3.2, assuming that every class has one ship named after the class. Array R1 := πclass(Classes)R2 := πclass(σname <> class(Ships))R3 := R1 – R2R1 := πcountry(σtype=bb(Classes))R2 := πcountry(σtype=bc(Classes))R3 := R1 ∩ R2R1 := πship,result,date(Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship(R3)No results from sample data.Exercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes.On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of thetuples of the original result and maybe the added tuple. If the added tuple is aduplicate tuple, then the set behavior will eliminate that tuple. Thus the unionoperator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get allof the tuples of the original result and maybe the added tuple. If the added tupledoes not exist in the relation that it is added but does exist in the otherrelation, then the result set will include the added tuple. Thus the intersectionoperator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get allof the tuples of the original result. Suppose we have relations R and S and we arecomputing R – S. Suppose also that tuple t is in R but not in S. The result of R– S would include tuple t. However, if we add tuple t to S, then the new resultwill not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all ofthe tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus theprojection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original resultand maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result.The original tuples are included in the new result because they still satisfy the select condition. Thus theselection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the originalresult and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples ofanother relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original resultand possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successfuljoins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on somecondition. The new tuple can only create additional tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Assuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2134.5.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)πA1,A2…An(R S)σspeed < 2.00 AND price > 500(PC) = ?Model 1011 violates this constraint.σscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = ?Model 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = ?Manufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(make r, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ? R3Manufacturers B,C,D violate the constraint.πmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = ?Models 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) = ?The Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) = ?No violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = ?The Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = ?Japan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = ?No violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = ? w here is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” metho d, we can simply say:E1– E2 = ?As a containment, we can simply say:E1 ? E2 AND E2 ? E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库系统基础教程第二章答案
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 ✍ acctNosavings ✍ type12000 ✍ balanceFor relation Customers and the first tuple, the components are:Robbie ✍ firstNameBanks ✍ lastName901-222 ✍ idNo12345 ✍ accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo ✍ Integertype ✍ Stringbalance ✍ IntegerfirstName ✍ StringlastName ✍ StringidNo ✍ String (because there is a hyphen we cannot use Integer)account ✍ IntegerExercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ 3.00 (PC)R2 := πmodel(R1)R2 := Product (R1)(R2)maker=B(Product PC)maker=B(Product Laptop)maker=B(Product Printer)model,price(R1)model,price(R2)model,price(R3)R5 R6color = true AND type = laserR2 := πmodel (R1)R1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker(R1)makerR5 := R3 – R4PC2R3 := R1 R2PC2(PC)R3 := R1 R2(R3)R1 := πmodel(σspeed ≥ 2.80(PC)) πmodel(σspeed ≥ 2.80(Laptop))R2 := πmaker,model(R1 Product) R3(maker2,model2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker(R4)R1 := πmodel,speed(PC)R2 := πmodel,speed(Laptop)R3 := R1 R2R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5Product)maker,speedPC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1) R4 := R1 (maker = maker2 AND speed <> speed2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5) R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7)launched < 1921battle=Denmark Strait AND result=sunk (Outcomes)R1 := Classes Ships R2 := σlaunched > 1921 AND displacement > 35000 (R1)battle=GuadalcanalR2 := Ships (ship=name) R1R3 := Classes R2nameR2 := πship (Outcomes)R3 := ρR3(name)(R2) R4 := R1 R3R1 := πclass(Classes)R2 := πclass(σname <> class(Ships))R3 := R1 – R2R1 := πcountry(σtype=bb(Classes))R2 := πcountry(σtype=bc(Classes))R3 := R1 ∩ R2R1 := πship,result,date(Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship(R3)No results from sample data.Exercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the original resultand maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate thattuple. Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple.The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone. SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples.The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create additional tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples. Assuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples. Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2134.5.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)πA1,A2…An(R S)σspeed < 2.00 AND price > 500(PC) = ?Model 1011 violates this constraint.σscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = ?Model 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = ?Manufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(make r, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ? R3Manufacturers B,C,D violate the constraint.πmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = ?Models 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) = ?The Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) = ?No violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = ?The Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = ?Japan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = ?No violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = ? where is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” metho d, we can simply say:E1– E2 = ?As a containment, we can simply say:E1 ? E2 AND E2 ? E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
数据库系统基础教程第二章答案
For relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)For relation Accounts and the first tuple, the components are:123456 → acctNosavings → type12000 → balanceFor relation Customers and the first tuple, the components are:Robbie → firstNameBanks → lastName901-222 → idNo12345 → accountFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)An example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)A suitable domain for each attribute:acctNo → Integertype → Stringbalance → IntegerfirstName → StringlastName → StringidNo → String (because there is a hyphen we cannot use Integer)account → IntegerExercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.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.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));CREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od C HAR (10) DEFAULT ‘none’;CREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));ALTER TABLE Classes DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := σspeed ≥ 3.00 (PC)R2 := πmodel (R1)R2 := Product (R1) R3 := πmaker (R2) R1 := σmaker=B (Product PC) R2 := σmaker=B (Product Laptop) R3 := σmaker=B (Product Printer) R4 := πmodel,price (R1) R5 := πmodel,price (R2) R6: = πmodel,price (R3) R7 := R4 R5 R6model price1004 6491005 6301006 10492007 1429color = true AND type = laser R2 := πmodel (R1)R1 := σtype=laptop (Product) R2 := σtype=PC (Product)R3 := πmaker (R1)maker R5 := R3 – R4R2 := ρPC2(PC) R3 := R1 R2 R4 := πhd (R3) R1 := ρPC1(PC) R2 := ρPC2(PC) R3 := R1 R2 R4 := πPC1.model,PC2.model (R3) R1 := πmodel (σspeed ≥ 2.80(PC)) πmodel (σspeed ≥ 2.80(Laptop))R2 := πmaker,model (R1 Product)R3 := ρR3(maker2,model2)(R2)model10051006 1013 maker E ABFG model 3003 3007 makerFGhd 250 80 160PC1.model PC2.model 1004 1012R4 := R2 (maker = maker2 AND model <> model2) R3 R5 := πmaker (R4) R1 := πmodel,speed (PC) R2 := πmodel,speed (Laptop) R3 := R1 R2 R4 := ρR4(model2,speed2)(R3) R5 := πmodel,speed (R3 (speed < speed2 ) R4) R6 := R3 – R5 R7 := πmaker (R6 Product) maker BR1 := πmaker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2 R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3 R6 := πmaker (R5) R1 := πmaker,model (Product PC) R2 := ρR2(maker2,model2)(R1) R3 := ρR3(maker3,model3)(R1) R4 := ρR4(maker4,model4)(R1) R5 := R1 (maker = maker2 AND model <> model2) R2 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6R8 := πmaker (R7) R1 := σbore ≥ 16 (Classes) R2 := πclass,country (R1) class country Iowa USA North Carolina USA Yamato JapanR1 := σlaunched < 1921 (Ships) R2 := πname (R1) name Haruna Hiei Kirishima Kongo Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign TennesseeR1 := σbattle=Denmark Strait AND result=sunk (Outcomes) R2 := πship (R1) ship Bismarck HoodR1 := Classes Ships R2 := σlaunched > 1921 AND displacement > 35000 (R1) name Iowa Missouri Musashi New Jersey North Carolina Washington Wisconsin Yamatobattle=GuadalcanalR2 := Ships (ship=name) R1R3 := Classes R2 name displacement numGuns Kirishima 32000 8 Washington 37000 9 name maker B E maker A D E maker A B D ER2 := πship (Outcomes)R3 := ρR3(name)(R2) R4 := R1 R3 From 2.3.2, assuming that every class has one ship named after the class.R1 := πclass (Classes)R2 := πclass (σname <> class (Ships))R3 := R1 – R2R1 := πcountry (σtype=bb (Classes))R2 := πcountry (σtype=bc (Classes))R3 := R1 ∩ R2R1 := πship,result,date (Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship (R3)No results from sample data.Exercise 2.4.5 The result of the natural join has only one attribute from each pair of equated attributes. On theother hand, the result of the theta-join has both columns of the attributes and their values are identical. Exercise 2.4.6 Union If we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone. IntersectionIfwe add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone. Difference If we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R – Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t . However, if we add tuple t to S, then the new result will not have tuple t . Thus the difference operator is not monotone. Projection If we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that areselected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybethe added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The originaltuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone. Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation.Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result andpossibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuplecannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The newtuple can only create additional tuples in the result, not less. If, however, the added tuple does not satisfy the selectcondition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the addedtuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operatorwill always return as many tuples as its argument. Thus the renaming operator is monotone.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. Then the union has max(m , n ) tuples.If all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.name California Haruna Hiei Iowa Kirishima Kongo Missouri Musashi New Jersey North Carolina Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign Tennessee Washington Wisconsin YamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of Wales Rodney Scharnhorst South DakotaWest VirginiaYamashiro class Bismarck country Japan Gt. BritainAssuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.3.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2134.5.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)πA1,A2…An(R S)σspeed < 2.00 AND price > 500(PC) = øModel 1011 violates this constraint.σscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = øModel 2004 violates the constraint.πmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = øManufacturers A,B,E violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(mod el) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ⊆ R3Manufacturers B,C,D violate the constraint.πmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = øModels 2002,2006,2008 violate the constraint.πclass(σbore > 16(Classes)) = øThe Yamato class violates the constraint.πclass(σnumGuns > 9 AND bore > 14(Classes)) = øNo violations to the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = øThe Kongo, Iowa and Revenge classes violate the constraint.πcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = øJapan and Gt. Britain violate the constraint.This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = øNo violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = øw here is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2= øAs a containment, we can simply say:E1⊆ E2 AND E2⊆ E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。
(完整版)数据库系统基础教程第二章答案
(Lena, Hand,805-333, 12345),
(Lena, Hand,805-333, 23456)
Exercise 2.2.1c
For relation Accounts and the first tuple, the components are:
idNo
firstName
lastName
account
805-333
Lena
Hand
23456
805-333
Lena
Hand
12345
901-222
Robbie
Banks
12345
Exercise 2.2.2
Examples of attributes that are created for primarily serving as keys in a relation:
Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.
Exercise 2.2.3a
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.
CREATE TABLE Outcomes (
ship CHAR(30), battle CHAR(30), result CHAR(10)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
RABC 367 257 723 443
S ABC 345 723
计算 R∪S，R-S，R∩S，R×S，π 3,2（S）,σ B<’5’(R),R 2<2 S, R S。
解： R∪S A B C 367 257 723 443 345
R×S R.A R.B R.C 3 67 3 67 2 57 2 57 7 23 7 23 4 43 4 43
⑵ π S#，SNAME（σ AGE＞'23' ∧ SEX='M'（SC）） ⑶ π CNAME，TNAME（σ S#='S3'（SC⋈C））
⑷ π SNAME（σ SEX='F' ∧ TNAME='LIU'（S⋈SC⋈C））
⑸ π C#（C）－π C#（σ SNAME='WANG'（S⋈SC）） ⑹ π 1（σ 1=4 ∧ 2≠5（SC×SC）） ⑺ π C#，CNAME（C⋈（π S#，C#（SC）÷π S#（S））） ⑻ π S#，C#（SC）÷π C#（σ TNAME='LIU'（C））
R－S
ABC 367 257 443
S.A S.B S.C 34 5 72 3 34 5 72 3 34 5 72 3 34 5 72 3
R∩S A B C 723
π 3，2（S） C B 54 32
σ B<’5’（R） A B C
S.B S.C
723 443
R⋈S R.A R.B R.C S.A
2<2
7 23 34 5
R⋈S A B C
723
2.7 设有关系 R 和 S： RAB ab cb de
S BC bc ea bd
Байду номын сангаас计算
R
⋈
S，R
⋈ S，σ B<C
A=C(R×S)，S
⋉
R
R⋈S A B C
a bc a bd c bc c bd
R ⋈ S A R.B S.B C
B<C
a bbc a bbd c bbc c bbd
σ A=C(R×S) A R.B S.B C
abea cbbc de bd
S⋉R B C
bc bd
2.17 设有三个关系： S（S#，SNAME，AGE，SEX) SC（S#，C#，CNAME） C（C#，CNAME，TEACHER）
试用关系代数表达式表示下列查询语句： ① 检索 LIU 老师所授课程的课程号和课程名。 ② 检索年龄大于 23 岁的男学生的学号和姓名。 ③ 检索学号为 S3 学生所学课程的课程名与任课教师名。 ④ 检索至少选修 LIU 老师所授课程中一门课的女学生姓名。 ⑤ 检索 WANG 同学不学的课程的课程号。 ⑥ 检索至少选修两门课的学生学号。 ⑦ 检索全部学生都选修的课程的课程号与课程名。 ⑧ 检索选修课程包含 LIU 老师所授全部课程的学生学号。 解：⑴ π C#，CNAME（σ TNAME='LIU'（C））