### Relation Schema:#### Relational Algebra Operators:- **σ (Selection)**- **π (Projection)**- **∩ / ∪ / − (Set Operations)**- **⋈ (Join Operation)**#### Relations:- **employee(employee-name, street, city) as E**- **works(employee-name, company-name, salary) as W**- **company(company-name, city) as C**- **manages(employee-name, manager-name) as M**### Queries:1. **Find the names of all employees in the database who earn more than 100k.**2. **Find the names of all employees in the database who live in the same city as the city of their companies.**3. **Find the employee’s name who earns more than his/her manager.**

The question given above is the multipart question. We will solve the first three for you, if you…

Relation Schema: o (Selection) | T (Projection) | N/U7- (Set operations)| M (Join operation) employee(employee-name, street, city) as E works(employee-name, company-name, salary) as W company(company-name, city) as C manages(employee-name, manager-name) as M 1. Find the names of all employees in the database who earn more than 100k. 2. Find the names of all employees in the database who live in the same city as the city of their companies. 3. Find the employee's name who earn more than his/her manager.

Relation Schema: o (Selection) | T (Projection) | N/U7- (Set operations)| M (Join operation) employee(employee-name, street, city) as E works(employee-name, company-name, salary) as W company(company-name, city) as C manages(employee-name, manager-name) as M 1. Find the names of all employees in the database who earn more than 100k. 2. Find the names of all employees in the database who live in the same city as the city of their companies. 3. Find the employee's name who earn more than his/her manager.

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

The following relational schema forms part of an Employees database. The primary keys are underlined and foreign keys are in italics. Job (jobID, jobTitle, minSalary, maxSalary) Dependent (dependentID, firstName, lastName, relationship, employeeID) Department (departmentID, departmentName, locationID) Location (locationID, street, postcode, city, state) Employee (employeeID, firstName, lastName, phoneNumber, salary, jobID, departmentID) Employee employeeID works as jobID at a department departmentID located at locationID. The employee has dependent(s) dependentID. Question 1 Begins Here 1. List employee names, department names, and their dependent names. Also, include employees without any dependents.
A DreamHome database has following tables. Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) PrivateOwner (ownerNo, fName, lName, address, telNo) Which is the correct relational algebra to express the following query? List the city and street of all branches which assistant staff’s salary is not greater than 9000 and age is at least 31. ( ) $$\Pi_{branchNo,street,city}(\sigma_{position='assistant' \wedge salary≤9000 \wedge DOB>'1-Jan-1990'} (Staff \bowtie Branch))$$ ( ) $$\sigma_{branchNo,street,city}(\Pi_{position=’assistant’ \wedge salary≤9000 \wedge DOB>’1-Jan-1990’}(Staff \cap Branch))$$ ( ) $$\sigma_{branchNo,street,city}(\sigma_{position=’assistant’ \wedge salary<9000 \wedge DOB>31}(Staff \bowtie Branch))$$ ( ) $$\Pi_{branchNo,street,city}(\sigma_{position=’assistant’ \wedge salary≤9000 \wedge…
StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…
If you sell a house more than once, it is treated as different "property" for each sale. Draw an entity-relationship diagram and write a database design outline. Q2. Law Office: You are trying to automate the records for your uncle's law firm. Multiple attorneys work for the firm, and for each of them you want to store their first name, last name, date hired, hourly billing rate, and date promoted to partner (blank if the attorney is not a partner). Each attorney has multiple cases, which is identified by a unique "docket number". Each case has a name and description, is worked on by only one attorney, and involves a single client. For each client, you want to store a first name, last name, middle name, phone number, address, city, state, and zip code (assume you do not have a zip-code table). Finally, you want to keep track of the billable hours spent on each case. For a single case, this comprises information that looks like Hours Billed Date 2/24/2005 Description Prepare brief…
Consider the following schema of hotel reservations database: Hotel (Hotel No, Name, City) Room (Room No, Hotel No, Type, Price) Booking (Hotel No, Guest No, Date From, Date_To, Room No) Guest (Guest_No, Name, City) Write RA queries for the following: a) Get the details (names and price) of all hotels in California. b) Get the average price of a room grouped by city. c) Find the hotel name and city of the hotel with the highest priced room. d) Get the hotel names and room numbers of any hotel rooms that have not been booked.
The database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.
The following database is maintained by a bookstore: the database structure is shown below. AUTHOR(AuthorName) BOOK(ISBN, AuthorName, Title, PublisherName, PublicationYear, RetailPrice)AuthorName foreign key to AUTHORPublisherName foreign key to PUBLISHER PUBLISHER(PublisherName) CUSTOMER(CustomerNumber, Name, Address, Phone, Email, CreditCardNumber, ExpiryDate) ORDER(OrderNumber, CustomerNumber, OrderDate)CustomerNumber foreign key to CUSTOMER ORDERDETAIL(OrderNumber, ISBN, QuantitySold, RetailPrice, TotalCost)OrderNumber foreign key to ORDER Write SQL code to implement the following separate queries numbered 1 to 4: 1. Find all orders placed in 2018-2019. 2. Report the following information: ISBN, AuthorName, Title, RetailPrice for all books written byAlexandre Dumas or by Mark Twain, published by ClassicsPublishingCo, with a retail price at least$24.99. 3. Create a table which shows the value of orders by each customer who actually placed an orderin 2019. Group in order by customer…
Create the following tables in your database with the following schema: Table: Employee | Column Name | Type | empId I name | supervisor | salary | int | varchar | | int | int empId is the primary key column for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager. Table: Bonus | Column Name | Type | | empId | bonus | int | int empId is the primary key column for this table. empId is a foreign key to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus. Add the following data to your tables: Input: Employee table: | empId | name | supervisor | salary | | 3 | Brad | null | 4000 | 1000 | 2000 | 4000 | John | 3 | 2 | 4 | Dan | 3 | Thomas | 3