数据库技术试题附答案.pdf

1 True-False Questions（1point*20） 1. The purpose of a database is to help people keep track of things. T 2. The relational model was first proposed in 1970 by E. F. Codd at IBM. T 3. A relation is a three(two)-dimensional table. F 4. An asterisk (*) following the SELECT verb means that all columns are to be obtained. T 5. The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column. T 6. In an E-R model, the three types of maximum cardinality are 1:1, 1:N and N:M. T 7. A null value is an attribute value that has been set to zero. （二者不等价） F 8. A data constraint is a limitation on data values. T 9. In a relational database design, all relationships are expressed by creating a foreign key. T 10. A transaction is a group of alternative database actions from which the database can choose to perform only one of them. F 11. Two-phased locking has a growing phase and a shrinking phase. T 12. “Repeatable Reads“ isolation is the most restrictive level of isolation. F 13. A “database save“ is used to mark the end of a transaction. F 14. To add a NULL column to a table, we simply use the MODIFY TABLE statement. F 15. Deleting tables and relationships is basically a matter of dropping foreign key constraints and then dropping the tables. T 16. The SQL keyword DELETE is used to delete a tables structure. F 17. Joins that show only matching rows from the joined tables in their results are called inner joins. T 18. An SQL virtual table is called a view. 2 T 19. SQL triggers are used for providing default values, validity checking, updating views, and performing referential integrity actions. T 20. Stored procedures have the advantage of greater security, decreased network traffic, SQL optimized by the DBMS compiler, and code sharing. T Multiple Choice Questions（1 point*30） 题号 1 2 3 4 5 6 7 8 9 10 答案 E B C D E A D D E E 题号 11 12 13 14 15 16 17 18 19 20 答案 B A C C A B B C A C 题号 21 22 23 24 25 26 27 28 29 30 答案 A E D B B D E A B D 1. A database stores: e a.) data. b.) relationships. c.) metadata. d.) a and b e.) a, b and c 2. A program whose job is to create, process and administer databases is called the _. b a.) Database Modeling System b.) Database Management System c.) Data Business Model System d.) Relational Model Manager e.) Data Business Management Service 3. A database is considered “self-describing“ because _ . c a.) all the users data is in one place b.) it reduces data duplication c.) it contains a description of its own structure d.) it contains a listing of all the programs that use it e.) All of the above. 4. A very popular development technique used by database professionals for database design is known as _ d. a.) data marts b.) normalization 3 c.) data models d.) entity-relationship data modeling e.) data migration 5. In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected? e a.) EXISTS b.) FROM c.) SELECT d.) SET e.) WHERE 6. In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character? a a.) (underscore) b.) ? (question mark) c.) * (asterisk) d.) % (percent) e.) # (pound) 7. Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter “S“ using standard SQL? d a.) SELECT * FROM EMPLOYEE WHERE Name IN S; b.) SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE S; c.) SELECT * FROM Name WHERE EMPLOYEE LIKE S*; d.) SELECT * FROM EMPLOYEE WHERE Name LIKE S%; e.) None of the above. 8. Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command? d a.) SELECT * FROM EMPLOYEE WHERE Name LIKE Ja%; b.) SELECT COUNT(*) FROM EMPLOYEE WHERE Salary 30000; c.) SELECT COUNT(EmpNo) FROM EMPLOYEE; d.) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary 30000; e.) SELECT HireDate, COUNT(*) 4 FROM EMPLOYEE GROUP BY HireDate; 9. A relation _ . e a.) has rows containing data about an entity 行包含实体 b.) has columns containing data about attributes of the entity 列包含实体的属性 c.) has cells that hold only a single value 每个单元只有一个值 d.) has no two identical rows 没有两个相同的行 e.) All of the above. 10. Which of the following is true about the functional dependency A (X, Y)? e a.) X is functionally dependent on A. X 功能依赖 A b.) A determines Y. c.) A is a determinant. d.) X and Y are functionally dependent on A. e.) All of the above. 11. A relation is in fourth normal form if it is in BCNF and it has no _ . b a.) transitive dependencies b.) multivalued dependencies 如果一个关系在 BCNF并且没有多值依 赖他就是第四范式 c.) partial dependencies d.) deletion dependencies e.) referential integrity conflicts 12. The advantages of normalization include _ . a a.) the elimination of modification anomalies 消除修改异常 b.) the elimination of duplicated data c.) more complex SQL for multitable subqueries and joins d.) a and b e.) a, b and c 13. To check for null values in a column in a table, use the SQL phrase _ . c a.) IS b.) IS NOT c.) IS NULL d.) COUNT(IS NOT) e.) COUNT(IS NULL) 14. A composite attribute is an attribute that _c_ . a.) is multivalued b.) describes a characteristic of the relationship c.) consists of a group of attributes d.) is calculated at run-time e.) is an identifier 15. Maximum cardinality refers to _a_ . a.) the most instances of one entity class that can be involved in a relationship instance with another entity class b.) the minimum number of entity classes involved in a relationship 5 c.) whether or not an instance of one entity class is required to be related to an instance of another entity class d.) whether or not an entity is a weak entity e.) None of the above. 16. An entity whose existence depends on the presence of another entity, but whose identifier does not include the identifier of the other entity is a(n) _b_ . a.) strong entity b.) weak entity c.) ID-dependent entity d.) a and c e.) b and c 17. You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown below, and are asked to draw the relationship between them. If a given customer can place only one order and a given order can be placed by at most one customer, which of the following should be indicated in the relationship symbol between the two entities? b a.) 0:1 b.) 1:1 c.) 1:N d.) N:1 e.) N:M 18. When an entity has a relationship to itself, we have a (n) _c_ . a.) supertype/subtype relationship b.) archetype/instance relationship c.) recursive relationship d.) a or c e.) b or c 19. Each attribute of an entity becomes a _a_ of a table. a.) column b.) primary key c.) foreign key d.) alternate key e.) b or d 20. Which of the following is not true about surrogate keys? c a.) They are identifiers that are supplied by the system, not the users. b.) They have no meaning to the users. c.) They are nonunique within a table. d.) They can be problematic when combining databases. e.) The DBMS will not allow their values to be changed. 21. To which of the following actions are referential integrity constraints not applied? a ORDER CUSTOMER 6 a.) create b.) insert c.) modify d.) delete e.) referential integrity constraints are applied to all of the listed actions 22. Which SQL keyword is used to impose restrictions on a table, data or relationship? e a.) SET b.) CREATE c.) SELECT d.) ALTER e.) CONSTRAINT 23. SQL views are constructed from d . a.) CREATE statements b.) INSERT statements c.) UPDATE statements d.) SELECT statements e.) VIEW statements 24. In the SQL statements SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1; the “C1“ is called a(n) _b_ . a.) term b.) alias c.) convention d.) phrase e.) label 25. If a DEFAULT constraint is included when a new column is added to a table, the default value is applied to b . a.) all existing rows at the time the column is added b.) all new rows c.) all new rows but only after the UPDATE command is issued d.) a and b e.) a and c 26. When dropping tables and relationships, which of the following steps are included in the process the order of the steps listed below is not relevant, only the steps themselves? d a.) Drop the foreign key constraints from the tables b.) Drop the tables c.) Drop the primary key constraints from the tables d.) a and b e.) a, b and c 7 27. Which of the following is not a database administration responsibility of a DBA? e a.) Managing the database structure b.) Managing data activity c.) Managing the DBMS d.) Maintaining the data repository e.) All of the above are database administration responsibilities of a DBA. 28. Measures that are taken to prevent one users work from inappropriately influencing another users work are called: a a.) concurrency control. b.) checkpoint. c.) database recovery. d.) database logging. e.) interleaving. 29. The situation that occurs when two users are each waiting for a resource that the other person has locked is known as a(n): b a.) lost update problem. b.) deadlock. c.) inconsistent read problem. d.) inconsistent write problem. e.) checkpoint. 30. Locks that are placed assuming that a conflict will not occur are called: d a.) dynamic. b.) explicit. c.) implicit. d.) optimistic. e.) pessimistic. Fill in the Blank Questions（1 point*10） 1. SQL stands for structured query language . 2. To remove duplicate rows from the result of a query, specify the SQL qualifier DISTINCT . 3. If a table is a relation then it is in 1 NF. 4. To limit the number of rows retrieved from a table, use the SQL keyword TOP . 5. By default, the identifier of the entity becomes the primary key of the corresponding table. 6. Rows in a table can be changed by using the UPDATE statement. 7. To add a NULL column to a table, we use the ALTER TABLE statement. 8. A(n) transaction is a series of actions to be taken on the database such that either all of them are performed successfully or none of them is performed at all. 9. A(n) exclusive lock locks the item from access of any 8 type. 10. A(n) static cursor processes a snapshot of the relation that was taken when the cursor was opened. Essay Questions（6 points*5） 1. Briefly describe subqueries and joins. Explain when each is not an acceptable alternative for the other. Subqueries and joins are both methods for retrieving data from multiple tables. Subqueries involve nesting one SELECT statement within another. The nested SELECT is used as part of a condition in the WHERE clause of the first SELECT statement. The nested SELECT statement can return a set of records from one table, which are then used in a logical operator within the parent SELECT query. A join combines records from each table into concatenated records containing the fields of both tables. The records are concatenated based on matching values in similar columns in the two tables. Subqueries cannot be used in situations where the results to be displayed include attributes from more than one table. Joins cannot be used as an alternative to a correlated subquery. 2. Briefly describe the process of converting an extended E-R model into a relational database design. To convert an extended E-R model into a relational database design, first tables and columns must be defined for each entity attribute. The attributes of the entity are represented as the columns of the table. Primary keys are then selected for each table, after which the relationships between the entities are represented through the creation of foreign keys. Next, constraints are specified. Finally the normalization is verified. 3. The following database will be used in this question: GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/1999 734 Smith 02/03/2000 345 Chen 01/25/1998 434 Johnson 11/23/1998 CUSTOMER CustNo CustName Balance SalesRepNo 9 Explain the use of the SQL statement CREATE TABLE. Do NOT discuss the ALTER statement in your answer, but DO include an example based on the SALESREP table in the General Sales database. The SQL statement CREATE TABLE forms that basis for all SQL table construction. CREATE TABLE is used to name tables and specify their structure, including column names and their associated data types. In addition, CREATE TABLE can be used to define primary keys, foreign keys, and to specify constraints on tables, columns and column values. There are five SQL keywords that specify these constraints: PRIMARY KEY, FOREIGN KEY, NULL/NOT NULL, UNIQUE and CHECK. PRIMARY KEY and FOREIGN KEY are used to specify keys. NULL and NOT NULL specify whether nulls are allowed as values in a column. UNIQUE specifies whether the column values have to be unique. CHECK is not discussed in this chapter. The SQL keyword CONSTRAINT may be used to specify some of the table constraints. For example, to create the SALESREP table for the General Sales database, the following SQL stateme