project-one-BoydHarold/ExpenseReimbursementSystem.sql at master · PizzaStack/project-one-BoydHarold · 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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
DROP TABLE IF EXISTS EmployeeUsers;
DROP TABLE IF EXISTS ManagerUsers;
DROP TABLE IF EXISTS Reimbursement;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Manager;


CREATE TABLE Employee
(
	EmployeeID SERIAL PRIMARY KEY,
	FirstName varchar(50) NOT NULL,
	LastName varchar(50) NOT NULL,
	EmailAddress varchar(50) NOT NULL,
	Address varchar(50) NOT NULL,
	Status smallint NOT NULL
);

INSERT INTO Employee (FirstName, LastName, EmailAddress, Address, Status)
VALUES ('Dwight','Schrute','[email protected]','123 Wallaby Ln',1),
('Jim','Halpert','[email protected]','456 Zoo Ave',1),
('Pam','Beesly','[email protected]','456 Zoo Ave',1),
('Kevin','Malone','[email protected]','333 W 3rd St',1),
('Angela','Martin','[email protected]','583 E 5th Dr',1),
('Stanley','Hudson','[email protected]','443 Scranton Ln',1),
('Meredith','Palmer','[email protected]','666 Yuri Ct',1),
('Oscar','Martinez','[email protected]','190 Virtuoso Ln',1),
('Creed','Bratton','[email protected]','The Pit by the Query',1),
('Phyllis','Lapin','[email protected]','449 Robert Dr',1),
('Toby','Flenderson','[email protected]','302 Nowhere Pl',1),
('Darryl','Philblin','[email protected]','542 Warehouse Zn',1);

CREATE TABLE Manager
(
	ManagerID SERIAL PRIMARY KEY,
	FirstName varchar(50) NOT NULL,
	LastName varchar(50) NOT NULL,
	EmailAddress varchar(50) NOT NULL,
	Address varchar(50) NOT NULL,
	Status smallint NOT NULL
);

INSERT INTO Manager (FirstName, LastName, EmailAddress, Address, Status)
VALUES ('Michael','Scott','[email protected]','736 Good Pl',1);
INSERT INTO Manager (FirstName, LastName, EmailAddress, Address, Status)
VALUES ('Jan','Levinson','[email protected]','440 Turkey Rd',1);

CREATE TABLE EmployeeUsers
(
	UserID SERIAL PRIMARY KEY,
	EmployeeID int REFERENCES Employee(EmployeeID),
	Username varchar(50) NOT NULL,
	Password varchar(50) NOT NULL,
	Status smallint NOT NULL
);

INSERT INTO EmployeeUsers (EmployeeID, Username, Password, Status)
VALUES (1,'schruted','ilovebeets',1),
(2,'halpertj','bearsbeetsbattlestargalactica',1),
(3,'beeslyp','iloveart',1),
(4,'malonek','snickers',1),
(5,'martina','sprinkles',1),
(6,'palmerm','imnotanalcoholic',1);

CREATE TABLE ManagerUsers
(
	UserID SERIAL PRIMARY KEY,
	ManagerID int REFERENCES Manager(ManagerID),
	Username varchar(50) NOT NULL,
	Password varchar(50) NOT NULL,
	Status smallint NOT NULL
);

INSERT INTO ManagerUsers (ManagerID, Username, Password, Status)
VALUES (1,'scottm','thatswhatshesaid',1);
INSERT INTO ManagerUsers (ManagerID, Username, Password, Status)
VALUES (2,'levinsonj','astrid',1);

CREATE TABLE Reimbursement
(
	ReimbursementID SERIAL PRIMARY KEY,
	EmployeeID int REFERENCES Employee(EmployeeID) NOT NULL,
	ManagerID int REFERENCES Manager(ManagerID),
	Status smallint NOT NULL,
	Title varchar(50) NOT NULL,
	Description varchar(500) NOT NULL,
	Amount numeric(100,2) NOT NULL,
	ReimbursementDate date NOT NULL,
	ReceiptLocation varchar(200) NOT NULL
);