Consider the follow relations’ schemas to answer the following question: Student (sid, name, level, address) Book(bid, title, author) BorrowedBook(sid, bid, date) Draw optimized tree for the next query: SELECT S.name FROM Student S, Book B, BorrowedBook BB WHERE S.sid = BB.sid AND B.bid = BB.bid AND B.author = 'Henry F. Korth' AND S.level between 2 and 4

Consider the follow relations’ schemas to answer the following question: Student (sid, name, level, address) Book(bid, title, author) BorrowedBook(sid, bid, date) Draw optimized tree for the next query: SELECT S.name FROM Student S, Book B, BorrowedBook BB WHERE S.sid = BB.sid AND B.bid = BB.bid AND B.author = 'Henry F. Korth' AND S.level between 2 and 4

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

Suppose you have a relation containing x, y coordinates and names of restaurants. Suppose also that the only query that will be asked are of the following forms. The query specified a point and asks if there is a restaurant exactly at that point. Which type of index would be preferable, R-Tree or B-tree? Why?
For each question in this section, you are required to decompose the given relation into BCNF form and state any new relations created in the process with their functional dependencies and identify any functional dependencies which are lost during the decomposition. You must show your working using the tree method presented in tutorials. Consider the functional dependencies in the order presented in the question. Question 1 R [A, B, C, D, E, F, G, H, I, J] {A} -> {B, C} {B} -> {D, E, F} {C} -> {G, H, I} {H, I} -> {F, J} Question 2 R [A, B, C, D, E, F, G, H] {A, B, C} -> {D, E, F, G} {G, H} -> {A, B, C} {C} -> {H}
Simplify the following query: SELECT ENAME, PNAME FROM EMP, ASG, PROJ WHERE (DUR > 12 OR RESP = "Analyst") AND EMP.ENO = ASG.ENO AND (TITLE = "Elect. Eng." OR ASG.PNO < "P3") AND (DUR > 12 OR RESP NOT= "Analyst") AND ASG.PNO = PROJ.PNO and transform it into an optimized operator tree using the restructuring algorithm.
1. How to make a clustered B-tree on camp_id for the camps table in sql the camps table contains columns; camp_id, camp_name, camp_location, camp_capacity 2. How to make a secondary B-tree on Name and a second one on CID for the customers table in sql the customers table contains columns; CID, name, address, phone#, email, camp_id
Simplify the following query: SELECT ENAME, PNAME FROM EMP, ASG, PROJ WHERE (DUR > 12 OR RESP = "Analyst") AND EMP.ENO = ASG.ENO AND (TITLE = "Elect. Eng." OR ASG.PNO < "P3") AND (DUR > 12 OR RESP NOT= "Analyst") AND ASG.PNO = PROJ.PNO Also transform it into an optimized operator tree using the restructuring algorithm where select and project operations are applied as soon as possible to reduce the size of intermediate relations.
Suppose RR is a relation with attributes A1,A2,…,AnA1,A2,…,An. How many superkeys will RR have if a) The only candidate key is A1A1. b) The only candidate keys are A1A2A1A2 and A1A3A1A3.
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 bank database of Figure 15.14, where the primary keys are underlined. Suppose that a B+-tree index on branch city is available on relationbranch, and that no other index is available. List different ways to handle thefollowing selections that involve negation: σ ¬(branch_city<“Brooklyn” ∨ assets<5000)(branch)
Make a hybrid merge-join variant that works in scenarios where neither relation is physically sorted but has a sorted secondary index on the joined attributes.
True/False Questions Every relationship in an E-R diagram must translate to an individual relation in the relational model. Given an SQL query, there are often multiple ways of writing it in relational algebra. For any relation, it is possible to construct two separate unclustered indexes on different keys. Given a relation that is not in BCNF there is always a unique decomposition into relations that are in BCNF. B+ trees can include duplicate keys.
Consider the following scenario: you have a relation that contains x, y coordinates, and the names of eateries. Assume, further, that the only questions that will be asked will be of the following types. The query specifies a location and inquires as to whether or not there is a restaurant located exactly at that location. If you were to choose between R-Tree and B-tree, which one would you choose? Why?
Exercise 12.4 Consider the following schema with the Sailors relation: Sailors(sid: integer, sname: string, rating: integer, age: real) For each of the following indexes, list whether the index matches the given selection conditions. If there is a match, list the primary conjuncts. A B+-tree index on the search key < Sailors.sid >. σSailors.sid<50,000(Sailors) σSailors.sid=50,000(Sailors) A hash index on the search key < Sailors.sid >. σSailors.sid<50,000(Sailors) σSailors.sid=50,000(Sailors) A B+-tree index on the search key < Sailors.sid, Sailors.age >. σSailors.sid<50,000∧Sailors.age=21(Sailors) σSailors.sid=50,000∧Sailors.age>21(Sailors) σSailors.sid=50,000(Sailors) σSailors.age=21(Sailors) A hash index on the search key < Sailors.sid, Sailors.age >. σSailors.sid=50,000∧Sailors.age=21(Sailors) σSailors.sid=50,000∧Sailors.age>21(Sailors) σSailors.sid=50,000(Sailors) σSailors.age=21(Sailors) (note that the book has a typo for part 4,…

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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