Question

subject: Database Management

Please speak to the issues of data integrity, consistency, and redundancy that you are discovering and/or trying to keep in mind as you are working through your initial memo of tables and fields for review.

Answer 1

Database Management System (DBMS)

In the DBMS, there are many points that need to be kept in mind before the database schema creation and addition of data. The failure of any of the points may lead to compromise of the data integrity, duplicate or inconsistent data. Few of the points that should be taken care of are:

1. Validate and break down the tables into normal forms like 3NF, 4NF or BCNF.
2. Think carefully about the integrity constraints like Foreign key, Unique key, Primary key, etc.
3. Use Indexes for better and fast search results
4. Follow standard conventions in order to manage the database with ease in the future.
5. Document the purpose of each of the database component.

This is not an exhaustive list but there may exist a few other points as well. If we avoid following these recommendations then we may end up having an inconsistent, redundant or inaccurate data. Let me explain to you each one in brief.

• Data Integrity: Data integrity is the overall completeness, correctness, and uniformity of data. Data integrity is usually imposed through the use of conventional methods and practices for error-checking and validations. The following integrity constraints are used in a relational database structure to achieve data integrity:

1. Entity Integrity: Can be achieved with the use of primary keys. It states that the table must have a single column as a Primary key and that column must be unique and not null.
2. Referential Integrity: Can be achieved with the use of foreign keys. It states that foreign key value would refer to a primary key value of another table, or it can be null i.e. no relationship exists.
3. Domain Integrity: This states that all columns in a relational database are in a defined domain.

• Data Consistency: The concept states that a given transaction will successfully modify the data as long as it doesn’t somehow violate any constraints or checks within the system. That means every transaction or process occurring onto the data must be either completely successful or completely a failure. In any case, the partial update of the data must not be allowed.
• Data Redundancy: The concepts states the existence of duplicate data in the database. This is a very common and repetitive issue in database management. This means that in the database some of the fields are repetitive and it allows the user to update the same data in different/same tables. This may lead us to a stage where data becomes inconsistent as the data may get updated at one place but remains the same in another place. This is a very point to be considered in the design phase and the schema must be prepared in such a way that a specific data must be present at only one place in the database from where all other tables can refer it with ease.