数据库脚本.txt

网上书店系统主要是实现网上选书、购书、产生订单等功能的系统。一个典型的网上商城一般都需要实现商品信息的动态提示、购物车管理、客户信息注册登录管理、订单处理等模块。

DROP TABLE bookcat CASCADE CONSTRAINTS;
CREATE TABLE bookcat(	
	id		NUMBER      	PRIMARY KEY,
	catname		VARCHAR2 (40) 	NOT NULL
);

//图书基本信息表
DROP TABLE book CASCADE CONSTRAINTS;
CREATE TABLE book(	
	id		NUMBER      	PRIMARY KEY,
	catid		NUMBER      	NOT NULL
			REFERENCES bookcat(id) ON DELETE CASCADE,
	name		VARCHAR2 (50) 	NOT NULL,
	price		NUMBER       	NOT NULL,
	saleprice	NUMBER        	NOT NULL,
	descript	VARCHAR2 (500)  NOT NULL,
	author		VARCHAR2 (50)   NOT NULL,
	contents	VARCHAR2 (2000) NULL,
	image		VARCHAR2 (50)  	NULL
);

//客户基本信息表
DROP TABLE storeuser CASCADE CONSTRAINTS;
CREATE TABLE storeuser(	
	username	VARCHAR2 (20) 	PRIMARY KEY,
	password	VARCHAR2 (20) 	NOT NULL,
	name		VARCHAR2 (20) 	NOT NULL,
	tel		VARCHAR2 (20)  	NOT NULL,
	address		VARCHAR2 (100)  NOT NULL,
	email		VARCHAR2 (50)   NULL
);

//订单信息表
DROP TABLE orders CASCADE CONSTRAINTS;
CREATE TABLE orders(	
	id		NUMBER      	PRIMARY KEY,
	username	VARCHAR2 (20)   NOT NULL
			REFERENCES storeuser(username) ON DELETE CASCADE,
	time		DATE 	    	NOT NULL,
	delivery	VARCHAR2 (20)   NOT NULL,
	payment		VARCHAR2 (20)   NOT NULL 
);

//订单条目详细信息表
DROP TABLE orderdetail CASCADE CONSTRAINTS;
CREATE TABLE orderdetail(	
	orderid		NUMBER      NOT NULL
			REFERENCES orders(id) ON DELETE CASCADE,
	bookid		NUMBER      NOT NULL
			REFERENCES book(id) ON DELETE CASCADE,
	count		NUMBER      NOT NULL,
	price		NUMBER      NOT NULL
);