Database Design for Pet Clinic

Assignment Brief
Program Title HND in Computing
Student Name/ID Number
Unit Number and Title Unit 4: Database Design & Development
Academic Year 2025
Unit Tutor Ms. Ishani Jayasuriya
Assignment Title Paws & Whiskers Animal Care
Issue Date 15-July-2025
Submission Date 06-October-2025
Submission Format
The submission should be in the form of an individual report written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using Harvard referencing system. Please also provide an end list of references using the Harvard referencing system.
The recommended word count is 5,000–5,500 words for the report excluding annexures, although you will not be penalised for exceeding the total word limit.

Unit Learning Outcomes
LO1.Use an appropriate design tool to design a relational database system for a substantial problem.
LO2.Develop a fully functional relational database system, based on an existing system design.
LO3.Test the system against user and system requirements.
LO4.Produce technical and user documentation

Transferable skills and competencies developed
1. Critical Thinking & Problem Solving
• Analyzing a real-world scenario and translating it into a functional database structure (e.g., identifying entities, relationships, and constraints).
2. Data Modeling & Logical Design
• Using tools like ER diagrams and normalization techniques to structure data efficiently and reduce redundancy.
3. Technical Proficiency in SQL & Database Tools
• Writing SQL statements (DDL, DML, queries) and using database software (MySQL, MS Access, etc.) to create, manage, and query databases.
4. Attention to Detail & Accuracy
• Ensuring primary and foreign keys, referential integrity, and validations are correctly applied to prevent data errors.
5. Communication & Documentation Skills
• Creating user guides, technical documents, and visual diagrams (e.g., flowcharts, use case diagrams) to explain system behavior to both technical and non-technical audiences.
Assignment activity and guidance
This database assignment develops skills in analyzing user needs, designing ER models, building normalized relational databases, creating interfaces, writing SQL queries, testing systems, and documenting processes. It strengthens problem-solving, data handling, technical proficiency, and communication—essential for both academic success and real-world IT or database management careers.
Recommended Resources
Textbooks
Churcher, C. (2012) Beginning Database Design: From Novice to Professional.
2nd Ed. Apress.
Connolly, T. and Begg, C. (2014) Database Systems: A Practical Approach to Design,
Implementation and Management. 6th Ed. Global Edition. Pearson.
Flejoles, R. P. (2018) Database Theory and Application. Arcler Press.
Journals
The Computer Journal - Oxford Academic
International Journal of Database Management (IJDMS)
Journal of Emerging Trends in Computing and Information Sciences
Web
mva.microsoft.com Microsoft Virtual Academy Database Development
(Training)
mva.microsoft.com/ebooks Microsoft Virtual Academy
Please note that the resources listed are examples for you to use as a starting point in your research – the list is not definitive.

Vocational Scenario
Paws & Whiskers Animal Care is a veterinary clinic based in York, England. They require a database system to manage their clinic operations effectively. The clinic caters to pet owners who bring their animals for various treatments, vaccinations, surgeries, and check-ups. Each customer can own multiple pets, and each pet can receive multiple treatments. Treatments are performed by qualified staff and often require medical equipment.
Paws & Whiskers employs a variety of professionals such as veterinarians, vet nurses, groomers, and administrative staff. Treatments may use equipment like surgical kits, scanners, or grooming tools. Each treatment session involves one or more staff members and takes place on a specific date.
Below are examples of the current paper-based records the clinic maintains.




Activity 1: Database Design
1.1 Conceptual & Logical Design
1. List three user requirements and two system requirements for the Paws & Whiskers Animal Care system.
2. Draw an Entity Relationship Diagram (ERD) for the scenario, identifying:
o At least 5 entities
o Primary keys for each entity
o Relationships between entities
o Cardinalities (1:1, 1:M, M:N)
3. Convert your ER diagram into a logical relational schema.
o Show tables, attributes, primary and foreign keys.
4. Explain What is referential integrity? Why is it important in your system?
5. Check your relational schema for normalization.
o Is your schema in 1NF, 2NF, and 3NF?
o If not, explain how you would normalize it.
________________________________________
1.2 Interface Design and Evaluation
6. Design a simple wireframe for the following screens:
o Add new pet and assign to owner
o Record treatment for a pet
o Search treatment history by pet name
7. Explain how your design meets both user and system requirements.
8. Evaluate your ERD and logical schema.
o Are all user needs addressed?
o Are all relationships clear and effective?
________________________________________
Activity 2: Database Development
2.1 SQL Development and Security
9. Write SQL DDL (CREATE TABLE) statements for:
o Owners
o Pets
o Treatments
o Staff
o Equipment
10. Create a sample interface and describe how it would allow the user to:
• Add new pets
• Assign a treatment
• Delete a record safely
11. What security measures can be implemented in your system?
• Example: Roles, access levels, permissions.
12. Evaluate how your system meets the user/system needs and protects data.
________________________________________
2.2 SQL Query Examples
13. Write and explain SQL statements using the following:
• SELECT with WHERE
• UPDATE a treatment record
• BETWEEN two treatment dates
• IN a list of staff roles
• GROUP BY species
• ORDER BY pet name
• HAVING count of treatments > 3
14. Explain how your queries return useful and meaningful data.
15. Write SQL queries for each of the following tasks using the developed database.
15.1 Write a query to display all pets of the species 'Dog' owned by someone from 'York'.
15.2 Write a query to update the breed of a pet named 'Max' to 'Golden Retriever'.
15.3. Write a query to find all treatment records that occurred between '2023-01-01' and '2023-12-31'
15.4 Write a query to show all staff whose roles are either 'Veterinarian', 'Vet Nurse', or 'Groomer'
15.5 Write a query to count how many pets belong to each species.
15.6 Write a query to list all pets in alphabetical order by their name.
15.7 Write a query to list species that have more than 2 pets in the system.
Activity 3: Testing and Feedback – Questions
3.1 Testing
16. Create a test plan table with at least 5 test cases.
• Include: test scenario, test data, expected result, actual result, pass/fail
17. How did you select your test data? Why is it appropriate?
18. How did the system behave during testing? Suggest improvements.
________________________________________
3.2 User & Developer Feedback
19. Design a short feedback questionnaire for:
• A non-technical user (e.g., receptionist)
• A developer/IT technician
20. Summarize key suggestions from users. What changes would you make?
21. Write a conclusion on how user feedback improved your design.
________________________________________

Activity 4: Documentation – Questions
22. List the key contents of your technical documentation.
• Tables, relationships, diagrams, validation rules
23. Draw one of the following system diagrams:
• Use case diagram for adding pet/treatment
• DFD (Level 0 or Level 1) for treatment processing
• Flowchart for searching pet treatments
24. Write a short user guide for using your database to:
• Add pets
• Record treatments
• Generate reports
25. Suggest future improvements or enhancements for the system:
• Features to add
• Ways to improve performance
• Interface/user experience upgrades