MySQL:Hire Co:DDL:HR

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

Sales, Hire and Repair Company - Creating the Human Resource tables

Icon Notes.png

Notes

This page is being transferred to the accounts. Then will be modified to show a compound primary key.


Icon Objective.png

Objective

Your task on this page is to

  • create the Human Resource (Timesheet) sub-system of the Hire repair company, and
  • to become familiar with:
    • the use of joins to link two tables and
    • the use of the SELECT statement to retrieve data.

Entity Relationship diagrams

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

Staff/Timesheet: ERD

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

Staff/Timesheet: Fully attributed ERD

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

Staff and Timesheet data

Create the following text file and call it HireHR.sql

## Hire Co: Database Creation and Human Resource sub-system#######
 
USE dbHire;
 
# Create and populate the Customer table -----------------
# I have added some extras from the diagram !!
 
DROP TABLE IF EXISTS tblStaff;
CREATE TABLE tblStaff
(
  StfId INT UNSIGNED NOT NULL AUTO_INCREMENT,
 
  StfLastName VARCHAR(40) NOT NULL,
  StfFirstName VARCHAR(40) NOT NULL,
  StfAddr1 VARCHAR(40),
  StfTownCity VARCHAR(40),
  StfPhone VARCHAR(20),
  StfGender ENUM('M','F'),
  StfDateStart DATE,
  StfHourlyRate DECIMAL(10,2),
    PRIMARY KEY (StfId),
    INDEX (StfLastName, StfFirstName)
);
# Don’t need the next two lines but checks all is working
SHOW TABLES;
DESCRIBE tblStaff;
# Insert six records
INSERT INTO tblStaff VALUES
  (\N,'Brown','Bill','12 Kea St', 'Napier','(06) 123-1111',
      'M','1999-10-01',12.5),
  (\N,'Digger','Dug','12 Dirt St', 'Napier','(06) 123-5554', 
      'M','2001-01-01',25.00),
  (\N,'Apple','Adam','11 Orchard St', 'Hastings','(06) 144-4444',
      'M', '1989-12-01',56.00),
  (\N,'Cupcake','Clare','12 bakery St', 'Flaxmere','(06) 123-7890',
      'F', '2005-01-01', 13.56),
  (\N,'Eggnog','Elly','16 Wippy Rd', 'Wellington','(04) 122-3344', 
      'F','2008-05-09',12.50),
  (\N,'Blue','Betty','16 Rainbow Rd', 'Napier','(06) 923-9876', 
      'F','2006-01-19',12.50)
;
SELECT * FROM tblStaff;

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

mySQL>SOURCE ..enter the drive and path../HireHR.sql

Now add the following to the script file HireHR.sql

# Create and populate the Timesheet table -----------------
DROP TABLE IF EXISTS tblTSheet;
CREATE TABLE tblTSheet
(
  TsId INT UNSIGNED NOT NULL AUTO_INCREMENT,
  StfID INT UNSIGNED NOT NULL,
  TsWkNum INT UNSIGNED,
  TsDsn VARCHAR(40),
  TsHrsWorked DECIMAL(10,2),
    PRIMARY KEY (TsId),
    FOREIGN KEY (StfId) REFERENCES tblStaff(StfId)
);
# Don’t need the next two lines but checks all is working
SHOW TABLES;
DESCRIBE tblTSheet;
# Insert eight records
INSERT INTO tblTSheet VALUES
  (\N,1,2,'Apples', 20),
  (\N,2,2,'Pears', 40),
  (\N,3,2,'Apples',25),
  (\N,1,3,'Plums', 30),
  (\N,3,3,'Apples',25),
  (\N,1,4,'Apples',10),
  (\N,2,4,'Pears', 10),
  (\N,3,4,'Apples',15); 
SELECT * FROM tblTSheet;

Now run this script in MySQL

mySQL>SOURCE ..enter the drive and path../HireHR.sql

Displaying the data using DQL

Create the following text file and call it HireHR1.sql

## HireCo: DML SELECT examples =========
 
USE dbHire;
 
# 1. Displaying the business table
#    (This should have been created previously and shows that
#     you have the right database selected.)
 
SELECT * FROM tblBusiness;
 
# 2. Displaying the staff table
SELECT * FROM tblStaff;
 
# 3. Displaying the Timesheet table
SELECT * FROM tblTSheet;
 
# 4. Sorting columns
SELECT StfId, StfLastName, StfFirstName, StfAddr1, StfTownCity 
  FROM tblStaff
  ORDER BY StfLastName DESC;
 
# 5. Filtering data 
 SELECT * FROM tblTSheet WHERE TsDsn LIKE 'P%';
 
# 6. Count the number of staff
SELECT COUNT(*) FROM tblStaff;
 
# 7. Count the number of Timesheets for each staff member
SELECT StfId, Count(*) From tblTSheet GROUP BY StfId;
 
# 8. Change Elly Eggnogs phone number
UPDATE tblStaff
  SET StfPhone = '(027) 999-9999' 
  WHERE StfLastName = 'Eggnog';

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

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

Staff and Timesheet data

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).

Result of an Inner Join between Staff and Timesheet

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

# 9. Display data from both the Staff and Timesheet tables
#    INNER JOIN - This will list Staff WITH timesheets
#    Note that the default order is on the INDEX stfLastName
 
SELECT tblStaff.StfId, tblStaff.StfLastName AS LastName,
       tblTSheet.TsWkNum 
  FROM tblStaff 
  INNER JOIN tblTSheet 
  ON tblStaff.StfId = tblTSheet.StfId;
 
# 10. Display data from both the Staff and Timesheet tables
#     This will change the order and list by tblStaff.StfId
 
SELECT tblStaff.StfId, tblStaff.StfLastName AS LastName,
       tblTSheet.TsWkNum 
  FROM tblStaff 
  INNER JOIN tblTSheet 
  ON tblStaff.StfId = tblTSheet.StfId
  ORDER BY tblStaff.StfId;
 
# 11. Count the number of Timesheets for each staff member
#    and show their name
SELECT tblTSheet.StfId, tblStaff.StfLastName, Count(*) 
  FROM tblTSheet
  INNER JOIN tblStaff
  ON   tblTSheet.StfId = tblStaff.StfId
  GROUP BY StfId;

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.

Result of an Left Join between Staff and Timesheet

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

# 12. Display data from both the Staff and Timesheet tables
#     LEFT JOIN = This will list ALL staff members
 
SELECT tblStaff.StfId, tblStaff.StfLastName AS LastName,
       tblTSheet.TsWkNum 
  FROM tblStaff 
  LEFT JOIN tblTSheet 
  ON tblStaff.StfId = tblTSheet.StfId
  ORDER BY tblStaff.StfId;

Tasks

A. Modify the SELECT statement in # 9 to include:

  • StfId , StfLastName,StfFirstName, StfHourlyRate and
  • TsId, TsWkNum, TsDsn, TsHrsWorked,

changing the column headings to be less geeky!!

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)

Icon References.png References


MySQL:Hire Co:DDL:HR. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 18, 2017, from http://www.virtualmv.com/wiki/index.php?title=MySQL%3AHire_Co%3ADDL%3AHR    (zotero)