Consider the relation EMPLOYEE(Eid, Name, Did, Salary, Age, Phone). Create indexes to support the following three important queries. Describing these indexes (tree, hash, cluster, etc.) and explaining your reasons are sufficient. You do not need to write the SQL statements. SELECT E.Phone SELECT E.Name SELECT E.Did, AVG (E.Salary) FROM EMPLOYEE E FROM EMPLOYEE E FROM EMPLOYEE E WHERE E.Eid = ... some ID WHERE E.Age < 45 GROUP BY E.Did

Consider the relation EMPLOYEE(Eid, Name, Did, Salary, Age, Phone). Create indexes to support the following three important queries. Describing these indexes (tree, hash, cluster, etc.) and explaining your reasons are sufficient. You do not need to write the SQL statements. SELECT E.Phone SELECT E.Name SELECT E.Did, AVG (E.Salary) FROM EMPLOYEE E FROM EMPLOYEE E FROM EMPLOYEE E WHERE E.Eid = ... some ID WHERE E.Age < 45 GROUP BY E.Did

Database Systems: Design, Implementation, & Management

11th Edition

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:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributesdeclared as not null. Give instances of relations r, s, and t such that in the result of(r natural left outer join s) natural left outer join tattribute C has a null value but attribute D has a non-null value.
If we had another relation, staff, which was union compatible with faculty, what would the following expression return for a result? Πempno (Staff ⋃ Instructor) Select one: a. The employee number of every staff employee and every instructor b. Every employee who is either a member of the staff or an instructor c. All tuples of the instructor relation along with all tuples of the staff relation d. The employee number of all rows in the staff and in the instructor relations
Consider the bank database of Figure 5.21. Write an SQL trigger to carry outthe following action: On delete of an account, for each customer-owner of theaccount, check if the owner has any remaining accounts, and if she does not,delete her from the depositor relation
For each of the following pairs of expressions, give instances of relations thatshow the expressions are not equivalent. σθ(E1 ⟕E2) and E1 ⟕σθ(E2), where θ uses only attributes from E2.
This question is related to the Database:- Suppose you are creating a database for a library management system. Explain how you would make a table for the "books" entity and insert some sample data, and then alter the table to add a new column for the "authorID" attribute and update "authorID" with some data. Finally, make a SQL query to retrieve all the books that were published after the year 2000. Note that this book schema has several columns, including "book_id" as the primary key, "title", "authorN", "publisher", "publication_year", "isbn", "language", and "num_pages". It also includes "available" column that is set to "true" by default and can be used to track the availability of the book. Please don't use handwriting...
So SQL does not suppor functional depency constraints. However, if the database system supports constraints on materialized views and materialized views are maintained immediately, is it possible to enforce the functional dependency constraints in SQL? If it is possible, for a relation r(A,B,C), how can the constraints on materialized views be used to enforce a functional dependency, like B->A?
Consider a relational database for an online hotel booking company. The database consisting ofthe following tables (where the primary keys are underlined): Hotel (hId, hName, hAddress, hCity) Guest(gId, gName, gAddress, gCity) Room(hid, roomNo, type, price) Booking(gId, hId, roomNo, fromDate, year, noOfDays)Where hId and gId are identifiers for the hotels and the guests,and the Booking relation indicated that a guest booked a hotel room for a specified number of days(noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220,Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotelfor 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of theguests.Write the following queries in relational algebra (1) Return thename of guests who have booked a hotel room in NYC in January 2016 (i.e.,fromDate between 01/01 and 01/31 and year=2016).(2) Return thename of guests who have booked a…
TRUE OR FALSE In the Relational Data Model, Referential Integrity Constraint is a special kind/case of Foreign Key Constraint. Multiple Inheritance happens when a class has more than one sub-class. This problem can be observed in Programming Languages as well. Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
I need help with the following Consider a STUDENT relation in a UNIVERSITY database with the followingattributes (Name, Ssn, Local_phone, Address, Cell_phone, Age, Gpa). Note thatthe cell phone may be from a different city and state (or province) from thelocal phone. A possible tuple of the relation is shown below:Name Ssn Local_phone Address Cell_phone Age GpaGeorge Shaw 123-45-6789 555-1234 123 Main St., 555-4321 19 3.75William Edwards Anytown, CA 94539a. Identify the critical missing information from the Local_phone andCell_phone attributes. (Hint: How do you call someone who lives in a dif ferent state or province?)b. Would you store this additional information in the Local_phone andCell_phone attributes or add new attributes to the schema for STUDENT?c. Consider the Name attribute. What are the advantages and disadvantagesof splitting this field from one attribute into three attributes (first name,middle name, and last name)?d. What general guideline would you recommend for…
Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributesdeclared as not null. Are there instances of r, s, and t such that the result ofr natural left outer join (s natural left outer join t) has a null value for C but a non-null value for D? Explain why or why not.
Consider an employee database with two relationsemployee (employee_name, street, city)works (employee_name, company_name, salary) where the primary keys are underlined. Write a function avg_salary that takes a company name as an argument and finds the average salary of employees at thatcompany. Then, write an SQL statement, using that function, to find companieswhose employees earn a higher salary, on average, than the average salary at“First Bank”.
Consider the SQL statement:SELECT city, amountFROM Customers INNER JOIN Orders ONCustomers.customerNumberOrders.customerNumberINNER JOIN payments ONCustomers.customerNumberPayments.customerNumberWHEREcustomer.city = "NYC" and orders.status = "Illustrate a relational algebra query tree for the execution of thestatement.

SEE MORE QUESTIONS

Recommended textbooks for you

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