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)
In October 1985, Codd presented 12 rules that a database
must obey if it is to be considered truly relational:
Information
rule—All
information in a relational database is represented explicitly at the logical
level and in exactly one way — by values in tables.
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.
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.
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.
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)
View
updating rule—All views
that are theoretically updateable are also updateable by the system.
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.
Physical
data independence—Application programs and terminal activities remain logically unimpaired
whenever any changes are made in either storage representations or access
methods.
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.
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.
Distribution
independence —A relational DBMS has distribution independence.
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).
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.
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 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 theinput parameter (or
parameters)is specified. On
the right is thelambda body.
The=>operator is sometimes verbalized as
“goes to” or “becomes.”
C# supports two types of lambda expressions –Expression LambdaandStatement
Lambda. It is the lambda body that determines what type is being created.
Expression Lambda– If the lambda body consists of asingle expression, then anexpression lambdais 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
HereiIndexis the parameter that is acted on
by the expressioniIndex + 2.
Thus, the result is the value ofiIndexincreased by two.
Here is another example:iCount => iCount % 2 == 0
In this case, this expression returns true ifiCountis 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.
if(oIsEven(i))
Console.WriteLine(i + " is even.");
}
}
}
The output is shown here:
UseoIncrementlambda expression:
-10 -8 -6 -4 -2 0
UseoIsEvenlambda expression:
2 is even.
4 is even.
6 is even.
8 is even.
10 is even.
Statement Lambda– If the lambda body consists of ablock of statements enclosed by
braces, then astatement
lambdais being created. A
statement lambda can contain multiple statements and include such things as
loops, method calls, andifstatements.
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.
