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
Data Usage Maps
Business function (process) to physical design technique matrix
For the database given, it is clearly evident that one-to-one normalization proficiency should be used since 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)
Paws and Beaks ERD
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.
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.
