Relational Model - ER Diagram

Laboratory and Organization

The system stores information about laboratories, departments, employees, roles, and work schedules.

A laboratory (LAB) contains multiple departments (DEPARTMENT), while each department employs multiple employees (EMPLOYEE). Employee information includes personal data, EMBG, hiring date, and activity status.

Since employees may have multiple roles, the many-to-many relationship between EMPLOYEE and EMPLOYEE_ROLE is resolved through EMPLOYEE_ROLE_ASSIGNMENT. Employee schedules are managed through EMPLOYEE_SCHEDULE.

Equipment

The system manages laboratory equipment through the LAB_EQUIPMENT entity. Each piece of equipment stores information such as name, model, serial number, and status.

Employee qualifications for operating equipment are managed through EMPLOYEE_EQUIPMENT_SKILL, which resolves the many-to-many relationship between employees and equipment.

Patients and Requests

Patients are stored in the PATIENT entity, including personal information, EMBG, gender, contact details, and status.

Each patient visit generates a LAB_REQUEST. Every request belongs to exactly one patient and one laboratory and contains information about the request date and status.

Samples

A laboratory request may contain multiple samples. Samples are stored in the SAMPLE entity and include barcode, collection date, status, sample type, and the employee responsible for collection.

Tests and Analysis Packages

The system supports individual laboratory tests and analysis packages.

TEST_TYPE defines categories of tests, while TEST stores specific laboratory tests and their prices.

ANALYSIS_PACKAGE represents groups of tests. The many-to-many relationship between packages and tests is implemented through TEST_IN_PACKAGE.

Orders

Patients can order either individual tests or analysis packages.

• ORDERED_TEST – individual tests
• ORDERED_PACKAGE – analysis packages

Both entities are linked to LAB_REQUEST.

Results

Laboratory results are stored in TEST_RESULT, the central entity of the system.

Each result is associated with:

• a sample
• a test
• a result parameter
• the employee who entered the result

Result values are stored as VARCHAR because they may contain either numerical or textual values.

The system enforces an XOR constraint, ensuring that each result originates either from ORDERED_TEST or from ORDERED_PACKAGE, but never from both simultaneously.

ER Diagram