clinique.sql

医疗管理系统

CREATE DATABASE Clinique
ON
(
name =Clinique_Data,
filename='D:\Clinique_Data.MDF',
size=1 MB,
maxsize=100 mb,
filegrowth=10 MB
)
LOG ON
(
name=Clinique_Log,
filename='D:\Clinique_Log.LDF',
size=1 MB,
filegrowth=10%
)
GO

--drop database Clinique

use Clinique

CREATE TABLE manager(
username varchar(20) PRIMARY KEY,
password varchar(50) not null,
name varchar(50) not null,
phone int  null,
email varchar(50) null,
)ON [PRIMARY]
GO 

CREATE TABLE doctor(
username varchar(20) PRIMARY KEY,
password varchar(50) not  null,
name varchar(50) not null,
profession varchar(20) not null,
phone int null,
email varchar(50) null,
)ON [PRIMARY]
GO 

CREATE TABLE patient(
ptn_id int IDENTITY(1,1) PRIMARY KEY ,
doc_id varchar(20) REFERENCES doctor(username) not null,
name varchar(50) not null,
sex char(1) CHECK(sex='F' OR sex='M') not null,
birth datetime CHECK(birth>'1900-01-01'  )not null,
blood varchar(2) CHECK (blood='A'OR blood='B' OR blood='C' OR blood='O' OR blood ='AB')null,
height numeric(5,1) null,
weight numeric(5,1) null
)ON [PRIMARY]
GO
 
--DROP TABLE doctor

CREATE TABLE leechdom(   ---药品
lch_id int IDENTITY(1,1) PRIMARY KEY,
name varchar(50) not null,
factory varchar(50) not null,
price numeric(5,2) not null
)ON [PRIMARY]
GO 

CREATE TABLE reckoning(----帐单
rck_id int IDENTITY(1,1) PRIMARY KEY,
doc_id varchar(20) FOREIGN KEY REFERENCES doctor(username),
ptn_id int FOREIGN KEY REFERENCES patient(ptn_id),
price money not null,
date datetime  null
)ON [PRIMARY]
GO