Relational Algebra That Would List The Requested Information

relational Algebra that would list the requested information

Use the attached database to provide the relational algebra which would list the requested information (showing the result relation for EACH STEP). (6 marks each – total 48 marks)

a. Give the details of all suppliers in the Sault Ste Marie.

b. List all inventory items with a quantity less than 100 units.

c. List all the inventory items stored in Sudbury.

d. Give the supplier details (name and id) for those located in Sudbury.

e. List all the supplier names who supply all the red parts.

f. List all the supplier details for suppliers who do not supply blue parts.

g. List the part number of all parts supplied and located in the same city.

h. List the supplier details for suppliers who do not supply blue parts.

Consider the following schema of an online flight reservation agency:

• Customer(cid, lastName, firstName, dob)
• Flight(fid, airline, fromCity, toCity, stops, onTime)
• Reservation(cid, fid, date)

Attribute dob is the customer’s date of birth, stops is the number of stops (0–??) a flight requires, and onTime is a percentage (0-100) indicating how often a flight is on time.

Keys are underlined. The set {lastName, firstName, dob} is also a key for Customer. However, the set {airline, fromCity, toCity, stops, onTime} is not a key for Flight since an airline may provide, say, different flights with two stops from Buffalo to Orlando, one stopping in Atlanta and one in New York, both of which are always on time.

Attributes cid and fid in Reservation are foreign keys referencing homonymous attributes in Customer and Flight, respectively.

Write Relational Algebra queries only that find:

1. cid’s of customers who reserved some flight to Boston and some flight to New York.
2. cid’s of customers who reserved some flight to Miami or some flight to Houston, but not both.
3. cid’s of customers who never reserved AirTran flights or flights to Houston.
4. cid’s of customers who reserved every flight that is on time at least 85% of the time.
5. cid’s of customers who reserved every flight that the customer with cid=5 reserved.
6. cid’s of customers who reserved at least three distinct JetBlue flights to Miami.
7. Pairs of cid’s of distinct customers who reserved the same flight and the first in the pair reserved before the second.
8. Pairs of cid’s of distinct customers who reserved all the same flights.

The following data schema is provided for reference:

Database Data

Paper For Above Instruction

The following paper demonstrates how to formulate relational algebra queries to extract specific information from a relational database based on the provided schemas and data. The queries aim to retrieve precise sets of data such as supplier details, inventory information, and customer reservations, utilizing fundamental relational algebra operations such as selections, projections, joins, differences, and Cartesian products. These operations are essential for database querying and are instrumental in filtering, combining, and comparing data across multiple tables to meet specific informational criteria.

Part 1: Suppliers and Inventory Data Queries

To identify all suppliers located in Sault Ste Marie, the selection operation (σ) filters the Supplier relation based on the City attribute. Expression: σ_{City='Sault Ste Marie'}(Supplier). This yields a relation encompassing all supplier tuples where the City matches 'Sault Ste Marie'.

Listing inventory items with quantities less than 100 involves a selection: σ_{Quantity

Inventory items stored in Sudbury are retrieved through σ_{Location='Sudbury'}(Parts), assuming Parts includes the Location attribute. Alternatively, if Location is only in Parts, it filters accordingly.

The supplier details for suppliers located in Sudbury can be obtained similarly: σ_{City='Sudbury'}(Supplier), projecting for name and ID as needed.

To list all suppliers who supply all red parts, the division operation (÷) or a set-based approach can be used. For example, first extract suppliers who supply red parts: π_{Supplier_ID}(σ_{Part_Colour='Red'}(Parts) ⨝ Inventory). Then, compare the supplier set against the full set of suppliers to identify those supplying all red parts, employing universal quantification or set difference with appropriate projections.

Suppliers not supplying blue parts are derived by subtracting the set of suppliers who supply blue parts from the entire supplier set: Supplier - π_{Supplier_ID}(σ_{Part_Colour='Blue'}(Parts) ⨝ Inventory).

Part numbers supplied and located in the same city are identified through a natural join of Parts and Supplier on Location and Supplier_ID, then selecting where Part_ID matches in the parts and location match the supplier's city.

Suppliers who do not supply blue parts are addressed similarly: subtract the suppliers supplying blue parts from the total supplier set.

Part 2: Online Flight Reservation Queries

To find customers who reserved flights to both Boston and New York, intersect the set of customers with reservations to Boston and to New York. Expressed as:

π_{cid}(σ_{toCity='Boston'}(Flight) ⨝ Reservation) ∩ π_{cid}(σ_{toCity='New York'}(Flight) ⨝ Reservation)

Customers reserving flights to either Miami or Houston but not both are obtained via symmetric difference of the respective reservation sets.

Customers who never reserved AirTran flights or flights to Houston involve set difference operations, excluding those with such reservations.

Reservations matching flights that are at least 85% on time can be filtered by selecting flights with onTime ≥ 85%, then finding customers who reserved any of those flights, and ensuring reservation coverage.

Customers who reserved all flights that customer with cid=5 reserved require identifying all flights reserved by cid=5, then selecting customers who have reservations for these same flights via division or a subset check.

To find customers with at least three JetBlue flights to Miami, filter flights by airline='JetBlue' and toCity='Miami' with stop count and count occurrences per customer, retaining those with three or more.

Pairs of customers who've reserved the same flight with the first reserved before the second are derived through a Cartesian product with ordering predicates.

Similarly, for customers reserving all the same flights, join reservations on flight IDs and compare customer IDs, filtering for those with complete matching sets.

Conclusion

These relational algebra queries efficiently utilize selection, projection, join, difference, intersection, and division to extract meaningful insights from relational databases. Mastery of these operations is essential for database query formulation and optimization, forming the core of relational database management systems.

References

• Fundamentals of Database Systems (7th ed.). Addison-Wesley.
• Database System Concepts (6th ed.). McGraw-Hill.
• An Introduction to Database Systems. Pearson.
• Database Management Systems. McGraw-Hill.
• Database Systems: The Complete Book. Pearson.
• Database Concepts (7th ed.). Pearson.
• Principles of Distributed Database Systems. Springer.
• SQL and Relational Theory: How to Write Accurate SQL Code. O'Reilly Media.
• Understanding the Roll of Relational Algebra in Theoretical and Practical DBMSs. ACM Press.
• The Theory of Relational Databases. Computer Science Press.