东北大学数据库题库.pdf

发布时间：2022-06-24 发布人：admin 分类：说明书资料大小：0.43M 资料格式：pdf 举报版权申诉

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第1页.png

第1页 / 共6页

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第2页.png

第2页 / 共6页

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第3页.png

第3页 / 共6页

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第4页.png

第4页 / 共6页

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第5页.png

第5页 / 共6页

d74d3d9d-cd46-4e24-b2f3-b8a5bf9e0e74.pdf-第6页.png

第6页 / 共6页

文本预览

09 年级会制作数据库复习资料

09 年级会制作数据库复习资料 3. Answer the following questions (12 points, 3 points each) a) Briefly describe what is physical data independence. b) Briefly describe the following concepts about keys in a relational model: Super Key, Candidate Key, Primary Key. c) Briefly describe the difference between tables and views. d) Briefly describe what is a transaction. 4. relational Algebra (12 points, 3 points each) Give the result of the following relational algebra expressions. R S A a1 a2 a1 a4 a2 a3 B 2 1 C 4 2 2 5 4 9 B 2 1 1 4 2 7 C 4 2

09 年级会制作数据库复习资料 a) R1=∏B,C(R) b) R2=δ C>3(R) c) R3=R S d) R4=R÷S 5. Compose SQL (30 points, 5 points each) Consider the following relations (the primary keys are underlined): Students (snum: integer, sname: string, major: string, level: string, age: integer) Faculty (fid: integer, meets at: string, room: string, fid: integer) Class (cname: string, meets at: string, room: string, fid: 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 correspond with snum in Students and cname in Class respectively. a) Write a SQL statement to create relation Class; Declare a primary key and foreign keys (if any) on the relation. Note that fid in Class should correspond with fid in Faculty. b) Write a SQL statement to increase the age of each student by one year. Note that only the rows where sname has value (i.e., not Null) should be updated. c) Create a vies BusyStudent that records the names of students who are enrolled in more than 5 classes. d) List the names of all students whose age is 20 years old.

09 年级会制作数据库复习资料 e) Find the “snum”(attribute) of students enrolled in three classes. f) Find the names of students who are enrolled in a class taught by “Joe Smith”. 6. E/R Diagram and Schema Design (16 points, 8 points each) Given are the following entities (names are underlined) for a software company: - IT Consultant with name, date of birth and address. - Programmer with name, date of birth and address. - Project with name, budget, start date and end date. - Programming Language with name. In addition there are the following relationships (names are underlined): - IT Consultant may supervise many Projects; a Project can be supervised by only one IT Consultant. - A programmer works on at most two Projects; at least one Programmer works for a Project; the working hours are registered for each Project of each Programmer; - A Programmer uses at least one Programming Language; a Programming Language is used by any number of Programmers. a) Design an E-R diagram with the given entities and relationships. b) Translate the E-R diagram into relational schemas. 7. Normalization and Schema Design (15 points) Consider a relation with schema R(A, B, C, D, E) and F={AB->C, DE->C, and B->D} holds on R, attribute set {A, B, E} is the only candidate key of R.

09 年级会制作数据库复习资料 a) Decide whether this relation is in BCNF or not. Explain why. (7 points) b) Decompose the relations, if necessary, into collections of relations that are in BCNF. Make sure to indicate which dependency you apply to each decomposition. (8 points)

分享到：

赞收藏

资料库

东北大学数据库题库.pdf

相关推荐

数据库

热门标签

最新资料