Unit 10 – Databases

Name - David Chebanov (70252678)

Intended Purpose and User Requirement

Intended Purpose (from the scenario)

The purpose of the new appointment system is to modernize the existing paper and
spreadsheet-based systems used by the doctors' surgery. It aims to streamline the process of
storing and managing information about patients and appointments. The upgrade is prompted
by an upcoming reorganization of health services, which will result in an increased number of
patients and doctors. The initial focus is on improving the appointment system to allow staff to
adapt to new technology gradually and minimize risks to patient safety.

Requirements (what the database will achieve)

1. Efficient in appointment booking.

2. Accessible by all the users.
3. Patient data Secured with regular backups.
4. User-friendly interface.
5. Reporting capabilities.

Hardware, Software and Other Resources

Hardware Software
iMac
Apple M3 chip Apple
8-core CPU
8- or 10-core GPU
8GB to 24GB unified memory
256GB to 2TB storage8
4.5K Retina display11
218 ppi
Heade of office
Inspiron Small Desktop
Windows 11 Home, English, Dutch, French,
13th Gen Intel® Core™ i5-13400 (20 MB
German, Italian
cache, 10 cores, 16 threads, 2.50 GHz to
4.60 GHz Turbo)
Office staff-15 IT staff-2
Inspiron 24 All-in-One
13th Gen Intel® Core™ i5-1335U (12 MB cache,
Windows 11
10 cores, 12 threads, up to 4.60 GHz Turbo)
Receptionist-1
HPE ProLiant DL380 Gen10 Server Windows 10
Processors 2 x Intel Xeon Gold 5120 2.2GHz
Fourteen-Core CPUs
Memory 128GB (4x32GB) 2400MHz DDR4
 RAM
Backplane 8SFF Backplane for up to 8 x 2.5"
SAS or SATA Hot-Swap Drives 8SFF
Backplane for up to 8 x 2.5" NVMe Hot-
Swap Drives Storage 2 x 300GB SAS 15k
2.5" 6Gbps HP Hot-Swap HDDs
Storage Controller HPE Smart Array
E208i-a Modular RAID Controller
RAID Levels: 0, 1, 5, 10
Networking Embedded HPE 331i 1Gbps
Quad Port RJ-45 Network Integrated Card
(4xPorts)
Additional FlexibleLOM Network Cards Sold
Separately
Power Supply Dual Redundant 500W Hot-
Swap Power Supplies (2xPSUs)
Remote Access Integrated Lights Out
(iLO 5) Standard Installed
Rack Rails Sliding Rack Rails Included
Bezel Bezel Sold Separately
Chassis Type 2U Rackmount
HP Officejet Pro 7720 A3 Colour Inkjet 4-in-
For printing documents
1 Printer with Wireless Printing
TP-Link 24-Port Gigabit Ethernet Switch,
To connect internet and server to the
Rackmount/Desktop, Steel Case (TL-
computers
SG1024D)
Cisco C819G-4G-VZ-K9 12.50Mb/s 4 x LAN 2
To connect to the internet wirelessly
Antennas Wireless Router

Justification for Hardware and Software

Components Why is it Needed

19Computer system with Windows 11, For work, to access and store data
Apple-1 Apple Computer
Server-1 to store data and get data form.

Wireless Access Point-3 to cover all the Belding

Switch-1 with 25 connectors To connect to the severs and get faster

transferring speed
Printers-3 To have easy throughout the building

Tablets-20 To have space where can work on

computers
Software list - Access, Word To access the data base and edit the
documents
Database Design (Sample records)
Record for a patient.
Patient First Last Gender Date of Phone Address Post
ID Name Name birth number Code

40 Aadit Yasmeen F 16/02/2001 02061 159 BV2

987847 Pleasant 2CK
Pioneer
Pike

Record for a Doctor

Doctor ID First name Last Name Consultation Room

50 Allan Johnson 4

Record for an appointment.

Appointment Date Time Doctor ID Patient ID
ID

612 31/12/2018 08:50 40 50

Tables & Attributes

Appointment Table

Field Name Field Type Attributes & Size Example

Appointment ID Numbers 999 164

Patient ID Numbers 999 154

Doctor ID Numbers 999 308

 Date of Appointment Date/Time 31/13/9999 20/05/2000

Time of Appointment Date/Time 31/13/9999 04/10/2001

Doctor Table
Field Name Field Type Attributes & Size Example

Doctors ID Numbers 999 50

First Name Short Text A/Z Arjun

Last Name Short Text A/Z Patel

Consultation Room Numbers 99 13

Patient Table

Field Name Field Type Attributes & Size Example

Patient ID Numbers Primary key/9999 1026

First Name Short Text A/Z David

Last Name Short Text A/Z Qiao

Gender Short Text M/F M

Date of Birth Date/Time 32/13/9999 10/07/1999

1073 North Bend

Address Short Text A/Z - 1-9
River Road
Post Code Short Text LL99LL SE2 7OP

Telephone Number Short Text 99999 999999 606-260-8753

Entity Relationship Diagram

 Validation and Verification
Appointment Table

Field Name Field Type Validation Rule Validation Text

Is Null Or Not Like

Appointment ID Number "*[!0-9]*" Please enter ID!

Is Null Or Not Like

Patient ID Number "*[!0-9]*" Please enter ID!

Is Null Or Not Like

Doctor ID Number "*[!0-9]*" Please enter ID!

Date of Appointment Date/Time Is Null Or Not Like Enter Time!

"*[!0-9]*"

Time of Appointment Date/Time Is Null Or Not Like

"*[!0-9]*" Enter Time!

Doctor Table

Field Name Field Type Validation Rule Validation Text

Is Null Or Not Like Sate a Primary key

Doctor ID Number "*[!0-9]*" Number!
 Surname Short Text Is Null Or Not Like Please enter your
"*[!a-z]*" First Name!

Forename Short Text Is Null Or Not Like Please enter your

"*[!a-z]*" Last Name

Is Null Or Not Like Please enter Rooms

Room Number "*[!0-9]*" Number!

Patient Table

Field Name Field Type Validation Rule Validation Text

Is Null Or Not Like Sate a Primary key

Patient ID Number "*[!a-z]*" Number!

First Name Short Text Is Null Or Not Like State your First
"*[!a-z]*" name!

Last Name Short Text Is Null Or Not Like State your Last
"*[!a-z]*" name!

Gender Short Text Is Null Or "M" Or Maley or Female

"F" please fell the box.

Address Short Text "Is Not Null" State your address!

Enter a valid post

Post Code Short Text "Is Not Null" code!

Date Of Birth Date/Time Please sate a

Date/Time

Is Not Null

Telephone Number Short Text Please state your

Phone Number!
Is Null Or Not Like
"*[!0-9]*"
Alternative Design Ideas

Input and Output Screens

Output Screen - Record

This is the logo I will be using for my

database. It aims to convey professionalism,
clarity of purpose, and attract users through
its distinctive and visually appealing design.
 This banner is simple and understandable. It shows that it NHS Surgeons small clinic.

It has simple design to represent government official clinic.

Queries

Name of Query Purpose of Query

Dr Allan Johnson To see patients seen by Dr Allan Johnson

Dr Arjun Patel To see patients seen by Dr Arjun Patel

Dr Samuel Obiekwe To see patients seen by Dr Samuel Obiekwe

Dr Antonio patients sorted by G Patients seen by Dr Antonio whose surnames starts with G

Maile group sorted by C Patients who are Males whose surnames starts with C

Patients who are Males and are older than 10 years

Maile group older than 10

11/01/2019 Patients that were seen on 11/01/2019

 24/01/2019 - 03/02/2019 Patients that were seen on 24/01/2019 and 03/02/2019

Maile group older than 25

Patients who are Males and are older than 25 years

Patients sorted by M
Patients whose surnames starts with M

Dr Johnson and Dr Patel Patients

Patients seen by both Dr Johnson and Dr Patel

Female group aged 20 or less

Patients who are Females and aged 20 or less

Menu System

Main Menu
➢ Appointments menu
➢ Doctor menu
➢ Patient menu
➢ Quit

Appointments Menu
➢ Create appointment.
➢ View appointment
➢ Cancel appointment.
➢ View today's appointments
➢ Return to main menu.

Doctors Menu
➢ Add doctor.
➢ Delete doctor.
➢ View all doctors.
➢ Return to main menu.
 Patient Menu
➢ Add patient.
➢ Delete patient.
➢ Patients sorted by age.
➢ Patients aged 18-30
➢ Patients aged 18-65
➢ Patients over 35
➢ Patients under 18
➢ Return to main menu.

Test Plan & Test Data

Testing software and navigation

Appointment Table

Field Name Incorrect Correct Data Expected Result

Data

Appointments A-Z Number of Only numbers accepted, ID inserted in ascending order

ID ID (e.g. 35)

Date
Blank or DD/MM/YY Only numbers accepted, value must be inserted, a valid
invalid date YY date of appointment

Time 9:79 00:00 Only numbers accepted, value must be inserted, a valid
time of appointment

Patient ID A-Z Number of Only numbers accepted, patient ID inserted

ID

Doctors ID A-Z Number of Only numbers accepted, doctor ID inserted

ID

Doctor Table
Field Name Incorrect Data Correct Data Expected Result

Doctor ID Blank, A-Z or Number of ID (e.g.

symbols 35) Only numbers accepted, ID
inserted in ascending order

First Name Blank, numbers or A-Z (e.g. Nikola) Field should not be empty
 symbols
Last Name Blank, numbers or A-Z (e.g. Reedtz)
symbols
Field should not be empty
Consultation Room Non-existent room Room number (e.g. Only numbers accepted, room
number 17) number inserted in ascending
order
Switchboard Navigation

Test Number Test Expected Result

1 Appointments menu Will navigate to appointment menu

2 Doctors’ menu Will navigate to doctor menu

3 Patient menu Will navigate to patient menu

4 Quit Will shut down the database

Patient Table

Field Name Incorrect Data Correct Data Expected Result

Patient ID Blank, A-Z or Number of ID (e.g. Only numbers accepted, ID

symbols 35) inserted in ascending order

First Name Blank, numbers or A-Z (e.g. Alex)

symbols
Field should not be empty

Last Name Blank, numbers or A-Z (e.g. Reedtz)

symbols
Field should not be empty

Gender Blank, numbers or M/F

Symbols, any letter Only M or F accepted, value
Except “M” or “F” must be inserted, a valid phone
number

Date of Birth Invalid date DD/MM/YYYY Only numbers accepted, value

must be inserted, a valid DOB
 Value must be inserted, a valid
Address Invalid address A/Z - 1-9 address written in

Post Code Invalid postcode AB1 2CD Value must be inserted, a valid
post code

Phone number Invalid phone (9999) 00090009 Only numbers accepted, value
number inserted, a valid phone number

Patient Table

PatientID First Name Last Name Gender Date of Birth Address Post Code Phone

40 Aaditt Yasmeen M 16/02/2001 159 Pleasant BV2 2CK 02061 987847

Pioneer Pike

41 Adam Copker M 23/09/1998 119 Devon Way BV8 3ZE 02084 987541

42 Ajoy Carr M 25/11/1986 32 Edward Street BV0 1UH 02086 987547

43 Ann Pims F 03/09/1979 162 Brandon Road BV6 8WZ 02081 124578

44 Beau Lytte M 19/02/1956 114 Albert Avenue BV0 6DJ 02079 987544

45 Brian Pryce M 29/07/1996 341 Pemberley BV9 4EZ 02064 981111

Road

46 Cedric Huhn M 21/01/1971 126 Vane Avenue BV1 3UN 02087 987111

47 Clara Cuth F 07/09/1970 232 Kenil Gardens BV1 2ZT 02075 987522

Design Justification
The alternative design wasn’t used because it not as good as the current one.

Constraints

There are some constraints with the use of my database, one of them being the skill of the
staff.