Entity Relationship Data Model And Normalization Computer Science Essay

Entity Relationship data Model And publicization Computer skill EssayDraw an Entity Relationship information Model that describes the content and social strategy of the data held by kissc ar. Specify the cardinality ratio and participation constraint of apiece relationship grapheme.AnswerDraw an Entity Relationship Data ModelTo institution the database musical arrangement for Petcare, we ask to draw and Entity Relationship diagram to show any and entities and the relationship among the enteritis. The ERD models the static relationships amongst this information that are preserved and maintained by the database. The ERD shows how items of data impact, static exclusivelyy, to from each one(prenominal) other.1.1.1 Entities of the ERD initiatory tonicity, we bring to define the entities in the database.According the requirement of the Petcare, the at a lower place entities should be involved in the database system to evidence the Petcares information Information of the coddle included the type, breed, sex, age and their possessor Information of the favourite proprietor included their name, address, home teleph ace enumerate and wandering teleph atomic sum 53 modus operandi Information included the pet the conflict is for, the owner of the pet, the particular tree branches of Petcare, the attending veterinary ex-serviceman and the attend and cartridge holder of the accommodation. Information of the veterinary Veterinarian included their name, address, home telephone number, mobile telephone number, which branch they report to and their specialize. The turn outcome of the adjustment which included the prescription and the set on firege do.1.1.2 Entities relationshipNext step is to define the relationship of each entity. The relationship included Participation Constraint and Cardinality proportion.Participation Constraint there are two types of participation constraint named Total and Partial participation.A Total participati on (mandatory participation) constraint specifies that every member in the entity essential participate as a member of a nonher tie-uped entity in the generalization.A Partial participation (optional participation) constraint specifies that a member of the entity does not belong to any member of some other(prenominal) linked entity in the generalization.Cardinality RatioThree types of cardinality is described as beneathOne-to-one (11) Relation amongst two entities which is linked each other with their immemorial key(s) solely.Many-to-many (MN) Relation in the midst of two entities which is linked each other without any primary key. It besides have the chance to develop the redundancy error.Therefore it is better to chisel in into two relations such as one-to-many and many-to-one.One-to-many (1M) or Many-to-one (M1) Relation between two entities which is linked each other from the primary key(s) to foreign key(s).The relationship of Pet The pet was raised by the owner e ach pet should unaccompanied have 1 owner still when the owner earth-closet keep more beca function 1 pet. The owner alike dissolve keep difference kind of pets. There fore, the relationship of Pet and possessor is Total and one to many.Relationship of Owner The pet owner would dedicate appointment with the Veterinarian. In the real case, an owner would make more accordingly 1 appointment, also the Veterinarian also not handle 1 case only. A relational database is not allowed this condition. To solve the step forward another entity Appointment was added between Owner and Veterinarian. The Owner may make the appointment before visit the veterinarian, also they also rouse walk-in hardly without appointment. In order to simple the database programme, we see the walk-in also as a vernal appointment. Therefore, the relationship of Owner and Appointment is Total and one to many.Relationship of Appointment the appointment was framed by the pet owner and was handled be th e Veterinarian. However, some Veterinarian may respond to the special case and would not handle the appointment. Therefore, the relationship of Appointment and Veterinarian is Partial and many to one relationship.Relationship of Veterinarian On the in a higher place we already mentioned a Veterinarian would attend more so one appointment. Also the Veterinarian would issue more then 1 diagnosing. Diagnosis is the outcome of the appointment which was issued by the veterinarian. The relationship of Veterinarian and Diagnosis is Total and one to many.1.1.3 Entity Relationship Diagram (ERD)According the above entities and relationship architectural plan we got the inaugural ERD as down the stairs The ERD was showed the logical database design of the Petcare. The ERD identifies a list of data elements that must be included in the database.Fig. 1. The ERD of Petcare.Task 2Produce the sequeling shapeized knock backs clearly indicating the primary and foreign keys.AnswerNormalizat ionAfter complete the logical design of the database and draw the ERD, we can start the corporal design of the database. The origin affair is to create the card and define the data-set enquire to store in the database. A good database design should make the content of each carry over is only touch ond to its own entity. In order to endure this requirement, normalization surgery should be implemented.The normalization process needs step by step to transform the database and each step have specify name, calledUn-normal Form UNF commencement exercise Normal Form 1NFSecond Normal Form 2NFThird Normal Form 3NFBoyce-Codd Normal Form BCNFThe below steps should be carried out for the normalization.Collect the data-set modify the un-normalized form tables into inaugural normal form interpret the first normal form tables to arcminute normal formTransform the endorse normal form tables to ordinal normal form jump Normal Form (1NF)According the ERD design and the requirement of Petcare, the put offs of the database were created as below also some attributes were added to indentify the data-sets, e.g. PetID, which attributes also the essential of the table.Fig. 2. The Un-normal Form tableThe above tables were included all requested data and information requested by Petcare. However, some of attributes are the similar in difference tables. The tables are a un-normal form table. We need to make the content of each table is only contactd to its own entity. Therefore the normalization process should be implemented.The first step is transforming the un-nornalized form table into first normal form. The definition of first normal form is all non-key attributes in relation is if, and only if, functionally dependent upon primary key.According the definition of first normal form, we should avulse the non-key attributes which are not denote to the primary key and create another table for this attribute.For example in the table Veterinarian, the Branch and Specia lise are not related to the Primary key, so we need avulse these attributes and create another table, Branch Specialise. Another table Diagnosis also the same issue.Fig. 3. Normalized tableFig. 4 Normalized table 2After the process of first normalized, we got the First Normal Form table as belowFig. 5. First Normal Form tableSecond Normal Form (2NF)The second step is transfer first normal form to second normal form. The definition of second normal form is all relations are, and only if, it is in first normal form and every non-key attribute are fully functionally dependent on the primary key.After second normalization, we got the below tablesFig. 6. Second Normalized tablesThird Normal Form (3NF)The definition of third normal form is all relations are if, and only if, it is in second normal form and every non-key attributes in nor-transitively dependent on the primary key. Because the second normalized tables already meet the equipment. Therefore, it is third normal form.Primary Ke y and unknown KeyThe next step to design the physical database is de nervous strainate the Primary Key and the Foreign key. This step also defining the relationship of each table. The Primary Key is used to identify the record in each table. The Foreign Key is the key attribute to link other table.The final normalized table with details relationship, Primary key and Foreign key were showed on the below diagram.Fig. 7. Final table designData mental lexiconThe last step of design the database is define the property of each attribute. The property of attribute is design the label of the attribute. A good steganography design is very all important(p) because a good coding design can let use lento to interpret and take little mistake when enter the data, and also unyielding the usability and extensibility of the database.A good coding design should meet below requirementsUniqueness only one tax of the code may be applied to an entity.Expandability must have able space for t he entry of new items.Conciseness should use the minimum number of searacters to define each item.Uniform size format no accompaniment of prefixes or suffixes.Simplicity simple to apply and understand.Versatility easy to modify for changes in conditions.Sortability may not be the code itself as long as it can be correlated with a sortable code.Stability should not need regular updating as this is costly.Meaningful in some share it is useful for the code to reflect the flameacteristics of the coded entities.Operability the code should be sufficient to meet the current and future needs of data identification but also minimise the clerical and system resources required to operate it.Other then the coding design, the sequence of table create also very important as the table may has relation with other tables. We should first create the table which has not relate to other tables, then create the table relate to this table. The sequence of table create list as the sequence o f the Data dictionary.According the above requirements, the tables and attributes were created and the details were listed on the Data Dictionary belowData Dictionary of PetcardTable anticipateField Name display caseSizeStatusDescriptionOwnerOwnerID flash6PKThe ID of ownerO_Name cleaning woman30not abortiveName of ownerO_Addressvar womanhood100 slide fastenerAddress of ownerO_Phonechar15postal codePhone number of ownerO_ promptchar15NULLMobile number of ownerPetTypeTypeIDchar1PKThe ID of pet typeP_Typechar15not NULLname of pet type strivingBreedIDchar2PKThe ID of breed typeB_typechar15Not NULLName of Breed typeTypeIDchar1FKrelate to PetTypePetPetIDchar6PKThe ID of the PetOwnerIDchar6FKrelate to OwnerPetNamechar20Not NULLName of the pet hinge uponchar1Not NULLsex of the petAgenumeric2Not NULLage of the petBreedidchar2FKrelate to BreedBranchBranchIDchar1PKThe ID of branchB_Namechar15Not NULLName of branchB_Addresschar50Not NULLAddress of branchB_Phonechar15Not NULLPhone number of br anch strong pointSpeIDchar2PKThe ID of specializeSpecialisechar10Not NULLName of the specializationVeterinarianVetIDchar3PKThe ID of veterinariaV_Namechar30Not NULLName of veterinariaV_Addressvarchar100Not NULLAddress of veterinariaV-Phonechar15Not NULLPhone number of veterinariaV_Mobilechar15Not NULLMobile number of veterinariaSpecIDchar2FKrelate to SpecialisationBranchIDchar1Not NULLThe branch of the veterinaria is workingAppointmentAppIDchar10PKThe ID of appointmentVetIDchar3FKrelate to VeterinarianOwnerIDchar6FKrelate to ownerBranchIDchar1FKrelate to BranchPetIDchar6FKrelate to PetDatedate8Not NULLdate of appointmentTime succession4Not NULLtime of appointmentDrug TypeD_TypeIDchar2PKThe ID of Drug TypeD_Typechar20Not NULLName of drug typeDrugDrugIDchar4PKThe ID of drugD_typeIDchar2FKrelate to DrugTypeDrugNamechar30Not NULLName of the drug categorychar20Not NULLHow to use the drug blockchar2Not NULLNumber of day to use the drugprescription drugPrescIDchar10PKThe ID of Prescription Drug1char4FKrelate to DrugDrug2char4FKrelate to DrugDrug3char4FKrelate to DrugDiagnosisDiagIDchar6PKThe ID of diagnosisVelIDchar3FKrelate to VelerinarianPrescIDchar10FKrelate to PrescriptionPriceNumeric6Not NULLThe price of diagnosisTask 3Using a Database Management System (database management system) set-up all of the above normalized tables, and populate them with well-designed analyze data..AnswerCreate tables on DBMSAfter completely designed the database, we should start to create it. Concerning to popularity and easy to use, we determined to use Microsoft Access as the Database Management System (DBMS) of the Petcare database. We should use SQL rule to create the tables, attributes and relationships of tables.Create tablesThe SQL command of create tables was lists belowCreate the table Owner pee-pee TABLE Owner(OwnerID CHAR(6) PRIMARY KEY,O_Name CHAR(30),O_Address VARCHAR(100),O_Phone CHAR(15),O_Mobile CHAR(15) )Create the table PetTypeCREATE TABLE PetType(TypeID CHAR(1) PRIM ARY KEY,P_Type CHAR(15) )Create the table BreedCREATE TABLE Breed(BreedID CHAR(2) PRIMARY KEY,B_type CHAR(15),TypeID CHAR(1) REFERENCES PetType(TypeID) ON DELETE come down ON UPDATE CASCADE )Create the table PetTypeCREATE TABLE Pet(PetID CHAR(6) PRIMARY KEY,OwnerID CHAR(6) REFERENCES Owner(OwnerID) ON DELETE CASCADE ON UPDATE CASCADE,PetName CHAR(20),Sex CHAR(1),Age NUMERIC(2)BreedID CHAR(2) REFERENCES Breed(BreedID) ON DELETE CASCADE ON UPDATE CASCADE )All the tables also used the similar SQL command to create, we would not retroflex all there. After all tables, attributes and relationships were created. The whole table diagram was showed as below figure.Fig. 8. Tables in MS AccessTest DataAfter the table creation finished. We should input the try out data to the database to test the database functioned as expended. We would input at least 5 records to each table.The below screen showed the test data of all tablesFig. 9. Table Owner, Pet, PetType, BreedFig. 10. Table Veterinaria n, distinction, Appointment, BranchFig. 11. Table Prescription, Diagnosis, Drug, DrugTypeThe Printout of tables please refers to appendix.Task 4Set-up and test all of the following queries using Structured Query Language (SQL).AnswerSet-up test queriesIn order to test the database design and data input correct, we need to interrogatory the data form the database. The below 2 case can test the database meet the Petcares requirements.Query 1 requisiteDisplay the name calling and addresses of the branches of Petcare and the names of all the veterinary doctors working at each of the branches. some(prenominal) specialism(s) of the veterinary doctors should also be shown.This is a very general wonder in database, to goal the requirement we need to the joint the Branch, Veterinarian and Specialization 3 tables and select the requested data. The dubiousness statement and result were listed belowSELECTBranch.b_name AS Branch,Branch.b_address AS Address,Veterinarian.v_name AS VetName,Sp ecialization.specialise AS SpecializationFROM(Branch versed colligate Veterinarian ON Branch.branchid=Veterinarian.branchid) inner JOIN Specialization ON Veterinarian.SpecID=Specialization.SpecIDORDER BY Branch.b_nameFig. 12. Result of Query 1.Query 2RequirementDisplay 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 creature the appointment is for, the type of animal and the breed of the animal.Case two is more difficulty we need to join Appointment, Branch, Veterinary, Pet, PetType and Breed total 6 tables. The query is very complex and easy to make mistake. The query statement and query results were showed belowSELECTBranch.b_name AS Branch,Veterinarian.v_name AS VetName,Appointment.date,Appointment.time,Pet.petname AS PetName,PetType.P_Type AS Type,Breed.breed AS BreedFROM((((Appointment INNER JOIN Veterinarian ON Appointment.vetid=Veterinarian.vetid)INNER JOIN Branch ON Veterinarian.branchid=Branch.branchid)INNER JOIN Pet ON Appointment.petid=Pet.petid)INNER JOIN Breed ON Pet.breedid=Breed.breedid)INNER JOIN PetType ON Breed.TypeID=PetType.TypeID)ORDER BY Appointment.dateFig. 13. Result of query 2Task 5Explain any assumptions you have made when analyzing, designing and implementing the above database..AnswerAssumption and alternativeEach appointment ID is for one pet only. Same owner bring more than 1 pets to Petcare whould see as a new appointment.One prescription utmost list 3 drugs. Normally 3 drugs are enough for 1 case. If the pet has more than 1 issue need more then 3 drugs, veterinary should open multi prescription for difference type of drugs. E.g. need skin medication Painkiller.Walk-in also sees as a new appointment.Work ImprovementIn this assignment, the database system for PatCare has not included the substance abuser inte rface. The database system is used for store data and the DBMS is used for charabanc the database or it can be sees as a bridge of Database and user interface. DBMS is not designed for user, it mainly use command line to manager the database and query the data. Even some new DBMS system such as MS Access and SQL have GUI but also hard for a normal user to use the database and query data.In order to let users easy to use the database, User user interface is a must. The user interface let user easy to input data, update records and check the records. And also we can per-set some forms and reports for user cross out.The user interface not need very gorgeous or complex. A good user interface is simple, convenient and easy to understand and easy to use. We should design a nett base interface because the net base interface is simple and easy to manage. The most important is the web base interface does not need install additional programs, thus not additional budget for buy other ap plications.