Database Design
shay
assessment.docxAppendix 1: Copies of manual records held so far
|
Owner details: First name: Emilio Surname: Ramirez Address: 43 Rashgill Road LOCHMABEN DG12 9FS Telephone number: 01576 321905
|
|
Pet details:
Name: Harif Dog/Cat: DOG - Male
Breed: Cockerspaniel Vet: Christina White
Kennel Cough/
|
|
Pet details:
Name: Sophia Dog/Cat: CAT - Male
Breed: Non-Specific Vet: Christina White
|
|
Owner details: First name: Janette Surname: Sparks Address: 53 Wellington Avenue DUMFRIES DG1 7GN Telephone number: 01387 253986
|
|
Pet details: Name: Misty Dog/Cat: DOG - Female Breed: Non-Specific Vet: Javier Fuentes Kennel Cough/Cat Flu Last Vaccine Date: 07/07/2010
|
Task 1: Appendix 2: Vet details
|
Vet’s Name |
Neil King |
|
Veterinary Practice Name and Address
|
Kings Small Pets Service Dumfries Road LOCHMABEN DG3 7RD |
|
Telephone Number |
01337 457982 |
|
Vet’s Name |
Christina White |
|
Veterinary Practice Name and Address
|
George Street Vets 85 George Street DUMFRIES DG1 3AS |
|
Telephone Number |
01387 234156 |
|
Vet’s Name |
James Grierson |
|
Veterinary Practice Name and Address
|
Lockerbie Vets Practice School Road LOCKERBIE DG12 4XX |
|
Telephone Number |
01576 982011 |
|
Vet’s Name |
Marissa Khalim |
|
Veterinary Practice Name and Address
|
Moffat Small Pets Practice Ballgreen Road MOFFAT DG11 6DJ |
|
Telephone Number |
01886 753210 |
|
Vet’s Name |
Javier Fuentes |
|
Veterinary Practice Name and Address
|
Barony Vets Barony Avenue DUMFRIES DG1 3LN |
|
Telephone Number |
01387 734222 |
Task: Using the information from the records on page 1 and 2
a. Design and create a relational database, and explain what the relevant entries are and how you assigned attributes to them:
*Draw 3 tables, one for customer details, one for pet details, and one for vet details, tables should have FIELD NAME – DATA TYPE – SIZE.*
The tables should include:
· valid data field names
· data types of one occurrence of date/time, number, text and currency
· customised field sizes appropriate to field content
· primary and foreign keys
· referential integrity rules applied
Draw tables here:
b. Explain what the primary key is for each entity:
c. Explain how you established relationships and relationship rules between the entities:
and produce an Entity-Relationship Model to demonstrate the relationship: (Fill in the boxes)
d.
Produce evidence on how you added foreign key/s to the relevant entity/entities to enable the relationship to be established:
e.
Explain how you determined referential integrity for each entity and justified your selection. Within your selection you must have at least one occurrence of cascade update and one occurrence of cascade delete. They may be selected separately or together:
