Databaskonstruktion-Assignment 1

Overview

I have been assigned the field Agent part of the system. The selected entities are Agent, FieldAgent, Operation, Incident, Observation, Equipment, and Region.

ER model

The ER diagram below shows the selected part of the system that I plan to implement.

Requirements

Agent

• Each agent is uniquely identified by a name and a number.
• The agent name consists of one letter.
• The agent's philosophy must be stored as a comment.
• The agent's salary must be stored.
• The agent's original first name and last name must be stored separately.
• A derived full name must be available from the first name and last name.
• Leif Loket Olsson, Greger Puckowitz and Greve Dracula are not allowed to become agents.
• An agent may not have a salary below 12000.
• An agent who is not a group leader may not have a salary above 25000.
• A group leader may not have a salary above 35000.
• If no salary is specified, the salary must automatically be set to 13000.
• An agent number may not be negative.
• An agent number may not be greater than 99.
• An agent number may not be zero unless the agent is a group leader.
• The value 13 is not allowed as an agent name.
• Assumption: AgentType is added to distinguish between different agent roles, such as FieldAgent and GroupLeader.

FieldAgent

• A field agent can participate in several operations.
• An operation can have several participating field agents.
• A field agent can have several favourite pieces of equipment.
• The same piece of equipment can be a favourite of several field agents.
• The field agent's competencies must be stored.
• The field agent's most important special competence must be stored.
• It must be possible to calculate the total number of operations a field agent has participated in.
• It must be possible to calculate the number of successful operations a field agent has participated in.
• Access to field agent data must be very fast because the table is used in real time.
• A field agent may normally work on only one operation at the same time.
• If the operations are in the same region, a field agent may work on up to three operations at the same time.

Operation

• Each operation is identified by its codename type, start date, and the incident that generated the operation.
• The codename type contains both the operation codename and the operation type.
• An operation may have an optional comment containing additional important details.
• Two operations for the same incident may have the same codename and type if they occur on different dates.
• An operation must be led by a group leader.
• Several pieces of equipment can be assigned to an operation.
• Field agents must be assigned to an operation.
• The end date of an operation must be stored so that agents and equipment can be booked.
• The end date may later be changed to match the actual end date.
• The end date must be later than the start date.
• An operation must last at least one day.
• If an operation lasts more than five weeks, its duration must automatically be adjusted to five weeks.
• When the end date is changed, all booking rules for agents, equipment and group leaders must be checked again.
• The success rate must be stored as an integer.
• The success rate must be 1 for a successful operation and 0 for a failed operation.
• No other success rate values are allowed.
• Assumption: The group leader of an operation is represented by an Agent whose AgentType is "GroupLeader".

Incident

• An incident can be identified by either its unique name or its unique number.
• Assumption: IncidentNumber is chosen as the primary key in the logical database design, while IncidentName must also remain unique.
• The location where the incident occurred must be stored.
• It must be possible to calculate the average grade of all observations connected to an incident.
• Every incident must occur in a region.
• An incident can have one or more operations connected to it.

Observation

• Every observation must have a date.
• Every observation must have a grade.
• Every observation must have a security value.
• The security value must be stored as a percentage.
• The grade must be between 1 and 4.
• Grade 4 represents extremely close contact.
• Every observation must belong to an incident.
• Access to observation data must be very fast and queries must have response times below one second.
• The security percentage must not be lower than 1 percent.
• The security percentage must not be higher than 114 percent.

Equipment

• Several pieces of equipment can be assigned to an operation.
• Each piece of equipment is uniquely identified by its name together with its number
• A short description of each piece of equipment must be stored.
• If a piece of equipment is broken or lost during an operation, it must be removed from the database.
• Only the group leader of operation may remove equipment used in that operation.
• When equipment is removed, the corresponding links to field agents and operations must also be removed.
• Each piece of equipment must have an ordinal number.
• An ordinal number lower than 1 or higher than 15 is invalid.
• The same piece of equipment may not be assigned to several operations at the same time.

Region

• A region is uniquely identified by its name.
• The main terrain of the region must be stored.
• It must be possible to derive the number of previous incidents in a region.
• It must be possible to derive the number of previous operations in a region.
• Incidents and operations must always remain connected to a region.
• Every region must have a terrain value.
• If no terrain value is specified, it must automatically be set to "blandad".
• Secret regions may temporarily be removed from the database.
• If the region "hemligstämplat" does not already exist, it must be created.
• When a secret region is removed, the affected incidents and operations must be connected to the region "hemligstämplat".

Logical Database Design

Agent(Name, Number, FirstName, LastName, Philosophy, Salary, AgentType)

FieldAgent(Name, Number, SpecialCompetence)

FieldAgentCompetence(Name, Number, Competence)

Region(Name, Terrain)

Incident(Number, Name, Location, RegionName)

Observation(ObservationID, Date, Grade, Security, IncidentNumber)

Equipment(Name, Number, Description)

Operation(CodenameType, StartDate, IncidentNumber, EndDate, Comment, SuccessRate, RegionName, LeaderName, LeaderNumber)

FieldAgentOperation( FieldAgentName, FieldAgentNumber, CodenameType, StartDate, IncidentNumber )

OperationEquipment( EquipmentName, EquipmentNumber, CodenameType, StartDate, IncidentNumber )

FieldAgentFavouriteEquipment( FieldAgentName, FieldAgentNumber, EquipmentName, EquipmentNumber )