影院数据库设计-草稿

--创建数据库
CREATE DATABASE cinema
--创建表格
--影片
CREATE TABLE Movie
(	
	Movie_ID INT PRIMARY KEY,
	Name VARCHAR(20),
	MainActor VARCHAR(100),
	Director VARCHAR(20),
	Description VARCHAR(100),	
	Duration DATETIME,			
	Categoary VARCHAR(100),		--影片类别 动作,冒险等
	Dimension INT				--影片的维度 2D,3D
);	
--影厅
CREATE TABLE Hall
(	
	Hall_ID INT PRIMARY KEY,
	Categoary VARCHAR(20),  --巨幕、激光、杜比、IMAX、极幕GIMO
	Price decimal NOT NULL,
	Seats INT
);	
--座位
CREATE　TABLE seat
(	
	Seat_ID INT PRIMARY KEY,
	Hall_ID INT REFERENCES Hall(Hall_ID),
	Seat_row INT,
	Seat_column INT,
	Seat_Isactive BIT
);	
--排片表
CREATE TABLE Schedule
(	
	Schedule_ID INT PRIMARY KEY,
	Movie_ID INT FOREIGN KEY REFERENCES Movie(movie_ID),
	Hall_ID INT FOREIGN KEY REFERENCES Hall(Hall_ID),
	Price decimal,
	BeginDateTime DATETIME,
);	
	
--订单表
CREATE TABLE orderHead
(	
	OrderHead_ID INT PRIMARY KEY,
	OrderHead_BuyDate DATETIME
);	
	
--订单明细
CREATE TABLE orderDetail
(	
	OrderDetail_ID INT PRIMARY KEY,
	OrderHead_ID INT FOREIGN KEY REFERENCES orderHead(orderHead_ID),
	Schedule_ID INT FOREIGN KEY REFERENCES Schedule(schedule_ID),
	Quantity INT NOT NULL DEFAULT(1),
);	
	
CREATE TABLE orderSeat
(	
	OrderDetail_ID INT FOREIGN KEY REFERENCES orderDetail(OrderDetail_ID),
	Seat_ID INT FOREIGN KEY REFERENCES seat(Seat_ID),
	PRIMARY KEY(OrderDetail_ID, Seat_ID)
);	
--会员等级
CREATE TABLE class
(	
	ClassID INT PRIMARY KEY,
	ClassName VARCHAR(20),
	Discount DECIMAL,
	IsActive BIT
);	
	
--用户
CREATE TABLE userInfo
(	
	Moblie VARCHAR(20) PRIMARY KEY,
	UserName VARCHAR(20),
	Password VARCHAR(100),
	ClassID INT FOREIGN KEY REFERENCES class(classID)
);