Database Systems Coursework: 5COSC020W

Entity Name

Brief Explanation

w1888626_Park

Represents a park where racket sports take place

w1888626_Court

A specific court used for either tennis or pickleball

w1888626_Player

Represent a person who books and participates in the session.

w1888626_Equipment

Represents equipment assigned to courts

w1888626_Session

A scheduled session for players, supervised or unsupervised

w1888626_SupervisedSession

Specialized session with an instructor present.

w1888626_UnSupervisession

Specialized sessions without an instructor, may include block bookings.

w1888626_Caretaker

A caretaker responsible for the upkeep of parks and courts

w1888626_SessionEquipment

Represents the association of equipment used during sessions.

General Entity

Specialised Entity

Brief Explanation

Sessions

SupervisedSessions

Session supervised by an instructor

Sessions

UnsupervisedSessions

The session was not supervised and booked by the players themselves

Equipment

TennisEquipment

Equipment specific to tennis such as tennis rackets and nets

Equipment

PickleballEquipment

Equipment specific to pickleball courts

Entity Name 1

Multiplicity 1

Relationship

Multiplicity 2

Entity Name 2

Brief Justifications for the Multiplicity

Park

1

contains

0..*

Court

1. A park must contain at least one court.

2. A park can have multiple courts.

3. A court must be located in exactly one park.

4. It is possible for a court to be added later, which is why it's optional (0) for a court to exist in the beginning.

Court

1

has

0..*

Equipment

1. A court can have multiple pieces of equipment.

2. Equipment is optional for each court (some courts may not need equipment).

3. A piece of equipment is associated with exactly one court.

4. This ensures equipment is specific to a court and cannot exist independently without one.

Court

1

maintained by

1

Caretaker

1. A court is maintained by exactly one caretaker.

2. Each caretaker is responsible for maintaining only one court.

3. This relationship ensures that every court has someone accountable for its upkeep.

4. A caretaker cannot be responsible for multiple courts in this model, to ensure accountability and a clear chain of tasks.

Player

0..*

participates

1..*

Session

1. A player can participate in many sessions.

2. Every session must have at least one player.

3. This relationship models the idea that sessions are attended by multiple players.

4. A player’s participation in sessions is optional, as they may not always book sessions.

Session

1

specializes into

0..1

SupervisedSession

1. A session may specialize into a supervised session (optional).

2. A supervised session cannot exist without a general session.

3. Each supervised session is a more specific type of session, inheriting attributes from the general session.

4. This relationship captures the idea of a supervised session as an extension of the general session entity.

Session

1

specializes into

0..1

UnsupervisedSession

1. A session may specialize into an unsupervised session.

2. An unsupervised session cannot exist without a general session.

3. Each unsupervised session inherits from the general session entity.

4. This ensures an unsupervised session is a more specific type of session.

Entity Name

Attributes for Each Entity (Include PK)

Brief Explanation

w1888626_Park

{w1888626_ParkID} {PK}: Unique Identifier for the Part

This serves as the primary key to uniquely identifying each park in the database.

w1888626_Name: Name of the park.

The name attribute provides a way to reference and identify the park in a human-readable format.

w1888626_Location: Physical location of the park.

This attribute describes where the park is situated, allowing for geographical identification.

w1888626_Size: Size of the park (in acres or square feet).

Indicates the total area of the park, useful for management and planning purposes.

w1888626_EstablishedDate: Date the park was established.

Helps track the history and age of the park.

w1888626_Court

{w1888626_CourtID} {PK}: Unique identifier for the court.

This is the primary key that distinguishes each court in the database.

w1888626_CourtType: Type of court (e.g., tennis, squash).

Specifies the kind of sport that can be played on the court, aiding in facility management.

w1888626_SurfaceType: Surface material of the court.

Describes the material of the court surface (e.g., clay, grass), relevant for maintenance decisions.

w1888626_Equipment

{w1888626_EquipmentID} {PK}: Unique identifier for the equipment.

This primary key uniquely identifies each piece of equipment in the database.

w1888626_Type: Type of equipment (e.g., racquets, balls).

Describes what type of equipment it is, assisting in inventory management.

w1888626_Condition: Current condition of the equipment.

Indicates whether the equipment is new, used, or requires repair, which is crucial for maintenance.

w1888626_Caretaker

{w1888626_CaretakerID} {PK}: Unique identifier for the caretaker.

Serves as the primary key to uniquely identifying each caretaker in the system.

w1888626_Name: Name of the caretaker.

Provides the caretaker’s name for identification purposes.

w1888626_ContactNumber: Phone number of the caretaker.

Allows for communication regarding maintenance tasks.

w1888626_Email: Email address of the caretaker.

Useful for formal communication and notifications regarding the courts they manage.

w1888626_Player

{w1888626_PlayerID} {PK}: Unique identifier for the player.

This primary key uniquely identifies each player in the system.

w1888626_Name: Name of the player.

The player’s name is important for identification and records.

w1888626_MembershipID: Identifier for the player's membership status.

Indicates whether the player has an active membership and may include various membership details.

w1888626_ContactNumber: Phone number of the player.

Essential for communication and appointment confirmations.

w1888626_Session

{w1888626_SessionID} {PK}: Unique identifier for the session.

Serves as the primary key to uniquely identify each session scheduled.

w1888626_DateTime: Date and time when the session is scheduled.

Indicates when the session will take place, crucial for scheduling.

w1888626_Duration: Length of the session (in minutes).

Specifies how long the session will last, helping with scheduling and resource allocation.

w1888626_SupervisedSession

{w1888626_SupervisedSessionID} {PK}: Unique identifier for supervised sessions.

This primary key uniquely identifies each supervised session.

w1888626_SupervisorID: ID of the supervisor conducting the session.

Links to the caretaker overseeing the session, providing accountability and oversight.

w1888626_UnsupervisedSession

{w1888626_UnsupervisedSessionID} {PK}: Unique identifier for unsupervised sessions.

This primary key uniquely identifies each unsupervised session.

w1888626_SelfCheckIn: Indicates if players can check in themselves.

Specifies whether players have self-service check-in options for the session.