Functions, Procedures, Triggers and View

What is data? : Data is the collection of facts, piece of information about any objects or things from which we get the conclusion of any query.

Example: For our organization the data will be Name of the employees, Data of joining, Current CTC, Years of experience, upcoming appraisal cycle, assigned projects.

What is data base?: Data base is a system where we save the interrelated data in a systemic way, it will be in tabular form, flat file form etc.

Relational data Base:? Generally there are three type of data base system flat- file system, database management system(DBM), relational database management system (RDBMS).

Flat file -

DBMS -

RDBMS-

Relational data base is based on the relational integrity of the data, For example: existence of value in one table or data-set is dependent on the existence of same value in another lined data-set or table.

Example: It achieve through the Primary and foreign key.

RDBMS = DBMS+ Referential integrity.

Ex: Oracle, Sqlserver, Mysql follow RDBMS model.

What is Data base testing? Data base testing is the testing of interface data with relational database data, data join, view, import and export of the data, testing the procedures, testing the locks, testing the indexing etc.

Why do we test it? For an organization customer data and the data of organizer itself is a priciest thing. The leakage, corruption, losses the data and retrieving the wrong information is directly impact the reputation and revenue of the organization. So maintaining the integrity and security we do the data base testing.

Ex.: Google recently had lost the data of number of users.

What should we test in data base?: The following things we test in the data base testing

1. Data Validation: Testing the correctness of the data.

Ex: Account number of a bank falling in a range, month should be exist in between 1 and 12, mobile number should be 10 digits log.

2. Data Integrity testing: Testing the business rules in the data base is called the data integrity testing.

Type of data integrity:

a. Entity Integrity: It can be achieved through primary key or unique key constraints.

Ex: Under this we check the data duplicity and null value data.

b. Domain Integrity: It can be achieved through null value, default value and check.

Ex: Data base will take default value in case user not puts the data in any cell, check age column should take <60.

c. Referential Integrity: It can be achieved through foreign key.

Ex: We can not delete the child row without deleting the corresponding parent row.

3. Checking whether any junk data is displaying in UI other than that stored in data base.
4. Checking the execution of stored procedures with the input values taken from the data base table.

Ex: In case of bank account, if user deposits some amount then it triggers procedures and save the rate of interest, total amount and other things.

5. Checking the data migration.

Ex: When we shift the data from one network into another netork. Local  staging  Live

6. Performance related testing: We perform the performance testing after apply the indexing with table. It is nothing but ordered list of values taken from one or more columns in the tables and organized in to b_tree structure
   
   

View: In database parlance, a View defines a "virtual table", i.e., a description of row and/or column selection filters (but with no permanent storage space allocated). When used in place of a table, a View selects the specified rows and/or columns from one or more real tables. Views enable you to see complicated data tables in a more convenient format. They also can be used as a security mechanism, by restricting user access to specific columns and/or rows

Views can provide advantages over tables:

• Views can represent a subset of the data contained in a table
• Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
• Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
• Depending on the SQL engine used, views can provide extra security
• Views can limit the degree of exposure of a table or tables to the outer world

Data Base Join: A SQL join clause combines records from two or more tables in a database

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

Procedures: Stored procedure is a set of sql commands that has been compiled and stores on the data base server.Once the store procedure has been store client application can execute the stored procedure again and again without sending it to data base server and without compiling it again. Stores procedure improve the performance be reduce the network traffic and CPU load.

Function: A stored function (also called a user function or user defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.