数据库大作业-电影院管理系统

3.2.1 消费者信息表字段名字段类型字段大小备注customer_idint60primary keycustomer_nameVARCHAR30not nullcustomer_mobileVARCHAR30not nullmember_idint60当不是会员时可null 且外码3.2.2 会员信息表字段名字段类型字段大小备注member_idint60primary keymember_

Cabbage coder

8232人浏览 · 2021-05-18 17:31:40

Cabbage coder · 2021-05-18 17:31:40 发布

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;