在线电影票务数据库设计有问题

Question

@J2D8T我对我的代码做了很少的修改，请告诉我它是否正确，它是否被正确地规范化了？提前谢谢。

        use [fadi1]

    create table Customer (
    Cnic varchar (17) primary key not null,
    name varchar (30) not null,
    Cpassword varchar (30) not null,
    email varchar (40) ,
    contactno varchar (15) ,
    city varchar (30)
    );

    create table Adimn (
    Anic varchar (17) primary key not null,
    name varchar (30) not null,
    Cpassword varchar (30) not null, 
    email varchar (40) ,
    contactno varchar (15) ,
    city varchar (30)
    );

    create table Movie (
    M_id int primary key not null, 
    name varchar(25) not null,
    Mdescription varchar(500) not null,
    imagename varchar (100) not null,
    Actors varchar (100) not null,
    );

    create table Venue(
    V_id int primary key not null, 
    V_Name varchar(25) not null,
    Maxcapacity int not null,   
    );


    create table Shows (
    showid int primary key IDENTITY(1,1) not null, 
    V_id int not null, 
    M_id int not null ,  
    date_time datetime  not null unique,
    remaining_tickets int not null,
    foreign key (v_id) references Venue(v_id),
    foreign key (M_id) references Movie(M_id),

    );

    create table Tickets(
    showid int not null,
    Seat_no  int not null, -- (maxcap-remaining tickets)+1 from capacity table of given pk of that table 
    T_id int primary key IDENTITY(1,1) not null,  --(p.k)
    price int not null, 
    foreign key (showid) references Shows(showid) 
    );


    create table Booking (
    showid int not null,
    T_id int not null unique, 
    Cnic varchar(17)  not null, 
    booking_time datetime  not null,
    foreign key (showid) references Shows(showid),
    foreign key (T_id) references Tickets(T_id),
    foreign key (Cnic) references Customer(Cnic),
    constraint pk_IDBooking primary key(T_id,showid), 
    );

请尽快通知我，因为我离最后期限不远了。

Answer 1

SQL Server中至少没有password数据类型。由于您没有提供有关如何存储数据的详细信息，因此我将其转换为varchar(50)。

SQLFiddle

create table Customer (
Cnic varchar (17) primary key not null,
name varchar (30) not null,
Cpassword varchar(50) not null,
email varchar (40) ,
contactno varchar (15) ,
city varchar (30) ,
)

create table Adimn (
Anic varchar (17) primary key not null,
name varchar (30) not null,
Cpassword varchar(50) not null,
email varchar (40) ,
contactno varchar (15) ,
city varchar (30) ,
)

create table Movie (
M_id int not null, 
name varchar(25) not null,
Mdescription varchar(500) not null,
imagename varchar (100) not null,
primary key (M_id)
);


create table Actors(
actor_id varchar(25) primary key not null ,
actor varchar(30)not null,
M_id int not null,
Act_role varchar (10)not null,
amountspent int ,
foreign key (M_id) REFERENCES Movie(M_id)
);

create table Venue(
V_id int primary key not null, 
M_id int not null,--F.k 
V_Name varchar(25)not null

);

create table Capacity(
M_id int not null, 
v_id int not null,
date_time varchar (15)  not null,
remaining_tickets int not null,
max_capacity int   not null, 
primary key (M_id, v_id ,date_time )

);


create table Booking (
v_id int not null, --key
T_id int not null,
M_id int not null,--key 
Cnic varchar(30) primary key not null,--(p.k))
date_time varchar (15) not null, -- (f.k ) 
);


create table Tickets(
Seat_no int primary key not null, -- (p.k))
T_id int not null,
M_id int not null, --(f.k)
price int not null,

);

Answer 2

我编辑了答案，只包含一些注释的代码，希望这能解决你的问题。

从您的原始帖子编辑：

create table Customer (
Cnic varchar (17) primary key not null,
name varchar (30) not null,
Cpassword password not null,
email varchar (40) ,
contactno varchar (15) ,
city varchar (30)
);

create table Adimn (
Anic varchar (17) primary key not null,
name varchar (30) not null,
Cpassword varchar (30) not null, // changed to varchar
email varchar (40) ,
contactno varchar (15) ,
city varchar (30)
);

create table Movie (
M_id int primary key not null, 
name varchar(25) not null,
Mdescription varchar(500) not null,
imagename varchar (100) not null
);

create table Actors(
actor_id varchar(25) primary key not null ,
actor varchar(30)not null,
M_id int not null,
Act_role varchar (10)not null,
amountspent int ,
foreign key (M_id) REFERENCES Movie(M_id)
);

create table Venue(
V_id int primary key not null, 
M_id int not null,--F.k 
V_Name varchar(25) not null,
foreign key (M_id) references Movie(M_id)
);

create table Capacity(
v_id int not null, // FK
M_id int not null unique, //FK 
date_time varchar (15)  not null unique,
remaining_tickets int not null,
max_capacity int   not null,
foreign key (v_id) references Venue(v_id),
foreign key (M_id) references Movie(M_id),
constraint pk_IDCapacity PRIMARY KEY (M_id,v_id,date_time) // this will create composite key
);

create table Tickets(
Seat_no int primary key not null, -- (p.k))
T_id int not null,
M_id int not null, --(f.k)
price int not null,
foreign key (M_id) references Capacity(M_id) 
);

create table Booking (
v_id int not null, //FK
T_id int not null, //FK
M_id int not null,//FK
Cnic varchar(30) primary key not null // Will this stil be neccessary
date_time varchar (15) not null, //FK
foreign key (v_id) references Venue(v_id),
foreign key (M_id) references Movie(M_id),
foreign key (date_time) references Capacity(date_time),
foreign key (T_id) references Tickets(T_id),
constraint pk_IDBooking primary key(v_id,M_id,T_id,date_time) //Can be in any order the PK's
);

可以通过规范化更紧凑，但它应该基于您提供的代码工作。