Database design and development \u2013 Computing<\/p>\n
Student name<\/p>\n
Institution<\/p>\n
Physical Design Considerations<\/p>\n
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\u2019s problem.<\/p>\n
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.<\/p>\n
Processes<\/p>\n
Enter, modify or delete clients<\/p>\n
Indexing<\/p>\n
List clients(clientID, lastname, firstname, phone_number) this is by using the firstname within the lastname basis.<\/p>\n
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.<\/p>\n
A client who owns only deceased animals can be removed<\/p>\n
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\u2019s 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. <\/p>\n
A client who owns no animals can be removed.<\/p>\n
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.<\/p>\n
A client with a credit status of bad cannot be removed.<\/p>\n
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.<\/p>\n
Enter, modify or delete veterinarians.<\/p>\n
Indexing<\/p>\n
List veterinarians by first name within last name (all fields to be shown)<\/p>\n
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.<\/p>\n
Enter, modify or delete clients\u2019 visits.<\/p>\n
Indexing<\/p>\n
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.<\/p>\n
List animals by name (animal id, name, gender, name of type plus id, last name, first name and credit status of client) \u2013 only show animals whose client has a credit status of \u2018good\u2019.<\/p>\n
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.<\/p>\n
Physical Design<\/p>\n
2.1 SQL to implement the operational business rules<\/p>\n
Enter, modify or delete clients<\/p>\n
List clients by first name within last name (all fields to be shown select ClientID, FirstName, LastName, StreetAddress, Suburb, City, Phone, CreditStatus,<\/p>\n
Order by lastname, firstname;<\/p>\n
A client who owns only deceased animals can be removed SelectclientID, AnimalID, Name, Gender, Deceased,Breed, DateOfBirth, AnimalTypeID*, ClientID*Description, <\/p>\n
Order by Description;<\/p>\n
A client who owns no animals can be removed. selectclientID, lastname, firstname, AnimalTypeID, Name;<\/p>\n
A client with a credit status of bad cannot be removed. select clientID, lastname, firstname, status(bad\/good);<\/p>\n
Enter, modify or delete veterinarians.<\/p>\n
List veterinarians by first name within last name (all fields to be shown) select veterinarianID, firstname, lastname;<\/p>\n
Enter, modify or delete animals<\/p>\n
List clients by first name within last name (id, last name, first name and street address to be shown) Select clientID, lastname, firstname,<\/p>\n
Order by street address;<\/p>\n
List animals by name (all fields to be shown from animal plus id, last name, first name of client Select animalID, lastname, firstnameOfclient;<\/p>\n
List animal types by name (all fields to be shown) Select AnimalID, Name, Gender, Deceased,Breed, DateOfBirth, AnimalTypeID*, ClientID*);<\/p>\n
Animals that have current visits cannot be removed<\/p>\n
Select animalID, status (visits\/no visits),<\/p>\n
Order by status_of_visit;<\/p>\n
Enter, modify or delete clients\u2019 visits.<\/p>\n
List animals by name (animal id, name, gender, name of type plus id, last name, first name and credit status of client) \u2013 only show animals whose client has a credit status of \u2018good\u2019. Select animalID, name, gender, nameOfType_ID,lastname,firstname, CreditStatus(\u2018G\u2019 for Good OR \u2018B\u2019 for Bad<\/p>\n
),<\/p>\n
Order by lastname;<\/p>\n
List veterinarians by first name within last name (all fields to be shown).<\/p>\n
Select veteranianID, lastname, firstname;<\/p>\n
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.<\/p>\n
Select VisitlD, VisitDate, Status AnimalID, VetID, PreviousVisitID)<\/p>\n
Order by visit date;<\/p>\n
When a record of the visit is created the visit is recorded as \u2018current\u2019 Select visit_record, status_of_record(\u2018current\u2019)<\/p>\n
Order by visit;<\/p>\n
Only \u2018paid\u2019 visits that are over 3 months old can be deleted Select visitID, visit_duration, visit status (\u2018paid\u2019\/ \u2018not paid\u2019),<\/p>\n
Order by paid;<\/p>\n
Visits with a status of \u2018current\u2019 cannot be deleted Select visitID, visit_status (\u2018current\u2019),<\/p>\n
Order by visit_status;<\/p>\n
A \u2018paid\u2019 visit that is linked to other visits cannot be deleted Select visitID, visit_status(\u2018paid\u2019\/\u2019not paid\u2019), <\/p>\n
Order by visit_status;<\/p>\n
Add or remove treatments\/medication to or from visits.<\/p>\n
List treatment type by description (all fields to be shown) Select (TreatmentID, Description, Cost);<\/p>\n
List medication type by description (all fields to be shown). Select MedicationID, Description, Cost;<\/p>\n
List visits (visit ID, visit date, status, animal ID, animal name, veterinarian ID, veterinarian last name, and veterinarian first name) by visit date.<\/p>\n
Select visitID,visit_date, status, animalID, animal_name, veterinarianID, veterinarian_last name, veterinarian_first name,<\/p>\n
Order by visit_date;<\/p>\n
Treatments and medications can only be entered against \u2018current\u2019 visits Select treatmentID, visit_status,<\/p>\n
Order by visit status(\u2018curent\u2019);<\/p>\n
Mark a visit as \u2018paid\u2019.<\/p>\n
List visits (visit ID, visit date, status, animal ID, animal name, client ID, client last name, and client first name) by visit date<\/p>\n
Select visitsID, status, animalID, animal_name, clientID, client_lastname, client_firstname,<\/p>\n
Order by visit_date;<\/p>\n
When an invoice has been paid, the corresponding visit is marked as \u2018paid\u2019 and all associated medications and treatments are deleted.<\/p>\n
Select invoiceID, clientID, visitID, lastname,firstname, current_status(\u2018paid\u2019\/\u2019not paid\u2019);<\/p>\n
Only current visits can be updated to \u2018paid\u2019 Select visitID, current_status(\u2018paid\u2019\/\u2019not paid\u2019);<\/p>\n
A \u2018paid\u2019 visit cannot be updated to current Select visitID, current_status(\u2018paid\u2019\/\u2019not paid\u2019);<\/p>\n
All veterinarians and paid visits<\/p>\n
Show the id and full name of every veterinarian that has \u2018paid\u2019 visits – sort by veterinarian first name within veterinarian last name<\/p>\n
Select VisitID, TreatmentID, VetID, FirstName, LastName, MobilePhone, Fee;<\/p>\n
Order by vet_firstname;<\/p>\n
Invoice for one selected animal\u2019s visit.<\/p>\n
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 \u2018current\u2019 visits.<\/p>\n
SelectvisitID, visit_date, status, animalID, animal name, client ID, client last name, and client first name,<\/p>\n
Order by visit date;<\/p>\n
For the selected visit show the client\u2019s id, full name, street address, suburb and city, also show the visit id and the animal\u2019s name Select animalID,lastname, firstname, fee_status, vetID, visitID;<\/p>\n
Select vetID, medicationID, cost, description,<\/p>\n
Order by DESCRIPTION;<\/p>\n
Data Usage Maps<\/p>\n
2257425405765PB_VISIT<\/p>\n
00PB_VISIT<\/p>\n
209550289560PB_CLIENT<\/p>\n
00PB_CLIENT<\/p>\n
2276475289560PB_ANIMAL<\/p>\n
00PB_ANIMAL<\/p>\n
4181475222885PB_ANIMAL<\/p>\n
TYPE<\/p>\n
00PB_ANIMAL<\/p>\n
TYPE<\/p>\n
Business function (process) to physical design technique matrix<\/p>\n
One-to-One<\/p>\n
relationship Associative entity with non-key attributes One-to-many<\/p>\n
relationship with reference data Duplication Horizontal<\/p>\n
Partitioning Vertical<\/p>\n
Partitioning Indexing<\/p>\n
Enter, modify or delete clients No No No No No No Yes <\/p>\n
Enter, modify or delete veterinarians. No No Yes No No Yes Yes<\/p>\n
Enter, modify or delete animals No No Yes No Yes Yes Yes<\/p>\n
Enter, modify or delete clients\u2019 visits. No No No No No No Yes<\/p>\n
Add or remove treatments\/medication to or from visits. No No No No No No Yes<\/p>\n
Mark a visit as \u2018paid\u2019. No No No No No No Yes<\/p>\n
All veterinarians and paid visits No No Yes Yes Yes No Yes<\/p>\n
Invoice for one selected animal\u2019s visit. Yes Yes No Yes Yes Yes Yes<\/p>\n
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.<\/p>\n
The associative entity normalization technique cannot be used as there are no associative entities in this database.<\/p>\n
The vertical partitioning cannot be used because there are no queries that access mutually exclusive fields.<\/p>\n
Final ERD with relations (and on-delete actions)<\/p>\n
4352925140335PB_<\/p>\n
MEDICATION<\/p>\n
00PB_<\/p>\n
MEDICATION<\/p>\n
Paws and Beaks ERD<\/p>\n
2028825200025PB_<\/p>\n
TREATMENT<\/p>\n
00PB_<\/p>\n
TREATMENT<\/p>\n
-95250247650PB_VISIT<\/p>\n
TREATMENT<\/p>\n
00PB_VISIT<\/p>\n
TREATMENT<\/p>\n
4657725437515PB_VISIT<\/p>\n
MEDICATION<\/p>\n
00PB_VISIT<\/p>\n
MEDICATION<\/p>\n
026670PB_VET<\/p>\n
00PB_VET<\/p>\n
191452583820PB_VISIT<\/p>\n
00PB_VISIT<\/p>\n
2028825157480PB_ANIMAL<\/p>\n
00PB_ANIMAL<\/p>\n
4124325243205PB_ANIMAL<\/p>\n
TYPE<\/p>\n
00PB_ANIMAL<\/p>\n
TYPE<\/p>\n
-95250290830PB_CLIENT<\/p>\n
00PB_CLIENT<\/p>\n
2.2 Data Volume Map<\/p>\n
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.<\/p>\n
3952240197485PB_<\/p>\n
MEDICATION100<\/p>\n
00PB_<\/p>\n
MEDICATION100<\/p>\n
185674043815PB_<\/p>\n
TREATMENT5<\/p>\n
00PB_<\/p>\n
TREATMENT5<\/p>\n
-22860043815PB_VISIT<\/p>\n
TREATMENT<\/p>\n
3<\/p>\n
00PB_VISIT<\/p>\n
TREATMENT<\/p>\n
3<\/p>\n
3648075232410PB_VISIT<\/p>\n
MEDICATION200<\/p>\n
00PB_VISIT<\/p>\n
MEDICATION200<\/p>\n
212090377190PB_VET10<\/p>\n
0PB_VET10<\/p>\n
1704975434340PB_VISIT20<\/p>\n
00PB_VISIT20<\/p>\n
1704975935990PB_ANIMAL25<\/p>\n
00PB_ANIMAL25<\/p>\n
-228600935990PB_CLIENT<\/p>\n
500<\/p>\n
00PB_CLIENT<\/p>\n
500<\/p>\n
4038600840740PB_ANIMAL<\/p>\n
TYPE40<\/p>\n
00PB_ANIMAL<\/p>\n
TYPE40<\/p>\n
Data Dictionary<\/p>\n
Referential Integrity Constraints<\/p>\n
A client with animals cannot be deleted from the database.<\/p>\n
An animal type with animals ID cannot be deleted also from the database.<\/p>\n
An animal with visits cannot be deleted directly from the database.<\/p>\n
A visit with medication allocated cannot be deleted<\/p>\n
A visit with treatment allocated cannot be deleted.<\/p>\n
A visit related to a previous visit will have its previous visit ID set to null if that previous visit is deleted.<\/p>\n
A veterinarian with visits cannot be deleted<\/p>\n
A treatment assigned to a visit cannot be deleted.<\/p>\n
A medication assigned to a visit cannot be deleted also from the database.<\/p>\n
Reference<\/p>\n
Frost, R., Day, J. C., & Van, S. C. (2006).\u00a0Database design and development: A visual approach. Upper Saddle River, New Jersey: Pearson Education.<\/p>\n
Siau, K. (2007).\u00a0Contemporary issues in database design and information systems development. Hershey, PA: IGI Pub.<\/p>\n
Ponniah, P. (2003).\u00a0Database design and development: An essential guide for IT professionals. Piscataway, NJ?: Hoboken, NJ.<\/p>\n","protected":false},"excerpt":{"rendered":"
Database design and development \u2013 Computing Student name Institution Physical Design Considerations In the setup of information given in the<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-36015","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"\n