友情连接

1、医疗信息管理系统数据库–MySQL【中文版】

医疗信息管理系统数据库–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