his.sql

Delphi6开发的HIS程序

--
--ER/Studio 6.0 SQL Code Generation
-- Company :      x
-- Project :      his.dm1
-- Author :       ralf
--
-- Date Created : Monday, September 15, 2003 19:23:00
-- Target DBMS : Oracle 8.x
--


-- 
-- TABLE: Approach 
--

CREATE TABLE Approach(
    ApproachID         NUMBER(38, 0)    NOT NULL,
    FrequencyDay       NUMBER(38, 0)    NOT NULL,
    FrequencyWeek      NUMBER(38, 0),
    PinYin             VARCHAR2(32)     NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    FrequencyWeekID    NUMBER(38, 0)    NOT NULL,
    ChargeItemCode     VARCHAR2(8)      NOT NULL,
    CONSTRAINT PKApproach PRIMARY KEY (ApproachID)
) 
;



-- 
-- TABLE: Area 
--

CREATE TABLE Area(
    AreaID      NUMBER(38, 0)    NOT NULL,
    ParentID    NUMBER(38, 0),
    Caption     VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKArea PRIMARY KEY (AreaID)
) 
;



-- 
-- TABLE: Bed 
--

CREATE TABLE Bed(
    BedID           NUMBER(38, 0)    NOT NULL,
    Caption         VARCHAR2(32)     NOT NULL,
    SickroomID      NUMBER(38, 0)    NOT NULL,
    BedCatalogID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKBed PRIMARY KEY (BedID)
) 
;



-- 
-- TABLE: BedCatalog 
--

CREATE TABLE BedCatalog(
    BedCatalogID    NUMBER(38, 0)    NOT NULL,
    Caption         VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKBedCatalog PRIMARY KEY (BedCatalogID)
) 
;



-- 
-- TABLE: BedChargeItem 
--

CREATE TABLE BedChargeItem(
    ChargeItemCode    VARCHAR2(8)      NOT NULL,
    BedCatalogID      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKBedChargeItem PRIMARY KEY (ChargeItemCode, BedCatalogID)
) 
;



-- 
-- TABLE: BloodType 
--

CREATE TABLE BloodType(
    BloodTypeID    NUMBER(38, 0)    NOT NULL,
    Caption        VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKBloodType PRIMARY KEY (BloodTypeID)
) 
;



-- 
-- TABLE: ChargeCatalog 
--

CREATE TABLE ChargeCatalog(
    ChargeCatalogCode    VARCHAR2(8)     NOT NULL,
    ParentCode           VARCHAR2(8),
    Caption              VARCHAR2(32)    NOT NULL,
    CONSTRAINT PKChargeCatalog PRIMARY KEY (ChargeCatalogCode)
) 
;



-- 
-- TABLE: ChargeItem 
--

CREATE TABLE ChargeItem(
    ChargeItemCode       VARCHAR2(8)      NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    ChargeCatalogCode    VARCHAR2(8)      NOT NULL,
    Charge               NUMBER(16, 4)    NOT NULL,
    CONSTRAINT PKChargeItem PRIMARY KEY (ChargeItemCode)
) 
;



-- 
-- TABLE: CheckInMethod 
--

CREATE TABLE CheckInMethod(
    CheckInMethodID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKCheckInMethod PRIMARY KEY (CheckInMethodID)
) 
;



-- 
-- TABLE: CheckOutMethod 
--

CREATE TABLE CheckOutMethod(
    CheckOutMethodID    NUMBER(38, 0)    NOT NULL,
    Caption             VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKCheckOutMethod PRIMARY KEY (CheckOutMethodID)
) 
;



-- 
-- TABLE: Country 
--

CREATE TABLE Country(
    CountryID    NUMBER(38, 0)    NOT NULL,
    Caption      VARCHAR2(18),
    CONSTRAINT PKCountry PRIMARY KEY (CountryID)
) 
;



-- 
-- TABLE: DAMaterial 
--

CREATE TABLE DAMaterial(
    DAMaterialId      NUMBER(38, 0)    NOT NULL,
    Executed          DATE              DEFAULT sysdate NOT NULL,
    Memo              VARCHAR2(64),
    StopDate          DATE,
    Amount            NUMBER(16, 4)    NOT NULL,
    IsActive          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created           DATE              DEFAULT SYSDATE NOT NULL,
    Updated           DATE              DEFAULT SYSDATE NOT NULL,
    DoctorAdviceId    NUMBER(38, 0)    NOT NULL,
    StopBy            NUMBER(38, 0),
    UseMethodID       NUMBER(38, 0)    NOT NULL,
    ApproachID        NUMBER(38, 0)    NOT NULL,
    MaterialID        NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDAMaterial PRIMARY KEY (DAMaterialId)
) 
;



-- 
-- TABLE: DAMedicine 
--

CREATE TABLE DAMedicine(
    DAMedicineID        NUMBER(38, 0)    NOT NULL,
    Executed            DATE              DEFAULT sysdate NOT NULL,
    Memo                VARCHAR2(64),
    StopDate            DATE,
    Amount              NUMBER(16, 4)    NOT NULL,
    IsActive            NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary         NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid             NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    DoctorAdviceId      NUMBER(38, 0)    NOT NULL,
    UseMethodID         NUMBER(38, 0)    NOT NULL,
    ApproachID          NUMBER(38, 0)    NOT NULL,
    StopBy              NUMBER(38, 0),
    PStockMedicineID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDAMedicine PRIMARY KEY (DAMedicineID)
) 
;



-- 
-- TABLE: DAOperation 
--

CREATE TABLE DAOperation(
    DAOperationId     NUMBER(38, 0)    NOT NULL,
    StopDate          DATE,
    Executed          DATE              DEFAULT sysdate NOT NULL,
    IsActive          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary       NUMBER(5, 0)      DEFAULT 1 NOT NULL
                      CHECK (IsTemporary in(0, 1)),
    IsValid           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Updated           DATE              DEFAULT SYSDATE NOT NULL,
    Created           DATE              DEFAULT SYSDATE NOT NULL,
    DoctorAdviceId    NUMBER(38, 0)    NOT NULL,
    OperationID       NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDAOperation PRIMARY KEY (DAOperationId)
) 
;



-- 
-- TABLE: DAService 
--

CREATE TABLE DAService(
    DAServiceId       NUMBER(38, 0)    NOT NULL,
    StopDate          DATE,
    IsTemporary       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Executed          DATE              DEFAULT sysdate NOT NULL,
    IsActive          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created           DATE              DEFAULT SYSDATE NOT NULL,
    Updated           DATE              DEFAULT SYSDATE NOT NULL,
    DoctorAdviceId    NUMBER(38, 0)    NOT NULL,
    ServiceID         NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDAService PRIMARY KEY (DAServiceId)
) 
;



-- 
-- TABLE: DATemplate 
--

CREATE TABLE DATemplate(
    DATemplateID    NUMBER(38, 0)    NOT NULL,
    Caption         VARCHAR2(32)     NOT NULL,
    Created         DATE              DEFAULT SYSDATE NOT NULL,
    Updated         DATE              DEFAULT SYSDATE NOT NULL,
    CONSTRAINT PKDATemplate PRIMARY KEY (DATemplateID)
) 
;



-- 
-- TABLE: DATMaterial 
--

CREATE TABLE DATMaterial(
    DATMaterialID    NUMBER(38, 0)    NOT NULL,
    Amount           NUMBER(16, 4)    NOT NULL,
    IsActive         NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary      NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created          DATE              DEFAULT SYSDATE NOT NULL,
    Updated          DATE              DEFAULT SYSDATE NOT NULL,
    DATemplateID     NUMBER(38, 0)    NOT NULL,
    MaterialID       NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDATMaterial PRIMARY KEY (DATMaterialID)
) 
;



-- 
-- TABLE: DATMedicine 
--

CREATE TABLE DATMedicine(
    DATMedicineID    NUMBER(38, 0)    NOT NULL,
    Amount           NUMBER(16, 4)    NOT NULL,
    IsActive         NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary      NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created          DATE              DEFAULT SYSDATE NOT NULL,
    Updated          DATE              DEFAULT SYSDATE NOT NULL,
    MedicineCode     VARCHAR2(16)     NOT NULL,
    UseMethodID      NUMBER(38, 0)    NOT NULL,
    ApproachID       NUMBER(38, 0)    NOT NULL,
    DATemplateID     NUMBER(38, 0)    NOT NULL,
    MedicineID       NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDATMedicine PRIMARY KEY (DATMedicineID)
) 
;



-- 
-- TABLE: DATOperation 
--

CREATE TABLE DATOperation(
    DATOperationID    NUMBER(38, 0)    NOT NULL,
    OPDate            DATE              DEFAULT SYSDATE NOT NULL,
    IsActive          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Updated           DATE              DEFAULT SYSDATE NOT NULL,
    Created           DATE              DEFAULT SYSDATE NOT NULL,
    DATemplateID      NUMBER(38, 0)    NOT NULL,
    OperationID       NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDATOperation PRIMARY KEY (DATOperationID)
) 
;



-- 
-- TABLE: DATService 
--

CREATE TABLE DATService(
    DATServiceID    NUMBER(38, 0)    NOT NULL,
    IsActive        NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary     NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid         NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created         DATE              DEFAULT SYSDATE NOT NULL,
    Updated         DATE              DEFAULT SYSDATE NOT NULL,
    DATemplateID    NUMBER(38, 0)    NOT NULL,
    ServiceID       NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDATService PRIMARY KEY (DATServiceID)
) 
;



-- 
-- TABLE: Department 
--

CREATE TABLE Department(
    DepartmentID    NUMBER(38, 0)    NOT NULL,
    Caption         VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKDepartment PRIMARY KEY (DepartmentID)
) 
;



-- 
-- TABLE: Diagnose 
--

CREATE TABLE Diagnose(
    DiagnoseID    NUMBER(38, 0)    NOT NULL,
    Caption       VARCHAR2(256)    NOT NULL,
    CONSTRAINT PKDiagnose PRIMARY KEY (DiagnoseID)
) 
;



-- 
-- TABLE: Doctor 
--

CREATE TABLE Doctor(
    DoctorID           NUMBER(38, 0)    NOT NULL,
    SectionOfficeID    NUMBER(38, 0)    NOT NULL,
    EmployeeID         NUMBER(38, 0)    NOT NULL,
    DoctorCatalogID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDoctor PRIMARY KEY (DoctorID)
) 
;



-- 
-- TABLE: DoctorAdvice 
--

CREATE TABLE DoctorAdvice(
    DoctorAdviceId        NUMBER(38, 0)    NOT NULL,
    IsActive              NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsTemporary           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid               NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Created               DATE              DEFAULT SYSDATE NOT NULL,
    Updated               DATE              DEFAULT SYSDATE NOT NULL,
    CreateBy              NUMBER(38, 0)    NOT NULL,
    UpdateBy              NUMBER(38, 0),
    DoctorID              NUMBER(38, 0)    NOT NULL,
    InpatientID           NUMBER(38, 0)    NOT NULL,
    DoctorAdviceTypeID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKDoctorAdvice PRIMARY KEY (DoctorAdviceId)
) 
;



-- 
-- TABLE: DoctorAdviceType 
--

CREATE TABLE DoctorAdviceType(
    DoctorAdviceTypeID    NUMBER(38, 0)    NOT NULL,
    Caption               VARCHAR2(32)     NOT NULL,
    ChargeCatalogCode     VARCHAR2(8)      NOT NULL,
    CONSTRAINT PKDoctorAdviceType PRIMARY KEY (DoctorAdviceTypeID)
) 
;



-- 
-- TABLE: DoctorCatalog 
--

CREATE TABLE DoctorCatalog(
    DoctorCatalogID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKDoctorCatalog PRIMARY KEY (DoctorCatalogID)
) 
;



-- 
-- TABLE: Dose 
--

CREATE TABLE Dose(
    DoseID     NUMBER(38, 0)    NOT NULL,
    Caption    VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKDose PRIMARY KEY (DoseID)
) 
;



-- 
-- TABLE: Employee 
--

CREATE TABLE Employee(
    EmployeeID      NUMBER(38, 0)    NOT NULL,
    LoginName       VARCHAR2(16)     NOT NULL,
    GrantA          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantA in(0, 1)),
    GrantB          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantB in(0, 1)),
    GrantC          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantC in(0, 1)),
    GrantE          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantE in(0, 1)),
    GrantD          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantD in(0, 1)),
    GrantF          NUMBER(5, 0)      DEFAULT 0 NOT NULL
                    CHECK (GrantF in(0, 1)),
    Password        VARCHAR2(16)     NOT NULL,
    CName           VARCHAR2(32)     NOT NULL,
    DepartmentID    NUMBER(38, 0)    NOT NULL,
    JobID           NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKEmployee PRIMARY KEY (EmployeeID)
) 
;



-- 
-- TABLE: Food 
--

CREATE TABLE Food(
    FoodID     NUMBER(38, 0)    NOT NULL,
    Caption    VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKFood PRIMARY KEY (FoodID)
) 
;



-- 
-- TABLE: FrequencyWeek 
--

CREATE TABLE FrequencyWeek(
    FrequencyWeekID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKFrequencyWeek PRIMARY KEY (FrequencyWeekID)
) 
;



-- 
-- TABLE: Inpatient 
--