数据库考试题型词语释义Homeworkpaper上年试题.pdf

? 1. ? 15?15? ? 2. ? 15?30? ? 3. ?12? ?ER? ? ? ?1?ER? ?2? ? 4. ?24? ?SQL? ? ? ?1? ?2? ?SQL? 5. ?10? ? ?BCNF?3NF? ? ? ?1? ? ? ?2? ? ?3?BCNF?3NF? ?4?BCNF?3NF 6. ?9? ? ? ? ? ? ? ? ?1? ? ?2? (3) ? ?redo, undo? ? ? ? ? ?1? ? ? ?Review Terms ? ? ?2?(?6?) ? ? 2.1 -2.7?6.1 ? 1.? ? ? ? ? ? ? ? ? Superkey Candidate Key Primary Key 2.? Selection Projection join(Natural,inner, outer etc.) Cartesian product Union set-difference intersection rename assignment aggregation ? 1. ?. 2. ? ?3? SQL? ? 3.1-3.10 ? 1? ? 2? ? ?4? ?SQL ? 4.4 ? ?SQL?(Primary key, foreign key, check, unique, not null) ? 1? ? 2? ? ?5? ?SQL ? 5.2 5.3 ? ?(PL/SQL?)? ? ? ?7? ?ER? ? 7.1-7.8 ? ?ER?ER? ?ER? ? 1? 2? ? ? ? 8.1-8.5 ? INF? ?/?/? ? ? ? ?(?)? ? ? ?lossless decomposition? ? ? ?(function depencency preserving)? BCNF? 3NF? ? 1? ? 2? ? ?12? ? ? 12.1-12.7 ? ?, ?(ACID) ? ?(schedule)? ?/? ?/?/?/? ? ?/?/? ?/?/? ? Checkpoint? ?Checkpoint? ? ? ?redo undo? ? ? 1? ? 2? ? ?review items? ? ? ? ? ? 1. ?database system(DS),database management system(DBMS) 2. ? table= relation?column = attribute?domain, atomic domain, row= tuple? relational database, relation schema, relation instance, database schema, database instance; 3. key?: super key, candidate key, primary key, foreign key, referencing relation, referenced relation; 4. ?(relational algebra)?selection, project, natural join, Cartesian product, set operations, union, intersect, set difference ( exceptminus), Rename, assignment, outer join, grouping, tuple relation calculus 5. SQL? DDL? DML?DCL?QL?sql query structure, aggregate functions, nested subqueries, exists(as an operator), unique(as an operator), scalar subquery, assertion, index(indices), catalogs, authorization, all privileges, granting, revoking, grant option, trigger, stored procedure, stored function 6. ?Integrity constraints, domain constraints, referential integrity constraints 7. ?(ER ?)? Entity-Relationship data model, ER diagram, composite attribute, single-valued and multivalued attribute, derived attribute, binary relationship set, degree of relationship set, mapping cardinality, 1-1, 1-m, m-n relationship set (one to one, one to many, many to many), participation, partial or total participation, weak entity sets, discriminator attributes, specialization and generalization 8. ?functional dependence, normalization, lossless join (or lossless) decomposition, First Normal Form (1NF), the third normal form (3NF), Boyce-codd normal form (BCNF), R satisfies F, F holds on R, Dependency preservation, Trivial, closure of a set of functional dependencies, closure of a set of attributes, Armstrongs axioms, reflexivity rule, augmentation rule, transitivity, restriction of F to Ri?canonical cover, extraneous attributes, decomposition algorithm. 9. ?transition, ACID properties, concurrency control system, recovery system, transition state, active, partially committed, failed, aborted, committed, terminated, schedule, conflict of operations, conflict equivalence, conflict serializablity, serializablity order, cascading rollback, locking protocol, shared-mode lock (S-lock), exclusive-mode lock (X-lock), compatibility, 2-phase locking protocol, intention lock, timestamp, recovery scheme, log, log-based recovery, deferred modification, immediate modification, checkpoint. Figure 3.20 3.9 Consider the relational database in Figure 3.20, where the primary keys are underlined. Give an expression is SQL for each of the following queries: a) Find the names and cities of residence of all employees work for “First Bank Corporation”.(= 2.12 a) b) Find the names, street address and cities of all employees who work for “First Bank Corporation” and earn more than $10,000. (= 2.12 b) c) Find all employees in the database who do not work for “First Bank Corporation”. d) Find all employees in the database who earn more than each employee of “First Bank Corporation”. e) Assume that the companies may be located in several cities, find all companies located in every city in which “Small Bank Corporation” is located. f) Find 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”. You can get 90% score, if working out five out of the seven items (5/7). You can get 100% score, if accomplish all these seven items. Homework 3 Entity Store is associated with store number, store name and address etc. Entity commodity is associated with commodity number, commodity name, specification and price. Entity employee is associated with employee no, employee name, sex and performance. Each commodity can be sold in many stores and each store may sold many kinds of commodity. For each commodity in a certain store, the sale quantity must be recorded per month. For each store, there are many employees. For each employee, he can works for only one store. For each employee who works in a certain store, salary and work period must be recorded. (1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assumptions if any. (2) Translate your ER diagram into relational database schemas, underline primary keys of each relation, and label foreign keys if it has. You can write your answers in the following format: “R(a1, a2, a3, a4), foreign key:a4 refer to R(b1)”. 5 ? ? ? -?-? ? ? ? ? 2014-2015?=?=?1 ? ? Database system ? )? )? ? ? ? ? ? 120 ? ? ? =? ? ? 11 ? ? ? ? ? ? ? Instructions to candidates: 1. Write your name, student number and class on both the question papers and the answer papers. 2. DO NOT write your answers on the question papers. Write them ALL ON THE ANSWER PAPERS. 3. Write your answers in either Chinese or English. 4. Hand in all papers (both the question papers and the answer papers). Question 1 (15 points) Question 2 (30 points) Question 3 (12points) ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ? C C C D B A B B A A B C B D B ? ? ? ? ? 6 hospital(ID, name, location, rank, capacity), primary key: ID, doctor(ID, name, age, skill, hospitalID, salary), primary key: ID, foreign key: hospitalID refer to hospital(ID) patient (ID, name, age, sex, address), primary key: ID, livein(patientID, hospitalID) primary key: (hospitalID, PatientID), foreign key hospitalID refer to hospital(ID)? foreign key patientID refer to patient (ID) Question 4 24 points 1? 1) ()() 1970-1-15000NameANDSalary Employee = 0), PRIMARY KEY (Pnumber, E-ID, Wdate), FOREIGN KEY (E-ID) REFERENCES Employee(ID), FOREIGN KEY (Pnumber) REFERENCES Project(Pnumber), ); 2) UPDATE Employee SET Salary=Salary1.05 WHERE ID IN (SELECT E-ID FROM Project natural join Works_on WHERE Pname=Network); 3) SELECT Name FROM Employee, Department WHERE ID=MrgID AND ID NOT IN (SELECT E-ID FROM Child); 4) SELECT Name FROM Employee ? ? ? ? ? 7 ? ? ? WHERE Dnumber=5 AND ID IN (SELECT E-ID FROM Project natural join Works_on WHERE Pname=X) AND ID IN (SELECT E-ID FROM Project natural join Works_on WHERE Pname=Y); 5) SELECT Pnumber, Pname, SUM(Hours) FROM Project NATURAL JOIN Works_On WHERE Wdate BETWEEN 2009-10-1 AND 2009-10-31 GROUP BY Pnumber, Pname; Question 5 10 points 1 A+=ABCDE, C+ =C, E+=E 2. AF is a candidate key of R 3. Yes F equivalent to A BC, BC E, B D, D A 4. the highest normal form of R is 1NF. The reason lies in that (1) it is not in BCNF, for A is not a super key but A BCD. (2) It is not in 3NF for all candidate keys are: DF, AF, BF, so that primary attributes are ABDF. To A BC, A is not a super key and c is not included in any candidate key therefore it violate rules of 3NF. (3) all attribute are atomic. Question 6 9 points 1. conflict operation pairs ? W2(B)R1(B) (2) W2(B)R3(B) (3)W2(B)W3(B) (4) R1(B)W3(B) (5) R2(B) W3(B) (6) R4(D) W1(D) 2. S= R1(A) R2(B)R3(A)R2(C)R4(D)W2(B)R1(B)W1(D)R3(B)W3(B)W2(C) ? R2(B)R1(A) R2(C) R3(A) W2(B) R4(D) R1(B)W1(D)R3(B) W2(C)W3(B) ? R2(B) R2(C)R1(A) W2(B) R3(A) R4(D) R1(B)W1(D) W2(C)R3(B) W3(B) ? R2(B) R2(C) W2(B) R1(A) R4(D) R3(A) R1(B) W2(C)W1(D) R3(B) W3(B) ? R2(B) R2(C) W2(B) R4(D)R1(A) R1(B) R3(A) W2(C) W1(D) R3(B) W3(B) ? R2(B) R2(C) W2(B) R4(D) R1(A) R1(B) W2(C) R3(A) W1(D) R3(B) W3(B) ? R2(B) R2(C) W2(B) R4(D) R1(A) W2(C)R1(B) W1(D) R3(A) R3(B) W3(B) ? R2(B) R2(C) W2(B) R4(D) W2(C)R1(A) R1(B) W1(D) R3(A) R3(B) W3(B) ? R2(B) R2(C) W2(B) W2(C)R4(D) R1(B) W1(D) R3(A) R3(B) W3(B) 3. it is a conflict serializable schedule. The equivalent serial schedules are: T2, T4, T1, T3 or T4, T2, T1, T3 ? ? ? ? ? ? ? ? ? ? 1 ? ? ? -?-?A? ? 2014-2015?=?=? ? ? ? ? ? ? ? ? ? ? ?120 ? ? ? =? ? ? 11 ? ? ? ? ? ? ? Question 1: true-false question (15 points) For each of the following statements, indicate whether it is TRUE or FALSE (Using ? for TRUE and ? for FALSE). You will get 1 point for each correct answer, -0.5 point for each incorrect answer, and 0 point for each answer left blank. Be sure to write your answer in the answer sheet. 1. A primary key is a field (or group of fields) that uniquely describes each record in the database. 2. Data redundancy improves the integrity of a database. 3. SQL is the language used by relational databases to create objects and to manipulate and retrieve data. 4. A relational database management system does not include tools for backing up Q2: SELECT * FROM R WHERE a NOT LIKE %; A.Q1 and Q2 produce the same answer. B. The answer to Q1 is always contained in the answer to Q2. C. The answer to Q2 is always contained in the answer to Q1. D. Q1 and Q2 produce different answers. 5. The Entity-Relationship data model is A. DBMS dependent B. DBMS independent C. both A and B D. neither A nor B 6. In SQL, an UPDATE statement without a WHERE clause: A. Updates every row in a table. B. Updates no rows in a table. C. Updates every column in a table. D. Results in a Cartesian product. 7. If a course can be taught by many teachers, and a teacher can teach only one course, then the mapping cardinality from course to teacher is A. one-to-one B. one-to-many C. many-to-one D. many-to-many 8. If there is a many-to-one relationship between entity A and B, then A. there exists a functional dependency from the primary key in B to the primary key in A, i.e., PK(B) ? PK(A). B. there exists a functional dependency from the primary key in A to the primary key in B, i.e., PK(A) ? PK(B). C. both A and B. D. neither A nor B 9. If a functional dependency AB?R holds on relation R(A, B, C), then (A, B) is definitely a _ of R. A. super key B. primary key C. candidate key D. foreign key 10. A relational schema R is in _ if the domains of all attributes of R are atomic A. 1NF B. 3NF C. BCNF D. 4NF 11. Which one of the following statement is true? A. 3NF is more strict than BCNF B. 4NF is more strict than BCNF C. 1NF is more strict than BCNF D. BCNF is the most strict normal form 12. If a transaction Ti has obtain an exclusive lock on data item Q, then transition Tj can _. A. obtain an exclusive lock on data item Q B. obtain a shared lock on data item Q C. wait for lock granting on data item Q D. read or write Q without a lock 13. If both and appear in the log after the nearest checkpoint to system crash, then transaction Ti must be _ A. undone B. redone C. deleted D. Neither A or B 3 ? ? ? 14. _ is the final state in a life cycle of a transaction. A. committed B. aborted C. failed D. A or B 15. in 2PL protocol, at stage, A transaction may obtain locks, but may not release locks. A. Shrinking phase B. Growing phase C. Committed D. Aborted Question 3 (12 points) Consider the following database requirement: A hospital has properties like ID, name, location, rank, capacity. A doctor can be described by ID, name, age, skill. A patient has properties like ID, name, age, sex, address. The above three entities must satisfy some constraints: Each doctor can be unemployed or employed by one hospital. If a doctor is employed, his salary needs to be recorded in the database. A patient can go to many hospitals. 1. Draw ER diagram to illustrate the above database requirement 8 points. 2. Translate your ER diagram into relational database schemas, and point out the primary keys and foreign keys. You can write your answers in the following format: “R(a1, a2, a3, a4), primary key: a1, foreign key: a4” 4 points. Question 4. (24 points) The following five tables are for a company management system: EMPLOYEE (ID, Name, Birthday, Address, Sex, Salary, Dnumber) DEPARTMENT (Dnumber, Dname, MgrID ) PROJECT (Pnumber, Pname, Pcity) WORKS_ON (Pnumber, E-ID, Wdate, Hours) CHILD (E-ID, CHD-ID, CHD_name, Sex, Birthday) 1. Based on the giving relations, Specify the following queries using relational algebra (3 points for each). 1) List the names of all employees with birthday earlier than 1970-1-1 and salary less than $5000. 2) List the names of all employees who have a child. 3) List the cities and the total number of projects which are located on same city. 2. Specify the following operations in SQL(3 points for each). 1) Define the table WORKS_ON, declare Pnumber, E-ID, Wdate as the primary key, Pnumber as the foreign key referencing the primary key of p