According to the project requirements, the table in the mysql should be the following four:
(1)admin
(2)adminlogs
(3)room
(4)tenant(client)
open the navicat for mysql, it should be typed as the following codes into the command line through my thoughts.
(1)admin
CREATE TABLE admin (
aid VARCHAR(50) NOT NULL,
name VARCHAR(50) NOT NULL,
password VARCHAR(32) NOT NULL,
lastdate DATETIME ,
phone VARCHAR(50) ,
photo VARCHAR(50) ,
flag INT ,
CONSTRAINT pk_aid PRIMARY KEY(aid)
)ENGINE=INNODB;
(2)adminlogs
CREATE TABLE adminlogs(
alid INT AUTO_INCREMENT,
aid VARCHAR(50),
logindate DATETIME ,
CONSTRAINT pk_alid PRIMARY KEY(alid),
CONSTRAINT fk_aid FOREIGN KEY(aid) REFERENCES admin(aid) ON DELETE SET NULL
)ENGINE=INNODB;
(3)room
CREATE TABLE room(
rid INT AUTO_INCREMENT,
item VARCHAR(20) ,
price INT,
note VARCHAR(200),
flag INT ,
CONSTRAINT pk_rid PRIMARY KEY(rid)
)ENGINE=INNODB;
(4)tenant
CREATE TABLE tenant(
tid INT AUTO_INCREMENT,
aid VARCHAR(50) ,
rid INT,
name VARCHAR(50),
phone VARCHAR(20),
card VARCHAR(20),
check_in DATETIME,
departure DATETIME,
CONSTRAINT pk_tid PRIMARY KEY(tid) ,
CONSTRAINT fk_aida FOREIGN KEY(aid) REFERENCES admin(aid) ON DELETE SET NULL,
CONSTRAINT fk_rida FOREIGN KEY(rid) REFERENCES room(rid) ON DELETE SET NULL
)ENGINE=INNODB;
Command line命令列
project requirements项目需求
tenant承租人