Question

In: Computer Science

Consider the following relational database schema:             employee(employee-name, employee-id, street, e-city)             works(employee-

Consider the following relational database schema:

            employee(employee-name, employee-id, street, e-city)

            works(employee-id, company-id, salary)

            company(company-name, company-id, c-city)

            manages(employee-id, manager-id)

Specify the following queries on this database schema using the relational operators we discussed in class. Write your answers on a separate sheet of paper in the order that they are specified below.

  1. Retrieve the name and address of employees who work for First Bank Corporation.
  1. Retrieve the name, street address, and city of residence of all employees who work for Thrifty Industries and earn more than $10,000 per year.

  1. Retrieve the name of all employees who live in the same city as the company for which they work.

  1. Retrieve the name of all employees who live on the same street and in the same city as do their managers.

  1. Retrieve the name of all employees who do not work for First Bank Corporation.

  1. Retrieve the name of all employees who earn more than every employee of Small Bank Corporation.

  1. (Assume that companies may be located in several cities) Retrieve all companies located in every city in which Thrifty Industries is located.

  1. For each company, find the average salary of employees working for that company.

  1. For each city, find the number of employees who work for at least one company in that city.

Solutions

Expert Solution

  1. Retrieve the name and address of emplofyees who work for First Bank Corporation
    SELECT employee_name , street, e-city FROM employee WHERE comapany-name IN (SELECT company-name FROM company WHERE company-name ="First Bank Corporation")
  2. Retrieve the name, street address, and city of residence of all employees who work for Thrifty Industries and earn more than $10,000 per year
    SELECT E.employee-name, street,city FROM employee as E, works as W where E.employee-name = W.employee-name and W.company-name = 'Thrifty Industries' AND salary > 10000
  3. Retrieve the name of all employees who live in the same city as the company for which they work.
    SELECT E.employee-name from employee as E, works as W , Company as C where E.employee-name = W.employee-name and E.city = C.city and W.company-name = C.company-name
  4. Retrieve the name of all employees who live on the same street and in the same city as do their managers.
    SELECT E1.employee-name From employee as E1, Employee as E2, Manages as M WHERE E1.employee-id = M.employee-id and E2.employee-id = M.manager-id AND E1.street = E2.street AND E1.city = E2.city
  5. Retrieve the name of all employees who do not work for First Bank Corporation.
    SELECT employee-name FROM employee WHERE company-name IN (SELECT company-name FROM Works WHERE company-name != 'First Bank Corporation'
  6. Retrieve the name of all employees who earn more than every employee of Small Bank Corporation.
    SELECT employee-name FROM employee WHERE salary > ( SELECT salary FROM works WHERE company-name = 'Small Bank Corporation')
  7. Retrieve all companies located in every city in which Thrifty Industries is located.
    SELECT C.company-name FROM company C WHERE ( SELECT R.city FROM company R WHERE R.company-name = T.company-name) CONTAINS (SELECT S.city FROM company S WHERE S.company-name = 'Thrifty Industries')
  8. For each company, find the average salary of employees working for that company.
    SELECT company-name FROM company GROUP BY company-name HAVING  AVG(salary) FROM works

Related Solutions

• Relational Schema Create a relational database schema consisting of the four relation schemas representing various...
• Relational Schema Create a relational database schema consisting of the four relation schemas representing various entities recorded by a furniture company.   Write CREATE TABLE statements for the following four relation schemas. Define all necessary attributes, domains, and primary and foreign keys. Customer(CustomerID, Name, Address) FullOrder(OrderID, OrderDate, CustomerID) Request(OrderID, ProductID, Quantity) Product(ProductID, Description, Finish, Price) You should assume the following: Each CustomerID is a number with at most three digits, each OrderID is a number with at most five digits,...
Question 2: consider the following library relational database schema Write a program segment to retrieves the...
Question 2: consider the following library relational database schema Write a program segment to retrieves the list of books that became overdue yesterday and that prints the book title and borrower name for each. 1- Use JDBC with Java as the host language
PROBLEM 3: Given the following relational database table: Patients(ID, name, symptom, days_in_hospital) The following insertions are...
PROBLEM 3: Given the following relational database table: Patients(ID, name, symptom, days_in_hospital) The following insertions are performed on the table Patients: Insert record <20, Johnson, cough, 3> Insert record <10, Black, fever, 5> Insert record <30, Miller, fever, 10> Insert record <70, Brown, fatigue, 2> Insert record <60, Grant, headache, 4> Insert record <50, Miller, nausea, 15> Insert record <90, Brown, cough, 8 > Assume each block in the Patients file can store up to 2 patient records. Do the...
Consider the following relational schema and set of functional dependencies. S(A,B,C,D,E,F,G) D → E E →...
Consider the following relational schema and set of functional dependencies. S(A,B,C,D,E,F,G) D → E E → B C → FG BE → AC Is the decomposition of S into S1(E,G,F) and S2(A,B,C,D,G) a lossless join decomposition? Choose one of the following queries as your answer: SELECT ’lossy’; SELECT ’lossless’;
Q3. Convert the conceptual database into a relational database schema. Identify the primary key and foreign...
Q3. Convert the conceptual database into a relational database schema. Identify the primary key and foreign key constraints. Q4. Show all the functional dependencies that should hold among the attributes. Q5. Design relation schemas for the database that are each in 3NF. Specify the primary and foreign key attributes of each relation. Hi expert! Can you help me with the above questions? I'm not asking you to solve my questions but want to find what each questions actually requires me...
Listed below is the relational database schema for an online store(SQL/Java): MEMBER(last_name, first_name, email, password, user,street,...
Listed below is the relational database schema for an online store(SQL/Java): MEMBER(last_name, first_name, email, password, user,street, city, state, zip, card_type, card_no, expiration, name_on_card) book_SALE(listing_no, seller, isbn, condition, price) ORDERS(order_no, buyer, order_date, tot) ITEM(order_no, listing_no) BOOK(isbn, title, author, edition, publisher, keywords) The bold attribute(s) in a relation is the primary key of that relation. The italized attributes in some relations denote foreign keys. The seller attribute in the book_SALE relation is a foreign key to the user attribute in the MEMBER...
Listed below is the relational database schema for an online store(SQL/Java): MEMBER(last_name, first_name, email, password, user,street,...
Listed below is the relational database schema for an online store(SQL/Java): MEMBER(last_name, first_name, email, password, user,street, city, state, zip, card_type, card_no, expiration, name_on_card) book_SALE(listing_no, seller, isbn, condition, price) ORDERS(order_no, buyer, order_date, tot) ITEMS(order_no, listing_no) BOOK(isbn, title, author, edition, publisher, keywords) The bold attribute(s) in a relation is the primary key of that relation. The italized attributes in some relations denote foreign keys. Create/Define the table.
The following tables form part of a database (Flights Database) held in a relational DBMS: employee...
The following tables form part of a database (Flights Database) held in a relational DBMS: employee (empNo, empName, empSalary, empPosition) aircraft (aircraftNo, acName, acModel, acFlyingRange) flight (flightNo, aircraftNo, fromAirport, toAirport, flightDistance, departTime, arriveTime) certified (empNo, aircraftNo) Where:  employee contains details of all employees (pilots and non-pilots) and empNo is the primary key;  aircraft contains details of aircraft and C is the primary key.  flight contains details of flights and (flightNo, aircraftNo) form the primary key.  certified...
Consider the following schema: Student(id, name) Registration(id, code) Course(code, instructor) A) Explain how we can violate...
Consider the following schema: Student(id, name) Registration(id, code) Course(code, instructor) A) Explain how we can violate the foreign key constraint from the id attribute of Registration to the Student relation.   Ans. B)Give the following queries in the relational algebra. 1)What are the names of students registered in IT244?    Ans. 2 )What are the names of students registered in both IT244 and CS141? Ans.                                 3)What are the names of students who are...
Consider the following relational schema: Salerep(sales_rep_ID, name, address, commission, rate) Customer(customer_number, name, address, balance, credit_limit, sales_rep_ID)...
Consider the following relational schema: Salerep(sales_rep_ID, name, address, commission, rate) Customer(customer_number, name, address, balance, credit_limit, sales_rep_ID) Part(part_number, part_description, on_hand, class, warehouse, price) Orders(order_number, order_date, customer_number) Orderlilne(order_number, part_number, number_order) Write SQL statements for the following queries: a) Produce a list showing part_number, part_description, on_hand, and price sorted by part_description. b) List customer’s name followed by order_number, part_description, and number_order. c) List names of customers who have ordered the most expensive item(Hint: Use a nested SQL query to determine thehighest price.) d)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT