Consider the relation schemas: Computer (c) → Model, Manufacturer, Price, Speed User (u) → Name, ComputerModel Employee (e) → Name, WeeklySalary a. Find all users such that the average price of their computers is greater than 2000 and their speed is at least 1000MHZ. Provide a solution that uses no nested queries but does use HAVING. b. Find every manufacturer who makes at least one computer whose price is more than the average weekly salary of the users who use computers from that manufacturer.

Consider the relation schemas: Computer (c) → Model, Manufacturer, Price, Speed User (u) → Name, ComputerModel Employee (e) → Name, WeeklySalary a. Find all users such that the average price of their computers is greater than 2000 and their speed is at least 1000MHZ. Provide a solution that uses no nested queries but does use HAVING. b. Find every manufacturer who makes at least one computer whose price is more than the average weekly salary of the users who use computers from that manufacturer.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Consider the relation R, which has attributes that hold schedules of courses and sections at auniversity;R = {CourseNo, SecNo, OfferingDept, CreditHours, CourseLevel, InstructorSSN, Semester, Year,Days_Hours, RoomNo, NoOfStudents}.Suppose that the following functional dependencies FDs hold on R:{CourseNo} → {OfferingDept, CreditHours, CourseLevel}{CourseNo, SecNo, Semester, Year} → {Days_Hours, RoomNo, NoOfStudents, InstructorSSN}{RoomNo, Days_Hours, Semester, Year} → {InstructorSSN, CourseNo, SecNo}Try to determine which sets of attributes form keys of R. How would you normalize this relation?
Given:1. The universal relation schema Q(CTHRSG), where C = course, T = teacher, H = hour, R = room, S= student, and G = grade.2. The minimal set of functional dependencies F are assumed: C ->T each course has one teacher HR -> C only one course can meet in a room at one time. HT -> R a teacher can be in only one room at one time. CS -> G each student has one grade in each course. HS -> R a student can be in only one room at one time. 3. The decomposition of the universal schema Q(CTHRSG) based on the algorithm (Lossless Join and Dependency-Preserving Decomposition into 3NF Relation Schema) is:D = {R1, R2, R3, R4, R5}, where R1 = {CT} R2 = {HRC} R3 = {HTR} R4 = {CSG} R5 = {HSR} 4. HS is the key of R, and is a part of R5. Please apply the Lossless Join Testing algorithm to see if the decomposition D is a lossless join decomposition. Please provide testing details step by step when you apply the set of functional dependency for testing.
1. Suppose that for an entity E, there are two candidate keys, A and B. If we make A the primary key, what should we do with B a) make it a SERIAL field b) just treat it like a regular attribute c) use the UNIQUE constraint d) use the NOT NULL constraint 2. Suppose we have the following relational model: Movie(mov_id(PK), title, yr) Person(pid(PK), fname, lname, gender) Acts_In(actor_id(PFK), movie_id(PFK), role) -- actor_id references Person, movie_id references Movie What would be true of the following query? SELECT title, yr FROM Movie m WHERE NOT EXISTS (SELECT * FROM Movie m2 WHERE m1.title = m2.title) a )Movies with an empty string title b) Movies where no other movie has the same title c) Movies where at least one other movie has the same title d) Movies with a null title.
Consider the relation R(A, B, C, D, E) and the set F = {AB → CE, E → AB, C → D}. What is the highest normal form of this relation? Explain your answer.
Consider a relation r(A, B, C), with an index on attribute A. Give an example of a query that can be answered by using the index only, without looking at the tuples in the relation. (Query plans that use only the index, without accessing the actual relation, are called index-only plans.)
Let A be the students {Tom, Jim, Lisa, Mary}, B be the courses {C100, C200, C300, C400}, C be the years {2010, 2011, 2012, 2013}. Let R be enrollment relation {(Tom, C200), (Jim, C400), (Lisa, C100), (Mary, C400)}. Let S be course offering relation {(C100, 2011), (C100, 2012), (C300, 2010), (C300, 2012), (C400, 2013)}. Find the enrollment year relation SoR. You must use arrow diagrams to find the answer.
. Consider the following relation with set of functional dependenciesR(ABCDEF)ABC->D, ABD->E, CD->F, CDF->B, BF->Da) Identify the candidate key(s) in the relation (with proper steps followed).b) Identify which normal form this relation is in and reason for the answer
Consider a relation schema R = {M, N, P, Q, S} and its functional dependency set F = {N → Q,S → M,M →P,P → S}. Is MN a super key of R? Is MN a candidate key of R? (a) MN is not a super key for R; MN is not a candidate key for R.(b) MN is a super key for R; MN is not a candidate key for R.(c) MN is a super key for R; MN is a candidate key for R.(d) MN is not a super key for R; MN is a candidate key for R.
We studied different properties of relations like reflexive, symmetric, antisymmetric, transitive. Please provide one example relation each for each property of the relation. Try to come up with original examples which can be seen practically. (Example, Reflexive relation R = {(a, b) | a and b share same birthday}, Symmetric relation R = {(a, b) | a and b are friends}, Antisymmetric relation R = {(a, b) | a is instructor of b}, Transitive relation R = {(a, c) | a is sibling of b, b is sibling of c, then a is sibling of c}
Consider the following relations:BOOKS(Book#, Primary_author, Topic, Total_stock, $price)BOOKSTORE(Store#, City, State, Zip, Inventory_value)STOCK(Store#, Book#, Qty)Total_stock is the total number of books in stock and Inventory_value is thetotal inventory value for the store in dollars.a. Give an example of two simple predicates that would be meaningful forthe BOOKSTORE relation for horizontal partitioning.b. How would a derived horizontal partitioning of STOCK be defined basedon the partitioning of BOOKSTORE?c. Show predicates by which BOOKS may be horizontally partitioned bytopic.d. Show how the STOCK may be further partitioned from the partitions in(b) by adding the predicates in (c).
Consider the following relation with set of functional dependenciesR(ABCDEF)ABC->D, ABD->E, CD->F, CDF->B, BF->Da) Identify the candidate key(s) in the relation with proper steps followed. Explain in detailb) Identify which normal form this relation is in and reason for the answer. Explain
Consider a relation schema R = {A, B, C, D, E} and its functional dependency set F = {A → B,CD → E,B →D,E → A}. Compute A+ and choose the answer.(a) A+ = AB(b) A+ = ABC(c) A+ = ABCD(d) A+= ABCDE(e) none of the above