RAID (redundant array of independent disks) is a way of storing the same data in different places on multiple hard disks or solid-state drives (SSDs) to protect data in the case of a drive failure. There are different RAID levels, however, and not all have the goal of providing redundancy.

RAID works by placing data on multiple disks and allowing input/output (I/O) operations to overlap in a balanced way, improving performance. Because using multiple disks increases the mean time between failures, storing data redundantly also increases fault tolerance.

RAID levels

RAID 0 : This configuration has striping but no redundancy of data. It offers the best performance, but it does not provide fault tolerance.

RAID 1 :  Also known as disk mirroring, this configuration consists of at least two drives that duplicate the storage of data. There is no striping. Read performance is improved, since either disk can be read at the same time. Write performance is the same as for single disk storage.

RAID 2 : This configuration uses striping across disks, with some disks storing error checking and correcting (ECC) information. RAID 2 also uses a dedicated Hamming code parity, a linear form of ECC (Error Correcting Code).

RAID 3 : This technique uses striping and dedicates one drive to storing parity information. The embedded ECC information is used to detect errors. Data recovery is accomplished by calculating the exclusive information recorded on the other drives. Because an I/O operation addresses all the drives at the same time, RAID 3 cannot overlap I/O. For this reason, RAID 3 is best for single-user systems with long record applications.

RAID 4 : This level uses large stripes, which means a user can read records from any single drive. Overlapped I/O can then be used for read operations. Because all write operations are required to update the parity drive, no I/O overlapping is possible.

RAID 5 : This level is based on parity block-level striping. The parity information is striped across each drive, enabling the array to function, even if one drive were to fail. The array's architecture enables read and write operations to span multiple drives. RAID 5 requires at least three disks, but it is often recommended to use at least five disks for performance reasons.

RAID 6: This technique is similar to RAID 5, but it includes a second parity scheme distributed across the drives in the array. The use of additional parity enables the array to continue functioning, even if two disks fail simultaneously. However, this extra protection comes at a cost. RAID 6 arrays often have slower write performance than RAID 5 arrays.

Trigger

A trigger is a statement that is executed upon modification to the database. A trigger is triggered automatically when an associated DML statement is executed.

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. 

A trigger description contains three parts:

Event: A change to database that activates the trigger

Condition: A query or test that is run when the trigger is executed

Action: A procedure that is executed when the trigger is activated and its condition is true.

Users may not be aware that a trigger was executed as a side effect of the procedure.

Syntax:

create trigger [trigger_name]

[before | after] 

{insert | update | delete} 

on [table_name] 

[for each row] 

[trigger_body]

Explanation of syntax:

1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired

 

BEFORE and AFTER of Trigger:

BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

Procedures & Functions

"A procedures or function is a group or set of SQL and PL/SQL statements that perform a specific task."

A function and  procedure is a named PL/SQL Block which is similar . The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

Procedures:

 A procedure  is a named PL/SQL block which performs one or more specific task.  A procedure has a header and a body.

The header consists of the name of the procedure and the parameters or variables passed to the procedure. 

The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is named for repeated usage.

parameters to procedures can be passes in three ways :

Parameters	Description
IN type	These types of parameters are used to send values to stored procedures.
OUT type	These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
IN OUT type	These types of parameters are used to send values and get values from stored procedures.

A procedure may or may not return any value.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}    <Datatype>,…) 

IS

  Declaration section<variable, constant> ;

BEGIN

  Execution section

EXCEPTION

  Exception section 

END

 

There are two ways to execute a procedure :

• From the SQL prompt : EXECUTE [or EXEC] procedure_name;
• from embedded SQl by call statement: call procedure-name;

 

Functions:

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

 Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 

 RETURN return_datatype;  {IS, AS}

 Declaration_section <variable,constant> ;

 BEGIN  

    Execution_section

    Return return_variable;  

 EXCEPTION

   exception section  

    Return return_variable; 

 END;

A function can be executed in the following ways.

• As a part of a SELECT statement : SELECT emp_details_func FROM dual;
• In a PL/SQL Statements like,  :  dbms_output.put_line(emp_details_func);

 

Procedures VS Functions:

• A function MUST return a value
• A procedure cannot return a value
• Procedures and functions can both return data in OUT and IN OUT parameters
• The return statement in a function returns control to the calling program and returns the results of the function
• The return statement of a procedure returns control to the calling program and cannot return a value
• Functions can be called from SQL, procedure cannot
• Functions are considered expressions, procedure are not

Accessing SQL from Programming Language

A database programmer must have access to a general-purpose programming language for at least two reasons.

·         Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language.

·         Non-declarative actions – such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface – cannot be done from within SQL

To access SQL from other programming languages, we can use:

• Dynamic SQL: JDBC and ODBC - A general-purpose program can connect to and communicate with a database server using a collection of functions
• Embedded SQL - provide a means by which a program can interact with a database server. The SQL statements are translated at compile time into function calls. At runtime, these function calls connect to the database using an API that provides dynamic SQL facilities

Ø  Dynamic SQL

ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) serve as APIs for a program to interact with a database server.

ODBC

ODBC works with C, C++, C# and Visual Basic (other APIs such as ADO.NET sit on top of ODBC).

ODBC is the standard for application programs communicating with a database server.

The API will:

·         open a connection with a database

·         send queries and updates

·         get back results

ODBC can be used with applications such as GUIs, spreadsheets etc.

JDBC

JDBC works with Java. Along with supporting various features for querying and updating data, and for retrieving query results, JDBC also supports metadata retrieval i.e. retrieving information about the database such as relations present in the database and the names and types of relation attributes.

JDBC connects with the database as follows:

·         open a connection

·         create a “Statement” object

·         execute queries using the Statement object to send queries and fetch results

·         exception mechanism to handle errors

 

Ø  Embedded SQL

Embedded SQL refers to embedding SQL queries in another language.

SQL can be embedded in various languages including C, Java and Cobol.

A language into which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.

The EXEC SQL statement is used to identify embedded SQL request to the preprocessor:

            EXEC SQL <embedded SQL statement> END_EXEC

Before executing any SQL statements, the program must first connect to the database. This is done using:

EXEC-SQL connect to server user user-name using password;

Here, server identifies the server to which a connection is to be established.

Variables of the host language can be used within embedded SQL statements. They are preceded by a colon (:) to distinguish from SQL variables (e.g., :credit_amount ).

Variables used as above must be declared within DECLARE section. The syntax for declaring the variables, follows the usual host language syntax.

            EXEC SQL BEGIN DECLARE SECTION

 int credit-amount ;

EXEC SQL END DECLARE SECTION;

·         To write an embedded SQL query, the statemenmt

declare c cursor for statement < SQL query>.

        is used. The variable c is used to identify the query

·         The open statement for our example is as follows:

 EXEC SQL open c ;

This statement causes the database system to execute the query and to save the results within a temporary relation.

·         The fetch statement causes the values of one tuple in the query result to be placed on host language variables.

EXEC SQL fetch c into :si, :sn;

      Repeated calls to fetch get successive tuples in the query result.

·         The close statement causes the database system to delete the temporary relation that holds the result of the query.

EXEC SQL close c ;

4NF and 5NF

Fourth normal form (4NF)

Multivalue dependency occurs when two attributes (or columns) in a table are independent of one another, but both depend on a third attribute.

For a dependency A -> B, if for a single value of A, multiple value of B exists, then the table may have multi-valued dependency. The table should have at least 3 attributes and B and C should be independent for A ->> B multivalued dependency.

• A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued dependency.
• For a dependency A → B, if for a single value of A, multiple values of B exists, then the relation will be a multi-valued dependency

STU_ID	COURSE	HOBBY
21	Computer	Dancing
21	Math	Singing
34	Chemistry	Dancing
74	Biology	Cricket
59	Physics	Hockey

 

 

 

 

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two independent entity. Hence, there is no relationship between COURSE and HOBBY.

In the STUDENT relation, a student with STU_ID, 21 contains two courses, Computer and Math and two hobbies, Dancing and Singing. So there is a Multi-valued dependency on STU_ID, which leads to unnecessary repetition of data.

Decompose above table into two tables to make it in 4NF:

student_course

STU_ID	COURSE
21	Computer
21	Math
34	Chemistry
74	Biology
59	Physics

 student_hobby

STU_ID	HOBBY
21	Dancing
21	Singing
34	Dancing
74	Cricket
59	Hockey

 

Fifth normal form (5NF)

Join dependency is a constraint which is similar to functional dependency or multivalued dependency. 

A relation that has join dependency cannot be divided into two(or more) relations such that the resulting tables can be combined to form the original table.

A relation is in 5NF if it is in 4NF and do not contains any join dependency and joining should be lossless.

5NF is also known as Project-join normal form (PJ/NF).

consider example:

SUBJECT	LECTURER	SEMESTER
Computer	Anshika	Semester 1
Computer	John	Semester 1
Math	John	Semester 1
Math	Akash	Semester 2
Chemistry	Praveen	Semester 1

 

decompose it into three relations P1, P2 & P3:

P1:

SEMESTER	SUBJECT
Semester 1	Computer
Semester 1	Math
Semester 1	Chemistry
Semester 2	Math

 

P2:

SUBJECT	LECTURER
Computer	Anshika
Computer	John
Math	John
Math	Akash
Chemistry	Praveen

 

P3:

SEMSTER	LECTURER
Semester 1	Anshika
Semester 1	John
Semester 1	John
Semester 2	Akash
Semester 1	Praveen

 

The natural Join of all the three relations will give original relation. So original relation has no join dependency. Three decomposed relations are in 4NF and have no join dependency.