3.2.1 消费者信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
customer_id | int | 60 | primary key |
customer_name | VARCHAR | 30 | not null |
customer_mobile | VARCHAR | 30 | not null |
member_id | int | 60 | 当不是会员时 可null 且外码 |
3.2.2 会员信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
member_id | int | 60 | primary key |
member_name | VARCHAR | 30 | not null |
member_discount | decimal | (3,1) | not null |
3.2.3 电影信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
movie_id | int | 60 | primary key |
movie_name | VARCHAR | 70 | not null |
movie_director | VARCHAR | 70 | not null |
movie_actor | VARCHAR | 70 | not null |
movie_description | VARCHAR | 70 | not null |
3.2.4 排片信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
schedule_id | int | 60 | primary key |
movie_id | int | 60 | not null |
Hall_id | int | 60 | not null |
Price | decimal | (5,2) | not null |
movie_time | time | 50 | not null |
3.2.5 影厅信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
Hall_id | int | 60 | primary key |
Hall_scale | VARCHAR | 60 | not null |
Hall_description | VARCHAR | 60 | not null |
is_moving | int | 10 | 1为正在放映 0为没有 |
3.2.6 座位信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
seat_id | int | 60 | primary key |
Hall_id | int | 60 | not null |
seat_row | int | 30 | not null 座位所在行 |
seat_colmun | int | 30 | not null 座位所在列 |
seat_isactive | int | 10 | 1 为此座位有人 0代表空 |
3.2.7 订单信息表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
order_id | int | 60 | primary key |
customer_id | int | 60 | not null |
schedule_id | int | 60 | not null |
Price | decimal | (5,2) | not null |
buy_time | time | 50 | not null |
seat_id | int | 60 | not null 外键 |
3.2.8 订单座位表
字段名 | 字段类型 | 字段大小 | 备注 |
---|---|---|---|
order_id | int | 60 | primary key |
seat_id | int | 60 | primary key |
Hall_id | int | 60 | not null |
seat_row | int | 30 | not null 座位所在行 |
seat_colmun | int | 30 | not null 座位所在列 |
1.
CREATE TABLE `manage_movie`.`customer` (
`cutomer_id` int(60) NOT NULL,
`customer_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`customer_moblie` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`member_id` int(60) NULL DEFAULT NULL,
PRIMARY KEY (`cutomer_id`) USING BTREE,
INDEX `会员`(`member_id`) USING BTREE,
CONSTRAINT `会员` FOREIGN KEY (`member_id`) REFERENCES `manage_movie`.`customer_mb` (`member_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
2.CREATE TABLE `manage_movie`.`customer_mb` (
`member_id` int(60) NOT NULL,
`member_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`member_discount` decimal(30, 0) UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY (`member_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
3.
CREATE TABLE `manage_movie`.`movie` (
`movie_id` int(60) NOT NULL,
`movie_name` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`movie_director` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`movie_actor` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`movie_description` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`movie_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
4.
CREATE TABLE `manage_movie`.`Hall` (
`Hall_id` int(60) NOT NULL,
`Hall_scale` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Hall_description` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`is_moving` int(10) NOT NULL,
PRIMARY KEY (`Hall_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
5.
CREATE TABLE `manage_movie`.`seat` (
`seat_id` int(60) NOT NULL,
`Hall_id` int(60) NOT NULL,
`seat_row` int(30) NOT NULL,
`seat_colmun` int(30) NOT NULL,
`seat_isactive` int(10) NOT NULL,
PRIMARY KEY (`seat_id`) USING BTREE,
INDEX `s_h`(`Hall_id`) USING BTREE,
CONSTRAINT `s_h` FOREIGN KEY (`Hall_id`) REFERENCES `manage_movie`.`hall` (`Hall_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
6.
CREATE TABLE `manage_movie`.`schedule` (
`schedule_id` int(60) NOT NULL,
`movie_id` int(60) NOT NULL,
`Hall_id` int(60) NOT NULL,
`price` decimal(5, 2) NOT NULL,
`movie_time` datetime NOT NULL,
PRIMARY KEY (`schedule_id`) USING BTREE,
INDEX `movie`(`movie_id`) USING BTREE,
INDEX `hall`(`Hall_id`) USING BTREE,
CONSTRAINT `movie` FOREIGN KEY (`movie_id`) REFERENCES `manage_movie`.`movie` (`movie_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `hall` FOREIGN KEY (`Hall_id`) REFERENCES `manage_movie`.`hall` (`Hall_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
7.
CREATE TABLE `manage_movie`.`order` (
`order_id` int(60) NOT NULL,
`customer_id` int(60) NOT NULL,
`schedule_id` int(60) NOT NULL,
`price` decimal(5, 2) NOT NULL,
`buy_time` datetime NOT NULL,
`seat_id` int(60) NOT NULL,
PRIMARY KEY (`order_id`) USING BTREE,
INDEX `customer`(`customer_id`) USING BTREE,
INDEX `schedule`(`schedule_id`) USING BTREE,
INDEX `seat`(`seat_id`) USING BTREE,
CONSTRAINT `customer` FOREIGN KEY (`customer_id`) REFERENCES `manage_movie`.`customer` (`cutomer_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `schedule` FOREIGN KEY (`schedule_id`) REFERENCES `manage_movie`.`schedule` (`schedule_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `seat` FOREIGN KEY (`seat_id`) REFERENCES `manage_movie`.`seat` (`seat_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
8.
CREATE TABLE `manage_movie`.`order_seat` (
`order_id` int(60) NOT NULL,
`seat_id` int(60) NOT NULL,
`Hall_id` int(60) NOT NULL,
`seat_row` int(30) NOT NULL,
`seat_colmun` int(30) NOT NULL,
PRIMARY KEY (`order_id`, `seat_id`) USING BTREE,
INDEX `hall2`(`Hall_id`) USING BTREE,
CONSTRAINT `hall2` FOREIGN KEY (`Hall_id`) REFERENCES `manage_movie`.`hall` (`Hall_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
插入数据
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (1, '高翔', '13708984682', 3275);
INSERT INTO `customer` VALUES (2, '刘洳榕', '19853508031', 3330);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of customer_mb
-- ----------------------------
INSERT INTO `customer_mb` VALUES (3275, '高翔', 0.90);
INSERT INTO `customer_mb` VALUES (3330, '刘洳榕', 0.80);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of movie
-- ----------------------------
INSERT INTO `movie` VALUES (1, '情书', '张艺谋', '村上春树', '经典爱情剧');
INSERT INTO `movie` VALUES (2, '速度与激情10', '冯小刚', '强森', '刺激的动作片');
INSERT INTO `movie` VALUES (3, '羞羞的铁拳', '宋阳', '沈腾', '搞笑片');
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of hall
-- ----------------------------
INSERT INTO `hall` VALUES (1, '3号厅', '中等规模', 0);
INSERT INTO `hall` VALUES (2, '1号厅', '激光杜比厅', 1);
INSERT INTO `hall` VALUES (3, '5号厅', 'vip厅', 1);
INSERT INTO `hall` VALUES (4, '7号厅', '小厅', 0);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of seat
-- ----------------------------
INSERT INTO `seat` VALUES (1, 3, 7, 5, 1);
INSERT INTO `seat` VALUES (2, 2, 7, 6, 1);
INSERT INTO `seat` VALUES (3, 2, 7, 3, 0);
INSERT INTO `seat` VALUES (4, 1, 3, 2, 1);
INSERT INTO `seat` VALUES (5, 4, 4, 3, 0);
INSERT INTO `seat` VALUES (6, 3, 4, 5, 1);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of schedule
-- ----------------------------
INSERT INTO `schedule` VALUES (1, 2, 3, 50.00, '2021-06-18 16:00:00');
INSERT INTO `schedule` VALUES (2, 3, 2, 45.00, '2021-06-18 21:00:00');
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 2, 1, '2021-06-17 20:03:51', 3);
INSERT INTO `order` VALUES (2, 1, 2, '2021-06-08 20:06:06', 4);
SET FOREIGN_KEY_CHECKS = 1;
数据库安全性:
GRANT ALL PRIVILEGES
ON TABLE Customer
TO U1;
GRANT UPDATE(member_id), SELECT
ON TABLE Customer_mb
TO U1;
REVOKE SELECT
ON TABLE Customer_mb
FROM PUBLIC;
更多推荐
数据库大作业-电影院管理系统
发布评论