Part 4: Relational Algebra – Query Design [35 points] Consider the following relations for a zoo management system. The primary keys are underlined. Animals(AnimalID, Species, Name, DateOfBirth, Gender, HabitatID, Origin, ArrivalDate, DietType)Habitats(HabitatID, Type, Size, Location, CleaningSchedule, EnvironmentControlSettings)ZooStaff(StaffID, FirstName, LastName, Role, Department, HireDate, Qualifications, ContactNumber)AnimalHealthCare(StaffID, AnimalID, CareDate, Treatment, Notes)VisitorTours(TourID, Date, Time, GuideStaffID, RouteDescription, Theme, Duration, GroupSize)Maintenance(MaintenanceID, HabitatID, Description, Year, Date, StaffID, Status, Priority)ConservationPrograms(ProgramID, Name, Description, StartDate, EndDate, ResponsibleStaffID)ZooEvents(EventID, Name, Date, Time, Location, OrganizerStaffID, TargetAudience, Budget) Write Relational Algebra expressions for the following queries. It is recommended to formulate your Relational Algebra expressions as multi-step queries. This provides you with a better overview, and you can deploy already computed intermediate relation results in later queries by using their names. Further, lengthy Relational Algebra expressions that are difficult to understand are avoided.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Show if the statement that πA(R ∩ S) = πA(R) ∩ πA(S) with A as a common attribute of R and S is correct or not.

Explain why Crow’s foot notation does not make use of double lines in ER diagrams for indicating total participation.

Part 3: Relational Algebra – Think Deeper [15 points] Provide answers to the following questions.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Consider the Relational Algebra statement πA(σF(R)) = πA(σF(πA ∪ B(R))) with A, B ⊆ R. Discuss if this statement is, in general, correct. If yes, argue why this is the case. If no, argue why this is not the case and determine if the statement can be made correct by an additional condition.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Database schema Animals(AnimalID, Species, Name, DateOfBirth, Gender, HabitatID, Origin, ArrivalDate, DietType)Habitats(HabitatID, Type, Size, Location, CleaningSchedule, EnvironmentControlSettings)ZooStaff(StaffID, FirstName, LastName, Role, Department, HireDate, Qualifications, ContactNumber)AnimalHealthCare(StaffID, AnimalID, CareDate, Treatment, Notes)VisitorTours(TourID, Date, Time, GuideStaffID, RouteDescription, Theme, Duration, GroupSize)Maintenance(MaintenanceID, HabitatID, Description, Year, Date, StaffID, Status, Priority)ConservationPrograms(ProgramID, Name, Description, StartDate, EndDate, ResponsibleStaffID)ZooEvents(EventID, Name, Date, Time, Location, OrganizerStaffID, TargetAudience, Budget) Query Find the names of staff members who have cared for every individual animal regarding both the lion and tiger species.

Part 2: Entity-Relationship Model – Think Deeper [10 points] Provide answers to the following questions.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Database schema Animals(AnimalID, Species, Name, DateOfBirth, Gender, HabitatID, Origin, ArrivalDate, DietType)Habitats(HabitatID, Type, Size, Location, CleaningSchedule, EnvironmentControlSettings)ZooStaff(StaffID, FirstName, LastName, Role, Department, HireDate, Qualifications, ContactNumber)AnimalHealthCare(StaffID, AnimalID, CareDate, Treatment, Notes)VisitorTours(TourID, Date, Time, GuideStaffID, RouteDescription, Theme, Duration, GroupSize)Maintenance(MaintenanceID, HabitatID, Description, Year, Date, StaffID, Status, Priority)ConservationPrograms(ProgramID, Name, Description, StartDate, EndDate, ResponsibleStaffID)ZooEvents(EventID, Name, Date, Time, Location, OrganizerStaffID, TargetAudience, Budget) Query Identify staff members (their names and roles) who have never organized an event, performed habitat maintenance, or provided health care to any animals.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Database schema Animals(AnimalID, Species, Name, DateOfBirth, Gender, HabitatID, Origin, ArrivalDate, DietType)Habitats(HabitatID, Type, Size, Location, CleaningSchedule, EnvironmentControlSettings)ZooStaff(StaffID, FirstName, LastName, Role, Department, HireDate, Qualifications, ContactNumber)AnimalHealthCare(StaffID, AnimalID, CareDate, Treatment, Notes)VisitorTours(TourID, Date, Time, GuideStaffID, RouteDescription, Theme, Duration, GroupSize)Maintenance(MaintenanceID, HabitatID, Description, Year, Date, StaffID, Status, Priority)ConservationPrograms(ProgramID, Name, Description, StartDate, EndDate, ResponsibleStaffID)ZooEvents(EventID, Name, Date, Time, Location, OrganizerStaffID, TargetAudience, Budget) Query Find the names of staff members who have only guided visitor tours with less than 10 people or more than 20 people.

Symbols for Relational Algebra Expressions and Other Symbols Symbols for relation schemas: R, S, T Symbols for relations: R, S, T, ∅ Symbols for Relational Algebra operators: ∪, -, π, σ, ρ, ⨯, ∩, Δ, ÷, ⨝, ⟗, ⟕, ⟖, ⋉, ⋊, ▷ Symbols for logical connectives: ∧, ∨, ¬ Symbols for comparison predicates: , =, ≤, ≥, ≠ Symbols for set-theoretical operations: ∈, ∉, ⊆, ⊂, ⊇, ⊃ Other mathematical symbols: ⊥, ⋅, ∃, ∀, ∘, ←, → All names (for example, for relations and attributes) should be written in normal font (no italics) to increase readability. All symbols should be surrounded by blanks to increase readability. Let R be a relation of schema R(A, B), S be a relation of schema S(C, D), and T be a relation of schema T(A, B, C, D). Let us further assume that T ÷ S = R holds. Determine how T can be obtained using R and S, and explain why.