aRBAF6h0

1drop database if exists timesheet_app;
2create database timesheet_app;
3use timesheet_app;
4
5create table projects (
6	project_id integer primary key auto_increment,
7	name varchar(255) not null
8);
9
10create table task_types (
11	task_type_id integer primary key auto_increment,
12	description varchar(255) not null
13);
14
15create table users (
16	payroll_no integer primary key auto_increment,
17	first_name varchar(255) not null,
18	surname varchar(255) not null,
19	password varchar(255) not null,
20	manager_id int references users(payroll_no)
21);
22
23create table timesheets (
24	timesheet_id integer primary key auto_increment,
25	date_first_day date not null,
26	submitted_on datetime,
27	authorised_on datetime,
28	authorised_by int references users(payroll_no),
29	payroll_no int references users(payroll_no)
30
31);
32
33create table task_rows (
34	project_code int references projects(project_id),
35	task_type_id int references task_types(task_type_id),
36	timesheet_id int references timesheets(timesheet_id),
37	mon_hours int not null  default 0 check (mon_hours > 0),
38	tue_hours int not null  default 0 check (tue_hours > 0),
39	wed_hours int not null  default 0 check (wed_hours > 0),
40	thu_hours int not null  default 0 check (thu_hours > 0),
41	fri_hours int not null  default 0 check (fri_hours > 0),
42	sat_hours int not null  default 0 check (sat_hours > 0),
43	sun_hours int not null  default 0 check (sun_hours > 0),
44	primary key (project_code, task_type_id, timesheet_id)
45);
46
47insert into projects(name)
48values ('ABC Project'), ('Internal Website'), ('Other Project');
49
50insert into task_types(description)
51values ('Daily Standup'), ('Meeting'), ('Phone Call'), ('Development'), ('Testing');
52
53insert into users(first_name, surname, password, manager_id)
54values
55('Joe', 'Bloggs', 'password', '1'),
56('Danny', 'Smith', 'password', '1');