Data types - Data types important when design a database tables which define data type of each field in the table. This is useful for restrict user when entering data. For example in above created company database. Date fields accepts only dates, age field accepts only integer value, and so on.
Field sizes - field size is another validation method for fields in the table. For example Employee first name field accept minimum 20 characters. This prevent unwanted characters in to the field, in the other words this prevent the user from entering first name and middle name together. It is designed to hold first name only.
Table level constraints
Table level Constraints are applied for more than one column in the table. The constraints are applied for once after the columns are defined. There are many types of constraints that I have used in my database. Which are as follows
NOT NULL Constraint -: by default columns in a table hold null values. To ensure that the column must contain value and it shouldn't be left blank. The NOT NULL constraint can be use. For example in Employee table, NOT NULL constraint is applied for Employee name. When inserting and updating to the employee name column it enforces to contain a value. D_Name varchar(15) NOT NULL
UNIQUE Constraint: UNIQUE constraint uniquely identifies record in a table. This constraint provides uniqueness for column or set of columns. For example in the department table department name set to be unique in my database. UNIQUE(D_Name)
PRIMARY KEY Constraint - primary key constraint is uniquely identifies each row in a table. This constraint cannot be hold null values because primary key guarantees the uniqueness of the tuple. For example, in employee table employee ID set to be as primary key of that table. primary key(E_ID)
FOREIGN KEY Constraint: foreign key constraint is a column or combination of a column that is used to enforce relationship between two tables. For example relationship between employee table and department table as foreign key(E_ID) references Employee (E_ID)
CHECK Constraint: CHECK constraint enforce by limiting the values that are accepted by one or more columns. For example logical expression for the salary column can be limited by creating CHECK constraint.
Salary decimal (10,2) CHECK (salary >= 15000) this is allows for only data that can be equal to 15000 and data that can be greater than 15000
Verifications are human based checking files and documents. Generally verifications are done before the validations.desk checking is the verification method that can be used.
Task 1.17 Access rights to various types of users
Database administrator has concern the security of the database and he has permission for access to the database while creating updating deleting and maintain the database. Although Access rights can be given to the users of database. The access rights may restrict operations such as CREATE, UPDATE, and DELETE. On predetermined objects such as databases, tables, view, queries and reports.
According to the scenario there are directors, managers, and minor staffs that use database their day to day transactions. Based on the levels of employees, users and roles were created first to user grant access to the data. Data stored in the company database must be protected from access by unauthorized users.
Views can be used to accessible to users while the base tables are not directly accessible. This leads to protect data. Furthermore the database administrator is give authorized for specific users to access the data. For instance, employee names, addresses and their ID numbers might all be stored in the same table; however, for lower level employees like minor staff, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:
By creating a view it allow reading certain columns from the base table. For example salary column in the employee table which needs to be hide from without knowing each other. CREATE VIEW statement helps to partitioned columns from table.
To View read only certain rows from tables. For example create view for every department managers to read details of the employees in his department. This can create via using SELECT and WHERE clause.
However there are many database access level as follows
Read and edit
Users can view any information and also add information to the database
Users can edit, view, update and delete any information in the database.
Table 1: Database Access Levels
This report mainly focuses on the data analysis and design concepts according to the employee project scenario. In this section of the report focuses on analyzing the different data models and it's evaluation from older data models to new data models. According to the scenario ER diagram is designed. Mapping algorithm helps to get finalized relational schema which is already normalized. Then the Implantation is done using Microsoft SQL Server 2008 based on the normalized relational schema.
After develop the Database test cases done accordingly to check whether the actual results are match with the expected results. Moreover suitable database testing methods are introduced to designed database. In addition, the data quality be of highest priority to an organization. Quality in terms of data accuracy, data relevance and should be up to date. However overview of the data verification and validation the methods are techniques have been used to keep up the data clean and accurate while help in maintaining data quality and preventing data decay.