四川大学数据库系统_双语期末考题

1.Multiple Choices (20 marks, 2 marks for each)1) In a database system, whose responsibility is it to provide data consistency?A. the database administrator'sB. the DBMS'sC. the user'sD. the application programmer's2) The term logical data independence refers to the ability to changeA. the conceptual schema without changing the external schemas, or the application programsB. the physical schema of the data without changing the external schemas, the conceptual schemas, or the application programsC. the application programs without changing the conceptual schemaD. the data without physically relocating the tables3) A database is needed for which of the following application scenarios?I. A video store that needs to keep track of data about members, about videos carried by the store, about videos rented by members, as well as data concerning borrow-date, return-date, and payment information.II. In the human resources department of a company, information about employees, their titles, their salaries and sick days, and about vacation days taken by each employee.III. A computer-simulated video game which needs to calculate and display, the physical (x, y) location of each actor in the game, the speed with which they are moving at the current instant, the direction in which they are moving, the action they are performing, the angle at which the game-player is viewing the scene.A. I and II onlyB. I onlyC. I and III onlyD. I, II, and III4) Which of the following are usually used to represents ENTITY TYPE in ER modeling.A. verbB. nounC. proposition.D. adjective5) What information is necessary when specifying the structure of a table in a relational schema?A. the name of the table, the names of the table's attributes, the data types of attributes, and the formats of attributesB. the name of the table and the names of the table's attributesC. the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can haveD. the name of the table and the amount of storage space to be allocated to the table6) Which of the following problems can be caused by data redundancy in a relational schema?I. Inefficient use of spaceII. Update anomalies and possible loss of dataIII. Inefficient use of processing timeA. I and II onlyB. II onlyC. I and III onlyD. I, II, and III7) Which of the following are properties of transactions in database systems?I. Atomicity and DurabilityII. Consistency and IsolationIII. Unique and IndependentA. I and II onlyB. I and III onlyC. II and III onlyD. I, II, and III8) Which of the following is true about transactions affecting the consistency of a database?A. A transaction always leaves the database in a consistent state.B. A transaction leaves the database in a consistent state if there is no system crash during the execution of the transaction.C. A transaction leaves the database in a consistent state if the user has not violated any integrity constraints.D. There are no guarantees that transactions will leave the database in a consistent state.9) In a two-phase locking protocol, what happens when a transaction requests a conflicting lock?A. The transaction is aborted immediately.B. The transaction immediately acquires the lock from the current lock-holder.C. The transaction proceeds without acquiring the lock.D. The transaction is blocked to acquire the lock.10) During recovery, which of the following transaction operations has both the before and after images?A. CommitB. InsertC. DeleteD. Update2. Write SQL statements (30 marks, 5 marks for each)Consider a database schema with the following relations:Student (SSN, name);Course (ID, instructorName, title, credits, classroom);Enroll (studentSSN, courseID, score);Write SQL statements in SQL2 to perform the following commands.1)Find SSNs and names of all students who are enrolled in a class taught by ‘Smith’;2)Find SSNs and names of all students who are NOT enrolled in a class taught by ‘Smith’;3)List alphabetically titles of all courses either are taught by teachers whose names begin with “Li”OR are taught in room number 4-456. Do NOT list duplicate titles;4)Find the name of the student who enrolled in the course titled “Database” and get the highest score;5)List all students’ name and their average score;6)Find SSNs and names of all students who are enrolled in at least all classes that the student ‘John’enrolled.3. Database Design (30 marks)Y ou 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.4. Normalization (20 marks)The following table lists dentist/patient appointment information. A patient is given an appointment at a specific date and time with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day.Perform the following tasks:1) The table is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies.2) Describe and illustrate the process of normalizing the table to BCNF. State any assumptions you make about the data shown in the table.。