Petcare Shop Database Management System Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Petcare is a medium sized veterinary surgery with six branches across London and there have many important data in each branch, we are already discovered getting them to work to each other on different database. Every data is part of company asset, therefore we are going to design a database system to handle the records of the Animals, Prescriptions, Appointments and Drug are one of care is needed, furthermore Doctors and Customer information also on our focus. Microsoft Access is most popular database management system that we base on it to build up database, why we use Microsoft Access, because it has more advantage on creation and management, it can designed for use on user level computer and provided facilities for creating of Tables, Queries, Forms and Reports, also It can be easily used to create our database and much easily sharing database, so this is good to fulfill our requirement.

The database system also can allow you to add, edit or remove data from each branch, the database is a location where you can store information related to customer pats. We will intend to use an Access database and a custom program to create and manage our data. A database allows you to store information related to a different category included branch, doctor, customer, animal type and breed, also you can store appointment record, prescription record and drug. In addition to database in Assess, you can also sort, extract, and summarize information and design custom report related to the data, Microsoft Office Access database creation and management program.

At the beginning, we will design an informal manner the idea of database as abstract machine, some of the major components will be develop in database system, and also we will consider the database infrastructure and how to organize. For design an informal of database infrastructure that we have to consolidate the network and relational database from each branch, it will be understand what data or components for design on Database system. To layout the concept of informal design, we will use ERD to present infrastructure of Database System that it can help us to understand what components and data of input we need in system for establish Tables at later on.

Entity Relationship Data Model

An Entity Relationship Diagram (ERD) will display a draft of data structures, in here (Figure 1.1) that it will show entities in a database system and relationships in tables. The diagram that we will provide the database concepts, DBMS and data model, in Tables can make us understand the database actually works with all interactions and data flows, and also it can display entities and relationships.

Figure 1.1 Entity Relationship Data Model (please see detail in Appendix)

In Entity Relationship Data Model, we have design nine tables with relationship for our data, there have different category in every table, in every table there will have many data inside. The diagram Figure 1.2 which contains 3 main element types: Entities, Attributes and Relationships

Figure 1.2 Three main Element types (please see detail in Appendix)

Constructs in Entity Relationship Approach

Entities is an abstract object, collection of such objects forms an entity set, the unit of data that it can be classified and have stated relationships to other entities. We can found that information of Branch, Doctor, Specialize, Customer, Animal, Appointment, Prescription, Medication and Drug that information in concept of informal design.

Relationships is specific connection between 2 or more entity sets, many choices exist between using attributes and using entity set or relationship combinations, in our diagram we are using one to many to connect entity sets. All relationships in which entity set is involved must have arrows entering it. It must be the one in many-one relationships, or its generalization for the case of multi-way relationships.

For defend the relationships of each Entity in Figure 1.3:

In Petcare branches, there have six branches in different areas, many doctors are work at one branch of Petcare only, from Branch to Doctor that we will defend the relationship is one to many type.

Every doctor can specialize in the treatment of one or more type of animal that the relationship will defend from Doctor to Specialise is one to many.

Every doctor must have more than one appointment in their work that we can defend from Doctor to Appointment is one to many type.

Each customer in Petcare, they may have more than one pet registered and one pet should only has one owner that we will defend from Customer to Animal is one to many type.

For each animal, it may have more than one appointment with doctor and on each appointment that doctor will take care one only of pet at that time, so we will defend the relationship from Animal to Appointment is one to many type.

In Appointment, the animal must have prescription to record animal anamnesis that we can defend from Appointment to Prescription is one to many.

In prescription, it may have more than one medication record that we can defend from Prescription to Medication is one to many type.

The medicine for animal may have more than one of kind that it should be one to many type of relationship.

Figure 1.3 Diagramming Notation of Relationship (please see detail in Appendix)

Attributes is some concrete data by which entity set is defined, it is simpler to implement than either entity set or a relationship. In each Table, attributes can not to be deficient, the list of attributes for each tables under Entities to display on following and Figure 1.4 Attributes Diagram,

Branch

BRID

BranchName

BranchAdd

BranchTele

EmergencyTele

OpeningHour

Doctor

DOID

DoctorName

DoctorAdd

DoctorTele

BRID

Specialise

SPID

DogSpecialist

CatSpecialist

RabbitSpecialist

Customer

CUID

CustomerName

CustomerAdd

CustomerTele

CustomerMobile

Animal

ANID

AnimalType

Breed

AnimalAge

AnimalSex

CUID

Appointment

APID

ANID

DOID

AppDate

AppTime

Prescription

PRID

PrescriptRecord

MedicalPeriod

MedicalFee

APID

Medication

PRID

DRID

ReceiptNo

Drug

DRID

DrugName

DrugCost

Figure 1.4 Attributes Diagram (please see detail in Appendix)

Task 2

Data Normalization

This path is focus on process efficiently for organizing data in database system, Data Normalization processing will eliminate redundant data and ensuring data dependencies, the goal is consumption and reduces the number of spatial databases to ensure data storage logic, and it will have some side effect if skip this process, the common are Deletion, Update and Insertion side-effect, the Normal Forms total have five levels, Petcare is medium sized company that data normalize to third level is enough for our database, at the beginning the unnormalised data set will collected on one table (Figure 2.1).

Figure 2.1 An Initial Data Schema for Branch (please see detail in Appendix)

First Normal Form

The First Normal Form is considering the data when it does not contain a duplicate set of data, we have move out every non-key attribute is functionally dependent upon the primary key, there have several non-key attribute data in Figure 2.1 of Branch information, and we have to create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

In Figure 2.1 that the functionally dependent have DOID related, there have DoctorName, DoctorAdd, DoctorAdd, DoctorTele, DogSpecialist, CatSpeciallist, RabbitSpecialist, and also APID is included, there have CustomerName, CustomerAdd, CustomerTele, CustomerMobile, AnimalType, Breed, AnimalType, Breed, AnimalAge, AnimalSex, AppDate, AppTime, PrescriptRecord, MedicalPeriod, MedicalFee, ReceiptNo, DrugName and DrugCost, we will create two separate tables for each group of related data, in Figure 2.2 have shown completed of First Normal Form.

Figure 2.2 First Normal Form Table (please see detail in Appendix)

Second Normal Form

This is further addresses the concept of removing duplicative data, it is in First Normal Form and every non-key attribute is fully functionally dependent on the primary key, to meet all the requirements of the first normal form and remove subsets of data that apply to multiple rows of a table and place them in different form, create relationships between these new tables and their predecessors through the use of foreign keys. In First Normal Form under Doctor Table, we found fully functionally dependent on primary key have DogSpecialist, CatSpecialist and RabbitSpecialist that it will move to new table, we will create a Specialise table to store those data, and in Appointment table that we create two tables are Animal and Prescription for two subsets of data are AnimalType, AnimalAge, Breed, CustomerName, CustomerAdd, CustomerTele, CustomerMobile, AnimalSex and PrescriptRecord, MedicailPeriod, MedicalFee, ReceiptNo, DrugName and DrugCost.

Figure 2.3 Second Normal Form Table (please see detail in Appendix)

Third Normal Form

Third Normal Form need meet all of Second Normal Form requirements, it is to remove in Second Normal Form and every non-key attribute is non-transitively dependent on the primary key, in Figure 2.3 table there have two problems on Animal and Prescription tables, the type description depends only on Animal and Prescription type, we will create a table of Customer for storing CustomerName, CistomerAdd, CustomerTele and CustomerMobile, another we have to create for Prescription are Medication and Drug, because RecriptNo and Drug information also are non-transitively dependent, we will put on two separate tables, it was shown in Figure 2.4

Figure 2.4 Second Normal Form Table (please see detail in Appendix)

Task 3

Database Management System

All data is an abstract, Database Management System can provide an environment to help integrated records, archives the data collected, convenient and effective use and storage of information retrieval, and it provides facilities to control data access, implementing data integrity, management, concurrency, and to restore the database from a backup. A DBMS also provides the ability to logically present database information to users.

The completed solution for medium size company in market that we can found Microsoft Access, MS Access can provide the most common language and the mechanisms are added to databases to improve retrieval performance, and it support Structured Query Language (SQL), SQL is a standard language for database access, it can provide many function on database management, execute queries against, retrieve data, insert records, update records, delete records, create database, create new tables, create stored procedures, create views…etc. In structure way that it can create table, data relationship management, data query, we can also use it to design have graphical user interface (GUI) for user input and control data, it have a function to create report or summary that we can easy for information retrieval.

When finished the normalization progress, we can start to create table in Database Management System that we using MS Access, we will use a create Table statement have a step to create whole tables of database as relationship connection. In our database infrastructure, it have nine tables to store our data, we will show the statement of create database in table on following.

Setup of Normalised Tables

To create Branch table in Figure 3.1:

CREATE TABLE Branch

(BRID CHARACTER(6) PRIMARY KEY,

BranchName VARCHAR(40),

BranchAdd VARCHAR(50),

BranchTele CHARACTER(8),

EnergencyTele CHARACTER(8),

OpeningHour CHARACTER(2)

);

Figure 3.1 create Table of Branch in MS Access (please see detail in Appendix)

We can use the Branch information input to Table show on Figure 3.2

Figure 3.2 Table of Branch (please see detail in Appendix)

To create Drug table in Figure 3.3:

CREATE TABLE Drug

(DRID CHARACTER(6) PRIMARY KEY,

DrugName VARCHAR(40),

DrugCost CHARACTER(6)

);

Figure 3.3 create Table of Drug in MS Access (please see detail in Appendix)

We can use the Drug information input to Table show on Figure 3.4

Figure 3.4 Table of Drug (please see detail in Appendix)

To create Doctor table in Figure 3.5:

CREATE TABLE Doctor

(DOID CHARACTER(6) PRIMARY KEY,

DoctorName VARCHAR(40),

DoctorAdd VARCHAR(50),

DoctorTele CHARACTER(8),

BRID CHARACTER(6) REFERENCES Branch (BRID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Figure 3.5 create Table of Doctor in MS Access (please see detail in Appendix)

We can use the Doctor information input to Table show on Figure 3.6

Figure 3.6 Table of Doctor (please see detail in Appendix)

To create Specialise table in Figure 3.7:

CREATE TABLE Specialise

(SPID CHARACTER(6) PRIMARY KEY,

DogSpecialist VARCHAR(3),

CarSpecialist VARCHAR(3),

RabbitSpecialist VARCHAR(3),

DOID CHARACTER(6) REFERENCES Doctor (DOID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Figure 3.7 create Table of Specialise in MS Access (please see detail in Appendix)

We can use the Specialise information input to Table show on Figure 3.8

Figure 3.8 Table of Specialise (please see detail in Appendix)

To create Customer table in Figure 3.9:

CREATE TABLE Customer

(CUID CHARACTER(6) PRIMARY KEY,

CustomerName VARCHAR(40),

CustomerAdd VARCHAR(50),

CustomerTele CHARACTER(8),

CustomerMobile CHARACTER(8)

);

Figure 3.9 create Table of Customer in MS Access (please see detail in Appendix)

We can use the Customer information input to Table show on Figure 3.10

Figure 3.10 Table of Customer (please see detail in Appendix)

To create Animal table in Figure 3.11:

CREATE TABLE Animal

(ANID CHARACTER(6) PRIMARY KEY,

Breed VARCHAR(40),

AnimalAge VARCHAR(2),

AnimalSex CHARACTER(1),

AnimalType CHARACTER(15),

CUID CHARACTER(6) REFERENCES Customer (CUID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Figure 3.11 create Table of Animal in MS Access (please see detail in Appendix)

We can use the Animal information input to Table show on Figure 3.12

Figure 3.12 Table of Animal (please see detail in Appendix)

To create Appointment table in Figure 3.13:

CREATE TABLE Appointment

(APID CHARACTER(6),

AppDate VARCHAR(20),

AppTime VARCHAR(20),

ANID CHARACTER(6) REFERENCES Animal (ANID)

ON DELETE CASCADE

ON UPDATE CASCADE,

DOID CHARACTER(6) REFERENCES Doctor (DOID)

ON DELETE CASCADE

ON UPDATE CASCADE,

PRIMARY KEY (APID, ANID, DOID)

);

Figure 3.13 create Table of Appointment in MS Access (please see detail in Appendix)

We can use the Appointment information input to Table show on Figure 3.14

Figure 3.14 Table of Appointment (please see detail in Appendix)

To create Prescription table in Figure 3.15:

CREATE TABLE Prescription

(PRID CHARACTER(6) PRIMARY KEY,

PrescriptRecord VARCHAR(40),

MedicalPeriod VARCHAR(50),

MedicalFee CHARACTER(8),

APID CHARACTER(6) REFERENCES Appointment (APID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Figure 3.15 create Table of Prescription in MS Access (please see detail in Appendix)

We can use the Prescription information input to Table show on Figure 3.16

Figure 3.16 Table of Prescription (please see detail in Appendix)

To create Medication table in Figure 3.17:

CREATE TABLE Medication

(ReceiptNo VARCHAR(6),

PRID CHARACTER(6) REFERENCES Prescription (PRID)

ON DELETE CASCADE

ON UPDATE CASCADE,

DRID CHARACTER(6) REFERENCES Drug (DRID)

ON DELETE CASCADE

ON UPDATE CASCADE,

PRIMARY KEY (PRID, DRID)

);

Figure 3.17 create Table of Medication in MS Access (please see detail in Appendix)

We can use the Medication information input to Table show on Figure 3.18

Figure 3.18 Table of Medication (please see detail in Appendix)

Task 4

Query and Testing

Executing the query is a independent database utility, SQL code can flexibility retrieve or display data, it provides a way to interact with database, from a simple lookup table creation and import / export the whole structure of the data. In our database system, we tried two queries using SQL code to display related data table.

First test that we tried enter SQLcode to display related data between Branch and Doctor information on Figure 4.1,

SELECT Branch.BranchName, Branch.BranchAdd, Doctor.DoctorName,Specialise.DogSpecialist, Specialise.CarSpecialist, Specialise.RabbitSpecialist

FROM Branch INNER JOIN (Doctor INNER JOIN Specialise ON Doctor.DOID = Specialise.DOID) ON Branch.BRID = Doctor.BRID;

Figure 4.1 Query code for Branch and Doctor (please see detail in Appendix)

On query result that we can successful to display on Table Figure 4.2, there will show the related data of BranchName, BranchAdd, DoctorName, DogSpecialist, CatSpecialist and RabbitSpecialist.

Figure 4.2 Table of between Branch and Doctor data (please see detail in Appendix)

Second test that we tried enter SQLcode to display related data between Appointment, Branch and Doctor on Figure 4.3,

SELECT Appointment.AppDate, Appointment.AppTime, Branch.BranchName, Doctor.DoctorName, Customer.CustomerName, Animal.AnimalType, Animal.Breed

FROM Branch INNER JOIN ((Doctor INNER JOIN (Customer INNER JOIN (Animal INNER JOIN Appointment ON Animal.ANID = Appointment.ANID) ON Customer.CUID = Animal.CUID) ON Doctor.DOID = Appointment.DOID) INNER JOIN Specialise ON Doctor.DOID = Specialise.DOID) ON Branch.BRID = Doctor.BRID;

Figure 4.3 Query code for Appointment, Branch, Doctor, Customer and Animal (please see detail in Appendix)

On query result that we can successful to display on Table Figure 4.4, there will show the related data of Appointment.AppDate, Appointment.AppTime, Branch.BranchName, Doctor.DoctorName, Customer.CustomerName, Animal.AnimalType, Animal.Breed.

Figure 4.4 Table of between Appointment, Branch, Doctor, Custmoner and Animal data (please see detail in Appendix)

Task 5

Assumptions

We are understood that Database Management System can help us much efficient and faultless to manage our important data. On above appoaching, from design informal idea of database to end of quary, we have different kind of procedures for analysing, designing and implementing, in all results that we could see how to made it success, for analysing that we used Entity Relationship Data Model to help for identity the data type and between table relationship, we have an advantage to use it for prepare a data structures and table of relationship in design tables on Database Management System. MS Access is most popular in market, it can provide facilities for creating of Tables, Queries, Forms and Reports, this is our working requested, also it can run on stand-alone system that we don’t have money expend on Server, the important is we can get the result of what we would like have in MS Access.

In future when Petcare size to expand, we have to consider the system environment can contain our database, hardware and software in our consider area. Furthermore, the data management and security control also very important, we have to plan some rules to control data accessing and about safety issue, safety management is one of our planning in our business, once database crash we can have backup for quick resume, and it can also a data retrieve function.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.