Applying Data Modeling To Database Solutions Computer Science Essay

Published:

This assignment aimed at the students to demonstrate an understanding of applying data modeling and data analysis techniques to design and development of database solutions. In this assignment, I have been sub-contracted to design the database system for PetCare. It is a medium sized veterinary surgery with six forks in London. It wants a computerized database system developed to control the records of the animals they care for, appointments and prescriptions. It gives some initial analysis for their manual database system and wants to convert them into computerized.

Task 1

1 - 1 Entity Relationship Diagram

1 - 2 Relational Schema

Animal

(Animal ID, Animal Name, Type, Breed, Sex, Age, Owner ID)

Owner

(Owner ID, Owner Name, Address, Home Phone, Mobile, E-mail)

Doctor

(Doctor ID, Doctor Name, Specialization, Address, Home Phone, Mobile, E-mail, Branch ID)

Branch

(Branch ID, Branch Name, Address, Phone, Opening Hours, Emergency Phone)

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

Appointment

(Appointment ID, Appointment Date, Appointment Time, Branch ID, Doctor ID, Owner ID, Total Charges)

Prescription

(Prescription ID, Appointment ID, Doctor ID, Total Cost)

Appointment Details

(Appointment ID, Animal ID, Diagnosis Made, Charges)

Prescription Details

(Prescription ID, Drug Name, Period for Drug Taken, Days for Drug Taken, Cost)

1 - 3 Entity Description

Entity

Description

Animal

An animal is a pet owned by an owner.

Owner

An owner is the person who has animal(s), come and make appointment to the system

Doctor

A doctor is the person who cures the sick animals in this system.

Branch

A branch is a place that is extension of PetCare Company.

Appointment

The process in which an owner appoints a doctor to cure its pet is called appointment.

Prescription

A prescription is come out according to the appointment

1 - 4 Content & Structure

Entity Name - Animal

Primary Key - Animal ID

Foreign Key - Owner ID

Attributes

Data Type

Size

Animal ID

varchar

50

Animal Name

varchar

50

Type

varchar

50

Breed

varchar

50

Sex

int

(18,2)

Owner ID

varchar

50

Entity Name - Doctor

Primary Key - Doctor ID

Foreign Key - null

Attributes

Data Type

Size

Doctor ID

varchar

50

Doctor Name

varchar

50

Specialization

varchar

50

Address

varchar

50

Home Phone

varchar

50

Mobile

varchar

50

E-mail

varchar

50

Branch ID

varchar

50

Entity Name - Owner

Primary Key - Owner ID

Foreign Key - null

Attributes

Data Type

Size

Owner ID

varchar

50

Owner Name

varchar

50

Address

varchar

50

Home Phone

varchar

50

Mobile

varchar

50

E-mail

varchar

50

Entity Name - Branch

Primary Key - Branch ID

Foreign Key - null

Attributes

Data Type

Size

Branch ID

varchar

50

Branch Name

varchar

50

Address

varchar

50

Telephone

varchar

50

Opening Hours

varchar

50

Emergency Phone

Varchar

50

Entity Name - Prescription

Primary Key - Prescription ID

Foreign Key - Appointment ID, Doctor ID

Attributes

Data Type

Size

Prescription ID

varchar

50

Appointment ID

varchar

50

Doctor ID

varchar

50

Total Cost

int

2

Entity Name - Prescription Details

Primary Key - Prescription ID

Foreign Key - null

Attributes

Data Type

Size

Prescription ID

varchar

50

Drug Name

varchar

50

Period for Drug Taken

varchar

50

Days for Drug Taken

varchar

50

Cost

int

2

Entity Name - Appointment

Primary Key - Appointment ID

Foreign Key - Branch ID, Doctor ID, Owner ID, Animal ID

Attributes

Data Type

Size

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

Appointment ID

varchar

50

Appointment Date

datetime

8

Appointment Time

datetime

8

Branch ID

varchar

50

Doctor ID

varchar

50

Owner ID

varchar

50

Total Charges

Int

2

Entity Name - Appointment Details

Primary Key - Appointment ID, Animal ID

Foreign Key - null

Attributes

Data Type

Size

Appointment ID

varchar

50

Animal ID

varchar

50

Diagnosis Made

varchar

50

Charges

int

2

Task 2

Task 2

2 - 1 Form Design

Form Design of Doctor Registration

Pet Care

VET CLINIC

LONDON

DOCTOR REGISTRATION

Branch ID : ______________________

Branch Name : ______________________

Doctor ID : ______________________

Doctor Name : ______________________

Specialization : ______________________

Address : ______________________

Home Phone : ______________________

Mobile : ______________________

E-mail : ______________________

Form Design of Appointment

Pet Care

VET CLINIC

LONDON

APPOINTMENT FORM

Appointment ID : ____________________

Appointment Date : ______________________

Appointment Time : ______________________

Branch ID : ________________________

Branch Name : _____________________

Doctor ID : ___________________________

Doctor Name : ________________________

Owner ID : ________________________

Owner Name : _____________________

Animal ID

Animal Name

Diagnosis Made

Charges

Total Charges

Form Design of Prescription

Pet Care

VET CLINIC

LONDON

PRESCRIPTION FORM

Prescription ID : ____________________

Appointment ID : ______________________

Doctor ID : ________________________

Doctor Name : ________________________

Drug Name

Drug Type

Period for Drug Taken

Days for Drug Taken

Cost

Total Cost

2 - 2 Normalization

Normalization of Doctor Registration Form

UNF

Branch ID

Branch Name

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

Primary Key

Doctor ID

1NF

Branch ID

Branch Name

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

2NF

Branch ID

Branch Name

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

3NF

*Branch ID

Branch Name

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

Branch ID

Branch Name

Optimization

[Doctor]

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

*Branch ID

[Branch]

Branch ID

Branch Name

Address

Phone

Opening Hours

Emergency Phone

Data Model

Normalization of Appointment Form

UNF

Appointment ID

Appointment Date

Appointment Time

Branch ID

Branch Name

Doctor ID

Doctor Name

Owner ID

Owner Name

Animal ID

Animal Name

Diagnosis Made

Charges

Total Charges

Primary Key

Appointment ID

1NF

Appointment ID

Appointment Date

Appointment Time

Branch ID

Branch Name

Doctor ID

Doctor Name

Owner ID

Owner Name

Total Charges

Appointment ID

Animal ID

Animal Name

Diagnosis Made

Charges

2NF

Appointment ID

Appointment Date

Appointment Time

Branch ID

Branch Name

Doctor ID

Doctor Name

Owner ID

Owner Name

Total Charges

Appointment ID

Animal ID

Diagnosis Made

Charges

Animal ID

Animal Name

3NF

Appointment ID

Appointment Date

Appointment Time

*Branch ID

*Doctor ID

*Owner ID

Total Charges

Branch ID

Branch Name

Doctor ID

Doctor Name

*Branch ID

Owner ID

Owner Name

Appointment ID

Animal ID

Diagnosis Made

Charges

Animal ID

Animal Name

Optimization

[Appointment]

Appointment ID

Appointment Date

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

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

Examples of our work

Appointment Time

*Branch ID

*Doctor ID

*Owner ID

Total Charges

[Appointment Details]

Appointment ID

Animal ID

Diagnosis Made

Charges

[Doctor]

Doctor ID

Doctor Name

Specialization

Address

Home Phone

Mobile

E-mail

*Branch ID

[Branch]

Branch ID

Branch Name

Address

Opening Hours

Emergency Phone

[Owner]

Owner ID

Owner Name

Address

Home Phone

Mobile

E-mail

[Animal]

Animal ID

Animal Name

Type

Breed

Sex

Age

*Owner ID

Data Model

Normalization of Prescription Form

UNF

Prescription ID

Appointment ID

Doctor ID

Doctor Name

Drug Name

Drug Type

Period for Drug Taken

Days of Drug Taken

Cost

Total Cost

Primary Key

Prescription ID

1NF

Prescription ID

Appointment ID

Doctor ID

Doctor Name

Total Cost

Prescription ID

Drug Name

Drug Type

Period for Drug Taken

Days of Drug Taken

Cost

2NF

Prescription ID

Appointment ID

Doctor ID

Doctor Name

Total Cost

Prescription ID

Drug Name

Drug Type

Period for Drug Taken

Days of Drug Taken

Cost

Prescription ID

*Appointment ID

*Doctor ID

Total Cost

Doctor ID

Doctor Name

Prescription ID

Drug Name

Drug Type

Period for Drug Taken

Days of Drug Taken

Cost

3NF

Optimization

[Prescription]

Prescription ID

*Appointment ID

*Doctor ID

Total Cost

[Doctor]

Doctor ID

Doctor Name

Address

Specialization

Home Phone

Mobile

E-mail

*Branch ID

[Prescription Details]

Prescription ID

Drug Name

Drug Type

Period for Drug Taken

Days of Drug Taken

Cost

[Appointment]

Appointment ID

Appointment Date

Appointment Time

*Branch ID

*Doctor ID

*Owner ID

Data Model

Task 3

Assumption

To get a flexible database, I use SQL Server Database Management System to setup this assignment database. Database Implementation is followed by the following steps:

Select the DBMS

Create Physical Schema

Establish storage structures and associated access mechanisms

Adding indexes

De-normalization

Define users and privileges

Tune in terms of the chosen DBMS

Build integrity constraints

Select and Exploit DBMS

The physical database design and regard to data are carried out by the following analysis:

Volume analysis

Usage analysis

Transaction analysis

Access Requirements analysis

Integrity analysis

Security/Control analysis

Distributive analysis

By using SQL Server, we can change easily from Window Application to Online Application for future trend (See Task 5: Future Plan). The advantages of using SQL Server are also described in Task 5: Assumption.

Design View

Animal Table

Appointment Table

Appointment Details Table

Branch Table

Doctor Table

Owner Table

Prescription Table

Prescription Details Table

Datasheet View

Animal Table

Appointment Table

Appointment Details Table

Branch Table

Doctor Table

Owner Table

Prescription Table

Prescription Details Table

Relationship View

Task 4

SQL Statements

Display the names and addresses of the branches of Petcare and the names of all the veterinary doctors working at each of the branches. Any specialism(s) of the veterinary doctors should also be shown.

SELECT b.BranchName, b.Address, d.DoctorName, d.Specialization

FROM tblBranch b, tblDoctor d

WHERE b.BranchID = d.BranchID

ORDER BY DoctorName ASC

Display all the appointments for the whole of the Petcare organization. This should be order by date. The result should display the branch the appointment is at, the name of the veterinary doctor the appointment is with, the date and time of the appointment, the name of the animal the appointment is for, the type of animal and the breed of the animal.

SELECT b.BranchName, d.DoctorName, p.AppointmentDate, p.AppointmentTime, a.Breed, a.AnimalName, a.AnimalType

FROM tblBranch b, tblDoctor d, tblAppointment p, tblAnimal a, tblAppointmentDetails ap

WHERE b.BranchID =p.BranchID AND a.AnimalID=ap.AnimalID AND d.DoctorID=p.DoctorID AND p.AppointmentID=ap.AppointmentID

ORDER BY p.AppointmentDate ASC

Result Table

Result Table

Task 5

Assumption

By using SQL Server Management System, we can get many advantages. SQL Server 2005 makes decrease the downtime of application, increased availability and performance, and tight flexible security management. It makes easy and simple to deploy, manage, and optimize enterprise data and analytical applications. It enables to control, monitor and tune the databases in the effective and efficient way. Failure of the original system, application program is able to reconnect immediately to the database on the secondary server by using Database Mirroring. SQL Server 2005 supports a new ability for the partitioning of tables across file groups in a database. It has services of MDX (Multidimensional Expressions), XML for Analysis and XML. Integration with the Microsoft Visual Studio development provides more efficient and successful development and debugging of business intelligence and line-of-business applications.

Alternative Approach

We can develop this system by also using Microsoft Access Database Management System but there are many gaps between SQL Server and Access:

Feature

SQL

Access

Database size

1,048,516 TB

2 GB plus

Linked tables size objects in a database

2,147,483,647

32,768

User Name Length

128

20

Password length

128

14

Table name length

128

64

Column name length

128

64

Index name length

128

64

Number of concurrent users

Limited

255

Columns per table

1024

255

Table size

Limited

1GB

Number of indexes in a table

250

10

Number of columns in an index

16

10

Bytes per row

8060

2000

Number of tables in a query

256

32

Columns per SELECT statement

4096

255

Nested subqueries

32

50

Number of enforced relationships

253

32

As a result from the above reasons, I prefer SQL Server more than Access. I also think it is more compatible with PetCare System.

System Requirements

The minimum hardware and software requirements are as follows:

Display Device

Require at least 1024x768 pixels VGA or higher resolution for SQL Server graphical tools.

Pointing Tool

Compatible pointing tool is necessary.

CD or DVD ROM

Installation from CD or DVD media demands a appropriate CD or DVD ROM.

Hardware Requirements Bunch

The maximum number of nodes, eight-node cluster installation which is supported by Microsoft Server 2003 can be run on 32-bit and 64-biy platforms.

Networking Requirements

If you used SQL server over network or join with network, there are some requirements and remark. Built-in network software is already installed in Microsoft Server 2003, Windows 2000 and above. You need to aware of one thing (i.e. it doesn't support the SPP (Sequenced Packet protocol), AppleTalk, NWLink SPX/IPX or Multiprotocol network protocols.) You need to choose another protocol to connect SQL Server 2005 if you previously connected with these protocols. It provides the following protocols:

TCP/IP

VIA

Shared memory

Named pipes

Failover network cluster does not support shared memory.

Internet Service Requirements

Internet Service Requirements for both 32 and 64-bit versions of SQL Server 2005 are the same. The table below shows the requirements:

Constituent

Requirements

Internet Browser

IE 6.0 or above is essential for all installations of SQL Server 2005, while it is also necessary for MMC (Microsoft Management Console and HTML Help. IE 4.01 with Service Pack 2 is required if you install only client components and do not connect to a server which requires encryption.

Internet Information Service (IIS)

SSRS (SQL Server 2005 Reporting Services) installations need Internet Information Service 5.0 or later.

ASP.NET Framework 2.0

It is also necessary for SSRS. Microsoft SQL Server Setup enables ASP.NET if it has not already enabled while installing Reporting Services.

Software Requirements

MDAC (Microsoft Data Access Components) 2.8 or later and Microsoft Windows Installer 3.1 or above is required. Microsoft .NET Framework 2.0, SQL Server Setup support files, Server Native Client have already contained in SQL Server Setup. It installs each of these parts separately if not already installed: it also removed automatically if you uninstall SQL Server 2005. After a computer has already installed required software, SQL Server Setup will confirm the computer where this computer has already installed SQL Server and meets the other requirements for a perfect installation.

Hardware Requirements

This SQL Database file is created with Server 2005 Express Edition. So, it needs P3 CPU or later processor type, processor speed is minimum 500 MHz and recommended speed is 1GHz or higher and minimum RAM space is 192MB while recommended space is 512 MB or more.

Backing Store Requirements

Microsoft SQL Server occupies the space storage for the following features:

Services and Features

Space Requirements

Notification Services engine components, client components and rules components

50MB

Analysis Services and data files

90MB

Integration Services

120MB

Reporting Services and Report Manager

120MB

SQL Server Books Online and SQL Server Compact Edition Books Online

240MB

Database Engine and data files, Replication and Full-Text Search

280MB

Samples and sample databases. Note that samples and sample databases are not installed by default

410MB

Client Components

850MB

Future Plan

The future trend for this PetCare System is available by using SQL Server. The system can convert from Windows Application system to Online Application system. We can add more functions and features for this PetCare System.