Database design and development

Database design and development – Computing

Student name

Institution

Physical Design Considerations

In the setup of information given in the system, it is clear that the numbers of different relations mostly the one-to-many relationships are eight in number while some of them like the case of 2 are taken as the reference entities in this case and these are the cases of PB_vet and the PB_visit. This concept clearly shows that one-to-many relationship can be implemented in this database design to come up with an appropriate solution to solve the company’s problem.

The different types of processes involved in the database development with respect to the company is like the case of the horizontal partitioning criteria and also the case of vertical partitioning technique. They cannot be within the application that is intended to be constructed since the feasibility of their use does not exist within the system.

Processes

Enter, modify or delete clients

Indexing

List clients(clientID, lastname, firstname, phone_number) this is by using the firstname within the lastname basis.

The function is performed in order to ensure that there is improved means of getting the information form the database without necessarily having to perform the sorting of each member available in the database. Despite all these, still it is clear that the process will be slowed down than ever.Despite the benefits of the process the indexing will be important in this context and will not be used in this case.

A client who owns only deceased animals can be removed

This action is to be performed in order to enable the performance to be improved simply by placing the index entity on the description of the client’s details.This also implies that the sorting out of the elements will not be required within the database management. The demerit of this method is that it makes the system to be generally slow in operation as compared to the other system. Also the indexing will not be that important in this case since the process will not be that of assistance at all since the application does not support any thing in reference to indexing.

A client who owns no animals can be removed.

This query is to be run in order to get the correct number of clients who do not own any animal. This allows the user to be able to delete the client who does not have any animal can be removed from the list or the data in the application.

A client with a credit status of bad cannot be removed.

This portion of the system enables the user to know the status of the client with the credit status of bad and gets a recommendation not to remove them from the database. This enables the user to know the correct number of clients who owns the animals and those that does not have any. This reduces the trend of sorting which is believed to be time wasting and uses many resources in the process.

Enter, modify or delete veterinarians.

Indexing

List veterinarians by first name within last name (all fields to be shown)

On this case the process of indexing can be of great importance since it enable the number of veterinarians be gotten and their time of visit can be sorted out easily without any problem. This query helps in getting the number of veterinarians who pays visit to the clinic to administer the respective treatment to the animals. Their ID is also very paramount and can be used in retrieving the information concerning their performance in the firm. It reduces the issue of the sorting that could have taken some times to come up with the solution. Most importantly again, it would be in order to get the information about their level of knowledge of the kind of diseases they deal with.

Enter, modify or delete clients’ visits.

Indexing

This query also is very important in that it enable one to be able to get more information concerning the client that needs to be deleted from the system. This helps in avoiding the number of the client that still in the clients within the system and removes them without the retaining them which at the end creates the redundancy of the data within the system.

List animals by name (animal id, name, gender, name of type plus id, last name, first name and credit status of client) – only show animals whose client has a credit status of ‘good’.

This query helps in getting the information concerning the various types of the animals their respective gender of the animals and also tend to reduce the level of the redundancy within the system.

Physical Design

2.1 SQL to implement the operational business rules

Enter, modify or delete clients

List clients by first name within last name (all fields to be shown select ClientID, FirstName, LastName, StreetAddress, Suburb, City, Phone, CreditStatus,

Order by lastname, firstname;

A client who owns only deceased animals can be removed SelectclientID, AnimalID, Name, Gender, Deceased,Breed, DateOfBirth, AnimalTypeID*, ClientID*Description,

Order by Description;

A client who owns no animals can be removed. selectclientID, lastname, firstname, AnimalTypeID, Name;

A client with a credit status of bad cannot be removed. select clientID, lastname, firstname, status(bad/good);

Enter, modify or delete veterinarians.

List veterinarians by first name within last name (all fields to be shown) select veterinarianID, firstname, lastname;

Enter, modify or delete animals

List clients by first name within last name (id, last name, first name and street address to be shown) Select clientID, lastname, firstname,

Order by street address;

List animals by name (all fields to be shown from animal plus id, last name, first name of client Select animalID, lastname, firstnameOfclient;

List animal types by name (all fields to be shown) Select AnimalID, Name, Gender, Deceased,Breed, DateOfBirth, AnimalTypeID*, ClientID*);

Animals that have current visits cannot be removed

Select animalID, status (visits/no visits),

Order by status_of_visit;

Enter, modify or delete clients’ visits.

List animals by name (animal id, name, gender, name of type plus id, last name, first name and credit status of client) – only show animals whose client has a credit status of ‘good’. Select animalID, name, gender, nameOfType_ID,lastname,firstname, CreditStatus(‘G’ for Good OR ‘B’ for Bad

),

Order by lastname;

List veterinarians by first name within last name (all fields to be shown).

Select veteranianID, lastname, firstname;

List visits (visit ID, visit date, status, animal ID, animal name, client ID, client last name, client first name, veterinarian ID, veterinarian last name, and veterinarian first name) by visit date.

Select VisitlD, VisitDate, Status AnimalID, VetID, PreviousVisitID)

Order by visit date;

When a record of the visit is created the visit is recorded as ‘current’ Select visit_record, status_of_record(‘current’)

Order by visit;

Only ‘paid’ visits that are over 3 months old can be deleted Select visitID, visit_duration, visit status (‘paid’/ ‘not paid’),

Order by paid;

Visits with a status of ‘current’ cannot be deleted Select visitID, visit_status (‘current’),

Order by visit_status;

A ‘paid’ visit that is linked to other visits cannot be deleted Select visitID, visit_status(‘paid’/’not paid’),

Order by visit_status;

Add or remove treatments/medication to or from visits.

List treatment type by description (all fields to be shown) Select (TreatmentID, Description, Cost);

List medication type by description (all fields to be shown). Select MedicationID, Description, Cost;

List visits (visit ID, visit date, status, animal ID, animal name, veterinarian ID, veterinarian last name, and veterinarian first name) by visit date.

Select visitID,visit_date, status, animalID, animal_name, veterinarianID, veterinarian_last name, veterinarian_first name,

Order by visit_date;

Treatments and medications can only be entered against ‘current’ visits Select treatmentID, visit_status,

Order by visit status(‘curent’);

Mark a visit as ‘paid’.

List visits (visit ID, visit date, status, animal ID, animal name, client ID, client last name, and client first name) by visit date

Select visitsID, status, animalID, animal_name, clientID, client_lastname, client_firstname,

Order by visit_date;

When an invoice has been paid, the corresponding visit is marked as ‘paid’ and all associated medications and treatments are deleted.

Select invoiceID, clientID, visitID, lastname,firstname, current_status(‘paid’/’not paid’);

Only current visits can be updated to ‘paid’ Select visitID, current_status(‘paid’/’not paid’);

A ‘paid’ visit cannot be updated to current Select visitID, current_status(‘paid’/’not paid’);

All veterinarians and paid visits

Show the id and full name of every veterinarian that has ‘paid’ visits – sort by veterinarian first name within veterinarian last name

Select VisitID, TreatmentID, VetID, FirstName, LastName, MobilePhone, Fee;

Order by vet_firstname;

Invoice for one selected animal’s visit.

List visits (visit ID, visit date, status, animal ID, animal name, client ID, client last name, and client first name) by visit date of all the ‘current’ visits.

SelectvisitID, visit_date, status, animalID, animal name, client ID, client last name, and client first name,

Order by visit date;

For the selected visit show the client’s id, full name, street address, suburb and city, also show the visit id and the animal’s name Select animalID,lastname, firstname, fee_status, vetID, visitID;

Select vetID, medicationID, cost, description,

Order by DESCRIPTION;

Data Usage Maps

2257425405765PB_VISIT

00PB_VISIT

209550289560PB_CLIENT

00PB_CLIENT

2276475289560PB_ANIMAL

00PB_ANIMAL

4181475222885PB_ANIMAL

TYPE

00PB_ANIMAL

TYPE

Business function (process) to physical design technique matrix

One-to-One

relationship Associative entity with non-key attributes One-to-many

relationship with reference data Duplication Horizontal

Partitioning Vertical

Partitioning Indexing

Enter, modify or delete clients No No No No No No Yes

Enter, modify or delete veterinarians. No No Yes No No Yes Yes

Enter, modify or delete animals No No Yes No Yes Yes Yes

Enter, modify or delete clients’ visits. No No No No No No Yes

Add or remove treatments/medication to or from visits. No No No No No No Yes

Mark a visit as ‘paid’. No No No No No No Yes

All veterinarians and paid visits No No Yes Yes Yes No Yes

Invoice for one selected animal’s visit. Yes Yes No Yes Yes Yes Yes

For the database given, it is clearly evident that one-to-one normalization proficiency should be usedsince in the given database, the one to one relationship is evident in the case of the as there are no one-to-one relationships in this database.

The associative entity normalization technique cannot be used as there are no associative entities in this database.

The vertical partitioning cannot be used because there are no queries that access mutually exclusive fields.

Final ERD with relations (and on-delete actions)

4352925140335PB_

MEDICATION

00PB_

MEDICATION

Paws and Beaks ERD

2028825200025PB_

TREATMENT

00PB_

TREATMENT

-95250247650PB_VISIT

TREATMENT

00PB_VISIT

TREATMENT

4657725437515PB_VISIT

MEDICATION

00PB_VISIT

MEDICATION

026670PB_VET

00PB_VET

191452583820PB_VISIT

00PB_VISIT

2028825157480PB_ANIMAL

00PB_ANIMAL

4124325243205PB_ANIMAL

TYPE

00PB_ANIMAL

TYPE

-95250290830PB_CLIENT

00PB_CLIENT

2.2 Data Volume Map

The data volume shown below shows the number of each entity as shown in the ERD diagram and their possible changes that are inevitable in this case. It enable the user to be able to know the number of data that can be fed in the system within a given particular time span and also ensure that they maintain the number in order to avoid any form of integrity constraints within the system.

3952240197485PB_

MEDICATION100

00PB_

MEDICATION100

185674043815PB_

TREATMENT5

00PB_

TREATMENT5

-22860043815PB_VISIT

TREATMENT

3

00PB_VISIT

TREATMENT

3

3648075232410PB_VISIT

MEDICATION200

00PB_VISIT

MEDICATION200

212090377190PB_VET10

0PB_VET10

1704975434340PB_VISIT20

00PB_VISIT20

1704975935990PB_ANIMAL25

00PB_ANIMAL25

-228600935990PB_CLIENT

500

00PB_CLIENT

500

4038600840740PB_ANIMAL

TYPE40

00PB_ANIMAL

TYPE40

Data Dictionary

Referential Integrity Constraints

A client with animals cannot be deleted from the database.

An animal type with animals ID cannot be deleted also from the database.

An animal with visits cannot be deleted directly from the database.

A visit with medication allocated cannot be deleted

A visit with treatment allocated cannot be deleted.

A visit related to a previous visit will have its previous visit ID set to null if that previous visit is deleted.

A veterinarian with visits cannot be deleted

A treatment assigned to a visit cannot be deleted.

A medication assigned to a visit cannot be deleted also from the database.

Reference

Frost, R., Day, J. C., & Van, S. C. (2006). Database design and development: A visual approach. Upper Saddle River, New Jersey: Pearson Education.

Siau, K. (2007). Contemporary issues in database design and information systems development. Hershey, PA: IGI Pub.

Ponniah, P. (2003). Database design and development: An essential guide for IT professionals. Piscataway, NJ?: Hoboken, NJ.