数据库设计初探（宿舍管理系统）

服务器端数据库的创建： dor_manage

create database dor_manage default character set utf8 collate utf8_general_ci;

create table building(

    -> BuildID char(4) not null primary key,                                                 

    -> Adress varchar(255) not null                                                          

    -> );

mysql> create table dorm(
-> BuildID char(4) not null references building(BuildID),
-> DormID char(8) not null primary key,
-> StudentNum int not null default 0
-> );

mysql> create table admin(
-> ID int(13) not null primary key,
-> Name varchar(255) not null,
-> Password char(16) not null,
-> Photonumber char(14) not null,
-> BuildID char(4) not null references building(BuildID)
-> );

mysql> create table student(
-> StudentID int(13) not null primary key,
-> Password char(16) not null,
-> Name varchar(255) not null,
-> Photonumber char(14) not null,
-> BuildID char(4) null references building(BuildID),
-> DormID char(10) null references dorm(DormID),
-> Gender tinyint not null,
-> Grade varchar(255) not null,
-> Birthplace varchar(255) not null
-> );

mysql> create table application_add(
-> ID int(13) not null auto_increment primary key,
-> StudentID int(13) not null references student(StudentID),
-> Reason varchar(255) not null,
-> Applydate date not null,
-> StatusS int(2) not null default 0,
-> StatusA int(2) not null default 0
-> );

 

mysql> create table application_change(
-> ID int(13) not null auto_increment primary key,
-> StudentID1 int(13) not null references student(StudentID),
-> StudentID2 int(13) not null references student(StudentID),
-> Reason varchar(255) not null,
-> Applydate date not null,
-> StatusS int(2) not null default 0,
-> StatusA int(2) not null default 0
-> );

mysql> create table photo(
-> ID int not null auto_increment primary key,
-> Photo blob comment'照片'
-> )
-> engine =InnoDB
-> default charset=utf8
-> collate = utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)

将照片ID加到student 和admin表中：

至此数据库基本建完，数据库的后续使用和维护

也会后续实验