【课程设计】数据库设计

数据库设计

使用id为主键
共12张表

administrator表

CREATE TABLE `administrator` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

user表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int DEFAULT NULL,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Sno` (`Sno`),
  CONSTRAINT `Sno` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

book表

CREATE TABLE `book` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Bno` varchar(30) NOT NULL,
  `Bname` varchar(30) DEFAULT NULL,
  `Bauthor` varchar(30) DEFAULT NULL,
  `Bprice` double(5,2) DEFAULT NULL,
  `Bnum` int DEFAULT NULL,
  `Bshelf` varchar(30) DEFAULT NULL,
  `Bremark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Bshelf` (`Bshelf`),
  KEY `Bno` (`Bno`),
  CONSTRAINT `Bshelf` FOREIGN KEY (`Bshelf`) REFERENCES `bookshelf` (`no`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

bookself表

CREATE TABLE `bookshelf` (
  `id` int NOT NULL AUTO_INCREMENT,
  `no` varchar(30) NOT NULL,
  `area` varchar(30) DEFAULT NULL,
  `location` varchar(30) DEFAULT NULL,
  `type` varchar(30) DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `no` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

student表

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int NOT NULL,
  `Sname` varchar(30) DEFAULT NULL,
  `Sage` int DEFAULT NULL,
  `Ssex` varchar(1) DEFAULT NULL,
  `Stel` varchar(30) DEFAULT NULL,
  `Smajor` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Smajor` (`Smajor`),
  KEY `Sno` (`Sno`),
  CONSTRAINT `Smajor` FOREIGN KEY (`Smajor`) REFERENCES `major` (`Mname`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

major

CREATE TABLE `major` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Mno` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Mname` varchar(30) DEFAULT NULL,
  `Mdept` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Mname` (`Mname`),
  KEY `Mdept` (`Mdept`),
  CONSTRAINT `Mdept` FOREIGN KEY (`Mdept`) REFERENCES `department` (`Dname`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

department

CREATE TABLE `department` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Dno` varchar(30) NOT NULL,
  `Dname` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Dname` (`Dname`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

notice表

CREATE TABLE `notice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sender` varchar(30) DEFAULT NULL,
  `receiver` varchar(30) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;

book__appointment_relation

CREATE TABLE `book_appointment_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int DEFAULT NULL,
  `Bno` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_appointment_Sno` (`Sno`),
  KEY `student_appointment_Bno` (`Bno`),
  CONSTRAINT `student_appointment_Bno` FOREIGN KEY (`Bno`) REFERENCES `book` (`Bno`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `student_appointment_Sno` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

student_borrow_relation

CREATE TABLE `student_borrow_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `state` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_borrow_relation` (`Sno`),
  CONSTRAINT `student_borrow_relation` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

student_fine_relation

CREATE TABLE `student_fine_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int DEFAULT NULL,
  `money` double(5,1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_fine_Sno` (`Sno`),
  CONSTRAINT `student_fine_Sno` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

student_book_relation

CREATE TABLE `student_book_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Sno` int DEFAULT NULL,
  `Bno` varchar(30) DEFAULT NULL,
  `time` varchar(30) DEFAULT NULL,
  `renew` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_book_Sno` (`Sno`),
  KEY `student_book_Bno` (`Bno`),
  CONSTRAINT `student_book_Bno` FOREIGN KEY (`Bno`) REFERENCES `book` (`Bno`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `student_book_Sno` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=utf8;