sql_tablecode.txt

连锁影院售票系统

create table UserLogin(UserID varchar(6) primary key,--用户登陆信息表
                       UserName varchar(20) not null,
                       Password varchar(20) not null,
                       UserRights int not null,
                       TheaterID varchar(4) not null,
                       Online int default 0,
                       constraint forOnline check(Online in(0,1)),
                       constraint forUserRights check(UserRights in(1,2,3,4)));
                       

create table TheaterInf(TheaterID varchar(4) primary key,--影院信息表
                        TheaterName varchar(20) not null,
                        AdminID varchar(6) not null,
                        LocationCity varchar(20) not null,
                        Address varchar(50) not null,
                        TelNo varchar(15) not null,
                        Longitude varchar(20) null,
                        Latitude varchar(20) null);

create table CinemaInf(CinemaID varchar(4) primary key,--放映室信息表
                       CinemaType varchar(6) default 'Common' not null,
                       TheaterID varchar(4) not null,
                       SingleSeat int null,
                       Boxes int null,
                       constraint forCinemaType check(CinemaType in('Common','Luxury')));
 
create table LoginHistory(RecordID int primary key,--登陆历史记录信息
                          UserID varchar(6) not null,
                          LoginTime varchar(20) not null,
                          LogoutTime varchar(20) not null,
                          SingleTickets int null,
                          BoxTickets int null,
                          BackTickets int null,
                          Total money null);
 
create table LoginRecord(UserID varchar(6) primary key,--登陆信息
                         LoginTime varchar(20) not null,
                         LogoutTime varchar(20) not null,
                         SingleTickets int null,
                         BoxTickets int null,
                         BackTickets int null,
                         Total money null);

create table FilmInf(FilmID varchar(8) primary key,--影片信息库
                     FilmName varchar(50) not null,
                     FilmType varchar(6) not null,
                     Director varchar(50) not null,
                     LeadActors varchar(50) not null,
                     PlayWright varchar(50) not null,
                     BriefStory varchar(1000) not null,
                     PlaybillAdd varchar(50) not null);

create table OnShowPlan(OnShowIndex int primary key,--上映安排表
                        OnShowDate varchar(20) not null,
                        FilmID varchar(8) not null,
                        FilmName varchar(50) not null,
                        CinemaID varchar(4) not null,
                        OnShowNOP int not null,
                        SaleAble int default 1 not null,
                        ChangeAble int default 1 not null,
                        OnShowBand int default 1not null,
                        constraint forSaleAble check(SaleAble in(1,0)),
                        constraint forChangeAble check(ChangeAble in(1,0)),
                        constraint forOnShowBand check(OnShowBand in(1,0)));

create table HistoryRecords(RecordID int primary key,--票房历史统计
                            FilmID varchar(8) not null,
                            CinemaID varchar(4) not null,
                            TheaterID Varchar(4) not null,
                            OnShowDate varchar(20) not null,
                            OnShowNOP int not null,
                            SingleTickets int null,
                            BoxTickets int null,
                            BoxOfficeIntake money);

create table BackTicketInf(OnShowIndex int not null,--退票记录
                           OnShowDate varchar(20) not null,
                           OnShowNOP int not null,
                           FilmID varchar(8) not null,
                           CinemaID varchar(4) not null,
                           SeatNo int null,
                           BoxNo int null);
                  
create table SaleTodayInf(OnShowIndex int not null,--当日上映影片售票记录
                          TheaterID varchar(4) not null,
                          CinemaID varchar(4) not null,
                          FilmID varchar(8) not null,
                          OnShowNOP int not null,
                          SingleTickets int not null,
                          BoxTickets int not null); 

create table SaleTomorrowInf(OnShowIndex int not null,--明日上映影片预售票记录
                             TheaterID varchar(4) not null,
                             CinemaID varchar(4) not null,
                             FilmID varchar(8) not null,
                             OnShowNOP int not null,
                             SingleTickets int not null,
                             BoxTickets int not null); 

create table SaleThirddayInf(OnShowIndex int not null,--后日上映影片预售票记录
                             TheaterID varchar(4) not null,
                             CinemaID varchar(4) not null,
                             FilmID varchar(8) not null,
                             OnShowNOP int not null,
                             SingleTickets int not null,
                             BoxTickets int not null,
                             Intake money not null); 

--创建外键约束  
Alter table UserLogin add
 constraint FK_UserLogin_TheaterInf foreign key
  (TheaterID) 
 references TheaterInf
  (TheaterID)
go 

Alter table TheaterInf add
 constraint FK_TheaterInf_UserLogin foreign key
  (AdminID)
 references UserLogin
  (UserID) 
go

Alter table CinemaInf add 
 constraint FK_CinemaInf_TheaterInf foreign key
  (TheaterID) 
 references TheaterInf
  (TheaterID)
go

Alter table LoginHistory add 
 constraint FK_LoginHistory_UserLogin foreign key
  (UserID)
 references UserLogin
  (UserID)                                                            
go

Alter table OnShowPlan add 
 constraint FK_OnShowPlan_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmrID)
go

Alter table OnShowPlan add
 constraint FK_OnShowPlan_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go

Alter table HistoryRecords add
 constraint FK_HistoryRecords_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmID)
go

Alter table HistoryRecords add
 constraint FK_HistoryRecords_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go

Alter table HistoryRecords add
 constraint FK_HistoryRecords_TheaterInf foreign key
  (TheaterID)
 references TheaterInf
  (TheaterID)
go

Alter table BackTicketInf add
 constraint FK_BackTicketInf_OnShowplan foreign key
  (OnShowIndex)
 references onShowplan
  (OnShowIndex)
go

Alter table BackTicketInf add
 constraint FK_BackTicketInf_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmID)
go

Alter table BackTicketInf add
 constraint FK_BackTicketInf_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go

Alter table SaleTodayInf add
 constraint FK_SaleTodayInf_OnShowplan foreign key
  (OnShowIndex)
 references onShowplan
  (OnShowIndex)
go

Alter table SaleTodayInf add
 constraint FK_SaleTodayInf_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmID)
go

Alter table SaleTodayInf add
 constraint FK_SaleTodayInf_TheaterInf foreign key
  (TheaterID)
 references TheaterInf
  (TheaterID)
go

Alter table SaleTodayInf add
 constraint FK_SaleTodayInf_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go

Alter table SaleTomorrowInf add
 constraint FK_SaleTomorrowInf_OnShowplan foreign key
  (OnShowIndex)
 references onShowplan
  (OnShowIndex)
go

Alter table SaleTomorrowInf add
 constraint FK_SaleTomorrowInf_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmID)
go

Alter table SaleTomorrowInf add
 constraint FK_SaleTomorrowInf_TheaterInf foreign key
  (TheaterID)
 references TheaterInf
  (TheaterID)
go

Alter table SaleTomorrowInf add
 constraint FK_SaleTomorrowInf_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go

Alter table SaleThirddayInf add
 constraint FK_SaleThirddayInf_OnShowplan foreign key
  (OnShowIndex)
 references onShowplan
  (OnShowIndex)
go

Alter table SaleThirddayInf add
 constraint FK_SaleThirddayInf_FilmInf foreign key
  (FilmID)
 references FilmInf
  (FilmID)
go

Alter table SaleThirddayInf add
 constraint FK_SaleThirddayInf_TheaterInf foreign key
  (TheaterID)
 references TheaterInf
  (TheaterID)
go

Alter table SaleThirddayInf add
 constraint FK_SaleThirddayInf_CinemaInf foreign key
  (CinemaID)
 references CinemaInf
  (CinemaID)
go