华南农业大学期末考试试卷（A卷）2005学年第一学期考试科目：数据库原理与方法考试类型：（闭卷）考试时间：120 分钟学号姓名年级专业2003___Instructions to candidates:1. This paper consists of 6 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 marks) Define the following terms. Each term is worth 2.5 marks.a. Databaseb. Physical data independencec. Data dictionaryd. Transactione. Serializable schedulef. Relational schema and relational instanceg. Data miningh. Foreign keyi. Functional dependencyj. CheckpointsQuestion 2: (17 marks) Consider a university database including the following data objects:●Departments: Each department has a unique id, a name, a location, and atelephone number. Departments employ professors and offer courses.●Professors: Each professor has a unique id, a name, a rank and a salary. Allprofessors with the same rank have the same salary. Professors teach courses and advise students.●Students: Each student has a unique id, a name, gender, and age. Studentsenroll in courses.●Courses: Each course has a course code. Courses also have a meeting day,time, and room.●Loans: Students can take out a loan to help with their expenses. Each loanhas a unique loan id, a loan amount, and the anticipated starting date for the payments.The system requirements are:●Departments can employ more than one professor and each professor canwork for multiple departments. There is a percent time associated with each department a professor works in. Departments also offer multiple courses each semester.●Professors can teach multiple courses in each semester, and the same coursein different semesters. Professors advise students and can have multiple advisees.●Students enroll in several courses in each semester. For each course, astudent receives a grade. Students are advised by a single professor. Students may apply for multiple loans.● A single course can only be taught once within a giver semester, but can berepeated in any other semesters. Courses are taught by a single professor and are taken by multiple students.●Loans are applied for by students.a. Draw an E-R diagram design for this database. [9 marks]b. Represent this database design as a set of relation schemas.[4 marks]c. Select a primary key for each relation (this can be indicated by underlining the selected attributes in your answer to Question 2b) and indicate foreign key for each relation as follows:Employ (Dept, Professor, PercentTime) -- FK Dept reference Department ….[4 marks]Question 3: (12 marks) Consider a relation Book which collects information about book identification numbers (“ISBN”), authors, tit les, publishers, and years of publication. Hence the relation is:Book = (ISBN, Author, Title, Publisher, Year)For convenience, we will henceforth abbreviate these attributes as:Book= (I, A, T, P, Y)Suppose the following set F of functional dependencies are asserted to hold.TP IAP TI ATPa. For each of the functional dependencies, state Yes or No according to whether they are members of F+: [4 marks](1) AT P(2) ATP Y(3) YI P(4) AP Ib. List all candidate keys of Book. [4 marks]c. Is the following decomposition of Book lossless? Why or why not? [4 marks](A, T, P), (I, P, Y), (I, T)Question 4: (12 marks) Consider a relational database as follows:employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give an expression in relational algebra for each of the following queries. All queriescarry equal marks.a.Find the names of all employees who work for the First Bank Corporation.b.Find the names and cities of residence of all employees who work for FirstBank Corporation.c.Find the names, street address, and cities of residence of all employees whowork for First Bank Corporation and earn more than $10,000 per annum.d.Find the names of all employees in this database who live in the same city asthe company for which they work.Question 5: (16 marks) You will be working with the following schema: Employee(SSN, name, salary, DNo)Department(DNo, DeptName, MgrSSN)Project(PNo, location, ProjName)HourLog(SSN, PNo, hours)The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department's manager (MgrSSN). Each department has a only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. Write the following queries in SQL. All queries carry equal marks.a. Find the SSN of everyone who works more than 100 hours on one particularproject.b. Find the name and SSN of everyone who works for department number 1 andalso work on project number 2.c. Find the name and the SSN of everyone who works on at least two projects.d. Find the SSN of everyone who is not working on any project.Question 6: (8 marks) Let the following relation schemas be given:R = (A, B, C)S = (D, E, F)Let relation r(R) and s(S) be given. Give an expression in SQL that is equivalent to each of the following queries. All queries carry equal marks.a. ∏A(r)b. σB=17(r)c. r⨯sd. ∏A,F (σC=D(r⨯s))Question 7: (10 marks)Figure 1 is a concurrent transaction schedule.Figure 1: A concurrent transaction scheduleIs this schedule conflict serializable? If Yes, show an equivalent serial schedule for T1, …, T4 , if No, argue why not.华南农业大学期末考试试卷（A卷-Answers）2005学年第一学期考试科目：数据库原理与方法Question 1:a. DatabaseA collection of interrelated data, usually contains information relevant to anenterprise.b. Physical data independenceApplication programs need not be rewritten if the physical schema changes.c. Data dictionaryData dictionary stores metadata about the structure of the database, inparticular the schema of the database.d. TransactionA transaction is a unit of program execution that accesses and possiblyupdates various data items. Transactions are required to have the ACIDproperties: atomicity, consistency, isolation, and durability.e. Serializable scheduleA schedule is serializable if it is equivalent to a serial schedule. There areseveral different notions of equivalence: conflict equivalence, viewequivalence, and etc.f. Relational schema and relational instanceRelational schema is the logical design of a relational table or a relation.Relational instance is the content of a relation at a given instance in time.g. Data miningData mining is the process of semi-automatically analyzing large databases tofind useful patterns.h. Foreign keyLet r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively.The subset α of R2 is a foreign key referencing K1 in relation r1, if for everyt2 in r2 there must be a tuple t1 in r1 such that t1[K1] = t2[α].i. Functional dependencyConsider a relation schema R, and let α⊆R and β⊆R. The functionaldependency α->βholds on schema R if , in any legal relation r(R), for allpairs of tuples t1 and t2 in r such that t1[α]=t2[α], it is also the case thatt1[β]=t2[β].j. CheckpointCheckpoints are used to reduce the number of log records that the systemmust scan when it recovers from a crash. During checkpointing thefollowing actions are carried out:●Output all log records currently residing in main memory onto stablestorage.●Output all modified buffer blocks to the disk.●Write a log record < checkpoint> onto stable storage.Question 2:a. See Figure 1.b. c.Department (ID, Name, Location, Phone)Professor (ID, Name, Rank) -- Rank FK references RankStudent (ID, Name, Gender, Age, Advisor) -- Advisor FK references ProfessorCourse (ID, Semester, MeetingDay, Time, Room, Dept, Teacher)-- Dept FK references Department-- Teacher FK references Professor Rank (ID, Salary)Loan (ID, Amount, PaymentDate, Borrower) -- Borrower FK references Student Employ (Dept, Professor, PercentTime)-- Dept FK references Department-- Professor FK references Professor Enroll (Course, Semester, Student, Grade)-- Course, Semester FK references Course-- Student FK references StudentFigure 1: ER DesignQuestion 3:a. (1) AT P No; (AT)+=AT(2) ATP Y No; (ATP)+=ATPI(3) YI P Yes; (YI)+=YIATP(4) AP I Yes; (AP)+=APTIb. YI, YTP, YAPc. Yes, because:IPY∩IT = I, and I+ = IATP; hence I ITITPY∩ATP = TP, and (TP) + =TPIA; hence TP ATP.Question 4:a.b.c.d.Question 5:a. select distinct SSNfrom HourLogwhere hours>=100;b. select SSNfrom Employee e, Department d, HourLog hwhere e.DNo=d.DNo and e.SSN=h.SSN and d.DNo=1 and h.PNo=2;c. select SSN, namefrom Employeewhere SSN in(select SSNfrom HourLoggroup by SSNhaving count(PNo)>=2);d. select SSNfrom Employeewhere SSN not in (select SSN from HourLog);Question 6:a.select A from r;b. select * from r where b=17;c. select * from r, s;d. select A, F from r, s where r.C=s.D;Question 7:The precedence graph is shown as Figure 2.Figure 2: Precedence graphThus the schedule given is conflict serializable, the (only) serialization order is T1, T3, T2, T4.。
