Saudi Electronic University Relational Algebra Expressions Worksheet

Question OnePg. 01
Learning
Outcome(s):
1.25 Marks
Question One
Consider the following database schema for student game, where each team is led by a
student of the team and each game is between two teams (Host and Guest):
LO3
Students
Create EntityRelationship model,
Student_Id
Student_Name
Address
Relational model,
and write SQL
Teams
queries
Team_Id
Team_Name
Rank
Team_Leader
Membership
Student_id
Team_Id
Games
Game_id
Score
Date
Host_Team
Your task is to show all the primary and foreign keys in Schema.
Guest_Team
Question Two
Pg. 02
Learning
Outcome(s):
Question Two
1.75 Marks
Consider the following schema:
Suppliers (sid, sname, city)
LO3
Parts (pid, pname, color)
Create Entity-
Catalog (sid, pid, price)
Relationship model,
Relational model,
Write relational algebra expressions for the following queries:
and write SQL
queries.
a. Find the sids of suppliers and names who are located in Riyadh.
b. Find the name of suppliers who are located in Riyadh and Jeddah.
c. Find the names of supplier who supply part name “fog light”.
d. Find the sids of suppliers who supply every part.
Question Three
Pg. 03
Learning
Outcome(s):
LO3
Create EntityRelationship model,
Relational model,
and write SQL
queries.
Question Three
2 Marks
Create an Entity Relationship diagram that captures the information about the
UPS system? Be certain to indicate identifiers and cardinality constraints.
UPS prides itself on having up-to-date information on the processing and current
location of each shipped item. To do this, UPS relies on a company-wide information
system. Shipped items are the heart of the UPS product tracking information system.
Shipped items can be characterized by item number (unique), weight, dimensions,
insurance amount, destination, and final delivery date. Shipped items are received into
the UPS system at a single retail center. Retail centers are characterized by their type,
uniqueID, and address. Shipped items make their way to their destination via one or
more standard UPS transportation events (i.e., flights, truck deliveries). These
transportation events are characterized by a unique scheduleNumber, a type (e.g, flight,
truck), and a deliveryRoute.