Part A: Entity-Relationship Modelling

Part A: Entity-Relationship Modelling

Task 1: Designing an Entity-Relationship Model

Metro Trains Study

Here's an ER diagram for the Metro Trains Management Database:

Lucid chart diagram

Entities:

TrainLine (name, distance)

Station (name, phone_number, station_number)

Facility (facility_number)

Train (train_number, passenger_capacity, model, manufacturer, year_made)

Employee (employee_number, first_name, last_name, birth_date)

Driver (weak entity, inherits attributes from Employee)

Supervisor (weak entity, inherits attributes from Employee)

Relationships:

HAS_STATIONS (Train Line - Station, Many-to-Many)

PROVIDES (Station - Facility, Many-to-Many)

ASSIGNED_TO (TrainLine - Train, One-to-Many)

WORKS_ON (Driver - TrainLine, Many-to-Many)

SUPERVISES (Supervisor - Station, One-to-One)

Assumptions:

A station can have multiple facilities of the same type (e.g., multiple vending machines).

A train can only be assigned to one train line at a time (excluding maintenance).

A driver can work on multiple train lines.

Only premium stations have supervisors.

Task 2: Optimize an Entity-Relationship Model

Advanced Metro Trains Study

Building upon the initial ER diagram, here's the optimized version incorporating the additional requirements:

Lucid chart diagram

Entities:

TrainLine (name, distance)

Station (name, phone_number, station_number)

Platform (platform_number)

TrainRun (run_number, is_express)

Facility (facility_number)

Train (train_number, passenger_capacity, model, manufacturer, year_made)

Employee (employee_number, first_name, last_name, birth_date)

Driver (weak entity, inherits attributes from Employee)

Supervisor (weak entity, inherits attributes from Employee)

Manager (weak entity, inherits attributes from Employee)

Relationships:

HAS_STATIONS (TrainLine - Station, Many-to-Many) - retains functionality from previous model

HAS_PLATFORMS (Station - Platform, One-to-Many) - new relationship

HAS_RUNS (TrainLine - TrainRun, One-to-Many) - new relationship

PROVIDES (Station - Facility, Many-to-Many) - retains functionality from previous model

ASSIGNED_TO (TrainRun - Train, One-to-One) - new relationship

RUNS_ON (TrainRun - Station, Many-to-Many) - new relationship (with departure time attribute)

DRIVES (TrainRun - Driver, One-to-Many) - new relationship (with primary/assistant driver roles)

WORKS_ON (Driver - TrainLine, Many-to-Many) - retains functionality from previous model

SUPERVISES (Supervisor - Station, One-to-One) - retains functionality from previous model

MANAGES (Manager - Employee, One-to-Many) - new relationship (with start date attribute)

Assumptions:

A station can have multiple platforms numbered sequentially.

A train run has one assigned train and departs from a specific platform at a particular station at a scheduled time.

A train run can have a primary driver and an assistant driver.

An employee can have one manager at a time.

Task 3: Mapping an ER Model to a Relational Database Schema

Identify Entities and Relationships:

Entities become tables in the relational schema.

Relationships are transformed into foreign keys linking related tables.

Convert Cardinalities:

One-to-One: Primary key of one table becomes a foreign key in the other table (or vice versa).

One-to-Many: Primary key of the "one" side becomes a foreign key in the "many" side's table.

Many-to-Many: Create an intersection table with foreign keys.

Resolve Complexities:

Weak entities inherit primary keys from their parent entities.

Many-to-Many relationships require additional intersection tables.

Normalize the Schema:

Eliminate data redundancy and improve data integrity.

This may involve further table splitting and foreign key additions.

Example for the Metro Trains scenario (considering Task 2's model):

Tables:

TrainLine (train_line_id, name, distance)

Station (station_id, name, phone_number, station_number)

Platform (platform_id, station_id, platform_number) - Foreign key to Station(station_id)

Facility (facility_id, facility_type) - Consider adding a foreign key to Station(station_id) if facilities are specific to stations.

Train (train_id, passenger_capacity, model, manufacturer, year_made)

Employee (employee_id, first_name, last_name, birth_date)

Driver (employee_id) - Primary key of Employee becomes the sole attribute in Driver (assuming no additional driver-specific attributes).

Supervisor (employee_id) - Primary key of Employee becomes the sole attribute in Supervisor.

Manager (employee_id, manager_id, start_date) - Primary key of Employee becomes the sole attribute in Manager, with additional attributes for manager_id (foreign key to Employee(employee_id)) and start_date.

TrainRun (train_run_id, train_line_id, run_number, is_express) - Foreign key to TrainLine(train_line_id)

AssignedTrain (train_run_id, train_id) - Many-to-Many relationship between TrainRun and Train.

RunsOn (train_run_id, station_id, departure_time) - Many-to-Many relationship between TrainRun and Station, with departure_time attribute.

Drives (train_run_id, employee_id, is_primary) - Foreign keys to TrainRun(train_run_id) and Employee(employee_id), with an additional attribute (is_primary) to denote primary/assistant driver.

Part B: Relational Database Model

Task 4: Relational Database Model

Answer 4.1: Missing Foreign Key Constraints

The missing foreign key constraints are:

Employees.department_id -> Departments.department_idJobHistory.employee_id -> Employees.employee_idJobHistory.department_id -> Departments.department_idUpdated schema with foreign keys denoted by an asterisk (*):

SQL

Employees(employee_id*, first_name, last_name, phone_number, hire_date, empjob_id, salary, department_id*)

Departments(department_id*, department_name, manager_id*, location_id*)

Jobs(job_id*, job_title, min_salary, max_salary)

Locations(location_id*, street_address, postal_code, city, state_province, country_id*)

Countries(country_id*, country_name)

JobHistory(employee_id*, start_date, end_date, job_id*, department_id*)

Answer 4.2: Working on Different Jobs

Yes, the schema allows an employee to work on different jobs. The JobHistory table tracks past contracts with various job_id entries for a single employee_id.

Answer 4.3: Working for Different Departments

Yes, the schema allows working for different departments. Similar to jobs, JobHistory records department changes with distinct department_id entries for the same employee_id.

Answer 4.4: Multiple Locations for a Department

No, the current schema restricts a department to a single location due to the foreign key constraint Departments.location_id -> Locations.location_id.

To allow multiple locations, you could:

Create a DepartmentLocation table as a many-to-many relationship between Departments and Locations.

Modify Departments.location_id to allow null values (not recommended as it weakens data integrity).

Answer 4.5: Update Issues and Past Contracts

The UPDATE statement has issues:

It updates both empjob_id and hire_date, which might not be intended for a job change within the same company.

It finds employees based on salary, which is not a reliable identifier for a specific employee.

Finding past contracts requires querying the JobHistory table based on employee_id. The current schema allows retrieving Adam Smith's past job history.

Answer 4.6: Deleting a Location

No, the DELETE statement from Locations WHERE location_id=10 might fail due to referential integrity constraints. Departments referencing location_id 10 would prevent deletion. You'd need to:

Alternatively, consider deactivating the location instead of deletion (adding an active flag to the Locations table).

Answer 4.7: Creating the JobHistory Table

Here's the SQL statement to create the JobHistory table:

SQL

CREATE TABLE JobHistory (

employee_id INTEGER NOT NULL,

start_date DATE NOT NULL,

end_date DATE,

job_id INTEGER NOT NULL,

department_id INTEGER NOT NULL,

PRIMARY KEY (employee_id, start_date),

FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),

FOREIGN KEY (job_id) REFERENCES Jobs(job_id),

FOREIGN KEY (department_id) REFERENCES Departments(department_id)

);

Answer 4.8: Inserting New Employee and Manager

The statements will partially work:

The employee insertion (Scott Wallace) is successful.

However, the department insertion for "Art" fails because there's no department with ID 4 (assuming auto-increment for department_id).

Here's the corrected approach:

Insert a record into Departments for "Art" with a manager assigned later.

Retrieve the department_id for "Art" to use in the manager insertion.

SQL

-- Insert Department for "Art" (assuming auto-increment for department_id)

INSERT INTO Departments (department_name, manager_id, location_id)

VALUES ('Art', NULL, 20);

-- Retrieve department_id for "Art"

SELECT department_id FROM Departments WHERE department_name = 'Art';

-- Insert Scott Wallace as manager of "Art" (replace actual department_id)

INSERT INTO Employees (first_name, last_name, phone_number, hire_date, empjob_id, salary, department_id)

VALUES ('Scott', 'Wallace', '1111', '2020-01-01', 10, 140000, department_id_from_previous_query);

-- Update Departments to set Scott Wallace as manager (optional, if not done in previous insert)

UPDATE Departments SET manager_id = employee_idFROM Departments

INNER JOIN Employees ON Departments.department_name = 'Art' AND Employees.first_name = 'Scott' AND Employees.last_name = 'Wallace';

This approach ensures proper department creation, retrieves the department ID, and inserts Scott Wallace as the manager for the "Art" department.

END OF THE ASSIGNMENT