DBG Exercises on Relational AlgebraTania Cerquitelli Relational algebra

(1)

DB M G

Relational algebra

Exercises on Relational Algebra

Tania Cerquitelli

(2)

DB M G

Exercise n. 1(a)

For each Italian tour guide who has only guided types of tours lasting more than 2 hours, show name and surname of the guide.

TOUR-GUIDE (GuideCode, Name, Surname, Nationality) TYPE-OF-TOUR (TourTypeCode, Monument, Duration, City) GROUP (GroupCode, NumberOfParticipants, Language)

GUIDED-TOUR-CARRIED-OUT (GroupCode, Date, StartTime, TourTypeCode, GuideCode)

(3)

DB M G

(4)

DB M G

(5)

DB M G

(6)

DB M G

(7)

DB M G

(8)

DB M G

Exercise n. 1(b)

Show surname and nationality of the guides who have guided all types of tours.

TOUR-GUIDE (GuideCode, Name, Surname, Nationality) TYPE-OF-TOUR (TourTypeCode, Monument, Duration, City) GROUP (GroupCode, NumberOfParticipants, Language)

GUIDED-TOUR-CARRIED-OUT (GroupCode, Date, StartTime, TourTypeCode, GuideCode)

(9)

DB M G

(10)

DB M G

(11)

DB M G

(12)

DB M G

Exercise n. 2

Show the name of laboratories with a capacity greater than 10 people, where at least 2 experiments were performed on the same day with devices of type 'video camera'.

STUDENT(StudentID, Name, Surname, DegreeCourse) LABORATORY(LabID, LabName, Capacity)

DEVICE(DeviceID, DeviceName, Type, LabID)

EXPERIMENT(DeviceID, StudentID, Date, Description, Category)

(13)

DB M G

(14)

DB M G

(15)

DB M G

(16)

DB M G

(17)

DB M G

(18)

DB M G

Exercise n. 3

Show the name and surname of the teenagers who subscribed on the same date (SubscriptionDate) to at least two different activities, which are organized by two distinct summer camps located in the same city.

TEENAGER(SSN, Name, Surname, BirthDate, CityOfResidence, Sex) ACTIVITY(ActivityCode, AName, Description, Category)

SUMMER-CAMP(CampCode, CampName, City)

SUBSCRIPTION-TO-ACTIVITY-IN-SUMMER-CAMP(SSN, ActivityCode, CampCode, SubscriptionDate)

(19)

DB M G

(20)

DB M G

(21)