DATABASE DESIGN DEVELOPMENT DESIGNS FOR DATA INTEGRITY VALIDATIONS

DATABASE DESIGN & DEVELOPMENT DESIGNS FOR DATA INTEGRITY, VALIDATIONS, SECURITY AND CONTROLS ZATIL RIDH'WAH HJ DAROT

DATA INTEGRITY • Refers to the accuracy and consistency of data. • For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these ________. • Maintaining data integrity means making sure the data remains intact and _______ throughout its entire life cycle. This includes the capture of the data, storage, updates, transfers, backups, etc. Every time data is processed there’s a risk that it could get ______ (whether accidentally or maliciously).

• 4 Types of Data Integrity: – Entity integrity – Referential integrity – Domain integrity – User-defined integrity

Entity integrity • Defines each row to be unique within its table. No two rows can be the same. • To achieve this, a primary key can be defined. The primary key field contains a unique identifier – no two rows can contain the same unique identifier.

Referential integrity • concerned with relationships. • When two or more tables have a relationship, we have to ensure that the _____value matches the primary key value at all times. We don’t want to have a situation where a foreign key value has no matching primary key value in the primary table. This would result in an orphaned record. • So referential integrity will prevent users from: – Adding records to a related table if there is ______ record in the primary table. – Changing values in a primary table that result in orphaned records in a related table. – Deleting records from a primary table if there are matching related records.

Domain integrity • Concerns the validity of entries for a given column. Selecting the appropriate _____for a column is the first step in maintaining domain integrity. Other steps could include, setting up appropriate constraints and rules to define the data format and/or restricting the range of possible values. User-defined integrity • Allows the user to apply business rules to the database that aren’t covered by any of the other three data integrity types.

DATA VALIDATION • Data validation can be implemented during the design process of a database by setting data _____ for the user input to avoid errors. • There are several different ways to validate data through Microsoft Access, some of which include: 1. Validation Rule Property: • This property allows the database designer to set a validation rule, so that data inputted into the database must follow a certain rule. • Example: Student titles such as Freshman, Sophomore, Junior, and Senior must be entered as ‘FR’, ‘SF’, ‘JR’, or ‘SR’. The database designer can also implement a validation rule text that displays a message stating the above rule if entered

2. Data Types: • You can restrict data types that are entered into an Access database by setting a certain required data type. • Example: If a data type is set to be ‘numeric’, then all other types, such as a character(s) will be denied with an error. By setting an input mask in a field in Microsoft Access, it controls the way data can be entered. Example: Input masks can specify that social security numbers be entered in the form of ‘AAA"-"AAAA’. By using this setting the user’s input automatically formats to the specified form. 3. Required Property: Using the required property is an easy way to avoid null values in unwanted areas. If the required property is set for a certain field but the user attempts to leave it ______, they will be prompted with an error message, requiring data to be entered before going any further.

DATA SECURITY & DATA CONTROL • Frequently data contains proprietary information – Personally identifiable data – Employee HR Data – Financial Data • The security and confidentiality of this data is of _____ importance.

• There are four key issues in the security of databases just as with all security systems: – Availability – Authenticity – Integrity – Confidentiality

AVAILABILITY • Data needs to be available at all necessary times • Data needs to be available to only the appropriate users • Need to be able to track who has ____ to and who has accessed what data

AUTHENTICITY • Need to ensure that the data has been edited by an ______ source • Need to confirm that users accessing the system are who they say they are • Need to verify that all report requests are from authorized users • Need to verify that any outbound data is going to the expected receiver

INTEGRITY • Need to verify that any external data has the _____and other metadata • Need to verify that all input data is accurate and verifiable • Need to ensure that data is following the correct work flow rules for your institution/corporation • Need to be able to report on all data changes and who authored them to ensure compliance with corporate rules and ______.

CONFIDENTIALITY • Need to ensure that confidential data is only available to correct people • Need to ensure that entire database is security from external and internal system breaches • Need to provide for reporting on who has accessed what data and what they have done with it • Mission critical and ______ data must be highly security at the potential risk of lost business and litigation

TUTORIALS • Discuss and provide an overview of the security measures provided by Microsoft Office Access DBMS. • Go to: https: //www. khanacademy. org/partnercontent/nova/cybersecurity/cyber/e/cybersecur ity-101 -quiz and complete the Cyber Security 101 quiz.

REFERENCES • Conolly, T. and Begg, C. (2014) Database Systems: A Practical Approach to Design, Implementation and Management. 6 th Ed. Global Edition. Pearson.