东北大学数据库期末试卷样例(共4页).doc

《东北大学数据库期末试卷样例(共4页).doc》由会员分享，可在线阅读，更多相关《东北大学数据库期末试卷样例(共4页).doc（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上总分一二三四五六七 密封线学院班级学号姓名东北大学考试试卷（B 卷） 学年第 学期 课程名称：数据库原理A. If R has any descriptive attribute it can be moved to E1.B. The primary key of R is the primary key of E2.C. E1 can be a weak entity setD. E2 must totally participate in R8) In the following statements about weak entity set, (_c_) i

2、s incorrect. A. Weak entity set is an entity set that does not have a super key B. The existence of a weak entity set depends on the existence of an identifying entity setC. Weak entity set is an entity set that does not have a foreign keyD. Weak entity set must relate to the identifying entity set

3、via a total, many to one relationship set9) If and only if (_a_), K is a super key of R.A. KRB. RKC. K(K-R)D. (R-K)K 10) A decomposition of R into R1 and R2 is lossless join if (_a_).A. R1R2 R1 is in F+B. R1R2 = FC. R1R2 FD. R1-R2 R11) Database systems provide an abstract view of the data, which is

4、achieved through 3 level of abstraction: physical level, logical level, and (_View level_).2) r and s are two relations. Suppose a tuple occurs 3 times in r and 5 times in s, then it occurs (_3_) times in the execution result of the SQL statement “r intersect all s”.3) Relation r has 100 tuples, amo

5、ng these tuples, only 2 have null values on attribute A, the result of the SQL statement “select count(*), count(A) from r” is (_100，98_).4) The grant statement is used to confer authorization, and the (_revoke_) statement is used to reclaim authorization.5) Collection of operations that form a sing

6、le logical unit of work in database system is called (_事务_).1. Single Choice (10 points, 1 point each)1) Which one of the following is NOT TRUE for database language? (_c_)A. Relational Algebra is a procedural database language.B. SQL is a declarative database language.C. SQL is both a declarative d

7、atabase language and a pure database language.D. SQL is both data definition language and data manipulation language. 2) Which one of the following is NOT TRUE for relational model? (_b_) A. In a relation, a foreign key can be a subset of the primary key. B. In a relation, the primary key can be a s

8、ubset of a candidate key.C. In a relation, a super key must contain a candidate key.D. In a relation, a candidate key can include multiple attributes.3) Given relation schema R1(A,B,C,D) and R2(B,C), the schema of the relation as the result of R1R2 is (_d_).A. (A, R1.B, R1.C, R2.B, R2.C)B. (A)C. (A,

9、 B, C) D. (A, D)4) Which one of the following is NOT TRUE for SQL? (_c_)A. Views may be defined in terms of other views.B. Result of where clause predicate is treated as false if it evaluates to unknown.C. Set operations automatically eliminate duplicates.D. DELETE TABLE r deletes not only all tuple

10、s of r, but also the schema for r.5) “Unique ( (mike, Null), (mike, Null) )” is evaluated to (_a_)A. TRUEB. FALSEC. UNKNOWN.D. NULL6) In “CREATE TABLE” statement, unique( A1, A2, , Am) states that the attributes A1, A2, Am form a (_c_).A. Primary keyB. Foreign keyC. Candidate keyD. Relation schema 7

11、) If R is a one-to-many relationship set from entity set E1 to E2, Which one of the following is TRUE? (_b_)2. TRUE or FALSE (10 points, 2 points each)3. Answer the following questions (12 points, 3 points each)1) R1 =B, C, 200（R）2) R2 =B 2（R）3) R3 = R S 4) R4 = Asum(B) 1) Briefly describe what is r

12、eferencing constraint.2) Briefly describe the following concepts about keys in a relational model: Super Key, Candidate Key, Primary Key.3) In a bank database, if a loan can be borrowed by more than one customer, should we divide relation schema (customer_id, loan_number, amount), into two schemas (

13、customer_id, loan_number) and (loan_number, amount)? Describe why. 4) Briefly describe the ACID properties of transactions.Give the result of the following relational algebra expressions.R S学院班级学号姓名密封线4. Relational Algebra ( 12 points, 3 points each )B C 67 2 3ABCa167a223a1 23a445a267a3795. Compose

14、SQL (30points, 5points each)密封线4) Create a view BusyFaculty that records the ids and names of faculties who teach more than 3 classes.5) Find the names of all students that enroll in a class where students meet in room R128 (i.e., Class.room = R128) or a class in which five or more than five student

15、s enroll.6) Find the names of all students who are enrolled in two classes that meet at the same time学院班级学号姓名Consider the following relations (the primary keys are underlined): Students(snum:integer, sname:string, major:string, level:string, age:integer)Faculty(fid:integer, fname:string, deptid:inte

16、ger)Class(cname:string, meets at:string, room:string, d:integer)Enrolled(snum:integer, cname:string) The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Note that snum and cname in Enrolled sho

17、uld correspond with snum in Students and cname in Class respectively.1) Write a SQL statement to create relation Class. Declare a primary key and foreign keys (if any) on this relation. 2) Write a SQL statement to insert into the database the fact that the 22 year-old senior CS student Kobe Bryant,

18、with snum 111, is enrolled in class CS411. (hint: Both Students and Enrolled tables need to be updated.)3) Write a SQL statement to delete all the classes taught by “Joe Smith”.6. E/R Diagram (14 points, 7 points each )密封线Consider a relation with schema R=A, B, C, D, E, F and F=ABCD; AD; DAE; EFhold

19、s on R.1) Give all candidate keys of this relation, motivate. (3 points)2) Indicate all extraneous attributes in F, motivate. (3 points)3) Is this relation in 3NF？If it is not, decompose it into relations in 3NF. (6 points)1学院班级学号姓名The club Travel-Often-And-A-Lot organizes shorter and longer tours f

20、or its members. Help them to make a model of their mini world. Travel-Often-And-A-Lot has members. Each member is represented by her/his full name, address, and birth date.Some members belong to the board of Travel-Often-And-A-Lot. Some members are organizers (of tours). Organizers must be stored wi

21、th their cell phone number so that they can be reached anytime. Organizers organize tours. Sometimes a tour is organized by several organizers.Each tour is denoted by a name, e.g. “Museums of Paris, 2004” or “Iceland, 2005”. Tours can take place multiple times. “Museums of Paris, 2004”, for instance

22、s, takes place twice: May 22nd to May 29th, 2004 and June 5th to June 12th, 2004. The cost of a tour depends on the date, e.g. “Museums of Paris, 2004” was cheaper in May than in June. Each travel such as “Museums of Paris, 2004” at June 5th to June 12th, 2004 is lead by one organizer members partic

23、ipate in travels.Travel-Often-And-A-Lot wants to keep track of the payments made by its members. A payment can e.g. be the annual club fee, a donation, etc. but also the payment for a travel. Mind the subtle distinction between tour and travel.1) Create an E-R model that fulfill above requirements. 2) Translate the E-R model into relation schemas. 7. Normalization and Schema Design (12 points)专心-专注-专业