武汉理工大学 数据库系统原理总复习题（完整版含答案）

武汉理工大学 数据库系统原理总复习题（完整版含答案）

1. Questions

1.1 What is the purpose of a database?

ANSWER：The purpose of a database is to help people track of things.

1.2 What is the most commonly used type of database?

ANSWER: the most commonly used type of database is the relational database.

1.7 Define the terms data and information. Explain how the two terms differ.

ANSWER: Data are recorded facts and numbers. we can now 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.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.12 What is the purpose of the largest databases at e-commerce companies such as Amazon.com?

ANSWER: The largest databases are those that track customer browser behavior.（用来记录用户的浏览行为的。） 1.13 How do the e-commerce companies use these databases? .

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 dashboards 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. What are the functions of application programs?

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, and 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

1.18 What is Structured Query Language (SQL), and why is it important?

ANSWER: Structured Query Language (SQL) is an internationally recognized standard language. Because it can be understood by all commercial DBMS products, in database processing and the fact that database applications typically send SQL statements to the DBMS for processing. 1.19 What does DBMS stand for?

ANSWER: The database management system. 1.20 What are the functions of the DBMS?

ANSWER: It can be used to create, process, and administer the database. 1.21 Name three vendors of DBMS products.

ANSWER: IBM, Microsoft, Oracle. 1.22 Define the term database.

ANSWER: A database is a self-describing collection of integrated tables. 1.23 Why is a database considered to be self-describing?

ANSWER: 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?

ANSWER: 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?

ANSWER: Because metadata is stored in tables, you can use SQL to query it. Thus, by learning how to write SQL to query user tables, you will also learn how to write SQL to query metadata. 1.26 List the components of a database other than user tables and metadata. ? Tables of user data ? Metadata ? Indexes

? Stored procedures ? Triggers ? Security data

? Backup/recovery data

2

1.27 Is Microsoft Access a DBMS? Why or why not?

ANSWER: No, Microsoft Access is not just a DBMS. Rather, it is a personal database system: a DBMS plus an application generator.

Because 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.37 List several consequences of a poorly designed database.

ANSWER:

-They may require application developers to write overly complex and contrived SQL to get –wanted data.

-they may be difficult to adapt to new and changing requirements. -they may fail in some other way. 1.38 Explain two ways that a database can be designed from existing data.

ANSWER:

The first type of database design involves databases that are constructed from existing Data.

A second way that databases are designed is for the development of new information systems. 1.39 What is a data warehouse? What is a data mart?

ANSWER: The data warehouse and data mart databases store data specifically organized for research and reporting purposes, and these data often are exported to other analytical tools, such as SAS‘s Enterprise Miner, IBM's SPSS Data Modeler, or TIBCO's Spot fire Metrics. 1.40 Describe the general process of designing a database for a new information system.

ANSWER: First, the team creates a data model from the requirements statements and then transforms that data model into a database design.

3

1.41 Explain two ways that databases can be redesigned.

ANSWER:

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.

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.42 What does the term database migration mean?

ANSWER: The process of a database is adapted to new or changing requirements.

1.43

Summarize 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.

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.

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.

4

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.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.50 What were the early objections to the relational model? 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:

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.

5

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 and display the results in descending order of TotalItemsOnHandLT3.

SELECT WarehouseID , SUM (QuantityOnHand) AS TotalItamsOnHandLT3 FROM INVENTORY GROUP BY WarehouseID

ORDER BY TotalItemsOnHandLT3 DESC HAVING SUM(Quantity)<3;

2.38 Write an SQL statement to display the WarehouseID and the sum of QuantityOn- Handgrouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3. Show WarehouseID only for warehouses having fewer than 2 SKUs in their TotalItemesOnHandLT3 and display the results in descending order of TotalItemsOnHandLT3.

SELECT WarehouseID , SUM (QuantityOnHand) AS TotalItamsOnHandLT3, COUNT (SKU)

FROM INVENTORY GROUP BY WarehouseID

ORDER BY TotalItemsOnHandLT3 DESC

HAVING SUM(Quantity)<3 AND COUNT(SKU)<2; 2.39 In your answer to Review Question 2.39, was the WHERE clause or the HAVING clause applied first? Why?

ANSWER: WHERE clause applied first. Because WHERE clause filters the records before GROUP BY clause, but HAVING clause filters the records after GROUP BY clause.

Use both the INVENTORY and WAREHOUSE tables to answer Review Questions 2.40 through 2.52:

2.40 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or Chicago warehouse. Do not use the IN keyword. ANSWER: SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE

WHERE WarehouseCity=‘Atlanta‘ OR WarehouseCity=‘Chicago‘ OR WarehouseCity=‘Bangor‘ ;

16

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

SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE

WHERE WarehouseCity IN (?Atlanta‘, ‘Chicago‘, ‘Bangor‘);

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

SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState

FROM INVENTORY, WAREHOUSE WHERE WarehouseCity= ‘Seattle‘;

2.43 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, Ware-houseCity, 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 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. Do not be concerned with removing leading or trailing blanks.

SELECT DISTINCT RTRIM (SKU_Description)+‘is in a warehouse‘ + RTRIM (WarehouseCity)

FROM INVENTORY, WAREHOUSE;

2.45 Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ?Lucille Smith‘. Use a subquery. SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY, WAREHOUSE WHERE WarehouseID IN

(SELECT WarehouseID FROM WAREHOUSE

WHERE Manager=‘Lucille Smith‘);

17

2.46 Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ?Lucille Smith‘. Use a join.

SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY, WAREHOUSE

WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.Manager= ?Lucille Smith‘;

2.47 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) FROM INVENTORY WHERE WarehouseID IN (SELECT WarehouseID

FROM WAREHOUSE

WHERE 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 join.

SELECT WarehouseID, AVG(QuantityOnHand) FROM INVENTORY

WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.Manager=‘ Lucille Smith‘;

2.49 Write 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; 2.50 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.

SELECT WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, QuantityOnHand FROM INVENTORY, WAREHOUSE WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.manager=‘ Lucille Smith;‘

18

2.52 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.

3.5 Describe the characteristics of a table that make it a relation.

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.

19

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.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.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 Intuitively, what is the meaning of the functional dependency : PartNumber →PartWeight

ANSWER：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：FirstName, and LastName determine Phone. 3.14 What is a composite determinant?

ANSWER：For example ：(Quantity, UnitPrice) → ExtendedPrice the composite determinant is the composite (Quantity, UnitPrice)

20

3.15 If (A, B) →C, then can we also say that A →C? ANSWER：No, neither A nor B will determine C by itself. 3.16 If A → (B, C), then can we also say that A →B? ANSWER：Yes, it is true that A →B and A →C. 3.19 If it is true that―PartNumber → PartWeight‖. Does 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.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? 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.

21

3.29 What is a foreign 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.33 Explain why duplicated data leads to data integrity problems.

ANSWER：Any table that has duplicated data is susceptible to update anomalies. 3.34 What relations are in 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?

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?

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? 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 fully 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?

ANSWER：If a relation is in BCNF, we can say it also in 3NF and 2NF.

22

3.40 What normal form is concerned with multivalued dependencies? ANSWER：4NF is concerned with multivalued dependencies. 3.41 What is the premise of Fagin‘s work on DK/NF?(假设)

ANSWER：He supposed that a relation in DK/NF has no modification anomalies. 3.42 Summarize the three categories of normalization theory.

ANSWER：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?

ANSWER：You can also design (or redesigning) the tables so that every determinant is a candidate key. 3.44 What is a referential integrity constraint? ANSWER：Referential integrity constraint is a set of integrity rules, which defines the semantic constraints to be observed by the data model. 数据完整性约束是一组完整性规则的集合，它定义了数据模型必须遵守的语义约束。 3.45 Explain the role of referential integrity constraints in normalization.（说明参照完整性约束在规范化中的作用）

ANSWER：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? ANSWER：Because normalized relation like a paragraph with only one themes.

23

本文来源：https://www.bwwdw.com/article/c3ot.html