CISC437/637 Database Systems Midterm ExamYou have from 2:00 to 3:15pm to complete the following questions. Use the back of the page if you needmore space. Good luck!Multiple Choice (2 point each; 40 total)1. In a relational database management system, a rule that ensures that every record in a table is uniqueis called a ...(a) candidate key constraint(b) referential integrity constraint(c) key constraint(d) participation constraint2. Which of the following is allowed for relations?(a) There can be a field with a non-atomic value.(b) The columns can be in any order.(c) Some of the values of a field can come from a domain that is not the same as the field’s domain.(d) Two rows may be identical.3. Which of the following is used in an E-R diagram to represent many-to-many total participation?(a) An unweighted arrow from an entity set to an entity set.(b) A thick line from an entity set to a relationship set.(c) An unweighted arrow from an entity set to a relationship set.(d) A thick arrow from an entity set to a relationship set.(e) An unweighted line from an entity set to an entity set.4. “Each case is handled by exactly one lawyer.” Which ER diagram best captures this requirement?(a)(b)(c)(d)(e)5. A referential integrity constraint policy that ensures that records with a foreign key are updated whenthe primary key of the referring record in the reference relation is updated is called a(a) incremental delete(b) incremental update(c) cascading delete(d) cascading update6. A “candidate key” is:(a) any set of fields that determines the values of all other fields(b) functionally dependent on non-key attributes(c) an attribute or set of attributes that can be the primary key(d) the primary key of a relation7. Which pair of SQL keywords is used to create and delete a table?(a) CREATE, DROP(b) CREATE, DELETE(c) CREATE, ALTER(d) INSERT, DROP(e) INSERT, DELETE(f) INSERT, ALTER8. Which built-in SQL function computes the number of rows in a table?(a) AVG(b) MAX(c) COUNT(d) SUM(e) MIN9. Which pair of SQL keywords is used to create and delete records in a table?(a) CREATE, DROP(b) CREATE, DELETE(c) CREATE, ALTER(d) INSERT, DROP(e) INSERT, DELETE(f) INSERT, ALTER10. A lossless join decomposition of a relation means(a) none of the attributes are lost(b) no functional dependencies are lost(c) the natural join of the relations in the decomposition produces the original relation(d) the relation is the cross-product of the decomposition(e) no information of any kind is lostThe next five questions are based on the following tables:ssn name firmName firmLoc111-11-1111 Bob Loblaw Dewey, Cheatham, and Howe Boston222-22-2222 Ally McBeal Payne and Feares Los Angeles222-22-3333 Maury Levy Baker and Launder Baltimore333-44-5555 Saul Goodman Recht and Greef Albuquerque555-55-6666 Atticus Finch Baker and Launder Baltimore(a) Lawyers(ssn:string, name:string, firmName:string, firmLoc:string)firmName firmLoc employeesDewey, Cheatham, and Howe Boston 72Dewey, Cheatham, and Howe San Francisco 95Payne and Feares Los Angeles 55Recht and Greef Albuquerque 120Pope and Gentile Milwaukee 100Boring and Leach Los Angeles 66(b) Firms(firmName:string, firmLoc:string, employees:integer)11. A natural join between Lawyers and Firms returns concatenated records with which condition?(a) Lawyers.firmName = Firms.firmName(b) Lawyers.firmLoc = Firms.firmLoc(c) Lawyers.firmName = Firms.firmName AND Lawyers.firmLoc = Firms.firmLoc(d) Lawyers.firmName = Firms.firmName OR Lawyers.firmLoc = Firms.firmLoc12. A left outer natural join between Lawyers and Firms will return how many records?(a) 3(b) 4(c) 5(d) 613. Joining Lawyers to itself on firmLoc will return how many records?(a) 5(b) 6(c) 7(d) 8(e) 2514. Which of the following would find the number of employees that work at law firms employing lawyerswith names starting with ’B’?(a) SELECT F.employees FROM Lawyers L, Firms FWHERE L.firmName=F.firmName AND L.firmLoc=F.firmLoc AND L.name=’B*’;(b) SELECT F.employees FROM Lawyers L, Firms FWHERE L.firmName=F.firmName AND L.name=’B*’;(c) SELECT F.employees FROM Lawyers L, Firms FWHERE L.firmName=F.firmName AND L.firmLoc=F.firmLoc AND L.name LIKE ’B%’;(d) SELECT F.employees FROM Lawyers L, Firms FWHERE L.firmName=F.firmName AND L.name LIKE ’B%’;15. Which of the following is not a valid SQL command?(a) SELECT * FROM Lawyers WHERE firmName LIKE ’% and %’;(b) SELECT firmLoc, COUNT(*) FROM Firms WHERE employees < 100;(c) SELECT COUNT(*) FROM Firms WHERE employees < 100;(d) SELECT firmLoc, COUNT(*) FROM Firms WHERE employees < 100 GROUP BY firmLoc;16. Which of the following is not true?(a) Every relation that’s in BCNF is also in 3NF.(b) Every relation that’s in 3NF is also in 1NF.(c) Relations in 1NF only have atomic values in record fields.(d) Decomposition of a 3NF relation into BCNF preserves functional dependencies.(e) Decomposition of a 3NF relation into BCNF preserves lossless join.17. What is the first step a high-level database application must perform?(a) connect to the database(b) execute queries(c) load the database driver(d) prepare query statements(e) print results18. A is a stored procedure that is executed automatically in connection with data updates.(a) event handler(b) trigger(c) constructor(d) function19. F is a set of functional dependencies on relation ABCDE with F = {A → ABCD, B → C, B → D}.Which of the following is in the closure of F ?(a) CD → B(b) C → D(c) B → CD(d) BCD → A(e) none of the above(f) more than one of the above20. Suppose R is a relation with attributes ABCDE, and F is a set of functional dependencies {A →ABCDE, B → C}. Which of the following is true?(a) ACDE, BC are both in BCNF and preserve lossless-join.(b) ABDE, BC are both in BCNF and preserve lossless-join.(c) ADE, BC are both in BCNF and preserve lossless-join.(d) All of the above.(e) The original relation is already in BCNF and does not need to be decomposed.Short Answer (30 points) Answer the following questions.1. (6 points) Three relational schema are given below. Express each of the provided information needs asa relational algebra query using only projection (π), selection (σ), renaming (ρ), inner joins (./), setunion/intersection (∪, ∩), and division (/).Patient(ssn:string, name:string, address:string, age:integer)Doctor(ssn:string, name:string, specialty:string, experience:integer)PrimaryPhysician(patientSSN:string, doctorSSN:string, firstAppointment:date)(a) Find the ssns of