Databaskonstruktion-Assignment 1

Overview

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

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 number.
• 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.
• 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.
• Field agents must be assigned to an operation.

• The end date of an operation must be stored so that agents 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 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.

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".

Equipment

• Equipment can be assigned to an Operation.

• Each equipment item is identified by its name and number.
• A short description of the equipment must be stored.

• If an equipment item breaks or disappears during an operation, it must be possible to remove it.
• Only the group leader of the operation may remove equipment used for that operation.
• When equipment is removed, its connections to FieldAgents and Operations must also be removed.
• The same equipment item may not be assigned to several operations at the same time.

• The equipment number must be between 1 and 15.
• A Field Agent can have several favorite equipment items.
• The same equipment item can be a favorite for several Field Agents.

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)

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

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

Equipment(Name, Number, Description)

FieldAgentFavouriteEquipment(FieldAgentName, FieldAgentNumber, EquipmentName, EquipmentNumber)

Oral Presentation Material

Assigned Part

I was assigned the Reexamination Same as Last Year Assignment Variant. My selected part of the system focuses on the Field Agent and related entities.

Conceptual Database Design

For the conceptual database design, I selected the entities Agent, FieldAgent, Operation, Incident, Observation, Region and Equipment. Each entity was selected because it has a clear connection to the work of a Field Agent.

Agent

Agent was selected because every Field Agent is also an Agent. The Agent entity stores the general information that all agents have, such as the agent name, number, salary, philosophy, first name and last name. It is therefore needed as the base entity for the FieldAgent entity.

FieldAgent

FieldAgent was selected because it is the main focus of my part of the system. A Field Agent can participate in several operations and has competencies and one important special competence that must be stored. The requirements also state that it must be possible to calculate the total number of operations and the number of successful operations that a Field Agent has participated in.

Operation

Operation was selected because Field Agents work in operations. An operation contains important information about the work of the Field Agent, such as start date, end date, success rate and the incident that generated the operation. The requirements also state that several Field Agents can participate in the same operation.

Incident

Incident was selected because operations are created as a result of incidents. An incident stores information such as its unique name or number and the location where it occurred. It also connects other important entities, because observations and operations are related to incidents.

Observation

Observation was selected because observations contain information about what has been reported in connection with an incident. Every observation stores a date, grade and security value and must belong to an incident. This information is relevant to a Field Agent because the requirements state that Field Agents should be able to read information about previous observations.

Region

Region was selected because incidents occur in regions and the location is important when Field Agents work with operations. A region also stores information about its main terrain. The Region entity therefore provides geographical information that connects to incidents and operations.

Equipment

Equipment was selected because Field Agents can have favorite equipment items. A Field Agent can have several favorite equipment items, and the same equipment item can be a favorite of several Field Agents.

Logical Database Design

In the logical database design, the selected entities from the ER diagram were converted into relations that can later be implemented as database tables.

Agent

Agent uses the combination of Name and Number as its primary key because the requirements state that an agent is uniquely identified by these two values together. The relation also contains FirstName, LastName, Philosophy, Salary and AgentType.

FieldAgent

FieldAgent represents the Field Agent subtype. It uses the same Name and Number as Agent so that each FieldAgent is connected to exactly one Agent.

FieldAgentCompetence

FieldAgentCompetence was created because a Field Agent can have several competencies. A separate relation is therefore needed to store multiple competence values for the same Field Agent.

Operation

Operation uses CodenameType, StartDate and IncidentNumber as a composite primary key. This follows the requirement that an operation is identified by its codename type, start date and the incident that generated it.

FieldAgentOperation

FieldAgentOperation was created to represent the many-to-many relationship between FieldAgent and Operation. A Field Agent can participate in several operations, and an operation can contain several Field Agents.

Incident

Incident uses Number as its primary key, while the incident name must still be unique. The relation also stores the location and the connection to Region.

Observation

Observation has its own ObservationID as primary key and contains Date, Grade and Security. It also contains a reference to Incident because every observation must belong to an incident.

Region

Region uses Name as its primary key because the requirements state that a region is uniquely identified by its name. Terrain is also stored as part of the relation.

Equipment

Equipment uses the combination of Name and Number as its primary key because each equipment item is identified by these two values together. The relation also stores a description of the equipment.

FieldAgentFavouriteEquipment

FieldAgentFavouriteEquipment was created to represent the many-to-many relationship between FieldAgent and Equipment. A Field Agent can have several favorite equipment items, and the same equipment item can be a favorite of several Field Agents.

Constraints

Constraints were added to make sure that only valid data can be stored in the database.

CHECK Constraints

Several CHECK constraints were implemented based on the requirements.

In AGENT, the salary must be at least 12000. The maximum salary depends on the agent type: a Field Agent may have a salary up to 25000, while a Group Leader may have a salary up to 35000.

The agent number must be between 0 and 99 and cannot be 13. Number 0 is only allowed for Group Leaders.

In OBSERVATION, the grade must be between 1 and 3 and the security value must be between 1 and 114. Grade 4 observations are stored separately in CLOSEOBSERVATION, where the grade must be exactly 4.

In OPERATION, the success rate can only be 0 or 1, and the end date must be later than the start date.

In EQUIPMENT, the equipment number must be between 1 and 15.

UNIQUE Constraint

The NAME attribute in INCIDENT uses a UNIQUE constraint because the requirements state that an incident can be identified by either its unique name or its unique number.

NOT NULL Constraints

NOT NULL constraints were used for attributes that must always contain a value. Examples are the agent's first name and last name, the Field Agent's special competence, the date, grade and security values of an observation, and the name and number of an equipment item.

Physical Database Design

Coding 1 - AgentType

AgentType was codified by replacing repeated text values with numeric codes.

A separate table, AGENTTYPECODE, was created to store the available agent types. In my implementation, code 1 represents FieldAgent and code 2 represents GroupLeader.

The AGENT table stores only the numeric code and uses a foreign key to AGENTTYPECODE. I chose this design to reduce repeated text values and to keep the agent types consistent.

Coding 2 - Terrain

Terrain was codified by replacing terrain text values with numeric codes.

A separate table, TERRAINCODE, was created to store the terrain types. In my implementation, the codes represent values such as Mountain, Forest, Desert and blandad.

The INCIDENT table stores the numeric terrain code and uses a foreign key to TERRAINCODE. I chose this design to avoid repeating the same terrain text in many rows and to keep the allowed terrain values consistent.

The default terrain code is 4, which represents blandad, so if no terrain is specified the database automatically uses the default value.

Merge 1 - Region into Incident

The Region relation was merged into the INCIDENT table in the physical database design.

Instead of keeping a separate physical REGION table, the region information is stored directly in INCIDENT using REGIONNAME and TERRAIN.

I chose this merge because every incident must occur in a region, so the region information can be stored together with the incident data. This reduces the number of tables and joins needed when retrieving incident information.

Vertical Split - OperationComment

A vertical split was used for the optional comment of an operation. The comment was separated from the OPERATION table and stored in a separate table called OPERATIONCOMMENT. The table uses the same identifying values as OPERATION: CodenameType, StartDate and IncidentNumber. I chose this design because the comment is optional and does not need to be stored together with the main operation data. Separating it keeps the main OPERATION table smaller while still allowing a comment to be connected to the correct operation.

Horizontal Split - CloseObservation

A horizontal split was used for observations with grade 4. Grade 4 represents extremely close contact, so these observations were separated into the CLOSEOBSERVATION table. The normal OBSERVATION table stores grades 1 to 3, while CLOSEOBSERVATION stores only grade 4 observations. I chose this design to separate the most sensitive close-contact observations from the ordinary observations while keeping the same basic attributes such as date, security value and incident number.

Indexing

Two indexes were created to improve query performance.

The first index, OBSERVATIONDATE, was created on the DATE column in OBSERVATION. This supports faster access when observations are sorted or searched by date.

The second index, OPERATIONDATES, was created on STARTDATE and ENDDATE in OPERATION. This supports faster searches for operations within a specific time period.

I chose these indexes because the requirements state that access to observation data should be very fast and because operations are often searched using their dates.

Rights

Rights were added for the Field Agent database user based on the information that a Field Agent needs to access.

The user mariam_fieldagent was given SELECT permission on the INCIDENT, OBSERVATION, OPERATION and EQUIPMENT tables.

The user was also given SELECT, INSERT and DELETE permissions on FIELDAGENTFAVOURITEEQUIPMENT so that favorite equipment can be viewed, added and removed.

The user was given EXECUTE permission on the CHANGEFAVORITEEQUIPMENT and GETFIELDAGENTOPERATIONCOUNT procedures.

These permissions allow the Field Agent to access the required information and manage favorite equipment without giving unnecessary permissions on other database tables.

Procedures and Triggers

GETFIELDAGENTOPERATIONCOUNT

This procedure calculates how many operations a specific Field Agent has participated in.

It uses the Field Agent's name and number as input and counts the matching rows in FIELDAGENTOPERATION. This implements the requirement that the total number of operations for a Field Agent must be possible to calculate.

GETFIELDAGENTSUCCESSCOUNT

This procedure calculates how many successful operations a specific Field Agent has participated in.

It uses the Field Agent's name and number as input, joins FIELDAGENTOPERATION with OPERATION, and counts only the operations where SUCCESSRATE is equal to 1. This implements the requirement that the number of successful operations for a Field Agent must be possible to calculate.

CHANGEFAVORITEEQUIPMENT

This procedure changes a Field Agent's favorite equipment item.

It uses the Field Agent's name and number, the current equipment name and number, and the new equipment name and number as input. The procedure replaces the selected favorite equipment with the new favorite equipment.

OPERATIONMAXFIVEWEEKS

This trigger checks the length of a new operation before it is inserted into the database.

If the end date is more than five weeks after the start date, the trigger automatically changes the end date so that the operation lasts exactly five weeks. This implements the requirement that an operation may not be longer than five weeks.

OPERATIONMAXFIVEWEEKSUPDATE

This trigger checks an operation before its end date is updated.

If the new end date would make the operation longer than five weeks, the trigger automatically changes the end date so that the operation lasts exactly five weeks. This ensures that the five-week limit is also enforced when an existing operation is updated.

CHECKFORBIDDENAGENT

This trigger checks a new Agent before the row is inserted into the database.

If the first name and last name match one of the forbidden persons, the insert is stopped and an error message is returned. This implements the requirement that Leif Loket Olsson, Greger Puckowitz and Greve Dracula are not allowed to become agents.