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.
