Database Design & Development: Designs for Data Integrity, Validations, Security, and Controls

1. DATABASE DESIGN & DEVELOPMENT Designs for Data Integrity, validations, security and controls Zatil Ridh'wah Hj Darot

2. 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).

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

4. 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.

5. 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.

6. 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.

7. 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 incorrectly.

8. 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"-"AA"-"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.

9. 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.

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

11. 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

12. 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

13. 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 ____________.

14. 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

15. 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/cybersecurity-101-quiz and complete the Cyber Security 101 quiz.

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