Exercise Notes

Learning goals

  • Using databases
  • Linking code to datasets

Programs used

Your task is to build a simple Employment Management Database. To keep it interesting, there are a few non-obvious requirements… Here’s the specification:

  1. Maintain a list of employees (employee number, first name, last name, age, salary). The employee number should be automatically generated by the database.
  2. Each employee should also have a Job Position. Store this as a link to a separate list of Job Positions.
  3. Each employee has a pension fund, that should be stored separately from the employee (i.e. in a separate table). This pension fund should contain the amount contributed, and the pension provider. A list of pension providers should be stored in yet another table; include a column which indicates which one of the providers is the default provider for new employees.

Design the database

Start off by drawing an Entity Relationship Diagram for the Employment Management Database. You can use pen and paper for this.

Hint

Remember to:

  • Mark the primary keys by underlining them
  • Make it clear which fields the relationships are on (it may be simplest to do this by making sure that the relationship lines go all the way to the relevant column)

Create the database

Now create a new database and use CREATE TABLE and other SQL statements to define the schema that you’ve designed.

PostgreSQL

You should have PostgreSQL already setup from the Bookish exercise earlier in the course, so it is recommended that you create that database with that.

You’ll also have pgAdmin, which includes a SQL editor in which you can run your SQL commands. Save the commands that you’re executing that you have a record and can review them with your trainer.

The examples in your reading for this module were written in a different dialect of SQL intentionally – this is a good opportunity for you to compare and contrast.

Refer to the PostgreSQL documentation to check syntax and explore other SQL commands that weren’t discussed in the reading material.

Queries

Once your Employment Management Database exists, create queries to do the following:

  1. Populate the database with some sample data. Make sure you use INSERTs rather than doing it via the graphical interface!

  2. Return the names of everyone older than a certain age. You want to return a single column that has their full name in it (e.g. “Fred Bloggs”, not “Fred” and “Bloggs” separately which is how it should be stored in your database)

  3. Each month, the employer contributes 5% of an employee’s salary into their pension fund. Write a query that increases the value of everyone’s pension fund by one month’s worth of contributions.

    Hint

    For this point, make sure you've read the "Update with Select" section of the reading material.

  4. Find the average salary for each job position

  5. Work out how many people have their funds with each of the pension providers

  6. Find all the employees without pension funds

  7. Modify the previous query to create pension funds for all those employees, with the default pension fund provider (default provider should be a column on your pension provider table)

Transactions

  1. Which of the following operations on your Employment Management Database ought really to use a separate transaction? Explain why in each case.

    • Increment everyone’s pension funds by 5% of their salary (you’ve written a query to do this previously).
    • Add a new employee and their (presumably zero-balance) pension fund.
    • Create a new job position, and then promote two named employees into that role (increasing their salary by 10% while you’re at it).
    • Run a series of UPDATE statements on the employees table, updating people’s salaries to new values. Each statement affects just a single employee, and the new salary for that employee is a hard-coded amount (e.g. UPDATE Employees SET Salary = 28000 WHERE Id = 17).
    • The same series of statements but this time the employee’s salary increase is coded into the SQL query as a percentage (e.g. UPDATE Employees SET Salary = Salary * 1.03 WHERE Id = 17).

  2. Find a real world example of transactionality in the project you’re working on. This could be:

    • Use of BEGIN TRANSACTION in the database
    • A scenario where several parts of your application logic must happen or not happen as a single unit
    • Or even a situation where transactionality ought really to be enforced but isn’t (either by design, because it would be complex to do it, or by accident…)

    Make some brief notes on the scenario, how transactionality is implemented (or how it could be implemented, if it’s not!), and what would / does happen if transactionality was not implemented.

Integrate with code

Create a console application providing an interface to your Employment Management Database. At a minimum it should:

  • List all the employees together with their job role, salary and pension fund balance
  • Allow the user to execute the procedure that adds 5% of salary to the pension fund

Migration (Stretch)

As a stretch goal you could add migration support to your system.

Can you set up migrations to create the whole database from scratch?