2020年北邮数据库系统原理期中测验答案.pdf-资料库

2020 Database System Principles Test One Class___ _________No_______ _____Name____________ (20 points) Fill in blanks 1. （1）Among the following statements, the correct one/ones is/are C . I. OpenGauss database, derived from PostgreSQL, is developed and distributed by Huawei. II. MySQL and PostgreSQL are two typical open-source database systems. III. A on-line shopping site has a three-tier Browser-Server(B/S) architecture. Its application programs are programmed in Java, and these programs access MySQL database server via the ODBC interface. IV. The relational model is applicable to managing structured data such as the table data, while XML provides a way to represent semi-structured data, e.g. the data with nested structures. A. I, II, III, IV B. I, II, III C. I, II, IV D. II, III, IV （2）The data model defines the specification of managing data items in database. It is a collection of conceptual tools for describing data structure, data relationships, data semantics, data operations and consistency constraints. (3) Database design involves the following phases: requirements analysis, conceptual schema design, logical design and physical design. (4) As human-machine interfaces, the pure database language consists of two parts, i.e. the data manipulation language and the data definition language (或：DDL) that is for specifying the database schema and as well as other properties of the data. 1

(5) A key/ superkey/primary key/ candidate key is a set of one or more attributes that, taken collectively, can be used to identify uniquely a tuple in the relation. (6) (2 points) For the entity set instructor(instructor_id, name, age, department, building, salary), the primary key is C , the primary attributes are D . A. instructor_id B.{instructor_id} C. {instructor_id, department} D. instructor_id, department (7) (2 points)For the entity sets student and instructor and the relationship set advisor among them in the following figure, the mapping cardinality from student to instructor is many-to-many , and the participation constraints of instructor in advisor is partial . , 1….3 0…..* student instructor (8) There are three types of pure query languages related to the relational model, that is, relational algebra, tuple relational calculus, and domain relational calculus . (9) The six fundamental operations in the relational algebra are select, project, union, set difference, Cartesian-Product , and rename. (10) (2 points) Convert the entity set “instructor”, in which the attribute “phone_number” is a multivalued attribute, into two relational tables instructor-id 201081 name Wang phone_number department 13912345, Comp.Sci. salary 2000 15854321 答案： 2

instructor-id 201081 name Wang department Comp.Sci. salary 2000 注意：主键下的的下划线，如缺少下划线，扣 0.5 分 instructor-id phone_number 201081 201081 13912345, 15854321 (11) If X is one or more attributes in relation R1, and X is also the primary-key of another relation schema R2, X is called a foreign key from R1 referencing R2. (12) In SQL language, the statement that can be used for security control is D A. insert B. update C. commit D. grant (13) Consider the relation schema Student-schema(studentID, sname, department, location) and relation Student, which one is not the metadata stored in data dictionary? D A. the name of the relation student B. the domain and length of attribute studentID C. the number of tuples in Student D. a tuple <2020211, Henry, Computer, Building_3> (14) Consider the relation schema R(A,B,C,D), if each attribute A,B,C,D is contained in a candidate key for R, R at least is in 3 normal forms. (15) Let r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively, the subsetαof R2 is foreign key referencing K1 in relation r1, if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1] = t2[α] (16) With respect to integrity constraints in DBS, reference integrity(或：foreign key) constraints ensures that a value appearing in a relation for a given set of attributes also appears for a certain set of attributes in another relation. 3

(17) SQL queries can be invoked from host languages, e.g. C++, via embedded SQL and dynamic SQL. 2. (9 points) 给出下列关系代数操作对应的 SQL 语句 (1) σp(r) (2) ∏A1, A2, .., Am ( r ) (3) r∞s , , 假设 r(A, B, C), s(C, E, F) Answers： (1) select * from r where P (2) select A1, A2, .., Am from r (3) select * from r natual join s 或者： select * from r where r.C = s.C 3. (9 points) 给出下列 SQL 语句对应的关系代数表达式 (1) select department, avg(salary) from instructor group by department 假设 instructor(instructor_id, name, department, salary) (2) update instructor set salary = salary *1.1 where salary > 10000 (3) insert into r select A1, A2, …, Am from r1, r2, .., rn where P 4

Answers: (1) departmentG avg(salary) (instructor) (2) T1 ← пinstructor_id, name, department, salary*1.2σsalary>10000 (instructor) T2 ←σamount ≤ 10000 (instructor) instructor ← T1 ∪ T2 (3) r ← r ∪пA1,A2,..,Am(σp(r1×r2×…×rn)) 4. (14 points）Consider the following relations in banking enterprise database, where the primary keys are underlined. branch (branch-name, branch-city, assets), loan ( loan-number, branch-name, amount) borrower( customer-name, loan-number) customer (customer-name, customer-street, customer-city) account (account-number, branch-name, balance) depositor (customer-name, account-number) employee(employee-ID, employee-name, branch-name, job-title) (1) (7 points) Use a SQL statement to define the relational table employee, in which {employee-ID} is the primary key, {employee-name} is the candidate key and not permitted to be null, and there exists referential integrity between the table employee and branch. It is also required that an employee’s job-tile must be one of manager, teller, officer, or secretary. 5

Answer: create table employee ( employee-ID integer, employee-name varch(50), /*也可以采用其它长度的 varch、char 类型 branch-name varch(50), job-title varch(50)， primary key (employee-ID), unique (employee-name), foreign key (branch-name) references branch, check (job-title in (‘manager’, ‘teller’, ‘officer’, ‘secretary’)) ) 4 个完整性约束，每个 1 分。 (2) (7 points) Give a SQL statement to find the customer’ name who has one or more accounts at the branches located in Brooklyn district, and the total sum of the balances of his these accounts in Brooklyn is more than $10,000. It is required to list in alphabetic descending order the customer’s name, and his total balances in the branches in Brooklyn. Answer: Select customer-name, sum(balance) From branch Natual Join account Natual Join depositor Where branch-district=Brooklyn Group by customer-name Having sum(balance)>10,000 Order by customer-name desc group by 和 having 子句占 2 分，其余部分 3 分。 6

4. (20 points) Convert the following E-R diagram to the relation schemas and identify the primary key of each relation by underlining the primary attributes. Figure 1 E-R diagram Student(StudID, first_name, middle_name, last_name, Major) (4 分，没有正确标注主键，扣 1 分) Class(ClassID, CourseID, RoomNo) (4 分, 没有正确标注主键，扣 1 分) Take(StudID, ClassID, CourseID, ExamType, Score) (4 分, 没有正确标注主键，扣 1 分) Course(CourseID, ISBN, CourseDesc, Credits) (4 分, 没有正确归并联系 Used-by，扣 1 分) Textbook(ISBN, Title, Price) (3 分) 7 TextbookISBN TitlePrice{Author }StudentStudIDStuNamefirst_namemiddle_namelast_nameMajorCourseIDCourseDescCreditsClassClassIDRoomNoUsed-byAssigned-toTakeExamTypeScoreCourse

TextbookAuthor(ISBN, Author) (3 分) 注意：take 必须转换为独立的表，used-by 不能转化为独立表 5. (8 points) Given R={A, B, C, D, E, F} and F={A→B, C→D, D→E, E→F, F→C} that holds on R. List all the candidate keys of R. (8 points) (利用求候选键算法，给出计算过程) Answers: 只出现在左端 L 类属性：A，只出现在右端 R 类属性：B 出现在左右端的 LR 类属性：C，D，E，F 左右端均不出现的 N 类属性：无 X-set={A}，Y-set={C, D, E, F}。由于闭包 X-set+ =AB，并不等于 R，故从 Y_set 中分别取出 C、D、E、F 与 X-set={A}组合在一起，计算其闭包 (AC)+=(AD) +=(AE) +=(AF) +=R，故 R 的候选键为：AC, AD, AE, AF 判卷标准：（1）四个候选键，每个 2 分；（2）没有将属性分类，即没有 L、R、LR、N 类属性划分，扣 2 分。 8

资料库

2020年北邮数据库系统原理期中测验答案.pdf

相关推荐

课程资源

热门标签

最新资料