Database Design And Development For Petcare Veterinary Computer Science Essay

Database Design And Development For Petc are Veterinary Computer Science EssayDraw an Entity race Data reproduction that describes the content and structure of data held by PetCare. Specify the cardinality ratio and participation constraint of totally(prenominal) family relationship type.Database Development ProcessThe work is divided into quatern main stages requirements elicitation, conceptual modeling, dianoetic modeling and physical modelling. The techniques used in the development process naturally divide into three categories those concerned modelling, those concerned with pellucid modelling and those concerned with physical modelling.Figure 1 the database development processRequirements triggerRequirements elicitation involves establishing the mainstay technical requirements for a database arranging usually done formal and informal interaction between developers and organizational stake devoteers such as exploiters. It provides the structure of data needed and t he use of the data in some info system context.Stakeholder identification and participationOne of the first base things that must be done in any information systems project is to come upon 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 enforce the database to for managing give-and-take information of fondlestave keep track of animal and escortRegulators 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 systemThe database should be capable of keep the following transaction occasion and maintain come ins recording the expatiate of Petcare Pets clinics and the members of staff at each cl inic.Create and maintain records recording the expatiate of deary owners.Create and maintain the details of dearys.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 bank notes to pet owners for treatment to their pets.Create and maintain pet owner/pet try-ons at each clinic.Data requirementsPetCare vet surgeryPetcare has six medium size veterinary surgery clinics crosswise London. The details of each clinic include address of branch, telephone sum, opening hours and emergency contact telephone number. Assuming that each clinic has a number of staff for example vets, nurses, secretaries and cleaners.StaffThe details stored on each member of staff include the staff name, address, home telephone number and quick telephone number.Pet ownersWhen a pet owner first contacts a clinic of Petcare the det ails of the pet owner are recorded, which include name, address, home telephone number and mobile telephone number.PetsThe details of the pet requiring treatment are noned, which include a put number, type of pet, age and sex.ExaminationsWhen 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 rise 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 to a greater extent types of treatment. For each types of treatment, the information recorded includes the examination number and date.InvoicesThe 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 honorarium for example cash, credit card or check. appellationsIf 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 methodologyEntity- family modeling is a top-down appeal 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 much deta ils such as the information we want to hold about the entities and relationships (called attributes) and any constraints on the entities, relationships, and attributes. commit entitiesIdentify entitiesA 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 finishedout LondonStaff commonplace 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 clinicExamination treatmentInvoice assigningPetTreatmentAttributesThe particular properties of entities are called attributes. Attributes represent what we want to k instantaneo usly about entities.Single-Valued attributesThe majority of attributes are genius-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 attributesAn 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 consanguinity (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 usually stored in a database because it would have to be updated regularly. On the other hand, as date of birth never changes and age tidy sum be derived from date of , date of birth is stored instead, an d age is derived from DOB attribute, when needed.Identify relationshipsHaving 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 erraticly identifiable within the set. A uniquely identifiable associations is called a relationships occurrenceClinic RelationshipsEntityRelationshipEntityClinicHasVeterinaryRegistersPetSchedules accommodationIsContactedByPetOwnerVeterinary RelationshipsEntityRelationshipEntityVeterinaryPerformsExaminationPet Owner RelationshipsEntityRelationshipEntityPetOwnerOwnsPetPaysInvoiceAttendsAppointmentPet RelationshipsEntityRelationshipEntityPetUndergoesExaminationAttendsAppointmentCardinality Ratio of PetCare databaseCardinality or degree concerns the number of instances involved in a relationship. A relationship can be said to be either a 11 (one-to-one) relationship, a 1 M (one-to-many) re lationship, or an M N (many-to-many) relationship.Final Clinic relationshipsEntityCardinalityRelationshipCardinalityEntityClinic1..1Has1..MStaff1..MRegisters1..MPet1..1Schedules1MAppointment1..1IsContactedBy1..MPetOwnerFinal Veterinary RelationshipsEntityCardinalityRelationshipCardinalityEntityVeterinary1..1Performs1..MExaminationFinal Pet Owner RelationshipsEntityCardinalityRelationshipCardinalityEntityPetOwner1..1Owns1..MPet1..1Pays1..MInvoice1..1Attends1..MAppointmentFinal Pet RelationshipsEntityCardinalityRelationshipCardinalityEntityPet1..1Undergoes1..MExamination1..1Attends1..MAppointmentFirst draft Entity Relationship Data ModelSecond draft Entity Relationship Data ModelFinal Entity Relationship Data ModelTask 2 NormalizationNormalization is a technique for producing a set of card 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), an d 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 slender 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 precisely(prenominal) one valueClinic (clinicNo, address, city, state, zipcode,telNo, faxNo, opeingHour)Primary Key clinicNoAlternate Key zipCodeAlternate Key telNoAlternate Key faxNoAlternate Key opeingHourclinicNoaddresstelNoopeningHourC001Enfield503-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 adjustment of the Clinic table is not in 1NFMore than one value, so not in 1NFConverting to 1NFTo convert this version of the Clinic table to 1NF, we give rise 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 quill call of the Clinic table. The primary advert 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 tableClinic (Not 1NF)clinicNoaddresstelNoopeningHourC001Enfield503-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-00 take out telNo column and create a new column called telNo in the new tableTake copy of clinicNo column to new table to become contradictory mainstayClinicTelephone (1NF)clinicNotelNoC001503-555-3618C001503-555-2727C001503-555-6534C002206-555-6756C002206-555-8836 C003212-371-3000C004206-555-3131C004206-555-4112C0058502333C0064650000Clinic (1NF)clinicNoaddressopeningHourC001Enfield9.00-21-00C002Islington9.00-21-00C003Hackney9.00-21-00C004Holloway9.00-21-00C005Chingford9.00-21-00C006Leyton9.00-21-00Second 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 determine in each non-primary-key column can be worked out from determine in all columns that confine 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 co lumns.staffNonamelastName profitsclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005ArtPeters45822C003Hackney8502333S006TommyVerciti65000C004Holloway4650000Values 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)staffNonamelastNamesalaryclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005ArtPeters45822C003Hackney8502333S006TommyVerciti65000C004H olloway4650000 live on column to new tableTake copy of clinicNo column to new table to become primary keyClinic (3NF)clinicNoclinicAddresstelNoC001Enfield5035553618C001Enfield5035553618C002Islington206555675C002Islington206555C003Hackney8502333C004Holloway4650000Staff (3NF)staffNonamelastNamesalaryclinicNoS001TomAdams25000C001S002SallyDaniels35222C001S003MaryChin5200C002S004SallyStern5000C002S005ArtPeters45822C003S006TommyVerciti65000C004Primary keyBecomes contrasted keyBecomes candidate keyBecomes primary keyTask 3Using 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 dataClinic Tablecreate table clinic(clinicNO int not null primary key,telNo varchar(255),address varchar(255),) qualify table clinicadd clinicName varchar (255)alter table clinicadd openingHour varchar(255)alter table clinicadd eTelNo varchar (255) lay in into clinicvalues (01,2863015,Darwin Avenue,8.00-21.00,2863000,Enfield) inject into clinicvalues (02,4650001,John David Avenue,8.00-21.00,2868000,Islington) stick in into clinicvalues (03,4278926,King Arthur Avenue,8.00-21.00,2867000,Hackney) get in into clinicvalues (04,2682365,Paul Mac Avenue,8.00-21.00,2866000,Holloway) enclose 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 ownercreate table petowner(ownerID int not null primary key,oFName varchar (255),oLName varchar (255),clinicNo int distant 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 peto wnervalues (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 Tablecreate 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 Tablecreate 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 Tablecreate 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 forei gn key references clinic (clinicNO)alter table appointmentalter column aDate dateinsert into appointmentvalues (01,2 October 2010,10.00,1,1,1,1)insert into appointmentvalues (02,3 November 2009,10.35,2,2,2,2)insert into appointmentvalues (03,4 December 2009,13.00,3,3,3,3)insert into appointmentvalues (04,5 January 2010,15.00,4,4,4,4)insert into appointmentvalues (05,6 Feb 2010,18.00,5,5,5,5)insert into appointmentvalues (06,7 March 2010,9.00,6,6,6,6)Invoice Tablecreate table invoice(invoiceNo int not null primary key,ownerid int foreign key references petowner (ownerid),amt int)alter table invoiceadd examNo int foreign key references examination (examNo)insert into invoicevalues (01,01,500,1)insert into invoicevalues (02,02,2000,2)insert into invoicevalues (03,03,400,3)insert into invoicevalues (04,04,300,4)insert into invoicevalues (05,05,1500,5)insert into invoicevalues (06,06,750,6)Veterinary Tablecreate table veterinary(veterinaryId int not null primary key,name varchar (255),Ad dress varchar(255),hTelNo varchar(255),mTelNo varchar(255),clinicBranch varchar (255),specialise varchar(255))select * from veterinaryalter table veterinaryadd spspecialise varchar(255)alter table veterinarydrop column clinicBranchalter table veterinaryadd clinicNo int foreign key references clinic (clinicNo)insert into veterinaryvalues (01,Jean,High Street Way 43,6358264,7595865,1,dog)insert into veterinaryvalues (02,Robbin,Lower Downtown 2,6582354,7362548,2,cat)insert into veterinaryvalues (03,Ricky,Market Ville Road,4582356,7586523,3,rabit)insert into veterinaryvalues (04,Rowan,Little China Road2,4582653,7263158,4,dog)insert into veterinaryvalues (05,Laksh,Havana Roadway,8596564,7236458,5,rabit)insert into veterinaryvalues (06,Hans,Talipos Road,4625687,7859584,6,cat)Task 4Set-up and test all of the following queries using Structured interrogate Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have dev eloped.Query caput 1Display the names and address of the branches of Petcare and the name of all veterinary doctors working at each of the branches. Any specialism (s) of the veterinary doctors should also show.Using SQL CodeSELECT clinic.clinicName AS Branches Name, clinic.address AS Branches Address, veterinary.name AS Veterinary Doctor Name,veterinary.specialise AS Veterinary SpecialismFROM clinic INNER JOINveterinary ON clinic.clinicNO = veterinary.clinicNoThe ResultUsing Query DesignerThe ResultQuery Question 2Display all the appointments for the whole of the Petcare organization. This should be ordered 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.Using SQL codeSELECT clinic.clinicName AS Branches Name, veterinary.name AS Veterinary Doctor Name, appointment.aDate A S Appointment Date,appointment.aTime AS Appointment Time, pet.petName AS Pet Name, pet.type AS Pet Type, pet.breed AS Pet BreedFROM appointment INNER JOINclinic ON appointment.clinicNO = clinic.clinicNO INNER JOINveterinary ON appointment.veterinaryId = veterinary.veterinaryId AND clinic.clinicNO = veterinary.clinicNo INNER JOINpet ON appointment.petNo = pet.petNo AND clinic.clinicNO = pet.clinicNoORDER BY Appointment DateThe ResultUsing Query DesignerThe ResultTask 5Explain any assumptions you have made when analyzing, conniving and implementing the above database, justify the approach you have taken and explain any alternative approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work.Determine candidate, primary and alternate key attributesThis step is concerned with identifying the candidate key for an entity and then selecting one to be the primary key. In the process of identifying primary keys, note whether an entity is strong or weak.In trying to identify candidate keys, I observe that the clinic number for the Clinic entity, the veterinary number for the veterinary entity, the invoice number for the Invoice entity are unique for the entire practice. On the other hand, the owner number for the PetOwner entity, the pet number for the Pet entity, are only unique for a particular clinic. Its not uncommon for a company to give different offices a degree of local autonomy. However, in a centralized database system its sometimes more appropriate to have uniqueness throughout the company. In discussion with the PetCare management, its agreed that all numbers should be allocated across the entire practice, as opposed to each branches. If this had not been the decision, it would have been necessary to add the clinic number to those numbers only unique within each clinic to gain uniqueness across the practice. With this in mind, I have now identify the primary keys and foreign key.Check model for redundanc yAt this point, I have a logical data model for Petcare. However, the data model may contain some redundancy which should be removed. More specically, I have to(1) Re-examine one-to-one (11) relationships.(2) Remove pleonastic relationships.(3) One-to-one (11) relationshipsRedundant relationshipsThere are a number of relationships between PetOwner, Pet, Clinic, and Appointment, and a closer examination is useful to identify any redundant relationships. First of all, note that the PetOwner/Pet entities have mandatory participation in the POAttends/PAttends/Owns relationships, and that a PetOwner may own many pets. Therefore, for any given Appointment we can identify the Owner through the POAttends relationship, but we cannot then identify the Pet through the Owns relationship. However, for any given Appointment, we can identify the Pet through the PAttends relationship and for any given Pet we can identify the PetOwner through the Owns relationship, which suggests that the POAttends relationship is redundant. In a similar way, through the PAttends relationship we can identify the Pet, and through the Registers relationship we can identify the Clinic involved in the Appointment, which suggests the Schedules relationship is also redundant.Note that the IsContactedBy relationship between Clinic and PetOwner also appears to be redundant. However, PetCare notes the details of pet owners when they first make contact and only obtains the details of pets at the first appointment, and so the IsContactedBy relationship is retained.Check business rulesBusiness rules are the constraints that I have inspect in order to protect the database from becoming inconsistent. Of the six types of business rules, four were identified in previous steps and documented in the above. I call back the remaining two here referential integrity and other business rules.Referential integrityThere are two issues to consider here(1) Identify whether nulls are allowed for the foreign key. In ge neral, if the participation of the child table in the relationship is mandatory, then the strategy is tha