Question

A new veterinary clinic will like to set up their database. The clinic will like to keep record of their customers such as: pet name, pet type, pet age, pet weight, owner name, owner address, owner contact. Also the clinic will like to keep track of the veterinarian name, specialty and schedule. They will like to keep track of the customer appointment date, what the appointment is for, and how much it will cost.

List all of the data that needs to be kept track of for the scenario above in a 1NF (Flat File) table

Use the normalization technique and form the 3NF tables

Write out the Create Table command in SQL for creating the database you have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT REFERENCES.

Write one SQL Insert into Values ( ); command for each table in your database

Answer 1

1).ANSWER:

GIVEN BELOW:

Solution 1:

Table T(pet_id, petname, pettype, petage, petweight, owner_id, ownername, address, contact, vet_id, vet_name, speciality, schedule_st, schedule_end, apt_date, apt_reason, apt_cost)

Solution 2:

The functional dependencies formed are pet_id -> petname, pettype, petage, petweight

owner_id -> ownername, address, contact

vet_id -> vet_name, speciality, schedule_st, schedule_end (schedule start and schedule end)

pet_id, vet_id, apt_date -> apt_reason, apt_cost

Thus the primary key = (pet_id + owner_id + vet_id +apt_date). Thus all functional dependencies stated above are partial dependencies. Thus table T is not in 2NF. Ths tables are divided and primary keys are underlined.

Table Pet(pet_id, petname, pettype, petage, petweight, owner_id) [owner_id is foreign key used to connect Pet and Owner tables]

Owner(owner_id, ownername, address, contact)

Veterinarian(vet_id, vet_name, speciality, schedule_st, schedule_end)

Appointment(pet_id, vet_id, apt_date, apt_reason, apt_cost)

Since no transitive dependency exists, these tables are in 3NF.

Solution 3:

CREATE TABLE PET(pet_id int PRIMARY KEY, petname varchar(50), pettype varchar, petweight float, owner_id int, FOREIGN KEY (owner_id) REFERENCES Owner(owner_id));

CREATE TABLE OWNER(owner_id int PRIMARY KEY, ownername varchar(50), adress varchar(100), contact int);

CREATE TABLE Veterinarian(vet_id int PRIMARY KEY, vet_name varchar(50), spceiality varchar(100), schedule_st date, schedule_end date);

CREATE TABLE Appointment(pet_id int , vet_id int, apt_date date, apt_reason varchar(50), apt_cost int, PRIMARY KEY (pet_id, vet_id, apt_date) );

Solution 4:

INSERT INTO PET VALUES(1,'Tom', 'xyz',10,1);

INSERT INTO OWNER VALUES(1,'Adam', 'address',123455678);

INSERT INTO Veterinarian VALUES(1, 'Robert', 'dogs', 09:00:00, 11:00:00);

INSERT INTO Appointment VALUES(1,1, 01-01-2020, 'abc', 100 );