First, create the database, establish the main foreign key constraints between the tables, and insert the data.
create database Hospital information management system
on(
name=Hospital information management system,
filename='D:Database course design hospital information management system hospital information management system.mdf',
size=5,
filegrowth=1
)
log on
( name=Hospital information management system_log,
filename='D:Database course design hospital information management system hospital information management system_log.ldf',
size=2,
maxsize=30,
filegrowth=10%
)
use Hospital information management system
go
create table Doctor table
(Doctor number char(15) not null primary key,
full name char(10) not null unique,
Gender char(5) null,
Age int null,
title char(11) null,
Department number char(20) null)
go
alter table Doctor table
add constraint ys_zc default 'physician' for title
go
alter table Doctor table
add constraint fk_ks foreign key(Department number) references Department table(Department number)
go
create table Department table
(Department number char(20) not null primary key,
Department name char(10) not null ,
Department address char(20) null,
Department telephone char(10) null,
Department Director char(10) null)
go
create table Patient table
(Medical record number char(15) not null primary key,
full name char(10) not null,
Gender char(5) null,
Age int null,
Ward number char(15) null,
Name of doctor char(10) null,
blood type char(4) null,
Confirmed results char(10)null,
Department number char(20) null)
go
alter table Patient table
add constraint fk_ksh foreign key(Department number) references Department table(Department number)
go
create table Ward table
(Ward number char(15) not null primary key,
Number of beds int null,
Ward address char(20) null,
Department number char(20) null)
go
alter table Patient table
add constraint fk_bfh foreign key(Ward number) references Ward table(Ward number)
go
alter table Ward table
add constraint fk_sk foreign key(Department number) references Department table(Department number)
go
create table Nurse table
(Nurse number char(15) not null primary key,
full name char(10) not null,
Gender char(5) null,
Age int null,
Department number char(20) null)
go
alter table Nurse table
add constraint fk_skh foreign key(Department number) references Department table(Department number)
go
create table Allocation table
( Ward number char(15) not null,
Nurse number char(15) not null,
primary key (Ward number,Nurse number),
foreign key(Ward number) references Ward table(Ward number),
foreign key(Nurse number) references Nurse table(Nurse number))
go
insert into Department table(Department number,Department name,Department address,Department telephone,Department Director)
values('101','internal medicine','A1101','10001','Goofy'),
('102','Surgery','A1102','10002','Li Xiang'),
('103','pediatrics','A1103','10003','Deng Yang'),
('104','gynaecology','A1104','10004','Liu Yu'),
('105','Department of Neurology','A1105','10005','Bai Hao'),
('201','Brain surgery','A1201','20001','Song Siqi'),
('202','Urology Department','A1202','20002','Sun Yi'),
('203','orthopaedics','A1203','20003','Yangzilong'),
('204','Endocrine Department','A1204','20004','Zhang Ziyi'),
('205','Dental Department','A1205','20005','Guo Yiman'),
('301','Ophthalmology Department','A1301','30001','Wang Luming'),
('302','Otolaryngology and rhinology','A1302','30002','Han Ziqi'),
('303','the skin department','A1303','30003','Xuruohong'),
('304','Cardiac surgery','A1304','30004','Li Li'),
('305','Thoracic Surgery','A1305','30005','Ruoyu Chen')
go
insert into Doctor table(Doctor number,full name,Gender,Age,title,Department number)
values('101001','Goofy','male','30','Chief physician','101'),
('101002','Yang Lei','female','24','Resident physician','101'),
('101003','Qi Feng','male','27','attending doctor','101'),
('101004','Li Xiang','female','45','Chief physician','102'),
('101005','Zhu Zhu','female','30','attending doctor','102'),
('101006','Landwind','male','35','attending doctor','102'),
('101007','Deng Yang','male','30','attending doctor','103'),
('101008','Cheng Yue','female','28','Associate attending physician','103'),
('101009','Liu Yu','female','40','Deputy chief physician','104'),
('101010','Liu Ziyi','female','34','attending doctor','104'),
('101011','Bai Hao','male','58','Chief physician','105'),
('101012','Su Nan','female','32','attending doctor','105'),
('101013','Song Siqi','female','50','Chief physician','201'),
('101014','Ludaming','male','24','Resident physician','201'),
('101015','Sun Yi','female','33','attending doctor','202'),
('101016','Liu Nan','male','33','attending doctor','202'),
('101017','Yangzilong','male','35','attending doctor','203'),
('101018','Jiao Dan','female','23','Resident physician','203'),
('101019','Zhang Ziyi','female','38','Deputy chief physician','204'),
('101020','Li Juan','female','32','attending doctor','204'),
('101021','Guo Yiman','female','35','attending doctor','205'),
('101022','Liu Xiao','male','22','Resident physician','205'),
('101023','Wang Luming','male','36','attending doctor','301'),
('101024','Zhang Zhen','female','29','Associate attending physician','301'),
('101025','Han Ziqi','male','31','attending doctor','302'),
('101026','Qiao Fang','female','31','attending doctor','302'),
('101027','Xuruohong','male','49','Chief physician','303'),
('101028','Lu Lei','male','31','attending doctor','303'),
('101029','Li Li','female','51','Chief physician','304'),
('101030','Gao Tao','male','31','attending doctor','304'),
('101031','Ruoyu Chen','female','39','Deputy chief physician','305'),
('101032','Liu Siyu','female','30','attending doctor','305')
go
insert into Ward table(Ward number,Number of beds,Ward address,Department number)
values('001','4','B1001','101'),
('002','5','B1002','101'),
('003','1','B1003','102'),
('004','2','B1004','102'),
('005','0','B1005','103'),
('006','3','B1006','103'),
('007','2','B1007','104'),
('008','3','B1008','105'),
('009','1','B1009','105'),
('010','2','B1010','201'),
('011','1','B1011','201'),
('012','3','B1012','202'),
('013','2','B1013','203'),
('014','2','B1014','204'),
('015','1','B1015','204'),
('016','5','B1016','205'),
('017','2','B1017','301'),
('018','1','B1018','302'),
('019','3','B1019','303'),
('020','1','B1020','303'),
('021','2','B1021','304'),
('022','1','B1022','304'),
('023','3','B1023','305'),
('024','2','B1024','305')
go
select *
from Department table
go
select *
from Doctor table
go
select Doctor table.full name , Department name
from Doctor table join Department table
on Doctor table.Department number=Department table.Department number
go
use Hospital information management system
go
insert into Patient table(Medical record number,full name,Gender,Age,Ward number,Name of doctor,blood type,Confirmed results,Department number)
values('21101','Wang Shengan','male','31','001','Qi Feng','A','pneumonia','101'),
('21102','Caizhuangbao','male','54','002','Goofy','B','Chronic tracheitis','101'),
('21103','Yi Jiangwei','male','28','003','Landwind','O','Spontaneous pneumothorax','102'),
('21104','Zhangshungu','male','32','004','Zhu Zhu','AB','Cholelithiasis','102'),
('21105','Li Xinhao','male','8','006','Deng Yang','A','bacterial dysentery','103'),
('21106','Liang Chengjing','female','8','006','Cheng Yue','A','Noru diarrhea','103'),
('21107','Shi dangshu','female','26','007','Liu Yu','B','cervicitis','104'),
('21108','He Gangming','male','66','008','Bai Hao','O','cerebral infarction','105'),
('21109','Yan Xihua','male','58','008','Su Nan','B','cerebral hemorrhage','105'),
('21110','Liu Xianfa','male','32','010','Song Siqi','A','Hydrocephalus','201'),
('21111','He Yi','male','35','010','Ludaming','O','brain abscess','201'),
('21112','tae ','male','38','011','Song Siqi','B','Craniocerebral trauma','201'),
('21113','Zhangshunlian','male','50','012','Liu Nan','A','urinary calculus ','202'),
('21114','Tang Qing','female','29','012','Sun Yi','B','Acute nephritis','202'),
('21115','Ke Xianxu','female','34','013','Jiao Dan','AB','Lumbar disc protrusion','203'),
('21116','Xialai','female','30','014','Zhang Ziyi','B','Electrolyte disorder','204'),
('21117','cuckoo','female','28','015','Li Juan','A','Endocrine disorder','204'),
('21118','Zhang Honghe','male','25','016','Guo Yiman','B','chronic periodontitis ','205'),
('21119','Chi muying','female','26','016','Liu Xiao','AB','Oral leukoplakia','205'),
('21120','Qi Fenxia','female','69','017','Wang Luming','O','cataract','301'),
('21121','Luquanxu','male','66','017','Zhang Zhen','A','glaucoma','301'),
('21122','Zhuo mutual knowledge','male','45','018','Han Ziqi','AB','tympanitis','302'),
('21123','Yuan Ganqi','male','16','019','Xuruohong','O','Flat Wart','303'),
('21124','Qin Changle','male','32','020','Lu Lei','B','tinea ','303'),
('21125','Qiu Cunxin','male','48','021','Li Li','O','Hemothorax','304'),
('21126','Lu Congfeng','male','18','022','Gao Tao','B','Pectus excavatum','304'),
('21127','Wang Hao','male','29','023','Ruoyu Chen','A','Foreign body in chest','305'),
('21128','Pan Yi','female','30','024','Liu Siyu','AB','Pleural effusion','305')
go
insert into Nurse table(Nurse number,full name,Gender,Age,Department number)
values('110101','Xia Yiliu','female','25','101'),
('110102','Xi Mengrong','female','28','101'),
('110103','Li Wenqian','female','22','102'),
('110104','Detour Zhanlin','male','24','102'),
('110105','Wu Shenmeng','female','29','103'),
('110106','Tian Tian','female','23','103'),
('110107','Gao Yueran','female','25','104'),
('110108','Liuzinuo','female','21','104'),
('110109','Zhang Yinuo','female','22','105'),
('110110','Zhao Jiayu','female','22','105'),
('110111','Zhang Yiming','female','35','201'),
('110112','Jiang Xinqin','female','26','201'),
('110113','Ma Hangyu','female','30','202'),
('110114','Su Zhixuan','female','25','202'),
('110115','Wang Xiaotian','female','24','203'),
('110116','Chenziyuan','female','26','203'),
('110117','Dong Xuan','female','21','204'),
('110118','Sun Yu','female','23','204'),
('110119','Li wanting','female','28','205'),
('110120','Song Yuhan','female','31','205'),
('110121','Deng Mengqi','female','24','301'),
('110122','Cui Jiayu','female','26','301'),
('110123','Guo Xingtong','female','24','302'),
('110124','Guo Siyu','female','23','302'),
('110125','Wang Yucheng','female','28','303'),
('110126','Liu Zilin','female','27','303'),
('110127','Li Yufei','female','29','304'),
('110128','Lu Jiachen','female','24','304'),
('110129','Zhang Xinyu','female','23','305'),
('110130','Liu Xinyue','female','27','305')
go
insert into Allocation table(Ward number,Nurse number)
values('001','110101'),
('002','110102'),
('003','110103'),
('004','110104'),
('005','110105'),
('006','110106'),
('007','110107'),
('007','110108'),
('008','110109'),
('009','110110'),
('010','110111'),
('011','110112'),
('012','110113'),
('012','110114'),
('013','110115'),
('013','110116'),
('014','110117'),
('015','110118'),
('016','110119'),
('016','110120'),
('017','110121'),
('017','110122'),
('018','110123'),
('018','110124'),
('019','110125'),
('020','110126'),
('021','110127'),
('022','110128'),
('023','110129'),
('024','110130')
go
At this time, the database has been established, and the basic operations of the database are as follows.
use Hospital information management system
insert into Patient table(Medical record number,full name,Gender,Age,Ward number,Name of doctor,blood type,Confirmed results)
values('21129','Zhang Gan','male','25','024','Liu Siyu','O','Pleural effusion')
go
delete
from Patient table
where Medical record number='21129'
go
update Nurse table
set Age='26'
where full name='Xia Yiliu'
go
select Medical record number,Patient table.full name,Patient table.Gender,Patient table.Age,Ward number,blood type,Name of doctor,title,Doctor table.Department number
from Patient table join Doctor table
on Patient table.Name of doctor=Doctor table.full name
where Confirmed results='Hydrocephalus'
go
select Department number,COUNT(Nurse number) as 'Number of nurses'
from Nurse table
group by Department number
go
select Department number,COUNT(Ward number) as 'Number of wards'
from Ward table
group by Department number
having(COUNT(Ward number))>1
go
select AVG(Age) as 'Average age of all doctors'
from Doctor table
go
select full name,Age
from Patient table
order by Age desc
go
select Nurse number,full name,YEAR(GETDATE())-Age as 'date of birth'
from Nurse table
go
select Nurse table.Nurse number,Ward number,full name
from Nurse table join Allocation table
on Nurse table.Nurse number=Allocation table.Nurse number
go
select Medical record number,Patient table.full name,Patient table.Gender,blood type,Ward number,Name of doctor,title,Doctor table.Department number,Department table.Department name,Department table.Department address
from Patient table join Doctor table
on Patient table.Name of doctor=Doctor table.full name
join Department table
on Doctor table.Department number=Department table.Department number
go
create view v_pyk
as
select Medical record number,Patient table.full name,Patient table.Gender,blood type,Ward number,Name of doctor,title,Doctor table.Department number,Department table.Department name,Department table.Department address
from Patient table join Doctor table
on Patient table.Name of doctor=Doctor table.full name
join Department table
on Doctor table.Department number=Department table.Department number
go
select *
from v_pyk
go
update v_pyk
set Ward number='021'
where Medical record number='21126'
go
create proc pr_inf @pr varchar(20)
as
select Patient table.full name,Patient table.Gender,blood type,Name of doctor,Doctor number,Confirmed results
from Patient table join Doctor table
on Patient table.Name of doctor=Doctor table.full name
where Patient table.full name like @pr
go
exec pr_inf 'Zhang%'
use Hospital information management system
go
alter proc hs_bf @hno char(6),@bno char(10) output ,@bed varchar(10) output
as
select @bno=Allocation table.Ward number,@bed=Number of beds
from Allocation table join Ward table
on Ward table.Ward number=Allocation table.Ward number
where Nurse number =@hno
set @bed=convert(varchar,@bed)
go
declare @bno1 char(10),@bed1 varchar(10)
exec hs_bf '110101',@bno1 output ,@bed1 output
print 'Ward number'+@bno1
print 'Number of beds'+@bed1
go
use Hospital information management system
go
alter trigger tri_br
on Patient table
after delete,update
as
declare @sno char(10) ,@cno char (10)
select @sno =Medical record number from deleted
select @cno=Ward number from deleted
delete from Patient table where Medical record number=@sno
update Ward table
set Number of beds=Number of beds+1
where @cno = Ward table.Ward number
go
delete from Patient table where Medical record number='21102'
go
The following is the entity diagram of each table
The following is the data flow diagram
The following is the E-R diagram of hospital management
The following is the functional structure of the hospital
1. Design tasks
1.1 overview of system development background
With the rapid development of computer technology and the popularization of computer application in hospital management, it is imperative to use computers to realize hospital management. For large and medium-sized hospitals, using computers to support hospitals to efficiently complete the daily affairs of labor hospital management is a necessary condition to meet the requirements of modern hospital management system and promote hospital management to be scientific and standardized.
At present, there are many popular information management systems on the market. However, for the hospital management system of medium-sized hospitals, there is no need for a large database system. It only needs a system with convenient operation and practical functions, which can meet the data management and needs of the center. Our goal is to develop a practical, easy to operate, simple and clear hospital management system. Be able to input the basic information of the hospital, and be able to complete the work such as adding, modifying, deleting, querying according to various conditions, setting of new users and password modification in operation, which basically meets the needs of daily personnel business. The team has completed the research and development of this topic, including analysis, design, coding, testing, documentation and so on.
1.2 purpose and significance of system development
With the development of modern society, in order to seize the opportunity and take the lead in the competition, as an essential and important part of enterprise management, the informatization and computerization of online management are also imperative. The development of online management information system is of macro significance, that is, to comply with the trend of informatization and modernization, improve efficiency, and promote the structural optimization of national economic management; It also has micro significance, that is, it can improve the modern procedures of management, strengthen the informatization means of management, improve work efficiency and increase unit benefits.
With the rapid development of computer and communication technology, human beings have gradually entered the information society. Information, like materials and energy, has become a basic means of production of society and plays an important role in human social production activities. At present, hospitals are also facing increasingly fierce market competition. If they want to win in the competition, they must also adopt advanced management methods and means. A well planned and advanced computer information management network system is a necessary means to win the competition. Through the realization of advanced computer network management, it can provide reliable digital basis for the management and decision-making of the leadership in time, and make the management more reasonable and advanced; Reduce the waste of human and material resources and reduce costs; Improve work efficiency and management efficiency; Improve economic benefits, so as to improve the overall competitiveness of the hospital. Establish a hospital information network management system centered on personnel management to meet the needs of the long-term and rapid development of the hospital. More importantly, it can make patients get more satisfactory treatment in the hospital.
2 demand analysis
2.1 information requirements
Figure 2-1 functional module diagram of hospital information system
This course is designed to simulate the information management of general hospitals and facilitate the query of hospital information. After sufficient research, it is determined that the system should manage the basic information of patients, departments, doctors, wards, nurses and the distribution of nurses. The main requirements of the system are:
- It can comprehensively manage the basic information of patients, departments, doctors, wards, nurses and nurses.
- This system can easily maintain all kinds of information.
- This system can easily query the basic information of each information table.
- It can facilitate the connection query and nested query of multiple information tables.
- This system can realize the query and statistics of useful information.
- This system can realize the output of useful information.
2.2 data flow diagram
Figure 2-2 data flow diagram of hospital information management
2.3 data dictionary
Data dictionary refers to the definition and description of five parts of data: data item, data structure, data flow, data storage, processing logic and external entity. Its purpose is to make a detailed description of each element in the data flow chart. This experiment mainly analyzes the data items of the data dictionary. As shown in Table 2:
Table 2 data items
Serial number
data item
data type
length
remarks
1
Doctor number
CHAR
6
Doctor's job number
2
full name
VARCHAR
20
Name of doctor
3
Gender
CHAR
2
Doctor's gender
4
Age
INT
Age of doctor
5
title
VARCHAR
20
Title of doctor
6
Department number
CHAR
3
Doctor's department number
7
Medical record number
CHAR
5
Patient's medical record number
8
full name
VARCHAR
20
Patient's name
9
Gender
CHAR
2
Gender of patient
10
Age
INT
Patient's age
11
Ward number
CHAR
3
Patient's ward number
13
blood type
VARCHAR
2
Patient's blood type
14
Department number
CHAR
3
Department of the patient
15
Confirmed results
VARCHAR
20
Patient's diagnostic report
16
Department number
CHAR
3
Department number
17
Department name
VARCHAR
20
Name of Department
18
Department address
VARCHAR
20
Address of Department
19
Department telephone
VARCHAR
10
Telephone number of the Department
20
Department Director
VARCHAR
20
Director of the Department
21
Ward number
CHAR
3
Ward number
22
Number of beds
INT
Number of beds in the ward
23
Department number
CHAR
3
Department of ward
24
Ward address
VARCHAR
20
Address of ward
25
Nurse number
CHAR
6
Nurse's number
26
full name
VARCHAR
20
Name of nurse
27
Gender
CHAR
2
Gender of nurse
28
Age
INT
Age of nurse
29
Department number
CHAR
3
Department of nurse
3 conceptual structure design
3.1 entity attributes of hospital information system
The physical attributes of hospital doctors mainly include doctor number, name, gender, age, department number and professional title. As shown in Figure 3-1 doctor entity attribute diagram:
Figure 3-1 doctor entity attribute diagram
The entity attributes of hospital departments, mainly including department number, department name, department address, department telephone, department director; As shown in Figure 3-2 Department entity attribute diagram:
Figure 3-3 ward entity attribute diagram
The entity attributes of hospital nurses mainly include nurse number, name, gender, age and department number. As shown in Figure 3-4 nurse entity attribute diagram:
Entity attribute map of hospital patients, mainly including medical record number, name, gender, age, diagnosis results, doctor name, ward number, blood type, department number. As shown in Figure 3-5 patient entity attribute diagram:
3.2 general E-R diagram
The general E-R diagram of hospital information management is shown in Figure 3-6:
4 logic structure design
4.1 relationship mode:
Doctor (Doctor number, name, gender, age, professional title, department number)
Patient (medical record number, name, gender, age, ward number, doctor's name, blood type, department number, diagnosis)
Department (department number, department name, department address, department telephone, department director)
Ward (ward number, bed number, department number, ward address)
Nurse (nurse number, name, gender, age, ward number)
5 physical structure design
5**.1 optimization of data relationship mode:******
For the registration of 1-to-N relationship, the department number attribute can be added to the patient mode.
For the 1-to-N relationship, you can add the department number attribute to the doctor mode.
For the ownership of 1-to-N relationship, the department number attribute can be added to the ward mode.
In this way, the optimized relationship mode is obtained:
Patient (medical record number, name, gender, age, ward number, doctor's name, blood type, diagnosis result, department number)
Doctor (Doctor number, name, gender, age, professional title, department number)
Ward (ward number, bed number, ward address, department number)
For the relationship pattern of N to M, a new pattern can be generated:
Assignment (ward number, nurse number)
For the optimized relationship mode:
Doctor (Doctor number, name, gender, age, professional title, department number)
Patient (medical record number, name, gender, age, ward number, doctor's name, blood type, diagnosis result, department number)
Department (department number, department name, department address, department telephone, department director)
Ward (ward number, bed number, ward address, department number)
Nurse (nurse number, name, gender, age, department number)
Assignment (ward number, nurse number)
Analysis shows that each relationship of the relational schema is an indivisible atomic value, that is, it is the first normal form. Because each non primary attribute does not transfer candidate keys that depend on the schema, this schema set is the third normal form.
The doctor table is obtained through the optimized relationship mode:
Table 4.1 basic information of doctors
Column name
data type
Field size
Is it empty
remarks
Doctor number
CHAR
6
NOT
Primary key
full name
VARCHAR
20
NOT
Gender
CHAR
2
NOT
Age
INT
NOT
title
VARCHAR
20
YES
Department number
CHAR
3
YES
Foreign key
The patient table is obtained through the optimized relationship mode:
Table 4.2 basic information of patients
Column name
data type
Field size
Is it empty
remarks
Medical record number
CHAR
5
NOT
Primary key
full name
VARCHAR
20
NOT
Gender
CHAR
2
NOT
Age
INT
NOT
Ward number
CHAR
3
YES
Name of doctor
VARCHAR
20
YES
blood type
CHAR
2
YES
Confirmed results
VARCHAR
20
YES
Department number
CHAR
3
YES
Foreign key
The Department table is obtained through the optimized relationship mode:
Table 4.3 basic information of departments
Column name
data type
Field size
Is it empty
remarks
Department number
CHAR
3
NOT
Primary key
Department name
VARCHAR
20
NOT
Department address
VARCHAR
20
YES
Department telephone
VARCHAR
10
YES
Department Director
VARCHAR
20
YES
The ward table is obtained through the optimized relationship mode:
Table 4.4 basic information of ward
Column name
data type
Field size
Is it empty
remarks
Ward number
CHAR
3
NOT
Primary key
Number of beds
INT
YES
Ward address
VARCHAR
20
YES
Department number
CHAR
3
YES
Foreign key
The nurse table is obtained through the optimized relationship mode:
Table 4.5 basic information of nurses
Column name
data type
Field size
Is it empty
remarks
Nurse number
CHAR
6
NOT
Primary key
full name
VARCHAR
20
NOT
Gender
CHAR
2
NOT
Age
INT
NOT
Department number
CHAR
3
YES
Foreign key
The distribution table is obtained through the optimized relationship mode:
Table 4.6 nurse allocation table
Column name
data type
Field size
Is it empty
remarks
Ward number
CHAR
3
NOT
Primary key, foreign key
Nurse number
CHAR
6
NOT
Primary key, foreign key
5.2 contents of design structure
Because users use the database through a specific DBMS, the physical structure design of the data must be combined with the specific DBMS, mainly including the selection of the storage structure and access method of the database.
5.2.1 determining the storage structure
The design of database physical structure is closely related to specific hardware environment, DBMS and implementation environment. The configuration of database is also an important content to determine the physical structure, including the allocation of database space, the size of log file, the determination of data dictionary space and the setting of relevant parameters.
5.2.2 select access method
Database access methods include indexing, clustering and other methods.
1. Index selection
Generally speaking, index attribute columns in the following cases:
Query frequent attribute columns.
Attribute columns that often appear in link operations.
Attribute columns in sentences such as WHERE, ORDER, GROUP BYD, etc.
Attribute columns that should not be indexed:
Attribute columns that do not appear or rarely appear in query criteria.
Columns with few attribute values.
Columns that need to be updated frequently.
Attribute columns of data tables that often need to be updated or contain fewer records.
2. Cluster selection
Clustering is another technology to improve system performance. Clustering can be divided into the following three cases:
Sub paragraph. Grouped by attributes, the files are decomposed vertically.
Partition. The files are horizontally decomposed and grouped according to the record access frequency.
Clustering. Take some attributes from different relationships and store them together physically to change the efficiency of connection query.
5.3 evaluation of physical structure
The physical structure meets the design requirements and has great efficiency in time and space. It can enter the database implementation stage. The physical structure design of the database needs to be repeatedly tested and continuously optimized.
6 database implementation
After completing the analysis and structural optimization, the implementation phase of the database begins. This chapter mainly focuses on the creation of the database, the addition, deletion, modification and query of data tables, the creation of views and the creation of stored procedures.
6.1 creation of database
createdatabase hospital information management system
on(
name= hospital information management system,
filename='D: database course design hospital information management system hospital information management system.mdf ',
size=5,
filegrowth=1
)
logon
(name= hospital information management system _log,
filename= 'D: database course design hospital information management system hospital information management system_ log.ldf’,
size=2,
maxsize=30,
filegrowth=10%
)
6.2 table creation
6.2.1 creation of department table
use hospital information management system
createtable department table
(department number char(3)notnullprimarykey,
Department name varchar(20)notnull,
Department address varchar(20)null,
Department phone varchar(10)null,
Department director varchar(10)null)
go
6.2.2 creation of doctor table
createtable doctor table
(Doctor No. char(6)notnullprimarykey,
Name varchar(20)notnullunique,
Gender char(2)notnull,
Age intnotnull,
Title varchar(20)null,
Department number char(3)null)
go
altertable doctor table
addconstraintys_zcdefault 'doctor' for professional title
go
altertable doctor table
addconstraintfk_ Ksforeignkey (department number) references department table (department number)
go
6.2.3 creation of patient table
createtable patient table
(medical record No. char(5)notnullprimarykey,
Name varchar(20)notnull,
Gender char(2)notnull,
Age intnotnull,
Ward number char(3)null,
Doctor name varchar(20)null,
Blood group char(2)null,
The diagnosis result varchar(20)null,
Department number char(3)null)
go
altertable patient table
addconstraintfk_ Kshforeignkey (department number) references department table (department number)
go
altertable patient table
addconstraintfk_ Bfhforeignkey (ward number) references ward table (ward number)
go
6.2.4 creation of ward table
createtable ward table
(ward No. char(3)notnullprimarykey,
Number of beds intnull,
Ward address varchar(20)null,
Department number char(3)null)
go
altertable ward table
addconstraintfk_ Skforeignkey (department number) references department table (department number)
go
6.2.5 creation of nurse form
createtable nurse table
(nurse No. char(6)notnullprimarykey,
Name varchar(20)notnull,
Gender char(2) notnull,
Age intnotnull,
Department number char(3)null)
go
altertable nurse form
addconstraintfk_ Skhforeignkey (department number) references department table (department number)
go
6.2.6 creation of allocation table
createtable allocation table
(ward No. char(3)notnull,
Nurse number char(6)notnull,
primarykey (ward number, nurse number),
ForeignKey (ward number) references ward table (ward number),
ForeignKey (nurse number) references nurse table (nurse number))
go
6.3 table data insertion
6.3.1 inserting data into department table
insertinto department table (department number, department name, department address, department telephone, department director)
values('101 ',' internal medicine ',' A1101 ',' A1-10001 ',' goofy '),
('102 ',' surgery ',' A1102 ',' A1-10002 ',' Li Xiang '),
('103 ',' Pediatrics', 'A1103', 'A1-10003', 'Deng Yang'),
('104 ',' gynecology ',' A1104 ',' A1-10004 ',' Liu Yu '),
('105 ',' neurology ',' A1105 ',' A1-10005 ',' Bai Hao '),
('201 ',' brain surgery ',' A1201 ',' A1-20001 ',' song Siqi '),
('202 ',' urology ',' A1202 ',' A1-20002 ',' Sun Yi '),
('203 ',' orthopaedics', 'A1203', 'A1-20003', 'yangzilong'),
('204 ',' endocrinology department ',' A1204 ',' A1-20004 ',' Zhang Ziyi '),
('205 ',' Department of Stomatology ',' A1205 ',' A1-20005 ',' Guo Yiman '),
('301 ',' ophthalmology ',' A1301 ',' A1-30001 ',' Wang Luming '),
('302 ',' otolaryngology ',' A1302 ',' A1-30002 ',' Han Ziqi '),
('303 ',' dermatology ',' A1303 ',' A1-30003 ',' Xu Ruohong '),
('304 ',' cardiac surgery ',' A1304 ',' A1-30004 ',' Li Li '),
('305 ',' thoracic surgery ',' A1305 ',' A1-30005 ',' Chen Ruoyu ')
Go
6.3.2 insert data in doctor table
insertinto doctor form (Doctor number, name, gender, age, professional title, department number)
values('101001 ',' Gao Fei ',' male ', 30,' chief physician ',' 101 '),
('101002 ',' Yang Lei ',' female ', 24,' Resident ',' 101 '),
('101003 ',' Qi Feng ',' male ', 27,' attending physician ',' 101 '),
('101004 ',' Li Xiang ',' female ', 45,' chief physician ',' 102 '),
('101005 ',' Zhu Zhu ',' female ', 30,' attending physician ',' 102 '),
('101006 ',' Lu Feng ',' male ', 35,' attending physician ',' 102 '),
('101007 ',' Deng Yang ',' male ', 30,' attending physician ',' 103 '),
('101008 ',' Cheng Yue ',' female ', 28,' associate attending physician ',' 103 '),
('101009 ',' Liu Yu ',' female ', 40,' associate chief physician ',' 104 '),
('101010 ',' Liu Ziyi ',' female ', 34,' attending physician ',' 104 '),
('101011 ',' Bai Hao ',' male ', 58,' chief physician ',' 105 '),
('101012 ',' Su Nan ',' female ', 32,' attending physician ',' 105 '),
('101013 ',' song Siqi ',' female ', 50,' chief physician ',' 201 '),
('101014 ',' Lu Daming ',' male ', 24,' Resident ',' 201 '),
('101015 ',' Sun Yi ',' female ', 33,' attending physician ',' 202 '),
('101016 ',' Liu Nan ',' male ', 33,' attending physician ',' 202 '),
('101017 ',' Yang Zilong ',' male ', 35,' attending physician ',' 203 '),
('101018 ',' Jiao Dan ',' female ', 23,' Resident ',' 203 '),
('101019 ',' Zhang Ziyi ',' female ', 38,' associate chief physician ',' 204 '),
('101020 ',' Li Juan ',' female ', 32,' attending physician ',' 204 '),
('101021 ',' Guo Yiman ',' female ', 35,' attending physician ',' 205 '),
('101022 ',' Liu Xiao ',' male ', 22,' Resident ',' 205 '),
('101023 ',' Wang Luming ',' male ', 36,' attending physician ',' 301 '),
('101024 ',' Zhang Zhen ',' female ', 29,' associate attending physician ',' 301 '),
('101025 ',' Han Ziqi ',' male ', 31,' attending physician ',' 302 '),
('101026 ',' Qiao Fang ',' female ', 31,' attending physician ',' 302 '),
('101027 ',' Xu Ruohong ',' male ', 49,' chief physician ',' 303 '),
('101028 ',' Lu Lei ',' male ', 31,' attending physician ',' 303 '),
('101029 ',' Li Li ',' female ', 51,' chief physician ',' 304 '),
('101030 ',' Gao Tao ',' male ', 31,' attending physician ',' 304 '),
('101031 ',' Chen Ruoyu ',' female ', 39,' associate chief physician ',' 305 '),
('101032 ',' Liu Siyu ',' female ', 30,' attending physician ',' 305 ')
Go
6.3.3 ward table insert data
insertinto ward table (ward number, number of beds, ward address, department number)
values('001',4,'B1001','101'),
('002',5,'B1002','101'),
('003',1,'B1003','102'),
('004',2,'B1004','102'),
('005',0,'B1005','103'),
('006',3,'B1006','103'),
('007',2,'B1007','104'),
('008',3,'B1008','105'),
('009',1,'B1009','105'),
('010',2,'B1010','201'),
('011',1,'B1011','201'),
('012',3,'B1012','202'),
('013',2,'B1013','203'),
('014',2,'B1014','204'),
('015',1,'B1015','204'),
('016',5,'B1016','205'),
('017',2,'B1017','301'),
('018',1,'B1018','302'),
('019',3,'B1019','303'),
('020',1,'B1020','303'),
('021',2,'B1021','304'),
('022',1,'B1022','304'),
('023',3,'B1023','305'),
('024',2,'B1024','305')
go
6.3.4 patient table insert data
insertinto patient table (medical record number, name, gender, age, ward number, doctor's name, blood type, diagnosis result, department number)
values('21101 ',' Wang Shengan ',' male ', 31,' 001 ',' Qi Feng ',' A ',' pneumonia ',' 101 '),
('21102 ',' Cai Zhuangbao ',' male ', 54,' 002 ',' Gao Fei ',' B ',' chronic tracheitis', '101'),
('21103', 'Yi Jiangwei', 'male', 28, '003', 'Lu Feng', 'O', 'spontaneous pneumothorax', '102'),
('21104 ',' Zhang shungu ',' male ', 32,' 004 ',' Zhu Zhu ',' AB ',' biliary calculi ',' 102 '),
('21105 ',' Li Xinhao ',' male ', 8,' 006 ',' Deng Yang ',' A ',' bacillary dysentery ',' 103 '),
('21106 ',' Liang Chengjing ',' female ', 8,' 006 ',' Cheng Yue ',' A ',' noru diarrhea ',' 103 '),
('21107 ',' Shi dangshu ',' female ', 26,' 007 ',' Liu Yu ',' B ',' cervicitis', '104'),
('21108 ',' he Gangming ',' male ', 66,' 008 ',' Bai Hao ',' O ',' cerebral infarction ',' 105 '),
('21109 ',' Yan Xihua ',' male ', 58,' 008 ',' Su Nan ',' B ',' cerebral hemorrhage ',' 105 '),
('21110 ',' Liu Xianfa ',' male ', 32,' 010 ',' song Siqi ',' A ',' hydrocephalus', '201'),
('21111 ',' He Yi ',' male ', 35,' 010 ',' Lu Daming ',' O ',' brain abscess', '201'),
('21112 ',' Tang Chenyu ',' male ', 38,' 011 ',' song Siqi ',' B ',' craniocerebral trauma ',' 201 '),
('21113 ',' Zhang Shunlian ',' male ', 50,' 012 ',' Liu Nan ',' A ',' urolithiasis', '202'),
('21114 ',' Tang Qing ',' female ', 29,' 012 ',' Sun Yi ',' B ',' acute nephritis', '202'),
('21115 ',' Ke Xianxu ',' female ', 34,' 013 ',' Jiao Dan ',' AB ',' prominent waist disc ',' 203 '),
('21116 ',' Xia Lai ',' female ', 30,' 014 ',' Zhang Ziyi ',' B ',' electrolyte disorder ',' 204 '),
('21117 ',' cuckoo ',' female ', 28,' 015 ',' Li Juan ',' A ',' endocrine disorder ',' 204 '),
('21118 ',' Zhang Honghe ',' male ', 25,' 016 ',' Guo Yiman ',' B ',' chronic periodontitis', '205'),
('21119 ',' Chi muying ',' female ', 26,' 016 ',' Liu Xiao ',' AB ',' oral leukoplakia ',' 205 '),
('21120 ',' Qi Fenxia ',' female ', 69,' 017 ',' Wang Luming ',' O ',' cataract ',' 301 '),
('21121 ',' Lu Quanxu ',' male ', 66,' 017 ',' Zhang Zhen ',' A ',' glaucoma ',' 301 '),
('21122 ',' Zhuo Huzhi ',' male ', 45,' 018 ',' Han Ziqi ',' AB ',' otitis media ',' 302 '),
('21123 ',' yuan Ganqi ',' male ', 16,' 019 ',' Xu Ruohong ',' O ',' verruca plana ',' 303 '),
('21124 ',' Qin Changle ',' male ', 32,' 020 ',' Lu Lei ',' B ',' tinea cutis', '303'),
('21125 ',' Qiu Cunxin ',' male ', 48,' 021 ',' Li Li ',' O ',' hemothorax ',' 304 '),
('21126 ',' Lu Congfeng ',' male ', 18,' 022 ',' Gao Tao ',' B ',' funnel chest ',' 304 '),
('21127 ',' Wang Hao ',' male ', 29,' 023 ',' Chen Ruoyu ',' A ',' chest foreign body ',' 305 '),
('21128 ',' Pan Yi ',' female ', 30,' 024 ',' Liu Siyu ',' AB ',' pleural effusion ',' 305 ')
Go
6.3.5 nurse table insert data
insertinto nurse form (nurse number, name, gender, age, department number)
values('110101 ',' Xia Yiliu ',' female ', 25,' 101 '),
('110102 ',' Xi Mengrong ',' female ', 28,' 101 '),
('110103 ',' liwenqian ',' female ', 22,' 102 '),
('110104 ',' Rao Zhan Lin ',' male ', 24,' 102 '),
('110105 ',' Wu Shenmeng ',' female ', 29,' 103 '),
('110106 ',' Tian Tian ',' female ', 23,' 103 '),
('110107 ',' Gao Yueran ',' female ', 25,' 104 '),
('110108 ',' Liu ZiNuo ',' female ', 21,' 104 '),
('110109 ',' Zhang Yinuo ',' female ', 22,' 105 '),
('110110 ',' Zhao Jiayu ',' female ', 22,' 105 '),
('110111 ',' Zhang Yiming ',' female ', 35,' 201 '),
('110112 ',' Jiang Xinqin ',' female ', 26,' 201 '),
('110113 ',' ma Hangyu ',' female ', 30,' 202 '),
('110114 ',' Su Zhixuan ',' female ', 25,' 202 '),
('110115 ',' Wang Xiaotian ',' female ', 24,' 203 '),
('110116 ',' chenziyuan ',' female ', 26,' 203 '),
('110117 ',' Dong Xuan ',' female ', 21,' 204 '),
('110118 ',' Sun Yu ',' female ', 23,' 204 '),
('110119 ',' Li wanting ',' female ', 28,' 205 '),
('110120 ',' song Yuhan ',' female ', 31,' 205 '),
('110121 ',' Deng Mengqi ',' female ', 24,' 301 '),
('110122 ',' Cui Jiayu ',' female ', 26,' 301 '),
('110123 ',' Guo Xingtong ',' female ', 24,' 302 '),
('110124 ',' guosiyu ',' female ', 23,' 302 '),
('110125 ',' Wang Yu Orange ',' female ', 28,' 303 '),
('110126 ',' Liu Zilin ',' female ', 27,' 303 '),
('110127 ',' Li Yufei ',' female ', 29,' 304 '),
('110128 ',' Lu Jiachen ',' female ', 24,' 304 '),
('110129 ',' Zhang Xinyu ',' female ', 23,' 305 '),
('110130 ',' Liu Xinyue ',' female ', 27,' 305 ')
Go
6.3.6 inserting data into allocation table
insertinto allocation form (ward number, nurse number)
values('001','110101'),
('002','110102'),
('003','110103'),
('004','110104'),
('005','110105'),
('006','110106'),
('007','110107'),
('007','110108'),
('008','110109'),
('009','110110'),
('010','110111'),
('011','110112'),
('012','110113'),
('012','110114'),
('013','110115'),
('013','110116'),
('014','110117'),
('015','110118'),
('016','110119'),
('016','110120'),
('017','110121'),
('017','110122'),
('018','110123'),
('018','110124'),
('019','110125'),
('020','110126'),
('021','110127'),
('022','110128'),
('023','110129'),
('024','110130')
Go
7 database operation
7.1 data table query
7.1.1 department table data query
go
select*
from department table
go
7.1.2 doctor table data query
go
select*
from doctor table
go
7.1.3 intra table connection query of doctors and departments
select doctor table. Name, department name
from doctor table join department table
on doctor table. Department number = department table. Department number
go
7.1.4 insert a message into the patient table
('21129 ',' Zhang Gan ',' male ',' 25 ',' 024 ',' Liu Siyu ',' O ',' pleural effusion ').
use hospital information management system
insertinto patient table (medical record number, name, gender, age, ward number, doctor's name, blood type, diagnosis result)
values('21129 ',' Zhang Gan ',' male ',' 25 ',' 024 ',' Liu Siyu ',' O ',' pleural effusion ')
go
7.1.5 delete the last record in the patient table
('21129 ',' Zhang Gan ',' male ',' 25 ',' 024 ',' Liu Siyu ',' O ',' pleural effusion ').
go
delete
from patient table
where medical record number = '21129'
go
7.1.6 revise the age of Xia Yiliu in the nurse table to 26.
update nurse form
set age = '26'
where name = 'Xia Yiliu'
go
7.1.7 query patients with hydrocephalus
His medical record number, name, gender, age, ward number, blood type, and the corresponding doctor's name, professional title, and department number.
select medical record number, patient table. Name, patient table. Gender, patient table. Age, ward number, blood type, doctor name, professional title, doctor table, department number
from patient table join doctor table
on patient table. Doctor name = doctor table. Name
where diagnosis result = 'hydrocephalus'
go
7.1.8 query the number of nurses in each department.
select department number, count (nurse number) as' number of nurses'
from nurse form
groupby department number
7.1.9 query departments with more than one ward.
select department number, count (ward number) as' number of wards'
from ward table
groupby department number
Having (count)) >1
go
7.1.10 inquire the average age of all doctors.
Selectavg (age) as' average age of all doctors'
from doctor table
go
7.1.11 query the age of patients and arrange them in descending order.
select name, age
from patient table
orderby age desc
go
7.1.12 inquire the date of birth of the nurse.
select nurse number, name, YEAR(GETDATE()) - age as' date of birth '
from nurse form
go
7.1.13 inquire the ward number of each nurse.
select nurse form. Nurse number, ward number, name
from nurse table join allocation table
on nurse table. Nurse number = assignment table. Nurse number
go
7.1.14 query the patient's corresponding medical record number
His name, gender, blood type, ward number, doctor's name, professional title, department number, department name, department address.
select medical record number, patient table. Name, patient table. Gender, blood type, ward number, doctor name, professional title, doctor table. Department number, department table. Department name, department table. Department address
from patient table join doctor table
on patient table. Doctor name = doctor table. Name
join department table
on doctor table. Department number = department table. Department number
go
7.1.15 create a view v_pyk
Query the patient's corresponding medical record number, name, gender, blood type, ward number, doctor's name, professional title, department number, department name and department address.
createviewv_pyk
as
select medical record number, patient table. Name, patient table. Gender, blood type, ward number, doctor name, professional title, doctor table. Department number, department table. Department name, department table. Department address
from patient table join doctor table
on patient table. Doctor name = doctor table. Name
join department table
on doctor table. Department number = department table. Department number
go
select*
fromv_pyk
go
7.1.16 place view v_ Data change in PYK
Change the ward number of the patient whose medical record number is 21126 to 021
updatev_pyk
set ward number = '021'
where medical record number = '21126'
go
7.1.17 create a stored procedure pr_inf
Output the name, sex, blood type, attending doctor, doctor number and diagnosis result of the specified patient.
createprocpr_inf@prvarchar(20)
as
select patient table. Name, patient table. Gender, blood type, doctor name, doctor number, diagnosis result
from patient table join doctor table
on patient table. Doctor name = doctor table. Name
where patient form. Name like@pr
go
execpr_inf 'Zhang%'
7.1.18 create a stored procedure hs_bf
Enter a nurse number, and output the ward number corresponding to the nurse and the number of remaining beds.
createprochs_bf@hnochar(6),@bnochar(10)output,@bedvarchar(10)output
as
select@bno =Allocation table. Ward number, @bed= number of beds
from allocation table join ward table
on ward table. Ward number = allocation table. Ward number
where nurse number = @hno
set@bed=convert(varchar,@bed)
go
declare@bno1char(10),@bed1varchar(10)
exechs_bf'110101',@bno1output,@bed1output
print 'ward number' +@bno1
print 'number of beds' +@bed1
go
7.1.19 create a trigger
When deleting a record in the patient table, the number of beds in the corresponding ward table should be updated in time.
createtriggertri_br
on patient table
insteadofdelete,update
as
declare@snochar(10),@cnochar (10)
select@sno =Medical record number fromdeleted
select@cno =Ward number fromdeleted
deletefrom patient table where medical record number =@sno
update ward table
set number of beds = number of beds +1
where@cno =Ward table. Ward number
go
deletefrom patient table where medical record number = '21102'
go
First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials