ssd7 数据库系统2010年--大学期中考试试题及答案

期中考试试题（闭卷）

（2010-20011学年第1学期）

题    号

一

二

三

四

五

六

七

八

九

十

得    分

阅卷教师

阅卷时间

总 成 绩

100

Problem1. Multiple Choices. (2points×15) 1．   If relation R has n tuples and relation S has m tuples, the minimum number of tupes that R∪S can contain is __a__. (a) max (n,m)        (b) min (n,m)         (c) |n-m|         (d) n+m 2．   The term physical data independence refers to the ability to change__b___. (a)      the conceptual schema without changing the application programs (b)      the physical layout of the data without changing the external schemas, the conceptual schemas, or the application programs (c)        the application programs without changing the conceptual schema (d)      the data without physically relocating the tables 3．   For two tables to be union compatible, corresponding columns from each table should have which of the following? a (a)        the same domain  (b) the same name  (c) different domains  (d) different names 4．   The result of a set difference operation r - s will be  c (a)        tuples in r after deducting their values by the corresponding values in the tuples in s (b)       tuples in s after deducting their values by the corresponding values in the tuples in r (c)       those tuples that are in s but not in r       (d) those tuples that are in r but not in s 5．   Which of the following is true about primary keys and foreign keys holding NULL value? d (a)        A primary key cannot hold a NULL value and a foreign key cannot hold a NULL value (b)       A primary key can hold a NULL value and a foreign key can hold a NULL value (c)       A primary key can hold a NULL value and a foreign key cannot hold a NULL value (d)       A primary key cannot hold a NULL value and a foreign key can hold a NULL value 6．   What does a selection operation do? b (a)        It extends the number of columns in a table.  (b) It selects rows from a table. (c) It extends the number of rows in a table.     (d) It selects columns from a table. 7．   An insertion operation will __a__ if the insertion violates the foreign key constraint. (a)        fail  (b) succeed with warning  (c) succeed without warning  (d) crash the system 8．   In the relational model, which of the following is true about the data type of a column? b (a)        It need not be atomic and it cannot be an abstract data type. (b)       It must be atomic and it cannot be an abstract data type. (c)       It must be atomic and it can be an abstract data type. (d)       It need not be atomic and it can be an abstract data type. 9．   What is an alternate key? c (a)        A key that will become the primary key when the primary key is deleted (b)       The key to use when the primary key does not work (c)       Any key that is not a primary key (d)       A key that was added to the table after the table was designed 10．                            Which of the following is true about the number of primary keys and alternate keys with respect to a table? a (a)        There can be only one primary key, but many alternate keys. (b)       There can be many primary keys, but only one alternate key. (c)       There can be only one primary key and only one alternate key. (d)       There can be many primary keys and many alternate keys. 11．                            Which of the following is true about the physical storage of tables defined by views? a (a)        There is no extra physical storage needed to store tables that a view defines. (b)       Extra physical storage is always needed to store tables that a view defines. (c)       Extra physical storage is needed for storing the tables defined by views, only if views define additional non-existing columns. (d)       Extra physical storage is needed to store tables that a view defines, only when rows are inserted into the view. 12．                            In SQL, the results of a _____ statement can be used to process a _____ statement.b (a)        DELETE, INSERT  (b) SELECT, INSERT  (c) INSERT, SELECT  (d) INSERT, DELETE 13．                            In a transaction, COMMIT specifies that c (a)        only the inserts and deletes of a transaction are about to be made permanent in the database (b)       only the inserts and deletes of a transaction are to be made permanent in the database (c)       all updates (including inserts, deletes, modifications) of a transaction are about to be made permanent in the database (d)       all updates (including inserts, deletes, modifications) of a transaction are to be made permanent in the database 14．                            In contrast to _____ tables, a view refers to _____. a (a) base, a virtual table                           (b) non-empty, empty tables (b) non-empty, empty tables                      (d) virtual, base tables 15．                            Which of the following SQL commands can be used to destroy and remove a table from the schema? a (a)        DROP TABLE  (b) DELETE TABLE  (c) DESTROY TABLE  d) REMOVE TABLE     Problem2. Consider the following Mail Order database: CUSTOMER CId Name Address Zip 11 George 23 Main St. 15218 22 Barbara 3 Walnut St. 15217 33 Waquim 82 Straton Av. 15280   PART Pno Pname UnitPrice 150 X 20.00 152 Y 33.00 153 Z 4.00 155 V 15.00 162 W 25.00   ORDERS Ono CId Received Shipped 20 11 10-Dec-97 12-Dec-97 21 11 13-Feb-98 15-Feb-98 22 22 26-Feb-98 NULL   INVOICE Ono Pno Qty BilledPrice 20 152 1 33.00 20 155 4 60.00 20 162 1 25.00 22 152 3 99.00 21 150 1 20.00 21 152 2 66.00 21 153 3 12.00 21 155 4 60.00 21 162 1 25.00 1.             Write both ralational algebra expressions and SQL statements (in SQL92) to perform the follwing queries. (1)   Get the part number of parts that cost between 10 and 25 dollars. Select pno From Part Where UnitPrice between 10 and 25; (2)   List all the parts’name and price that order 20 contains. Select Pname From part,orders,invoice Where orders.ono=Invoice.ono and part.Pno=Invoice.Pno and invoice.ono=20; (3)   List all orders that contains parts that order 20 contains. Select * from oders o where not exists ((select pno from invoice where Ono=20)  Except  (select pno from invoice i where o.Ono=i.Ono)); 2.        In the table ORDERS above, use alter table statement to specify the following constraints: Received date cannot be undefined and that the Shipped date, if it is not NULL, should be greater than the Received date. Alter table orders alter column Received set NOT NULL; Alter table orders add constraint Order_ck check(Received < Shipped); Problem 3. Consider the following relational schema describing an atlas(地图集) : continent (name, area) country (name, continent, population) province (name, country, capital, population) city (name, country, province) Write SQL statements in SQL92 to perform the following instructions. (1)   List the name of the countries of the continent whose name begins these letters:’as’ in alphbetical order. Select name from country where continent like ‘as%’ order by name; (2)   Give the number of cities for each country in the continent whose name is ‘asia’ in ascending order. Select country.name, count(*) from country, city where continent=’asia’ and city.country=country.name group by country.name order by count(*); (3)   List the name of all countries with more than ten cities. Select country.name from country, city where country.name=city.country group by country.name having count(*) >10; (4)   Give the name of the country that have the most cities. Select country from city group by country having count(*) >= all (select count(*) from city group by country); (5)   List the countries’ name in the continent ‘asia’ that have a lager population than any of the countries of ‘europe’. Select name from country where continent=’asia’ and population >=all (select population from country where continent=’europe’); (6)   For each country , list its name and the number of its cities.  Select country.name, count(*)  From country,city  Where country.name=city.country  Group by country.name;