Consider the following relational schema as part of a university database: Prof(ssn, pname, office, age, sex, specialty, dept_did) Dept(did, dname, budget, num_majors, chair_ssn) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance: Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single- and multiple-attribute index search keys are permitted. Specify the table and attributes you recommend indexing on. Note: If you can search with an index-only plan you should create a composite search key. Please define more than one index if it is necessary. 1. Find the maximum age of professors in each department. 2. List all the department information for departments with professors in a specified age range. 3. Find the name of professors who have a specified research specialty. 4. List names of the departments with the budget more than 100K. 5. List all the information about professors who are chairpersons of specified department ids.

Consider the following relational schema as part of a university database: Prof(ssn, pname, office, age, sex, specialty, dept_did) Dept(did, dname, budget, num_majors, chair_ssn) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance: Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single- and multiple-attribute index search keys are permitted. Specify the table and attributes you recommend indexing on. Note: If you can search with an index-only plan you should create a composite search key. Please define more than one index if it is necessary. 1. Find the maximum age of professors in each department. 2. List all the department information for departments with professors in a specified age range. 3. Find the name of professors who have a specified research specialty. 4. List names of the departments with the budget more than 100K. 5. List all the information about professors who are chairpersons of specified department ids.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Similar questions

3. Assume that relation PROJ of our example database above is horizontally fragmented as follows: PROJO PNOS P2 (PROJ) PROJ₂ = PNO P2 (PROJ) And, the relation ASG is horizontally fragmented as ASGO PNOS P2 (ASG) ASG₂ = σ "P2" <PNOS P3 (ASG) ASG=σPNO>P3 (ASG) 1) Transform the following query into a reduced query on fragments: and 2) Is it better than the generic query? And Why? or Why not? SELECT RESP, BUDGET FROM ASG, PROJ WHERE ASG.PNO PROJ.PNO AND PNAME="CAD/CAM" =
Given the following query, which is represented by a relational algebra expression:Project[Bill Amount, Withdrawal Amount](Select( Withdrawal date=6/19/2019, Join(Account,Account,Account.rid=Account.rid )))All tables are arranged by key. It assumes that we have defined B+ trees on the Account.rid and Withdrawal.date attributes. Our database management system uses the following algorithms to implement contacts:a) contact with nested loop and index andb) contact with sorting and merging.Which contact implementation algorithm reads the fewest blocks from disk? How many blocks does it read? You can only make a rough estimate of the blocks read for the given algorithms.
You are given the following database schema:Sailors(sid, sname, rating, age)Boats(bid, bname, color)Reserves(sid, bid, day)Consider the join on Sailors ▷◁ Reserves and assume that you have the following statistics andlimitations: * You have available 9 pages in main memory.* Sailors: 100000 tuples, 1000 pages; Boats: 2000 tuples, 10 pages; Reserves: 110000 tuples,550 pages 1. Calculate the I/O cost of sorting the Sailors relation?2. Calculate the I/O cost of sorting the Reserves relation?3. Calculate the total I/O cost of this join if we use Sort/Merge join?
Explain the role of indexing structures like B-trees and hash indexes in database systems. When and why might you choose one over the other?
In this hypothetical situation, you have access to a database providing the x, y coordinates and names of all neighbouring eateries. Only the following types of inquiries need to be made: The inquiry investigates into whether or not the specified address is acceptable for a restaurant. Which indexing method, B-tree or R-tree, is superior, and why? Why?
Consider the following relational database schema that contains information about employees and projects to which they are assigned. Emp (eid, lastname, gender, DOB) Assign (eid, pid, hours) Proj (pid, pname, budget, manager_eid) Answer the following questions. (a) Find two superkeys in the Proj table — both of these two superkeys should NOT be same as the primary key. You should use appropriate set notation if the superkey contains multiple attributes. (b) Suppose the Emp and Proj tables have been created appropriately. Define (i.e., create) the Assign table in SQL (Just create only one table.) You should follow all the requirements discussed in the class. (c) Suppose in the Proj table a project has changed pid from P06 to P07. From the perspective of integrity constraints, how should this modification be handled?
Give each of the following methods for storing a relational database two advantages, two disadvantages, and a summary of how they affect the database as a whole. A separate file should be kept for each relationship.
Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.
Assume that a bank's database is safeguarded using snapshot isolation database technology. Do you have any instances of how a nonserializable execution might harm the bank?
Assume, for the sake of argument, that you have access to a database listing the addresses and names of all nearby restaurants. Only the following kind of questions are required: The question seeks to determine whether the given location is suitable for an eating establishment. When comparing B-tree vs R-tree indexing, which one is better and why? Why?
Consider the following shorthand representation of a relational database used by a book publisher to answer the following questions.BRANCH (BRANCH_NUM, COUNTRY, NUM_EMPLOYEES) WRITER (WRITER_NUM, LAST_NAME, FIRST_NAME, BOOK_NUM) BOOK (BOOK_NUM, TITLE, TYPE, PRICE, WRITER_NUM) INVENTORY (BOOK_NUM, BRANCH_NUM, ON_HAND)2.3 List the number of employees for each branch in South Africa. 2.4 List the book code and title of each book of type “Romance”. 2.5 For each type of book list the type and the average price. 2.6 Find the title for each book written by “Bob Builder”. 2.7 List the name of all books in South Africa written by “Hans Anderson“ 2.8 Count the number of books (AS NumOfBooks) written by each author
Consider the following business rule:An employee can manage multiple customers. A customer can be managed by multiple employees. How does this business rule(s) affect the database design? a.) The business rules define that there isn't a need for a relationship between the EMPLOYEE and CUSTOMER entities, as the relationship isn't a strong one. b.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and a 1:M relationship between those two entities. c.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and an M:N relationship between those two entities requiring a bridge table. d.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and a 1:1 relationship between those two entities.

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS