sql.txt

程序为PB+SQL 使用需有SQL安装数据库文件

create table shop_user(
	username     varchar(10) not null,
	password     varchar(10) not null,
	flag         int,
	constraint pk_shop_user primary key (username)
);

insert into  shop_user values('dirk','dirk',8);
insert into  shop_user values('guest','guest',1);
insert into  shop_user values('new','old',1);


create table table_dept(
	dept_id           int not null,
	dept_name         varchar(20) not null,
	dept_descreption  varchar(200),   
	constraint pk_table_dept primary key (dept_id)
);

create table table_income(
	income_id         int not null,
	dept_id           int not null,
	daily_income      float(5) not null,
	business_datetime     datetime,
	lst_mod_timestemp datetime,
	constraint pk_table_income primary key (income_id),
        constraint fk_table_income foreign key (dept_id)
        references table_dept (dept_id)
);

create table table_vendor(
	vendor_id       int not null,
	vendor_name     varchar(50) not null,
	vendor_address  varchar(200) not null,
	vendor_phone    int not null,
	vendor_fax      int not null,
	vendor_contact_person  varchar(10) not null,
	constraint pk_table_vendor primary key (vendor_id)
);

create table table_bill(
	bill_id           int not null,
	vendor_id         int not null,
	bill_datetime         datetime,
	bill_due_datetime     datetime,
	bill_paid_flag    int,
	bill_amount       float(5),
	constraint pk_table_bill primary key (bill_id),
        constraint fk_table_bill foreign key (vendor_id)
        references table_vendor (vendor_id),
        constraint ck_table_bill CHECK (bill_datetime<bill_due_datetime)
);



create table table_bill_item(
	bill_id		int not null,
	dept_id         int not null,
	bill_item_id    int not null,
	bill_item_expense float(5) not null,
	constraint pk_table_bill_item primary key (bill_item_id),
        constraint fk_table_bill_item foreign key (bill_id)
        references table_bill (bill_id),
        constraint fk_table_bill_item_1 foreign key (dept_id)
        references table_dept (dept_id)
);