CSE 3330-002 Chapter 8 Review Hyeonjun An

pdf

School

University of Texas, Arlington *

*We aren’t endorsed by this school

Course

3330

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

2

Uploaded by skypass359

Report
Hyeonjun An CSE 3330-002 Professor Guizani Feb. 3 rd , 2023 Chapter 8 Review 1. Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. A. List the names of all employees who have a dependent with the same first name as themselves. ⋈ E ← (EMPLOYEE ) ⋈ SSN=ESSN AND FNAME=DEPENDENT_NAME (DEPENDENT) R ← ∏ LNAME, FNAME (E) Result (empty): LNAME FNAME B. Retrieve the names of all employees that work on every project. PROJ_EMPS(PNO,SSN) ← ∏ PNO,ESSN(WORKS_ON) ALL_PROJS(PNO) ← ∏ PNUMBER (PROJECT) EMPS_ALL_PROJS ← PROJ_EMPS ÷ ALLPROJS RESULT ← ∏ LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS) Result (empty): LNAME FNAME C. Retrieve the average salary of all female employees. RESULT(AVG_F_SAL) ← Ʒ AVG_SALARY ( σ SEX = ‘F’ (EMPLOYEE)) Result: AVG_F_SAL 31000 D. List the last names of all department managers who have no dependents. DEPT_MANAGERS(SSN) ← ∏ MGRSSN (DEPARTMENT) EMPS_WITH_DEPENDENTS(SSN) ← ∏ ESSN (DEPENDENT) 2. Specify query 1C using QBE format. FIND average of Salary WHERE Sex = 'F' 3. Consider the AIRLINE relational database schema shown in Figure 5.8. Specify the following queries in relational algebra: A. List all fare information for flight number 'co197'. RESULT ← σ Flight_number = 'CO197' (FARE) B. List the flight numbers and weekdays of all flights or flight legs that depart from Houston Intercontinental Airport (airport code ‘iah’) and arrive in Los Angeles International Airport (airport code ‘lax’).
DEP_HOUS ← σ Departure_airport_code = 'IAH' (FLIGHT_LEG) ARR_LA ← σ Arrival_airport_code = 'LAX' (FLIGHT_LEG) HOUSTOLA ← DEP_HOUS * ARR_LA RESULT ← π Flight_number, Weekdays (HOUSTOLA * FLIGHT) C. Retrieve the number of available seats for flight number ‘co197’ on ‘2009-10-09’. RESULT ← σ LEG_INSTANCE FLIGHT_NUMBER = ‘CO197’ AND DATE = ‘1999-10-09’ SEAT_AVAILABLE ← ∏ NUMBER_OF_AVAILABLE_SEATS (FLIGHT_NUMBER ┬ SUM NUMBER_OF_AVAILABLE_SEATS) 4. Specify query 3C using QBE format. FIND Number_of_available_seats WHERE Flight_number = 'co197' AND Date = '2009-10-09’ 5. Specify the following queries in relational algebra on the database schema given in Exercise 5.14: A. List the Order# and Ship_date for all orders shipped from Warehouse #W2. π Order#, Ship_date (σ Warehouse# = 'W2' (SHIPMENT)) B. Produce a listing Cname, No_of_orders, Avg_order_amt, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer. π Cname, No_of_orders, Avg_order_amt (CUSTOMER ⨝ (π CUSTOMER.Cust#, COUNT(ORDER.Order#), AVG(ORDER.Ord_amt) (ORDER)) C. List the Order# for orders that were shipped from all warehouses that the company has in New York. π Order# (ORDER ⨝ (π Order# (WAREHOUSE ⨝ SHIPMENT) ⋈ City = 'New York' (WAREHOUSE))) 6. Specify query 5B using QBE format. SELECT c.Came, COUNT(o.Order#) AS No_of_orders, AVG(o.Ord_amt) AS Avg_order_amt FROM CUSTOMER c JOIN ORDER o ON c.Cust# = o.Cust# GROUP BY c.Came
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

lab 11.docx
Lab 2.docx
Siddique, In Class Assignment 1 Revised.docx
DAD 220 Database Documentation 6-1 Project 1 Golda Smith.docx
Assignment # 4 Analyzing Classroom Data to Plan Intervention.docx
CSE 3330-002 Chapter 6,7 Review Hyeonjun An.pdf
Lab_Assignment_2 - 2.html
Chapter 2 Homework-1.docx
Midterm_Exam - Solutions.pdf
Homework_Assignment_1 - Solutions.pdf
Homework_Assignment_1 - 5.75.docx
SCS 100 Mod 1 Sasha Goncalves.docx
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
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
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
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