What is Normalization?


Normalization is the process of designing database tables in a way that makes for efficient use of disk space and that allows the efficient manipulation and updation of data.
The main objective of normalization is to minimize redundancy and dependency, which involves dividing large tables into smaller tables and defining relationships between them. 
Normalization isolates data, so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Benefits of Normalization:
  • Elimination of data redundancy and dependency in database
  • Improved performance
  • Query optimization 
  • Efficient manipulation and updation of data (faster updates due to less number of columns in one table)
  • Index improvement

Codd’s 12 Rules for Relational Databases


In October 1985, Codd presented 12 rules that a database must obey if it is to be considered truly relational:
  1. Information rule All information in a relational database is represented explicitly at the logical level and in exactly one way — by values in tables.
  2. Guaranteed access rule Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name. 
  3. Systematic treatment of NULL values NULL values are supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of the data type. 
  4. Dynamic online catalog based on the relational model The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
  5. Comprehensive data sublanguage rule A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings, and that is comprehensive in supporting all of the following items:
    • Data definition  
    • View definition
    • Data manipulation (interactive and by program)
    • Integrity constraints
    • Authorization   
    • Transaction boundaries (begin, commit, and rollback)
  6. View updating rule All views that are theoretically updateable are also updateable by the system.
  7. High-level insert, update, and delete The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data, but also to the insertion, update, and deletion of data.
  8. Physical data independence Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.
  9. Logical data independence Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables. 
  10. Integrity independence Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs. 
  11. Distribution independence ­ A relational DBMS has distribution independence. 
  12. Nonsubversion rule If a relational system has a low-level (single record at a time) language, that low level cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher-level relational language (multiple records at a time). 



     

What is RDBMS?


Data Model: The data structures and access techniques provided by a particular DBMS are called its data model. A data model determines both the “personality” of a DBMS and the applications for which it is particularly well-suited.

RDBMS: A relational database (RDBMS) is a database where all data visible to the user is organized strictly as tables of data values, and where all database operations work on these tables. It is based on the relational model proposed by Codd as an attempt to simplify the database structure.

A relational DBMS can represent parent/child relationships, but they are visible only through the data values contained in the database tables. SQL is based on the relational data model that organizes the data in a database as a collection of tables.

Tables: The organizing principle in a relational database is the table, a rectangular row/column arrangement of data values. Each table in a database has a unique table name that identifies its contents.
  • Each table has a table name that uniquely identifies it.
  • Each table has one or more named columns, which are arranged in a specific, left-to-right order.
  • Each table has zero or more rows, each containing a single data value in each column. The rows are unordered.
  • All data values in a given column have the same data type and are drawn from a set of legal values called the domain of the column. 

Tables are related to one another by the data they contain. The relational data model uses primary keys and foreign keys to represent these relationships among tables.

Primary Keys: A primary key is a column or combination of columns in a table whose value(s) uniquely identify each row of the table. A table has only one primary key.

Foreign Keys: A foreign key is a column or combination of columns in a table whose value(s) are a primary key value for some other table. A table can contain more than one foreign key, linking it to one or more other tables.

Just as a combination of columns can serve as the primary key of a table, a foreign key can also be a combination of columns. In fact, the foreign key will always be a compound (multicolumn) key when it references a table with a compound primary key. Foreign keys are a fundamental part of the relational model because they create relationships among tables in the database.

Note: A primary key/foreign key combination creates a parent/child relationship between the tables that contain them.

Introduction to SQL

SQL is a comprehensive language for controlling and interacting with a database management system which consists of about 40 statements, specialized for database management tasks. It is a declarative or descriptive language rather than a procedural one (4th generation language).

The original name given it by IBM was Structured English Query Language, shortened to the acronym SEQUEL. When IBM discovered that SEQUEL was a trademark owned by the Hawker Siddeley Aircraft Company of the United Kingdom, they shortened the acronym to SQL. The word “English” was then dropped from the spelled-out name to match the new acronym.

SQL is used to control all of the functions that a DBMS provides for its users, including:
  • Data definition SQL lets a user define the structure and organization of the stored data and relationships among the stored data items.
  • Data retrieval SQL allows a user or an application program to retrieve stored data from the database and use it. 
  • Data manipulation SQL allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data. 
  • Access control SQL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access. 
  • Data sharing SQL is used to coordinate data sharing by concurrent users, ensuring that changes made by one user do not inadvertently wipe out changes made at nearly the same time by another user. 
  • Data integrity SQL defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures. 
Other roles played by SQL:
  • SQL is an interactive query language. Users type SQL commands into an interactive SQL program to retrieve data and display it on the screen, providing a convenient, easy-to-use tool for ad hoc database queries.
  • SQL is a database programming language. Programmers embed SQL commands into their application programs to access the data in a database. Both user-written programs and database utility programs (such as report writers and data entry tools) use this technique for database access. 
  • SQL is a database administration language. The database administrator responsible for managing a minicomputer or mainframe database uses SQL to define the database structure and to control access to the stored data. 
  • SQL is a client/server language. Personal computer programs use SQL to communicate over a network with database servers that store shared data. This client/server architecture is used by many popular enterprise-class applications. 
  • SQL is an Internet data access language. Internet web servers that interact with corporate data and Internet application servers all use SQL as a standard language for accessing corporate databases, often by embedding SQL database access within popular scripting languages like PHP or Perl. 
  • SQL is a distributed database language. Distributed database management systems use SQL to help distribute data across many connected computer systems. The DBMS software on each system uses SQL to communicate with the other systems, sending requests for data access. 
  • SQL is a database gateway language. In a computer network with a mix of different DBMS products, SQL is often used in a gateway that allows one brand of DBMS to communicate with another brand. 

Lambda Expressions

Lambda expression provides a powerful alternative to the anonymous method. The principal use of lambda expressions is found when working with LINQ, but they are also applicable to delegates and events.

The Lambda Operator (=>)
All lambda expressions use the lambda operator (=>).This operator divides a lambda expression into two parts. On the left the input parameter (or parameters) is specified. On the right is the lambda body. The => operator is sometimes verbalized as “goes to” or “becomes.”

C# supports two types of lambda expressions – Expression Lambda and Statement Lambda. It is the lambda body that determines what type is being created.

Expression Lambda – If the lambda body consists of a single expression, then an expression lambda is being created. In this case, the body is free-standing — it is not enclosed between braces.
The expression on the right side of the => acts on the parameter (or parameters) specified by the left side. The result of the expression becomes the result of the lambda operator and is returned.
Syntax:
a) Single Parameter – param => expr
b) Multiple Parameter – (param-list) => expr

Here is a simple expression lambda: iIndex => iIndex + 2
Here iIndex is the parameter that is acted on by the expression iIndex + 2. Thus, the result is the value of iIndex increased by two.

Here is another example: iCount => iCount % 2 == 0
In this case, this expression returns true if iCount is even and false if it is odd.

Usage of Lambda Expression: To use a lambda expression involves two steps:
1) Declare a delegate type that is compatible with the lambda expression.
2) Declare an instance of the delegate, assigning to it the lambda expression.
Once this has been done, the lambda expression can be executed by calling the delegate instance. The result of the lambda expression becomes the return value.

Example:

// Use two simple lambda expressions.
using System;
// Declare a delegate that takes an int argument
// and returns an int result.
delegate int Increment(int v);
// Declare a delegate that takes an int argument
// and returns a bool result.
delegate bool IsEven(int v);
class SimpleLambdaDemo
{
  static void Main()
  {
    // Create an oIncrement delegate instance that refers to
    // a lambda expression that increases its parameter by 2.
    Increment oIncrement = count => count + 2;
    // Now, use the oIncrement lambda expression.
    Console.WriteLine("Use oIncrement lambda expression: ");
    int iIndex = -10;
    while(iIndex <= 0)
    {
      Console.Write(iIndex + " ");
      iIndex = oIncrement(iIndex); // increase x by 2
    }
    Console.WriteLine("\n");
    // Create an IsEven delegate instance that refers to
    // a lambda expression that returns true if its parameter
    // is even and false otherwise.
    IsEven oIsEven = n => n % 2 == 0;
    // Now, use the oIsEven lambda expression.
    Console.WriteLine("Use oIsEven lambda expression: ");
    for(int i = 1; i <= 10; i++)
    {
      if(oIsEven(i)) Console.WriteLine(i + " is even.");
    }
  }
}

The output is shown here:
Use oIncrement lambda expression:
-10 -8 -6 -4 -2 0
Use oIsEven lambda expression:
2 is even.
4 is even.
6 is even.
8 is even.
10 is even.


Statement Lambda – If the lambda body consists of a block of statements enclosed by braces, then a statement lambda is being created. A statement lambda can contain multiple statements and include such things as loops, method calls, and if statements.

Example:

// Demonstrate a statement lambda.
using System;
// Factorial takes one int argument and returns an int result.
delegate int Factorial(int iEnd);
class StatementLambdaDemo
{
  static void Main()
  {
    // A statement lambda that returns the factorial
    // of the value it is passed.
    Factorial oFactorial = iLimit =>
    {
      int iValue = 1;
      for(int i=1; i <= iLimit; i++)
      iValue = i * iValue;
      return iValue;
    };
    Console.WriteLine("The factorial of 3 is " + oFactorial(3));
    Console.WriteLine("The factorial of 5 is " + oFactorial(5));
  }
}

The output is shown here:
The factorial of 3 is 6
The factorial of 5 is 120

Q. How does the compiler know about the type of data used in a lambda expression?
A. The compiler infers the type of the parameter and the expression’s result type from the delegate type. Thus, the lambda parameters and return value must be compatible with the parameter type(s) and return type of the delegate.

Lambda Expressions



Lambda Expressions – What are they good for?