software_homework/create_database.sql at master · Jometeorie/software_homework · 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
create database TWS;
use TWS;

create table EMPLOYEE (
	EID		varchar(10) not null,
	Name	varchar(10) not null,
	Depart	varchar(20),
	Soncmp	varchar(20),
	Worktype varchar(10),  -- normal or expert;
	primary key (EID)
);

create table TOOL (
	TID		varchar(10) not null,
	Name	varchar(10) not null,
	Tooltype varchar(10),  -- expensive or cheap
	Soncmp	varchar(20),
	Good	boolean default true,
	primary key (TID)
);

create table LEND (
	LID	 varchar(10) not null,
	Lendtime time,
	EID		varchar(10) not null,
	TID		varchar(10) not null,
	foreign key (EID) references EMPLOYEE(EID) ON DELETE CASCADE,
	foreign key (TID) references TOOL(TID) ON DELETE CASCADE,
	primary key (LID, EID, TID)
);

create table LOGIN (
	EID		varchar(10) not null,
	Password varchar(10) not null,
	foreign key (EID) references EMPLOYEE(EID) ON DELETE CASCADE,
	primary key (EID, Password)
);


create table LENDTMP (
	EID		varchar(10) not null,
	Lendtime	time,
	TID		varchar(10) not null,
	primary key (EID, TID, Lendtime));