Hospital management system database, course design, SQLserver, pure code design

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

Tags: Back-end Front-end Android Interview

Posted by MystedVeil on Mon, 15 Aug 2022 01:44:48 +0930