数据库英语综合测试题16答案

　　Part 3: Questions and Answers

　　1. Consider the following relational schema: Use SQL to write the following queries:

　　A. Create the table for the book table appropriate domain and required contraints.

　　B. Change the data type of the checkout date attribute of the loan table to date.

　　C. Add a constraint into the loan table to make sure the loan duration is no more than 180 days.

　　D. Grant Arvil and Amy select and update authrorization on the book table.

　　2. What are 6 basic operators of relational algebra?

　　3. Explain how natural-join operation can be accomplished by basic relational algebra operations?

　　4. Explain how the division operation can be accomplished by basic relational algebra operations?

　　5. The database of a research center contains the following three tables about employees,projects, and the time spent by the employees on the projects.

　　Employee(ssn: int, name: string, jobTitle: string)

　　Project(pid: int, name: string, sponsor: string, startYear: int, endYear: int) WorkedOn(ssn: int, pid: int, year: int, month: int, noHours: int).

　　The table Employee lists all the employees of the centre. The table Project lists all the projects of the centre with their sponsor and the start and end year of the project. The table WorkedOn records how many hours the employees have spent on which project in which month. For each table, the attributes that make up the primary key are underlined.

　　Express each of the following queries in relational algebra.

　　A. Return the names of the projects that were active in 2008.

　　B. Return the names of those programmers who in some month spent more than 60 hours on a project sponsored by the EU.

　　C.Return the names of those programmers who never worked on a project sponsored by the EU.

　　6. Consider the relational schema of Question 5. Write SQL queries over this schema that answer the following questions.

　　A. How many projects that were active in 2008 were sponsored by the EU?

　　B. For each project, year, and month, how many hours of work have been spent? (Return only data for a project, year and month if some time has been spent.)

　　C. How many programmers are there who have experience in working on a project sponsored by the EU? (Note that a programmer who worked on two or more projects should be counted only once.)

　　D. Return the names of the programmers who worked on no more than two projects sponsored by the EU. (Note that this includes the programmers who never worked on any project sponsored by the EU.)

　　E. Which programmer(s) spent the maximal total number of hours on EU projects

　　among all programmers working on EU projects?

　　7. Consider the following relation that keeps track of the bookings in a hotel: Booking(guestID, guestName, creditCard, roomNo, roomCat, from, to).

　　Suppose the following functional dependencies hold on the relation:

　　guestID ? guestName, creditCard

　　roomNo ? roomCat

　　roomNo, from ? guestID, to

　　roomNo, to ? guestID, from.

　　A. Decompose the relation in smaller relations such that

　　– each of the smaller relations is in BNCF with respect to the projection of the original dependencies;

　　– the decomposition is a lossless join decomposition.

　　B.Is your decomposition dependency preserving? If your answer is “yes”, argue why. If your answer is “no”, show which dependencies have been lost.

　　8. Draw an ER diagram that captures all the following information:

　　_ Patients are identified by an SSN, and their names, addresses and ages must be recorded.

　　_ Doctors are identified by an SSN. For each doctor, the name, specialty and years of experience must be recorded.

　　_ Each pharmacy has a name, address and phone number. A pharmacy must have a manager.

　　_ A pharmacist is identified by an SSN, he/she can only work for one pharmacy. For each

　　pharmacist, the name, qualification must be recorded.

　　_ For each drug, the trade name and formula must be recorded.

　　_ Every patient has a primary physician. Every doctor has at least one patient.

　　_ Each pharmacy sells several drugs, and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.

　　_ Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and quantity associated with it.

　　9. Convert the following E/R design (for a simple banking application) into a relational design. Give the relational design as a relational diagram with arrows to indicate the foreign key relationships. Underline all attributes that correspond to primary keys.

　　10. Consider the following employee database, where the primary keys are underlined.

　　Employee(ename:string, street:string, city:string);

　　Works(employee:string, company:string, salary:real);

　　Company(cname:string, city:string);

　　Manages(employee:string, manager-name:string)

　　Give a single SQL statement for each of the following queries:

　　A. Find the names, street addresses, and cities of residence of all employees who work for “First Bank Corporation and earn more than $40,000.

　　B. Find the names of all employees in the database who live in the same cities as the companies for which they work.

　　C. Give all managers of First Bank Corporation a 10 percent salary raise.

　　D. Find the names of all employees in the database who earn more than any employee of “Small Bank Corporation.

　　E. Assume that the companies may be located in several cities. Find the names of all companies located in every city in which Small Bank Corporation is located. F. Find the name of the company that has the most employees.

　　G. Find those companies whose employees earn a higher salary, on average, than the average salary at “First Bank Corporation, display those companies' names in ascending order.

　　11. Consider the following (simplified) relational schema for university study: Student(id:integer, family:string, given:string, degree:string, enrolled:date) Course(id:integer, code:string, session:string, title:string, syllabus:string)

　　Enrolment(student:integer, course:string , mark:real, grade:string)

　　A.For each of the following SQL queries, write an efficient relational algebra

　　expression that might be used to implement the query. To make the expressions clearer, you may use as many named intermediate temporary relations as you wish. Correct, but grossly inefficient, relational algebra expressions will be awarded only half marks.

　　A. select given,family from Student

　　B. select * from Enrolment where student=2233456

　　C. select given,family,course

　　from Enrolment, Student

　　where Enrolment.student = Student.id

　　D. select e.code, e.session, c.title, e.mark, e.grade

　　from Enrolment e, Course c, Student s

　　where e.course = c.id and e.student = s.id and s.id = 2234567

　　12. Consider the following E/R diagram, modeling data about patients in a hospital:

　　A. Perform a conversion of the E/R diagram into relation schemas. You should eliminate relations that are not necessary (e.g., by combining relations).

　　13. Given the interleaved schedules:

　　Schedule 1

　　Schedule 3

　　A. Which of the following schedules are serializable? Give a serial schedule or identify possible anomalies.

　　B. Draw the precedence graph for all three schedules and check whether they are conflict-serializable or not.

　　C. Apply strict 2PL to the non-conflict-serializable schedules

　　D. In one of the schedules a deadlock emerges – draw the waits-for-graph for this schedule after all transactions are captured in the deadlock situation.

　　(Use X(.) to denote exclusive locks and S(.) to denote shared locks!)

　　14. Consider a relational schema ABCDEFGHIJ, which contains the following FDs: AB?C, D?E, AE?G, GD?H, IF?J.

　　A. Check whether or not the functional dependencies entail

　　ABD?GH

　　ABD?HJ

　　ABC?G

　　GD?HE

　　B. Let A denote a key for the aforementioned relation. Derive a lossless join, dependency preserving decomposition in 3NF!

　　15. What do the ACID properties stand for? Give a brief description of the four characteristics.

　　16. What are the serial schedule, equivalent schedules and serializable schedule?

　　17. Let R and S are two relations shown as below:

　　A. ? A,B+C? X(R) B. ?B,C(R) -?B,C(S)

　　C. B?B,sumD.(S)

　　D. B?B,maxD.(R S)

　　18. Consider the following relational schema for movie DVD rental store: Use SQL to write the following queries:

　　A. Create 3 tables for the above schema with appropriate domain and required contraints.

　　B. Change the data type of the checkout date attribute of the borrow table to date.

　　19. Consider the following gradebook relational schema describing the data for a grade book of a particular instructor

　　Use relation algebra and SQL to write the following queries:

　　A. Retrieve the names of students enrolled in the 'Database' class in the term of Fall 2009.

　　B. Retrieve the names of students who have enrolled in CS226 or CS227.

　　C. Retrieve the names of students who have not enrolled in any class.

　　D. Retrieve the titles of courses whose average score of the whole class is more than 80.

　　20. Consider a company database with the following relation schemas where primary keys are underlined:

　　department no)

　　Use relation algebra and SQL to write the following queries:

　　A. Retrieve the names of all employees in the 'Research' department who work more than 10 hours per week on the 'ProductX' project.

　　B. Find the names of employees who are directly supervised by 'Avril Lavigne'.

　　C. Retrieve the names of employees who work on every project.

　　D. Retrieve the names of all employees who work on at least one project located in Houston but whose department has no location in Houston.

　　E. For each department, retrieve the department name, number of employees in that department, and the average salary of employees working in that department.

　　F. For each department whose average employee salary is more than $50000, retrieve the department name and

　　the number of employees working for that department.

　　G. Remove employees whose salary is more than $100000.

　　H. Increase the pay of all employees in the 'Research' department by 5%

　　21. Consider the following bank schema.

　　account (account id, branch name, balance)

　　borrower (customer id, loan number)

　　branch (branch id, branch name, branch city, assets)

　　customer (customer id, customer name, customer street, customer city)

　　depositor (customer id, account number)

　　loan (loan number, branch id, amount)

　　Write SQL commands for the following query:

　　A. Retrieve all different branch names.

　　B. Retrieve all loan number that falls between 1000000 and 2000000.

　　C. Retrieve all customer names in the ’East Gate’ branch.

　　D. Retrieve the branch name and number of accounts for each branch.

　　22. Consider the following order table.

　　where the keys are underlined.

　　A. Normalize the above table to the 3 NF and draw the relational schema diagram and indicate the primary keys and the referential constraints.

　　B. Based on the above schema diagram draw the ER diagram.

　　23. A student relation has 4 attributes: student id, name, email, and phone no. No two customers have the same student id and email.

　　A. List keys, superkeys, and primary key for the student relation.

　　B. Explain the reason of choosing the primary key.

　　24. Consider the following electronic store database:

　　Use SQL to answer the following questions.

　　A. Create the product table with the required constraints.

　　B. Add a constraint of price 0 in the product table.

　　C. Insert ('P2348', 'Personal Computer', 1200) into the product table.

　　D. Find the all customers who have ordered a WII.

　　E. Change the price of 'WII' from 8000 to 7500.

　　F. Remove all orders that 'Lady Gaga has put.

　　25. Consider The database of a online game company has three relations: player, play, and game for storing information about players who plays which game. The database schema of the game company is shown as follows:

　　player (member no, name, level, phone, email)

　　play (member no, game id, date, time)

　　game (game id, title, type)

　　Please draw the ER diagram.

　　26. Consider a HollyWood Enterprise that requires modeling information about the different type of people

　　A. involved in the movie production.

　　B. _ Each person should have person ID, name, phone, gender, and address.

　　C. _ There are two main groups of persons: Movie professionals and celebrity. Each movie professionals work on some

　　D. company.

　　E. _ A movie professionals can be either a director or a agent. Each director has her or his popularity and can direct a

　　F. number of movies. Each agent has the agent fee.

　　G. A celebrity can be a movie star, a model, or both. Each movie star has her or his movie style and play in some

　　H. movies. Each model has her or his preferences.

　　I. _ Each movie has the information about the title, released date, and language. J. Draw a EER diagram for the above HollyWood Enterprise.

　　K. The owner of several apartment buildings is interested in a database to manage his L. business.

　　M. ??Buildings have one or more apartments. Every building has an address. N. ??Apartments have apt. nos., and are characterized by their size: 1BR, 2BR, etc.

　　O. ??Tenants lease apartments. Each lease has a lease date and a period of lease. P. Tenant information is his/her name and phone number.

　　Q. ??Each building has a manager. The manager has a name, telephone no. and salary.

　　R. ??Each building has some parking spaces. Some tenants rent the parking spaces.

　　Design an E-R diagram for the above. State any additional assumptions.

　　Convert the above E-R diagram (Q.1) to relational model and write the SQL commands to create the tables for both the entities as well as relationships.

　　27. The following questions are based on a Sporting Goods database described below:

　　customer (id: int, name: string, city: string, country: string, rating: string, sales_rep_id: int )

　　dept(id: int, name: string, region_id: string)

　　sales_rep(id: int, last_name: string, first_name: string, dept_id: int, salary: int) order(id: int, customer_id: int, date_ordered: date, total: int)

　　Write SQL queries for each of the following sub-questions.

　　A. Display the name, city, country and rating of all customers whose number of orders exceeds the “average” number of orders for a customer.

　　B. Display the name of all the departments that have at least one employee.

　　C. Display the first name and last name of all sales representatives who do not have customers.

　　D. Find the countries in which there are no sales representatives. If required, make

　　any assumptions and state them.

　　28. Consider the relation R, which has attributes that hold schedules of courses and sections at a university; R = {CourseNo, SecNo, OfferingDept, CreditHours,

　　CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents}. Suppose that the following functional dependencies hold on R:

　　{CourseNo} ? ?{OfferingDept, CreditHours, CourseLevel}

　　{CourseNo, SecNo, Semester, Year} ??{Days_Hours, RoomNo,

　　NoOfStudents, InstructorSSN}

　　{RoomNo, Days_Hours, Semester, Year} ??{InstructorSSN, CourseNo, SecNo}

　　A. Try to determine which sets of attributes form keys of R. How would you normalize this relation?

　　29. Consider the relational database schema and write the SQL statements according to the this model.

　　Part(PartNo, PartName, ProjNo Price, Weight)

　　Project(ProjNo, ProjName, Location, departNo)

　　Emp(Ssn, Name, Surname, departNo, Addres, salary)

　　Work(ssn, ProjNo, Hour)

　　A. Listing the workers info, according to the PartNo=24 that is used in the one project.

　　B. Listing the project names and locations, according to the employees

　　address includes “Bahcesehir”.

　　C. Alter the emp table and add a new column which is corresponding the

　　emp’s birth date.

　　D. List the emp info, according to the his salaries greater than avarage salary of the emp table.

　　30. Branch(branch-name, branch-city, assets)

　　Account(account-number, branch-name,balance)

　　Depositer(customer-name, account-number)

　　Customer(customer-name, customer-street, customer-city)

　　Loan(loan-number, branch-name, amount)

　　Borrower(customer-name, loan-number)

　　Database schema is given above.

　　A. Find all customers who have both an account and a loan in the bank.

　　B. Find all customers who have a loan at the bank but do not have an account at the bank.

　　C. Find all customers who have both an account and a loan at the Perryridge branch.

　　D. Find all branches that have greater assets than some branches located in Brooklyn.

　　E. Find all loan numbers which appear in the loan relation with null values for amount.

　　31. You have been asked to design a database for the university administration, which records the following information:

　　1. All students necessarily have a unique student ID, a name, and a university email address. Each student is also either an undergraduate or a graduate student.

　　2. Each graduate student has an advisor.

　　3. Each undergraduate student has a major.

　　4. Students take courses. A student may take one course, multiple courses, or no courses.

　　5. Each course has a course number, course name, and days of the week the course is scheduled.

　　6. Each course has exactly one head TA, who is a graduate student.

　　7. Every head TA has an office where he or she holds office hours.

　　A. Draw an ER diagram for this application. Be sure to mark the multiplicity of each relationship of the diagram. Decide the key attributes and identify them on the diagram. Please state all assumptions you make in your answers.

　　B. Translate your ER diagram into a relational schema. Select approaches that yield the fewest number of relations; merge relations where appropriate. Specify the key of each relation in your schema.

　　32. Consider a relation R with five attributes A, B, C, D, and E. The following dependencies are given:

　　AB? C, BC ? D, CD ? E, DE ? A.

　　A. List all keys for R. Do not list superkeys that are not a key.

　　B. Is R in 3NF? Briefly explain why.

　　C. Is R in BCNF? If yes, please explain why. Otherwise, decompose R into relations that are in BCNF.

　　33. The following questions refer to the database schema below: Product(pid, price, color), Order(cid, pid, quantity), Customer(cid, name, age).

　　A. Write a query, in relational algebra, to return the names of customers who order at least one product with color “Red.”

　　B. Write an SQL query, to return the total quantity of products ordered by customers with age greater than 70.

　　C. Write an SQL query, to return the pid(s) of the most ordered product(s) (i.e. the product(s) with the highest total ordered quantities).

　　34. Consider “drinker” database with the following relations.

　　Drinker(drinkerName, street, age)

　　Bar(barName, owner, street)

　　Frequent(drinkerName, barName)

　　We ask you to write queries. Please write simple and non-redundant queries – Note that we will really check if your answers are unnecessarily complex.

　　A. In relational algebra, write a query to return the bars that Sally frequents.

　　B. In relational algebra, write a query to return each drinker who frequents only bars on the same street that he lives.

　　C. In SQL, write a query to return the bars whose frequent drinkers are “young”– in particular, with average age below 37.

　　35. Consider the following relational schema:

　　Account(accountNumber, branchName, balance)

　　Branch(branchName, street, city, assets)

　　Customer(customerSSN, street, city)

　　Deposit(customerSSN, accountNumber,Amount)

　　A. List all the attributes (in the four tables) that are foreign keys and indicate what attributes they are referencing.

　　B. Define a view BigBranch that gives for each branch its branchName, city, and assets. The branch should have more than 50 accounts and the total balance of all accounts is greater than $1,000,000.

　　C. Suppose we want to check that, for each branch, the total balance of all accounts is less than or equal to the assets of the branch. Complete the following SQL statement, by specifying _condition_. Note, by definition, such an “assertion” statement will enforce the _condition_ to hold true at all times.

　　CREATE ASSERTION BalanceCheck CHECK _condition_

　　36. Convert the following unnormalized tables into First Normal Form (1NF) relations :

　　(a) R(A, B, {C, D})

　　(b) R(A, B, {C, D, {E, F}})

　　37. Convert ER diagrams A and B below into relations.