create database databasename
on
( name=wsb0077240_data,
filename='e:\database\databasename_data.mdf',
size=5,
maxsize=10,
filegrowth= 1 )
log on
( name=wsb0077240_log,
filename='e:\database\databasename_log.ldf',
size=3,
maxsize=5,
filegrowth=1)
Go
use databasename
create table employee(
emp_no char(5) Not null primary key,
emp_name char(10) Not null,
sex char(1) null,
constraint emp_sexchk check(sex in ('m','f')),
dept char(10) null,
title char(10) null,
date_hired datetime null,
birthday datetime Null,
salary int null,
addr char(50) null,
Mod_date datetime Default(getdate()),
constraint emp_bh check(emp_no like ('E[0-9][0-9][0-9][0-9]'))
)
create table customer(
cust_id char(5) Not null primary key,
cust_name char(20) null,
addr char(40) null,
tel_no char(13) null,
zip char(6) null
)
create table product(
prod_id char(5) Not null primary key,
prod_name char(20) Not null
)
create table sales(
order_no int Not null primary key,
cust_id char(5) null,
sale_id char(5) null,
tot_amt numeric(9,2) Not null,
order_date datetime null,
ship_date datetime null,
invoice_no char(10) null,
foreign Key (cust_id) References customer(cust_id),
foreign Key (sale_id) References employee(emp_no),
constraint sale_un unique (invoice_no)
)
create table sale_item(
order_no int Not null,
prod_id char(5) Not null,
qty int Not null,
unit_price numeric(7,2) Not null,
order_date datetime null,
constraint zhukey primary key(order_no,prod_id),
foreign Key (prod_id) References product(prod_id),
foreign Key (order_no) References sales(order_no)
)