Scribd Logo
Upload

Welcome to Scribd!

    • 59 views

    Database Management System 13: Query Using Relational Algebra

    Uploaded by

    089ASHUTOSH PATI
    This document discusses using relational algebra to write queries on various database relations. It provides examples of queries written using operations like selection, projection, join, set difference, etc. on relations like Employee, Sailors, Shipment, etc. It shows how to find employees working on projects, sailors reserving boats, shipments details, customers details, and more using relational algebra operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Database Management System 13: Query Using Relational Algebra

    Uploaded by

    089ASHUTOSH PATI
    59 views10 pages
    This document discusses using relational algebra to write queries on various database relations. It provides examples of queries written using operations like selection, projection, join, set difference, etc. on relations like Employee, Sailors, Shipment, etc. It shows how to find employees working on projects, sailors reserving boats, shipments details, customers details, and more using relational algebra operations.

    Original Title

    DBMS-13

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses using relational algebra to write queries on various database relations. It provides examples of queries written using operations like selection, projection, join, set difference, etc. on relations like Employee, Sailors, Shipment, etc. It shows how to find employees working on projects, sailors reserving boats, shipments details, customers details, and more using relational algebra operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    59 views10 pages

    Database Management System 13: Query Using Relational Algebra

    Uploaded by

    089ASHUTOSH PATI
    This document discusses using relational algebra to write queries on various database relations. It provides examples of queries written using operations like selection, projection, join, set difference, etc. on relations like Employee, Sailors, Shipment, etc. It shows how to find employees working on projects, sailors reserving boats, shipments details, customers details, and more using relational algebra operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 10

    Query Using Relationa

    Algebra

    Chittaranjan Pradhan

    Database Management Query Using


    Relational Algebra

    System 13
    Query Using Relational Algebra

    Chittaranjan Pradhan
    School of Computer Engineering,
    KIIT University
    13.1
    Query Using Relationa
    Query Using Relational Algebra Algebra

    Chittaranjan Pradhan

    Employee Database Query Using


    Relational Algebra
    Emp(empNo,name)
    Project(projectNo,pName,manager)
    Assigned_To(projectNo,empNo)

    Query: Find empNo of employees working on project ’comp01’


    π empNo (σ projectNo=0 comp010 (Assigned_To))

    Query: Find details of employees working on project ’comp01’


    π empNo,name (σ projectNo=0 comp010 (Emp n Assigned_To))
    o

    Query: Obtain the details of employees working on the database


    project
    π empNo,name (σ pName=0 database0 (Emp n Assigned_To o
    o n Project))

    13.2
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query Using
    Relational Algebra

    Query: Find the details of employees working on the ’comp01’


    and ’comp02’ projects
    π empNo,name (σ projectNo=0 comp010 ∧projectNo=0 comp020 (Emp o
    n
    Assigned_To))

    Query: Find the empNo who don’t work on project ’comp01’


    π empNo (Assigned_To) - π empNo (σ
    projectNo=0 comp010 (Assigned_To))

    This query can be solved as:


    π empNo (σ projectNo6=0 comp010 (Assigned_To))

    13.3
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Sailor Database
    Query Using
    Sailors(sid, sname, rating, age) Relational Algebra

    Boats(bid, bname, color)


    Reserves(sid, bid, day)

    Query: Find the names of sailors who’ve reserved boat 105


    π sname (σ bid=105 (Reserves n Sailors))
    o

    This query can also be written as:


    π sname (σ bid=105 (Reserves) o
    n Sailors)

    Query: Find the names of sailors who’ve reserved a green boat


    π sname (σ color =0 green0 (Boats n Reserves o
    o n Sailors))

    This query can also be written as:


    π sname ((σ color =0 green0 (Boats)) o
    n Reserves o
    n Sailors)

    13.4
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query Using
    Relational Algebra

    Query: Find the sailor ids of the sailors who’ve reserved all boats
    π sid,bid (Reserves) ÷ π bid (Boats)

    Query: Find the names of sailors who’ve reserved all boats


    1. ρ Temp (π sid,bid (Reserves) ÷ π bid (Boats))
    2. π sname (Temp o n Sailors)

    This query can also be written as:


    n Reserves) ÷ π
    π sname,bid (Sailors o bid (Boats)

    13.5
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query Using
    Shipment Database Relational Algebra

    Customer(cust_id, cust_name, annual_revenue)


    Truck(truckno, driver_name)
    City(city_name, population)
    Shipment(shipment_no, cust_id, weight, truckno,
    destination_city)

    Query: Find the list of shipment numbers for shipments weighing


    over 20 pounds
    π shipment_no (σ weight>20pound (Shipment))

    Query: Find the names of customers with more than $10 million
    in annual revenue
    π cust_name (σ annual_revenue>$10million (Customer))

    13.6
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query: Find the driver of truck 45


    Query Using
    Relational Algebra
    π driver _name (σ truckno=45 (Truck))

    Query: Find the names of cities which have received shipments


    weighing over 100 pounds
    π destination_city (σ weight>100pounds (Shipment))

    Query: Find the name and annual revenue of customers who


    have sent shipments weighing over 100 pounds
    π cust_name,annual_revenue (σ weight>100pounds (Customer o
    n
    Shipment))

    Query: Find the truck numbers of trucks which have carried


    shipments weighing over 100 pounds
    π truckno (σ weight>100pounds (Shipment))

    13.7
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query: Find the names of drivers who have delivered shipments


    weighing over 100 pounds Query Using
    Relational Algebra

    π driver _name (σ weight>100pounds (Shipment n Truck))


    o

    Query: List the cities which have received shipments from


    customers having over $15 million in annual revenue
    π destination_city (σ annual_revenue>$15million (Customer n Shipment))
    o

    Query: List the customers having over $5 million in annual


    revenue who have sent shipments weighing greater than 1 pound
    π cust_name (σ annual_revenue>$5million (Customer) nσ
    o weight>1pound
    (Shipment))

    This query can also be written as:


    π cust_name (σ annual_revenue>$5million∧weight>1pound (Customer o
    n
    Shipment))

    13.8
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query: List the customers whose shipments have been delivered Query Using
    by truck driver Ramesh Relational Algebra

    π cust_name (σ driver _name=0 Ramesh0 (Customer n Shipment o


    o n
    Truck))

    Query: Find the customers having over $5 million in annual


    revenue who have sent shipments weighing less than 1 pound or
    have sent a shipment to Bhubaneswar
    π cust_name (σ annual_revenue>$5million (Customer)nσ
    o
    weight>1pound∨destination_city =0 Bhubaneswar 0 (Shipment))

    Query: Find the customers who have sent shipments to every


    city with population over 500000
    π cust_name,destination_city n Shipment) ÷ π
    (Customer o city (σ
    population>500000 (City))

    13.9
    Query Using Relationa
    Query Using Relational Algebra... Algebra

    Chittaranjan Pradhan

    Query: List the drivers who have delivered shipments for


    customers with annual revenue over $20 million to cities with Query Using
    Relational Algebra
    population over 1 million
    π driver _name (σ annual_revenue>20million (Customer) o
    n Shipment o
    n
    Truck o n (σ population>1million (City)))

    This query can also be written as:


    π driver _name (σ annual_revenue>20million∧population>1million (Customer o
    n
    Shipment o n Truck o n City))

    Query: Find the cities which have received shipments from every
    customer
    π destination_city ,cust_id (Shipment) ÷ π cust_id (Customer)

    Query: Find the drivers who have delivered shipments to every


    city
    π driver _name,destination_city n Shipment) ÷ π
    (Truck o city _name (City)

    13.10

    You might also like