Discussion:
"Maintaining Data Integrity" Please respond to the following:
• Describe the importance of maintaining integrity rules throughout the database structure. Create at least three examples of common relationships that are used within a relational database system. Justify your response.
• Many times, database designs do not include the proper use of indexes. Provide a specific example on how an index could be used in order to enhance user search experience. List specific fields that you would index. Analyze what would occur if the proper index is not included, with respect to your example.
Classmates:
Describe the importance of maintaining integrity rules throughout the database structure. Create at least three examples of common relationships that are used within a relational database system. Justify your response.
Data integrity refers to the accuracy and consistency (validity) of data over its lifecycle (Lord, 2017). Accuracy of data is important since so many different systems rely on large databases today and corporations around the globe make decisions based on the accuracy of data. A good example of having poor data integrity would be with a company such as Wal-Mart or Amazon. If a product price within a database is not accurate the company could lose money or customers since the price data is not reliable. Consistent data is also important since it highlights the need to compare data sets over a long period of time. If data has been compromised and inconsistent data points are noted, it can lead to bad decision making at all levels of an organization.
Data integrity ensures recoverability and searchability, traceability (to origin), and connectivity, it also increases stability and performance while improving reusability and maintainability (Lord, 2017). Having the ability to accurately recover data in the event of data loss is important to any customer. With services such as Amazon Web Service, who provide data storage for companies, if the recoverability and connectivity to the data is compromised in any way customers will not trust the ability of Amazon to provide that service.
The three most common types of relationships within the relational database model are the one-to-one, one-to-many, and the many-to-many relationships.
The one-to-one relationship can best be described as one record in a table matching to exactly one record in another table. This relationship is usually achieved through a shared key between the two tables. It can be the primary key and secondary key or it can be two secondary keys within the tables.
The one-to-many relationship can be described as one record in a table able to be matched to many records within another table. An example of this would be a customer who orders items from a website. One customer can order many items.
Finally, the many-to-many relationship can be described as linking many records from one table to many records with another table. This type of relationship should be avoided since it can lead to redundancies within the database and also data integrity issues which need to avoided whenever possible.
Of the three relationships the one to many is typically the most used within a database. This is most likely due to the ease of linking data fields within the database to one another and also the need to have multiple entries associated with a customer.
Many times, database designs do not include the proper use of indexes. Provide a specific example on how an index could be used in order to enhance user search experience. List specific fields that you would index. Analyze what would occur if the proper index is not included, with respect to your example.
Using a proper index helps speeds up the search times within a database. Instead of having a search look at every field within a table we can have indexed fields with specific search criterion to help find records faster and more accurately. Some of the common fields to be indexed should be names, employee IDs, order numbers, and date fields. If some of these fields were not properly indexed a search could be made across the entire database, not just one table, which significantly slows down the users experience and in time slows the entire database down. This can be especially problematic if the database is accessed through a web interface or has large numbers of users needing the data.
References:
Lord, N. (2017, July 27). What is Data Integrity? Data Protection 101. Digital Guardian. Retrieved from https://digitalguardian.com/blog/what-data-integrity-data-protection-101.