ITNPB3 Assignment One: Relational Database Design Introducti

Itnpb3 Assignment Onerelational Database Designintroductionits A Dog

Develop a relational database based on the scenario of “It’s a Dog’s Life,” a kennel operating outside Newtown that cares for dogs while their owners are away. The current data are stored in a spreadsheet with information on dogs, owners, vet visits, and notes. Your task is to design a database to store these data in a normalized form, create corresponding tables, insert data, and write SQL queries to analyze the information. The report should include an introduction, ER diagram, schema design justification, SQL code for creating tables, inserting data, and sample queries to answer specific questions about the data.

Paper For Above instruction

Introduction

The “It’s a Dog’s Life” kennel operates by providing care for dogs owned by multiple owners, with each dog potentially having multiple owners. The kennel maintains detailed records on each dog, including breed, age, special instructions, kennel room, microchip number, and vet visits. Managing this data efficiently requires a relational database that supports data integrity, normalization, and easy retrieval of information to facilitate daily operations and strategic decision-making.

One key business rule derived from the scenario is: “A dog must have a unique microchip number and can have multiple owners, but each owner can own multiple dogs.” This many-to-many relationship necessitates an associative table to link owners and dogs, ensuring the database accurately reflects ownership.

A typical use case involves retrieving details about dogs, their owners, vet visits, or calculating average dog age by breed—tasks that require the database to efficiently handle complex queries involving relationships among multiple entities.

ER Diagram

An ER diagram illustrating the entities, attributes, and relationships is essential to understand the database structure:

  • Entities: Dog, Owner, VetVisit, Room, Notes
  • Attributes:
  • Dog: DogID (PK), Name, Breed, Age, MicrochipNo, SpecialInstructions, RoomID (FK)
  • Owner: OwnerID (PK), Name, Address, Phone
  • VetVisit: VetVisitID (PK), DogID (FK), VetID, VetDate, VetNote
  • Room: RoomID (PK), RoomNumber
  • Notes: NoteID (PK), DogID (FK), NoteText, Date
  • Relationships:
  • Dog and Owner: Many-to-many, implemented via DogOwner associative table
  • Dog and VetVisit: One-to-many
  • Dog and Room: Many-to-one

The cardinalities are derived from the scenario: each dog can have multiple owners and multiple vet visits; each vet visit involves only one dog. Each dog occupies one room.

Database Schema

The schema is designed to be in Third Normal Form (3NF), ensuring minimal redundancy and dependency issues. The schema includes the following tables:

  • Dogs: DogID (PK), Name, Breed, Age, MicrochipNo (unique), SpecialInstructions, RoomID (FK)
  • Owners: OwnerID (PK), Name, Address, Phone
  • DogOwner: DogOwnerID (PK), DogID (FK), OwnerID (FK)
  • VetVisits: VetVisitID (PK), DogID (FK), VetID, VetDate, VetNote
  • Rooms: RoomID (PK), RoomNumber
  • Vets: VetID (PK), VetName, VetContact
  • Notes: NoteID (PK), DogID (FK), NoteText, Date

Assumptions include: each dog has a unique microchip number; vet details are stored in a separate vet table; the DogOwner table manages the many-to-many relationship; additional fields like VetID are added for vet records.

Schema diagram illustrates relationships: Dog to DogOwner (one-to-many), Owner to DogOwner (one-to-many), Dog to VetVisits (one-to-many), Dog to Room (many-to-one), and VetVisits linked to Vets.

Create Tables

SQL statements to create the tables are as follows:

CREATE TABLE Rooms (

RoomID INT AUTO_INCREMENT PRIMARY KEY,


RoomNumber VARCHAR(10) NOT NULL


);


CREATE TABLE Vets (


VetID INT AUTO_INCREMENT PRIMARY KEY,


VetName VARCHAR(100),


VetContact VARCHAR(100)


);


CREATE TABLE Owners (


OwnerID INT AUTO_INCREMENT PRIMARY KEY,


Name VARCHAR(100),


Address VARCHAR(255),


Phone VARCHAR(20)


);


CREATE TABLE Dogs (


DogID INT AUTO_INCREMENT PRIMARY KEY,


Name VARCHAR(50),


Breed VARCHAR(50),


Age INT,


MicrochipNo VARCHAR(50) UNIQUE NOT NULL,


SpecialInstructions TEXT,


RoomID INT,


FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID)


);


CREATE TABLE DogOwner (


DogOwnerID INT AUTO_INCREMENT PRIMARY KEY,


DogID INT,


OwnerID INT,


FOREIGN KEY (DogID) REFERENCES Dogs(DogID),


FOREIGN KEY (OwnerID) REFERENCES Owners(OwnerID)


);


CREATE TABLE VetVisits (


VetVisitID INT AUTO_INCREMENT PRIMARY KEY,


DogID INT,


VetID INT,


VetDate DATE,


VetNote TEXT,


FOREIGN KEY (DogID) REFERENCES Dogs(DogID),


FOREIGN KEY (VetID) REFERENCES Vets(VetID)


);

Insert Data

Data insertion examples:

INSERT INTO Owners (Name, Address, Phone) VALUES ('Sarah Smith', 'The Meadows, Newtown', 'FK40 7LL');

INSERT INTO Dogs (Name, Breed, Age, MicrochipNo, SpecialInstructions, RoomID) VALUES ('Fido', 'Labrador', 10, 'Microchip001', '', 1);


INSERT INTO DogOwner (DogID, OwnerID) VALUES (1, 1);


INSERT INTO Vets (VetName, VetContact) VALUES ('Dr. Jane', '123-456-7890');


INSERT INTO VetVisits (DogID, VetID, VetDate, VetNote) VALUES (1, 1, '2024-05-18', 'Worming tablets given');

SQL Queries

  1. List all dog names: 
    SELECT Name FROM Dogs;
  2. List all dog breeds, unique: 
    SELECT DISTINCT Breed FROM Dogs;
  3. Count dogs with “Labr” in breed: 
    SELECT COUNT(*) FROM Dogs WHERE Breed LIKE '%Labr%';
  4. Average age of all dogs: 
    SELECT AVG(Age) AS AvgAge FROM Dogs;
  5. Average age of dogs by breed: 
    SELECT Breed, AVG(Age) AS AvgAge FROM Dogs GROUP BY Breed;
  6. Breeds with average age > 8: 
    SELECT Breed FROM Dogs GROUP BY Breed HAVING AVG(Age) > 8;
  7. Dog names with owners’ names: 
    SELECT D.Name AS DogName, O.Name AS OwnerName

    FROM Dogs D
    

    JOIN DogOwner DO ON D.DogID = DO.DogID
    

    JOIN Owners O ON O.OwnerID = DO.OwnerID;
  8. Dogs with vet notes, name and date: 
    SELECT D.Name, V.VetDate FROM Dogs D

    JOIN VetVisits V ON D.DogID = V.DogID
    

    WHERE V.VetNote IS NOT NULL;
  9. Dogs with vet visit and notes or null if none: 
    SELECT D.Name,

    V.VetNote AS Vet_Note
    

    FROM Dogs D
    

    LEFT JOIN VetVisits V ON D.DogID = V.DogID;

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments