loading...

CS403 Assignment 2 Solution Spring 2018

Objectives

The motive of this assignment is to enable students how to transform ER data model in to Relations with Primary Foreign key constrains. It will also help the students to understand Basic Operators of Relational Algebra and Join Operations.

Important things to consider before doing this assignment: 

This assignment is based on the previous scenario (Assignment No.1 solution).

You are required to thoroughly observe the ERD as above ( solution of assignment No.1) and provide solution of the following tasks:

  1. Map all entities into Relations with Primary, Foreign key constrains where possible.
  2. Consider the below populated relations. Perform the cross product on SERVICES and EVENT relations by providing the resultant relation.

3.      Apply projection operation on SERVICES relation and provide resultant relation. II serID , serCharges (SERVICES)

4.      Take Union of SERVICES and EVENT relations and provide output.

SERVICES

serId serTitle serDescription serCharges
01 Food and beverage Class B 200$
02 Food and beverages Class A 300$
03 Room upgrades Economic 150$

EVENT

eventId Date timeSlot
Cx01 12.5.2018 11:00 am
Bs03 25.5.2018 12:00 pm

 

Solution :

  1. Map all entities into Relations with Primary, Foreign key constrains where possible.

Answer:

Services (ServID, SerTitle, SerDescription, SerCharges)

Category (CategoryID, CatName, IntialRates)

Event (EventID, Data, TimeSlot)

Customer (CustID, CustName, Email, Address)

Venue (VenueID, City, Capacity, Description, Location)

Menue (MenueID, EventID, No of Services, Quantity)

Food_items (ItemID, ItemName, Description)

  1. Consider the below populated relations. Perform the cross product on SERVICES and EVENT relations by providing the resultant relation.

Answer:

SerID serTitle SerDescription SerCharges EventID Date timeSlot
01 Food and beverage Class B 200$ Cx01 12-05-2018 11:00 am
02 Food and beverages Class A 300$ Cx01 12-05-2018 11:00 am
03 Room upgrades Economic 150$ Cx01 12-05-2018 11:00 am
01 Food and beverage Class B 200$ Bs03 25-05-2018 12:00 pm
02 Food and beverages Class A 300$ Bs03 25-05-2018 12:00 pm
03 Room upgrades Economic 150$ Bs03 25-05-2018 12:00 pm

 

  1. Apply projection operation on SERVICES relation and provide resultant relation. I-I serID , serCharges (SERVICES)

Answer:

II serID , serCharges (SERVICES)

serID serCharges
01 200$
02 300$
03 150$
  1. Take Union of SERVICES and EVENT relations and provide output.

Answer:

The given relations cannot satisfy the UNION conditions. The conditions are:

  • Both the relations should be the same degree, which means that the number of attributes in both relations should be exactly same.
  • The domains of corresponding attributes in both relations should be the same corresponding attributes means first attributes of both relation, then second and so on

The given tables don’t have same attributes and also the same domain of the corresponding attributes. So UNION of these relations are not possible.

DOWNLOAD SOLUTION HERE
loading...