《数据库模拟题(共160页).docx》由会员分享，可在线阅读，更多相关《数据库模拟题(共160页).docx（4页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上1. Single Choice1) 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 database language and a pure database language.D. SQL is both data definit
2、ion language and data manipulation language. 2) Which one of the following is NOT TRUE for relational model? (_) 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 subset of a candidate key.C. In a relation, a super key must contain a candi
3、date 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, B, C) D. (A, D)4) Which one of the following is NOT TRUE for SQL? (_c_)A.
4、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 tuples of r, but also the schema for r.5) “Unique ( (mike, Null), (mike, Null) )
5、” 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 (_).A. Primary keyB. Foreign keyC. Candidate keyD. Relation schema 7) If R is a one-to-many relationship set from entity set E1 to E2, Which one
6、of the following is TRUE? (_)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, (_) is incorrect. A. Weak entity set is an
7、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 via a total, many to one relationship
8、set9) If and only if (_), 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. R1R2 R1 is in F+B. R1R2 = FC. R1R2 FD. R1-R2 R12. Fill in the following blanks1) Database systems provide an abstract view of the data, which is achieved th
9、rough 3 level of abstraction: physical level, logical level, and (_).2) r and s are two relations. Suppose a tuple occurs 3 times in r and 5 times in s, then it occurs (_) times in the execution result of the SQL statement “r intersect all s”.3) Relation r has 100 tuples, among these tuples, only 2
10、have null values on attribute A, the result of the SQL statement “select count(*), count(A) from r” is (_).4) The grant statement is used to confer authorization, and the (_) statement is used to reclaim authorization.5) Collection of operations that form a single logical unit of work in database sy
11、stem is called (_).3. Answer the following questions1) Briefly describe what is referencing 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
12、 we divide relation schema (customer_id, loan_number, amount), into two schemas (customer_id, loan_number) and (loan_number, amount)? Describe why. 4) Briefly describe the ACID properties of transactions.4、Relational AlgebraGive the result of the following relational algebra expressions.R SABCa167a2
13、23a1 23a445a267a379B C 67 2 31) R1 =B, C, 200（R）2) R2 =B 2（R）3) R3 = R S 4) R4 = Asum(B) 5. Compose SQLConsider 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:integer)Cl
14、ass(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 should co
15、rrespond 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, with s
16、num 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”.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 s
17、tudents 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 students enroll.6) Find the names of all students who are enrolled in two classes that meet at the same time6、E/R DiagramThe club Travel-Often-And-A-Lot organizes shor
18、ter and longer tours for 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). Organi
19、zers must be stored with 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 Pari
20、s, 2004”, for instances, 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 or
21、ganizer members participate 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 fulfil
22、l above requirements. 2) Translate the E-R model into relation schemas. 7、Normalization and Schema DesignConsider a relation with schema R=A, B, C, D, E, F and F=ABCD; AD; DAE; EFholds 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)专心-专注-专业