RELATIONAL AND NON-RELATIONAL DATABASES

ITNPBD3

SUBMISSION DATE- 17/04/2023

Introduction – Introduce the business requirement and describe one business rule and one use case you
can identify from the scenario given above. [10 marks]

Answer

Requirements of the business:

The kennel known as “it’s a dog life” maintains the spreadsheet of dogs, their owners, and vet
information all in spreadsheets represented through a table. The database can be handled more
efficiently in order to give it a better structure and track any required data more easily and quickly. Thus
it is essential for the kennel to manage its data using a relational database design.

Use Case

A use case requires an actor/end user to interact with a system to achieve a goal. In the kennel’s
context, the actor could be a data scientist interacting with database software (system) to retrieve/
access any data more quickly (goal). For instance, now the kennel maintains data using a spreadsheet. It
may take more time to track a specific dog through its ID. After creating a database via software, it can
easily keep track of all its dogs for future purposes.

Business rule

Business rules determine how different actors and entities interact within a database management
system. Other than the data scientist who is involved in creating the database, here are some entities
that may interact with the database in the following ways.

- The database may help the manager make decisions about how the dogs can be better-taken
care of and also can be used for future references for its clients.
- It may help the marketers of the kennel to promote/ make content based on the available
databases.
- The caretakers of the kennel may use the databases to keep a record of any specific dogs that
need special attention/ re-visit by vets in the future.
- The customers(dog owners) may require the database to track their dog’s room number when
visiting them

2. ER Diagram - Identify the entities and relationships in the scenario given above and draw an ER
diagram showing the entities, attributes, relationships, and cardinality. Do not decompose any of the
relationships at this point. [10 marks]

Answer:

The entities in this scenario are Dogs, Owners, and Vets. Each of the entities has a few attributes. For
instance, Dogs have attributes such as Name, Breed, Age, Chip Number, Room Number, and Notes.
Owners have attributes such as Name, Address, and Phone Number. Vets have attributes such as Vet ID,
Vet Date, and Vet Note. The relationships between these entities are in a way in which dogs are owned
by their owners and dogs are seen by their vets. In terms of cardinalities, the dogs and owners have
many to many relationships with each other defined as 1..*. Whereas dogs and vets have one-to-one
relationships with each other defined as 1*. This information is illustrated in the Entity Relationship
diagram below

3. Database Schema - Decompose the design to create a schema to store the data in a suitable normal
form (2NF or 3NF). Say which normal form the design is in and justify your choice. List the tables and
show the fields, primary keys, and any foreign keys in your schema. Describe any assumptions you have
made or any additional fields you have added to your design. Make sure all the primary keys are a
sensible choice and introduce new fields if needed. Draw a schema diagram to illustrate your design. [20
marks]

Answer:

Firstly, the dogs, owners, and vets fit into the first normal form as there are single entries of each record
and the values of each attribute are of the same type. Thus, it fits into the first normal form.

Now for the second normal form, we’ll first have a look at the

Dogs’ Table. Here, the primary key is the Chip Number. All the non-key fields are fully functionally
dependent on it. Furthermore, there are single entries for each record. Therefore this meets 2NF

Chip Number Dog Name Breed Age Room Note

( PK)
545682 Fido Labrador 10 1 None
356784 Sheba Labrador 9 2 None
746587 Pat Labradoodle 3 3 Enjoys Petting
546875 Paddy Golden Retriever 7 6 Enjoys All
546888 Rex Mongrel 6 12 Requires pill

Owner’s Table. Dog Fido has more than one owner and the owners Sarah Smith and Frank Jones own
more than one dog. For such repetition, I have used 2NF for further decomposition. Additionally, I have
created the Owner ID to uniquely identify the owners for future reference. (There might be owners
named Sarah/Frank in the future). The IDs are created by using:
 - First letter of the Owner’s First Name
- Last Letter of the Owner’s Last Name
- Number of dogs owned by each owner.

For instance, Sarah Smith owns 3 Dogs so the Owner ID would be Sh3. Two of the other IDs are created
accordingly.

The records of each owner can be tracked by looking at the “Ownership” table below where each of the
Owner’s IDs has single entries for the dogs they own.

OWNERSHIP

Owner ID Owner Address Phone Chip Dog Name

(PK) Name Number
(PK)
Sh3 Sarah Smith The Meadows 01234 567890 545682 Fido
Newtown FK40
7LL
Sh3 Sarah Smith The Meadows 01234 567890 356784 Sheba
Newtown FK40
7LL
Sh3 Sarah Smith The Meadows 01234 567890 746587 Pat
Newtown FK40
7LL
Jh1 John Smith The Meadows 01234 567890 545682 Fido
Newtown FK40
7LL
Fs2 Frank Jones 14 Main Street 0376 458015 546875 Paddy
Newtown FK40
6TT
Fs2 Frank Jones 14 Main Street 0376 458015 546888 Rex
Newtown FK40
6TT

In the ownership table, the Owner ID and Chip Number are the primary keys we need to know both the
owner ID and Chip number to find the Dog’s Name. However, the owner’s name, address, and phone is
dependent on the Owner’s ID only, thus there is a partial dependency. Hence I decomposed it to 2NF
and created a new table called Owner ID:
Decomposed

OWNS

Owner ID Chip Number Dog Name

(PK) (PK)
Sh3 545682 Fido
Sh3 356784 Sheba

Sh3 746587 Pat

Jh1 545682 Fido

Fs2 546875 Paddy

Fs2 546888 Rex

OWNERS TABLE

Owner ID (PK) Owner Name Address Phone Number

Sh3 Sarah Smith The Meadows 01234 567890
Newtown FK40
7LL
Jh1 John Smith The Meadows 01234 567890
Newtown FK40
7LL
Fs2 Frank Jones 14 Main Street 0376 458015
Newtown FK40
6TT

VET TABLE

Vet Id Chip Number Vet Date Vet Note

(PK) (FK)
76 356784 20.05.18 Worming tablets
given

I have created a ‘Visit Table’ below to record the vet who visited a particular dog with chip number
356784, which is the foreign key referencing to Chip Number from Dog’s Table

VISIT TABLE

Chip Number (PK) Vet Id Vet Date Vet Note

(PK)
356784 76 20.05.18 Worming tablets given
The Vet Note is dependent on both Chip Number and Vet ID as we need to know which vet prescribed
what kind of notes for a particular dog. However, the Vet date is dependent on Vet ID, we don’t need to
know the chip number to know the visit date. For such partial dependency, I have decomposed the Visit
table to 2NF to create another table called Vet Date.

Decomposed

VISIT TABLE

Chip Number (PK) Vet Id (PK) Vet Note

356784 76 Worming tablets given

VET_DATE TABLE

Vet Id (PK) Vet Date

76 20.05.18

Database Schema

I have designed a relational database to highlight the relationship between “Owners and Dogs” and “Vet
and Dogs”

Relational Design between Owner and Dog

Relational Design between Vet and Dog

4. Create Tables - Create a MySQL database to store the schema that you designed in part 2 above.
Create the tables using SQL statements and reproduce the code in your answer report. Define the keys
and choose appropriate data types and any other restrictions you think are required. [20 marks]

SQL statements for Ownership:

OWNER TABLE
CREATE TABLE IF NOT EXISTS Owner (

Owner_ID CHAR (3) PRIMARY KEY,

Owner_Name VARCHAR (100),

Address VARCHAR (200),

Phone VARCHAR (11)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

DOG TABLE
CREATE TABLE IF NOT EXISTS Dog (

Chip_Number CHAR (6) PRIMARY KEY,

Dog_Name VARCHAR (8),

Age INT,

Breed VARCHAR (20),

Note VARCHAR (250),

Room INT

) ENGINE=InnoDB DEFAULT CHARSET=utf8

OWNS TABLE
CREATE TABLE IF NOT EXISTS Owns (

Owner_ID CHAR (3),

Chip_Number CHAR(6),

Dog_Name VARCHAR(8),

FOREIGN KEY (Owner_ID) REFERENCES Owner (Owner_ID),

FOREIGN KEY (Chip_Number) REFERENCES Dog (Chip_Number)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLE owns

ADD PRIMARY KEY (Owner_ID, Chip_Number)

SQL statements for Vet Visit:

VET TABLE
CREATE TABLE IF NOT EXISTS Vet (

Vet_ID CHAR (2) PRIMARY KEY,

Chip_Number VARCHAR (6),

Vet_Date DATE,

Vet_Note VARCHAR (200)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLE Vet ADD CONSTRAINT FOREIGN KEY (Chip_Number) REFERENCES Dog (Chip_Number)

DOG TABLE
CREATE TABLE IF NOT EXISTS Dog (

Chip_Number CHAR (6) PRIMARY KEY,

Dog_Name VARCHAR (8),

Age INT,

Breed VARCHAR (20),

Note VARCHAR (250),

Room INT

) ENGINE=InnoDB DEFAULT CHARSET=utf8

VISIT TABLE
CREATE TABLE IF NOT EXISTS Visits (

Vet_ID CHAR (2),

Chip_Number CHAR(6),

Vet_Note VARCHAR (200),

FOREIGN KEY (Vet_ID) REFERENCES Vet (Vet_ID),

FOREIGN KEY (Chip_Number) REFERENCES Dog (Chip_Number)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLE visits

ADD PRIMARY KEY (Vet_ID, Chip_Number)

5. Insert the Data – Insert the data given above into the appropriate tables. Give a single example of an
SQL statement that you used to insert one row of data into one of the tables. [10 marks]

OWNERSHIP DATABASE

OWNER TABLE

INSERT INTO `owner` (`Owner_ID`, `Owner_Name`, `Address`, `Phone`) VALUES ('Sh3', 'Sarah Smith',
'The Meadows Newtown FK40 7LL', '01234 567890');

DOG TABLE

INSERT INTO `dog` (`Chip_Number`, `DogName`, `Age`, `Breed`, `Note`, `Room`) VALUES ('545682',
'Fido', '10', 'Labrador', 'None', '1');
OWNS TABLE

INSERT INTO `owns` (`Owner_ID`, `Chip_Number`, `Dog_Name`) VALUES ('Sh3', '545682', 'Fido');

VET VISIT DATABASE

VET

INSERT INTO `vet` (`Vet_ID`, `Chip_Number`, `Vet_Date`, `Vet_Note`) VALUES ('76', '356784', '2018-05-
20', 'Worming Tablets Given');

DOG

INSERT INTO `dog` (`Chip_Number`, `DogName`, `Age`, `Breed`, `Note`, `Room`) VALUES ('545682',
'Fido', '10', 'Labrador', 'None', '1');
VISITS

INSERT INTO `visits` (`Vet_ID`, `Chip_Number`, `Vet_Note`) VALUES ('76', '356784', 'Worming tablets
given');

6. SQL Queries - Carry out the appropriate SQL queries, answering the following questions. For each
query, give the SQL code and the result of running the query on the data in your database. Marks are
given by each question.

i. Return a list of all dog names. [2 marks]

SELECT DogName FROM Dog

ii. Return a list of all dog breeds, showing each breed only once. [3 marks]
SELECT DISTINCT Breed FROM Dog
iii. Write a query to count how many dogs have the string “Labr” in their breed. [2 marks]

SELECT * FROM Dog WHERE Breed LIKE '%Labr%'

iv. Calculate the average age of all dogs in the kennel. [2 marks]
SELECT AVG(Age) FROM Dog

v. Calculate the average age of dogs by breed. [3 marks]

SELECT Breed, AVG (Age) FROM Dog GROUP BY Breed
vi. Find the breed of dog that has an average age of more than 8. [4 marks]
SELECT Breed, AVG (Age) FROM Dog GROUP BY Breed HAVING AVG (AGE) > 8

vii. List all the dog names along with their owners’ names. [4 marks]
SELECT DogName, OwnerName FROM Dog, Owner, Owns WHERE Owns.Chip_Number = Dog.C
hip_Number AND Owner.Owner_ID = Owns.Owner_ID

viii. List all the dogs who have had a vet report. Give the name of the dog and the date of the vet report.
[5 marks]
SELECT Vet_Date, DogName FROM Dog RIGHT OUTER JOIN Vet ON Dog.Chip_Number=Vet.
Chip_Number

ix. List all dogs, giving their name, and if a dog has had a vet visit, give the note for the visit, otherwise, if
the dog has not visited the vet, return null in the vet note field. [5 marks
SELECT DogName, Vet_Note FROM Dog LEFT OUTER JOIN Vet ON Dog.Chip_Number=Vet.C
hip_Number