Database Design And Development For Petcare Veterinary Computer Science Essay

Database Design And Development For fondlec be veterinary surgeon Com projecter wisdom EssayDraw an Entity descent Data Model that describes the content and mental synthesis of data held by favoriteCare. Specify the cardinality ratio and participation constraint of separately human blood figure.Database Development ProcessThe exploit is divided into four main st epochs requirements inst all(prenominal)ation, conceptual dumbfounding, perspicuous modeling and physical modelling. The techniques procedured in the maturation 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 processRequirements ElicitationRequirements elicitation involves establishing the happen upon technical requirements for a database system comm lonesome(prenominal) through bodal and in rowal interaction amid developers and organizational stake styleers 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 eldest things that must be d cardinalness in any information systems count on is to place the relevant stakeholders. A stakeholders classify is any social group within and with reveal the organization that potentially may influence the successful use and impact of the database system.Veterinary bear upon Use the database to for managing intercession information of dearie lag keep track of animal and assigningRegulators 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 future(a) is a list of proposed requirements for systemThe database should be capable of supporting the side by side(p) transaction fashion and maintain temp ers recording the details of Petcare Pets clinics and the members of mental faculty at for all(prenominal) one clinic. raise and maintain records recording the details of deary possessors.Create and maintain the details of embraces.Create and maintain records recording the details of the types of treatments for flatters.Create and maintain records recording the details of enquirys and treatments given to pets.Create and maintain records recording the details of nonices to pet owners for treatment to their pets.Create and maintain pet owner/pet denominations at each clinic.Data requirementsPetCare ex-servicemanerinary surgeryPetcare has vi medium sized veterinary surgery clinics across London. The details of each clinic include channelress of branch, environ reduce, opening hours and emergency contact call up number. Assuming that each clinic has a number of faculty for example vets, nurses, secretaries and cleaners. provideThe details stored on each member of st aff include the staff lay down, apportion, home telephone number and mobile telephone number.Pet ownersWhen a pet owner first contacts a clinic of Petcare the details of the pet owner are preserve, which include name, augmentress, home telephone number and mobile telephone number.PetsThe details of the pet requiring treatment are noted, which include a put number, type of pet, age and sex. runsWhen 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 spacious description of the outcome of the examination consequences.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. chroniclesThe pet owner is responsible for the cost of the treatment given to a pet. The owner is handbilld for the treatment arising from each examination, and the details recorded on the poster include the account statement number, invoice date, owner number, owner name and full cast upress, 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. additive data is withal recorded on the payment for example cash, character reference card or check. participationsIf the pet requires to be seen by the vet at later date, the owner and pet are given an fight. The details of an designation are recorded and include an appointment number, owner name, date and time. exploitation the logical database design methodologyEntity-Relationship modeling is a top- down lift to database design. We begin ER modeling by citeing the important data (called entities) and relationships betwixt the data that must be represented in the model. We then add more details such as the information we want to hold most the entities and relationships (called attributes) and any constraints on the entities, relationships, and attributes. station entitiesIdentify entitiesA range 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 direct to represent in the database.Entity nameDescriptionOccurrenceClinicVeterinary clinicsOne or more Petcare clinics determined throughout LondonStaffGeneral term describing all staff employed by PetcareEach member of staff works at a peculiar(prenominal) clinicPetOwnerOwners of pets interpreted to PetcareOwner takes his/her pet to a particular clinicPetSick animal seek treatment to the c linicOne or more animal are taken to the clinicExaminationTreatmentInvoice adjustmentPetTreatmentAttributesThe particular properties of entities are called attributes. Attributes represent what we want to know about entities.Single-Valued attributesThe 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 attributesAn attributes that represents a value that is derivable from the value of a colligate attributes, or inured of attributes, no necessarily in the same entity. several(prenominal) 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 . come along is not conventionalismly stored in a database because it would ache to be updated regularly. On the other hand, as date of birth never changes and age fuel be derived from date of , date of birth is stored instead, and age is derived from DOB attribute, when needed.Identify relationshipsHaving identifies the entities near step is to identify all the relationships that exist between these entities. A relationship is a set of connexions between participating entities. As with entities, each association should be uniquely distinctive within the set. A uniquely identifiable associations is called a relationships occurrenceClinic RelationshipsEntityRelationshipEntityClinicHasVeterinaryRegistersPetSchedules interlockingIsContactedByPetOwnerVeterinary RelationshipsEntityRelationshipEntityVeterinaryPerformsExaminationPet Owner RelationshipsEntityRelationshipEntityPetOwnerOwnsPetPaysInvoiceAttendsAppointmentPet RelationshipsEntityRelationshipEntityPetUndergoesExaminationA ttendsAppointmentCardinality Ratio of PetCare databaseCardinality or degree concerns the number of instances convoluted in a relationship. A relationship butt end be said to be all a 11 (one-to-one) relationship, a 1 M (one-to-many) relationship, 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..MAppointment scratch draft Entity Relationship Data ModelSecond draft Entity Relationship Data ModelFinal Entity Relationship Data Model assign 2 NormalizationNormalizati on is a technique for producing a set of plug-in with desirable properties that support the requirements of a user or union. thither 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 chromatography chromatography columns of a sidestep.First normal forms (1NF)Only first normal form (1NF) is little in creating appropriate gameboards for relational databases. All the subsequence normal forms are optional. A prorogue in which the intersection of every column and record contains unaccompanied one valueClinic (clinicNo, address, city, state, zip canon,telNo, faxNo, opeingHour)Primary mainstay 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- 00C003 hackney coach212-371-30009.00-21-00C004Holloway206-555-3131, 206-555-41129.00-21-00C005 bring upgford85023339.00-21-00C006Leyton46500009.00-21-00This version of the Clinic circumvent is not in 1NFMore than one value, so not in 1NFConverting to 1NFTo convert this version of the Clinic skirt to 1NF, we take in separate postpone called ClinicTelephone to hold the telephone number of clinics, by removing the tellNo column from the Clinic accede along with a duplicate of the old winding primordial of the Clinic table. The primary underlying for the in the raw 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-41 129.00-21-00C005 chin upgford85023339.00-21-00C006Leyton46500009.00-21-00Remove telNo column and piddle a new column called telNo in the new tableTake copy of clinicNo column to new table to become outside keyClinicTelephone (1NF)clinicNotelNoC001503-555-3618C001503-555-2727C001503-555-6534C002206-555-6756C002206-555-8836C003212-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 yen from update anomalies. A table that is already in 1NF and which the determine in each non-primary-key column hatful be worked out from set in all columns t hat make up the primary key.Third normal form (3NF)Although 2NF table set about less circumlocution 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 value in all non-primary key columns can be worked out from only the primary key column and no other columns.staffNonamelastName lucreclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005 subterfugePeters45822C003Hackney8502333S006TommyVerciti65000C004Holloway4650000 determine 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 3NFsta ffNonamelastNamesalaryclinicNoclinicAddresstelNoStaffClinic (Not 3NF)staffNonamelastNamesalaryclinicNoclinicAddresstelNoS001TomAdams25000C001Enfield5035553618S002SallyDaniels35222C001Enfield5035553618S003MaryChin5200C002Islington206555675S004SallyStern5000C002Islington206555S005ArtPeters45822C003Hackney8502333S006TommyVerciti65000C004Holloway4650000Move column to new tableTake copy of clinicNo column to new table to become primary keyClinic (3NF)clinicNoclinicAddresstelNoC001Enfield5035553618C001Enfield5035553618C002Islington206555675C002Islington206555C003Hackney8502333C004Holloway4650000Staff (3NF)staffNonamelastNamesalaryclinicNoS001TomAdams25000C001S002SallyDaniels35222C001S003MaryChin5200C002S004SallyStern5000C002S005ArtPeters45822C003S006TommyVerciti65000C004Primary keyBecomes unlike keyBecomes prospect keyBecomes primary keyTask 3 victimisation a Database Management System (DBMS) of your choice, set up all the above normalized tables, and populate them with well-designed t est data (minimum 5 records per table). Provides printouts of all tables.Reasonable supposal may be made with regard to dataClinic Tablecreate table clinic(clinicNO int not abortive primary key,telNo varchar(255),address varchar(255),) diversify table clinicadd clinicName varchar (255)alter table clinicadd openingHour varchar(255)alter table clinicadd eTelNo varchar (255) barge in into clinic set (01,2863015,Darwin Avenue,8.00-21.00,2863000,Enfield) enter into clinic set (02,4650001,John David Avenue,8.00-21.00,2868000,Islington) enroll into clinicvalue (03,4278926,King Arthur Avenue,8.00-21.00,2867000,Hackney) barge in into clinic determine (04,2682365,Paul Mac Avenue,8.00-21.00,2866000,Holloway) inaugurate into clinic determine (05,4682685,James Micheal Avenue,8.00-21.00,2865000,Chingford) confine into clinicvalues (06,2863015,Benaoit stamp Avenue,8.00-21.00,2864000,Leyton)Pet ownercreate table petowner(ownerID int not null primary key,oFName varchar (255),oLName varchar (255),c linicNo 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 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,Terr oer,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 examinatio nvalues (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 foreign 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 ex amination (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),Address varchar(255),hTelNo varchar(255),mTelNo varchar(255),clinicBranch varchar (255),specialise varchar(255)) pick out * 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 elbow room 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)inse rt 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 Query Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed.Query research 1Display the names and address of the branches of Petcare and the name of all veterinary doctors working at each of the branches. Any forte (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 proceedsUsing Query DesignerThe ResultQuery Question 2Display all the appointments for the whole of the Petcare organization. This should be cabareted by date. The resul t 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 AS 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 5 rationalise any assumptions you have made when analyzing, designing and implementing the above database, justify the nest you have taken and explain any alternati ve approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work. mend 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 singularity 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 redundancyAt 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 unnecessary 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 bare(a) 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 logical argument rulesBusiness rules are the constraints that I have impose in o rder 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 consider the remain two here referential integrity and other business rules. denotive integrityThere are two issues to consider here(1) Identify whether nulls are allowed for the foreign key. In general, if the participation of the child table in the relationship is mandatory, then the strategy is tha