【数据库设计及SQL代码实现】英皇电影院电影管理系统

一、项目背景

1.1公司介绍

英皇集团一直积极发展其电影业务，英皇电影集团包括三大电影品牌。
英皇电影城 自2015年成立以来，已在中国大陆多个城市落地。
在北京长安街的旗舰店是其在大陆的首映场。自2017年起，
香港的英皇电影院进驻了中环置地娱乐大厦、屯门美罗广场、马鞍山的阳光城中心、荃湾的荃新天地、尖沙咀的国际广场等。
进驻将军澳的香港城和铜锣湾的时代广场，标志着集团业务的另一个里程碑。2021年，英皇电影院进驻澳门，为当地电影观众提供优质的观影体验。

1.2 项目背景

本项目的主要目的是为英皇电影院设计一个电影院管理系统，以便英皇电影院的管理员可以在这个系统中，安排、取消、搜索或改变电影的档期，如电影名称、影厅、时间和座位，以及客户信息，如客户姓名、客户电话号码和客户的会员信息等。

二、Entity-Relationships description

三、数据库设计

3.1conceptual design

3.2logical design

3.3Show all functional dependencies in relations and demonstrate that all relations are in 3NF

3.4每个字段的数据类型

3.5 每个表的索引

四、数据库实现（SQL Server）

4.1创建表

注：数据为示例，非真实数据

CREATE TABLE customer(
customer_id varchar(8) Not NULL primary key,
customer_sex varchar(6) not null,
customer_name varchar(100) not null,
customer_frequency int not null,
customer_phone int,
customer_year int,
member_id varchar(8),
);
CREATE TABLE member(
member_id varchar(8) not null primary key,
member_name varchar(100) not null,
member_discount decimal(3,2) not null,
);
CREATE TABLE movie(
movie_id varchar(8) not null primary key,
movie_name varchar(100) not null,
movie_type varchar(100) not null,
movie_nation varchar(100) not null,
);
CREATE TABLE seat(
seat_id varchar(6) not null primary key,
seat_hall varchar(2) not null,
seat_row varchar(2) not null,
seat_column varchar(2) not null,
);
CREATE TABLE schedule(
schedule_id varchar(8) not null primary key,
movie_id varchar(8) not null foreign key references movie(movie_id),
schedule_hall varchar(2) not null,
price decimal(4,2) not null,
movie_time varchar(8) not null,
);
CREATE TABLE orders(
order_id varchar(8) not null primary key,
customer_id varchar(8) Not NULL foreign key references customer(customer_id),
schedule_id varchar(8) not null foreign key references schedule(schedule_id),
Price decimal(4,2) not null,
seat_id varchar(6) not null foreign key references seat(seat_id),
);
insert into customer values('10000001','male','ken', 4,43675849,29,'');
insert into customer values('10000002','female','angle',5,43645849,26,'20000002');
insert into customer values('10000003','male','bob',7,42775840,24,'20000003');
insert into customer values('10000004','female','marry', 6,32675844,29,'20000004');
insert into customer values('10000005','male','Lee',8,19675847,19,'20000005');
insert into customer values('10000006','female','cherry',7,50675845,22,'20000006');
insert into customer values('10000007','male','Andlex', 6,20305847,17,'20000007');
insert into customer values('10000008','male','Chris', 2,82965848,43,'');
insert into customer values('10000009','female','chail', 3,43492743,36,'');
insert into customer values('10000010','male','Bieber', 4,33673345,25,'');
insert into customer values('10000011','female','Rihana',2,80632842,45,'');
insert into customer values('10000012','female','orcal', 2,52675246,8,'');
insert into customer values('10000013','male','steven', 1,36673447,46,'');
insert into customer values('10000014','female','anna', 1,59664844,4,'');
insert into customer values('10000015','male','Michle', 1,79675499,10,'');
insert into member values('20000002','angle',0.75);
insert into member values('20000003','bob',0.50);
insert into member values('20000004','marry',0.75);
insert into member values('20000005','Lee',0.50);
insert into member values('20000006','cherry',0.50);
insert into member values('20000007','Andlex',0.75);
insert into movie values('77000001','Fast&Furious','action','US');
insert into movie values('77000002','The Bourne identity','action','US');
insert into movie values('77000003','Casino Royale','action','UK');
insert into movie values('77000004','Begotten','horror','US');
insert into movie values('77000005','The Grudge','horror','UK');
insert into movie values('77000006','August Rush','love','US');
insert into movie values('77000007','Green book','feature','UK');
insert into movie values('77000008','Iron Man','science','US');
insert into seat values('010101','01','01','01');
insert into seat values('011004','01','10','04');
insert into seat values('010808','01','08','08');
insert into seat values('020101','02','01','01');
insert into seat values('020612','02','06','12');
insert into seat values('020808','02','08','08');
insert into seat values('030408','03','04','08');
insert into seat values('030507','03','05','07');
insert into seat values('030808','03','08','08');
insert into seat values('030912','03','09','12');
insert into seat values('040903','04','09','03');
insert into seat values('040904','04','09','04');
insert into seat values('040808','04','08','08');
insert into schedule values('28000001','77000001','01',80 ,'12：00p.m');
insert into schedule values('28000002','77000002','02',40 ,'12：00p.m');
insert into schedule values('28000003','77000003','03',80 ,'12：00p.m');
insert into schedule values('28000004','77000004','04',40 ,'12：00p.m');
insert into schedule values('28000005','77000005','01',40 ,'03：00p.m');
insert into schedule values('28000006','77000006','02',40 ,'03：00p.m');
insert into schedule values('28000007','77000007','03',40 ,'03：00p.m');
insert into schedule values('28000008','77000008','04',80 ,'03：00p.m');
insert into schedule values('28000009','77000001','01',80 ,'09：00p.m');
insert into schedule values('28000010','77000005','02',40 ,'09：00p.m');
insert into schedule values('28000011','77000007','03',40 ,'09：00p.m');
insert into schedule values('28000012','77000008','04',80 ,'09：00p.m');
insert into orders values('55000001','10000001','28000001',80 ,'011004');
insert into orders values('55000002','10000002','28000002',30 ,'020101');
insert into orders values('55000003','10000003','28000003',40 ,'030408');
insert into orders values('55000004','10000003','28000008',40 ,'040904');
insert into orders values('55000005','10000005','28000005',20 ,'010101');
insert into orders values('55000006','10000005','28000002',20 ,'020612');
insert into orders values('55000007','10000006','28000003',40 ,'030808');
insert into orders values('55000008','10000006','28000008',40 ,'040808');
insert into orders values('55000009','10000007','28000003',70 ,'030507');
insert into orders values('55000010','10000007','28000006',30 ,'020808');
insert into orders values('55000011','10000007','28000011',30 ,'030808');
insert into orders values('55000012','10000004','28000012',60 ,'040808');
insert into orders values('55000013','10000008','28000001',80 ,'010101');
insert into orders values('55000014','10000009','28000002',40 ,'020808');
insert into orders values('55000015','10000010','28000003',80 ,'030912');
insert into orders values('55000016','10000015','28000004',40 ,'040903');
insert into orders values('55000017','10000002','28000005',30 ,'011004');
insert into orders values('55000018','10000005','28000010',20 ,'020101');
insert into orders values('55000019','10000003','28000011',20 ,'030408');
insert into orders values('55000020','10000006','28000012',40 ,'040903');

4.2表信息

4.2.1customer table

4.2.2member table

4.2.3movie table

4.2.4seat table

4.2.5schedule table

4.2.6orders table

4.3表查询

1.查询女性会员人数

SELECT COUNT(customer.member_id) AS NumberOfFemaleMembers FROM
member LEFT JOIN customer
ON member.member_id = customer.member_id
WHERE customer.customer_sex='female';

2.查询最年轻会员的id、姓名和年龄

SELECT TOP 1 customer.member_id, customer.customer_name,customer.customer_year AS
MinAge FROM
member LEFT JOIN customer
ON member.member_id = customer.member_id
ORDER BY customer.customer_year;

3. 计算电影销售的总收入

SELECT SUM(price) AS TotalSalesAmount FROM orders;

4. 计算电影票均价

SELECT CONVERT(decimal(10,2),AVG(price)) AS AvgMoviePrice FROM schedule;

5. 查询观看过电影77000001的顾客

SELECT orders.customer_id, schedule.movie_id FROM
orders LEFT JOIN schedule
ON orders.schedule_id = schedule.schedule_id
WHERE schedule.movie_id = 77000001;

6.统计观看每个类型电影的顾客人数

SELECT movie.movie_type, COUNT(orders.customer_id) AS NumOfCustomers FROM
(orders LEFT JOIN schedule ON orders.schedule_id = schedule.schedule_id)
LEFT JOIN movie ON schedule.movie_id = movie.movie_id
GROUP BY movie.movie_type
ORDER BY NumOfCustomers DESC;

7. 查询未观看电影的顾客，以便向他们推送营销信息

SELECT customer.customer_id, customer.customer_name,orders.order_id FROM customer
LEFT JOIN orders
ON customer.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

8.找出所有享受最低的50%折扣的会员的联系方式，以便于
定期向他们推送相应的会员活动

SELECT member.member_id, customer.customer_name, customer.customer_phone FROM
member LEFT JOIN customer
ON member.member_id = customer.member_id
WHERE member.member_discount = 0.50;

9.找到消费最高的5名顾客

SELECT TOP 5 customer.customer_id, SUM(orders.price) AS IndividualAmount FROM
orders LEFT JOIN customer
ON orders.customer_id = customer.customer_id
GROUP BY customer.customer_id
ORDER BY SUM(orders.price) DESC;

10. 电影77000002 的价格从40涨到50, 更新相应表格信息

UPDATE schedule
SET price = 50.00
WHERE movie_id=77000002;