创建表.sql

这是一个网上订票系统

delete from payment;
drop table payment;

delete from speakfor;
drop table speakfor;

delete from planetype;
drop table planetype;

delete from bill;
drop table bill;

delete from ticket;
drop table ticket;

delete from passenger;
drop table passenger;

delete from airline;
drop table airline;

delete from aircompany;
drop table aircompany;

delete from refundment;
drop table refundment;

create table aircompany
(
 companyname varchar(20) not null,
 url varchar(50) not null, 
 primary key(companyname)
);


create table airline
(
 airno varchar(6) not null,
 companyname varchar(20) not null,
 startcity varchar(10) not null,
 arrivecity varchar(10) not null,
 starttime varchar(5) not null,
 arrivetime varchar(5) not null,
 airdate varchar(8) not null,
 primary key(airno,airdate),
 foreign key(companyname) references aircompany(companyname)
);


create table passenger
(
  id varchar(18) not null,
  pass_name varchar(20) not null,
  sex varchar(2) not null,
  addr varchar(40) not null,
  postalcode varchar(6) not null,
  airno varchar(6) not null,
  primary key(id)
);



create table ticket
(
 ticketno varchar(8) not null,
 airno varchar(6) not null,
 startdate varchar(8) not null,
 starttime varchar(5) not null,
 tickettype varchar(2) not null,
 pass_name varchar(20) not null,
 id varchar(18) not null,
 price number not null,
 companyname varchar(20) not null,
 primary key (ticketno),
 foreign key(id) references passenger(id),
 foreign key(companyname) references aircompany(companyname)
);

create table bill
(
 billno varchar(6) not null,
 airno varchar(6) not null,
 pass_name varchar(20) not null,
 numbers number not null,
 price number not null,
 amount number not null,
 arrivecity varchar(10) not null,
 companyname varchar(20) not null,
 primary key(billno),
 foreign key(companyname) references aircompany(companyname)
);


create table planetype
(
 type varchar(10) not null,
 seat_num number not null,
 airno varchar(6) not null,
 primary key (type,airno)
);

create table speakfor
(
 ticketno varchar(8) not null,
 id varchar(18) not null,
 primary key(ticketno),
 foreign key(ticketno) references ticket(ticketno),
 foreign key(id) references passenger(id)
);


create table payment
(
 id varchar(18) not null,
 billno varchar(6) not null,
 primary key(id),
 foreign key(id) references passenger(id),
 foreign key(billno) references bill(billno)
);

create table refundment 
(
 id varchar(18) not null,
 ticketno varchar(8) not null,
 pass_name varchar(20) not null, 
 money varchar(4) not null,
 addr varchar(40),
 postalcode varchar(6),
 primary key(id,ticketno)
);