Database project assignment

profileadi_6294
database_project_sample.docx
Home>Computer Science homework help>Database project assignment

[Database assignment is1060]

UOL Student Number: 090404702

Introduction

Mable Skin Clinic has 5 different branches within the City of Singapore. It offers medical treatment and consultation to the public on various skin illnesses. Its popularity has grown its customer base in Singapore. Mable’s 5 branches that are located in various parts of Singapore handle tour enquiries and bookings from customers.

Each branch is assigned with two skin specialist doctors. Besides treating normal skin diseases these specialists hold different field of expertise. Customers usually make bookings on the specialists that they deem appropriate to their skin illness.

Adding value to a company’s customer service by providing the necessary information and instruction prior to a product or service purchase is essential. The purpose of my report is to showcase how a database management system is able to help a Mable Skin Clinic use the data captured from its patients to better serve them and also help the clinics to better manage and plan the medical appointment schedules better.

Problem & Concern

There are some voices of concerned within the Mable’s management that there is an increase in internal operational problems and complaints from patients. The problems concerned are listed below;

· Increase in the number of patients that cancelled their medical appointment that they have previously made online at the very last minute. This has certainly caused a disruption and wastage of resources as the doctors and specialists were unproductive during that appointment slot where the booking has been cancelled.

· Customer complaints that the instruction and information prior to the medical appointment are inaccurate.

· Employees do not have any reports to refer to.

· Management have problems tracking the customer’s data.

Mable skin clinic operates in 5 different locations in the city. They are as follows;

1. Woodlands Branch

2. Bishan Branch

3. Jurong Branch

4. Orchard Branch

5. Tampines Branch

Data Analysis

Naming Data Items

Full Name

Assigned Name

Appointment No

Appt No

Clinic No

ClinicNo

Appointment Date

ApptDate

Appointment Time

ApptTime

Consultation Fee Only

ConsultationFee

Specialist No

SpecialistNo

Specialist Name

SpecialistName

Expertise

Expertise

Specialist Telephone No

SpecialistTelNo

Clinic Name

ClinicName

Room No

RoomNo

Booking No

BKNo

Booking Date

BKDate

Booking Time

BKTime

Booking Instructions

BKInst

Customer No

CustNo

Customer Name

CustName

Customer Address

CustAddr

Customer Telephone No

CustTelNo

Entity Types & Named Data Items

APPOINMENT ( ApptNo , ApptDate,ApptTime,ConsultationsFee, [ClinicNo])

Note: Different appointments may be held at the same clinic. [] represents a repeating group

BOOKING ( BKNo , CustNo , ApptNo, BKDate,BKTime,BKInst,)

CLINIC ( ClinicNo , RoomNo ,ClinicName)

Note: Each clinics holds two unique room no.

CUSTOMER ( CustNo , CustName, CustAddr, CustTelNo)

SPECIALIST ( SpecsNo , ClinicNo , Expertise,SpecsName,SpecsTelNo)

Notes: Each specialist will be assigned to a specific clinic.

Normalised Entity Relationship Diagram (ERD)

Place

BKNo

BOOKING

CUSTOMER

CustNo

Cater For

Assign-to

Consultation-by

SPECIALIST

SpecsNo

CLINIC

APPOINMENT

ClinicNo

ApptNo

Notes:

· CUSTOMER may place one or many BOOKING. A BOOKING must be placed by one and only one CUSTOMER.

· An APPOINMENT may cater one or many BOOKINGs. A BOOKING must be catered by one and only one APPOINMENT.

· CLINIC may have an assignment of one or many APPOINMENTs. An APPOINMENT must be assign to one and only one CLINIC.

· A SPECIALIST may provide consultation to one or many APPOINTMENTs. An APPOINTMENT may receive consultation from one or many SPECIALISTs.

Tables

Total of 5 tables in the database have been created base on the entity types. Below is the Customer table shown on Datasheet View. A Primary key that hold unique datas from the entity type.

It is through the design view that this selection of primary key can be amended if required. The field names are design in order to minimise any data misunderstandings throughout the analysis and design process. When the identification is made easy, the consistency of accuracy is maintained.

Queries

The query tool allows user to extract the specific data or information that follows a certain requirements. Below is an example of a Booking List Query.

Enter clinic number that user want to query on.

The ‘”All Access Objects” allows user to conveniently open table, do queries, use forms and show report request.

Enter appointment date that user wants to query on.

Access will then generate the specific data that user wants to extract, as per below examples of Booking List Query & Specialist Doctor Details Query.

Switching to the design view, allows user to see the relationship between the different entities with the help of the normalize ERD. Base on the specific query requirements set, user could customise the required data to suit the query request.

Forms

A form enables users to control the type of information that were entered into a table and protects existing data from any accidental error from the user. Below shows a Customer Booking Entry Form that has been design to allow user to key in the data received from customers into the required fields easily. It also allows user to keep track of the records easily, and therefore make amendments to any data more convenient.

Reports

The aims of the database report almost have the same objective of a Query where user could extract the required information but unlike query where the information is portrayed in a raw tabular format. Reports in this case allows the generation of compiled information in a more pleasing format that can be read on screen or for the purpose of printing and distribution to more than one user.

An example of a Booking List Report and Customer Report are shown below, where the system made available the date of printing, page summary and summation of figures if any.

Conclusion

Database system eliminates heavy usage of paperwork. A simple query enables the user to generate information that it specifically request. Mable Skin Clinic could better manage the tracking of customer data with database system implementation as it contributes to a more efficient workflow rather than the paper files.

Internally, database system will aid Mable skin clinic in communication better, especially when the clinic have 5 branches spread across the city. Faster report generation allows employees to be more proactive and this could initiate courtesy reminder calls to patients to confirm on their appointment bookings. For larger companies with lager pool of customer base, more sophisticated database system may be required. Database projects are not always straight forward as I have experienced, it involves careful planning and understand the requirement needs of the business function, even then the data analysis have to be carefully look at to create an accurate entity relationship.

Page 7 of 7