Database Management System: Integrity and Normalization Analysis

This report provides a comprehensive analysis of database integrity constraints and the concept of Second Normal Form (2NF). Part 1 defines integrity constraints, explaining their purpose in maintaining database consistency and accuracy. It categorizes integrity constraints into five types: required data, attribute domain constraints, multiplicity, entity integrity, and referential integrity, providing examples for each. Part 2 delves into 2NF, explaining its basis on full functional dependency and the removal of partial dependencies. The report includes an analysis of a sample database table, identifying its non-compliance with 2NF due to partial dependencies and demonstrating the attributes dependent on the Part and Supplier attributes, respectively. The report highlights the importance of normalization in preventing update anomalies and ensuring data integrity within a database management system.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author’s note:

1DATABASE MANAGEMENT SYSTEM
Part 1: Describe the purpose of integrity constraints and identify the main
types of integrity constraints on a logical data model.
The integrity constraints can be referred to as the constraints of database which can be
imposed for protecting the database from being inconsistent, inaccurate and incomplete.
However, the controls of database management system regarding the integrity constraints
may not be present but the concern is not that. In this report, the basic concepts of database
integrity constraints and types of constraints will be described.
The integrity constraint can be defined as the protocol for making sure that the
alterations made in the database through the authorized users does not result into the data
consistency loss. Any arbitrary predicate can be the integrity constraint applied to the
database. This means that the integrity constraint can be utilized for guarding any accidental
damage against the database. The biggest impact of the integrity constraint is that it maintains
the database’s information quality.
The integrity constraints can be categorized into five categories. These categories are
as following.
i. Required Data: The attributes should have a valid value, this means that those
attributes must not have any have any null values. Taken as an example, an
order must have an order date. These constraints are generally recognized
while documenting the data dictionary.
ii. Attribute Domain Constraints: The domain can be defined as the collection of
values that can be considered as real. Domain is available for every attribute.
Taken as an example, the order can be completed, confirmed or rejected.
Therefore, the domain of the order status attribute can be a string that can
consist of “completed”, “confirmed” or “rejected”. Generally, these

2DATABASE MANAGEMENT SYSTEM
constraints are recognized while we select the domain of attribute regarding
the data model.
iii. Multiplicity: The multiplicity can be considered as the constraints which are
positioned on a relationship among the data within the database. The instances
of constraints like this consist of the requirements such as an order can be
placed by only one customer but a customer can place many orders. It is to
make sure that entire relevant integrity constraints must be recognized and
symbolised as an aspect of the database modelling to meet the data
requirements of the selected enterprise. This constraints mainly defines the
association of one entity with another one. This is done during the business
rule identification process and represented into the data model like ERD.
iv. Entity Integrity: The entity integrity defines the primary key of an entity. The
primary key of an entity must have a value, means that it can never have any
null values. Taken as an example, the primary key of the order table, orderID,
must have a valid value. Based on this value, the rows of the entity are
uniquely defined.
v. Referential Integrity: The referential integrity defines relationship between
two entities of a database. The foreign key is defined by the referential
integrity constraints. The foreign key may or may not be null, it depends on
the nature of the relation. Taken as an example, the customer entities primary
key can be the foreign key of the order table. This foreign key will represent
which customer has made the order. As an order must be made by a customer,
the foreign key will always have a valid value that will refer to a primary key
of customer table. The nature of the foreign key is defined by the multiplicity
constraints which is determined by the relationship among entities.

3DATABASE MANAGEMENT SYSTEM
Part 2: Database Table Analysis
Definition of Second Normal Form: Second Normal Form (2NF) is based on the
concept of full functional dependency. Second normal form applies to relations with
composite keys, that is, relations with a primary key composed of two or more attributes. A
relation with a single-attribute primary key is automatically in at least 2NF. A relation that is
not in 2NF may suffer from the update anomalies, suppose we wish to change the rent of
property number PG4. The normalization of 1NF relations to 2NF involves the removal of
partial dependencies. If a partial dependency exists, we remove the partially dependent
attribute(s) from the relation by placing them in a new relation along with a copy of their
determinant. We demonstrate the process of converting 1NF relations to 2NF relations in the
following example.
Is the above table in 2NF? The table is not in second normal form.
Analysis of the Answer: The provided database entity is as following.
Parts (Part, Price, Cost, Supplier, Street, City, State, Zip, Telephone)
Now, as per the definition of second normal form, the tables must not have any partial
dependency. However, it can be seen that the Street, City, State, Zip, Telephone attributes are
partially dependent on the supplier. On the other hand, Price, Cost are dependent on the part
attribute. This can be represented as following.
Part  Price, Cost
Supplier  Street, City, State, Zip, Telephone