Relational algebra in database management systems solved exercise

Relational algebra – solved exercise

Question:

Consider the following relational database schema consisting of the four relation schemas:

passenger ( pid, pname, pgender, pcity)

agency ( aid, aname, acity)

flight (fid, fdate, time, src, dest)

booking (pid, aid, fid, fdate)

Answer the following questions using relational algebra queries;

Solution:

Relational algebra operators:

σ – selection with conditions (It selects all tuples that satisfies the conditions. Shows entire table with respect to the structure)

Π – projection operator (It selects the attributes which are listed here)

⨝ - natural join operator (Binary operator that join two relations on common attributes’ values)

-, ∪, and ∩ - set operators (difference, union and intersection)

Most of the following queries can be written in many different ways.

a) Get the complete details of all flights to New Delhi.

σ _{destination
= “New Delhi”}(flight)

-----------------------------------------------------------------------------------------------------

b) Get the details about all flights from Chennai to New Delhi.

σ _{src = “Chennai”
^ dest = “New Delhi”} (flight)

-----------------------------------------------------------------------------------------------------

c) Find only the flight numbers for passenger with pid 123 for flights to Chennai before 06/11/2020.

Π_fid(σ_{pid = 123}(booking) ⨝ σ_{dest = “Chennai” ^
fdate < 06/11/2020}(flight))

[Hint: Given conditions are pid, dest, and fdate. To get the flight id for a passenger given a pid, we have two tables flight and booking to be joined with necessary conditions. From the result, the flight id can be projected]

-----------------------------------------------------------------------------------------------------

d) Find the passenger names for passengers who have bookings on at least one flight.

Π_pname(passenger ⨝ booking)

-----------------------------------------------------------------------------------------------------

e) Find the passenger names for those who do not have any bookings in any flights.

Π_pname((Π_pid(passenger) - Π_pid(booking)) ⨝ passenger)

[Hint: here applied a set difference operation. The set difference operation returns only pids that have no booking. The result is joined with passenger table to get the passenger names.]

-----------------------------------------------------------------------------------------------------

f) Find the agency names for agencies that located in the same city as passenger with passenger id 123.

Π_aname(agency ⨝_{acity = pcity}(σ_{pid = 123}(passenger)))

[Hint: we performed a theta join on equality conditions (equi join) here. This is done between details of passenger 123 and the agency table to get the valid records where the city values are same. From the results, aname is projected.]

-----------------------------------------------------------------------------------------------------

g) Get the details of flights that are scheduled on both dates 01/12/2020 and 02/12/2020 at 16:00 hours.

(σ_{fdate = 01/12/2020 ^
time = 16:00}(flight)) ∩ (σ_{fdate = 02/12/2020
^ time = 16:00}(flight))

[Hint: the requirement is for flight details for both dates in common. Hence, set intersection is used between the temporary relations generated from application of various conditions.]

-----------------------------------------------------------------------------------------------------

h) Get the details of flights that are scheduled on either of the dates 01/12/2020 or 02/12/2020 or both at 16:00 hours.

(σ_{fdate = 01/12/2020 ^ time = 16:00}(flight)) ∪ (σ_{fdate = 02/12/2020 ^ time = 16:00}(flight))

-----------------------------------------------------------------------------------------------------

i) Find the agency names for agencies who do not have any bookings for passenger with id 123.

Π_aname(agency ⨝ (Π_aid(agency) – Π_aid(σ_{pid = 123}(booking)))

-----------------------------------------------------------------------------------------------------

j) Find the details of all male passengers who are associated with Jet agency.

Π_{passengers.pid,
pname, pcity}(σ_{pgender =
“Male” ^ aname = ‘Jet’}(passengers ⨝ booking ⨝ agency))

[Hint: To get the link between passengers and agency, we need to join all three tables passengers, booking, and agency with necessary condition. Here, agency links both passengers and agency. As we have performed natural join operation between all three tables, the degree of the result will consist of all attributes from all the three tables. Hence, we project only passengers details as these are mentioned as required.]

*************

Convert the Entity Relationship Diagram to Relational Database Schema, How to convert ERD to conceptual schema, Reduce ER diagram to relational schema

Reduction of ERD to Relational Schema - Solved Exercise

Question:

Reduce the following ER diagram to relational database schema

Solution:

Notations used in the ERD for relationships

Step 1: First let us reduce the strong entity sets into schema. We have the strong entity sets LOT, RAW_MATERIALS and PRODUCTION_UNITS. They can be converted into schemas as follows;

Rules to convert: Strong Entity Set

Name of the schema = Name of the strong entity set

Attributes of the schema = attributes of the strong entity set

Underline the primary key attribute in the resultant schema

Lot (LotNumber, CreateDate, Cos-of-Materials)

Raw_Materials (material-ID, UnitCost, type)

Production_Units (serial#, exactWeight, productType, productDesc, qualityTest)

Step 2: To decide on whether relationships to be converted into separate tables or not. We have two relationship sets in our problem.

(1) Includes is a one-to-many relationship from entity set Lot to Production_Units.

Rules to convert: One-to-Many relationship set

Insert the primary key attribute of one side entity set as a foreign key in the many side entity set.

LotNumber attribute to be inserted into Prodution_Units schema as a foreign key. So, the schema Production_Units is updated as follows;

Production_Units (serial#, exactWeight, productType, productDesc, qualityTest, LotNumber)

(2) Created_From is a many-to-many relationship set between Lot and Raw_Materials.

Rules to convert: Many-to-Many relationship set

Create a separate table for many-to-many relationship set with primary keys of participating entity sets as attributes. All the primary key attributes of participating entity sets will form a composite key in the resultant relation.

Hence, create a separate schema for Created_From as follows;

Created_From(LotNumber, material-ID)

Step 3: To reduce Descriptive attributes into schema.

Rules to convert: Descriptive attribute

Descriptive attribute is an attribute attached to the relationship directly. While reducing the relationship into schema, insert the descriptive attributes with that schema.

Hence, Created_From is updated as follows;

Created_From(LotNumber, material-ID, Units)

Resultant schema:

After reduction, the schema looks like the following; [primary keys are underlined, foreign keys are connected with the concerned primary keys with headed arrows]

TOPICS (Click to Navigate)

Monday, April 27, 2020