Recap - linking tables in databases

(And entities in java)

• In relational databases entities are linked together by one entity/table having a reference (a so called Foreign Key) to another tables primary key.
• In the below illustration we see how a One-To-Many relationship between a department and its employees are created by allowing each employee to reference the id of the department to which he/she belongs. Now a department can have many employees and an employee must have one and only one department.

• If we want to ensure, that no employee can exist without a reference to an existing department we create a FOREIGN KEY CONSTRAINT

• eg. CONSTRAINT fk_employee_department FOREIGN KEY (FK_dept_id) REFERENCES department(dept_id)

• Now if we try to add a new employee without setting an existing department id we get a foreign key constraint violation.

• In this way we ensure data integrity (that no employee can be entered in a form that is not acceptable to the business logic)

• In Object Oriented Languages like Java we use object references from one class to another to ensure that one object is related to another:

  

• Above we can see how the ‘Department’ class has a field called ‘employees’ which is a List with a reference to all the employees in the department.

• We can now say that the way we make relationships i OO languages vs. in relational databases a different in the way that in DB the child knows its parent while in OO the parent knows its Children (and the Child can off cause know its parent if we have a bi-directional relationship where in this case Employee would also have a reference to its Department object)

Exercise

• Discuss how you would create your database if employees can be hired and only later be assigned to a department. Implement a database with the 2 entities and create 3 new employees and 2 departments. Let one employee be without department.
• Change your database so that now the database will create an error if an employee is created without a valid department.
• Now change your database so that an employee can be connected to more than one department.
• Make a new java application and create a DataMapper class with the following interface:
  • createEmployee(Employee emp) //This method should persist the given Employee into the database and ensure that the employee is registered in the right department(s) in the database.
  • Hint: The Employee class should have a List departments that can hold references to the departments.
• If you have the time and energy try implementing the use of transactions in your Mapper method. The idea in using a transaction is that we can make multiple sql statements run as ONE. This means that either all statements in the transaction goes through or none of them does. This could be usefull if we wanted to persist an employee and a department (that does not yes exist in the database). Then if we group the creation of the department with the creation of the employee in a single transaction we do not risk creating only one of them.
  • Hint see this link: Using transaction in your java code (JDBC)