Topics discussed in the post the following questionnaire :
Q1) What are some predominant features of No-SQL databases ?
Q2) Why NoSQL is called "SQL with a No " with "No" as the preceding string on SQL ?
Q3) How do organisations leverage NoSQL data for their businesses ?
Q4) Where are NoSQL databases useful ?
Q5) What do you mean by the statement , "The constraints of a relational database are relaxed ? "
* The Cartesian product operation is denoted by a cross ( X )
which allows us to combine information from any two relations .
* One can write the Cartesian Product of Relations R1 and R2 as
R1 x R2
* A relation is by definition a subset of a Cartesian product of
a set of domains .
* From the definition , one can have an intuition about the
definition of the Cartesian product operation .
* Since the same attribute name may appear in both R1 and R2 ,
one may need to devise a naming schema to distinguish between the attributes .
* One can do the attachment to an attribute , the name of the
relation from which the attribute originally came from .
* For example , the relation schema for
r = borrower * loan is :
(
borrower.customer_name ,
borrower.loan_number,
loan.loan_number ,
loan.branch_name
,
loan.branch_name
,
loan.amount
)
* With the schema , one can distinguish between
borrower.loan_number from loan.loan_number as both the attributes do have the
same name but the main relational table for the table are different which are
loan and borrower
* The naming convention for any of the relations or schemas
requires that the relation should have distinct names
* In general , if we have two relations r1(R1) and r2(R2) , then r1 x r2 is a relation whose schema is the concatenation of relations R1 and R2 .
Relation R contains all the tuples t for which there is a tuple
t1 in relation r1 ; and a tuple t2 in r2 for which t|R1| = t|R1| and t|R2| =
t2|R2|
* Suppose we want to find the names of all the customers who have a loan at the "PerryRidge" branch , then one may need the information in both the loan relation and the borrower relation through the given selection statement .
<< FIG - 01 >>
* From the above relation , if one wants to find if there is a
cartesian product operation that associates every tuple of loan with every
tuple of borrower , with the customer having a loan in the “PerryRidge” branch
, then there is some tuple in borrower x loan that contains the name of the
customers which can be obtained by
criterion / condition from one table borrower to table loan
where borrower.loan_number =
loan.loan_number
Union Operation - RDBMS
Fundamental Relational Algebra Concept
=======================================
* Scenario where a Union Operation over a rdbms table could be
used : -
Consider a query to find the names of all the bank customers who
have either an account or a loan or both in a bank .
* To find the names of the customer who have an account and also
a deposit account in the bank , the search query would consider the search to
take over "depositor" relation table and the "borrower" relation
table .
* In order to find out the names of all the customers with a
loan in the bank , the query that could be used for the operation would be :
{figure-01}
The upper relational equation is a form of projection which
allows us to produce the relevant relation that returns the argument relation
specified within the parenthesis . So as we are interested in finding out the names
of the customers who have a loan account , the
relevant result would be fetched from the projection relation as
mentioned in the above statement .
* Similarly , if we want to know the names of all the customers
with an account in the bank , then it can be expressed in the following
projection equation :
{figure-02}
* Therefore , in order to find the answer to the raised question in our scenario that is discussed in the opening statement , one needs to do a "Union" operation over the two sets which is : we need all the customer names that appear in either both or two of the relations which can be found out by using the binary operation Union upon both the queries . The relevant relational equation can be represented in the given manner :
* The resulting relation for the query would result in a relation
with the relevant tuples from both the tables
* The resulting relations are sets from which duplicate values
are eliminated .
In Hindsight :
In our example , we took the union of two sets , both of which
consisted of the attribute "customer_names" values . In general , one
must ensure that unions are taken between compatible relations which would generate
the appropriate results without duplicates.
* Points to note when using a Union Operation over relational
sets :
If r is a set
and s is set , and one needs to find { r U s } , If r is a set and s is set ,
and one needs to find { r U s } , then the conditions that should satisfy and
hold for both relations
1)
The relations r and s must
be of the same type and they must have the same number of attributes
2) The domains of the ith attribute of r and ith attribute of s must be the same for all values of i . One may note that , both r and s are either database relations or temporary relations that are the result of relational algebra expressions as given in figure 1 and figure 2 of the article .
Cardinality Ratio concept in DBMS
Cardinality Ratio
It is the number of relationship instance that an
entity can participate in . If one tries to understand the relationship between
Student and Guide , then the relationship between the Student entity and the
guide entity can be described in the following entity - relationship diagram .
Considering the given case , one can observe and try to understand the given relationship with the help of a Entity Relation Diagram with the help of a Set Diagram .
Here , some scenarios emerge like the given case :
Case-1
One guide (G1) can guide two students (S1) and (S2)
whereas (S1) can only be guided by guide (G1) .
Case-2
Third Student (S3) can be guided by guide (G2) .
This is a kind of restriction set by the relationship where both the entity sets are mutually associated to each other by the relation between them . So here , the number of instance objects relation among each other is restricted and it can be also observed how the cardinality relationship is mapping one particular instance to another particular instance object through the given relationship diagram which depicts the way the entities can participate in .
From the above one can get a pretty good understanding of what a Cardinality Ratio is : -
Definition of Cardinality Ratio
The number of relationship instances that an entity
can participate in is called as the Cardinality Ratio .
From the diagram we deduce that :
Relationship R1 exists from Department D1 to HOD H1
.
Relationship R2 exists from Department D2 to HOD H2
.
Relationship R3 exists from the Department D3 to
HOD H3 .
A scenario over where such a type of relationship
exists where there is only one relationship mapping from one Set's instance
object to another Set's instance object is called a One to One relationship .
One can get a better understanding of this through
the help of a E-R diagram shown at the bottom of the above figure .
===================================================================
The second type of relationship that exists is called One to Many Relationship
In the given figure , one can notice that there is a relationship existing between many departments and one student . Each department will have multiple students and thus one can notice from the given relation diagram that multiple relations exist from one department to student of another set but the student would be associated with only one department .
This is an instance of Many to One Relationship which is depicted by the Ratio form of representation (1:M) .which is another form of cardinality ratio expressed in the form of Many to One relation .
This means that Many Instances of any particular Entity Type will be associated or will be participating in the "Has" relationship .
Many to Many Relationship
In the given example , if one can see then one
would be able to determine that there is Many to Many Relationship between the
students set and Subjects set .
This can be rightfully depicted in the form , Relation between a Student on the left hand side and Subject on the right hand side of the Relationship Diagram . One can notice that multiple instance objects belonging to the set "Student" bear a many to many relationship between students of the other entity set which is the "Subject" set .
The depiction of the relation has been done through the help of an E-R diagram .
===================================================================
Now , we can get to understand the behaviour of
these relationships that depict the manner in which relationships exist between
instance objects of one set with another or multiple other sets in the article's showcased manner
SQL - Structured Query Language
( a basic overview )
·
The backbone of any Query Based Language is
Relational Algebra which provides a concise , formal notation for
representation of queries . However , commercial database systems required a
query language which would be much user friendly and for this purpose SQL short
for Structured Query Language was created which was the most influential
commercially marketed query language .
·
SQL uses a combination
of relational algebra and relational calculus constructs for its construction
and implementation
·
Although we refer to
SQL language as a "query language" , SQL can do much more than
just query a database . SQL can define the structure of the data , modify the
data in the database and it can also specify security constraints . This means
that SQL can present to its user a brief highlight of the size of the database
that the user is working upon . SQL can also be used to define any database and
also the tables that a user wants to create and maintain over a database
residing in the system as well as server memory . Also , on top of that SQL can
be used to modify the structure of the database over which other data reside .
Also , SQL can be used to specify the various security constraints that need to
be defined over a database which would make the data specific to the users it
is meant for by provisioning the access specifiers for access limitation as
well as defining the needed integrity constraints which would ensure the ACID
properties of a suitable relational database management system .
·
In this article a complete User's Guide and
How to Use / Implement a SQL installation won't be discussed nor is it intended
for in this basic article but the aim of the article is to present SQL's
fundamental constructs and concepts for brief usage by any User who is not only
new to SQL but may be an intermediate or highly skilled versatile user of the
language .
·
Background of SQL -
IBM developed the original version of SQL which was originally called as
"Sequel" ( Any of the present day readers of the article can perhaps
think of the word Sequel as some sort of movie sequel kind of the unlimited
sequels of the Star Wars series .. seems a poor joke ) as part of the System R
project of the early 1970's . The Sequel language has evolved since then , and
its name has changed to SQL ( Structured Query Language ) . Many applications
and products in the form of standalone and server based products now implement
SQL as a part of their database engine and thus over the years SQL has become
the supreme rdbms package which is not only widely used but also the most user
friendly product among all the users of the current database users .
·
In 1986 , the American National Standards
Institute ( ANSI ) and the International Organisation of Standardisation ( ISO
) published an SQL standard which used to be called as SQL-86 at that point of
time ( the postfix 86 succeeding SQL obviously comes from the manufacturing
year of 1986 ) . Following this the standardisation organisation ANSI published
an extended standard for SQL , SQL -89 in the year 1989 . The next version of
the standard was then published in the year 1992 and thus the version was
therefore called as SQL-92 standard , and then later versions followed like
SQL-1999 etc . The most recent version that I have worked upon and also is
installed over my system is the SQL-2003 version which serves most of the
purpose . Apart from some server connection issues and importing data from
other incompatible transactional data formats , I have not faced any major
hurdles while using the present version of standalone SQL installation ,
However , if one wants to read a full scale bibliographic notes reference of
these standards , one may read them in the documentation notes of the
accompanying installation CD or executable software .
·
SQL language has the
several parts namely :
1) Data Definition
Language (DDL)
The SQL DDL provides commands for defining the
relation schemas , deletion of the relations and modification of the relation
schemas .
2) Interactive Data
Manipulation Language (DML )
The SQL DML
includes a query language based on both the relational algebra and the tuple
relational calculus . Which means that one can enter queries over a table and
find out the necessary information from the database . The search query is
mostly written in a language which implements the core
principles and working of relational algebra and
relational calculus as their underlying coding platforms
3) Integrity
The SQL DDL includes commands for specifying
integrity constraints that the data stored in the database must satisfy. All
the necessary updates that violate the integrity constraints are not allowed
over the query engine of SQL.
4) View Definition
The SQL DDL includes commands for defining the
views upon a database. Views are nothing but a sub-part representation of a
main table present over a database and it consists of only the structure of the
database with filtered or unfiltered data from the main table .However any form
of DML statements cannot be made to work upon a view.
5) Transaction Control
SQL includes commands for specifying the beginning
and ending of the transactions . The broader meaning of this statement says
about the way in which stored procedures , cursors and triggers could be used
for the sake of making and breaking any transaction at the desired point of
time
6) Embedded SQL and
Dynamic SQL
Embedded and Dynamic SQL defines the manner in
which SQL statements can be embedded within general purpose programming
languages such as C , C++ , Java , PL/I , Cobol , Pascal and Fortran .
7) Authorization
The SQL DDL includes commands for specifying access
rights to relations and views . This means that SQL provides its users with the
potential to access the tables present over a given / created / hosted database
specific rights for access and views to its data within the database . Many
database systems support most of the SQL-92 standards and some of the new
constructs which are present in SQL : 1999 and SQL : 2003 although no databases
provide non-standard features which defer from the detailed and provided
features of SQL as provided in the standard specification manual of the
different release versions of SQL .
·
The set of relations
within a database must be specified to the system by means of a data definition
language . Here , it means that all the forms of defining the structure and
schema of a database and a relation are to be done with the help of a standard
language called as a Structured Query Language .
·
The SQL DDL allows the
provision of specification of not only a set of relations but also information
on each of the relations over a database which also includes the following
types :
1) The schema for each of the relation within the database
This means that SQL language can be used for the
creation of the schema definition of each of the relation within the database
over which one is working upon . For example if there are a set of associated
relational tables over a database management system in the given format :
branch (branch_name,branch_city,assets)
customer(customer_name,customer_street,customer_city)
Here , one can notice that the two existing tables
within the given supposed database consists of primarily two tables that is
branch and customer which has the following attributes as given in the
parenthesis brackets . This representative definition of the two relational
tables can be practically created and implemented using the SQL language using
the create table command which takes the attribute names as parameters within
the database schema definition function .
2) The domain of values associated with each of the
attribute .
So from the above picture one can notice what are
the essential domain types present in SQL that support the purpose of Data
Definition within a database relation . So whatever data needs to be defined
and stored within the columns/attributes of a relational table they should adhere
to these domain standards and any exception in the datatype and format of the
data is not facilitated within the SQL scripts .
3) The Integrity Constraints
The purpose of defining integrity constraints upon
a database is to make sure that the database consists of attributes which
adhere to the definition set upon the database as per the manner prescribed
upon the database as in the case wherever not null is defined , the attribute
cannot have a null value within it ;
wherever the constraint primary is defined , then
it will mean that the values contained within the attribute for the relation
cannot having non-unique values within it but columns / attributes where
primary is not declared from the beginning , those attributes / columns can
contain non--unique values .. etc etc
4) The set of indices to be maintained for each of
the relation
Tables and Relations are needed to be maintained
and described for the purpose of better searching , easier access and book
keeping of relational tables within the database and as such indexing is one of
the key parameters while considering the case of creation / definition of the
data over databases
5) The security and authorization of information
for each of the relation
This aspect of Data definition for a database and all the relations within it is a mandatory criteria for maintenance of security of the database ; otherwise unauthorised users would access the data and make unregulated transactions or tampering which might make the purpose of database and relational database invalidated
6) The physical storage structure of each relation
on a disk
Sometimes whenever a relational table and other
associated tables are created , the physical storage limit is specified so that
stack overflow and buffer overflow type of memory issues could be handled well
in time or in advance . Constraints related to data size limits are also set
while defining the attributes during
the table creation commands so that any issues
relating to memory would be handled while data defining and storage and
exceptions would not arise afterwards .
