Basic Concepts of Database

Question : What is a Database ?

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). Together, the data and the DBMS, along
with the applications that are associated with them, are referred to as a database
system, often shortened to just database.

Data within the most common types of databases in operation today is typically
modeled in rows and columns in a series of tables to make processing and data
querying efficient. The data can then be easily accessed, managed, modified,
updated, controlled, and organized. Most databases use structured query
language (SQL) for writing and querying data.

Question : What is a database management system?

A database management system (DBMS) is a software tool that enables users to

manage a database easily. It allows users to access and interact with the
underlying data in the database. These actions can range from simply querying
data to defining database schemas that fundamentally affect the database
structure.

Furthermore, DBMS allow users to interact with a database securely and

concurrently without interfering with each user and while maintaining data
integrity.

Question : What are the advantages and disadvantages of Manual Record

keeping ?

Answer : Advantages of Manual Record Keeping System :

1. It is less expensive.
2. Less risk of data loss.
3. No software specialised person is required.

Disadvantages of Manual Record Keeping System :

1. No sharing of data.
2. More chances of inconsistent data.
3. Making correction is very time consuming.

Question : What are the advantages and disadvantages of Electronic Record

Keeping System ?

A System in which records are maintained in computer system instead of in paper.

Advantages of Electronic Record Keeping System :

1. Less paper wastage.

2. Searching of record is very simple.
3. Easy to backup the documents.

Disadvantages of Electronic Record Keeping System :

1. More expensive.
2. More risk to data loss.
3. A Software specialised person is required to manage this system.

Question : What is a Database Management System ?

A database management system (DBMS) is a software that can be used to create

and manage databases. Some examples of open source and commercial DBMS
include MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB etc.

Databases are widely used in various fields. Some applications are given below :

Attributes : The columns of a relation are the attributes which are also referred as
fields. for example : In the table “Student” given below, there are four attributes.

Roll_no Name Class Section

1 Amit Kumar XI A

4 Simar Singh XI B

Tuple : Each row of data in a relation (table) is called a tuple. It is also known as
record. for example In the table “Student” given above, there are two tuples.

Domain : It is a set of values from which an attribute can take a value in each
row. Usually, a data type is used to specify domain for an attribute. For example,
in “Student” relation given above, the attribute Roll_no takes integer values and
hence its domain is a set of integer values.

Degree : The number of attributes in a relation is called the Degree of the relation.
For example, the relation “Student” given below with four attributes is a relation
of degree 4.

Roll_no Name Class Section

1 Amit Kumar XI A

4 Simar Singh XI B
Cardinality : The number of tuples in a relation is called the Cardinality of the
relation. For example, the cardinality of relation “Student” is 2 as there are 2 tuples
in the table.

Roll_no Name Class `1245

1 Amit Kumar XI A

4 Simar Singh XI B

Database Schema : It is the skeleton of the database that represents the structure
(table names and their fields/columns), the type of data each column can hold,
constraints on the data to be stored (if any), and the relationships among the
tables.

Data Constraint : Certain restrictions or limitations on the type of data that can
be inserted in one or more columns of a table during table creation is called data
constraint. Constraints are used to ensure accuracy and reliability of data in the
database.

Meta-data or Data Dictionary : The database schema along with various

constraints on the data is stored by DBMS in a database catalog or dictionary,
called meta-data. A meta-data is data about the data.

Database Instance : When we define database structure or schema, state of

database is empty i.e. no data entry is there. After loading data, the state or
snapshot of the database at any given time is the database instance.

Query : A query is a request to a database for obtaining information in a desired

way. Query can be made to get data from one table or from a combination of
tables.

Data Manipulation : Modification of database consists of three operations viz.

Insertion, Deletion or Update. Insertion means adding a new record in a table.
Deletion means removing an existing record from a table. Updation means
editing an existing record in a table.

Database Engine : Database engine is the underlying component or set of

programs used by a DBMS to create database and handle various queries for
data retrieval and manipulation.

Question : What are the different Keys in Relational Database ?

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a

tuple (or row) in a relation (or table). Keys are also used to establish relationships
between the different tables and columns of a relational database. Individual
values in a key are called key values.
  Candidate Key : Those fields which can act as a primary key in a table are
called Primary Key.
 Primary Key : A field which uniquely identifies each and every record in
table is called primary key.
 Alternate Key : Alternate keys are those candidate keys which are not the
Primary key. There can be only one Primary key for a table. Therefore all the
remaining Candidate keys are known as Alternate or Secondary keys.
 Foreign Key : A foreign key is used to represent the relationship between
two relations. A foreign key is an attribute whose value is derived from the
primary key of another relation.

Queston : Qhat are the Advantages of RDBMS over a Traditional File System ?

File System: A File Management system is a DBMS that allows access to single
files or tables at a time. In a File System, data is directly stored in a set of files. It
contains flat files that have no relation to other files (when only one table is
stored in a single file, then this file is known as a flat file).

DBMS: A Database Management System (DBMS) is application software that

allows users to efficiently define, create, maintain and share databases. Defining
a database involves specifying the data types, structures and constraints of the
data to be stored in the database.

Advantages of DBMS over File system:

 Interfaces: DBMS provides different multiple user interfaces like graphical user
interface and application program interface.
 Easy Maintenance: DBMS can be is easily maintainable due to its centralized
nature.
 Data sharing: The file system does not allow sharing of data or sharing is too
complex. Whereas in DBMS, data can be shared easily due to a centralized
system.
 Data concurrency: Concurrent access to data means more than one user is
accessing the same data at the same time.
 Data searching: For every search operation performed on the file system, a
different application program has to be written. While DBMS provides inbuilt
 searching operations. The user only has to write a small query to retrieve data
from the database.
 Data integrity: There may be cases when some constraints need to be applied to
the data before inserting it into the database. The file system does not provide
any procedure to check these constraints automatically. Whereas DBMS
maintains data integrity by enforcing user-defined constraints on data by itself.
 System crashing: In some cases, systems might have crashed due to various
reasons. It is a bane in the case of file systems because once the system crashes,
there will be no recovery of the data that’s been lost. A DBMS will have the
recovery manager which retrieves the data making it another advantage over
file systems.
 Data security: A file system provides a password mechanism to protect the
database but how long can the password be protected? No one can
guarantee that. This doesn’t happen in the case of DBMS. DBMS has specialized
features that help provide shielding to its data.
 Backup: DBMS creates a backup subsystem to restore the data if required.

Question : Explain the following Terms :

Data: Basic/raw facts about something which is not organized, for example details
of some students which is not organized.

Data Item: Each piece of information about an entity, such as name of a person
or address, age or name of a product or the price is a Data Item.

Database: A well organised collection of data that ensures safety, security and
integrity of data.

DataBase Management System(DBMS): Comprehensive software that provides

the essential services to create, manage and maintain the databases. In short, a
DBMS provides the means to store the data in the database, to edit or delete the
data stored, to search and analyze the data in the database. They also provide
various safety and security mechanisms that ensures that in any case stored data
will be safe and accessible.

Relational DataBase Management System(RDBMS): In a relational data model,

the data is organized into tables (i.e. Rows and Columns). These tables are called
Relations. A row in a table represents a relationship among a set of values. Since
table is a collection of relationships it is generally referred to using the
mathematical term Relation.

Database Systems: Systems comprising of Databases and Database

Management Systems are simply referred as database systems.

Advantages of Data Base System:

1) Reduce data redundancy (duplication of data).
2) Control data inconsistency to a large extent.
3) Database facilitate sharing of data.
4) Enforce standards.
5) Centralized databases can ensure data security.
Question : What is MySQL ?

MySQL: It is an Open Source RDBMS Software that uses Structured Query

Language. It is available free of cost. Key Features of MySQL:
1. High Speed.
2. Ease of Use.
3. Available Free of Cost.
4. Supports standards based SQL.
5. Provides portability.
6. High Security.
7. Provides many data types.
8. Handles large database.

Question : Whats do you understand by The Structured Query Language(SQL) ?

SQL (pronounced SEQUEL for Simple English Query Language) is Non-procedural

universal data access language used to access and manipulate data stored in
nearly all the data bases available currently.

SQL standards are defined by ANSI (American National Standards Institute). SQL
statements are used to retrieve and update data in a database.

SQL works with database programs like MySQL, MS Access, DB2, Informix, MS SQL
Server, Oracle, Sybase, etc. Most of the SQL database programs also have their
own proprietary extensions in addition to the SQL standard.

Question : What are the types of SQL Commands ?

SQL commands can be classified into the following:

Data Definition Language (DDL): A database scheme is defined by set of

definitions, which are expressed, by a special set of commands called Data
Definition Language (DDL). They are used to create tables, databases, identify
data items, provide unique names to the data items and to define the length and
provide the range of values that each data item can assume. They are CREATE
TABLE, ALTER TABLE and DROP TABLE commands.

Data Manipulation Language (DML): The data manipulation language (DML)

handles operations such as entering rows into a table, changing data, deleting
rows, and extracting data from rows and tables. With DML, one does not change
the table’s structure, but rather its contents. It contains commands like INSERT,
UPDATE and DELETE.

Transaction Control Language (TCL): A transaction is a one complete unit of work.

A transaction is successfully completed if and only if all its constituent steps are
successfully completed. To manage and control the transactions, the transaction
control commands are used. e.g. COMMIT, ROLLBACK, SAVEPOINT.