Chapter 12 Relational Database

-​ A database may be defined as a collection of interrelated data stored together to serve multiple applications.
-​ The intention of a database is that the same collection of data should serve as many applications as possible.

DBMS (Database Management System )

-​ A DBMS refers to a software that is responsible for storing, maintaining and utilizing databases. A database along with a DBMSis
referred to as a database system.

Data Redundancy

-​ Duplication of data is known as Data Redundancy.

Data Inconsistency

- Mismatched multiple copies of the same data is known as Data Inconsistency.

Purpose Of DBMS
-​ It organizes our files to give the user more control over the data.
-​ It makes it possible for users to create, edit and update data in database files.
-​ Once created, the DBMS makes it possible to store and retrieve data from those database files.

Relational Database Models

-​ In this case the data is organized into tables. These tables are called relations.
-​ A row in a table represents a relationship among a set of values.
-​ Rows of relations are called tuples and the columns are usually referred to as attributes.
-​ Each item has a unique number, name, price.
COMPONENTS OF A TABLE
-​ Byte: A byte is a group of eight bits and is used to store a character.
-​ Data item : A data item is the smallest unit of named data. It may consist of any number
of bits or bytes.
-​ Field: A data item represents one type of information and is often referred to as a field
or data element.
-​ Record: a record is a named collection of data items which represents a complete unit
of information.
-​ Table: A table is a named collection of all occurrences of a given type of logical record.
-​ Primary Key Field : it has a unique value for each row. There is only one primary field.

RELATIONAL DATABASE MANAGEMENT SYSTEMS:

-​ It is based on the collection of tables(relations). The user may query these tables,
insert new tuples, delete tuples or modify tuples.
-​ There are several languages for expressing these operations. Two such languages are:
a.​Relational query language.
b.​Relational algebra.
-​ Examples of common relational database Management System:(These are open
source)
a.​MySQL : It is a relational DBMS that can run virtually on all platforms. It is a part of
open source enterprise stack LAMP.
b.​SQLite: It is a RDBMS system. It is not a client-server database engine, rather it is
embedded into the end program.
 c.​PostgreSQL: It is a general purpose and object-relational database management
system. It is the most advanced open source database.
-​ COMMON DATABASE MANAGEMENT TOOLS FOR MOBILE DEVICE:
a.​IBM Mobile database
b.​SQLite
c.​SQLBase
THE RELATIONAL MODEL TERMINOLOGY

-​ Different terms are used in the relational model they are:

a.​ Relation: A relation is a table i.e., data arranged in rows and columns. A relation has
the following properties :-
1.​The items in a given column of a table are the same kind whereas items in
different columns may not be the same kind.
2.​For a row each column must have an atomic value and also for a row each column
cannot have more than one value.
3.​A relation does not contain two rows which are identical in every column.
4.​There is no order maintained for rows inside a relation. That is we cannot retrieve
anything by saying for example saying row 5 will not give anything column name
that has to be accessed.
b. Domain: A domain is a pool of values from which the actual values appearing in a
given column are drawn.
A domain is said to be atomic if elements of the domain are considered to be invisible units.
c. Tuple : The rows of tables (relations) are generally referred to as tuples.
d. Attributes: The columns of tables are generally referred to as attributes.
e. Degree : The number of attributes in a relation determine the degree of a relation. A
relation having n attributes is said to be a relation of n degrees. And relations of degree n
are n-ary.
f.Cardinality : The number of tuples (rows) in a relation is called the cardinality of the
relation.
--------------------------------------------------------------------------------------------------------------------------
-​ VIEWS : A view is a virtual table that does not really exist on its own right but is instead
derived from one or more underlying base tables.
-​ STRUCTURE OF RELATIONAL DATABASES
1.​KEYS: it helps in distinguishing rows.
-​ Primary keys : A Primary key is a set of one or more attributes that can
uniquely identify tuples within the relation.
Composite Primary Keys: When a primary key is made up of two or more
attributes.
-​ Candidate keys : All attribute combinations inside a relation that can serve as
primary key are candidate keys as they are candidates for the primary key
position.
-​ Alternate keys : A candidate key that is not the primary key is called an
alternate key
-​ Foreign Keys : A non-key attribute, whose values are derived from the
primary key of some other table, known as foreign key in its current table.
 -​ Referential Integrity : It is a system of rules that a DBMS uses to ensure that
relationships between records in related tables are valid and that users don't
accidentally delete or change related data.

MySQL DATABASE SYSTEM

-​ The key role of a database management system is information management.
-​ MySQL database system refers to the combination of a MySQL server instance and a
MySQL database. MySQL operates using client/server architecture in which the server
runs on the machine containing the databases and clients connect to the server over a
network.
-​ MySQL is a multi-user database system, meaning several users can access the
database simultaneously . Here:
1.​The Server listens for client requests coming in over the network and accesses
database contents according to those requests and provides that to the clients.
2.​Clients are programs that connect to the database that connect to the database
server and issue queries in a pre-specified format. MySQL is compatible with the
standard based SQL
-​ SOME OF THE KEY FEATURES OF MySQL are:
1.​Speed : If the server hardware is optimal, MySQL runs very fast.
2.​Easy of Use : MySQL is a high performance, relatively simple database system.
3.​Cost : MySQL is available free of cost. MySQL is an open source database.
4.​Query Language Support: MySQL understands standards based SQL
 5.​Portability: MySQL provides portability as it has been tested with a broad range of
different compilers and can work on many different platforms.
6.​ Data Types : MySQL provides many data types to support different types of data.
7.​Security : MySQL offers a privilege and password system that is very flexible and
secure, and that allows host-based verification.
8.​Scalability and Limits : MySQL can handle large databases.
9.​ Connectivity : Clients can connect to MySQL server using several protocols.
10.​ Localization: The server can provide error messages to clients in many
languages.
11.​ Clients and Tools : MySQL provides several client and utility programs. These
include both command line programmes such as mysqldump and mysqladmin and
graphical programs such as MySQL Administrator and MySQL Query Browser.

MySQL and SQL

-​ In order to access data within the MySQL database, all programs and users must use SQL (
Structured Query Language).
-​ The Structured Query Language is a language that enables us to create and operate on relational
databases, which are sets of related information stored in tables.
-​ PROCESSING CAPABILITIES OF SQL
1.​Data Definition Language (DDL) : The SQL DDL provides commands for defining relation
schemas, deleting relations, creating indexes and modifying 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.
 3.​Embedded Data Manipulation Language
4.​View Definition
5.​Authorization
6.​Integrity
7.​Transaction control

CLASSIFICATION OF SQL STATEMENTS

1.​Data Definition Language (DDL) Commands
It allows us to perform tasks related to data definition. Through these commands we can perform
tasks like:
a.​Create, alter and drop schema objects. It is used to create, or define, or change, or define
objects such as a table, view, an index etc. Create commands are used to create schema
objects, ALTER commands are used to modify or change the definition of already existing
schema and DROP commands are used to delete or remove schema objects.
b.​Grant and revoke privileges and roles. It is used to grant or revoke permissions or privileges to
work upon schema objects.
c.​ Maintenance commands. It is used to analyze information on a table with an aim of
maintaining it.
2.​Data manipulation Language (DML) Commands
-​ A data Manipulation Language is a language that enables users to access or manipulate data
as organized by the appropriate data model.
-​ The DML’s are of two types:
a.​Procedural DMLs require a user to specify what data is needed and how to get it.
b.​Non-Procedural DMLs require a user to specify what data is needed without specifying
how to get it.
3.​Transaction Control Language (TCL) Commands
-​ There are 3 TCL commands:
-​ COMMIT : It makes all the changes made by statements issued, permanent.
-​ ROLLBACK: It undoes all changes since the beginning of a transaction or since a savepoint.
-​ SAVEPOINT: It marks the point upto which all earlier statements have been successfully
completed and if required-in case of failure– one may undo the changes.
-​ SET TRANSACTION : It establish properties for the current transactions
4.​Session Control Commands
5.​System Control Commands