Fact Finding Techniques

Integrity Constraints

Integrity constraints are controls implemented on databases to ensure a database maintains its integrity (Pedamkar, 2022). This includes checking that only verified and validated data is inserted or updated into a database. Integrity constraints also check on alter and delete actions to ensure that only the intended data is affected during the execution of the two actions (Pedamkar, 2022). Key constraints are integrity constraints used on databases (Connolly & Begg, 2015). The key constraints are primary and foreign keys that control insertion and update functions. These keys would ensure that similar records are not inserted on the same table by providing a warning message. For example, the warning message could display a message such as a matching employee ID exists, or one ID cannot be used more than once. Another integrity constraint is the entity integrity constraint. This ensures that records in a table are uniquely identified (Pedamkar, 2022). This means that each field on a database should have a unique name that enables a database user to differentiate between the fields. This control ensures that a user inserts updates, alters, or deletes data from a database by accurately identifying the record and field name.

Other integrity controls used on a database include domain and referential integrity constraints (Pedamkar, 2022). Domain integrity constraints ensure that a field’s content is defined correctly. These include the field sizes, data types, and acceptable data. The field size would limit the number of characters a field can hold. Data types would indicate the type of data to be stored in a field, such as string, integer, or Acceptable Boolean data, which would determine the data that can be stored in the field. For example, some fields might remain null while others would not be null. An example of domain integrity constraint is setting field properties for a field; customer names hold 25 characters, have the data type character, and restrict null storage. Referential integrity constraints check relationship validity between two or more database tables (Pedamkar, 2022). This ensures that primary keys point to the corresponding foreign keys on database tables. An example of a referential integrity constraint would be ensuring that the foreign key on a child database table points to a primary key in the parent table.

Normalization Process; 2NF

According to Connolly and Begg (2015), a table in the second standard form must fulfil the requirements of a table in the first normal form. It should also have all its non-primary keys entirely dependent on the primary key. In its first common form, a table should not contain multivalued attributes (Ebrahim & Gadallah, 2014). The parts table in part 2 is in the second common formal form. The table fulfils the first legal form requirement and does not contain multivalued attributes (Ebrahim & Gadallah, 2014). It also meets the requirement of having all its non-primary keys entirely dependent on the primary key. The primary key in the parts table on part 2 is Part. All other keys in the table are non-primary and reliant on the Part attribute. The attributes Price, Cost, Supplier, Street, City, State, Zip, and Telephone are the non-primary keys. Part determines Price, Cost, and Supplier attributes, while Suppliertreet, City, State, Zip, and Telephone determine Supplier. If the parts table contained two or more primary keys that decided some non-primary keys, this would introduce partial dependency (Ebrahim & Gadallah, 2014). For example, when a table has a project ID and student ID as primary keys where project ID determines a non-primary key, project name, and a primary key student ID that determines a non-primary key, student name, the table would not be in the second standard form. This is because the table would violate having all non-primary keys entirely dependent on one primary key.

 References

Connolly, T., & Begg, C. (2015). Database systems: A practical approach to design, implementation, and management (6th ed.). Pearson Higher Ed.

Ebrahim, M. E., & Gadallah, M. E. (2014). Database Normalization [Doctoral dissertation]. https://www.researchgate.net/publication/333972824_Project-Database_Normalization

Pedamkar, P. (2022). Integrity Constraints in DBMS. https://www.educba.com/integrity-constraints-in-dbms/

ORDER A PLAGIARISM-FREE PAPER HERE

We’ll write everything from scratch

Book URL: https://online.vitalsource.com/reader/books/9781269875356/epubcfi/6/48[idloc_023.xhtml-itemref]!/4[eid33295]/2[page_break_inline_403]

Fact Finding Techniques

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 control other than the one in the text (15%).

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 PPart, the SSupplier for each kind of PPart, and the amount we pay the SSupplier for every type of PPart (the cost). Each SSupplier can provide us with many different kinds of parts, but each piece can be provided to us by only one SSupplier. 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 for 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. Explain your answer using the table and field names above. 10%

Your submission must be original and 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 and 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).