Class -10

Unit-8
DATA BASE MANAGEMENT SYSTEM

Full forms
1. DBA-Database Administrator
2. DBMS-Database Management System
3. RDBMS-Relational Management System
4. DDL-Data Definition Language
5. DML-Data Manipulation Language
6. SQL-Structured Query Language
7. DAL-Data Access Language
8. DCL-Data Control Language
9. TCL-Transaction Control Language
10. DQL-Data Query Language
11. Extension of database file is .odb
NOTES
DATA- The input to the computer is known as Data. Data comprises of numbers, alphabets or any other form
like pictures, sound etc. It is collected from various sources but not organized. It cannot be used directly for
decision making.
Eg:- Sugam scored 704 marks out of 800.
INFORMATION- The output which we get from processor is known as Information. It is the data that has been
organized and processed. It can be used for decision making.
Eg:- Danish scored more marks than sugam did.
KNOWLEDGE -Knowledge refers to the practical use of information.
DATA ITEM OR FIELD
A set of characters which are used together to represent a specific data elements. They are also known as
Field. Eg:- Roll number of a student may be represented as Roll_No.
RECORD- Collection of fields is called a Record. It is always represented as a row in a table. Eg:- The class
record for a student contains such data fields as Roll_No, Name, Marks etc.
FILE -Collection of records is called a file. Eg: A class file might consists of the student marks record for a class.
Eg:- File : Student
Roll_No Name Marks
2001 Sugam 94
2002 Danish 69
DIFFERENCE BETWEEN DATA AND INFORMATION
Data Information
Data is raw, unorganized facts that need to be Information is the data that has been processed
processed. and organized.
Data is an individual unit that contains raw Information is a group of data that collectively
materials which do not carry any specific meaning. carries a logical meaning.
Data doesn’t depend on information. Information depends on data.
Information is measured in meaningful units like
It is measured in bits and bytes.
time, quantity, etc.

RELATION OR TABLE- A relation in a database means a ‘TABLE’. As we all know, tables are something which is
made up of rows and columns, a relation in database will also have rows and columns.
TABLE STRUCTURE- Table structure refers to the name, size and type of various fields. Table structure
comprises of:
 Fields name  Field Properties
 Field types  Primary Key
 Length( Field size)

DATABASE-A Database is defined as a collection of interrelated data stored together without harmful or
unnecessary redundancy to serve multiple application.
DATABASE MANAGEMENT SYSTEM (DBMS)
A database management system is a software package with computer programs that controls the creation,
maintenance and use of a database. A DBMS allows different user application programs to concurrently access
the same database.
POPULAR RELATIONAL DATABASE MANAGEMENT SYSTEMS:
 Open Office Base  Microsoft SQL Server
 Microsoft Access  MySQL  Oracle
RDBMS- A relational database management system (RDBMS) is a database management system that is based
on the relational model. In the relational model of a database, all data is represented in terms of tuples (rows),
grouped into relations (tables). A database organized in terms the relational model is a relational database.
TUPLE OR ROW OR RECORD- A row represents a single, data items in a table. Each row in a table represents a
set of related data, and every row in the table has the same structure.
COLUMNS OR FIELD OR ATTRIBUTE- A column is a set of data values of a particular simple type, one for each
row of the table. For eg. Emp_Code ,Emp_Name , Emp_Address etc.
DEGREE -The degree of a relation is the number of attributes it contains.
CARDINALITY- The cardinality of a relation is the number of tuples it contains.
DATABASE SERVER- Database Server is a computer that is dedicated to database storage and retrieval. It is a
computer system that provides other computers with services related to accessing and retrieving data from
the database.
KEYS- A Key allows us to identify a set of attributes that distinguish rows from each other.
Types of keys
1. PRIMARY KEY- A Primary Key is a set of one or more attributes that can uniquely identify tuples within the
realtion. Every relation does have a primary key. Primary key cannot be NULL.
Eg:- EID, ROLL_NO etc.
2. CANDIDATE KEY- All attribute combinations inside a relation that can serve as primary key are candidate
keys as they are candidates for the primary key position. In the case two or more candidate keys, the database
analyst decides one of them as primary key for the relation.
3. SUPER KEY/COMPOSITE KEY- A Super Key is a set of one or more attributes that taken collectively allows us
to identify uniquely an entity in the entity set. Eg:- EID attribute of the table Customer is sufficient to
distinguish one customer entity from another. The EID is a super key.
4. ALTERNATE KEY- In case of two or more candidate keys, only one of them serves as the primary key. The
rest of them are alternates only. A candidate key that is not a primary key is called an Alternate key.
5. FOREIGN KEY- The foreign key identifies a column or set of columns in one (referencing) table that refers to
a column or set of columns in another (referenced) table. A non-key attribute whose values are derived from
the primary key of some other table, is known as foreign key in its current table.
ADVANTAGES OF A DATABASE SYSTEM
1) Reduces Data Redundancy: Database reduces data redundancy (duplication of data)
2) Sharing of Data: In a database, the users of the database can share the data among themselves.
3) Data Integrity: Data integrity means that the data is accurate and consistent in the database.
4) Data Security: Database provides data security as only authorized users are allowed to access the database
and their identity is authenticated by using a username and password.
5) Privacy: The privacy rule in a database states that only the authorized users can access a database
according to its privacy constraints.
6) Backup and Recovery: Database Management System automatically takes care of backup and recovery.

DISADVANTAGES OF DATABASE SYSTEM

1. DBMS software is very costly.
2. High hardware cost
3. High Conversion Cost
4. Higher Programming Cost
5. Backup and Recovery are more difficult.
DATABASE MANAGEMENT SYSTEM HAS FIVE COMPONENTS:
1. DATA- It is an important component of the database management system. The main task of DBMS is to
process the data. Databases are used to store the data, retrieved, and updated to and from the databases.
2. SOFTWARE- Software is the actual DBMS between the physical database and the users of the system. All
the requests from the user for accessing the database are handled by DBMS.
3. HARDWARE- The hardware is the actual computer system used for keeping and accessing the database. The
conventional DBMS hardware consists of secondary storage devices such as hard disks. Databases run on the
range of machines from micro computers to mainframes.
4. USERS- There are a number of users who can access or retrieve the data on demand using the application
and the interfaces provided by the DBMS.
5. PROCEDURES-refer to general instructions to use database management system. This include procedures to
setup and install a DBMS, to login and logout of DBMS software, to manage databases, to take backups ,
generating reports etc.
DIFFERENCE BETWEEN DBMS AND RDBMS

DBMS RDBMS
DBMS stores data as file. RDBMS stores data in tabular form.
Multiple data elements can be accessed at the
Data elements need to access individually.
same time.
It is used for small organization and deal with small
It is used to handle large amount of data.
data.
It supports single user. It supports multiple users.
Low software and hardware necessities. Higher software and hardware necessities.
Examples: MySQL, PostgreSQL, SQL Server, Oracle,
Examples: XML, Microsoft Access, etc.
etc.

Base uses the following objects for data management:

TABLE- Tables are comprised of rows and columns of data. A Table is a collection of records that can be
divided into fields. Rows are called records and columns are called fields.
QUERY- In a database table, you use a query to search, view and modify the data that exists in the table.
FORMS- A form is a database object that you can use to enter, edit, or display data from a table or a query.
You can use forms to control access to data, such as which fields of data are displayed.
REPORTS
Reports are a great way to organize and present data from your Access database. Reports enable you to
format your data in an attractive and informative layout for printing or viewing on screen. Reports are often
used to present a big-picture overview, highlighting main facts and trends.
DATATYPES
Data Types are used to define what data may be stored in a column and how that data is actually stored.
Different Data types are:
1. NUMERIC-It stores numbers. Eg:- mobile no, roll number etc.
2. STRING- It stores names, addresses etc.
3. DATE AND TIME-Date time data types in a database can be used for storing information such as date of
birth, date of admission, date of product sale, etc.
4. BOOLEAN-Binary data types are used to store all sorts of data such as graphic images, multimedia and word
processor documents.
5. MEMO-It is used for large amount of text. It stores upto 65,536 characters.
6. IMAGE-It stores photographs, pictures, signatures etc.
MANIPULATING DATA
1. DDL-DDL is short name of Data Definition Language, which deals with database schemas and descriptions,
of how the data should reside in the database.
DDL COMMANDS:-
CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers).
ALTER – alters the structure of the existing database.
DROP – delete objects from the database.
TRUNCATE – remove all records from a table; also, all spaces allocated for the records are removed.
RENAME – rename an object.
2. DML-DML is short name of Data Manipulation Language which deals with data manipulation, and includes
most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify,
retrieve, delete and update data in database.
SELECT – retrieve data from one or more tables.
INSERT – insert data into a table.
UPDATE – updates existing data within a table.
DELETE – delete all records from a table.
DATA REDUNDANCY
Data redundancy is a condition created within a database or data storage technology in which the same piece
of data is held in two separate places.
OPEN OFFICE BASE
It is a database management system that you can use for all your information management needs.
REFERENTIAL INTEGRITY
It ensures that all the values in the foreign key match the values in the primary key. Referential integrity
ensures that the data in the database remains uniformly consistent, accurate and usable even after that data
it has been changed.
DATA VALIDATION
Data Validation refers to a process of ensuring that only valid data is entered in a table. Eg: If you set the Yes
value for the Entry required property, it will ensure that users enter some value in this field during data entry.
SQL COMMANDS
CREATE COMMAND

Eg:-
CREATE TABLE “STUDENT1”
(
“ROLLNO” NUMERIC(12) PRIMARY KEY,
“NAME” VARCHAR(30),
“FEE” FLOAT(12),
“CITY” VARCHAR(30)
);
INSERTING DATA

INSERT INTO “CUSTOMERS” (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

UPDATING RECORDS
Example:

UPDATE “CUSTOMERS”
SET “ADDRESS” = 'Pune'
WHERE “ID” = 6;

DELETING RECORDS
Syntax:- DELETE FROM table_name
WHERE [condition];
SORTING
It is the technique of storing the records in ascending or descending order of one or more columns. Eg:- A to Z
or Z to A.

Example:-

SELECT “ROLLNO”, “NAME”

FROM CUSTOMERS
ORDER BY “NAME” DESC;
SEARCHING RECORDS

Example:- SELECT “ID”, “NAME”, “SALARY” FROM CUSTOMERS;

QUERY TO GENERATE ALL THE RECORDS FROM THE TABLE

The symbol * is used with SELECT keyword.

Eg: SELECT * FROM table_name;

ALTERING TABLES
Alter command (a DDL command) is used for altering the table structures such as
 To add a column
 To change the data type of any column or to modify its size
 To rename any existing column
 To drop a column from the table

Eg: ALTER TABLE STUDENTS

ADD (Marks INTEGER)

DROPPING TABLES
This command of SQL completely removes a table from the database.
Eg:- DROP TABLE Students
RELATIONSHIPS
It helps to combine data from two different tables. Each relationship consists of fields in two tables with
corresponding data.
1. ONE TO ONE RELATIONSHIP
An entity A is associated with at most one entity in B and an entity in B is associated with atmost one entity in
A.

2. ONE TO MANY RELATIONSHIP

An entity A is associated with any number of entities in B. An entity in B can be associated with at most one
entity in A.

3. MANY TO ONE RELATIONSHIP

An entity A is associated with at most one entity in B and an entity in B can be associated with any number of
entities in A.

4. MANY TO MANY RELATIONSHIP

An entity A is associated with any number of entities in B and an entity in B can be associated with any
number of entities in A.

DIFFERENCE BETWEEN CHAR AND VARCHAR

CHAR VARCHAR
Its full name is CHARACTER Its full name is VARIABLE CHARACTER
It stores values in fixed lengths and are padded with VARCHAR stores values in variable length along with
space characters to match the specified length 1-byte or 2-byte length prefix and are not padded with
any characters
It can hold a maximum of 255 characters. It can hold a maximum of 65,535 characters.
It uses static memory allocation. It uses dynamic memory allocation.