课程设计表

create database Drug
--药品表
create table Drug
(
Did Nvarchar(10) primary key,
Dname Nvarchar(50) not null,
Dimage Nvarchar(100) not null,
Dmodel Nvarchar(10) check  (Dmodel in('Internal','outside')) default 'Internal',
Defeect Nvarchar(100) not null,
Dprice float check (Dprice>=0) default '0',
Dqty int not null default '0',
Ddate datetime default getdate()
)
drop table  Client Drug
--顾客表
create table Client
(
ClientId Nvarchar(10) primary key,
Cposition Nvarchar(20) default 'Client',
Cname Nvarchar(10) not null,
Csex Nvarchar(2) check(Csex in('man','women')) default 'man',
Cimage Nvarchar(100) not null,
Cage int check (Cage>10 and Cage<150) default '20',
Caddress Nvarchar(50),
Ctel Nvarchar(11),
Ccymptom Nvarchar(100),
Cdate datetime default getdate()
)
--salsman table
create table Salsman
(
SId nvarchar(10) primary key,
Sposition nvarchar(20) default 'Salsman',
Sname nvarchar(10) not null,
Ssex Nvarchar(3) check ( Ssex in('man','woman')) default 'man',
Stel nvarchar(11),
Sage int check (Sage>=18 and Sage<=80) default '18',
Simage nvarchar(100) not null,
Sdate datetime default getdate()
)
---Medicine sales table
create table MS
(
SalesSlip    Nvarchar(20) primary key,
clientID    Nvarchar(10) foreign key references Client(ClientId) on update cascade,
DrugId    Nvarchar(10) foreign key references Drug(Did) on update cascade,
SalesmanID    Nvarchar(10) foreign key references Salsman(sID) on update cascade,
Sqty    Int not null,
Stotalprices float  not null,
Dimage    Nvarchar(100) not null,
Sdate    datetime default  Getdate()
)
--Purchaser table
create table Purchaser
(
PurchaserID Nvarchar(10)  primary key,
Pposition Nvarchar(20) default 'Purchaser',
Pname Nvarchar(10)  not null,
Psex Nvarchar(3) check(Psex in('man','woman')) default 'man',
Pimage Nvarchar(100) not null,
Page Int check (Page between 10 and 150 ) default '18',
Paddress Nvarchar(20),
Ptel Nvarchar(11),
Pdate datetime default Getdate()
)
drop table Purchaser

---Manufacturer
create table Manufacturer
(
Mname    Nvarchar(20) primary key,
Mtel    Nvarchar(11) not null,
Maddress    Nvarchar(50) not null
)    
--Purchase table
create table Purchase
(
orderformID    Nvarchar(20) primary key,
purchaserID    Nvarchar(10) foreign key references purchaser(PurchaserID) on update cascade,
manufacturerName    Nvarchar(20) foreign key references Manufacturer(MName)on update cascade,
drugId    Nvarchar(10) foreign key references drug(Did) on update cascade,
Pqty    int not null default '0',
Pprice    float not null default '0',
PtotalPrices float default '0',
Dimage    Nvarchar(100),
PDate datetime default    Getdate(),
)


drop table Purchase

---Login table
create table Login
(
Postion    Nvarchar(20) check ( Postion in('Manager','Purchaser','Salsman')) default 'Salsman',
Lname    nVarchar(20)  not null,
Lpasswd    Varchar(20)  not null
)