Week7 Database Exercise Essay

Week7 Database Exercise Essay

Week 7 Database Exercise

Part 1: Purpose and Types of Integrity Constraints

In Database Management Systems (DBMS), integrity constraints are a list of rules that are implemented in table columns or associations to make sure the consistency, integrity, and validity, integrity, and consistency of the information in the database table is preserved. Every alter, delete, update, or insert on a table is assessed against the criteria or constraints specified in the integrity constraint (Connolly, 2015). Only if the criterion outcome is true is the data entered, modified, erased, or changed. As a result, the integrity constraint prohibits an authorized user from accidentally causing damage to the database.’

ORDER A PLAGIARISM-FREE PAPER HERE

Integrity constraints are used to verify that the data in a database system is consistently accurate. The central concept behind these limits is to ensure that data is not duplicated and is retrieved from a similar source (Connolly, 2015). This is based on links to a single dataset stored in a database. If a given data set in a database system is stored in numerous locations, altering one will not change the other. This can lead to inaccuracies when the out-of-date data is cited, resulting in two locations with the same data having distinct values. The simple fix is to have a single master place for a set of only referred data. Connolly (2015) identified three types of integrity constraints: domain integrity, referential integrity, and entity integrity. These constraints are inherent in the relational data architecture and are used to ensure the integrity of the database. Further constraints that come under the class of User-Defined can be imposed by the programmer.

The principle of the Primary Key underpins entity integrity. Each entity must have a distinct, non-null primary key. A primary key assigns a unique identity to each entry within an entity, preventing misunderstanding about which record is being accessed.

The principle of having a foreign key underpins referential integrity. According to Connolly (2015), a foreign key is typically the primary key of a different table, although it can also be null, indicating that no relationship exists or is unidentified. A foreign key creates a connection between two items. When configuring referential integrity, it is critical that the FK and PK have the same data structures and emanate from same domain, or else the RDBMS would reject the join.

The principle of domain integrity states that each attribute field of an object must include data items within identical predefined values. Domain integrity constraints include limiting the type of data and size of an input, limiting the field to just entries specified on a list, or mandating the use of a look-up table or drop-down (Connolly, 2015).

User-defined integrity, also known as enterprise integrity, uses user-defined events to execute an operation when a particular condition is fulfilled instantly. While user-defined integrity is not built into the logical design, it is frequently used in tandem with it (Connolly, 2015). Automatic operations, including flagging unusual activity, blocking certain records for modification, or automatically establishing a new entry on an item since a specific criterion was satisfied, are examples of user set constraints.

Part 2: Normalization

Definition of 2NF

A relation in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key (Connolly, 2015).Normalization ensures that when data is entered, deleted, or changed, it does not cause any problems for database tables and enhances the efficiency and integrity of relational tables. It is critical to normalize a database in order to reduce duplication (redundant data) and guarantee that only linked data is stored in a table. Normalization also eliminates any problems that may arise as a result of database adjustments such as updates, deletions, and insertion.

Is The Above Table In 2NF?

The table is in 2NF. As per the 2NF definition, all non-prime qualities in the table are totally functionally dependent on the Parts’ unique identifier (primary key). Since a specific supplier and every supplier can only supply each part with one address, the Part key is utilized to identify all other properties in the database. The table in the case study is in 1NF. If all of the table’s characteristics include only unique values, the table will be in First Normal Form (1NF). If a table contains tuples data items in attributes or compound entries, the relationship cannot be in the first normal form. As a result, we must first convert it to normalized form by making the table values atomic. Also, a table is considered to be in 2NF if it has a single unique identifier or primary key used to identify all others in the table; there are no partial dependencies in the preceding table, rendering it 2NF. The given table satisfies all the criteria of a table in 2NF.

Reference

Connolly, T. M., & Beg, C. E. (2015). Database systems: A practical approach to design, implementation, and management (Sixth edition). Pearson.

Week7 Database Exercise

Instructions

Include the below numbering scheme in the submission. DO NOT include the Questions or other content from the instructions
Part 1) 55%

Complete Review Question 17.5 on page 509 in the Connolly text; explain each constraint (40%) and provide an example of each constraint other than the one in the text (15%). The response should be in the order of 500+ words

 

• 5 Describe the purpose of integrity constraints and identify the main types of integrity constraints on a logical data model.

Part 2) 20%

HINT: Review the Normalization Process: Parts and Suppliers One-to-Many Example in the Terms and Concepts discussion.
Background
We want to keep track of the price we charge for each type of part, the supplier for each type of part, and the amount we pay the supplier for each type of part (the cost). Each supplier can provide us with many different types of parts, but each part can be provided to us by only one supplier. This requires a one-to-many relationship.

Functional analysis:
Part —- > Price, Cost, Supplier, Street, City, State, Zip, Telephone
Supplier —- > Street, City, State, Zip, Telephone
Part —- > Price, Cost, Supplier
Table:
Parts (Part, Price, Cost, Supplier, Street, City, State, Zip, Telephone)

Compete the following:
1. Copy and paste the 2NF definition from the Connolly text (see page 422) 4%
2. Is the above table in 2NF? 6%
3. Using the table and field names in the above table explain your answer in plain English. 10%

Your submission must be original, include supporting sentences using the terms, concepts, and theories with the page number or website from the required readings or other material. Your submission should paraphrase material you reference, restrict your use of direct quotes (copy and paste) to less than 15% of the submission (the required copy and paste content will not be considered part of the 15% guideline).

Name your document Last Name_Assignment7 (i.e. Smith_Assignment7).

Contact the instructor with any questions.

The following rubric will be used:
Part 1
Synthesis of Concepts
Response to 17.5             40%
Non-text examples           15%
Part 2                               20%
Using APA style/reference      5%
Writing Org/Clarity/Spelling   20%
(-1 per misspelled word)
Total                               100%