- 友情连接
- 1、医疗信息管理系统数据库--MySQL【中文版】
- 2、邮件管理数据库设计--MySQL【中文版】
- 3、学生成绩管理系统数据库设计--MySQL
- 4、点餐系统数据库设计--SQL Server
- 5、商品管理系统数据库设计--SQL Server
- 6、SQL Server电影院数据库管理系统【英文版-源码】--（Movie Theatre Management System Database）
- 1. ER图
- 2. 创建数据库及数据表、插入数据
- 3. 查询数据
- 4. 创建视图
医疗信息管理系统数据库–MySQL【中文版】
2、邮件管理数据库设计–MySQL【中文版】邮件管理数据库设计–MySQL【中文版】
3、学生成绩管理系统数据库设计–MySQL学生成绩管理系统数据库设计–MySQL
4、点餐系统数据库设计–SQL Server点餐系统数据库设计–SQL Server
5、商品管理系统数据库设计–SQL Server商品管理系统数据库设计–SQL Server
6、SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database）SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database）
1. ER图 2. 创建数据库及数据表、插入数据
--******Create/Drop Databse******
-- if MedicalManagementSystem exists, kill current connections to Database
-- make single users
IF DB_ID('MedicalManagementSystem') IS NOT NULL
BEGIN
USE [MASTER];
ALTER DATABASE [MedicalManagementSystem]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE MedicalManagementSystem;
END
GO
-- create new database called MedicalManagementSystem
CREATE DATABASE MedicalManagementSystem;
GO
USE MedicalManagementSystem;
GO
--******Create Tables*******
--table 1: users_info
DROP TABLE IF EXISTS users_info
GO
CREATE TABLE users_info
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAr(50) NOT NULL,
last_name VARCHAr(50) NOT NULL,
login_name VARCHAr(20) NOT NULL,
password VARCHAr(20) NOT NULL,
permit VARCHAr(20) NOT NULL,
user_type VARCHAr(20) NOT NULL,
gender VARCHAr(10) NOT NULL,
age TINYINT NOT NULL,
tel VARCHAr(20) NOT NULL,
email VARCHAr(50) NOT NULL,
address VARCHAr(70) NOT NULL,
hire_date DATE NOT NULL
);
GO
--table 2: patients_info
DROP TABLE IF EXISTS patients_info
GO
CREATE TABLE patients_info
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
--table 3: drugs_devices_info
DROP TABLE IF EXISTS drugs_devices_info
GO
CREATE TABLE drugs_devices_info
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
-- table 4: treat_price
-- copay means cost of registration
-- consultation_fee means cost of diagnosis
DROP TABLE IF EXISTS treat_price
GO
CREATE TABLE treat_price
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
-- table 5: appointment
DROP TABLE IF EXISTS appointment
GO
CREATE TABLE appointment
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
-- table 6: case_history
DROP TABLE IF EXISTS case_history
GO
CREATE TABLE case_history
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
-- table 7: medical_records
DROP TABLE IF EXISTS medical_records
GO
CREATE TABLE medical_records
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
-- table 8: nursing
DROP TABLE IF EXISTS nursing
GO
CREATE TABLE nursing
(
-- 需要完整代码请添加文章底部微信，付费咨询
);
GO
--******Database Population*******
INSERT INTO users_info
VALUES ('Andrew', 'Adams', 'python', 'jwjehj', 'lowlevel', 'receptionist', 'female', 30, '123456789', 'adfff@gmail.com', '585 Robertson Drive Bunbury', convert(datetime, '2/6/2019', 103)),
-- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO patients_info
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
-- drugs : dr00000XX
-- devices : de0000XX
INSERT INTO drugs_devices_info
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO treat_price
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO appointment
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO case_history
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO medical_records
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
INSERT INTO nursing
VALUES -- 需要完整代码请添加文章底部微信，付费咨询
3. 查询数据
USE MedicalManagementSystem; GO -- Query 1: Young Doctors -- SELECT doctor's first name, age, user_type -- WHERe: -- age less than 40 -- Order the results by age SELECT first_name, age, user_type FROM dbo.users_info WHERe user_type = 'doctor' AND age < 40 ORDER BY age; -- Query 2: The Number of Patients in Each Doctor's Care -- SELECT doctor's first name, the number of patients, -- WHERe: -- the doctor's hire_date in June 2019 -- Order the results by the number of patients SELECT u.first_name, p.number -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY p.number; -- Query 3: Drugs Being Used -- SELECT drug's id, name, specification, quantity, expriry_date and the number of being used -- WHERe: -- it was being used in prescription -- Order the results by the number of being used, in descending order SELECT d.d_id, d.name, d.specification, d.quantity, d.expiry_date, m.number -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY m.number DESC; -- Query 4: Appointments and Nurse Care -- SELECT the patient's name who had an appointment, the whole time of being cared by nurse -- show all the patients, even if the patient was not being cared by nurse -- Order by the time of being cared, in descending order SELECT a.pt_id, ISNULL(DATEDIFF(hour, n.start_time, n.finish_time), 0) AS care_time -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY care_time DESC; -- Query 5: Patient's Case History and Prescription -- SELECT patient's id, first name, case history -- WHERe: -- the number of sum total drugs and devices that being used to patients more than 1 -- Order by the patient's id SELECT pa.pt_id, pa.first_name, c.description, c.diagnosis, c.therapy, me.number -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY pa.pt_id; -- Query 6: The Patients Who Was Diagnosed by the Most Expensive Doctor -- SELECT patient's id, full name, age, gender and doctor's full name, -- WHERe: -- the doctor have the most expensive consultation_fee and have been in charge of patients -- Order the results by patient's id SELECT p.pt_id, p.first_name + ' ' + p.last_name AS patient_name, p.age, p.gender, u.first_name + ' ' + u.last_name AS doctor_name, t.consultation_fee -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY p.pt_id; -- Query 7: The Three Oldest Patients -- SELECT the full name, gender, age, the case history and the docotr's full name of the three oldest patients -- Order the results by patient's age, in descending order SELECT TOP (3) p.first_name + ' ' + p.last_name AS patient_name, p.gender, p.age, c.description, c.diagnosis, c.therapy, u.first_name + ' ' + u.last_name AS doctor_name -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY p.age DESC; -- Query 8: Patients With The Same Drug -- SELECT the full name, gender, age, the case history and drug's name, drug's dosage of patients -- WHERe: -- more than 1 people use the same drug -- Order the results by drug's name ASC, drug's dosage in descending order SELECT dr.name AS drug_name, me.quantity AS dosage, pa.first_name + ' ' + pa.last_name AS patient_name, pa.gender, pa.age, ca.description, ca.diagnosis, ca.therapy -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY drug_name ASC, dosage DESC; -- Query 9: Total Cost of Patients -- SELECT every patient's cost of copay, consultation_fee, drugs, devices, total -- the cost of copay, consultation_fee are calculated in times -- and the doctors who are in the patients_info table for one time means one registration and one diagnosis -- Order the results by total cost, in descending order SELECT c.pt_id, t.copay, t.consultation_fee, ISNULL(SUM(m1.quantity * d.selling_price), 0) AS drug_cost, ISNULL(SUM(m2.quantity * d.selling_price), 0) AS device_cost, (t.consultation_fee + t.consultation_fee + ISNULL(SUM(m1.quantity * d.selling_price), 0) + -- 需要完整代码请添加文章底部微信，付费咨询 ORDER BY total_cost DESC;4. 创建视图
USE MedicalManagementSystem; GO -- Patients View -- Create a view that selects the patient's id, full name, gender, age, description, diagnosis, therapy DROP VIEW IF EXISTS v_patients GO CREATE VIEW v_patients AS SELECT pa.pt_id, pa.first_name + ' ' + pa.last_name AS patient_name, pa.gender, pa.age, ca.description, ca.diagnosis, ca.therapy -- 需要完整代码请添加文章底部微信，付费咨询 GO -- Doctors View -- Create a view that selects the following details: -- The doctor's id, full name, gender, age, tel, email, hired date -- The number of patients who were diagnosed by the doctor -- The total revenue of copay and consultation_fee of the doctor DROP VIEW IF EXISTS v_doctors GO CREATE VIEW v_doctors AS SELECT u.id, u.first_name + ' ' + u.last_name AS doctor_name, u.gender, u.age, u.tel, u.email, u.hire_date, ISNULL(pa.patients_num, 0) AS patients_num, ISNULL((t.copay + t.consultation_fee) * pa.patients_num, 0) AS total_revenue -- 需要完整代码请添加文章底部微信，付费咨询 WHERe u.user_type = 'doctor'; GO
