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.
