资源描述
Part 3: Questions and Answers1. Consider the following relational schema:student (student no, _rst name, last name)book (isbn, title, authors, publisher, year)loan (student no, isbn, checkout date, duration)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, creditCardroomNo roomCatroomNo, from guestID, toroomNo, 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 eachpharmacist, 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 StudentB. select * from Enrolment where student=2233456C. select given,family,course from Enrolment, Studentwhere Enrolment.student = Student.idD. select e.code, e.session, c.title, e.mark, e.gradefrom Enrolment e, Course c, Student swhere e.course = c.id and e.student = s.id and s.id = 223456712. 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 1T1 RA. RC. WC. CommitT2 RC. WC.RB. WB. CommitT3 RC. RA. WA. CommitSchedule 2T1 RA. RC. WA. Commit T2 RC. RB. WB. Commit T3 RB. WB. CommitSchedule 3T1 RC. WA. WA. Commit T2 WA. RB. WB. CommitA. 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: ABC, DE, AEG, GDH, IFJ. A. Check whether or not the functional dependencies entail ABDGH ABDHJ ABCG GDHE 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:R ABC123456789SBCD231023116712Write the results of the following queries:A. A,B+C X(R)B. B,C(R) -B,C(S)C. BB,sumD.(S)D. BB,maxD.(R S)18. Consider the following relational schema for movie DVD rental store:customer (customer id, first name, last name)DVD (dvd id, title, genre, director, released year)borrow (customer id, dvd id, checkout date, duration)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 instructorcatalog(cno, ctitle)students(sid, fname, lname, minit)courses(term, secno, cno, score)enrolls(sid, term, secno)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:employee (first name, last name, id no, birthday, gender, salary, supervisor id no, department no)department (department name, department no, manager id no)department locations (department no, department location)project (project name, project no, project location, department no)works on (id no, project no, hours)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 andthe 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.order no date customer no customer name item product no product name unit unit price amount price 0610248 2006-5-30 VICRP Victor Corp. 1 10001042 rice 3 Kg/bag 150 10 1500 0610248 2006-5-30 VICRP Victor Corp. 2 10001072 coke 24 cans/box 480 5 2400 0610249 2006-6-8 DONDI Dondi Corp. 1 10001014 milk 24 bottles 600 9 5400 0610249 2006-6-8 DONDI Dondi Corp. 2 10001051 corn chips 24 bags/box 720 5 3600 0610249 2006-6-15 JENRE Jenren Corp. 1 10001002 beer 24 cans/box 480 10 4800 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:customer table customer id name C98022 Lady Gaga C98145 Lily Allen C98262 Taylor Swift purchase table customer id order no C98022 O1234 C98145 O2234 C98262 O1681 order table order no product no O1234 P1168 O2234 P1234 O1681 P1688 product table product no product name price P1168 MP3 Player 1200 P1234 WII 8000 P1688 DVD Player 3000 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 peopleA. 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 someD. company.E. _ A movie professionals can be either a director or a agent. Each director has her or his popularity and can direct aF. 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 someH. 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 hisL. 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 oforders 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 emps 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 appro
展开阅读全文