Get help now

Veterinary Surgery Database Development Computer Science

  • Pages 11
  • Words 2563
  • Views 379
  • dovnload

    Download

    Cite

  • Pages 11
  • Words 2563
  • Views 379
  • Academic anxiety?

    Get original paper in 3 hours and nail the task

    Get your paper price

    124 experts online

    This assignment is for the Database Design and Development faculty. In this assignment, we will hold to implement informations modeling, informations analysis and informations design techniques to plan and develop a database. First, we need to look into and seek to understand the scenario given. Harmonizing to the given scenario, we will hold to plan a database for the Petcare veterinary surgery. Petcare is a medium sized veterinary surgery with six subdivisions across London. It is given that the Petcare holds records of the animate beings they care for, prescriptions and assignments. Each animate being has a peculiar proprietor. Each physician has specialized in their Fieldss of animate being.

    There are wholly five undertakings in this assignment. The undertakings require us to use informations analysis and design methods to carry through them. We will hold to utilize such methods as entity modeling and standardization techniques. After the information has been analysed and designed, we will necessitate to put up the database and write SQL questions to recover informations from the database.

    In Task 1, we are required to pull an entity relationship informations theoretical account which describes the content and construction of the informations stored by Petcare.

    Undertaking 2 is the undertaking in which we will hold to bring forth the ensuing normalised tabular arraies together with their primary keys and foreign keys.

    Harmonizing to Task 3, we need to put up the normalised tabular arraies from Task 2 utilizing a Database Management System ( DBMS ) and dwell the tabular arraies with trial informations. There need to be at least 5 records per tabular array. Then, we will hold to capture screen shootings of the tabular arraies we have set up.

    After puting up the tabular arraies, Task 4 asks us to put up and trial questions utilizing Structured Query Language ( SQL ) to retreive certain records from the tabular arraies. We will hold to compose SQL codification for the undermentioned questions:

    Display the names and references of the subdivisions of PetCare and the names of all the veterinary physicians working at each of the subdivisions. Any specialism ( s ) of the veterinary physicians should besides be shown.

    Display all the assignments for the whole of the PetCare organisation. This should be ordered by day of the month. The consequence should expose the subdivision the assignment is at, the name of the veterinary physician the assignment is at, the name of the veterinary physician the assignment is with, the day of the month and clip of the assignment, the name of the animate being the assignment is for, the type of animate being and the strain of the animate being.

    Then, we will hold to supply screen shootings of the ensuing end product produced when the questions are run in the database.

    Undertaking 5 is the undertaking in which we will hold to explicate premises we have made during the class of the assignment analysing, planing and implementing the database, provide grounds as to why we have chosen the attack we have taken and explicate other alternate attacks we could hold taken to transport out the above undertakings from 1 to 4. We will besides necessitate to discourse alterations we could do to better our work.

    Table of Contentss

    No Description Page

    1 Cover Page 1

    2 Confirmation and Statement 2

    3 Introduction 3-4

    4 Table of contents 5

    5 Task 1 6-11

    6 Task 2 12-21

    7 Task 3 21-28

    8 Task 4 29-33

    9 Task5 34-36

    10 Mention 36

    ERD ( Entity Relationship Diagram )

    Owner

    Prescription

    Doctor

    Animal

    Branch

    Appointment

    Relational Schema

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

    Owner – ( Owner ID, Name, Address, Home Telephone, Mobile Telephone )

    Appointment – ( Appointment ID, Doctor ID, Animal ID, Owner ID, Date, Time, Diagnosis,

    Charges, Branch ID )

    Branch – ( Branch ID, Name, Address, Opening hours, Telephone No, Emergency

    Contact No )

    Prescription – ( Prescription No, Drug Type, Periods, Day, Cost, Appointment ID )

    Doctor – ( Doctor ID, Name, Address, Home Telephone, Mobile Telephone, Specialize

    animate being )

    Remark: Description

    Primary Key:

    Cardinality Ratio

    Owner Animals ( 1: Meter )

    Owner Appointment ( 1: Meter )

    Animal Appointment ( 1: Meter )

    Doctor Appointment ( 1: Meter )

    Animal Doctor ( 1: Meter )

    Doctor Prescription ( 1: Meter )

    Branch Doctor ( 1: Meter )

    Entity with premise

    The relationship for these entities is One-to-Many. An animate being has one proprietor and an proprietor can compose more than one animate beings.

    The relationship for these entities is One-to-Many. An proprietor can compose more than one assignment and an assignment has one proprietor.

    The relationship for these entities is One-to-Many. An animate being has more than one assignment and an assignment has one animate being.

    Doctor and Appointment are related with One-to-Many relationship. A physician can compose more than one assignment and an assignment has one physician.

    The relationship for these entities is One-to-Many. A physician can compose more than one prescription and a prescription has one physician.

    Branch and Doctor are related with one-to-many relationship. A subdivision has more than one physician and a physician has one subdivision.

    Content and construction of the informations by Pet attention

    Entity Name: Animal

    Primary Key: Animal ID

    Foreign Key: Owner ID

    Field Name

    Data Type

    Size

    Description

    Animal ID

    Animal Name

    Type

    Breed

    Sexual activity

    Age

    Owner ID

    Var char

    Var char

    Var char

    Var char

    Var char

    Var char

    Var char

    10

    50

    20

    30

    10

    2

    20

    Auto no of animate being ID

    Name of Animal

    Type of animate being

    Breed of animate being

    Male or Female

    Age of Animal

    Auto no of proprietor ID

    Entity Name: Appointment

    Primary Key: Appointment ID

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

    Field Name

    Data type

    Size

    Description

    Appointment ID

    Owner ID

    Doctor ID

    Animal ID

    Date/Time

    Diagnosis

    Charges

    Branch ID

    Var char

    Var char

    Var char

    Var char

    Var char

    Var char

    Var char

    Var char

    10

    10

    10

    10

    20

    20

    50

    10

    Auto no of assignment ID

    Auto no of proprietor ID

    Auto no of physician ID

    Auto no of animate being ID

    Date & A ; Time of assignment

    Diagnosis of assignment

    Charges of assignment

    Auto no of Branch ID

    Entity Name: Prescription

    Primary Key: Prescription ID

    Foreign Key: Appointment ID

    Field Name

    Data Type

    Size

    Description

    Prescription ID

    Appointment ID

    Drug Type

    Time periods

    Cost

    Var char

    Var char

    Var char

    Var char

    Var char

    10

    10

    20

    10

    10

    Auto no of prescription ID

    Auto no of assignment ID

    Type of drug

    Timess of drug used to take

    Sum of money to pay

    Entity Name: Doctor

    Primary Key: Doctor ID

    Foreign Key: Branch ID

    Field Name

    Data Type

    Size

    Description

    Doctor ID

    Name

    Address

    Home Telephone

    Mobile No

    Branch ID

    Specialist

    Var char

    Var char

    Text

    Var char

    Var char

    Var char

    Var char

    10

    20

    30

    20

    20

    10

    20

    Auto no of Doctor ID

    Name of Doctor

    Address of Doctor

    Home Telephone no of Doctor

    Mobile no of Doctor

    Branch which have appointed

    Animal that physician specialise

    Entity Name: Branch

    Primary Key: Branch ID

    Foreign Key: –

    Field Name

    Data Type

    Size

    Description

    Branch ID

    Name

    Address

    Opening hours

    Telephone No

    Emergency Contact No

    Var char

    Var char

    Text

    Var char

    Var char

    Var char

    10

    20

    30

    20

    20

    20

    Auto no of Branch ID

    Name of Branch

    Address of Branch

    Opening hours of Branch

    Telephone No of Branch

    Emergency contact no of Branch

    Entity Name: Owner

    Primary Key: Owner ID

    Foreign Key: –

    Field Name

    Data Type

    Size

    Description

    Owner ID

    Name

    Address

    Home Telephone

    Mobile Telephone

    Var char

    Var char

    Text

    Var char

    Var char

    10

    20

    30

    20

    20

    Auto no of Owner ID

    Name of Owner

    Address of Owner

    Home Telephone of Owner

    Mobile Telephone of Owner

    Branch Form

    Doctor ID Doctor Name Specialize Animal

    Branch Form

    Branch ID: _____________ Telephone: ______________

    Name: _____________ Opening hours: ______________

    Address: _____________ Emergency Contact No: _______

    UNF 3. 1NF

    Branch ID Branch ID

    Name Name

    Address Address

    Telephone Telephone

    Opening hours Opening hours

    Emergency Contact No Emergency Contact No

    Doctor ID

    Doctor Name Branch ID

    Specialize Animal Doctor ID

    Doctor Name

    Choose a key Specialize Animal

    Branch ID

    2NF 5. 3NF

    Branch ID Branch ID

    Name Name

    Address Address

    Telephone Telephone

    Opening hours Opening hours

    Emergency Contact No Emergency Contact No

    Branch ID Branch ID

    Doctor ID Doctor ID

    Doctor ID Doctor ID

    Specialize Animal Specialize Animal

    Doctor Name Doctor Name

    Optimization

    ( Branch ) ( Doctor )

    Branch ID Doctor ID

    Branch Name Doctor Name

    Address Specialize Animal

    Telephone Branch ID

    Opening hours

    Emergency Contact No

    Data Model

    Doctor

    Branch

    Appointment Form

    Appointment Form

    Appointment ID: _______ Doctor ID: _________

    Owner ID: _______ Doctor Name: _________

    Owner Name: _______ Animal ID: _________

    Date/Time: _______ Animal Type: _________

    UNF 3. 1NF

    Appointment ID Appointment ID

    Animal ID Animal ID

    Animal Type Animal Type

    Owner ID Owner ID

    Owner Name Owner Name

    Doctor ID Doctor ID

    Doctor Name Doctor Name

    Date/Time Date/Time

    Choose a key

    Appointment ID

    4. 2NF 5. 3NF

    Appointment ID Appointment ID

    Animal ID Animal ID

    Animal Type Owner ID

    Owner ID Doctor ID

    Owner Name Date/Time

    Doctor ID Animal ID

    Doctor Name Animal Type

    Date/Time Owner ID

    Owner Name

    Doctor ID

    Doctor Name

    6. Optimization

    ( Appointment ) ( Animal ) ( Owner ) ( Doctor )

    Appointment ID Animal ID Owner ID Doctor ID

    Animal ID Animal Type Owner Name Doctor Name

    Owner ID

    Doctor ID

    Date/Time

    7. Datas Model

    Animal

    Appointment

    Doctor

    Owner

    Prescription Form

    Prescription Form

    Prescription ID: ________

    Animal ID: ________ Animal Type: ________

    Owner ID: ________ Owner Name: ________

    Doctor ID: ________ Doctor Name: ________

    Appointment ID Drug Type Period Cost

    UNF 3. 1NF

    Prescription ID Prescription ID

    Appointment ID Doctor Name

    Animal ID Animal ID

    Animal Type Animal Type

    Owner ID Owner ID

    Owner Name Owner Name

    Doctor ID Doctor ID

    Doctor Name

    Drug Type Prescription ID

    Period Appointment ID

    Cost Drug Type

    Time period

    Choose a key Cost

    Prescription ID

    4. 2NF

    Prescription ID Prescription ID

    Doctor Name Appointment ID

    Animal ID Cost

    Animal Type Period

    Owner ID Drug Type

    Owner Name

    Doctor ID

    3NF

    Prescription ID Prescription ID

    Owner ID Appointment ID

    Animal ID Drug Type

    Doctor ID Cost

    Time period

    Owner ID

    Owner Name

    Animal ID

    Animal Type

    Doctor ID

    Doctor Name

    Optimization

    ( Prescription ) ( Animal ) ( Owner ) ( Doctor )

    Prescription ID Animal ID Owner ID Doctor ID

    Animal ID Animal Type Owner Name Doctor Name

    Owner ID

    Doctor ID

    ( Prescription Detail ) ( Appointment )

    Prescription ID Appointment ID

    Drug Type

    Time period

    Cost

    Data Model

    Prescription

    Owner

    Appointment

    Doctor

    Prescription Detail

    Animal

    Table Design View and Datasheet View

    Undertaking Table Design View

    Datasheet View

    Undertaking Table Design View

    Datasheet View

    Undertaking Table Design View

    Datasheet View

    Undertaking Table Design View

    Datasheet View

    Undertaking Table Design View

    Datasheet View

    Undertaking Table Design View

    Datasheet View

    Set-up and test all of the questions utilizing Structured Query Language ( SQL ) . Provide printouts of SQL codification for each question and the end product produced when you run the question in the database you have developed.

    Display the names and references of the subdivisions of Pet Care and the names of all the veterinary physicians working at each of the subdivisions. Any specialism ( s ) of the veterinary physicians should besides be shown.

    SELECT b.NAME AS BranchName, b.Address, d.name AS DoctorName, d.Specialist

    FROM Branch B, physician vitamin D

    WHERE b.BranchID = d.BranchID

    Order BY b.Name

    Display all the assignments for the whole of the Pet Care organisation. This should be ordered by day of the month. The consequence should expose the subdivision the assignment is at, the name of the veterinary physician the assignment is at, the name of the veterinary physician the assignment is with, the day of the month and clip of the assignment, the name of the animate being the assignment is for, the type of animate being and the strain of the animate being.

    SELECT B. [ Name ] AS BranchName, b.Address, an. [ Date/Time ] , d. [ Name ] AS DoctorName, a. [ Name ] AS AnimalName, a. [ Type ] , a. [ Breed ]

    FROM Animal a, Appointment an, Branch B, Doctor vitamin D

    WHERE b. [ BranchID ] =an. [ Branch ID ]

    AND d. [ Doctor ID ] =an. [ Doctor ID ]

    AND a. [ Animal ID ] =an. [ Animal ID ]

    Order By an. [ Date/Time ]

    Analysis and Premise

    Undertaking 1 is the undertaking where we have to pull an entity relationship informations theoretical account for the Petcare. Therefore, to transport out Task 1, I have to first read the scenario given and seek to derive an apprehension of it. After reading the scenario, I extracted the entities that will be in the database system of Petcare. I deduced which will be entities and which will be the properties of the entities. When I have carried out the analysis of the informations held by Petcare, I have got the undermentioned entities: Animal, Owner, Appointment, Branch, Prescription and Doctor.

    The relationship for Owner and Animal entities is One-to-Many. An animate being has one proprietor and an proprietor can compose more than one animate beings. The relationship for Owner and Appointment is One-to-Many. An proprietor can compose more than one assignment and an assignment has one proprietor. The relationship for Animal and Appointment is One-to-Many. An animate being has more than one assignment and an assignment has one animate being. Doctor and Appointment are related with One-to-Many relationship. A physician can compose more than one assignment and an assignment has one physician. Doctor and Prescription are related with One-to-Many relationship. A physician can compose more than one prescription and a prescription has one physician. Branch and Doctor are related with One-to-Many relationship. A subdivision has more than one physician and a physician has one subdivision.

    I have made the premise that an proprietor can hold more than one animate being and each animate being has more than one assignment. So, proprietor can do one or more assignment. An assignment has a one animate being and physician has many assignments. Animal has more than one physician which that means physician are specialized in their field of animate being. Doctor makes a prescription for one animate being at a clip. So, physician has many prescriptions. . Petcare is a medium sized veterinary surgery with six subdivisions across London. So, there are more than one physician in each subdivision.

    After placing the entities, I determined properties for them in Task 1. The entities I have produced in Task 1 will go tabular arraies in the database. When transporting out Task 2, I have to place the primary keys and foreign keys for the tabular arraies produced. Since the primary key is alone in a tabular array, I decided to hold Animal ID as primary key in Animal tabular array. Similarly, Appointment ID as primary key in Appointment tabular array, Prescription ID in Prescription tabular array, Doctor ID in Doctor Table, Branch ID in Branch tabular array, Owner ID in Owner tabular array. Owner tabular array and Animal tabular array are related with one-to-many relationship. In add-on, Owner ID will be the foreign key in the Animal Table. In Appointment Table, Animal ID, Doctor ID, Branch ID and Owner ID will be the foreign keys and Appointment ID is foreign key in Prescription Table. In the Doctor Table Branch ID is foreign key. After Task 2, I have got tabular arraies and their Fieldss and the types and sizes of the Fieldss.

    Since requires us to normalise the tabular arraies produced in Task 1, I have considered the signifier design of the signifiers likely to be used by Petcare. I assumed that the entities of Petcare are Animal, Owner, Appointment, Branch, Prescription, Doctor. Therefore, I have used the signifiers which focuses on these entities to transport out standardization. The signifiers I have used are Branch Form, Appointment Form and Prescription Form. Branch Form is the signifier which shows the item of Branch, Doctor who are specialized in what animate being. Appointment Form is the signifier which describes the item of Owner, Doctor and Animal. Prescription Form is the signifier which shows the item of Prescription gives to which animate being, given by whom, which drug are given for how many periods and how much it cost. The information theoretical accounts produced from normalizing the signifiers is the same as that of the ERD theoretical account in Task 1 when they are combined. If we carry out Task 1 foremost, there is a chance that we may happen that the normalised informations theoretical account and the ERD do non fit.

    I decided to used Microsoft SQL Server 2005 to put up the database for the Petcare since I am familiar with this DBMS. I created tabular arraies in the database and inserted trial informations in the database. When dwelling the database with the trial information, I inserted meaningful informations since it would be easier to see and understand the database. I defined the field names, informations types, sizes and formats of the Fieldss of the tabular arraies in the design position. Since the undertaking requires that there should at least be 5 records in each tabular array, I inserted a lower limit of 5 records in the tabular array. I defined primary keys and foreign keys in the tabular array. After that, I created a relationship diagram demoing the relationships between the tabular arraies utilizing primary keys and foreign keys. In Task 3, I have presented the tabular arraies I have created both in design position and datasheet position. An alternate attack in puting up the database will be to utilize Microsoft SQL Server or other DBMS of pick.

    When making the question exposing all six subdivisions names and references physician in each subdivision and their specialism in what animate being. Then I created another question demoing Branch Name, Address, and Appointment Date/Time, Doctor Name, Animal Name, Animal Type and Breed.

    In decision, these are the premises I have made when analyzing, planing and implementing the database and the attacks I have taken.

    This essay was written by a fellow student. You may use it as a guide or sample for writing your own paper, but remember to cite it correctly. Don’t submit it as your own as it will be considered plagiarism.

    Need a custom essay sample written specially to meet your requirements?

    Choose skilled expert on your subject and get original paper with free plagiarism report

    Order custom paper Without paying upfront

    Veterinary Surgery Database Development Computer Science. (2016, Dec 04). Retrieved from https://graduateway.com/veterinary-surgery-database-development-computer-science-essay/

    Hi, my name is Amy 👋

    In case you can't find a relevant example, our professional writers are ready to help you write a unique paper. Just talk to our smart assistant Amy and she'll connect you with the best match.

    Get help with your paper
    We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy