MySQL:Hire Co:DDL:HR

Entity Relationship diagrams
In the design phase the staff/timesheet entities and relationships were described



After discussions with the client, the fully attributed ERD following was created



Note that Primary keys are underlined and foreign keys have a * placed next to them (but only in the diagram - do not put a * in the field name).

Create the database and tables and add some data
We will now add the following data to the staff and timesheet tables



Create the following text file and call it HireHR.sql

Now run this script in MySQL to test it is all working

Now add the following to the script file HireHR.sql

Now run this script in MySQL

Displaying the data using DQL
Create the following text file and call it HireHR1.sql

Now run the script (SOURCE) to see that all is working.

Joining the staff and timesheet tables and displaying the data using DQL


An INNER JOIN allows us to link two tables together. INNER means that a table is created when there is a link between both tables. So in the example, there are no time-sheets for number 4 (Clare Cupcake) and 6 (Betty Blue).

Task: Add the following to the text file (HireHR1.sql)

A LEFT JOIN allows us to link two tables together, however a LEFT JOIN means that a table is created that shows all of the data from the left hand table (tblStaff) and only data on the right when there is a link. So in the example, all staff are included with timesheet data for number 4 (Clare Cupcake) and 6 (Betty Blue) set to NULL.

Task: Add the following to the text file (HireHR1.sql)

Tasks

A. Modify the SELECT statement in # 9 to include: changing the column headings to be less geeky!!
 * StfId, StfLastName,StfFirstName, StfHourlyRate and
 * TsId, TsWkNum, TsDsn, TsHrsWorked,

B. Use the PowerPoint examples to practice creating your own SELECT statements.

Trouble shooting

 * 1) Check that the data types for both Primary and Foreign keys are the same.
 * 2) Check that the tables are created in the correct order (e.g. staff before timesheets)