Group4/wagetheft.sql at master · TomRigg/Group4 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

DROP TABLE IF EXISTS Employee CASCADE CONSTRAINTS;
DROP TABLE IF EXISTS Employer CASCADE CONSTRAINTS;
DROP TABLE IF EXISTS Jobs CASCADE CONSTRAINTS;
DROP TABLE IF EXISTS Hours CASCADE CONSTRAINTS;
DROP TABLE IF EXISTS Paycheck CASCADE CONSTRAINTS;


CREATE TABLE Employee (
  employeeID INT AUTO_INCREMENT,
  jobID INT,
  name VARCHAR(40) NOT NULL,
  phoneNumber VARCHAR(15) DEFAULT NULL,
  employeeAddress VARCHAR(80) NOT NULL,
  employeeType INT(1) NOT NULL,
  PRIMARY KEY(EmployeeID)
)

CREATE TABLE Employer (
  employerID INT AUTO_INCREMENT,
  employerAddress VARCHAR(40),
  PRIMARY KEY(EmployerID)
)

CREATE TABLE Jobs (
  jobID INT AUTO_INCREMENT,
  employerID INT,
  employeeID INT,
  hourlyWage DECIMAL(6,2),
  workAddress VARCHAR(80),
  PRIMARY KEY(JobID),
  FOREIGN KEY(employerID) REFERENCES Employer(employerID),
  FOREIGN KEY(employeeID) REFERENCES Employee(employeeID)
)


CREATE TABLE Hours (
  hoursID INT unsigned NOT NULL,
  jobID INT,
  date VARCHAR(40),
  workAddress VARCHAR(80),
  hoursWorked INT(3),
  wagesEarned INT(6),
  PRIMARY KEY (HoursID),
  FOREIGN KEY (JobID) REFERENCES Jobs(JobID)
)

CREATE TABLE Paycheck (
  paycheckID int unsigned NOT NULL,
  jobID INT,
  checkDate VARCHAR(20),
  hoursWorked INT(3),
  wagesEarned INT(6),
  PRIMARY KEY (paycheckID),
  FOREIGN KEY (JobID) REFERENCES Jobs(JobID)
)


ALTER TABLE Employee ADD CONSTRAINT FOREIGN KEY (jobID) REFERENCES Jobs(JobID);