CREATE DATABASE Lab2
ON
PRIMARY
(
NAME = 'Lab2_data1',
FILENAME = 'D:\data\Lab2_data1.mdf',
SIZE = 20 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
),
(
NAME = 'Lab2_data2',
FILENAME = 'D:\data\Lab2_data2.ndf',
SIZE = 20 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
LOG ON
(
NAME = 'Lab2_log1',
FILENAME = 'D:\data\Lab2_log1.ldf',
SIZE = 50 MB,
MAXSIZE = 100 MB,
FILEGROWTH = 10 MB
);
USE Lab2
ALTER DATABASE lab2 REMOVE FILE Lab2_data2;
/*按要求创建四个关系S,P,J,SPJ*/
CREATE TABLE S/*供应商表*/
(
sno varchar(20) primary key,
sname varchar(20) unique,
city varchar(50)
);
CREATE TABLE P/*零件表*/
(
pno varchar(20) primary key,
pname varchar(20) ,
color varchar(10) ,
weight int check(weight>=1 and weight <=50),
);
CREATE TABLE J/*工程项目表*/
(
jno varchar(20) primary key,
jname varchar(20) not null unique,
city varchar(50) ,
);
CREATE TABLE SPJ/*供应情况表*/
(
sno varchar(20) references S(sno),
pno varchar(20) references P(pno),
jno varchar(20) references J(jno),
qty int
);