数据库系统概论 第五版(王珊) 数据库课程实验

数据库系统概论 第五版(王珊) 数据库课程实验

定义基本表

8个基本表建表sql如下：

CREATE TABLE Region(
	/*地区表*/
	regionkey INTEGER PRIMARY KEY ,
	name CHAR(25),
	COMMENT VARCHAR(152)
);

CREATE TABLE Nation (
	/*国家表*/
	nationkey integer PRIMARY KEY ,
	name char(25),
	regionkey integer REFERENCES Region(regionkey),
	COMMENT varchar(152)
);

CREATE TABLE supplier(
	/*供应商基本表*/
	suppkey integer PRIMARY KEY ,
	name char(25),
	address varchar(40),
	nationkey integer REFERENCES nation(nationkey),
	phone char(15),
	acctbal REAL ,
	COMMENT varchar(101)
);

CREATE TABLE part(
	/*零件基本表*/
	partkey integer PRIMARY KEY ,
	name varchar(55),
	mfgr char(250),
	/* 书中为char(10) 字段长度不够，会导致导入报错，因此改为cahr(100) */
	brand char(100),
	"type" varchar(25),
	"size" integer ,
	container char(10),
	retailprice REAL ,
	COMMENT varchar(23)
);

CREATE TABLE partsupp(
	/*零件供应联系表*/
	partkey integer REFERENCES part(partkey),
	suppkey integer REFERENCES supplier(suppkey),
	availqty integer ,
	supplycost REAL,
	COMMENT varchar(199),
	PRIMARY KEY(
		partkey,
		suppkey
	)
)

CREATE TABLE customer(
	/*顾客表*/
	custkey integer PRIMARY KEY ,
	name varchar(25),
	address varchar(40),
	"nationkey" integer REFERENCES nation(nationkey),
	phone char(15),
	acctbal REAL,
	mktsegment char(10),
	COMMENT varchar(117)
);

CREATE TABLE orders(
	/*订单表*/
	orderkey integer PRIMARY KEY ,
	custkey integer REFERENCES customer(custkey),
	orderdate date,
	orderstatus char(1),
	totalprice REAL,
	orderpriority char(15),
	clerk char(15),
	shippriority integer ,
	COMMENT varchar(79)
);

CREATE TABLE lineitem(
	/*订单明细表*/
	orderkey integer REFERENCES orders(orderkey),
	partkey integer REFERENCES part(partkey),
	suppkey integer REFERENCES supplier(suppkey),
	linenumber integer ,
	quantity REAL,
	extendedprice REAL,
	discount REAL,
	tax REAL,
	returnflag char(1),
	linestatus char(1),
	shipdate date,
	commitdate date,
	receipdate date,
	shipinstruct char(25),
	shipmode char(10),
	COMMENT varchar(44),
	PRIMARY KEY (
		orderkey,
		linenumber
	),
	FOREIGN KEY (
		partkey,
		suppkey
	) REFERENCES partsupp(
		partkey,
		suppkey
	)
);