关系数据库试题(英文版以及答案)

哈尔滨理工大学 2006－2007学年第一学期考试试题答案 A 卷 考试科目： 数据库系统 考试时间：120分钟 试卷总分100分 考试班级： 题号 一 二 三 四 五 六 七 八 九 十 总分 得分 评卷教师 I 、Choice Questions 1.B 2.C 3.D 4.B 5.D 6.C 7.D 8.D 9.B 10.C II 、 B lank-filling Questions. 1、① condition(s) , ② action(s) 2、A= 180 ;B= 170 3、① logical , ② view or ① view ，② logical 4、 4ms 5、① optimizer ② evaluation engine III 、 A nswer the following questions briefly. 1、 A superkey is a set of one or more attributes that, taken collectively, to identify uniquely an entity in the entity set. Candidate keys are minimal superkeys which no proper subset is a superkey. Primary key is a candidate key that is chosen as the principal means of identifying entities within an entity set. 2、 Database administrator's duties include: (choose any four from the following) ● Schema definition ● Storage structure and access method definition ● Schema and physical organization modification● Granting user authority to access the database● Specifying integrity constraints● Acting as liaison with users● Monitoring performance and responding to changes in requirements3、 a) U2, U3 and U5;b) U1, U3, U4 and U5.4、 result = ADresult = ABCD (A → C and A → B)result = ABCDE (CD → E and CD ⊆ ADBC)result = ABCDEH (CD → H and CD ⊆ ADBCE)Is AD a candidate key?1. Is AD a super key?1 Does AD → R? == Is (AD)+ ⊇ R装订线 班级： 学号：姓名：2.Is any subset of AD a superkey?1Does A→R? == Is (A)+ ⊇ R2Does D→R? == Is (D)+ ⊇ R？？5、The relation schema SCT is not in BCNF. After decomposition the relation schemas are:IV、Complete the following queries.1. a) ∏customer_name (borrower) ⋃∏customer_name (depositor)b) account ←account –σbranch_name = “Perryridge” (account )c) account ←account ⋃{(“Perryridge”, A-973, 1200)}depositor ←depositor ⋃{(“Smith”, A-973)}2. create table account( account_number char(10),branch_name char(15),balance numeric(12,2),primary key (account_number),foreign key (branch_name) references branch,check (balance >=0))3. a) select loan_numberfrom loanwhere branch_name = … Perryridge‟ and amount > 1200b) (select customer_name from depositor)intersect(select customer_name from borrower)c) select branch_name, avg (balance)from accountgroup by branch_namehaving avg (balance) > 1200d) select loan_numberfrom loanwhere amount is null哈尔滨理工大学2006－2007学年第一学期考试试题答案A卷V、Resolve the following questions of designing.1、E-R diagram for the university registrar‟s office：2、The relational database corresponding to the preceding E-R diagram：student( sid, name, program)course_offering(courseno, secno, year, semester, time, room)Instructor(iid, name, department,title)courses(courseno, title, credits, syllabus)enrolls(sid, secno, grade)teaches( iid, secno)requires(couseno, prerequisite, maincourse)。

Database System Principle Final Examination Name:___________ Student ID:__________Scores:________Exam Rules:1) Close book and notes, 100 minutes2) Please write down your name and student ID number NOW.3) If you think a problem is ambiguous, write down your assumptions, argue that they arereasonable, then work on the problem using those assumptions.4) Please write your solutions in the spaces provided on the exam. Make sure yoursolutions are neat and clearly marked. You may use the blank areas and backs of the exam pages for scratch work. Please do not use any additional scratch paper.1. (15 points) ER and Translation to Relational Model:a. [10 points] Create a relational schema that captures this E/R diagram. For everyrelation in your schema, specify the key of that relation.b. [5 points] What is the key for entity " Contract_Emps "? And what is the key for2. (10 points) Schema Refinement:Consider the relation R(A,B,C,D,E) with the following functional dependencies: (A, B)->E, (C, D)->E, A->C, C->A.Is R in BCNF? If not, decompose R into a collection of BCNF relations. Show each step of the decomposition process.3. (20 points, 5 points each) Relational Algebra and SQL Queries: Consider a database schema with the following relations:Student (ssn, name)Prof (ssn, name)Course (number, instructor-ssn, title, credits, room#)Enroll (student-ssn, course#)Room (number, capacity)a.Write a relational algebra query that finds the names of all students who areenrolled in a class taught by “Jones”.b.Write an SQL query that finds the names of all students who are NOT enrolled intwo classes held in the same room.c.Write an SQL query that lists, in alphabetical order(按字母顺序), the title of allcourses either taught by “Smith” OR are taught in room number 444. Do not list duplicate titles.d. Write an SQL query that considers all the courses that have ever been taught by“Brown” and are of 3 credits, and groups them according to title. For each course, the query should compute the average capacity of rooms in which the course has been offered, then return only courses for which this average is more than 20.（请在横线上填入合适表达式完成该查询）SELECT Course.title, AVG (Room.capacity)FROM Prof, Course, RoomWHERE____________________________________________________________________________________________________________________GROUP BY _________________________________________________HAVING__________________________________________________4. (16 points) Transaction ManagementConsider the following sequence of log records:<START S>; <S,A,60,61>;<COMMIT S>; <START T>; <T,A,61,62>; <START U>;<U,B,20,21>; <START CKPT (T,U)>; <T,C,30,31>; <START V>; <U,D,40,41>;<V,F,70,71>; <COMMIT U>;<END CKPT>; <T,E,50,51>; <COMMIT T>;<V,B,21,22>; <COMMIT V>.if there is a crash and the last log record to appear on disk is:a) <T,E,50,51>b) <COMMIT T>a)当日志中的最后一条记录为<T,E,50,51>时，利用日志对数据库进行恢复后，恢复后的下列值应为多少？A is set to ______B is set to ______C is set to ______D is set to ______E is set to ______F is set to ______此时恢复数据库完成后，应在日志文件中填入什么记录？Write ______________and _______________ records on the log.b)当日志中的最后一条记录为<COMMIT T>时，利用日志对数据库进行恢复后，恢复后的下列值应为多少？A is set to ______B is set to ______C is set to ______D is set to ______E is set to ______F is set to ______5．(9 points)Consider the following schedule involving three transactions T1, T2 and T3:a)Draw the precedence graph for this schedule.b)Is this schedule conflict serializable? Why or why not? If it is conflict serializable,give the equivalent serial schedule (just write the order of the transactions).6．(30 points)选择题,请将答案写在下面表格里。

CH1 作业Fill in the blank.1、A data model is a notation for describing data or information. The description generally consists of three parts: 、、.2、and is called the schema for that relation.3、When clearing key (keys), an attribute or list of attributes may be declared or .4、If PRIMARY KEY is used, then attributes in S are not allowed to have as a value for their components.True or false1、The relational model is based on tables.2、SQL value can’t be NULL.3、Either says that no two tuples of the relation may agree in all the attribute(s) on the list.4、No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.5、Like HTML, the opening tag in XML can have atttribute = value pairs.Single choice.1、In the following figure, there are instances of two relations that might constitute part of abanking database. Indicate the following:The attributes of Account relation is.A．acctNo, type, balance B. acctNo, type C. acctNo, balance D. acctNom, type, balance2、How many different ways (considering orders of tuples and attributes) are there to represent a relation instance if that instance has:n attributes and m tuples?A. n!m!,B. (n-1)!m!,C. n!(m-1)!, D n!(m+1)!3、How many different ways (considering orders of tuples and attributes) are there to represent a relation instance if that instance has:Four attributes and five tuples?A.1440,B. 2880,C.576, D 7204、How many different ways (considering orders of tuples and attributes) are there to represent a relation instance if that instance has:Three attributes and three tuples?A. 20,B. 25,C.36, D 48Multiple choice1、In the following figure, there are instances of two relations that might constitute part of abanking database. Indicate the following:The tuples of Account relation are.A. (12345, savings, 12000)B. (34567, checking, 1000),C. (23456, checking, 1000)D. (34567, savings, 25)Essay question1、We suggested that there are many examples of attributes that are created for the purpose of serving as keys of relations. Give some examples.。

数据库原理（双语）习题assignment - of - module02Assignment of Module02Questions:2.1 Discuss each of the following concepts in the context of therelational data model: (a) relation (b) domain (c) attribute (d) tuple(e) degree and cardinality2.2 Discuss the properties of a relation2.3 Discuss the difference between the candidate keys and the primary key of a relation. Example what is meant by a foreign key. How do foreign keys of relations relate to primary key? Give examples to illustrate your answer. (1)Candidate Key (2)Primary Key (3) Foreign Keys2.4 Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.2.5 What is view? Discuss the difference between a view and a base relation.Exercises :The following tables form part of a database held in a relational DBMS: Hotel (hoteNo, hoteName, city) Room (roomNo, hoteNo, type, price)Booking (hoteNo, guestNo, dateFrom, dataTo, roomNo) Guest (guestNo, guestName, guestAddress)Where Hotel contains hotel details and hotelNo is the primary keys;Room contains room details for each hotel and (roomNo, hoteNo) forms the primary key;Booking contains details of bookings and (hoteNo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key.2.6 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.2.7 describe the relations that would be produced by the following relational algebra operations: a) ∏hotelNo(? price>50(Hotel))b) ? Hotel.hotelNo=Room.hotelNo(Hotel × Room)c) ∏hotelNo(Hotel∞Hotel.hotelNo=Room.hotelNo（? price>50(Room)）)d) (? dataeTo≥’1-Jan-2002’(Booking))∝Gueste) Hotel�SHotel.hotelNo=Room.hotelNo(? price>50(Room)) (‘�S’meanssemi-join operation) f) (∏guestNo,hotelNo (Booking∞（Guest)）)÷∏hotelNo(? city=’London’Hotel))2.8(a) List full details of all hotels.(b) List all single rooms with a price below $20 per night. (c) List the names and cities of all guests.(d) List the price and type of all rooms at the Grosvenor Hotel. (e) List the guests currently staying at the Grosvenor Hotel.(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied(g) List the guest details(guestNo, guestName, and guestAddress) of all guests staying at Grosvenor Hotel.2.9 Using relational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price detail. What are the advantage of this view?2.10 Represent, by means of one or more relations, the information contained in a timetable of departures from a railway station. Show the number, time, final destination, category and stops of every departing train.感谢您的阅读，祝您生活愉快。

数据库英语综合测试题16Part 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 basicrelational algebra operations?4.Explain how the division operation can be accomplished by basicrelational algebra operations?5.The database of a research center contains the following three tablesabout employees,projects, and the time spent by the employees on theprojects.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 than60 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 overthis 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 whonever 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 ahotel: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) intoa relational design. Give the relational design as a relational diagramwith arrows to indicate the foreign key relationships. Underline allattributes that correspond to primary keys.10.Consider the following employee database, where the primary keys areunderlined.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 universitystudy: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,coursefrom Enrolment, StudentD.from 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 ahospital: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. CommiSchedule 3A. 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 schedulesD. 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 thefollowing 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 →HEB. 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 thefour characteristics.16. What are the serial schedule, equivalent schedules and serializabletT1 RA. RC. WA. Commi tT2RC. RB. WB. Commi tT3 RB .WB. CommitT1 RC. WA. WA. Commit T2 WA. RB. WB. Commitschedule?17.Let R and S are two relations shown as below:RA B C1 2 34 5 67 8 9SB C D2 3 102 3 116 7 12Write the results of the following queries: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: 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 datafor 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 whereprimary 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.orderno date customernocustomernameitemproduct noproductname unitunitpriceamount price06102 48 2006-5-30VICRPVictorCorp.110001042rice3Kg/bag150 10 150006102 48 2006-5-30VICRPVictorCorp.210001072coke24cans/box480 5 240006102 49 2006-6-8DONDIDondiCorp.110001014milk24bottles600 9 5400where 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 phoneno. 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.0610249 2006-6-8 DON DI Dondi Corp. 2 10001051 corn chips 24bags/box 720 53600 06102492006-6-15 JENR E Jenren Corp. 1 10001002 beer 24cans/box 480 104800customer table customer id name C98022Lady Gaga C98145Lily Allen C98262Taylor Swift purchase table customer id order noC98022 O1234 C98145 O2234 C98262 O1681 order table order no product no O1234 P1168 O2234 P1234 O1681 P1688 product table product no product name pric e P1168 MP3 Player 1200 P1234 WII 8000 P1688DVD Player 300025.Consider The database of a online game company has three relations:player, play, and game for storing information about players who playswhich game. The database schema of the game company is shown asfollows: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 informationabout 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 andcelebrity. Each movie professionals work on somepany.E._ A movie professionals can be either a director or a agent. Each directorhas 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 hasher 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, andlanguage.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 databasedescribed 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 donot 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 ofcourses and sections at a university; R = {CourseNo, SecNo,OfferingDept, CreditHours, CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents}. Suppose that the followingfunctional 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 younormalize this relation?29.Consider the relational database schema and write the SQL statementsaccording 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 theone project.B.Listing the project names and locations, according to the employeesaddress includes “Bahcesehir”.C.Alter the emp table and add a new column which is corresponding theemp’s birth date.D.List the emp info, according to the his salaries greater than avaragesalary 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 anaccount at the bank.C.Find all customers who have both an account and a loan at thePerryridge branch.D.Find all branches that have greater assets than some branches locatedin Brooklyn.E.Find all loan numbers which appear in the loan relation with nullvalues for amount.31.You have been asked to design a database for the universityadministration, 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. Thefollowing 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.EMPLOYEE supervisesEMP#Name 1NSkillsSUPPLIER BRANCHsuppliesS#SName B#BName M N BLocationDateSLocation A B。

习题一、[12分] 用英文解释1、DBMS2、Data Dictionary3、Transaction[10分]二、单项选择题1．There may be instances where an attribute has a set of values for a specific entity. This type of attribute is said to be 【】A．single valued attribute B．multivalued attributeＣ．simple attribute D．composite attribute2．In a particular bank, a loan can belong to only one customer, and a customer can have several loans, then the relationship set from c ustomer to loan is【】A．one to many B．many to many C．many to one D．one to one3．A【】contains metadata─ that is, data about data.A．table B．view C．data dictionary D．trigger【】4．The phrase “greater than at least one” is represented in SQL byA．>all B．<all C．<some D．>some5．In general, all aggregate functions except 【】ignore null values in their input collection.A．sum B．avg C．min D．countby a series of swaps of non-conflicting 6．If a schedule S can be transformed into a schedule S’　e【】instructions, we say that S and S’ arA．non-conflicting equivalent B．conflict equivalentC．non-conflicting serializable D．conflict serializable7．The fundamental operations in the relational algebra are【】。

考试科目： 数据库系统 考试时间：120分钟 试卷总分100分题号I II III IV V 总分 得分 评卷教师I 、Choice questions. (Write your answers onto the answer sheet.)(There are 10questions, totally 20 marks, and each question worth 2 marks.）1. The relationship among Database (DB), Database System (DBS) and DatabaseManagement System (DBMS) is ___A_____. A. “DBS includes DB and DBMS” B. “DBMS includes DB and DBS” C. “DB includes DBS and DBMS” D. “DB is DBS, also as DBMS”2. In an index, ___A_____ is the attribute to set of attributes used to look up records in afile.A. search keyB. structured keyC. sequence keyD. select key3. A _____D___ of an entity set is a set of one or more attributes whose values uniquelydetermine each entity. A. key B. primary key C. candidate key D. super key4. From the following physical storage media, ____D___ is VOLATILE .A. optical storageB. tape storageC. magnetic-diskD. main memory5. Choose the only one INCORRECT description from the followings: __D_____A. Neither tuples nor attributes have order.B. Attributes can appear in any order and the relation is still the same.C. Each value in the database must be a member of some domain.D. Duplicate tuples can exist in a relation.6. There is a relation R(A, B, C ) contains the following data. Which of the descriptionabout functional dependency is CORRECT? ___B_______ A. Functional dependence A → B holds on R. B. Functional dependence BC → A holds on R. C. Functional dependence B → A holds on R.D. Functional dependence A → BC holds on R.7. The right figure shows ___B_____ parallel databasearchitectures.A. ‘Shared memory’B. ‘Shared disk’C. ‘Shared nothing’D. ‘Hierarchical’8. Choose the proper choice to make the following SQL statement to realize query “Findthe names of all customers whose street end with the substring ‘%Dajie’”A B C 2 2 3 2 3 43 3 5 装订线班级：学号：姓名：A. LIKE ‘\%Dajie’SELECT customer_nameB. LIKE ‘\%Dajie%’FROM customerC. LIKE ‘%\%Dajie’WHERE customer_street ____C______D. LIKE ‘%\Dajie%’9.Suppose that there are two relations R(A, B) and S(B, C). Choose the equivalentrelational algebra expression for the following SELECT clause:_C__.A.∏A , B (σC≠‘C56’ (R S)) SELECT A, BB. ∏A , B (σC ≠ ‘C56’ (R S)) FROM RC. R- ∏A , B (σC = ‘C56’ (R S)) WHERE B NOT IN( SELECT BD. R- ∏A , B (σC ≠ ‘C56’ (R S)) FROM SWHERE C=’C56’)10.Choose the only one CORRECT expression about SQL from the followings:____C___.A. (≠ some) ≡ inB. (= all) ≡ not inC. EXISTS r ⇔r ≠ ØD. UNIQUE r ⇔r = ØII、Blank-filling questions.(Write your answers onto the answer sheet.) (There are7 questions and 10 blanks, totally 20 marks, each blank worth 2 marks.)1.To design a trigger mechanism, we must specify the ①under which thetrigger is to be executed; specify the ②to be taken when the trigger executes.①conditions , ②actions2.Given two original values A=300,B=150; compute both the values of Aand B after the transactions T1T2with the right side schedule.A= ;B= .A=225 ; B= 225 3.The basic query process has been list in the following figure, please fill the TWOblanks.1.①parser and translator , ②execution plan4.In physical level, the database is stored as a collection of files. Each file is a sequenceof records , each of which is a sequence of fields.5.Given 0.3ms as the time to transfer one block and 0.2ms as the time for one seek. Ifwe ignore CPU costs and the cost to writing output to disk, the cost is ___ 8 ______ ms for 20 block transfers and 10 seeks for simplicity.6.In database system, indexing mechanisms are used to speed up accessto desired data.7.In distributed database system, data is spread over multiple machines(also referred to as sites or nodes).III、B riefly description questions. (Write your answers onto the answer sheet.) (There are 4 questions, totally 16 marks, and each question worth 4 marks.）1.Please briefly list FOUR duties of a database administrator.2.Please give the definition and the ACID properties of transaction.3.Given the relation schema R = (A, B, C, G, H, I), and the functional dependency F = {A → B, A → C, CG → H, CG → I, B → H}. Explain whether AG → I and CG → HI are the members of F+? Why?AG →I is a member of F+.By augmenting A →C with G, to get AG →CG and then transitivity with CG →I, we can get AG →I.CG →HI is a member of F+.By augmenting CG →I to infer CG → CG I, and augmenting of CG →H to infer CGI →HI, and then transitivity, we can get CG →HI.4.Given the relation schema R = (A, B, C, G, H, I), and the functional dependency F = {A → B, A → C, CG → H, CG → I, B → H}. Compute (AG)+.The processes of computing result of (AG)+ have been shown below.① result = AG② result = ABCG (A →C and A → B)③ result = ABCGH (CG →H and CG ⊆AGBC)④ result = ABCGHI (CG →I and CG ⊆AGBCH)IV、Query questions. (Write your answers onto the answer sheet.) (There are 2 questions with 7 queries. There are totally 28 marks, each query worth 4 marks.)1.Consider the relational database of a banking enterprise with the following relation schemas, where the primary keys are underlined.branch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan (loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)Give an expression in the relational algebra to express each of the following two queries:①Find the names of all customers who have a loan, an account, or both, from the bank.②Insert information in the database specifying that Smith has $4500 in account A-911 at the Perryridge branch.2.Consider the academic database contains three relations as the following 3 tables, andthen give an expression in SQL for each of the following queries.①Tip: Describe primary keys, foreign keys and check constrains if necessary. CREATE TABLE SC( Sno int,Cno int,Grade int,PRIMARY KEY (Sno, Cno),FOREIGN KEY (Sno) REFERENCES S,FOREIGN KEY (Cno) REFERENCES C,CHECK (Grade >= 0))②Find the student numbers and names of the male students, each of whom is older than 22-year-old.SELECT Sno, SNAMEFROM SWHERE AGE>22 AND SEX=‘M’;③Find the student numbers of the students, each of whom has at least chosen two courses.SELECT SnoFROM SC AS X, SC AS YWHERE X.Sno=Y.Sno AND o !=o④Find the names and ages of the male students, each of whom is older than all the female students.SELECT SNAME, AGEFROM SWHERE SEX=‘M’ AND AGE> ALL (SELECT AGEFROM SWHERE SEX=‘F’)⑤List the course name and average grade for all the courses which are taught by teacher LIU.SELECT CNAME, A VG(GRADE)FROM C, SCWHERE C.C#=SC.C# AND TEACHER=‘LIU’GROUP BY C.C#V、D esigning questions. (Write your answers onto the answer sheet.) (There are two questions, totally 16 marks. Question 1 worth 10 marks and question 2 worth 6 marks.)A university registrar’s office maintains data about the following entities:(a)students, including student-id, name, program;.(b)instructors, including id, name, department and title;(c)courses, including course number, title, credits, syllabus and prerequisites;(d)course offerings, including course number, year, semester, section number,instructor(s), timings, and classroom.Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.1.Construct an E-R diagram for the university registrar’s office. A class meets only at one particular place and time; y ou needn’t model a class meeting at different places at different times; also n eedn’t guarantee that the database does not have two classes meeting at the same place and time.2.Design a relational database, which is constructed by relation schemas, corresponding to the preceding E-R diagram with marked primary keys.。

D． specific19._________ takes a value of true if a subquery（子查询）returns an intermediateresults（中间结果) table which contains(包含）one or more rows.（）A． InB． HavingC． ExistsD． Extents20。

A _________is a temporary table（临时表) used in the FROMclause（子句）of an SQL query.( ）A． correlated subquery（相关子查询）B． derived table(导出表）C． view table（视图表)D． None of the above二、判断题（每题1分，共10分，正确的填T，错误的填F，将答案填在下表内）题号 1 2 3 4 5 6 7 8 9 10答案1。

We can define(定义）a relation schema(关系模式) R（A1, A2, A3）where A1 is the primary key（主键) and A3 is a foreign key（外键) that refers to(参考) R。

( ）2.A constraint（约束) is a rule in a database system that can be violated(违反）by users.（）3。

The E—R model is used to construct（构建) a conceptual model（概念模型)。

（）4.The entity integrity rule(实体完整性规则）states that a primary key attribute can benull. （）5.The attribute value in a tuple（元组) that makes up（组成) a foreign key（外键）canbe null。

数据库考试题及答案Introduction:A database is an organized collection of data that can be stored, accessed, and managed. It plays a crucial role in various industries, including finance, healthcare, and e-commerce. In this article, we will explore a set of database exam questions and provide their corresponding answers.1. Define a relational database and explain its components.A relational database is a type of database that organizes data into tables, which consist of rows and columns. Its components include:- Tables: They represent entities or concepts in the real world and consist of rows (tuples) and columns (attributes).- Rows (tuples): Each row represents a single record or instance of an entity, and it contains values for each attribute.- Columns (attributes): They define the characteristics or properties of an entity, such as name, age, or address.- Keys: They are used to uniquely identify each row in a table. Primary keys are unique identifiers for the table, while foreign keys establish relationships between tables.- Relationships: They define associations between tables, such as one-to-one, one-to-many, or many-to-many relationships.2. What is the purpose of SQL and explain its main components.SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. Its main components include:- Data Definition Language (DDL): It is used to define and manage the structure of the database objects, such as creating tables, altering table structures, and deleting tables.- Data Manipulation Language (DML): It is used to manipulate the data within the tables, including inserting, retrieving, updating, and deleting records.- Data Control Language (DCL): It deals with user access and security, including granting and revoking permissions, and managing user roles and privileges.- Transaction Control Language (TCL): It is used to manage the database transactions, including committing or rolling back changes.3. Describe the ACID properties in the context of database transactions.ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the four properties that ensure reliability and integrity in database transactions.- Atomicity: It guarantees that a transaction is treated as a single unit of work, where all its operations are completed successfully, or none of them are executed at all. If any operation fails, the transaction is rolled back, and the database is left unchanged.- Consistency: It ensures that a transaction brings the database from one consistent state to another. The integrity constraints, such as primary key or foreign key constraints, are maintained during the transaction.- Isolation: It ensures that each transaction is executed independently and in isolation from other concurrent transactions. Each transaction should not be affected by other transactions until it is completed.- Durability: It ensures that once a transaction is committed, its changes are permanent and will survive any subsequent system failures. The changes are recorded in a transaction log and can be recovered in case of a crash or restart.4. Differentiate between a primary key and a foreign key.A primary key is a unique identifier for a table that enforces the entity's integrity and ensures data uniqueness. It uniquely identifies each record in the table and is used to establish relationships between tables. Only one primary key can be defined per table.A foreign key, on the other hand, establishes a relationship between two tables. It refers to the primary key of another table and helps maintain referential integrity. A foreign key can have duplicate values and allows for multiple occurrences in a table, unlike a primary key.5. Explain the difference between SQL's JOIN and UNION operators.JOIN and UNION are two different SQL operators used for combining data from multiple tables or queries.- JOIN: It is used to retrieve data by combining rows from two or more tables based on related columns. There are different types of JOIN operations, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, each specifying a different way to combine the tables based on matching or non-matching rows.- UNION: It is used to combine the result sets of two or more SELECT statements into a single result set. The columns and data types of the SELECT statements being combined must match. UNION removes duplicate rows from the final result set, while UNION ALL retains all rows, including duplicates.Conclusion:Understanding the fundamental concepts of databases and their components is crucial for successful data management and manipulation. This article covered a range of exam questions related to databases, providing clear explanations and answers. By grasping these concepts, individuals can enhance their knowledge and skills in utilizing databases effectively.。

习题一、 [12分] 用英文解释1、DBMS2、Data Dictionary3、Transaction[10分]二、单项选择题1．There may be instances where an attribute has a set of values for a specific entity. This type of attribute is said to be 【】A．single valued attribute B．multivalued attributeＣ．simple attribute D．composite attribute2．In a particular bank, a loan can belong to only one customer, and a customer can have several loans, then the relationship set from customer to loan is【】A．one to many B．many to many C．many to one D．one to one3．A【】contains metadata─ that is, data about data.A．table B．view C．data dictionary D．trigger4．The phrase “greater than at least one” is represented in SQL by【】A．>all B．<all C．<some D．>some5．In general, all aggregate functions except 【】ignore null values in their input collection.A．sum B．avg C．min D．count6．If a schedule S can be transformed into a schedule S’ by a series of swaps of non-conflicting instructions, we say that S and S’ are【】A．non-conflicting equivalent B．conflict equivalentC．non-conflicting serializable D．conflict serializable7．The fundamental operations in the relational algebra are【】。

1. A primary key is a field (or group of fields) that uniquely describes each record in thedatabase. T2.Data redundancy improves the integrity of a database.3.SQL is the language used by relational databases to create objects and to manipulateand retrieve data.4. A relational database management system does not include tools for backing up &restoring databases.5.1，一种主键是一个字段（或字段组）唯一地描述了每个记录在数据库中。

Ť6. 2.数据冗余改善了数据库的完整性。

7.3，SQL是关系型数据库用于创建对象，并以操作和检索数据的语言。

8. 4.关系数据库管理系统并不包括用于备份和恢复数据库的工具。

9.An attribute is also known as a row in most databases.10.An association between entities is known as a relationship.11.Integrity constraints limit the number of entities that can be placed in a table ordatabase.12.The Entity-Relationship data model is often used in the physical design phase.13.The concept “relation” in relation model is exactly the same as the concept“relationship” in ER model.14.9.一种属性也被称为一个行中大多数数据库。

关系数据库与SQL Server 2008习题参考答案第1章关系数据库原理【课后习题】一、填空题1．DBMS（数据库管理系统）2．安全性3．实体属性联系4．1:N5．网状模型6．外键7．实体8．型9．每个实体的码10．N端实体的码11．诸实体码的组合12．实体二、选择题1．D2．B3．A4．A5．C6．B7．D8．D9．A三、简答题1．数据是数据库中存储的基本对象。

数据库是长期存储在计算机内、有组织的、可共享的数据集合。

数据库管理系统是位于用户与操作系统之间的一层数据管理软件。

数据库系统是指在计算机系统中引入数据库后的系统构成，一般由数据库、数据库管理系统（及开发工具）、应用系统、数据库管理员和用户构成。

2．数据库系统的特点有：数据结构化、数据的共享性高，冗余度低，易扩充、数据独立性高、数据由DBMS统一管理和控制。

3．数据库管理系统的主要功能有：数据定义功能、数据操纵功能、数据库的运行管理、数据库的建立和维护功能。

4．等值连接是把两个表中的行按照给定的等值条件进行拼接而形成新表，结果列为参与连接的两个表的所有列。

自然连接是一种特殊的等值连接，要求参与连接的两个表有共同属性（列），其结果是在参与操作的两个表的共同属性上进行等值条件连接后，再去除重复的属性后所得的新表。

5．关系的完整性规则包括实体完整性、域完整性和参照完整性三个方面。

实体完整性用于保证数据库表中的每一个元组都是唯一的，要求在任何关系的任何一个元组中，主键的值不能为空值、也不能取重复的值。

域完整性用于保证给定字段中数据的有效性，即保证数据的取值在有效的范围内，要求由用户根据实际情况，定义表中属性的取值范围。

参照完整性用于确保相关联的表间的数据保持一致，要求“不引用不存在的实体”，即：不允许在一个关系中引用另一个关系中不存在的元组。

例如：主表：学生（学号，姓名，性别，专业号，年龄）从表：专业（专业号,专业名）主表的“专业号（外键）”的取值只能为两种情况：若取非空值，则它必须是从表中存在的值；取空值（null），表明尚未给学生分配专业，null不等于0或空字符串。

关系数据库理论考试（答案见尾页）一、选择题1. 关系数据库中的基本概念是什么？A. 数据库、关系、元组、列、行B. 数据库、关系、元组、键、索引C. 数据库、关系、元组、列、主键D. 数据库、关系、元组、列、外键2. 关系数据库中的数据类型有哪几种？A. 数值型、字符型、日期型、逻辑型B. 数值型、字符型、日期型、结构化数据类型C. 数值型、字符型、日期型、二进制数据类型D. 数值型、字符型、日期型、自定义数据类型3. 关系数据库中的关系模型有哪些特点？A. 非结构化数据、二维表格、实体-关系图B. 二维表格、实体-关系图、规范化C. 实体-关系图、规范化、SQL语言D. 二维表格、实体-关系图、SQL语言、数据完整性约束4. 关系数据库中的完整性约束包括哪些类型？A. 实体完整性约束、参照完整性约束、用户定义完整性约束B. 实体完整性约束、参照完整性约束、统计完整性约束C. 实体完整性约束、参照完整性约束、数据完整性约束D. 实体完整性约束、参照完整性约束、业务规则完整性约束5. 关系数据库中的查询语言是什么？A. SQLB. JavaC. PythonD. C++6. 在关系数据库中，如何更新表中的数据？A. 使用INSERT语句B. 使用UPDATE语句C. 使用DELETE语句D. 使用INSERT、UPDATE和DELETE语句7. 在关系数据库中，如何插入新的记录？A. 使用INSERT语句B. 使用UPDATE语句C. 使用DELETE语句D. 使用COPY命令8. 在关系数据库中，如何删除表中的记录？A. 使用DELETE语句B. 使用TRUNCATE语句C. 使用DELETE和INSERT语句组合D. 使用COPY命令9. 在关系数据库中，如何创建新的数据库？A. 使用CREATE DATABASE语句B. 使用CREATE TABLE语句C. 使用ALTER DATABASE语句D. 使用CREATE INDEX语句10. 在关系数据库中，如何修改已存在的数据库结构？A. 使用ALTER DATABASE语句B. 使用ALTER TABLE语句C. 使用DROP TABLE语句D. 使用CREATE TABLE语句11. 关系数据库中的数据类型有哪些？A. 整数、浮点数、字符、日期B. 整数、浮点数、字符、日期、时间戳C. 整数、浮点数、字符、日期、时间戳、逻辑类型D. 整数、浮点数、字符、日期、时间戳、货币类型12. 关系数据库中的关系模型是什么？A. 二维表格模型B. 非结构化数据模型C. 层次模型D. 网状模型13. 关系数据库中的常用操作有哪些？A. 查询、插入、更新、删除B. 查询、插入、更新、删除、事务C. 查询、插入、更新、删除、索引D. 查询、插入、更新、删除、视图14. 关系数据库中的规范化理论主要用于解决什么问题？A. 数据冗余B. 数据不一致性C. 数据完整性D. 数据安全性15. 关系数据库中的隔离级别有哪些？A. 读未提交、读已提交、读已解锁、序列化B. 读未提交、读已提交、读已解锁、更新C. 读未提交、读已提交、读已解锁、提交、回滚D. 读未提交、读已提交、读已解锁、提交、锁定16. 关系数据库中的索引类型有哪些？A. 单索引、复合索引、唯一索引、组合索引B. 单索引、复合索引、唯一索引、覆盖索引C. 单索引、复合索引、唯一索引、哈希索引D. 单索引、复合索引、唯一索引、空间索引17. 关系数据库中的数据完整性的定义包括哪些方面？A. 实体完整性、参照完整性、域完整性B. 实体完整性、参照完整性、属性完整性C. 实体完整性、参照完整性、用户定义完整性D. 实体完整性、参照完整性、统计完整性18. 关系数据库中的备份策略有哪些？A. 完全备份、增量备份、差异备份B. 完全备份、增量备份、差异备份、日志备份C. 完全备份、增量备份、差异备份、镜像备份D. 完全备份、增量备份、差异备份、热备份19. 关系数据库中的数据完整性是什么意思？A. 保持数据的一致性和准确性B. 保证数据的唯一性C. 限制数据的冗余D. 提高数据的可维护性20. 在关系数据库中，什么是外键？A. 用于唯一标识表中的每一行B. 用于唯一标识表中的每一列C. 用于建立两个表之间的联系D. 用于定义数据的约束条件21. 关系数据库中的数据规范化是为了解决什么问题？A. 保证数据的完整性B. 减少数据冗余C. 提高数据的查询效率D. 确保数据的一致性22. 在关系数据库中，什么是触发器？A. 一种存储过程，用于自动执行特定的操作B. 一种约束条件，用于限制数据的行为C. 一种事件，当特定条件满足时自动发生D. 一种数据加密技术23. 关系数据库中的事务是什么？A. 一段程序代码，用于执行特定的操作B. 一组操作的集合，具有原子性、一致性、隔离性和持久性（ACID）C. 一种数据结构，用于存储数据D. 一种查询语言，用于访问数据24. 在关系数据库中，什么是索引？A. 一种数据结构，用于快速查找数据B. 一种约束条件，用于限制数据的行为C. 一种存储过程，用于自动执行特定的操作D. 一种数据加密技术25. 关系数据库中的视图是什么？A. 一种数据结构，用于存储数据B. 一种查询语言，用于访问数据C. 一种虚拟表，具有与选定表相同的列和行D. 一种约束条件，用于限制数据的行为26. 在关系数据库中，什么是关系代数？A. 一种数据结构，用于存储数据B. 一种查询语言，用于访问数据C. 一种理论框架，用于研究关系数据库的设计和处理方法D. 一种存储过程，用于自动执行特定的操作27. 关系数据库中的数据分割是什么？A. 将大型数据分成较小的独立部分，便于管理和分析B. 将大型数据分成多个子集，以便进行并行处理C. 将大型数据分成不同的组，以便进行分组和比较D. 将大型数据分成多个片段，以便进行分布式处理28. 关系模型中有哪些类型的关系？A. 一对一关系B. 一对多关系C. 多对多关系D. 以上都是29. 在关系数据库中，什么是主键？它的主要作用是什么？A. 主键是唯一标识表中每一行的一个或一组列。

哈尔滨理工大学2007－ 2008学年 第一学期考试试题 B 卷 软件学院软件工程系 出题教师： 刘欢 系主任：蒋慧民考试科目： 数据库系统 考试时间：120分钟 试卷总分100分 考试班级： 题号 I II III IV V 总分 得分 评卷教师 I ． Blank-filling questions in DBS situation.（2 marks × 15 = 30 marks ） 1、To illustrate the concept of a data model, we outline two data models in this section : the _________________ model and the _____________ model. 2、Since many database-systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users‟ interactions with the system:________ level, _______ level and view level. 3、A database system provides a language to specify the database schema and a data manipulation language to express database queries and updates. 4、Assume that we have 100 tuples in table A and 100 tuples in table B,then there are tuples in A B. 5、Indices whose search key specifies an order different from the sequential order of the file are called secondary indices,also called indices. 6、There are two modes in which a data item may be locked, shared and . 7、An index record consists of a search-key value and to one or more records with value as their search-key value. 8、There are four different types of database-system users ：_______________,_________________, sophisticated users and specialized users.9、A _____________ is a unit of program execution that accesses and possibly updates various data items.10、A data _________ is a file that stores metadata about the structure of the database.11、A database-management system is a collection of interrelated _________ and a set of ________ to access those data.II ． Briefly-answer questions. ( 5 marks × 4 = 20 marks)1、Given the following relation SCT and two functional dependencies:软件专业班级：学号：姓名：Is the relation schema in BCNF? Why? If it isn‟t, decompose it into BCNF.2、Please list the five duties of a database administrator.3、Please give the definition of “trigger” in database system.4、Explain the distinctions among the terms superkey, candidate key and primary key.III．C omplete the following queries in the relational algebra. ( 4 marks × 3 =12 marks)Consider the relational database of a banking enterprise with the following relation schemas, where the primary keys are underlined.branch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan (loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)1、Find the names of all customers who have a loan, an account, or both, from the bank.2、Delete all account records in the Perryridge branch.3、Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch.IV．Answer the following questions and queries in SQL. ( 4 marks ×5 = 20marks)From the database schema above, give an expression in SQL for each of queries 2, 3, 4 and 5.1.Define the relation …account‟ in SQL.Tip: Describe primary keys, foreign keys and check constrains if necessary. 2.To find all loan number for loans made at the Perryridge branch with loanamounts greater than $1200.3.Find all customers who have both a loan and an account4.Find the names of all branches where the average account balance is morethan $1,200.哈尔滨理工大学2007－2008学年第一学期考试试题B卷5.Find all loan number which appear in the loan relation with null values foramountV．Resolve the following questions of designing. (10marks+8marks =18marks) A university registrar‟s office maintains data about the following entities:(a)students,including student-id,name, sex,age,class.(b)instructors,including id,name,and title.(c)courses,including c-number,title,credits.(d)department,including d-number,name,director,telephone.The relations among the entities are:(a)There are many instructors, students and courses in a department. Ainstructor can work in one department at most. A student can study in one and only one department. A course can only belong to a single department, too.(b)A student can take several courses as elective courses. A course can be takenas an elective course by some students. Grades awarded to students in each course must be enrolled.(c)An instructor can teach a few courses, a course can be taught by someinstructors in each semester.1．Construct an E-R diagram for the university registrar‟s office. Express the mapping constraints.( 10marks)2．Design a relational database corresponding to the preceding E-R diagram.List their primary keys and foreign keys. ( 8marks)。

数据库原理英文选择题1. Which of the following is NOT a characteristic of a relational database?A. Data is stored in tablesB. Data is accessed through SQLC. Data redundancy is encouragedD. Data integrity is maintained2. What is the primary function of a primary key in a database table?A. To ensure data uniquenessB. To establish relationships between tablesC. To provide a means of data encryptionD. To improve query performance3. In a relational database, which of the following represents a relationship between two tables?A. Primary keyB. Foreign keyC. IndexD. Trigger4. Which of the following SQL statements is used to retrieve data from a database?A. SELECTB. INSERTC. UPDATED. DELETE5. What is the purpose of normalization in database design?A. To improve data redundancyB. To eliminate data anomaliesC. To increase data storage spaceD. To decrease query performanceA. A primary key consisting of a single columnB. A foreign key referencing a primary keyC. A primary key consisting of multiple columnsD. A unique key that allows null values7. In SQL, what is the difference between a WHERE clause and a HAVING clause?A. WHERE clause filters rows before grouping, while HAVING clause filters groups after groupingB. WHERE clause is used with SELECT statements, while HAVING clause is used with UPDATE statementsC. WHERE clause is used to sort data, while HAVING clause is used to filter dataD. WHERE clause is used with JOIN operations, while HAVING clause is used with subqueriesA. CREATE TABLEB. ALTER TABLEC. DROP TABLED. SELECT TABLE9. What is the purpose of an index in a database?A. To improve data redundancyB. To enhance data securityC. To speed up query executionD. To reduce data integrity10. Which of the following is NOT a type of database constraint?A. PRIMARY KEYB. FOREIGN KEYC. UNIQUED. VIEW数据库原理英文选择题（续）11. When designing a database, which of the following isa key principle to ensure data consistency?A. Data duplicationB. Data isolationC. Data abstractionD. Data normalization12. In a database, what is the term used to describe the process of converting a query into an execution plan?A. ParsingB. OptimizationC. CompilationD. Execution13. Which of the following SQL statements is used to modify existing data in a database table?A. SELECTB. INSERTC. UPDATED. DELETE14. What is the purpose of a transaction in a database system?A. To store data permanentlyB. To ensure data consistencyC. To improve query performanceD. To create new tables15. Which of the following is a type of join that returns rows when there is at least one match in both tables?A. INNER JOINB. LEFT JOINC. RIGHT JOIND. FULL OUTER JOIN16. In a database, what is the term used to describe the process of retrieving only distinct (unique) values from a column?A. GROUP BYB. ORDER BYC. DISTINCTD. COUNTA. DROP TABLEB. DELETE TABLEC. TRUNCATE TABLED. ALTER TABLE18. What is the purpose of a stored procedure in a database?A. To store temporary dataB. To perform a series of SQL operationsC. To create a new databaseD. To delete a database19. Which of the following is a characteristic of a NoSQL database?A. It uses a fixed schemaB. It is optimized for structured dataC. It is horizontally scalableD. It only supports SQL as a query language20. In a database, what is the term used to describe a collection of related data organized in rows and columns?A. TableB. ViewC. SchemaD. Database数据库原理英文选择题（续二）21. What is the difference between a database and a data warehouse?A. A database stores current data, while a data warehouse stores historical dataB. A database is used for transactional purposes, while a data warehouse is used for analytical purposesC. A database is small in size, while a data warehouse is large in sizeD. A database is structured, while a data warehouse is unstructuredB. To enforce referential integrityC. To format data before it is displayedA. UnionB. JoinC. IntersectionD. Concatenation24. Which of the following SQL keywords is used to limit the number of rows returned a query?A. LIMITB. FETCHC. OFFSETD. ROWS25. What is the purpose of a database schema?A. To define the physical storage of dataB. To define the logical structure of a databaseC. To define the security permissions for usersD. To define the backup and recovery procedures26. Which of the following is NOT a type of database management system (DBMS)?A. Relational DBMSB. Document DBMSC. Hierarchical DBMSD. Sequential DBMS27. In a database, what is the term used to describe a collection of data that is treated as a single unit?A. TupleB. AttributeC. RelationD. Entity28. Which of the following SQL statements is used to create a view in a database?A. CREATE VIEWB. ALTER VIEWC. DROP VIEWD. SELECT VIEW29. What is the purpose of a database index?A. To sort data in ascending or descending orderB. To improve the speed of data retrievalC. To enforce uniqueness of dataD. To hide sensitive data from users30. Which of the following is a characteristic of a distributed database?A. Data is stored in a single locationB. Data is replicated across multiple locationsC. Data access is limited to a single user at a timeD. Data consistency is not maintained across locations。

数据库英文版第六版课后答案Chapter 1: IntroductionQuestions1.What is a database?A database is a collection of organized and structured data stored electronically in a computer system. It allows users to efficiently store, retrieve, and manipulate large amounts of data.2.What are the advantages of using a database system?–Data sharing and integration: A database system allows multiple users to access and share data simultaneously.–Data consistency and integrity: A database system enforces rules and constraints to maintain the accuracy and integrity of the data.–Data security: A database system provides access control mechanisms to ensure that data is accessed by authorized users only.–Data independence: A database system separates the data from the application programs that use it, allowing for easier applicationdevelopment and maintenance.Exercises1.Discuss the advantages and disadvantages of using a database system.Advantages:–Data sharing and integration–Data consistency and integrity–Data security–Data independenceDisadvantages:–Cost: Database systems can be expensive to set up and maintain.–Complexity: Database systems require a certain level of expertise to design, implement, and manage.–Performance overhead: Database systems may introduce some overhead in terms of storage and processing.Overall, the advantages of using a database system outweigh the disadvantages in most cases, especially for large-scale applications with multiple users and complex data requirements.Chapter 2: Relational Model and Relational Algebra Questions1.What is a relation? How is it represented in the relational model?A relation is a table-like structure that represents a set of related data. It is represented as a two-dimensional table with rows and columns, where each row corresponds to a record and each column corresponds to a attribute or field.2.What is the primary key of a relation?The primary key of a relation is a unique identifier for each record in the relation. It is used to ensure the uniqueness and integrity of the data.Exercises1.Consider the following relation:Employees (EmpID, Name, Age, Salary)–EmpID is the primary key of the Employees relation.–Name, Age, and Salary are attributes of the Employees relation.2.Write a relational algebra expression to retrieve the names of all employees whose age is greater than 30.π Name (σ Age > 30 (Employees))Chapter 3: SQLQuestions1.What is SQL?SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It provides a set of commands and statements that allow users to create, modify, and query databases.2.What are the main components of an SQL statement?An SQL statement consists of the following main components:–Keywords: SQL commands and instructions.–Clauses: Criteria and conditions that specify what data to retrieve or modify.–Expressions: Values, variables, or calculations used in SQL statements.–Operators: Symbols used to perform operations on data. Exercises1.Write an SQL statement to create a table called。

数据库模拟试题及答案（英⽂）Exam of Database Technology & Applications1. Describe the three levels and data independence.2. What are key constraints and foreign constraints?3. Explain LEFT JOIN, OUTER JOIN and INNER JOIN.4.For the following relation schema an d sets of FD’s: R is (A, B, C, D, E, F,G) with FD’s A-> B, B-> C, A-> E, CD->G.1) Identify the candidate key(s) for R.2) Identify the best normal form that R satisfies3) Decompose it in 3NF if necessary.5. Explain the ACID properties.6. Notown Records has decided to store information about musicians who perform on its albums (as well as other company data) in a database. The company has chosen to hire you as a database designer.●Each musician that records at Notown has an SSN, a name, an address, and aphone number.●Each instrument used in songs recorded at Notown has a name (e.g., guitar,synthesizer,flute) and a musical key (e.g., C, B-flat, E-flat).●Each album recorded on the Notown label has a title, a copyright date, a format(e.g.,CD or MC), and an album identifier.●Each song recorded at Notown has a title and an author.●Each musician may play several instruments, and a given instrument may beplayed by several musicians.●Each album has a number of songs on it, but no song may appear on more thanone album.●Each song is performed by one or more musicians, and a musician may perform anumber of songs.●Each album has exactly one musician who acts as its producer. A musician mayproduce several albums, of course.1) Defining the completed E-R diagram.2) Defining information for each relation.7. Consider the following relational schema and give T-SQL expressions for the following queries.Sailors(sid, sname, age)Boats(bid, bname, color)Reservers(sid, bid , day)1）Create the table Sailors (sid, sname , age). It includes the domain of values associated with each attribute and integrity constraints.2) Change the attribute sname V ARCHAR(12).3) Delete all tuples in the Sailors relation for sailors whose age is less than 18.4) Find the names of sailors who have reserved a boat on ‘2010-1-1’.5) Find the names of sailors who have reserved a red boat.6) Find the names of sailors who have reserved at least one boat.7) Find the sid of sailors who have reserved a red boat and a green boat.8) Find the names of sailors who have reserved all boats.8. Consider the Buys_computer Relation shown in Figure 1. The first four columns show the age and salary of a potential customer and the Buys_computer column shows whether the person buys a computer. We want to use this data to construct aANSWER1. The three levels are physical level, logical level and view level. Physical level describes all relations that are stored in the database. Logical level summarizes how the relations are actually stored on secondary storage devices. Each view level consists of a collection of one or more views and relations from the conceptual level.There are actually two mappings: the conceptual/internal mapping and the external/conceptual mapping. Theconceptual/internal mapping lies between the conceptual and internal levels. If the structure of the stored database is changed, then the conceptual/ internal mapping must also be changed accordingly so that the view from the conceptual level remains constant. It is thismapping that provides physical data independence for the database.The external/conceptual view lies between the external and conceptual levels. If the structure of the database at the conceptual level is changed, then the external/conceptual mapping must change accordingly so the view from the external level remains constant. It is this mapping that provides logical data independence for the database.2. Primary key constraints assure that the keys of any two records are not same in a table. The primary key constraints enforce the entity integrity of the table.Foreign key constraints control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. This constraint enforces referential integrity by ensuring that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table.3. The LEFT OUTER JOIN includes all rows in the left table in the results, whether or not there isa match on the join column in the right table.FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value.This INNER JOIN is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.4.1）(A,D) is the primary key for R2) R∈1NF3) R1(A,B,E) ,R2(B,C,F),R3(C,D,G)5. Atomicity: This property guarantees that a set of records that are part of a transaction is indivisible. Thus either all operations of the transaction are properly reflected in the database or none are.Consistency: Database consistency is the property that every transaction sees a consistent database instance. Database consistency follows from transaction atomicity, isolation, and transaction consistency.Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions.Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.6.1)2)musician (SSN, m_name, address, phone number.)instrument ( i_name, musical key)album (a_title, copyright date, format , album identifier, SSN)song ( s_title, author, a_title)play(SSN,i_name)produce(SSN, s_title)7.1)CREATE TABLE Sailors(sid INT PRIMARY KEY,sname VARCHAR(10) NOT NULL,age INT CHECK( age BETWWEN 0 AND 100))2) ALTER TABLE Sailors ALTER COLUMN sname V ARCHAR(12)3) DELETE FROM Sailors WHERE age<184) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid WHERE rday='2010-1-1'5) SELECT snameFROM Sailors S JOIN Reserves R ON S.sid=R.sidJOIN Boats B ON B.bid=R.bidWHERE color='red'6) SELECT snameFROM Sailors S JOIN Reserves R ON S.sid=R.sid7) SELECT snameFROM Sailors S1 JOIN Reserves R1 ON S1.sid=R1.sid JOIN Boats B1 ON B1.bid=R1.bid WHERE B1.color='red' AND sid IN(SELECT sid FROM Sailors S2 JOIN Reserves R2 ON S2.sid=R2.sid JOIN Boats B2ON B2.bid=R2.bid WHERE B2.color='green' )8) SELECT sname FROM Sailors S WHERE NOT EXISTS(SELECT * FROM Boats B WHERE NOT EXISTS (SELECT * FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid)) 8.Info(D)= —149log 2(149)—145log 2(145)=0.940 Info age (D)= 145×(—52log 2(52)—53log 2(53)) + 144×（—44log 2(44)—40log 2(40 )）+145×(—53log 2(53)—52log 2(52))=0.694 Gain （age ）= Info(D)—Info age (D)=0.246 Info salary (D)=154 ×(—43log 2(43)—41log 2(41) ) + 156× (—64log 2(64)—62log 2(62)) + 154×(—42log 2(42)—42log 2(42))=0.911 Gain （salary ）= Info(D)—Info salary (D)=0.029。

Exercise 2.2.1aFor relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExercise 2.2.1bFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)Exercise 2.2.1cFor relation Accounts and the first tuple, the components are: 123456 → acctNosavings → type12000 → balanceFor relation Customers and the first tuple, the components are: Robbie → firstNameBanks → lastName901-222 → idNo12345 → accountExercise 2.2.1dFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)Exercise 2.2.1eAn example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)Exercise 2.2.1fA suitable domain for each attribute:acctNo → Integertype → Stringbalance → IntegerfirstName → StringlastName → StringidNo → String (because there is a hyphen we cannot use Integer)account → IntegerExercise 2.2.1gAnother equivalent way to present the Account relation:Another equivalent way to present the Customers relation:Exercise 2.2.2Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in United States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industryto identify vehicles.Exercise 2.2.3aWe can order the three tuples in any of 3! = 6 ways. Also, the columns can be ordered in any of 3! = 6 ways. Thus, the number of presentations is 6*6 = 36.Exercise 2.2.3bWe can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in anyof 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880Exercise 2.2.3cWe can order the three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!Exercise 2.3.1aCREATE TABLE Product (maker CHAR(30),model CHAR(10) PRIMARY KEY,type CHAR(15));Exercise 2.3.1bCREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2));Exercise 2.3.1cCREATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2));Exercise 2.3.1dCREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2));Exercise 2.3.1eALTER TABLE Printer DROP color;Exercise 2.3.1fALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’; Exercise 2.3.2aCREATE TABLE Classes (class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);Exercise 2.3.2bCREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);Exercise 2.3.2cCREATE TABLE Battles (name CHAR(30),date DATE);Exercise 2.3.2dCREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10));Exercise 2.3.2eALTER TABLE Classes DROP bore; Exercise 2.3.2fALTER TABLE Ships ADD yard CHAR(30); Exercise 2.4.1aR1 := σspeed ≥ 3.00 (PC)R2 := πmodel(R1)model100510061013Exercise 2.4.1bR1 := σhd ≥ 100 (Laptop)R2 := Product (R1)R3 := πmaker (R2)makerEABFGExercise 2.4.1cR1 := σmaker=B (Product PC)R2 := σmaker=B (Product Laptop)R3 := σmaker=B (Product Printer)R4 := πmodel,price (R1)R5 := πmodel,price (R2)R6: = πmodel,price (R3)R7 := R4 R5 R6model price1004 6491005 6301006 10492007 1429Exercise 2.4.1dR1 := σcolor = true AND type = laser (Printer)R2 := πmodel (R1)model30033007Exercise 2.4.1eR1 := σtype=laptop (Product)R2 := σtype=PC(Product)R3 := πmaker(R1)R4 := πmaker(R2)R5 := R3 – R4makerFGExercise 2.4.1fR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.hd = PC2.hd AND PC1.model <> PC2.model) R2R4 := πhd(R3)hd25080160Exercise 2.4.1gR1 := ρPC1(PC)R2 := ρPC2(PC)R3 := R1 (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model) R2 R4 := πPC1.model,PC2.model(R3)PC1.model PC2.model1004 1012Exercise 2.4.1hR1 := πmodel(σspeed ≥ 2.80(PC)) πmodel(σspeed ≥ 2.80(Laptop))R2 := πmaker,model (R1 Product)R3 := ρR3(maker2,model2)(R2)R4 := R2 (maker = maker2 AND model <> model2) R3R5 := πmaker(R4)makerBEExercise 2.4.1iR1 := πmodel,speed(PC)R2 := πmodel,speed(Laptop)R3 := R1 R2R4 := ρR4(model2,speed2)(R3)R5 := πmodel,speed (R3 (speed < speed2 ) R4)R6 := R3 – R5R7 := πmaker(R6 Product)makerBExercise 2.4.1jR1 := πmaker,speed (Product PC)R2 := ρR2(maker2,speed2)(R1)R3 := ρR3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed <> speed2) R2R5 := R4 (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R3R6 := πmaker(R5)makerADEExercise 2.4.1kR1 := πmaker,model(Product PC)R2 := ρR2(maker2,model2)(R1)R3 := ρR3(maker3,model3)(R1)R4 := ρR4(maker4,model4)(R1)R5 := R1 (maker = maker2 AND model <> model2) R2R6 := R3 (maker3 = maker AND model3 <> model2 AND model3 <> model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) R6 R8 := πmaker(R7)makerABDEExercise 2.4.2aπmodelσspeed≥3.00PCExercise 2.4.2bLaptopσhd ≥ 100 ProductπmakerExercise 2.4.2cσmaker=Bπmodel,price σmaker=B πmodel,priceσmaker=B πmodel,priceProduct PC Laptop Printer ProductProductExercise 2.4.2dPrinter σcolor = true AND type = laserπmodelExercise 2.4.2eσtype=laptopσtype=PC πmakerπmaker –Product ProductExercise 2.4.2fρPC1ρPC2 (PC1.hd = PC2.hd AND PC1.model <> PC2.model)πhd PC PCExercise 2.4.2gρPC1ρPC2PC PC (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model)πPC1.model,PC2.modelExercise 2.4.2hPC Laptopσspeed ≥ 2.80σspeed ≥ 2.80πmodelπmodel Productπmaker,modelρR3(maker2,model2)(maker = maker2 AND model <> model2)πmakerExercise 2.4.2iPC LaptopProductπmodel,speed πmodel,speed ρR4(model2,speed2)πmodel,speed(speed < speed2 )–makerExercise 2.4.2jProduct PC πmaker,speed ρR3(maker3,speed3)ρR2(maker2,speed2)(maker = maker2 AND speed <> speed2)(maker3 = maker AND speed3 <> speed2 AND speed3 <> speed)makerExercise 2.4.2kπmaker(maker4 = maker AND (model4=model OR model4=model2 OR model4=model3)) (maker3 = maker AND model3 <> model2 AND model3 <> model)(maker = maker2 AND model <> model2)ρR2(maker2,model2)ρR3(maker3,model3)ρR4(maker4,model4)πmaker,modelProduct PCExercise 2.4.3aR1 := σbore ≥ 16 (Classes)R2 := πclass,country (R1)Exercise 2.4.3bR1 := σlaunched < 1921 (Ships)R2 := πname (R1)nameHarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeExercise 2.4.3cR1 := σbattle=Denmark Strait AND result=sunk(Outcomes)R2 := πship (R1)shipBismarckHoodExercise 2.4.3dR1 := Classes ShipsR2 := σlaunched > 1921 AND displacement > 35000 (R1)R3 := πname (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoExercise 2.4.3eR1 := σbattle=Guadalcanal(Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := πname,displacement,numGuns(R3)name displacement numGuns Kirishima 32000 8Washington 37000 9 Exercise 2.4.3fR1 := πname(Ships)R2 := πship(Outcomes)R3 := ρR3(name)(R2)R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of WalesRodneyScharnhorstSouth DakotaWest VirginiaYamashiroExercise 2.4.3gFrom 2.3.2, assuming that every class has one ship named after the class.R1 := πclass(Classes)R2 := πclass(σname <> class(Ships))classBismarckExercise 2.4.3hR1 := πcountry(σtype=bb(Classes))R2 := πcountry(σtype=bc(Classes))R3 := R1 ∩ R2countryJapanGt. BritainExercise 2.4.3iR1 := πship,result,date(Battles (battle=name) Outcomes)R2 := ρR2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date < date2) R2R4 := πship(R3)No results from sample data.Exercise 2.4.4aπclass,countryσbore ≥ 16ClassesExercise 2.4.4bπnameσlaunched < 1921ShipsExercise 2.4.4cπshipσbattle=Denmark Strait AND result=sunkOutcomesExercise 2.4.4dπnameσlaunched > 1921 AND displacement > 35000Classes Ships Exercise 2.4.4eσbattle=Guadalcanal Outcomes Ships(ship=name)πname,displacement,numGunsExercise 2.4.4f Ships Outcomesπnameπship ρR3(name)Exercise 2.4.4gClasses Ships πclass σname <> class πclass–Exercise 2.4.4hClasses Classes σtype=bb σtype=bcπcountry πcountry∩Exercise 2.4.4iBattles Outcomes (battle=name)πship,result,dateρR2(ship2,result2,date2)(ship=ship2 AND result=damaged AND date < date2)πshipExercise 2.4.5The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.Exercise 2.4.6UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples ofthe original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone. IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of thetuples of the original result and maybe the added tuple. If the added tuple does not existin the relation that it is added but does exist in the other relation, then the result set willinclude the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of thetuples of the original result. Suppose we have relations R and S and we are computing R – S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus thedifference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator onlyselects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of the tuplesof the original result and maybe the added tuple. If the added tuple satisfies the selectcondition, then it will be added to the new result. The original tuples are included in thenew result because they still satisfy the select condition. Thus the selection operator ismonotone.Cartesian ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where R has m tuples and S has n tuples. If we add a tuple to R that is not alreadyin R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesianproduct operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuples of the original result and possibly additional tuples. The new tuple can only createadditional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thusthe natural join operator is monotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by aCartesian product followed by a selection on some condition. The new tuple can onlycreate additional tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the thetajoin operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of the original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return asmany tuples as its argument. Thus the renaming operator is monotone.Exercise 2.4.7aIf all the tuples of R and S are different, then the union has n + m tuples, and this number is the maximum possible.The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Then the union has max(m , n) tuples.Exercise 2.4.7bIf all the tuples in one relation can pair successfully with all the tuples in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has zero tuples.Exercise 2.4.7cIf the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.Exercise 2.4.7dAssuming that the list of attributes L makes the resulting relation πL(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of πL(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of the tuples in πL(R) appear in S. Then the difference has max(n–m , 0) tuples.Exercise 2.4.8Defining r as the schema of R and s as the schema of S:1.πr(R S)2.R δ(πr∩s(S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 2133.R – (R –πr(R S))Exercise 2.4.9Defining r as the schema of R1.R - πr(R S)Exercise 2.4.10πA1,A2…An(R S)Exercise 2.5.1aσspeed < 2.00 AND price > 500(PC) = øModel 1011 violates this constraint.Exercise 2.5.1bσscreen < 15.4 AND hd < 100 AND price ≥ 1000(Laptop) = øModel 2004 violates the constraint.Exercise 2.5.1cπmaker(σtype = laptop(Product)) ∩ πmaker(σtype = pc(Product)) = øManufacturers A,B,E violate the constraint.Exercise 2.5.1dThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence: R1(maker, model, speed) := πmaker,model,speed(Product PC)R2(maker, speed) := πmaker,speed(Product Laptop)R3(model) := πmodel(R1 R1.maker = R2.maker AND R1.speed ≤ R2.speed R2)R4(model) := πmodel(PC)The constraint is R4 ⊆ R3Manufacturers B,C,D violate the constraint.Exercise 2.5.1eπmodel(σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price(PC × Laptop)) = øModels 2002,2006,2008 violate the constraint.Exercise 2.5.2aπclass(σbore > 16(Classes)) = øThe Yamato class violates the constraint.Exercise 2.5.2bπclass(σnumGuns > 9 AND bore > 14(Classes)) = øNo violations to the constraint.Exercise 2.5.2cThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(class,name) := πclass,name(Classes Ships)R2(class2,name2) := ρR2(class2,name2)(R1)R3(class3,name3) := ρR3(class3,name3)(R1)R4(class,name,class2,name2) := R1 (class = class2 AND name <> name2) R2R5(class,name,class2,name2,class3,name3) := R4 (class=class3 AND name <> name3 AND name2 <> name3) R3The constraint is R5 = øThe Kongo, Iowa and Revenge classes violate the constraint.Exercise 2.5.2dπcountry(σtype = bb(Classes)) ∩ πcountry(σtype = bc(Classes)) = øJapan and Gt. Britain violate the constraint.Exercise 2.5.2eThis complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:R1(ship,battle,result,class) := πship,battle,result,class(Outcomes (ship = name) Ships)R2(ship,battle,result,numGuns) := πship,battle,result,numGuns(R1 Classes)R3(ship,battle) := πship,battle(σnumGuns < 9 AND result = sunk (R2))R4(ship2,battle2) := ρR4(ship2,battle2)(πship,battle(σnumGuns > 9(R2)))R5(ship2) := πship2(R3 (battle = battle2) R4)The constraint is R5 = øNo violations to the constraint. Since there are some ships in the Outcomes table that are not in the Ships table, we are unable to determine the number of guns on that ship.Exercise 2.5.3Defining r as the schema A1,A2,…,A n and s as the schema B1,B2,…,B n:πr(R) πs(S) = øwhere is the antisemijoinExercise 2.5.4The form of a constraint as E1 = E2 can be expressed as the other two constraints.Using the “equating an expression to the empty set” method, we can simply say:E1– E2 = øAs a containment, we can simply say:E1⊆ E2 AND E2⊆ E1Thus, the form E1 = E2 of a constraint cannot express more than the two other forms discussed in this section.。

Part 1:True or False1. The relational data model was proposed by Dr. Edgar Codd in early 1970¨s.2. Schema normalization not only reduces potential data redundancy but alsoenhances query efficiency.3. SQL stands for Structured Query Language.4. Any relationship satisfying referential integrity constraint will satisfy single valueconstraint as well.5. A schema in BCNF will be in 3NF as well.6. If A ★ B and C ★ D hold, then AC ★ BD also holds.7. Given a relation with attributes A, B, C, D, E : if AB ★ C, BC ★ AD, and D ★ Ehold, then AB is a key.8. SQL is a declarative query language, in which we simply declare what we want,but not how to compute, in formulating a query.9. With the six basic operations (union, difference, selection, projection, product,and renaming),relational algebra is Turing complete. Other operations are just syntactic sugar and can be derived from the basic operations.10. In SQL, a view can be used like a stored relation in any operations.11. In SQL, the value NULL is ignored in any aggregation.12. For any SQL query, there exists a unique translation into relation algebra.13. An aggregate function, e.g., Sum and Avg, returns a value computed from a set ofvalues. Thus, Min and Max are not aggregate, since they only return a single value.14. If two relations are both in BCNF, their join must also be in BCNF.15. When translating an E-R diagram to the relational model, there are multiple waysto translate a sub-class relationship.16. Schema normalization is a technique that will lead to more efficient queryprocessing.17. An E-R diagram with m entities and n relationships will translate to m+n tables.18. A table with two attributes, such as R(A,B), must be in BCNF.19. In relational algebra, join is a derived operator.20. Schema normalization often contributes to enhancement of query processingefficiency.21. For relation R(A,B,C), if A is a key, then the decomposition into R(A,B) and。