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.

Design an ER diagram for the following requirements of an online restaurant management system. Each restaurant chain has a unique ID, a name, and a headquarters address. The chain comprises multiple branches, each with a unique Branch ID, a name, a location, a manager’s contact information, and operating hours. Every branch offers a variety of menu items. Menu items at each branch have a unique item ID and are described by a name, a description, a price, and a category. Customers visit branches to place orders, each identified by an order ID. These orders include details such as date, time, status, and total price. Each order is associated with a specific table in the branch. Each branch features multiple tables, each with a table number and details such as seating capacity. A branch has employees, each with an employee ID, name, role, contact information, and a shift schedule, associated with a specific branch. Branches accept reservations, each with a unique reservation ID, including details such as date, time, and number of guests, with tables reserved accordingly. Create your diagram using ERDPlus. Provide additional explanations if necessary. When you are finished, export it as an image, and upload it.

Chen’s notation provides support for composite attributes and multi-valued attributes. Discuss if an attribute can be a composite attribute and a multi-valued attribute at the same time.

When using (min, max)-notation for the design of ER diagrams, the specification of cardinalities for ternary and higher relationship sets is possible and meaningful. Argue why this is the case. Provide a simple example.

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. Assume the following relation R: R X Y 1 ‘A’ 2 ‘B’ 3 ‘C’ Design a Relational Algebra expression that uses R to create a new relation S with the following contents: S X1 Y1 X2 Y2 1 ‘A’ 3 ‘C’ 2 ‘B’ 2 ‘B’ 3 ‘C’ 1 ‘A’

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 events that have the largest budgets in the “Amazon area” (location name and unique).

Part 1:  Entity-Relationship Model [40 points] Design an Entity-Relationship diagram with ERDPlus by considering the requirements listed in the statements below. That means that you have to identify suitable entity sets, relationship sets, attributes, keys of entity sets, and so on. Further, add the cardinalities (1:1, 1:m, m:1, m:n) to the relationship sets. Write down your thoughts and assumptions regarding the cardinalities if there could be a doubt. It is up to you if you choose Chen’s notation or Crow’s foot notation. But you have to use the selected notation consistently, that is, you should not mix the two notations. As we all have found out, ERDPlus does not cleanly support both notations. In both notations, ERDPlus uses rectangles for entity sets, double rectangles for weak entity sets, diamonds for relationship sets, double diamonds for identifying relationship sets, ovals for attributes, double ovals for multiple attributes, trees for composite attributes, and trees for generalizations. The only difference that ERDPlus provides to distinguish both notations refers to cardinalities. In Chen’s notation, one has to use the labels 1, n, and m for cardinalities (with the known overlapping problem). Double lines are not provided so it is impossible to represent total participation as well as the double line between an identifying relationship set and a weak entity set. You should then describe this in a sentence below the ER diagram. In crow’s foot notation, ERDPlus offers the little bar, the little oval, and the crow’s foot to indicate cardinalities. The instructor’s recommendation is to use the ERDPlus version of Crow’s foot notation since it provides all the functionality that you need.