Database Concepts

Introduction to Database
Database is a collection of interrelated data items stored together to serve multiple applications.
● A database is an organized collection of structured information, or data, typically
stored electronically in a computer system. A database is usually controlled by a
database management system (DBMS).
● It is a computer-based record keeping system.
● It not only allows storage but also allows us modification of data as per requirements.

Database Management System (DBMS):

● A DBMS refers to a Database Management System. It is a software that is responsible for
storing, manipulating, maintaining, and utilizing databases. A database along with a DBMS
is referred to as a database system.
● There is various DBMS software available in the market like: Oracle, MS SQL Server,
MySQL, Sybase, PostgreSQL, SQLite.

Purpose of DBMS:
In typical file processing system permanent records are stored in various files. Several
different application programs are written to extract records from and add records to
appropriate files. But this typical file processing systems has several major limitations and
disadvantages, such as
1. Data Redundancy: Same information is stored in more than one file. This would result in
wastage of space.
2. Data Inconsistency: Mismatched multiple copies of same data, is known as Data
Inconsistency.
3. Lack of Data Integration: As data files are independent, accessing information out of
multiple files becomes very difficult.
4. Data Dependence: Data are stored in a specific format or structure in a file. If the
structure or format itself is changed, all the existing application programs accessing that
file also need to be changed.
Some more are Unsharable Data, Insecure Data, Incorrect Data, etc.

A database management system can overcome these problems and also adds a lot of
advantages like Reduced Data Redundancy, Control Data Inconsistency, Sharing of
data, Ensure data Integrity, Enforce standard, etc.

Relational Database Model:

● In relational database model data is organized into tables (i.e., rows and columns). These
tables are also known as relations.

Anjeev Singh | [Link]/@anjeevsingh | [Link] | [Link] | 1/40

 ● Data is organized in two-dimensional tables called relations. The tables or relations are
related to each other.
● A row in a table represents a relationship among a set of values.
● A column represents the field/attributes related to relation under which information will
be stored.
● For example:
○ if we want to store details of students then: Roll, Name, Class, Section, etc. Will be
the column/attributes and the collection of all the column information will become a
Row/Record

Sample Tables:

Component of a table
● Byte: group of 8 bits and is used to store a character.
● Data Item: Smallest unit of named data. It represents one type of information and often
referred to as a field or column information.
● Record: Collection of data items which represent a complete unit of information
● Table: Collection of all Rows and Columns.

Characteristics of Relational Data Model

● Data is arranged into rows and columns,
● Each relation is represented as a table.
● Every row in a table represents a single entity.
● At any given row or column position, there is one and only one value.
Advantages of a Relational Data Model
● Changes made in the table structure do not affect the data access or other Application
programs.

Anjeev Singh | [Link]/@anjeevsingh | [Link] | [Link] | 2/40

 ● Tabular view also provides easier database design, use, implementation, and
management.
● Built-in query support.
● Mathematical operations can be successfully carried out using RDBMS.

The limitations of relational model are:

● RDBMS incurs hardware and system software overheads.
● The size of the database becomes very large.
Common RDBMS / DBMS
● Oracle, MS SQL Server, MySQL, IBM DB2, IBM Informix, SAP Sybase, Adaptive Server
Enterprise, SAP Sybase IQ, Teradata, PostgreSQL, SQLite, etc.
● Out of these MySQL, PostgreSQL and SQLite are Open-source implementations.

Common DBMS Tools for Mobile Devices

● SQL Anywhere, DB2 Everywhere, IBM Mobile Database, SQL Server Compact, SQL
Server Express, Oracle DatabaseLite, SQLite, SQLBase etc.
● Out of these, SQLite is a public domain open source implementation.

Various Terms Used in Relational Data Model

A relational database is a type of database that stores and provides access to data points that
are related to one another.

Basic Terminologies related to a Relational Data Model

● Entity: An entity is something that exists and an object which can be distinctly identified.
For example, student entity, employee entity,
● Attribute: The term attribute is also used to represent a column.
● Tuple: Each row in a table is known as tuple.
● Cardinality of Relation: It is the number of records or tuples in the relation.
● Degree of Relation: Number of columns or attributes is known as degree of a relation.
● Domain: It defines the kind of data represented by the attribute.
● Body of the Relation: It consists of an unordered set of 0 or more tuples.

Concept of Keys
● A key refers to an attribute/a set of attributes that help us identify a row (or tuple) uniquely
in a table (or relation).
● In relation each record must be unique i.e. no two identical records are allowed in the
Database.
● A key attribute identifies the record and must have unique values.
● A key is also used when we want to establish relationships between the different columns
and tables of a relational database.

Anjeev Singh | [Link]/@anjeevsingh | [Link] | [Link] | 3/40

Types of Keys
There are various types of Keys.
1. Primary Key:
● A set of one or more attributes that can identify a record uniquely in the relation is
called Primary Key.
● There can be only 1 primary key in a table
● Allows only distinct (no duplicate) values and also forces mandatory entry (NOT
NULL) i.e. we cannot leave it blank.
● When two or more than two columns are defined as Primary key in a table, then it is
called Composite Key or Composite Primary Key.

2. Candidate Key:
● In a table there can be more than one attribute which contains unique values. These
Columns are known as candidate keys as they are the candidate for primary key.
● Among these database analysts select one as a primary key based on requirements
like must contain unique value, compulsory entry and where maximum searching is
done etc.

3. Alternate Key:
● In case of multiple candidate keys, one of them will be selected as Primary Key and
rest of the column will serve as Alternate Key
● A Candidate Key, which is not a primary key, is an Alternate Key.

4. Foreign Key:
● Used to create relationships between two tables.
● It is a non-key attribute whose value is derived from the Primary key of another table.
● Foreign key column will for the value in Primary key of another table, if present then
entry will be allowed otherwise data will be rejected.
● Primary Key column table from where values will be derived is known as Primary Table
or Master Table or Parent Table and Foreign key column table will be Foreign Table
or Detail Table or Child table.

Primary Key

Anjeev Singh | [Link]/@anjeevsingh | [Link] | [Link] | 4/40

 Foreign Key

In the above table, we can observe that the VCODE column of TRAVEL table is deriving its value
from VCODE of table VEHICLE. So, we can say that the VCODE of TRAVE table is a foreign
key whose value is dependent upon the Primary key column VCODE of table VEHICLE.

REFERENTIAL INTEGRITY:
Used to ensure the relationship between records in related tables is valid and users don’t
accidentally delete or change the related data.

Referential integrity can be applied when:

● The master table’s column is a Primary Key or has a unique index.
● The related fields have the same data type.
● Both tables must belong to the same database.

When referential integrity is enforced using Foreign Key you must observe
the following rules:
 You cannot enter a value in the Child Table which is not available in Master Table’s
Primary key column. However, you can enter NULL values in foreign key.
 You cannot delete a record from the Master Table if a matching record exists in a related
table.
 You cannot modify or change the Primary Key value in the Master table if its matching
record is present in the related table.

Anjeev Singh | [Link]/@anjeevsingh | [Link] | [Link] | 5/40