database quiz help

snfqtfnqs
timer Asked: Apr 24th, 2021

Question Description

a database quiz on april 27 EST;

need some help;

read this sample quiz, make sure you are able to solve it all before you pick the order

request:

grade B;

clear writing;

full solutions;

i will review after I receive my grade.

Unformatted Attachment Preview

b. List all candidate keys. c. Normalize the relation to the third Normal Form and show the resulting relations. 3. Consider the following relational schemas: professor(profname, deptname) department(deptname, building) committee(commname, profname) a. Explain what data the following queries retrieve: i) select distinct B.profname from committee A, committee B where A.profname = 'Piper' and B.commname = A.commname ii) select distinct B.profname from committee B where not exists ( (select commname from committee A where profname = 'Piper') except (select commname from committee A where A.profname = B.profname) ) b. Write the following queries in SQL: i) “Find all the committees that integrate both professor ‘Piper’ and professor ‘John’.” ii) “Find the names of all professors who have not offices in any of those buildings that Professor Piper has offices in.” 4. The following relations keep track of airline flight information: Flights(flno: integer, origin: string, destination: string, distance: integer, departs: timestamp, arrives: timestamp, price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary: integer) Write the SQL necessary to implement the following requirements: a. Add a new attribute to the relation Flights that stores the currency of price. b. c. d. e. Define CAD as the default value for the attribute currency created. flno, in relation Flights, should be the primary key aid, in relation Certified, should be a foreign key to Aircraft relation Check that all employees have a salary higher than 2000. 5. Consider again the relations that keep track of airline flight information from que previous question: Flights(flno: integer, origin: string, destination: string, distance, integer, departs: timestamp, arrives: timestamp, price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary: integer) a. Write a SQL query to list in reverse alphabetical order all pilots who are certified to fly some Airbus plane. b. Write a SQL view multi that displays all pilots and the number of airplanes that they can fly. c. Using the SQL view multi above write a query for finding the name(s) and salary(salaries) of the pilot(s) who is(are) certified to fly the largest number of planes. d. Write a SQL query that, for each plane that has at least six pilots, shows the name of the plane and the average salary of the pilots who are certified to fly it. 6. Consider the following relations: Sailors(sid, sname, rating, age) Reserves(sid, bid, day) Boat(bid, bname, bcolor) Write expressions in Relational Algebra (RA), Tuple Relational Calculus (TRC) or Domain Relational Calculus (DRC) as indicated for the following queries: a. (RA) List the colors of boats reserved by Albert. b. (RA) List the id’s of all sailors who have a rating of at least 8 or reserved boat 103. c. (TRC) List the names and age of all sailors who have a rating lower than 3 d. (DRC) List the id’s of all boats that were reserved on 2019-04-28. e. (DRC) List the colors of all boats reserved by Lubber. 7. Select the correct correspondences: A - I can use a RAID level 0 technique because 1 - fault tolerance is important for my application and I need to protect my data even if two disks fail at the same time. B - I can use a RAID level 1 technique because 2 - I’m not concerned with losing data. My main goal is to be able to read and write at high speed. C - I can use a RAID level 5 technique because 3 - I have 6 disks available but I need the capacity of 5 of them which means that I can only spare the space corresponding to a single disk to ensure redundancy. D - I can use a RAID level 6 technique because 4 - I only have two disks available which represent more than the double of the capacity that I need for my application and I’m concerned with being able to recover data if necessary. E - I prefer to use a parity-based approach instead of a mirrored approach because 5 - Fault tolerance is important for my application but I don’t have much space available to spare. 8. Consider the following B+-tree with n=4. a. Show the B+-tree that results after inserting (in the given order) 56, 50, 75, 87, 48. b. Using the B+-tree previously obtained in (a.) show the B+-tree that results after delete (in the given order) 50, 24, 65, 93, 75. 9. Consider the following instance of the relation cars: a. Construct a bitmap index for the attributes Brand and Color for this table. b. Show how bitmap indices can be used to answer the queries: i. “Show the Brand of all cars that are not black” ii. “Give the total number of red Opel cars with a medium risk score.” 10. Consider the following hash function h(x)=x mod 4 for building an hash index. This function allows you to use 4 different buckets. Assume that each bucket can only contain 3 index entries. a. Use this function to build the hash index of the following search key values: 2, 4, 6, 12, 13, 16, 20, 24, 28, 40 b. Given the search key values, is this function a good hash function? Explain your answer.
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Content

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors