发布时间 : 星期六 文章四川大学数据库系统期末试题2014-2015更新完毕开始阅读
四川大学期末考试试题（闭卷） （2014-2015学年第1学期）
1. Multiple Choices (20 marks, 2 marks for each)
1）、The following are functions of a DBMS except （ ）
A. creating and processing forms B. creating databases C. processing data
D. administrating databases 2）、In a one-to-many relationship, the entity that is on the one side of the relationship is called a（ ）entity. A. parent B. child C. instance D. subtype
3）、When the values in one or more attributes being used as a foreign key must exist in another set of one or more attributes in another table, we have created a（ ）: A. transitive dependency. B. insertion anomaly.
C. referential integrity constraint. D. normal form.
4）A tuple is a（ ）: ） A. column of a table.
B. two dimensional table. C. row of a table. D. key of a table.
5）、In which of the following is a single-entity instance of one type related to many entity instances of another type?（）（ ）
A. One-to-One Relationship B. One-to-Many Relationship C. Many-to-Many Relationship D. Composite Relationship
6）、Which of the following is NOT a basic element of all versions of the E-R model?（ ）
A. Entities B. Attributes C. Relationships D. Primary keys
注：试题字迹务必清晰，书写工整。 本题3页，本页为第1页
教务处试题编号：
课程名称：数据库系统 任课教师 张天庆、龚勋、李川、屈立笳 学号： 姓名： 7）、The DROP TABLE statement:（ ）
A. deletes the table structure only.
B. deletes the table structure along with the table data.
C. works whether or not referential integrity constraints would be violated. D. is not an SQL statement.
8）、SQL views can be used to hide:（ ）
A. columns and rows only.
B. complicated SQL syntax only.
C. both of the above can be hidden by an SQL view. D. None of the above is correct.
9）、The first step in database development is which of the following?（ ）
A. Enterprise data modeling B. Logical database design
C. Physical database design and definition D. Database Implementation
10）、Which of the following database activities determines the entities, attributes, and relationships of data?（ ）
A. Conceptual data modeling B. Logical database design
C. Physical database design and definition D. Database implementation
2. Write SQL statements (30 marks, 6 marks for each)
2) 3) 4) 5) 6) 7) 8) 9) 10) Consider the relational database of a banking enterprise with the following relation schemas, where the primary keys are underlined.
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city) loan (loan_number, branch_name, amount) borrower (customer_name, loan_number)
account (account_number, branch_name, balance)
本题3页，本页为第2页 教务处试题编号：
1) 课程名称：数据库系统 任课教师 张天庆、龚勋、李川、屈立笳 学号： 姓名： depositor (customer_name, account_number) From the database schema above, give an expression in SQL for each of queries 2, 3, 4 and 5. 1）、 Define the relation “account” in SQL.
Tip: Describe primary keys, foreign keys and check constrains if necessary.
2）、To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200.
3）、Find all customers who have both a loan and an account
4）、Find the names of all branches where the average account balance is more than $1,200.
5）、Find all loan number which appear in the loan relation with null values for amount
3． Briefly-answer questions (20 marks, 5marks for each) 1) 2) 3) 4) 5) 6) 7) 8) 9) 10) 1）、What is the purpose of the index mechanism in database system? And give the definitions of primary index and secondary index.
2）、Please list the five duties of a database administrator 3）、Please give the definition of “trigger” in database system.
4）、Explain the distinctions among the terms superkey, candidate key and primary key. 4、 Database Design (30 marks)
You are first required to create a conceptual data model of the data requirements for a company that specializes in IT training and then map the conceptual data model into a relational schema. The Company has 30 instructors and can handle up to 100 trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session. 1) 、 Identify the main entity types for the company.
2)、 Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data.
3)、 Using your answers for the previous two to draw a single ER model to represent the data requirements for the company. 4)、 Convert the E-R model into a relational schema using the mapping algorithm specified in this course. Specify key and referential integrity constraints, using directed arcs. Make sure you also identify alternate keys. Label each step of the mapping algorithm.
本题3页，本页为第3页 教务处试题编号：