2008年DB-EXAM(12-24).doc

华南农业大学期末考试试卷（A卷） 2008-2009　学年第一学期　 考试科目： 数据库系统 考试类型：（闭卷）　　　 考试时间： 120 分钟 学号 姓名 年级专业 题号 一 二 三 四 五 六 七 总分 得分 评阅人 _____________________________________________________________________________ Instructions to candidates: 1. This paper consists of 7 questions. 2. Answer all questions on the answer sheets. 3. Hand in the answer sheets, and this paper can be taken away. Question 1: (25 points) Define the following terms. Each term is worth 2.5 marks. a. Data dictionary b. View c. Data independence d. Data model e. Foreign key f. Trigger g. Functional dependence h. Consistency i. Transactions j. Log Question 2: (18 marks) An Internet store sends emails to customers and would like to use a database to keep track of which messages are sent to which customers. A message has a message id (mId), a subject (subject), and a body (body). A customer is identified by the email address (email), and customer’s data includes the attributes name, sex, householdSize, and address. When an email is sent, the date (sendDate) is recorded. You can assume any reasonable domains for these attributes. a. Give an E-R diagram that completely describes the entities and relationships of this plan. [6 marks] b. Translate the E-R diagram into SQL tables using SQL DDL, by specifying a table for messages, a table for customers, and a table for which messages are sent to which customers,in SQL DDL. [6 marks] c. How to specify that the subject of a message must not be longer than 60 characters, in SQL? [1 marks] d. How to require that customers at the same address all have different names, in SQL? [1 marks] e How to enforce the restriction that the only valid values of sex are male or female, in SQL?