上海财经大学数据库系统课程考试卷.docVIP

上海财经大学数据库系统课程考试卷（A）20052006学年 第二学期姓名 学号 班级 得分 1. True or False. (20 points)1). External/Conceptual Mapping is the key to physical data independence. 2). DA is the person who provides the necessary technical support for implementing those decisions. 3). It is the job of the DML processor to choose an efficient way to implement users request.4). Relational data model represents the data in a database as a collection of tables.5). Indexes allow the database management system to directly access the rows of the table on which the index has been defined.6). Since the logical data structures are intended for the application programmers, the database designer is not involved in their development. 7). Views are only descriptions of data. They are not real tables.8). Denormalization causes the redundant storage of information. 9). Horizontal splitting of a relational variable always creates relational variables for different primary key ranges of the original relational variable. 10). For an unordered retrieval request, different executions of the request may return the retrieved rows in a different sequence. 11). A relationship type can again be the source or target of a relationship type. 12). A business relationship type can always be translated into a relationship type of the entity-relationship model for the application domain.13). The updating of redundant information for violations of the Third Normal Form cannot be controlled by triggers.14). SQL does not permit users to define their own types, columns must be defined in terms of built-in types only. 15). Once a transaction commits, its updates may not survive in the database when there is a subsequent system crash. 16). Any transaction which was in progress at the time of the system failure must be redo at restart time. 17). The checkpoint record gives a list of all transactions that were in progress at the time the checkpoint was taken. 18). If transaction A holds an S lock on tuple t, then a request from some distinct transaction B for a X lock on t will be denied. 19). Breaking the deadlock involves choosing one of the deadlocked transactions and commit it. 20). If all transactions obey the two-phase locking protocol, all possible interleaved schedules are serializable. 2. Choose the right answer of the following exercises. (30 points)1). Given the tables:CustomerAccountCustomerNoAccountNoAccountNoBalanceC001A101A101100C002A105A105170C002A107The statement: SELECT * FROM Customer, Accountwill return how many rows? A. 2 B. 3 C. 5 D. 6 2). Given the tables:EMPINCOMEEmpidNameEmpidMonthNoIncome1JOE1150002BOB1255003SMITH2110000228000INCOME was defined as follows:CREATE TABLE INCOME (Empid CHAR(3), MonthNo CHAR(3), Income DECIMAL(6,2),CONSTRAINT const1 FOREIGN KEY (Empid)REFERENCES EMP(Empid) ON DELETE CASCADE); How many rows would be deleted from INCOME if the following command is issued ?DELETE FROM EMP WHERE Empid = 2;A. 0 B. 1 C. 2 D. 3 3). Given the following tables:StudSCSnoSnameS#C#Grade1Smith11802Jones25903Smith261004BOB7130The following results are desired:SnoSnameS#C#Grade1Smith11802Jones25902Jones261003Smith4BOBWhich of the following joins will yield the desired results?A. SELECT * FROM Stud, SC WHERE Stud.Sno=SC.S# B. SELECT * FROM Stud, SC WHERE Stud.Sno=SC.S#(+)C. SELECT * FROM Stud , SC WHERE Stud.Sno(+)=SC.S#D. SELECT * FROM Stud , SC WHERE Stud.Sno(+)=SC.S#(+)4). Given the table T1, created by:CREATE TABLE EMP(ID CHAR(3) PRIMARY KEY, Name CHAR(8), Age Integer,CONSTRAINT const1 CHECK (Age18 and Age60);The following SQL statements are issued:INSERT INTO EMP(ID, Age) VALUES (001,25);INSERT INTO EMP(ID, Age) VALUES (002,35);INSERT INTO EMP(ID, Age) VALUES (003,15);COMMIT;How many rows are inserted into Table EMP?A. 0 B. 1 C. 2 D. 3 5). Given the following tables:StudSCSnoSnameS#C#Grade1Smith11802Jones25903Smith261004BOB3130Which of the following statements removes the rows from the Stud table that have courses in the SC table?A. DELETE FROM Stud WHERE Sno IN (SELECT Sno FROM SC); B. DELETE FROM Stud WHERE S# IN (SELECT S# FROM SC);C. DELETE FROM Stud WHERE Sno IN (SELECT S# FROM SC);D. DELETE FROM Stud WHERE S# IN (SELECT Sno FROM SC);6). Relvar R(A,B,C,D,E,F)satisfies the following FDs:A BCB EFWhich of the following is the candidate key ?A. null set B. AC C. AD D. ABD7). Relvar ACCOUNT(Cno, Cname, AccountNo, Balance)satisfies the following FDs:Cno CnameAccountNo Cno, BalanceWhich of the following is the best answer ?A. R1NF B. R2NF C. R3NF D. RBCNF8). Relvar R(A,B,C,D,E)satisfies the following FDs:A BCDEWhich of the following is the best answer ?A. R1NF B. R2NF C. R3NF D. RBCNF9). Relvar R (S#, SNAME, P#, QTY) satisfies the following FDs:Which of the following is the best answer ?A. R1NF B. R2NF C. R3NF D. RBCNF10). Relvar R(S#,STATUS,CITY) satisfies the following FDs:S# CITY CITY STATUSReplace R by the two projections R1 and R2 as follows:R1(S#, CITY) with Primary Key S#R2(S#, STATUS) with Primary Key S#Which of the following is the right answer ?A. Projections R1 and R2 of relvar R are independent.B. The decomposition is dependency preservation.C. The decomposition is a nonloss decomposition.D. The decomposition is dependency preservation and nonloss decomposition11). Which two of the following choices are objectives the normalization of relation variable wants to achieve? A. Remove redundancies within relation variables.B. Improve the performance of the database being designed.C. Reduce the size of the relation variables.D. Remove redundancies across relation variables.E. Avoid data inconsistencies and other problems resulting from insert, update, or delete operations.12). Let transactions T1,T2 be defined to perform the following operations: T1: Add four to BT2: Treble BSuppose transactions T1 and T2 are allowed to execute concurrently. If B has initial value three. How many possible correct results are there?A. 1 B. 2 C. 3 D. 413). When can tuple types be merged?A. Tuple types with always corresponding foreign key values can be merged.B. Tuple types with always corresponding primary key values can be merged.C. Tuple types with different foreign key values can be merged.D. Tuple types with different primary key values can be merged.14). Assume that the delete rule for a referential constraint is CASCADE. Choose a case for which the deletion of a parent row would still fail.A. The parent table has another foreign key. B. Another referential constraint with delete rule RESTRICT prevents the deletion of the parent row.C. Its referential table has more than one candidate keys.D. The parent table has more than one candidate keys.15). Which two of the following methods can ensure the correctness of derivable data ? A. Not storing them and deriving them each time they are needed.B. Revaluating and storing the data which affect the derivable data. C. Triggers reevaluating and storing the data which affect the derivable data.D. Triggers reevaluating and storing the derivable data each time data affecting the derivable data are inserted, updated, or deleted.3. Assume that you have the following entity-relationship model:Furthermore, assume that the entity types and relationship types have the following instances:Which instances will be deleted after entity instance C2 of entity type C has been deleted? (8 points)4. Consider the following four relations for an Order database:CREATE TABLE Product(Pno CHAR(4) PRIMARY KEY, Pname VARCHAR2(40),Price NUMBER(7,2),Inventory int);CREATE TABLE Customer (Cno CHAR(5) PRIMARY KEY, Cname CHAR(20),Company CHAR(30),City CHAR(20), Tel CHAR(15);CREATE TABLE Orders (Ono CHAR(5),Order_date DATE,Cno CHAR(5),Freight INT,Shipment_date DATE,PRIMARY KEY(Ono),FOREIGN KEY(Cno) REFERENCES Customer(Cno);CREATE TABLE Order_items(Ono CHAR(5),Pno CHAR(4),Qty int,Discount NUMBER(4,2),PRIMARY KEY(Ono,Pno);Specify the following queries in relational algebra: (10 points)1). Get the name and telephone number for all customers who is located in Shanghai.2). Get the Order number, product name and quantity for all orders.3). Get the Order Number for whose orders which ordered all the products.4). Get the product number which is ordered by customer C001.5. Based on the above database, specify the following operate in SQL. (18 points)1). Get order number, order date and freight of all orders. 2). For all orders, get order number, product name, quantity and price. Order the result by order number and product name.3). Find the order number of all orders which didnt order P001 product.4). Find all the customer names and the total number of orders they ordered. 5). Add a foreign key reference on the order_items table which ensure that the order will not order an nonexistent product.6). Delete all order items of order O001. 6. Assume that R and R1 through Rn are relational variables satisfying the following conditions: They all have the same primary key. At all times, each primary key value of R1 through Rn occurs in R.Which further condition must be satisfied for R1 through Rn being a perfect decomposition of R?(3 Points)7. Relvar RA,B,C,D,E,F,G,H,I satisfies the following FDs:A EAB CEG HCompute the closure ABG+under this set of FDs. (4 Points)8. A relvar TIMETABLE is defined with the following attributes: D: Day of the week (1 to 5) P: Period within day (1 to 8) C: Classroom number T: Teacher name L: Lesson name The tuple D:d,P:p,C:c,T:t,L:l appears in this relvar if and only if at time D:d,P:p lesson l is taught by teacher t in classroom c. You can assume that lessons are one period in duration and that every lesson has a name that is unique with respect to all lessons taught in the week. 1). What are the candidate keys of relvar TIMETABLE?2). Is relvar TIMETABLE in 3NF? (7 points)上海财经大学数据库系统课程考试卷（A）标准答案 20052006学年 第二学期 1、是非题答案填入下面的表格中。(共20分)（“T”表示相应题目的描述是正确，“F”表示相应题目的描述是错误的）1). F11). T2). F12). F3). F13). F4). T14). F5). T15). F6). F16). F7). T17). T8). T18). T9). F1