૭( ᵕ•̀ᵕ•́૭)

Lab 9

4 min read

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Database Failure Analysis

Incident Documentation

The database incident that our team analyzed was the tribble tracking trouble. They had a lot of issues with duplicate data as well as data not being updated properly. One of the main issue was that one tribble was somehow its own grandmother and third cousin, which caused the system to crash.

Tribbles(tribble_id, tribble_color, tribble_weight, mother_id, mother_color, mother_weight, father_id, father_color, father_weight, birth_stardate, containment_area, feeding_schedule)

The normal form that was violated was the second normal form, specifically the fields have partial dependencies which violate the second normal form. For instance, the father_color and father_weight are partially dependent on father_id

Technical Analysis

Since parental information was stored within the same table as the tribble itself, it has the possibilities of impossible relationships—such as being ones own grandmother and third cousin simultaneously.

The anomaly that could occur is an update anomaly. For instance, if a tribble’s father is updated, it’s not automatically updated within the child’s table because of the partial dependency. There is also the possibility of an insert anomaly since there aren’t any constraints on the relationships.

The lack of proper normalization was the reason that the tribbles had issues. The major concern was that a tribble was its own grandmother and third cousin—which is impossible and the system crashed because of this (it’s not entirely possible to deduct why, but the circular relationship is most likely the reason)

Normalized Solution

erDiagram
    TRIBBLES {
        varchar(5) tribble_id PK
        varchar(20) tribble_color
        varchar(20) tribble_weight
        date birth_stardate
        varchar(20) containment_area
        date feeding_schedule
    }
    
    TRIBBLE_RELATIONSHIPS {
        varchar(5) child_id PK, FK
        varchar(5) mother_id FK
        varchar(5) father_id FK
    }
    
    TRIBBLES }|--|| TRIBBLE_RELATIONSHIPS : ""

Implementation Benefits

By making the relationships in their own table, there’s no possibility of a child being its own parents, since there would be proper constraints in place. Additionally, since child_id is a primary key, it cannot be duplicate, which would stop some more issues that exist in the current database. Additionally, it would be quicker to calculate things like family trees if the relationships are their own table, since you don’t need to include all of the tribble data—just the relationships.

Starfleet Database Normalization

Current Schema Assessment

  • PERSONNEL → PLANET defaults to 'Earth', and SUPER refers back to the same table. Since the PLANET column is dependent on ID, but isn’t normalized further for roles or ranks. This is a transitive dependency and thus violates the third normal form.
  • ASSIGNMENT → POSITION is a varchar(10) field and its inconsistent across rows. For instance, ‘Com’, ‘Comd’, ‘Cmdr’, ‘XO’, ‘CMO’. This violates the second normal form since it mixes multiple roles semantically, and the same person can appear multiple times, but role info isn’t normalized—instead, it should map to another table of the proper position ranks or at least be constrained in some way.
  • MEDICAL_RECORD (my own ERD) → SPECIES is repeated for every medical record, even though it only depends on the person, not the individual exam. This violates the second normal form, since SPECIES is partially dependent on PERSON_ID, not the full primary key RECORD_ID. Also FITNESS_STATUS has no constraints which violates the third normal form, since it introduces possible inconsistencies and should reference a FITNESS_STATUS table or at least be constrained in some way.

Normalization Implementation

PERSONNEL

CREATE TABLE RANK (
	ID INT PRIMARY KEY,
	CODE CHAR(4) UNIQUE,
	TITLE VARCHAR(50)
)
 
CREATE TABLE PERSONNEL (
	ID CHAR(5) PRIMARY KEY,
	NAME VARCHAR(20),
	RANK_ID INT REFERENCES RANK(ID),
	PLANET VARCHAR(20) DEFAULT 'Earth' FOREIGN KEY REFERENCES PLANET(NAME),
	GENDER CHAR(1) CHECK (GENDER IN ('F', 'M')),
	WAGES DECIMAL(8,2) DEFAULT 12000
)
 
CREATE TABLE SUPERVISION (
  PERSON_ID CHAR(5) PRIMARY KEY FOREIGN KEY REFERENCES PERSONNEL(ID),
  SUPERVISOR_ID CHAR(5) FOREIGN KEY REFERENCES PERSONNEL(ID)
);

ASSIGNMENT

CREATE TABLE POSITION (
  ID INT IDENTITY PRIMARY KEY,
  TITLE VARCHAR(20) UNIQUE NOT NULL
);
 
CREATE TABLE ASSIGNMENT (
  PERSON CHAR(5) NOT NULL FOREIGN KEY REFERENCES PERSONNEL(ID) ON DELETE CASCADE,
  SHIP VARCHAR(8) NOT NULL FOREIGN KEY REFERENCES STARSHIP(REGISTRY),
  ASSIGNED DATE,
  POSITION_ID INT FOREIGN KEY REFERENCES POSITION(ID),
  CONSTRAINT PK_Assignment PRIMARY KEY (SHIP, PERSON)
);

MEDICAL_RECORD

CREATE TABLE FITNESS_STATUS (
  ID INT IDENTITY PRIMARY KEY,
  LABEL VARCHAR(20) UNIQUE NOT NULL,
  CONSTRAINT CK_Fitness_Label CHECK (LABEL IN ('Fit', 'Unfit', 'Needs Follow-up'))
);
 
CREATE TABLE MEDICAL_RECORD (
  RECORD_ID CHAR(5) PRIMARY KEY,
  PERSON_ID CHAR(5) FOREIGN KEY REFERENCES PERSONNEL(ID),
  EXAM_DATE DATE NOT NULL,
  FITNESS_STATUS_ID INT FOREIGN KEY REFERENCES FITNESS_STATUS(ID)
);

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Graph View