CREATE DATABASE 医到家健康服务系统							
	ON						
		(NAME='DataFile_1'					
		,FILENAME='C:\医到家健康服务系统\DataFile_1.mdf')					
	LOG ON						
		(NAME='LogFile_1'					
		,FILENAME='C:\医到家健康服务系统\LogFile_1.ldf');

USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Doctor') IS NOT NULL					
	DROP TABLE tb_Doctor;				
GO					
CREATE TABLE tb_Doctor				
	(D_ID 				
		Char(18) 			
		NOT NULL			
		CONSTRAINT pk_Doctor_D_ID			
			PRIMARY KEY(D_ID)				
	,D_No 				
		Char(6) 			
		NOT NULL
	,D_Name
		Varchar(10)
		NOT NULL
	,D_Sex
		Char(2)
		NOT NULL
	,D_Password
		Varchar(16)
		NOT NULL
	,DoctorNo
		Char(6)
		NOT NULL
	,WorkYear
		Int
		NOT NULL
	,D_Tel
		Char(11)
		NOT NULL);
		
USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Patient') IS NOT NULL					
	DROP TABLE tb_Patient;				
GO					
CREATE TABLE tb_Patient				
	(P_ID 				
		Char(18) 			
		NOT NULL			
		CONSTRAINT pk_Patient_P_ID			
			PRIMARY KEY(P_ID)				
	,P_No 				
		Char(6) 			
		NOT NULL
	,P_Name
		Varchar(10)
		NOT NULL
	,P_Birthdate
		Date
		NOT NULL
	,P_Sex
		Char(2)
		NOT NULL
	,P_Password
		Varchar(16)
		NOT NULL
	,Address
		Varchar(100)
		NOT NULL
	,P_Tel
		Char(11)
		NOT NULL
	,IfChronic
		Char(2)
		NOT NULL
	,IfTakePills
		Char(2)
		NOT NULL
	,PayType
		Char(4)
		NOT NULL);

USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Medicine') IS NOT NULL					
	DROP TABLE tb_Medicine;				
GO					
CREATE TABLE tb_Medicine			
	(M_No 				
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_Medicine_M_No			
			PRIMARY KEY(M_No)				
	,M_Name 				
		Char(50) 			
		NOT NULL
	,M_Price
		Varchar(50)
		NOT NULL
	,M_Type
		Varchar(20)
		NOT NULL
	,M_Number
		Int
		NOT NULL
	,Functions
		Varchar(200)
		NOT NULL
	,Dosage
		Varchar(200)
		NOT NULL
	,SideEffect
		Varchar(200)
		NOT NULL
	,Attention
		Varchar(200)
		NOT NULL);

USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Instrument') IS NOT NULL					
	DROP TABLE tb_Instrument;				
GO					
CREATE TABLE tb_Instrument			
	(Ins_No				
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_Instrument_Ins_No			
			PRIMARY KEY(Ins_No)				
	,Ins_Name				
		Varchar(20)			
		NOT NULL
	,Ins_Number
		Int
		NOT NULL
	,Ins_State
		Char(4)
		NOT NULL);
		
USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Ask') IS NOT NULL					
	DROP TABLE tb_Ask;				
GO					
CREATE TABLE tb_Ask
	(Ask_No
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_Ask_Ask_No			
			PRIMARY KEY(Ask_No)				
	,Asker				
		Varchar(10)			
		NOT NULL
	,Ask
		Varchar(500)
		NOT NULL
	,Answerer
		Varchar(10)
		NULL
	,Answer
		Varchar(500)
		NULL);

		
USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Reservation') IS NOT NULL					
	DROP TABLE tb_Reservation;				
GO					
CREATE TABLE tb_Reservation
	(Res_No
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_Reservation_Res_No			
			PRIMARY KEY(Res_No)				
	,ResPatientNo			
		Char(6)	
		NOT NULL
	,ResTime
		Date
		NOT NULL
	,ResAddress
		Varchar(100)
		NOT NULL
	,ResDoctorNo
		Char(6)
		NOT NULL
	,Main_Say
		Varchar(200)
		NOT NULL);

		
USE 医到家健康服务系统;					
IF OBJECT_ID('tb_Recipe') IS NOT NULL					
	DROP TABLE tb_Recipe;				
GO					
CREATE TABLE tb_Recipe
	(Recipe_No
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_Recipe_Recipe_No
			PRIMARY KEY(Recipe_No)				
	,Recipe_DNo
		Char(6)	
		NOT NULL
	,Recipe_PNo
		Char(6)
		NOT NULL
	,Temperature
		Varchar(10)
		NULL
	,BP
		Int
		NULL
	,Height
		Varchar(10)
		NULL
	,Weight
		Varchar(10)
		NULL
	,Recipe_Time
		Date
		NOT NULL
	,Recipe_MNo
		Char(10)
		NOT NULL
	,Judge
		Varchar(200)
		NOT NULL
	,Total
		Varchar(1000)
		NOT NULL);
		
USE 医到家健康服务系统;					
IF OBJECT_ID('tb_History') IS NOT NULL					
	DROP TABLE tb_History;				
GO					
CREATE TABLE tb_History
	(H_PNo
		Char(10)			
		NOT NULL			
		CONSTRAINT pk_History_H_PNo		
			PRIMARY KEY(H_PNo)				
	,H_PName		
		Varchar(10)
		NOT NULL
	,Ill_History
		Varchar(200)
		NOT NULL
	,H_Time
		Date
		NULL
	,H_DNo
		Char(10)
		NOT NULL
	,Allergic_History
		Varchar(200)
		NOT NULL);