第一章：
What is the purpose of a database?数据库的目的
The purpose of a database is to help people track(跟踪监测) of things.
What is the most commonly used type of database?最常见的数据库类型 The most commonly used type of database is the relational database.
Give an example of two related tables other than the example used in this book. Use the STUDENT and GRADE tables in Figure 1-3 as an example pattern(模式) for your tables. Name the tables and columns using the conventions in this book.给一个关联表的例子，参考1-3
1.1
1.2
1.3
Answer:
Query1：学生信息 StudentNumber LastName 1 Cookie 2 Lara 3 Harris FirstName Sam Marcia Lou EmailAddress 1234@OurU.edu 1245@OurU.edu 2453@OurU.edu 4 Greene Grace 2563@OurU.edu 5 Smith Anna 4345@OurU.edu Query2：宿舍信息 StudentNumber RoomNumber RoomInformation Rent 1 1 5-123 1000 2 3 3-633 1100 3 4 4-466 1200 4 2 2-454 1100 5 5 4-454 1300
For the tables you created in Review Question 1.3, what are the primary keys of each table? Do you think that any of these primary keys could be surrogate(代理) keys?每张表的主码？这些主码可以是代理码吗？ Query1: StudentNumber
Query2: StudentNumber, RoomNumber Yes, both.
1.5 Explain how the two tables you provided in Review Question 1.3 are related. Which table contains the foreign key, and what is the foreign key?
The primary key of Q1 were added to the Q2 with a primary key of StudentNumber to uniquely identify each row.
In Q2 StudentNumber and RoomNumber each now serves as a foreign key.
1.6 Show your two tables from Review Question 1.3 without the columns that represent the relationships. Explain how the value of your two tables is diminished(减少) without the relationships.在你的表中去掉代表关系的列，解释没有关系的表的value如何减少？
1.7 Define the terms data and information. Explain how the two terms differ.定义术语data和information,解释它俩的不同。 Answer: Data are recorded facts and numbers. We can define information as: Knowledge derived from data.
Data presented in a meaningful context.
Data processed by summing, ordering, averaging, grouping, comparing or other similar operations.
1.8 Give an example of information that could be determined using the two tables you provided in your answer to Review Question 1.3. Anna Smith lives in 4-454 which the rent is 1300.
1.9 Give examples of a single-user database application and a multiuser database application other than the ones shown in Figure 1-5.举例子，单用户和多用户的数据库应用，除表1-5之外。 single-user: Cloud Service
1.4
multi-user: File Management System
1.10 What problem can occur when a database is processed by more than one user? 当数据库被不止一个用户处理会导致什么问题？
Answer: When more than one user employs a database application, these is always the chance that one user's work may interfere with other's.
1.11 Give an example of a database application that has hundreds of users and a very large and complicated database. Use an example other than one in Figure 1-5.
举一个有成百上千个用户和一个庞大而复杂数据库的数据库应用，除表1-5以外。
Answer: Selection lesson system 选课系统
1.12 What is the purpose of the largest databases at e-commerce companies such as Amazon.com?
像亚马逊这样的电商公司有着最大的数据库的目的是什么？
Answer: The largest database are those that track customer browser behavior. (用来记录用户的浏览行为)
1.13 How do the e-commerce companies use the databases discussed in Review Question 1.12?
电商公司如何使用数据库？
Answer: E-commerce companies use Web activity databases to determine which items on a Web page are popular and successful and which are not.
1.14 How do digital dashboard and data mining applications differ from transaction processing applications?
数字仪表板和数据挖掘应用程序在事务处理应用上有何不同？
Answer: Digital dashboard and other reporting systems assess past and current performance. Data mining applications predict future performance.
1.15 Explain why a small database is not necessarily simpler than a large one. 解释为什么一个小的数据库不一定比大的数据库简单？
Answer: Supposed we have 2 company which are different in sales but have similar database. Though the difference in sale, both have the same kinds of data, about the same number of tables of data, about the same level of complexity in data relationships. Only the amount of data varies from one to the other. Thus, although a database for a small business may be small, it is not necessarily simple. (一句话，麻雀虽小五脏俱全)
1.16 Explain the components in Figure 1-7.解释图1-7的成分要素
A database system is defined to consist of five components:users, the database application,Structured Query Language (SQL), the database management system (DBMS), and the database.
a. Users employ a database application to keep track of things. They use forms to read, enter, and query data, and they produce reports to convey information.
b. A database application is a set of one or more computer programs that serves as an intermediary between the user and the DBMS.
c. Structured Query Language (SQL), an internationally recognized standard language that is understood by all commercial DBMS products, in database processing and the fact that database applications typically send SQL statements to the DBMS for processing.
d. The database management system (DBMS) is a computer program used to create, process, and administer the database.
e. The database is a collection of related tables and other structures.
1.17 What are the functions of application programs? 应用程序的功能是什么？ Answer:
? Create and process forms ? Process user queries
? Create and process reports ? Execute application logic ? Control application
1.18 What is Structured Query Language (SQL), and why is it important?
Structured Query Language (SQL) is an internationally recognized standard language. Because it nearly can be understood by all commercial DBMS products, in database processing, database applications typically send SQL statements to the DBMS for processing. (在数据库方法中，数据库应用向DBMS发送SQL语句)
1.19 What does DBMS stand for? Database management system
1.20 What are the functions of the DBMS? ? Create database、Create tables ? Create supporting structures ? Read database data
? Modify (修改)(insert, update, or delete) database data ? Maintain database structures ? Enforce rules
? Control concurrency(并发控制) ? Provide security
? Perform backup and recovery (备份与恢复)
1.21 Name three vendors of DBMS products. MySQL，Microsoft SQL Server，IBM
1.22 Define the term database.(数据库一词的定义)
A database is a self-describing collection of integrated tables.
1.23 Why is a database considered to be self-describing?
A database is self-describing because it contains a description of itself. Thus, databases contain not only tables of user data, but also tables of data that describe that user data.
1.24 What is metadata(元数据)? How does this term pertain(属于) to a database? databases contain not only tables of user data, but also tables of data that describe that user data. Such descriptive data is called metadata because it is data about data.(元数据是描述数据的数据)
1.25 What advantage is there in storing metadata in tables?
You can examine(调查) metadata to determine if particular tables, columns, indexes, or other structures exist in a database.
可以通过调查元数据得知某些特定的结构是否存在于数据库中
1.26 List the components of a database other than user tables and metadata. 除了用户表和元数据以外的其他数据库组件。 ? Indexes
? Stored procedures ? Triggers ? Security data
? Backup/recovery data
1.27 Is Microsoft Access a DBMS? Why or why not?
No, Microsoft Access is not just a DBMS. Because it is a personal database system: a DBMS plus an application generator. Although Microsoft Access contains a DBMS engine that creates, processes, and administers the database, it also contains form, report, and query components that are the Microsoft Access application generator.(应用程序生成器)
1.28-1.35了解就好，太偏太细，感觉不会考
1.28 Describe the components shown in Figure 1-15.
Components of a Microsoft Access Database System
Microsoft Access contains a DBMS engine that creates, processes, and administers the database, it also contains form, report, and query components that are the Microsoft Access application generator.
1.29 What is the function of the application generator in Microsoft Access?
The application generator consists of applications components that create and process forms, reports, and queries.
1.30 What is the name of the DBMS engine within Microsoft Access? Why do we rarely hear about that engine?
The current DBMS engine within Microsoft Access is called the Access Database Engine (ADE). ADE is a Microsoft Office specific version of Microsoft‘s Joint Engine Technology (JET or Jet) database engine. But you seldom hear about Jet because Microsoft does not sell Jet as a separate product. ADE是Jet引擎的一个版本，很少听说是因为Jet不是一个独立销售的产品
1.31 Why does Microsoft Access hide important database technology?
Because it is an effective strategy for beginners working on small databases
1.32 Why would someone choose to replace the native Microsoft Access DBMS engine with SQL Server?
You would do this if you wanted to process a large database or if you needed the advanced functions and features of Microsoft SQL Server.
1.33 Name the components of an enterprise-class database system.
1.34 Name and describe the four categories of database applications that would use an enterprise-class database system.
client/server applications：the application program is a client that connects to a database server. Client/server applications often are written in programming languages such as VB.NET, C++, or Java.
e-commerce applications：run on a Web server
reporting applications：publish the results of database queries on a corporate portal or other Web site.
XML Web services applications：use a combination of the XML markup language and other standards to enable program-to-program communication.
1.35 How do database applications read and write database data?
All of these database applications get and put database data by sending SQL statements to the DBMS.
1.35How do database applications read and write database data?(数据库应用程序是如何读写数据库数据的？ PPT: First class P23)
Answer：the application program first queries the DBMS for data (again using SQL). The application then formats the query results as a report. （首先在DBMS中使用SQL查询数据，然后将查询结果作为报表显示）
1.36Name the five DBMS products described in this chapter, and compare them in terms of power, features, and difficulty of use.（列出本章中5个DBMS产品，并比较他们的功效、特性和使用的难易程度 PPT：second class p39）
Answer: Microsoft Access, MySQL, SQL Server, DB2, and Oracle Database.(这已经是按比较顺序来的了)
1.37List several consequences of a poorly designed database.（列出几个数据库设计不良的后果） Answer：
（1）They may require application developers to write overly complex and contrived SQL to get –wanted data.（开发人员需要编写过于复杂的SQL来获取数据） （2）they may be difficult to adapt to new and changing requirements.（很难适应新的不断变化的需求）
（3）they may fail in some other way.（或其他）
1.38Explain two ways that a database can be designed from existing data.（解释两种可以用现有数据设计的数据库 PPT: second class p13） Answer：
（1）The first type of database design involves databases that are constructed from existing
（2）A second way that databases are designed is for the development of new information systems.（ p17）
1.39 What is a data warehouse? What is a data mart?（什么是数据仓库？什么是数据集市）
Answer：data from an operational database, such as a CRM or ERP database, may be copied into a new database that will be used only for studies and analysis. Such databases are used in facilities called data warehouses and data marts. (笔记)
1.40Describe the general process of designing a database for a new information system.(描述为一个新的信息斯通设计数据库的一般过程) Answer：（1）create data model from application requirements
( 2)transform data model into database design (PPT: second class p12)
1.41 Explain two ways that databases can be redesigned.(解释两种数据库重设计的方式 PPT： third class p19或12) Answer：
(1)In the first, a database is adapted to new or changing requirements. This process sometimes is called database migration. In the migration process, tables may be created, modified, or removed; relationships may be altered; data constraints may be changed; and so forth.
(2)The second type of database redesign involves the integration of two or more databases. This type of redesign is common when adapting or removing legacy systems. It is also common for enterprise application integration, when two or more previously separate information systems are adapted to work with each other.
1.42What does the term database migration mean?(数据库迁移是什么意思)
Answer：The process of a database is adapted to new or changing requirements.
1.43Summarize the various ways that you might work with database technology.（总结你可能使用数据库技术的不同方式） Answer：
In our career, we may work with database technology as either a user or as a database administrator.
（1）As a user, you may be a knowledge worker who prepares reports, mines data, and does other types of data analysis or you may be a programmer who writes applications that process the database. （作为用户时）
（2）Alternatively, you might be a database administrator who designs, constructs, and manages the database itself. Users are primarily concerned with constructing SQL statements to get and put the data they want. Database administrators are primarily concerned with the management of the database.（作为数据库管理员时）
1.44 What job functions does a knowledge worker perform?（知识工作者（意会……）工作职能是什么）
Answer：preparing reports, mining data, and doing other types of data analysis.
1.45 What job functions does a database administrator perform?（数据库管理员的工作职能是什么）
Answer：designing, constructing, and managing the database itself.
1.46 Explain the meaning of the domains（域）in Figure 1-23.（解释图中的域的意思）（这题找不到= =）
Answer：
Knowledge workers Programmers
Database administrators
1.47 What need drove the development of the first database technology?
Answer：The need for data integration drove the development of the first database technology.
1.48 What are Data Language/I and CODASYL DBTG?
Answer：Data Language/I (DL/I) used hierarchies or trees (see Appendix G) to represent relationships.
This subcommittee developed a standard data model that came to bear its name—the CODASYL DBTG model. It was an unnecessarily complicated model. This data relationship used data structures called networks.
1.49 Who was E. F. Codd?
ANSWER: E.F.Codd was a little-known IBM engineer published a paper in the Communications of the ACM3 in which he applied the concepts of a branch of mathematics called relational algebra to the problem of ―shared data banks,‖ as databases were then known. The results of this work are now the relational model for databases, and all relational database DBMS products are built on this model.
1.50What were the early objections to the relational model?(最早的关系模型的反对是什么)
Answer： Early objections included (1) too theoretical for practical implementation, (2) too slow, and(3) so much storage would be required that the model would never be useful in thecommercial world
1.51 Name two early relational DBMS products. ANSWER: Oracle Database, DB2.
1.52 What are some of the reasons for the success of Oracle Database? （数据库成功的原因是什么？） ANSWER:
Oracle Database achieved success for many reasons, one of which was that（1） it
would run on just about any computer and just about any operating system. （2）Oracle Database had, and continues to have, an elegant and efficient internal design.
1.53 Name three early personal computer DBMS products. ANSWER:
dBase, R:base, Paradox.
1.54 What happened to the products in your answer to Review Question 1.53? ANSWER:
dBase was the most successful of the early products, but another product, R:base, was the first to implement true relational algebra and other operations on the PC. Later, another relational DBMS product named Paradox was developed for personal computers. Eventually, Paradox was acquired by Borland.
Microsoft Access killed R:base and Paradox, and then Microsoft bought a dBase ―work-alike‖ product called FoxPro and used it to eliminate dBase
1.55 What was the purpose of OODBMS products? State two reasons that OODBMS products were not successful.（面向对象数据库管理系统产品的目的是什么？陈述OODBMS产品均未成功的两个原因） ANSWER:
These products were designed to make it easy to store the data encapsulated in OOP objects
There were two reasons for their lack of acceptance. First, using an OODBMS required that the relational data be converted from relational format to object-oriented format. By the time OODBMS emerged, billions upon billions of bytes of data were stored in relational format in organizational databases. No company was willing to undergo the expensive travail of converting those databases to be able to use the new OODBMS.
Second, object-oriented databases had no substantial advantage over relational databases for most commercial database processing. As you will see in the next chapter, SQL is not object oriented. But it works, and thousands of developers have created programs that use it. Without a demonstrable advantage over relational databases, no organization was willing to take on the task of converting their data to OODBMS format.
1.56 What characteristic of HTTP was a problem for database processing applications? ANSWER:
HTTP is a stateless protocol; a server receives a request from a user, processes the request, and then forgets about the user and the request. Many database interactions are multistage. A customer views products, adds one or more to a shopping cart, views more products, adds more to the shopping cart, and eventually checks out. A stateless protocol cannot be used for such applications.
1.57 What is an open source DBMS product? Which of the five DBMS products that you named in answering Review Question 1.36 is historically an open source DBMS product?（什么是一个开源的数据库管理系统产品？） ANSWER:
the MySQL DBMS 答案待商榷
1.58 What has been the response of companies that sell proprietary DBMS products to the open source DBMS products? Include two examples in your answer. ANSWER:
One interesting outcome of the emergence of open source DBMS products is that companies that typically sell proprietary (closed source) DBMS products now offer free versions of their products. For example, Microsoft now offers SQL Server 2008 R2 Express (www.microsoft. com/express/Database), and Oracle Corporation makes its Oracle Database 10g Express Edition available for free.
1.59 What is XML? What comment did Bill Gates make regarding XML?（什么是XML？比尔盖茨对此做了什么评论？） ANSWER:
XML means Extensible Markup Language.
Bill Gates said that ―XML is the lingua-franca of the Internet Age.‖
1.60 What is the NoSQL movement? Name two applications that rely on NoSQL databases. ANSWER:
It‘s the work is really on databases are often based on XML.
Two applications that rely on NoSQLdatabases: Facebook and Twitter.
第二章
2.1 What is a business intelligence (BI) system? ANSWER:
Application refers to the collection of commercial information, the integration, analysis and the technical reports.
2.2 What is an ad-hoc query? ANSWER:
ad-hoc SQL queries is how SQL is used to ―ask questions‖ about the data in the database.
2.3 What does SQL stand for, and what is SQL? ANSWER:
SQL stand for Structured Quqery Language. SQL is not a complete programming language, like Java or C#. Instead, it is called a data sublanguage, because it has only those statements needed for creating and processing database data and metadata.
2.4 What does SKU stand for? What is an SKU? ANSWER:
SKU stand for stock-keeping unit. SKU is a unique identifier for each particular item that Cape Codd sells或SKU is an integer value that identifies a particular product sold by Cape Codd.
2.5 Summarize how data were altered and filtered in creating the Cape Codd data extraction. ANSWER:
the ORDER_ITEM table stores an extract of the items purchased in each order. There is one row in the table for each item in an order, and this item is identified by its SKU.
The OrderNumber Column in ORDER_ITEM relates each row in ORDER_ITEM to the corresponding OrderNumber in the RETAIL_ORDER table. SKU identifies the actual item purchased by its stock-keeping unit number. Further, the SKU column in ORDER_ITEM relates each row in ORDER_ITEM to its corresponding SKU in the SKU_DATA table
2.6 Explain, in general terms, the relationships among the RETAIL_ORDER, ORDER_ITEM,and SKU_DATA tables. ANSWER:
The RETAIL_ORDER table has data about each retail sales order, the ORDER_ITEM table has data about each item in an order, and the SKU_DATA table has data about each stock-keeping unit (SKU)
2.7 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).
2.8 What is SQL-92? How does it relate to the SQL statements in this chapter? ANSWER:
Subsequent versions of SQL were adopted in 1989 and 1992. The 1992 version is sometimes referred to as SQL-92, or sometimes as ANSI-92 SQL. 缺少第二问答案
2.9 What features have been added to SQL in versions subsequent to the SQL-92? 什么样的功能已被添加到SQL版本SQL-92后续
ANSWER: All the new versions of SQL support for Extensible Markup Language (XML) and there are many other common language features.最重要的功能是添加了对XML语句的支持
这是ppt里面复制的话：Each of these added new features or extended existing SQL
features, the most important of which for us is SQL support for Extensible Markup Language (XML).
2.10 Why is SQL described as a data sublanguage?
ANSWER: Because it has only those statements needed for creating and processing d
atabase 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. 百度翻译：它被称为一个数据子语言，因为它只有那些创建与数据库数据处理和元数据需要的报表。你可以在许多不同的方式使用SQL语句。您可以直接提交给数据库管理系统处理。您可以嵌入SQL语句为客户端/服务器应用程序。您可以将它们嵌入到网页中，并且您可以使用它们来报告和数据提取程序。你还可以执行SQL语句直接从Visual Studio .NET等开发工具。
2.11 What does DML stand for? What are DML statements?
ANSWER: DML stand for Data Manipulation Language, which is a category of SQL. Data manipulation language (DML) statements, which are used for querying, inserting, modifying, and deleting data.
2.12 What does DDL stand for? What are DDL statements?
ANSWER: DDL stand for Data definition language, which is a category of SQL. Data definition language (DDL) statements, which are used for creating tables, relationships, and other structures
2.13 What is the SQL SELECT/FROM/WHERE framework?
ANSWER: The basic form of SQL queries uses the SQL SELECT/FROM/WHERE framework.
In this framework:
? The SQL SELECT clause specifies which columns are to be listed in the query results.
? The SQL FROM clause specifies which tables are to be used in the query.
? The SQL WHERE clause specifies which rows are to be listed in the query results. SQL查询的基本形式采用SQL选择/从/框架。在这个框架中： ?SQL SELECT子句中指定的列将查询结果中的列。 ?FROM子句中指定的SQL表用于查询。
?SQL WHERE子句指定哪些行是查询结果中的列。
（2.10 2.11 2.12 2.13解释那些东西都是ppt的原话）
2.14 Explain how Microsoft Access uses SQL.（ppt：第四课 p4之后 不造咋概括。。答案写的也有点奇怪，，不是很懂）
ANSWER: Every time you process a form, create a report, or run a query Microsoft Access generates SQL and sends that SQL to Microsoft Access‘ internal ADE DBMS engine. To do more than elementary database processing, you need to uncover the SQL hidden by Microsoft Access. Further, once you know SQL, you will find it easier to write a query statement in SQL rather than fight with the graphical forms, buttons.
2.15 Explain how enterprise-class（企业级）DBMS products use SQL.（ppt 第五课应该都是。。。同上一题懵逼。。。）
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 Figure 2-24. 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.
If at all possible, you should run your SQL solutions to the following questions against an actual database. a Microsoft access database named Cape-Codd.accdb is
available on our Web site (www.pearsonhighered.com/kroenke) that contains all the tables and data for the Cape Codd Outdoor Sports sales data extract database. also
available on our Web site are SQL scripts for creating and populating the tables for the
Cape Codd database in Microsoft SQL Server, Oracle Database, and MySQL.
2.16 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?
比较SKU和INVENTORY表，INVENTORY中有个设计缺陷，说明为什么会产生这个缺陷 不懂。。。。。
Use only the Inventory table to answer Review Questions 2.17 through 2.39:
SELECT 查找列（去除重复用DISTINCT） FROM 表名
WHERE 从行的角度选择满足条件的行 GROUP BY 分组
HAVING 从分组的角度选择满足条件的分组
ORDER BY 根据指定的列对结果集进行排序，默认升序，降序需要使用DESC关键字
2.17 Write an SQL statement to display SKU and SKU_Description.
SELECT SKU,SKU_Description FROM INVENTORY;
2.18 Write an SQL statement to display SKU_Description and SKU.
SELECT SKU_Description,SKU FROM INVENTORY;
（2.17、2.18和搜的那份答案不一样）
2.19 Write an SQL statement to display WarehouseID.
SELECT WarehouseID FROM WAREHOUSE;
2.20 Write an SQL statement to display unique WarehouseIDs.
SELECT WarehouseIDs FROM WAREHOUSE;
2.21 Write an SQL statement to display all of the columns without using the SQL asterisk(*) wildcard character.
SELECT WarehouseID, WarehouseCity, WarehouseState, Manager, SquareFeet FROM WAREHOUSE.
SELECT WarehouseID.SKU,SKU_Description,QuantityOnHand,QuantityOnHand FROM INVENTORY;
2.22 Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcardcharacter. SELECT *
FROM WAREHOUSE.
SELECT *
FROM INVENTORY;
2.23 Write an SQL statement to display all data on products having a QuantityOnHand greater than 0.
SELECT *
FROM INVENTORY
WHERE QuantityOnHand>0;
2.24 Write an SQL statement to display the SKU and SKU_Description for products havingQuantityOnHand equal to 0.
SELECT SKU,SKU_Description FROM INVENTORY
WHERE QuantityOnHand=0;
2.25 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID forproducts that have a QuantityOnHand equal to 0. Sort the results in ascending order byWarehouseID. SELECT SKU,SKU_Description,WarehouseID FROM INVENTORY
WHERE QuantityOnHand=0 ORDER BY WarehouseID;
2.26 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
WHEREQuantityOnHand> 0
ORDER BYWarehouseIDDESC,SKU ASC;
2.27 Write 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
WHEREQuantityOnHand = 0 ANDQuantityOnOrder> 0 ORDER BYWarehouseIDDESC,SKU ASC
2.28 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
WHEREQuantityOnHand = 0 ORQuantityOnOrder = 0 ORDER BYWarehouseIDDESC,SKU ASC;
2.29 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,WarehouseIDQuantityOnHand FROM INVENTORY
WHEREQuantityOnHand>1 ANDQuantityOnHand< 10 ORDER BYWarehouseIDDESC,SKU ASC;
2.30 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,WarehouseIDQuantityOnHand FROM INVENTORY
WHEREQuantityOnHandBETWEEN 1 and 10 ORDER BYWarehouseIDDESC,SKU ASC;
2.31 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%';
2.32 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 %';
2.33 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%';
2.34 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()
SELECT SUM(QuantityOnHand) AS QuantitySUM, FROM INVENTORY SELECT AVG(QuantityOnHand) AS QuantityAVG FROM INVENTORY SELECT MIN(QuantityOnHand) AS QuantityMINFROM INVENTORY
SELECT MAX(QuantityOnHand) AS QuantityMAX FROM INVENTORY SELECT COUNT(QuantityOnHand) AS QuantityCOUNTFROMINVENTORY
2.35 Explain the difference between the SQL built-in functions COUNT and SUM.
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.
2.36 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 WarehouseIDSUM(QuantityOnHand) ASTotalItemsOnHand FROM INVENTORY; GROUP BYWarehouseID,
ORDER BY TotalItemsOnHand DESC;
2.37 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.
2.38 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.
2.39 In your answer to Review Question 2.38, was the WHERE clause or the HAVING clause applied first? Why?
Use both the INVENTORY and WAREHOUSE tables to answer Review Questions 2.40through 2.55:
2.40 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.
SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY WHERE WAREHOUSE.WarehouseCity=‘ Atlanta‘ ORWAREHOUSE.WarehouseCity=‘ Bangor‘ ORWAREHOUSE.WarehouseCity=‘ Chicago‘;
2.41 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.
SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY
WHERE WAREHOUSE.WarehouseCityIN(‘Atlanta‘, ?Bangor‘, or?Chicago‘);
2.42 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. SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY WHERE WAREHOUSE.WarehouseCity<>‘ Atlanta‘ ORWAREHOUSE.WarehouseCity<>‘ Bangor‘ ORWAREHOUSE.WarehouseCity<>‘ Chicago‘;
2.43 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. Use the NOT IN keyword.（写一个SQL语句显示SKU,SKU_Description,WarehouseID,
WarehouseCity,WarehouseState所有项目不存储在亚特兰大,班戈,或 芝加哥仓库。使用关键字。）
SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState
FROM INVENTORY, WAREHOUSE
WHERE WarehouseCity NOT IN (?Atlanta‘, ‘Chicago‘, ‘Bangor‘);
2.44 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. Donot be concerned with removing leading or trailing blanks.（写一个SQL语句产生单个列称为ItemLocation结合SKU_Description,短语―在一个仓库‖,和WarehouseCity。做不关心删除前导或尾随空格。）
SELECT DISTINCT RTRIM (SKU_Description)+‘is in a warehouse‘ + RTRIM (WarehouseCity)
FROM INVENTORY, WAREHOUSE;
warehouseID是哪个表里面的啊
2.45 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 subquery.（写一个SQL语句显示SKU,SKU_Description,WarehouseID物品储存在一个仓库管理的露西尔·史密斯。使用子查询。）
SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE WarehouseID IN
(SELECT WarehouseID FROM WAREHOUSE
WHERE Manager=‘Lucille Smith‘);
2.46 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for allitems stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN ON syntax.（写一个SQL语句显示SKU,SKU_Description,WarehouseID 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接,但不使用连接 在语法上。）
SELECT SKU,SKU_Description,WAREHOUSE.WarehouseID(不要前缀？) FROM INVENTORY,WAREHOUSE
WHERE INVENTORY.WarehouseID=WAREHOUSE. WarehouseID AND WAREHOUSE. Manager=‘Lucille Smith‘;
2.47 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.（写一个SQL语句显示SKU,SKU_Description,WarehouseID 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接使用连接的语法。） SELECT INVENTORY.SKU ,INVENTORY.SKU_Description ,WAERHOUSE. WarehouseID
FROM INVENTORY JOIN WAREHOUSE
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID WHERE WAREHOUSE.Manager= ?Lucille Smith‘;
2.48 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.（写一个SQL语句显示WarehouseID和平均QuantityOnHand 物品储存在一个仓库管理的露西尔·史密斯。使用子查询。） SELECT WarehouseID, AVG(QuantityOnHand) FROM INVENTORY WHERE WarehouseID IN
(SELECT WarehouseID FROM WAREHOUSE
WHERE Manager=‘ Lucille Smith‘) GROUP BY WarehouseID;
2.49 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 JOINON syntax.（写一个SQL语句显示WarehouseID和平均QuantityOnHand 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接,但不使用连接 在语法上。）
SELECT INVENTORY. WarehouseID, AVG(INVENTORY. QuantityOnHand) FROM INVENTORY ,WAREHOUSE
WHERE INVENTORY. WarehouseID= WAREHOUSE. WarehouseID AND WAREHOUSE. Manager=‘ Lucille Smith‘ GROUP BY WarehouseID;
2.50 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 using JOIN ON syntax.（写一个SQL语句显示WarehouseID和平均QuantityOnHand物品储存在一个仓库管理的露西尔·史密斯。使用一个连接使用连接的语法。）
SELECT WarehouseID, AVG(QuantityOnHand) FROM INVENTORY JOIN WAREHOUSE
ONINVENTORY.WarehouseID=WAREHOUSE.WarehouseID
WHERE WAREHOUSE.Manager=‘ Lucille Smith‘;
2.51Write 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 QuantityOnHandas TotalItemsOnHand.(写一个SQL语句显示WarehouseID,QuantityOnOrder的总和QuantityOnHand的总和,按WarehouseID和QuantityOnOrder分组。的名字的总和QuantityOnOrderTotalItemsOnOrder和QuantityOnHand的总和TotalItemsOnHand。)
SELECT WarehouseID, SUM(QuantityOnOrder) AS TotalItemsOnOrder, SUM(QuantityOnHand) AS TotalItemsOnHand FROM INVENTORY
GROUP BYWarehouseID, QuantityOnOrder;
2.52 Write 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. (写一个SQL语句显示WarehouseID,WarehouseCity,WarehouseState,manager,SKU,SKU_Description,QuantityOnHand所有项目经理露西尔·史密斯。使用一个连接。)
SELECT WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, QuantityOnHand FROM INVENTORY, WAREHOUSE WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.manager=‘ Lucille Smith;‘
2.53 Explain why you cannot use a subquery in your answer to Review Question 2.51.(解释为什么你不能使用子查询2.51审查问题的答案。)
in a query that contains a subquery, only data from fields in the table used in the top-level query can be included in the SELECT statement. If data from fields from other tables are also needed, a join must be used. In question 2.51 we needed to display WAREHOUSE.Manager but INVENTORY would have been the table in the top-level query. Therefore, we had to use a join.
2.54 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.(子查询会创建一个新表,包含下一个查询所需的信息。但连接只使用存在信息在最近的表。)
子查询就是查询中又嵌套的查询,嵌套的级数随各数据库厂商的设定而有所不同,一般最大嵌套数不超过15级,实际应用中,一般不要超过2级,否则代码难以理解.一般来说,所有嵌套
子查询都可改写为非嵌套的查询,但是这样将导致代码量增大.子查询就如递归函数一样,有时侯使用起来能达到事半功倍之效,只是其执行效率同样较低,有时用自身连接可代替某些子查询,另外,某些相关子查询也可改写成非相关子查询
表连接都可以用子查询，但不是所有子查询都能用表连接替换，子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件，而表连接更适合与查看多表的数据
子查询不一定需要两个表有关联字段，而连接查询必须有字段关联（所谓的主外键关系）
2.55 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. Run this statement.(编写一个SQL语句加入仓库和库存和包括所有行仓库在你的回答,不管他们是否有库存。运行这种说法。)
SELECT *
FROM WAREHOUSE LEFT OUTER JOIN INWENTORY //不管右边取值是否为空
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID
第三章：
3.1 Name three sources for databases.（三个来源数据库名称。）
(1) Existing data, (2) the development of new information systems, and (3) the redesign of existingdatabases
3.2 What is the basic premise of this and the next chapter?（的基本前提是什么,下一章吗?）
The basic premise is that we have received one of more tables of data from some source that need to beincorporated into a new database
3.3 Explain what is wrong with the table in Figure 3-2.（解释什么是错误的与表如图
3 - 2所示。）
PRODUCT_BUYER, the table in Figure 3-2, contains a multivalued dependency.Specifically, it containsthe field CollegeMajor, which may have more than one value for each Buyer Name.
3.4 Define each of the terms listed in Figure 3-3.（图3 - 3中列出的每一个术语定义。）
1）Relation:A table-like structure of rows and columns, where the rowsstore data about an entity and the columns store data aboutthe attributes of that entity.Each column has a uniquename, and all values in a column are for the same attribute.The cells in the table can only hold a single value.Theorder of the rows doesn‘t matter, and neither does the orderof the columns.The data in each row as a whole must beunique 2）Functional dependency:A relationship between attributes in a relation where thevalue (or values) of one (or more) attributes determines thevalue (or values) of another attribute (or set of attributes).For example, the functional dependency A?B expressesthe fact that if we know a value of A we will always knowthe corresponding value of B
3)Determinant:The attribute or set of attributes that functionally determinethe value of another attribute or set of attributes.The ―left-hand side‖ of a functional dependency. For example, in thefunctional dependency A?B, A is the determinant
4)Candidate key:An attribute or set of attributes that uniquely determines thevalues of all the other attributes for a row in a relation
3)Composite key :A key that contains two or more attributes.
2)Primary key:The candidate key selected to be the ―official‖ key of arelationship 3)Surrogate key:A column of artificial data added to a relation to serve asthe primary key
4)Foreginkey:IThe attribute (or set of attributes) that is a primary key inone table that is then placed in a second table to form arelationship to the first table by storing linking values.Theterm foreign key refers to the attribute or set of attributes inthe second table.
5)Referenttial integrity constraint:A value constraint of a foreign key that states that no valuecan be placed in the foreign key unless it already exists as aprimary key value in the linked table
6)Normal form:One category in a set of categories (―normal forms‖) usedto describe relations according to the type of anomalies thatcan occur in the relations
7)Multivalued dependency:In contrast to the functional dependency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependencyis a special case of tuple-generating dependency. The multivalued dependencyplays a role in the 4NF database normalization
3.5 Describe the characteristics of a table that make it a relation. Define the term domain
and explain the significance of the domain integrity constraint to a relation. ANSWER：characteristics :
1、Rows contain data about an entity.(实体)
2、Columns(列) contain data about attributes(属性) of the entities. 3、All entities in a column are of the same kind. 4、Each column has a unique name. 5.Cells of the table hold a single value. 6.The order of the columns is unimportant. 7.The order of the rows is unimportant. 8.No two rows may be identical(重复的).
Domain（域）：The range and value type of attributes are called domains
Describing two-dimensional table, each column value type and scope of a field or attribute data file.描述二维表中每一列属性或数据文件的某一字段的取值类型和范围
the significance of the domain integrity constraint：Domain integrity is a constraint for a specific relational database, which ensures that some of the columns in the table can not enter an invalid value.
Domain integrity means the integrity of the column's range. Such as data type, format, value range, and so whether to allow null values.
Domain integrity limit value for certain properties that appear in the property is limited to a finite set.
域完整性是针对某一具体关系数据库的约束条件，它保证表中某些列不能输入无效的值。
域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。
域完整性限制了某些属性中出现的值，把属性限制在一个有限的集合中。例如，如果属性类型是整数，那么它就不能是101.5或任何非整数。
3.6 Give an example of two tables that are not relations. ANSWER：
The table in Figure 3-7 is not a relation, because the entries for employees Caruthers and Caldera require a particular row arrangement. If the rows in this table were rearranged, we would not know which employee has the indicated Fax and Home numbers.
3.7 Suppose that two columns in two different tables have the same column name. What
convention is used to give each a unique name?
ANSWER：For example, supposing we have two different tables named T1 and T2. The same column name of them is ―number‖. So we can name them as ―T1.number‖and ―T2.number‖as a unique name for each.
3.8 Must all the values in the same column of a relation have the same length? ANSWER：Even though every cell of a relation must have a single value, this does not mean that all values must have the same length. 3.8必须所有的值在同一列的关系有相同的长度吗？
答：即使一个关系的每一个单元格必须有一个单一的值，这并不意味着所有的值必须有相同的长度。
3.9 Explain the three different sets of terms used to describe tables, columns, and rows.
ANSWER：① As defined by Codd, the column of a relation is called attribute, and the rows of a relation is called tuple. The most of practitioners mean relation when they say table.
②Some practitioners use the terms file, field, and record for the terms table, column, and row respectively. These terms arose from traditional data processing and they are common in connection with legacy systems.
3.9解释用于描述表、列和行的三种不同的术语集。
答：①由Codd的定义，关系中的列称为属性，和关系中的行称为元组。当他们说表时，大多数从业者的平均关系。
②一些从业者使用条款文件，现场，和记录的术语表，列和行分别。这些术语源于传统的数据处理，它们在与传统系统的连接中是常见的。
3.10 Explain the difference between functional dependencies that arise from equations and those that do not.
ANSWER：Functional dependencies that arise from equations can let us compute the actual value. Functional dependencies that do not arise from equations tells us the character of the relationship between the different objects.
3.11 Explain the intuitive meaning of the functional dependency. PartNumber→PartWeight
ANSWER：PartWeight depends on取决于PartNumber, PartNumber determines 决定PartWeight.
3.12 Explain the following statement: ―The only reason for having relations is to store instances of functional dependencies.‖
ANSWER：ObjectColor → (Weight, Shape)
For example, if there were a formula by which we could take ObjectColor and somehow compute Weight and Shape, then we would not need the table. We would just make the computation.
3.13 Explain the meaning of the expression: (FirstName, LastName) →Phone
ANSWER：The composite determinant (FirstName, LastName) determines Phone
together, Phone depends on the composite determinant (FirstName, LastName).
3.14 What is a composite determinant?
ANSWER： Determinants that have more than one attribute are called composite determinants.
3.15 If (A, B) →C , then can we also say that A→ C?
ANSWER：if (A, B) →C, then, in general, neither A→C nor B→C.
3.16 If A →(B, C), then can we also say that A → B? ANSWER：If A → (B, C), then A → B and A →C.
3.17 For the SKU_DATA table in Figure 3-1 ?, explain why Buyer determines Department, but Department does not determine Buyer.
3.18 For the SKU_DATA table in Figure 3-1？, explain why: SKU_Description→(SKU, Department, Buyer).
3.19 If it is true that PartNumber S PartWeightdoes that mean that PartNumber will be unique in a relation?
ANSWER：No,maybe there are other objects to determine the PartWeight.
3.20 Under what conditions will a determinant be unique in a relation?
ANSWER：A determinant is unique in a relation only if it determines every other column in the relation.
3.21 What is the best test for determining whether a determinant is unique?
ANSWER：The best strategies are to think about the nature of the business activity from which the data arise and to ask the users.
3.21 What is the best test for determining whether a determinant is unique?(确定一个行列式是否唯一的最佳测试是什么)
Answer:The best strategies are to think about the nature of the business activity from which the date arise and to ask the users.
3.22 What is a composite key?(什么是复合关键字)
Answer:Keys that have two columns or more are called composite keys.
3.23 What is a candidate key?(什么是候选关键字)
Answer:A candidate key is a determinant that determines all of the other columns in a relation.
3.24 What is a primary key?(什么是主关键字) Explain the significance of the entity integrity constraint to a
primary key.(解释实体完整性约束的意义)
Answer:When designing a database,one of the candidate keys is selected to be the primary key.The DBMS will use it as its primary means for finding rows in a table.
3.25 Explain the difference between a candidate key and a primary key.(解释一下候选关键字和主关键字的不同)
Answer:A table has only one primary key,but it can has many candidate keys.
3.26 What is a surrogate key?(什么是代理键)
Answer:A surrogate key is an artificial column that is added to a table to serve as the primary key.
3.27 Where does the value of a surrogate key come from?(代理键的值来自哪里) Answer:The DBMS assigns a unique value to a surrogate key when the row is created.The assigned value never changes.
3.28 When would you use a surrogate key?(你什么时候会用到代理键) Answer:Surrogate keys are used when the primary key is large and unwieldy.
3.29 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.
3.30 The term domestic key is not used. If it were used, however, what do you think it would
mean?(国内关键是不被使用了，如果被使用，你觉得它是什么意思)
Answer:A domestic key is a column or composite of columns that is only the primary
key of a table in which it appears.
3.31 What is a normal form?(什么是范式)
Answer:Normal form is a collection of relational patterns that conform to a certain level of a certain level.
3.32 Illustrate deletion, modification, and insertion anomalies on the STUDENT_ACTIVITY
relation in Figure 3-24.(说明一下在STUDENT_ACTIVITY关系中删除，修改，插入异常)——这个题不会啊！！！！！)
3.33 Explain why duplicated data lead to data integrity problems.(解释为什么重复数据导致数据库完整性的问题)
Answer:Any table that has duplicated data is susceptible to update anomalies.
3.34 What relations are in 1NF?(在1NF的关系是什么)
Answer:Any table meeting the conditions of all the characteristics of relations is a relation in 1NF.
3.35 Which normal forms are concerned with functional dependencies?(哪一个范式被认为是函数依赖)
Answer:BCNF,3NF and 2NF are all concerned with functional dependencies.
3.36 What conditions are required for a relation to be in 2NF?(一个关系是2NF需要具备哪些条件)
Answer:It requires a database table or a row for each instance must be uniquely distincted.To achieve the requirement,it usually requires a column to store the unique identification for each instance.
3.37 What conditions are required for a relation to be in 3NF?(一个关系是3NF需要
哪些条件)
Answer:It requires a database table does not include non-primary key information is already contained in other tables.
3.38 What conditions are required for a relation to be in BCNF?(一个关系是BCNF需要哪些条件)
Answer:(1)All non-primary attributes are totally dependent function keys for each candidate.
(2)All the main attributes are also completely functional dependency for each candidate key that does not contain them.
(3)It does not have any property full functionally dependent on any one set of properties of non-candidate keys.
3.39 If a relation is in BCNF, what can we say about it with regard to (关于)2NF and 3NF?
Ans: If a relation is in BCNF, we can say it is also in 2NF and 3NF.
3.40 What normal form is concerned with multivalued dependencies? Ans: 4NF is concerned with multivalued dependencies(多值依赖)
3.41 What is the premise of Fagin‘s work on DK/NF?
Ans: He supposed that a relation in DK/NF has no modifictionanomalies.(修改异常)
3.42 Summarize the three categories(类别) of normalization theory.
Ans:The first category is concerned with anomalies that are caused by functional dependencies.
The second category is concerned with anomalies that are caused by multivalued dependencies.
The third category is concerned with anomalies that are caused by data constraints and odd conditions.
3.43 In general, how can you transform a relation not in BCNF into ones that are in BCNF?
Ans:
3.44 What is a referential integrity constraint? Define the term, and give an example of its use. Are null values allowed in foreign key columns with a referential integrity constrain? How does the referential integrity constrain contribute to databaseintegrity(如何引用完整性并有助于数据库完整性约束)?
Ans:(1)Referential integrity constraint is a set of integrity rules, which defines the semantic constraints to be observed by the data model. 数据完整性约束是一组完整性规则的集合，它定义了数据模型必须遵守的语义约束 (2)null values is not allowed in foreign key columns with a referential integrity constrain (3)
3.45 Explain the role of referential integrity constraints in normalization.
Ans:To ensure that the data stored in the database is correct, effective and compatible. 以确保数据库中存储的数据正确、有效、相容
3.46 Why is an un-normalized relation like a paragraph with multiple themes? Ans:Because normalized relation like a paragraph with only one themes.
3.47 In normalization example 3, why is the EXTENDED_PRICE relation ―silly‖? (没有例子，完全搞不懂在说啥)
3.48 In normalization example 4, under what conditions is the functional dependency (StudentID, Activity) →ActivityFee more accurate(精确) than Activity→ActivityFee (依然没有例子)
3.49 If a determinant is part of a candidate key, is that good enough for BCNF? Ans:If a determinant is part of a candidate key, that is not good enough for BCNF.
3.50 In normalization example 5, why are the following two tables not correct? DEPARTMENT (Department, DeptBudgetCode, Buyer) SKU_DATA_4 (SKU, SKU_Description, Department) (依然没有例子)
3.51 How does a multivalued dependency differ from a functional dependency?
Ans:In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database；a multivalued dependency is a full constraint between two sets of attributes in a relation.
3.52 Consider the relation:
PERSON (Name, Sibling, ShoeSize)
Assume that the following functional dependencies exist: Name →→Sibling Name →ShoeSize
Describe deletion, modification, and insertion anomalies for this relation.
3.53Place the PERSON relation in Review Question 3.52 into 4NF. （找不到题目）
3.54 Consider the relation:
PERSON_2 (Name, Sibling, ShoeSize, Hobby)
Assume that the following functional dependencies exist: Name →→Sibling Name →ShoeSize Name →→Hobby
Describe deletion, modification, and insertion anomalies(异常) for this relation.
3.55 Place the PERSON_2 relation in Review Question 3.54 into 4NF. （找不到题目）
3.56 What is 5NF?
Ans:There is a fifth normal form (5NF), also known as Project-Join Normal Form (PJ/NF), which involves an anomaly(异常) where a table can be split apart but not correctly joined back together.
3.57 How do the conditions for DK/NF correspond to(相当于) the conditions for BCNF?
Ans: Basically, DK/NF requires that all the constraints on the data values be logical implications(含义) of the definitions of domains(域) and keys. This can be restated as follows: Every determinant of a functional dependency must be a candidate key. This, of course, is simply our definition of BCNF, and, for practical purposes, relations in BCNF are in DK/NF as well.