|
|
USE master;
|
|
|
|
|
|
IF EXISTS (SELECT *
|
|
|
FROM sysdatabases
|
|
|
WHERE name = 'DIMS')
|
|
|
BEGIN
|
|
|
DROP DATABASE DIMS;
|
|
|
END;
|
|
|
|
|
|
CREATE DATABASE DIMS;
|
|
|
|
|
|
GO
|
|
|
|
|
|
USE DIMS;
|
|
|
|
|
|
CREATE LOGIN dba WITH PASSWORD = 'abcd1234@', DEFAULT_DATABASE = DIMS;
|
|
|
|
|
|
CREATE USER dba FOR LOGIN dba WITH DEFAULT_SCHEMA = dbo;
|
|
|
|
|
|
EXEC sp_addrolemember 'db_owner', 'dba';
|
|
|
|
|
|
CREATE TABLE Admin( -- 库存管理员
|
|
|
Ano VARCHAR(20) PRIMARY KEY, -- 编号
|
|
|
Aname VARCHAR(20) NOT NULL, -- 姓名
|
|
|
Asex BIT NOT NULL DEFAULT 1, -- 性别 (1 为男，0 为女)
|
|
|
Aage SMALLINT NOT NULL, -- 年龄
|
|
|
Apwd VARCHAR(20) NOT NULL DEFAULT '00000000' -- 登陆密码
|
|
|
);
|
|
|
|
|
|
CREATE TABLE Doctor( -- 医生
|
|
|
Dno VARCHAR(20) PRIMARY KEY, -- 编号
|
|
|
Dname VARCHAR(20) NOT NULL, -- 姓名
|
|
|
Dsex BIT NOT NULL DEFAULT 1, -- 性别 (1 为男，0 为女)
|
|
|
Dage SMALLINT NOT NULL, -- 年龄
|
|
|
Dpwd VARCHAR(20) NOT NULL DEFAULT '00000000' -- 登陆密码
|
|
|
);
|
|
|
|
|
|
CREATE TABLE Nurse( -- 发药处护士
|
|
|
Nno VARCHAR(20) PRIMARY KEY, -- 编号
|
|
|
Nname VARCHAR(20) NOT NULL, -- 姓名
|
|
|
Nsex BIT NOT NULL DEFAULT 1, -- 性别 (1 为男，0 为女)
|
|
|
Nage SMALLINT NOT NULL, -- 年龄
|
|
|
Npwd VARCHAR(20) NOT NULL DEFAULT '00000000' -- 登陆密码
|
|
|
);
|
|
|
|
|
|
CREATE TABLE Supplier( -- 供应商
|
|
|
Sno VARCHAR(20) PRIMARY KEY, -- 编号
|
|
|
Sname VARCHAR(60) NOT NULL, -- 名称
|
|
|
Saddr VARCHAR(60) NOT NULL, -- 地址
|
|
|
Sphone VARCHAR(20) NOT NULL -- 电话
|
|
|
);
|
|
|
|
|
|
CREATE TABLE Drug( -- 药品
|
|
|
PDno VARCHAR(20) PRIMARY KEY, -- 编号
|
|
|
PDname VARCHAR(20) NOT NULL, -- 名称
|
|
|
PDlife SMALLINT NOT NULL -- 保质期 (天数)
|
|
|
);
|
|
|
|
|
|
CREATE TABLE InventoryDrug( -- 库存药品
|
|
|
PDno VARCHAR(20), -- 编号
|
|
|
PDbatch DATE, -- 批次
|
|
|
PDnum SMALLINT NOT NULL, -- 数量
|
|
|
Sno VARCHAR(20) NOT NULL, -- 供应商编号
|
|
|
SAno VARCHAR(20) NOT NULL, -- 入库库存管理员编号
|
|
|
Stime DATETIME NOT NULL DEFAULT GETDATE(), -- 入库时间
|
|
|
PRIMARY KEY(PDno, PDbatch),
|
|
|
FOREIGN KEY (PDno) REFERENCES Drug(PDno),
|
|
|
FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
|
|
|
FOREIGN KEY (SAno) REFERENCES Admin(Ano)
|
|
|
);
|
|
|
|
|
|
CREATE TABLE DestroyedDrug( -- 已销毁药品
|
|
|
PDno VARCHAR(20), -- 编号
|
|
|
PDbatch DATE, -- 批次
|
|
|
PDnum SMALLINT NOT NULL, -- 数量
|
|
|
Sno VARCHAR(20) NOT NULL, -- 供应商编号
|
|
|
SAno VARCHAR(20) NOT NULL, -- 入库库存管理员编号
|
|
|
Stime DATETIME NOT NULL, -- 入库时间
|
|
|
DAno VARCHAR(20) NOT NULL, -- 销毁库存管理员编号
|
|
|
Dtime DATETIME NOT NULL DEFAULT GETDATE(), -- 销毁时间
|
|
|
PRIMARY KEY(PDno, PDbatch),
|
|
|
FOREIGN KEY (PDno) REFERENCES Drug(PDno),
|
|
|
FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
|
|
|
FOREIGN KEY (SAno) REFERENCES Admin(Ano),
|
|
|
FOREIGN KEy (DAno) REFERENCES Admin(Ano)
|
|
|
);
|
|
|
|
|
|
CREATE TABLE Prescription( -- 处方
|
|
|
Pno INT PRIMARY KEY IDENTITY (1, 1), -- 编号
|
|
|
Pid VARCHAR(20) NOT NULL, -- 病人身份证号码
|
|
|
Dno VARCHAR(20) NOT NULL, -- 开出医生编号
|
|
|
Ptime DATETIME NOT NULL, -- 开出时间
|
|
|
Nno VARCHAR(20), -- 处理护士编号
|
|
|
Htime DATETIME, -- 处理时间
|
|
|
Pstate BIT NOT NULL DEFAULT 0, -- 状态 (1 为已处理，0 为未处理)
|
|
|
FOREIGN KEY (Dno) REFERENCES Doctor(Dno),
|
|
|
FOREIGN KEY (Nno) REFERENCES Nurse(Nno)
|
|
|
);
|
|
|
|
|
|
CREATE TABLE PID( -- 处方包含的药品
|
|
|
Pno INT, -- 处方编号
|
|
|
PDno VARCHAR(20), -- 药品编号
|
|
|
PDnum SMALLINT NOT NULL, -- 药品数量
|
|
|
PRIMARY KEY (Pno, PDno),
|
|
|
FOREIGN KEY (Pno) REFERENCES Prescription(Pno),
|
|
|
FOREIGN KEY (PDno) REFERENCES Drug(PDno)
|
|
|
);
|
|
|
|
|
|
GO
|
|
|
|
|
|
CREATE VIEW DrugView
|
|
|
AS
|
|
|
SELECT d.PDno, d.PDname, d.PDlife, COALESCE(SUM(i.PDnum), 0) AS PDnum
|
|
|
FROM Drug d LEFT OUTER JOIN InventoryDrug i ON d.PDno = i.PDno
|
|
|
GROUP BY d.PDno, d.PDname, d.PDlife;
|
|
|
|
|
|
GO
|
|
|
|
|
|
CREATE VIEW InventoryDrugView
|
|
|
AS
|
|
|
SELECT d.PDno, d.PDname, d.PDlife, i.PDbatch, i.PDnum, i.Sno, i.SAno, i.Stime
|
|
|
FROM Drug d, InventoryDrug i
|
|
|
WHERE d.PDno = i.PDno;
|
|
|
|
|
|
GO
|
|
|
|
|
|
CREATE VIEW DestroyedDrugView
|
|
|
AS
|
|
|
SELECT d.PDno, d.PDname, d.PDlife, dd.PDbatch, dd.PDnum, dd.Sno, dd.SAno, dd.Stime, dd.DAno, dd.Dtime
|
|
|
FROM Drug d, DestroyedDrug dd
|
|
|
WHERE d.PDno = dd.PDno;
|
|
|
|
|
|
GO
|
|
|
|
|
|
CREATE VIEW AllPDbatchView
|
|
|
AS
|
|
|
SELECT PDno, PDname, PDlife, PDbatch, PDnum, Sno, SAno, Stime FROM InventoryDrugView
|
|
|
UNION
|
|
|
SELECT PDno, PDname, PDlife, PDbatch, PDnum, Sno, SAno, Stime FROM DestroyedDrugView;
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|
|
INSERT INTO Admin(Ano, Aname, Asex, Aage, Apwd) VALUES('admin0001', '李勇', 1, 25, 'admin0001');
|
|
|
INSERT INTO Admin(Ano, Aname, Asex, Aage, Apwd) VALUES('admin0002', '刘晨', 0, 28, 'admin0002');
|
|
|
INSERT INTO Admin(Ano, Aname, Asex, Aage, Apwd) VALUES('admin0003', '王敏', 0, 24, 'admin0003');
|
|
|
INSERT INTO Admin(Ano, Aname, Asex, Aage, Apwd) VALUES('admin0004', '张立', 1, 27, 'admin0004');
|
|
|
INSERT INTO Admin(Ano, Aname, Asex, Aage, Apwd) VALUES('admin0005', '张三', 1, 32, 'admin0005');
|
|
|
|
|
|
INSERT INTO Doctor(Dno, Dname, Dsex, Dage, Dpwd) VALUES('doctor0001', '李四', 1, 39, 'doctor0001');
|
|
|
INSERT INTO Doctor(Dno, Dname, Dsex, Dage, Dpwd) VALUES('doctor0002', '王红', 0, 35, 'doctor0002');
|
|
|
INSERT INTO Doctor(Dno, Dname, Dsex, Dage, Dpwd) VALUES('doctor0003', '何琳', 0, 29, 'doctor0003');
|
|
|
INSERT INTO Doctor(Dno, Dname, Dsex, Dage, Dpwd) VALUES('doctor0004', '李敏', 0, 38, 'doctor0004');
|
|
|
INSERT INTO Doctor(Dno, Dname, Dsex, Dage, Dpwd) VALUES('doctor0005', '张辉', 1, 41, 'doctor0005');
|
|
|
|
|
|
INSERT INTO Nurse(Nno, Nname, Nsex, Nage, Npwd) VALUES('nurse0001', '白磊', 1, 29, 'nurse0001');
|
|
|
INSERT INTO Nurse(Nno, Nname, Nsex, Nage, Npwd) VALUES('nurse0002', '杜鹃', 0, 35, 'nurse0002');
|
|
|
INSERT INTO Nurse(Nno, Nname, Nsex, Nage, Npwd) VALUES('nurse0003', '李强', 1, 32, 'nurse0003');
|
|
|
INSERT INTO Nurse(Nno, Nname, Nsex, Nage, Npwd) VALUES('nurse0004', '张飞', 1, 44, 'nurse0004');
|
|
|
INSERT INTO Nurse(Nno, Nname, Nsex, Nage, Npwd) VALUES('nurse0005', '黎敏', 0, 25, 'nurse0005');
|
|
|
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SYT0001', '遵义市意通医药有限责任公司', '遵义市沙河小区华南C2栋', '17811941621');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SDS0001', '贵州鼎圣药业有限公司', '遵义市红花岗区沙河路B号楼2楼', '17822557252');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SGT0001', '贵州国泰医药有限公司', '贵阳市富源北路35号', '17833553673');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SFT0001', '铜仁梵天药业有限公司', '铜仁市梵净山大道绿福宫小区', '17845457475');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SKYQD0001', '贵州科渝奇鼎医药有限公司', '贵阳市园林路1号', '17855257965');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SZX0001', '贵州振兴医药有限公司贵阳分公司', '贵阳市舒家寨富源中路261号', '17867568686');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SGYKG0001', '国药控股贵州有限公司', '贵阳国家高新技术产业开发区金阳科技产业园', '17837785477');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SXZT0001', '铜仁新中太药业有限公司', '铜仁市文笔路25号', '17880257080');
|
|
|
INSERT INTO Supplier(Sno, Sname, Saddr, Sphone)
|
|
|
VALUES('SYC0001', '贵州省药材公司', '贵阳市富源北路22号A区', '17859030598');
|
|
|
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H31020838', '阿莫西林胶囊', 180);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H20043730', '氨苄西林钠', 120);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H32021306', '青霉素钠', 90);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H19993751', '磷霉素钠', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H15021340', '头孢氨苄甲氧苄啶胶囊', 270);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H31020892', '头孢拉定胶囊', 120);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H10910034', '头孢曲松钠', 90);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H20153143', '头孢吡肟', 180);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H20023788', '头孢噻肟钠', 480);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z20054523', '骨筋丸胶囊', 180);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z51020341', '复方穿心莲片', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z20083397', '四季感冒片', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z34020775', '午时茶颗粒', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z63020270', '十三味马钱子丸', 720);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('H41024386', '氯芬黄敏片', 540);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z20025728', '痫愈胶囊', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('HC20181007', '布地奈德鼻喷雾剂', 180);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('X20010202', '甲磺酸多沙唑嗪控释片', 360);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('J20030013', '噻奈普汀片', 180);
|
|
|
INSERT INTO Drug(PDno, PDname, PDlife) VALUES('Z20120003', '复方樟薄软膏', 720);
|
|
|
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H31020838', '2019-06-20', 15, 'SYT0001', 'admin0001', '2019-06-25 10:30:30.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H31020838', '2019-11-25', 270, 'SKYQD0001', 'admin0002', '2019-11-30 13:59:53.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H20043730', '2019-08-18', 32, 'SDS0001', 'admin0001', '2019-08-25 10:28:23.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H20043730', '2019-11-27', 500, 'SFT0001', 'admin0003', '2019-11-30 15:25:22.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H32021306', '2019-09-30', 180, 'SGT0001', 'admin0002', '2019-10-10 15:05:26.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H19993751', '2019-06-01', 280, 'SFT0001', 'admin0002', '2019-06-10 16:45:39.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H15021340', '2019-03-15', 5, 'SKYQD0001', 'admin0003', '2019-03-25 09:16:30.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H31020892', '2019-09-01', 50, 'SZX0001', 'admin0003', '2019-09-10 10:30:30.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H10910034', '2019-10-08', 50, 'SGYKG0001', 'admin0004', '2019-10-11 10:36:55.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H20153143', '2019-11-05', 245, 'SXZT0001', 'admin0004', '2019-11-09 14:29:16.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H20023788', '2019-11-21', 165, 'SYC0001', 'admin0005', '2019-11-25 11:53:55.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z20054523', '2019-10-10', 5, 'SFT0001', 'admin0003', '2019-10-11 15:29:42.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z20054523', '2019-11-01', 15, 'SFT0001', 'admin0005', '2019-11-05 17:35:23.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z20054523', '2019-11-06', 500, 'SFT0001', 'admin0002', '2019-11-08 14:25:12.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z51020341', '2019-11-08', 14, 'SFT0001', 'admin0001', '2019-11-11 15:25:22.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z51020341', '2019-11-10', 300, 'SFT0001', 'admin0001', '2019-11-15 15:16:29.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z20083397', '2019-11-25', 500, 'SFT0001', 'admin0001', '2019-11-30 14:35:02.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z34020775', '2019-11-25', 25, 'SFT0001', 'admin0002', '2019-12-01 15:46:02.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z63020270', '2019-11-29', 3, 'SFT0001', 'admin0004', '2019-12-02 16:47:32.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z63020270', '2019-12-01', 600, 'SFT0001', 'admin0004', '2019-12-04 08:25:32.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H41024386', '2019-12-01', 35, 'SFT0001', 'admin0003', '2019-12-04 09:22:55.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('H41024386', '2019-12-02', 700, 'SFT0001', 'admin0005', '2019-12-05 15:56:22.000');
|
|
|
INSERT INTO InventoryDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime)
|
|
|
VALUES('Z20025728', '2019-12-05', 109, 'SFT0001', 'admin0002', '2019-12-09 10:45:06.000');
|
|
|
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H20023788', '2018-06-10', 5, 'SYC0001', 'admin0005', '2018-06-25 10:30:30.000',
|
|
|
'admin0001', '2019-09-30 15:22:16.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H20153143', '2018-08-18', 7, 'SXZT0001', 'admin0005', '2018-08-25 10:28:23.000',
|
|
|
'admin0002', '2019-02-06 16:13:43.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H10910034', '2018-09-30', 13, 'SGYKG0001', 'admin0004', '2018-10-10 15:05:26.000',
|
|
|
'admin0001', '2018-12-24 09:16:55.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H31020892', '2018-06-01', 9, 'SZX0001', 'admin0004', '2018-06-10 16:45:39.000',
|
|
|
'admin0004', '2018-09-24 13:59:15.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H15021340', '2018-03-15', 15, 'SKYQD0001', 'admin0003', '2018-03-25 09:16:30.000',
|
|
|
'admin0001', '2018-12-05 11:05:06.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H19993751', '2018-06-01', 3, 'SFT0001', 'admin0003', '2018-06-10 10:30:30.000',
|
|
|
'admin0001', '2019-05-10 16:17:05.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H32021306', '2018-10-08', 5, 'SGT0001', 'admin0002', '2018-10-11 10:36:55.000',
|
|
|
'admin0005', '2019-01-02 12:05:53.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H20043730', '2018-11-05', 7, 'SDS0001', 'admin0002', '2018-11-09 14:29:16.000',
|
|
|
'admin0003', '2019-02-26 10:59:05.000');
|
|
|
INSERT INTO DestroyedDrug(PDno, PDbatch, PDnum, Sno, SAno, Stime, DAno, Dtime)
|
|
|
VALUES('H31020838', '2018-11-21', 17, 'SYT0001', 'admin0001', '2018-11-25 11:53:55.000',
|
|
|
'admin0002', '2019-05-05 15:15:29.000');
|
|
|
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019901023', 'doctor0001', '2019-12-01 09:05:10.000', 'nurse0002', '2019-12-01 09:25:13.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019981102', 'doctor0002', '2019-12-01 09:22:19.000', 'nurse0003', '2019-12-01 09:28:33.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019971206', 'doctor0003', '2019-12-01 09:35:17.000', 'nurse0005', '2019-12-01 09:45:19.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019810516', 'doctor0004', '2019-12-01 09:55:10.000', 'nurse0004', '2019-12-01 10:05:52.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019760215', 'doctor0005', '2019-12-01 10:05:59.000', 'nurse0001', '2019-12-01 10:25:33.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019950916', 'doctor0004', '2019-12-01 10:55:18.000', 'nurse0002', '2019-12-01 11:05:46.000', 1);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019910416', 'doctor0003', '2019-12-01 15:19:35.000', NULL, NULL, 0);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019970730', 'doctor0002', '2019-12-01 15:27:45.000', NULL, NULL, 0);
|
|
|
INSERT INTO Prescription(Pid, Dno, Ptime, Nno, Htime, Pstate)
|
|
|
VALUES('44019921016', 'doctor0001', '2019-12-01 15:35:16.000', NULL, NULL, 0);
|
|
|
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(1, 'H31020838', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(1, 'H32021306', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(1, 'Z20025728', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(1, 'Z51020341', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(2, 'H15021340', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(2, 'Z34020775', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(2, 'Z20054523', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(3, 'H10910034', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(3, 'H20153143', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(3, 'H20023788', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(4, 'H20043730', 5);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(4, 'H32021306', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(5, 'H20043730', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(5, 'H32021306', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(5, 'H15021340', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(5, 'H10910034', 7);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(5, 'H20023788', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(6, 'H20043730', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(6, 'H19993751', 1);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(7, 'Z63020270', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(7, 'Z51020341', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(7, 'Z20054523', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(7, 'H19993751', 4);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(8, 'H31020892', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(8, 'H20153143', 3);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(8, 'H20023788', 2);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(9, 'Z51020341', 1);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(9, 'Z20083397', 7);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(9, 'H31020838', 1);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(9, 'H41024386', 7);
|
|
|
INSERT INTO PID(Pno, PDno, PDnum) VALUES(9, 'Z20025728', 1);
|
