Database Design And Development For Petcare Veterinary

Essay add: 24-10-2015, 21:30 / Views: 468

Draw an Entity Relationship Data Model that describes the content and structure of data held by PetCare. Specify the cardinality ratio and participation constraint of each relationship type.

Database Development Process

The process is divided into four main stages: requirements elicitation, conceptual modeling, logical modeling and physical modelling. The techniques used in the development process naturally divide into three categories: those concerned modelling, those concerned with logical modelling and those concerned with physical modelling.Figure 1 the database development process

Requirements Elicitation

Requirements elicitation involves establishing the key technical requirements for a database system usually through formal and informal interaction between developers and organizational stakeholders such as users. It provides the structure of data needed and the use of the data in some information system context.Stakeholder identification and participationOne of the first things that must be done in any information systems project is to identify the relevant stakeholders. A stakeholders group is any social group within and without the organization that potentially may influence the successful use and impact of the database system.Veterinary doctor - Use the database to for managing treatment information of petStaff - keep track of animal and appointmentRegulators - National and regional government may need to audit the databaseRequirements Elicitation and requirements specificationRequirements elicitation is the precursor to requirements specification. In terms of a system to manage Petcare, the following is a list of proposed requirements for system:

The database should be capable of supporting the following transaction:

Create and maintain records recording the details of Petcare Pets clinics and the members of staff at each clinic.Create and maintain records recording the details of pet owners.Create and maintain the details of pets.Create and maintain records recording the details of the types of treatments for pets.Create and maintain records recording the details of examinations and treatments given to pets.Create and maintain records recording the details of invoices to pet owners for treatment to their pets.Create and maintain pet owner/pet appointments at each clinic.

Data requirements

PetCare veterinary surgery

Petcare has six medium sized veterinary surgery clinics across London. The details of each clinic include address of branch, telephone number, opening hours and emergency contact telephone number. Assuming that each clinic has a number of staff for example vets, nurses, secretaries and cleaners.

Staff

The details stored on each member of staff include the staff name, address, home telephone number and mobile telephone number.

Pet owners

When a pet owner first contacts a clinic of Petcare the details of the pet owner are recorded, which include name, address, home telephone number and mobile telephone number.

Pets

The details of the pet requiring treatment are noted, which include a put number, type of pet, age and sex.

Examinations

When a sick pet is brought to a clinic, the vet on duty examines the pet. The details of each examination are recorded and include an examination number, the date and time of the examination, the name of the vet, the pet number, pet name, and type of pet, and a full description of the outcome of the examination results.Petcare provides various treatments for all types of pets. The details of each treatment include a treatment number, full description of the treatment, and the cost to the pet owner. Based on the result of the examination of a sick pet, the vet may propose one or more types of treatment.

For each types of treatment, the information recorded includes the examination number and date.

Invoices

The pet owner is responsible for the cost of the treatment given to a pet. The owner is invoiced for the treatment arising from each examination, and the details recorded on the invoice include the invoice number, invoice date, owner number, owner name and full address, put number, put name, and details of the treatment given. The invoice provides the cost for each type of treatment and total cost of all treatments given to the pet.

Additional data is also recorded on the payment for example cash, credit card or check.

Appointments

If the pet requires to be seen by the vet at later date, the owner and pet are given an appointment. The details of an appointment are recorded and include an appointment number, owner name, date and time.

Using the logical database design methodology

Entity-Relationship modeling is a top-down approach to database design. We begin ER modeling by identifying the important data (called entities) and relationships between the data that must be represented in the model. We then add more details such as the information we want to hold about the entities and relationships (called attributes) and any constraints on the entities, relationships, and attributes. Identify entities

Identify entities

A set of objects with the same properties, which are identified by a user or organization as having an independent existenceThe first step in logical database design is to identify entity that you have to represent in the database.Entity nameDescriptionOccurrenceClinicVeterinary clinicsOne or more Petcare clinics located throughout LondonStaffGeneral term describing all staff employed by PetcareEach member of staff works at a particular clinicPetOwnerOwners of pets taken to PetcareOwner takes his/her pet to a particular clinicPetSick animal seek treatment to the clinicOne or more animal are taken to the clinicExaminationTreatmentInvoiceAppointmentPetTreatment

Attributes

The particular properties of entities are called attributes. Attributes represent what we want to know about entities.

Single-Valued attributes

The majority of attributes are single-valued for a particular entity. For example, each occurrence of the Staff entity has a single value for the staffNo attribute (for example,001), and therefore the staffNo attribute is referred to as being single-valued.

Derived attributes

An attributes that represents a value that is derivable from the value of a related attributes, or set of attributes, no necessarily in the same entity.Some attributes may be related for a particular entity. For example, the age of a pet is derivable form the date of birth (DOB) attribute, and therefore the age and DOB attributes are related. We refer the age attributes as a derived attributes, the value of which is derived from the DOB attribute.Age is not normally stored in a database because it would have to be updated regularly.

On the other hand, as date of birth never changes and age can be derived from date of , date of birth is stored instead, and age is derived from DOB attribute, when needed.

Identify relationships

Having identifies the entities; next step is to identify all the relationships that exist between these entities. A relationship is a set of associations between participating entities. As with entities, each association should be uniquely identifiable within the set. A uniquely identifiable associations is called a relationships occurrence

Clinic Relationships

Entity

Relationship

Entity

ClinicHasVeterinaryRegistersPetSchedulesAppointmentIsContactedByPetOwner

Veterinary Relationships

Entity

Relationship

Entity

VeterinaryPerformsExamination

Pet Owner Relationships

Entity

Relationship

Entity

PetOwnerOwnsPetPaysInvoiceAttendsAppointment

Pet Relationships

Entity

Relationship

Entity

PetUndergoesExaminationAttendsAppointment

Cardinality Ratio of PetCare database

Cardinality or degree concerns the number of instances involved in a relationship. A relationship can be said to be either a 1:1 (one-to-one) relationship, a 1: M (one-to-many) relationship, or an M: N (many-to-many) relationship.

Final Clinic relationships

Entity

Cardinality

Relationship

Cardinality

Entity

Clinic1..1Has1..MStaff1..MRegisters1..MPet1..1Schedules1…MAppointment1..1IsContactedBy1..MPetOwner

Final Veterinary Relationships

Entity

Cardinality

Relationship

Cardinality

Entity

Veterinary1..1Performs1..MExamination

Final Pet Owner Relationships

Entity

Cardinality

Relationship

Cardinality

Entity

PetOwner1..1Owns1..MPet1..1Pays1..MInvoice1..1Attends1..MAppointment

Final Pet Relationships

Entity

Cardinality

Relationship

Cardinality

Entity

Pet1..1Undergoes1..MExamination1..1Attends1..MAppointment

First draft Entity Relationship Data Model

Second draft Entity Relationship Data Model

Final Entity Relationship Data Model

Task 2 Normalization

Normalization is a technique for producing a set of table with desirable properties that support the requirements of a user or company. There are several normal forms, although the most ones are called first normal form (1NF), second normal form (2NF), and third normal form (3NF). All these normal forms are based on rules about relationships among the columns of a table.

First normal forms (1NF)

Only first normal form (1NF) is critical in creating appropriate tables for relational databases. All the subsequence normal forms are optional. A table in which the intersection of every column and record contains only one valueClinic (clinicNo, address, city, state, zipcode,telNo, faxNo, opeingHour)Primary Key clinicNoAlternate Key zipCodeAlternate Key telNoAlternate Key faxNoAlternate Key opeingHour

clinicNo

address

telNo

openingHour

C001Enfield503-555-3618, 503-555-2727, 503-555-65349.00-21-00C002Islington206-555-6756, 206-555-88369.00-21-00C003Hackney212-371-30009.00-21-00C004Holloway206-555-3131, 206-555-41129.00-21-00C005Chingford85023339.00-21-00C006Leyton46500009.00-21-00This version of the Clinic table is not in 1NF

More than one value, so not in 1NF

Converting to 1NFTo convert this version of the Clinic table to 1NF, we create separate table called ClinicTelephone to hold the telephone number of clinics, by removing the tellNo column from the Clinic table along with a copy of the primary key of the Clinic table. The primary key for the new ClinicTelephone table is now the telNo column. The Clinic and ClinicTelephone table are in 1 NF as there is a single value at the intersection of every column with every record for each table

Clinic (Not 1NF)

clinicNo

address

telNo

openingHour

ClinicTelephone (1NF)

clinicNo

telNo

C001503-555-3618C001503-555-2727C001503-555-6534C002206-555-6756C002206-555-8836C003212-371-3000C004206-555-3131C004206-555-4112C0058502333C0064650000

Clinic (1NF)

clinicNo

address

openingHour

C001Enfield9.00-21-00C002Islington9.00-21-00C003Hackney9.00-21-00C004Holloway9.00-21-00C005Chingford9.00-21-00C006Leyton9.00-21-00

Second normal form (2NF)

Second normal form applies only to tables with composite primary keys that are table with a primary key composed of two or more columns. A 1NF table with a single column primary key is automatically in at least 2NF. A table that is not in 2NF may suffer from update anomalies.

A table that is already in 1NF and which the values in each non-primary-key column can be worked out from values in all columns that make up the primary key.

Third normal form (3NF)

Although 2NF table have less redundancy that table in 1 NF, they may still suffer from update anomalies. A table that is already in 1NF and 2NF, and in which the values in all non-primary key columns can be worked out from only the primary key column and no other columns.staffNonamelastNamesalaryclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005ArtPeters45822C003Hackney8502333S006TommyVerciti65000C004Holloway4650000Values in clinicNo and clinicAddress columns can be worked out from telNo, so table not in 3NFValues in clinicNo and telNo columns can be worked out from clinicAddress, so table not in 3NFValues in all non-primary-key columns can be worked out from the primary key, staffNoValues in clinicAddress and telNo columns can be worked out from clinicNo, so table not in 3NFstaffNonamelastNamesalaryclinicNoclinicAddresstelNoStaffClinic (Not 3NF)staffNonamelastNamesalaryclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005ArtPeters45822C003Hackney8502333S006TommyVerciti65000C004Holloway4650000Move column to new tableTake copy of clinicNo column to new table to become primary keyClinic (3NF)clinicNoclinicAddresstelNoC001Enfield5035553618C001Enfield5035553618C002Islington206555675C002Islington206555C003Hackney8502333C004Holloway4650000Staff (3NF)staffNonamelastNamesalaryclinicNoS001TomAdams25000C001S002SallyDaniels35222C001S003MaryChin5200C002S004SallyStern5000C002S005ArtPeters45822C003S006TommyVerciti65000C004Primary keyBecomes foreign keyBecomes candidate keyBecomes primary key

Task 3

Using a Database Management System (DBMS) of your choice, set up all the above normalized tables, and populate them with well-designed test data (minimum 5 records per table). Provides printouts of all tables.Reasonable assumption may be made with regard to data

Clinic Table

create table clinic

(

clinicNO int not null primary key,telNo varchar(255),address varchar(255),

)

alter table clinicadd clinicName varchar (255)alter table clinicadd openingHour varchar(255)alter table clinicadd eTelNo varchar (255)insert into clinicvalues (01,2863015,'Darwin Avenue','8.00-21.00',2863000,'Enfield')insert into clinicvalues (02,4650001,'John David Avenue','8.00-21.00',2868000,'Islington')insert into clinicvalues (03,4278926,'King Arthur Avenue','8.00-21.00',2867000,'Hackney')insert into clinicvalues (04,2682365,'Paul Mac Avenue','8.00-21.00',2866000,'Holloway')insert into clinicvalues (05,4682685,'James Micheal Avenue','8.00-21.00',2865000,'Chingford')insert into clinicvalues (06,2863015,'Benaoit Frank Avenue','8.00-21.00',2864000,'Leyton')

Pet owner

create table petowner

(

ownerID int not null primary key,oFName varchar (255),oLName varchar (255),clinicNo int foreign key references clinic (clinicNo))alter table petowneradd addres varchar(255)alter table petowneradd hTelNo varchar(255)alter table petowneradd mTelNo varchar (255)insert into petownervalues (01,'Marvin','Hemraj',1,'Edith Cavel Str',2106584,758956)insert into petownervalues (02,'Ramjeet','Lavin',2,'Avenue Gonin',2564589,7585695)insert into petownervalues (03,'Arzeena','Bakarkhan',3,'Gorgetown Str',2106584,758956)insert into petownervalues (04,'Chetan','Sing',4,'Jackson Road',2458695,7582658)insert into petownervalues (05,'Hansley','Nowjee',5,'15 Ollier Avenue',2565458,7589562)insert into petownervalues (06,'Sam','Fisher',6,'Leess Street',26584585,75895623)

Pet Table

create table pet

(

petNo int not null primary key,type varchar (255),breed varchar (255),sex varchar (255),dob varchar (255)

)

alter table petadd clinicNo int foreign key references clinic (clinicNo)alter table petadd ownerid int foreign key references petOwner (ownerid)alter table petadd petName varchar (255)insert into petvalues (01,'Dog','Terroer','Male','1 Jan 2004',01,01,'Wouf')insert into petvalues (02,'Dog','Poodle','Female','2 Feb 2005',02,02,'Snoopy')insert into petvalues (03,'Cat','Persian','Male','3 March 2006',03,03,'Minous')insert into petvalues (04,'Cat','Siamese','Female','4 April 2007',04,04,'Milous')insert into petvalues (05,'Rabit','Dwarf','male','5 May 2008',05,05,'Lapino')insert into petvalues (06,'Cat','Siamese','Female','4 June 2009',06,06,'Lapinas')

Examination Table

create table examination

(

examNo int not null primary key )alter table examinationadd veterinaryId int foreign key references veterinary (veterinaryId)alter table examinationadd petNo int foreign key references pet (petNo)alter table examinationadd presDrugType varchar (255),presPeriod varchar (255)insert into examinationvalues (1,1,'Anti-biotic',15,01)insert into examinationvalues (2,2,'Painkiller',14,02)insert into examinationvalues (3,3,'Behaviour modification',13,03)insert into examinationvalues (4,4,'Ear medication',12,04)insert into examinationvalues (5,5,'Skin medication',15,05)insert into examinationvalues (6,6,'Painkiller',10,06)

Appointment Table

create table appointment

(

appNo int not null primary key,aDate varchar (255),aTime varchar (255),petNo int foreign key references pet (petNo),ownerID int foreign key references petowner (ownerID),veterinaryId int foreign key references veterinary (veterinaryId)

)

alter table appointmentadd clinicNO int foreign key references clinic (clinicNO)alter table appointmentalter column aDate dateinsert into

Article name: Database Design And Development For Petcare Veterinary essay, research paper, dissertation

Database Design And Development For Petcare Veterinary

Related essay