What you see below is an Entity Relationship Diagram, or ERD, of a very small Human Resources (HR) database schema. The 7 tables you see represent entities that have various relations with one another.
The bolded text is the name of the entity (table), while the purple text represents the primary key. You will notice that most of the entries in purple text end in _id with one exception in the JOB_HISTORY table.
I would like you to take this HR schema and create a database in MySQL from it using appropriate data types, appropriate database engine, setting up primary and foreign keys (remember, foreign keys require a certain MySQL database engine), etc. Call your database HR, just as you see below.
As you can see in this sample schema, each block represents an entity or table as we call it, once it reaches our database. The black BOLDED text represents the name of the table. The entries under the name represent the various fields (columns), while the purple entries represent primary keys. Some of the tables have a single relationship with another table, while others have relationships with multiple tables.
You'll also notice that there are a number of column names that end in "_id", but are not the purple colored primary keys for the table. If you look, almost every one of these point to a primary key in another table, making them foreign keys. You'll also notice a particularly strange relationship that has the EMPLOYEES table relating back to itself.
Questions to Answer
1) There are actually three types of keys in the JOB_HISTORY table. What are those three types of keys? Remember that some keys can actually be of more than one type.
3) You'll notice a strange relationship that the EMPLOYEES table has back to itself. This is known as a recursive relationship. Looking at the fields in EMPLOYEES, why would this table have a relationship back to itself?
5) This version of MySQL doesn't implement check constraints. With that in mind, how might you check to ensure dates are entered correctly? For example, that the start_date is prior to the end_date in the JOB_HISTORY table?