数据库习题及答案

 

1 What is a business intelligence (BI) system?

ANSWER: Essentially, Business Intelligence systems are data-driven Decision Support Systems (DSS). They provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data ,to aid in decision-making.

2 What is an ad-hoc query（即席查询）?

ANSWER:在每一个查询操作被执行之前，查询的目标对象是不明确的。
3 Explain, in general terms, the relationships among the RETAIL_ORDER, ORDER_
ITEM, and SKU_DATA tables.

ANSWER:

4 Summarize the background of SQL.

ANSWER: SQL was developed by the IBM Corporation in the late 1970s. It was endorsed as a national standard by the American National Standards Institute (ANSI) in 1986 and by the International Organization for Standardization (ISO).
5 Why is SQL described as a data sublanguage?

ANSWER: Because it has only those statements needed for creating and  processing database data and metadata. You can use SQL statements in many different ways. You can submit them directly to the DBMS for processing. You can embed SQL statements into client/server application programs. You can embed them into Web pages, and you can use them in reporting and data extraction programs. You also can  execute SQL statements directly from Visual Studio.NET and other development tools.
6 Explain how enterprise-class DBMS products use SQL.

ANSWER: Enterprise-class DBMSs such as Microsoft SQL Server 2008 R2, Oracle Database 11g, Oracle MySQL 5.5, and IBM DB2 require that you know SQL. With these products, all data manipulation is expressed using SQL.

The Cape Codd Outdoor Sports sale extraction database has been modified to include two additional tables, the Inventory table and the Warehouse table. The table schemas for these tables, together with the Retail _Order, Order_ Item, and SKU_ Data tables, are as follows:
RETAIL_ORDER (OrderNumber, StoreNumber, StoreZip, OrderMonth, OrderYear, OrderTotal)
ORDER_ITEM (OrderNumber, SKU, Quantity, Price, ExtendedPrice)
SKU_DATA (SKU, SKU_Description, Department, Buyer)
WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet)
INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder)
The five tables in the revised Cape Codd database schema are shown in before. The column characteristics for the Warehouse table are shown in Figure 2-25, and the column characteristics for the Inventory table are shown in Figure 2-26. The data for the Warehouse table are shown in Figure 2-27, and the data for the Inventory table are shown in Figure 2-28.

1 There is an intentional（故意的） flaw（缺陷） in the design of the INVENTORY table used in these
exercises. This flaw was purposely included in the INVENTORY tables so you can
answer some of the following questions using only that table. Compare the SKU and
INVENTORY tables, and determine what design flaw is included in INVENTORY.
Specifically, why did we include it?

ANSWER:

SELECT 查找列（去除重复用DISTINCT）

FROM 表名

WHERE 从行的角度选择满足条件的行

GROUP BY 分组

HAVING 从分组的角度选择满足条件的分组

ORDER BY 根据指定的列对结果集进行排序，默认升序，降序需要使用DESC关键字
Use only the Inventory table to answer Review Questions:
2 Write an SQL statement to display SKU and SKU_Description.

SELECT SKU,SKU_Description

FROM INVENTORY;
3Write an SQL statement to display SKU_Description and SKU.

SELECT SKU_Description ,SKU

FROM INVENTORY;
4Write an SQL statement to display WarehouseID.

SELECT WarehouseID

FROM INVENTORY;
5 Write an SQL statement to display unique WarehouseIDs.

SELECT DISTINCT WarehouseID

FROM INVENTORY;
6 Write an SQL statement to display all of the columns without using the SQL asterisk (*) wildcard character.

SELECT WarehouseID,SKU,SKU_Description,QuantityOnHand,QuantityOnOrder

FROM INVENTORY;
7 Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard character.

SELECT *

FROM INVENTORY;
8 Write an SQL statement to display all data on products having a QuantityOnHand greater than 0.

SELECT *

FROM INVENTORY

WHERE QuantityOnHand>0;
9 Write an SQL statement to display the SKU and SKU_Description for products having QuantityOnHand equal to 0.

SELECT SKU,SKU_Description

FROM INVENTORY

WHERE QuantityOnHand = 0;

 

 

 

 

10 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for
products that have a QuantityOnHand equal to 0. Sort the results in ascending order by
WarehouseID.

SELECT SKU,SKU_Description,WarehouseID

FROM INVENTORY

WHERE QuantityOnHand=0

ORDER BY WarehouseID;

11 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for
products that have a QuantityOnHand greater than 0. Sort the results in descending
order by WarehouseID and in ascending order by SKU.

SELECT SKU, SKU_Description,WarehouseID

FROM INVENTORY

WHERE QuantityOnHand > 0

ORDER BY WarehouseID DESC, SKU;

12Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all
products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0.

Sort the results in descending order by WarehouseID and in ascending order by SKU.

SELECT SKU, SKU_Description,WarehouseID

FROM INVENTORY

WHERE QuantityOnHand = 0 AND QuantityOnOrder > 0

ORDER BY WarehouseID DESC, SKU；

13 Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all
products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort
the results in descending order by WarehouseID and in ascending order by SKU.

SELECT SKU, SKU_Description,WarehouseID

FROM INVENTORY

WHERE QuantityOnHand = 0 OR QuantityOnOrder = 0

ORDER BY WarehouseID DESC, SKU ASC;

14 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and
QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Do not use the BETWEEN keyword.

SELECT SKU, SKU_Description,WarehouseID QuantityOnHand

FROM INVENTORY

WHERE QuantityOnHand >1 AND QuantityOnHand < 10；
15 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and
QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Use the BETWEEN keyword.

SELECT SKU, SKU_Description,WarehouseID QuantityOnHand

FROM INVENTORY

WHERE QuantityOnHand BETWEEN 2 and 9；(假设了QuantityOnHand为整数，因为BETWEEN语句本身会包含上下界)

16 Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with 'Half-dome'.

SELECT DISTINCT SKU, SKU_Description

FROM  INVENTORY

WHERE SKU_description LIKE 'Half-dome%';

17 Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word 'Climb'.

SELECT DISTINCT SKU, SKU_Description

FROM  INVENTORY

WHERE SKU_Description LIKE '% Climb %';

18 Write an SQL statement to show a unique SKU and SKU_Description for all products having a 'd' in the third position from the left in SKU_Description.

 

SELECT DISTINCT SKU, SKU_Description

FROM  INVENTORY

WHERE SKU_Description LIKE '_ _d%';

19 Write an SQL statement that uses all of the SQL built-in functions on the QuantityOnHand column. Include meaningful column names in the result.

built-in functions: SUM()  AVG()  MIN()  MAX()  COUNT()

(1)

SELECT SUM(QuantityOnHand) AS QuantitySUM

FROM INVENTORY;

SELECT AVG(QuantityOnHand) AS QuantityAVG

FROM INVENTORY;

SELECT MIN(QuantityOnHand) AS QuantityMIN

FROM INVENTORY;

SELECT MAX(QuantityOnHand) AS QuantityMAX

FROM INVENTORY;

SELECT COUNT(QuantityOnHand) AS QuantityCOUNT 

FROM INVENTORY;

20 Explain the difference between the SQL built-in functions COUNT and SUM.

ANSWER:SUM是对符合条件的记录的数值列内容求和

COUNT是对查询中符合条件的结果（或记录）的个数求和

 

The build-in function COUNT means that calculating how many number are there in the table. But the function SUM means that adding all the number in the table which match the condition to get the sum.
21 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum TotalItemsOnHand. Display the results in descending order of TotalItemsOnHand.

SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHand

FROM INVENTORY

GROUP BY WarehouseID

ORDER BY TotalItemsOnHand DESC;
22 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Omit(删除) all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3. Display the results in descending order of TotalItemsOnHandLT3.

SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3

FROM INVENTORY

GROUP BY WarehouseID

ORDER BY TotalItemsOnHandLT3 DESC

HAVING SUM(QuantityOnHand) < 3;
23 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand,
grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from
the sum, and name the sum TotalItemsOnHandLT3. Show Warehouse ID only for
warehouses having fewer than 2 SKUs in their TotalItemsOnHandLT3. Display the results in descending order of TotalItemsOnHandLT3.

 

Use both the Inventory and Warehouse tables to answer Review Questions:
25 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or
Chicago warehouse. Do not use the IN keyword.

SELECT WAREHOUSE. WarehouseCity, WAREHOUSE.WarehouseState, INVENTORY. SKU, INVENTORY. SKU_Description, INVENTORY. WarehouseID

FROM WAREHOUSE, INVENTORY

WHERE WAREHOUSE. WarehouseCity=’ Atlanta’  OR  WAREHOUSE. WarehouseCity=’ Bangor’  OR  WAREHOUSE. WarehouseCity=’ Chicago’;

26 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or
Chicago warehouse. Use the IN keyword.

SELECT WAREHOUSE. WarehouseCity, WAREHOUSE.WarehouseState, INVENTORY. SKU, INVENTORY. SKU_Description, INVENTORY. WarehouseID

FROM WAREHOUSE, INVENTORY

WHERE WAREHOUSE. WarehouseCity  IN  (’Atlanta’, ‘Bangor’, or‘Chicago’);

 

27 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or
Chicago warehouse. Do not use the NOT IN keyword.

(1)

SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE

WHERE WAREHOUSE.WarehouseCity != ‘Atlanta’ AND WAREHOUSE.WarehouseCity != ‘Bangor’ AND WAREHOUSE.WarehouseCity != ‘Chicago’;

(2)

SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE

WHERE WarehouseCity= ’Seattle’;

28Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or
Chicago warehouse. Use the NOT IN keyword.

SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE

WHERE WAREHOUSE.WarehouseCity NOT IN(‘Atlanta’,‘Bangor’,‘Chicago’);

29 Write an SQL statement to produce a single column called ItemLocation that combines the SKU_Description, the phrase “is in a warehouse in”, and WarehouseCity. Do
not be concerned with removing leading or trailing blanks.

SELECT SKU_Description + ’is in a warehouse in’ + WarehouseCity AS ItemLocation

FROM INVENTORY, WAREHOUSE;

30Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by 'Lucille Smith'. Use a subquery.

SELECT SKU,SKU_Description, WarehouseID

FROM INVENTORY

WHERE WarehouseID IN (SELECT WarehouseID

FROM WAREHOUSE

WHERE Manager = ‘Lucille Smith’);
31Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN
ON syntax.

SELECT SKU,SKU_Description, WarehouseID

FROM INVENTORY, WAREHOUSE

WHERE INVENTORY.WarehouseID=WAREHOUSE. WarehouseID AND WAREHOUSE. Manager = ‘Lucille Smith’;
32 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.

SELECT SKU,SKU_Description, WarehouseID

FROM INVENTORY JOIN WAREHOUSE ON INVENTORY. WarehouseID = WAREHOUSE .WarehouseID

WHERE WAREHOUSE.Manager = ‘Lucille Smith’;
33 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by 'Lucille Smith'. Use a subquery.

SELECT WarehouseID,AVG(QuantityOnHand) AS AvgQuantityOnHand

FROM INVENTORY

WHERE WarehouseID IN (SELECT WarehouseID

FROM WAREHOUSE

WHERE Manager = ‘Lucille Smith’);

34 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN
ON syntax.

SELECT WarehouseID,AVG(QuantityOnHand) AS AvgQuantityOnHand

FROM INVENTORY, WAREHOUSE

WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID  AND WAREHOUSE.Manager = ‘Lucille Smith’;
35Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.

SELECT WarehouseID,AVG(QuantityOnHand) AS AvgQuantityOnHand

FROM INVENTORY JOIN WAREHOUSE ON INVENTORY. WarehouseID = WAREHOUSE .WarehouseID

WHERE WAREHOUSE .Manager = ‘Lucille Smith’;

36Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder, and
the sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name
the sum of QuantityOnOrder as TotalItemsOnOrder and the sum of QuantityOnHand
as TotalItemsOnHand.

SELECT WarehouseID, SUM(QuantityOnOrder ) AS TotalItemsOnOrder, SUM(QuantityOnHand) AS TotalItemsOnHand

FROM INVENTORY

GROUP BY WarehouseID , QuantityOnOrder;
37Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState,
Manager, SKU, SKU_Description, and QuantityOnHand of all items with a Manager of
'Lucille Smith'. Use a join.

SELECT WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, QuantityOnHand

FROM INVENTORY, WAREHOUSE

WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.manager=’ Lucille Smith;’
38 Explain how subqueries and joins differ.

ANSWER: The subqueries would create a new table which contain the information needed by the next query. But the joins only use the existed information in the recent tables.

子查询能够创造一个包含下次查询需要使用的信息的新表，而‘join’只能使用当前已经存在的信息。

子查询就是查询中又嵌套的查询,嵌套的级数随各数据库厂商的设定而有所不同,一般最大嵌套数不超过15级,实际应用中,一般不要超过2级,否则代码难以理解.一般来说,所有嵌套子查询都可改写为非嵌套的查询,但是这样将导致代码量增大.子查询就如递归函数一样,有时侯使用起来能达到事半功倍之效,只是其执行效率同样较低,有时用自身连接可代替某些子查询,另外,某些相关子查询也可改写成非相关子查询

表连接都可以用子查询，但不是所有子查询都能用表连接替换，子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件，而表连接更适合与查看多表的数据

子查询不一定需要两个表有关联字段，而连接查询必须有字段关联（所谓的主外键关系）
39 Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of
WAREHOUSE in your answer, regardless of whether they have any INVENTORY.

 

SELECT *

FROM WAREHOUSE LEFT OUTER JOIN INVENTORY ON WAREHOUSE.WarehouseID = INVENTORY.WarehouseID;

函数规范化部分

基本概念

1.一个关系模式是一个五元组，形如R(U,D,DOM,F)。其中D、DOM与模式设计关系不大，可以看作三元组R<U,F>。

 

    关系名R是符号化的元组定义；

    U为一组属性；

    D为属性组U中的属性所来自的域；

    DOM为属性到域的映射；

    F为属性组U上的一组数据依赖。

 

2.数据依赖：一个关系内部属性与属性之间的一种约束关系。最重要的是函数依赖（FD）和多值依赖（MVD）。

 

3.分析关系模式常见问题：

 

    数据冗余：重复出现，浪费空间。（尽可能少）

    更新异常：更新代价（最好没有）

    插入异常：插入部分信息时无法插入（最好没有）

    删除异常：可能删除了其他想要的数据（最好没有）

名词解释

1.函数依赖：

2.若X→Y，则称X为这个函数依赖的决定属性组，也称决定因素，Y为依赖因素。

3.码：能够唯一标识一条记录的属性或者属性集

Candidate key 候选码：若关系中某一属性（或属性组）的值能唯一地标识一个元组，则该属性（或属性组）为候选码。

Primary key 主码：若一个关系能有多个候选码，则选定其中一个为主码。

Key attribute主属性：包含在任何候选码中的属性称为主属性。

Non-key attribute 非码属性：不包含在任何候选码中地属性称为非码属性。

Composite key复合码：

Foreign key 外码：如果关系模式R中的属性（属性组）不是该关系的码，但它是其他关系的码，那么该属性（属性组）对关系模式R而言是外码。

1. What is the best test for determining whether a determinant(决定因子) is unique?

 

1. What is a composite key?

ANSWER：Composite key is a key of two or more attributes that uniquely identifies the row.

1. What is a candidate key?

ANSWER：A candidate key is a determinant that determines all of the other columns in a relation.

1. When would you use a surrogate key?

ANSWER：Surrogate keys are used when the primary key is large and unwieldy.（不易操作）

5. What is a foreign key? Explain the significance of the referential integrity constraint to a

foreign primary key.

   ANSWER：A foreign key is a column or composite of columns that is the primary key of a table other than the one in which it appears. the referential integrity constraint limits the values of a foreign key. Need to ensure that the values of a foreign key match a valid value of a primary key.

1.Consider the table:

STAFF_MEETING (EmployeeName, ProjectName, Date)

The rows of this table record the fact that an employee from a particular project attended a meeting on a given date. Assume that a project meets at most once per day.

Also, assume that only one employee represents a given project, but that employees

can be assigned to multiple projects.

1. State the functional dependencies in STAFF_MEETING.

Date->EmployeeName, ProjectName

ProjectName-> EmployeeName

1. Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

第一范式:任何一个关系数据库都满足第一范式

第二范式:在满足第一范式的基础上，所有的非码属性都由完整的主码决定

第三范式:满足第二范式的基础上，非码属性之间不存在函数依赖

BC范式:满足第三范式而且每个决定因子都是候选码

链接：https://www.cnblogs.com/lca1826/p/6601395.html

规范到BCNF步骤：

1. 找出函数依赖
2. 找出每个候选码
3. 判断是否存在一个函数依赖其决定因子不是候选码：

1. 将这种函数依赖所对应的列移到一个新的关系
2. 使原来函数依赖的决定因子作为新关系的主码
3. 使原来函数依赖的决定因子作为旧关系的外码
4. 新关系与旧关系之间保证满足完整性约束

此数据库满足第一和第二范式，但是因为非码属性之间存在函数依赖，所以不满足第三范式，应将projectname和employeename移到另一张单独的表里，即:

STAFF_MEETING(Date，ProjectName)

PROJECT(ProjectName，EmployeeName)

同时满足了BCNF

在STAFF_MEETING中：

主码:Date

候选码:Date

外码:ProjectName

在PROJECT中：

主码: ProjectName

候选码: ProjectName

外码:ProjectName

完整性约束:ProjectName in STAFF_MEETING must exits in PROJECT

C. Is your design in part B an improvement over the original table? What advantages

and disadvantages does it have?

是的， b部分的设计在原来的关系上有了提高。

优点：降低冗余，利于保证数据的一致性和完整性

缺点：这样做导致在为一个项目指定负责人之前必须要先确定开会日期。

 

 

2. Consider the table:

STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Club,

ClubCost, Sibling, Nickname)（sibling为兄弟姐妹）

Assume that students pay different dorm costs depending on the type of room they

have, but that all members of a club pay the same cost. Assume that students can have

multiple nicknames.

A. State any multivalued dependencies in STUDENT.

B. State the functional dependencies in STUDENT.

C. Transform this table into two or more tables such that each table is in BCNF and in

4NF. State the primary keys, candidate keys, foreign keys, and referential integrity

constraints.

1. We will assume that Number ->Name where name is not unique

StudentNumber->->Club

StudentNumber->->Sibling

StudentNumber->->Nickname

1. StudentNumber->(StudentName,Dorm,RoomType,DormCost)

(Dorm,RoomType)->DormCost

Club->ClubCost

1. We’ll move the obvious multivalues dependencies into their own tables, and then

check for BCNF.  IF we have BCNF and no multivalued dependencies, we also have

4NF:

STEP ONE:  MOVE MUTIVALUED DEPENDENCIES INTO SEPARATE TABLES.

STUDENT_2(StudentNumber, StudentName,Dorm,RoomType,DormCost)

STUDENT_CLUB_MEMBERSHIP(StudentNumber,Club,ClubCost)

STUDENT_SIBLING(StudentNumber,Sibling)

STUDENT_NICKNAME(StudentNumber,Nickname)

STEP TWO:  CHECK EACH OF THE RESULTING TABLES FOR BNCF:

STEP TWO (A): STUDENT_2 FUNCTIONAL DEPENDENCIES: 

StudentNumber ->(StudentName,Dorm,RoomType,DormCost)

(Dorm,RoomType)->DormCost

 

STUDENT_2 CANDIDATE KEYS:

 StudentNumber

 Is every determinant a candidate key?

  NO, RoomType is NOT a candidate key.

  Therefore the relation is NOT in BCNF,Therefore, move RoomType DormCost into another table   STUDENT_3 (StudentNumber, StudentName, Dorm, RoomType)

  DORM_RATE (Dorm ,RoomType, DormCost) 

STEP TWO (A) (1):  CHECK STUDENT_3:

  STUDENT FUNCTIONAL DEPENDENCIES:  

StudentNumber ->(StudentName ,Dorm, RoomType)

  STUDENT CANDIDATE KEYS:

  StudentNumber

  Is every determinant a candidate key?    YES, Therefore STUDENT_3 is in BNCF.

 STEP TWO (A) (2):  CHECK STUDENT:

        DORM_RATE FUNCTIONAL DEPENDENCIES:  

(Dorm ,RoomType )->DormCost

  DORM_RATE CANDIDATE KEYS:

  (Dorm ,RoomType)

  Is every determinant a candidate key?    YES, Therefore DORM_RATE is in BNCF.

 

 STEP TWO (B): 

 STUDENT_CLUB_MEMBERSHIP FUNCTIONAL DEPENDENCIES:  STUDENT_CLUB_MEMBERSHIP (StudentNumber, Club, ClubCost) 

(StudentNumber, Club)->ClubCost

 Club->ClubCost

 STUDENT_CLUB_MEMBERSHIP CANDIDATE KEYS:

 (StudentNumber, Club)

 Is every determinant a candidate key?   NO, Club is not a candidate key.

  Therefore, move Club  ClubCost into another table   STUDENT_CLUB_MEMBERSHIP (StudentNumber, Club)

  STUDENT_CLUB_COST (Club, ClubCost) 

  STEP TWO (B) (1):  CHECK STUDENT_CLUB_MEMBERSHIP:

  STUDENT_CLUB_MEMBERSHIP FUNCTIONAL DEPENDENCIES:   STUDENT_CLUB_MEMBERSHIP (StudentNumber, Club)   None  StudentNumber does not determine Club, and Club does not determine Number.

  STUDENT_CLUB_MEMBERSHIP CANDIDATE KEYS:

  (StudentNumber, Club) 

Is every determinant a candidate key?    YES, in this case there are NO determinants, but this meets the criteria!

   Therefore STUDENT_CLUB_MEMBERSHIP is in BNCF.   Are the fields of the multivalued dependency the only fields  in this table?    YES, Therefore STUDENT_CLUB_MEMBERSHIP is in 4NF.

 

  STEP TWO (B) (2):  CHECK STUDENT_CLUB_COST(Club, ClubCost)  

  STUDENT_CLUB_COST FUNCTIONAL DEPENDENCIES:   Club ->ClubCost

  STUDENT_CLUB_COST  CANDIDATE KEYS: Club 

Is every determinant a candidate key?    YES, Therefore STUDENT_CLUB_COST is in BNCF.

  STEP TWO (C):  STUDENT_SIBLING (StudentNumber, Sibling)

 STUDENT_SIBLING FUNCTIONAL DEPENDENCIES:

  None  StudentNumber does not determine Sibling,

  and Sibling does not determine StudentNumber.

 STUDENT_SIBLING CANDIDATE KEYS:

 (Number, Sibling) 

Is every determinant a candidate key?   YES, in this case there are NO determinants, but this meets the criteria!

  Therefore STUDENT_SIBLING is in BCNF.  Are the fields of the multivalued dependency the only fields in this table?   YES, Therefore STUDENT_SIBLING is in 4NF.

STEP TWO (D): STUDENT_NICKNAME (StudentNumber, Nickname)

 STUDENT_NICKNAME FUNCTIONAL DEPENDENCIES:    None  StudentNumber does not determine Nickname, and Nickname does not determine StudentNumber.

 STUDENT_NICKNAME CANDIDATE KEYS:

 (StudentNumber, Nickname)

Is every determinant a candidate key?   YES, in this case there are NO determinants, but this  meets the criteria!

  Therefore STUDENT_NICKNAME is in BCNF.  Are the fields of the multivalued dependency the only fields in this table?   YES, Therefore STUDENT_NICKNAME is in 4NF.

 ALL TABLES ARE NOW IN BCNF AND 4NF!

STEP THREE:  STATE FINAL MODEL SPECIFICATIONS:

 Primary Keys are underlined. （下划线）

 Foreign Keys are italicized.(斜体)

Non-Primary Key Candidate Keys (Alternate Primary Keys) are stated

following each relation as Alternate Keys [NOTE: None exist].

 Referential Integrity Constraints are stated following each relation.

 

STUDENT_3 (StudentNumber, Name, Dorm, RoomType)

 WHERE STUDENT.RoomType must exist in DORM_RATE.RoomType

DORM_RATE (RoomType, DormCost)

STUDENT_CLUB_MEMBERSHIP (StudentNumber, Club)

 WHERE STUDENT_CLUB_MEMBERSHIP.Number must exist in 

   STUDENT_3.Number

 AND  STUDENT_CLUB_MEMBERSHIP.Club must exist in

STUDENT_CLUB_COST.Club

STUDENT_CLUB_COST (Club, ClubCost)

STUDENT_SIBLING (StudentNumber, Sibling)

 WHERE STUDENT_SIBLING.Number must exist in STUDENT_3.Number

STUDENT_NICKNAME (StudentNumber, Nickname)

 WHERE STUDENT_NICKNAME.Number must exist in STUDENT.

 

James Morgan keeps a table of data about the stores from which he purchases. The stores are located in different countries and have different specialties（专长、特产）. Consider the following relation:

STORE (StoreName, City, Country, OwnerName, Specialty)

1. Explain the conditions under which each of the following is true:

1. StoreName→City

TRUE when a store name is associated with only one city.

1. City→StoreName

TRUE when there is only one store in each city.

1. City →Country

TRUE when city names are unique within a country.

1. (StoreName, Country) →(City, OwnerName)

The same store name can exist in multiple countries and the same country can have multiple store names, but the Name, Country pairs must be unique. The Name, Country pairs are always associated with the same city and owner.

1. (City, Specialty) →StoreName

The same store name can exist in multiple countries but different stores have different specialties.

1. OwnerName→→StoreName

TRUE when owners may own more that one store.

7. StoreName→→ Specialty

TRUE when stores may have more than one specialty.

1. With regard to the relation in part A:

 

1. Specify which of the dependencies in part A seem most appropriate for a small import–export business.

(StoreName, Country) →(City, OwnerName)

StoreName→→ Specialty

2. Given your assumptions in B.1, transform the STORE table into a set of tables that are in both 4NF and BCNF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.

STORE_LOCATION (StoreName, Country, City, OwnerName)

STORE_SPECIALTY (StoreName, Specialty)

WHERE STORE_SPECIALTY. StoreName must exist in STORE_LOCATION.

 

C. Consider the relation:

SHIPMENT (ShipmentNumber, ShipperName, ShipperContact, ShipperFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

1. Write a functional dependency that expresses the fact that the cost of a shipment between two cities is always the same.

 

(CountryOfOrigin, Destination) ->ShipmentCost

1. Write a functional dependency that expresses the fact that the insurance value is always the same for a given shipper.

 

ShipperName ->lnsuranceValue

1. Write a functional dependency that expresses the fact that the insurance value is always the same for a given shipper and country of origin.

(ShipperName, CountryOfOrigin)-> lnsuranceValue

1. Describe two possible multivalued dependencies in SHIPMENT.

ShipperName -> -> ShipperContact

ShipperName ->->Destination

1. State what you believe are reasonable functional dependencies for the SHIPMENT relation for a small import–export business.

ShipmentNumber ->(ShipperName, ShipperContact, ShipperFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

(CountryOfOrigin, Destination) ->ShipmentCost

 

1. State what you believe are reasonable multivalued dependencies for the SHIPMENT relation.

ShipperName ->-> Destination

ShipperName -> -> ShipperContact

 

7. Using your assumptions in 5 and 6, transform SHIPMENT into a set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.

SHIPMENT (ShipmentNumber, VendorName, VendorContact, VendorFax , DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost , lnsuranceValue,  Insurer)

STEP ONE:   LIST THE MULTIVALUED DEPENDENCIES:

ShipperName ->-> Destination

ShipperName -> -> ShipperContact

STEP TWO:   LIST THE FUNCITONAL DEPENDENCIES:

ShipmentNumber ->(ShipperName, ShipperContact, ShipperFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

(CountryOfOrigin, Destination) ->ShipmentCost

STEP THREE:  LIST THE INITIAL CANDIDATE KEYS

ShipmentNumber

Is every determinant a candidate key?

NO, (CountryOfOrigin, Destination) is NOT a candidate key.

Therefore the relation is NOT in BCNF

Final Set of BCNF relations:

SHIPMENT_3(ShipmentNumber,ShipperName, DepartureDate, Arrival Date, CountryOfOrigin, Destination, lnsuranceValue, Insurer)

WHERE SHIPMENT_3. ShipperName must exist in SHIPPER_FAX. ShipperName

SHIPCOUNTRY(CountryOfOrigin, Destination, ShipmentCost)

WHERE SHIPCOUNTRY. CountryOfOrigin and Destination must exist in SHIPMENT_3. CountryOfOrigin and SHIPCOUNTRY. Destination must exist in SHIPMENT_3. Destination SHIPPER_FAX (ShipperName, ShipperFax)

SHIPPER_CONTACT ( ShipperName,ShipperContact)

WHERE SHIPPER_CONTACT.ShipperName must exist in SHIPMENT_3.ShipperName