Subject Specific Skills Unit-3

Database Management System

Database:

A database is an organized collection of data. It is considered as a container of information.

In the manual system, you would maintain several files with different bits of information while in the
computerized system you would use database programs such as Microsoft Access, OpenOffice.org Base,
and MySQL, to organize the data as per your business need.

Database Management System :

A database management system is a software package with computer programs that controls the creation,
maintenance, and use of a database. for example Oracle, IBM DB2, Microsoft SQL Server, Microsoft
Access, PostgreSQL, MySQL, FoxPro, and SQLite.

RDBMS:

A relational database is a collective set of multiple data sets organized by tables, records and columns.
Relational database establish a well-defined relationship between database tables. Tables communicate and
share information, which facilitates data searcheability, organization and reporting. A Relational database
use Structured Query Language (SQL), which is a standard user application that provides an easy
programming interface for database interaction

Data can be organized into two types:

1. Flat File : Data is stored in a single table. Usually suitable for less amount of data.
2. Relational : Data is stored in multiple tables and the tables are linked using a common field.
Relational is suitable for medium to large amount of data.

Database Servers :

Database servers are dedicated computers that hold the actual databases and run only the DBMS and
related software.

Advantages of Database :

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 are 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.
Some key features of a database:

1) A database can have one or many tables.

2) Each table in a database contains information about one type of item.

3) Every table has a key field which ensures that there are 100% unique values throughout the database.

Important Terms :

1) Primary Key : A primary key is a unique value that identifies a row in a table. It helps the database to
search for a record.

2) Composite Primary Key : When primary key constraint is applied on one or more columns then it is
known as Composite Primary Key.

3) 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.

DATABASE OBJECTS :

1) Table : A table is a set of data elements (values) that is organized using a model of vertical columns and
horizontal rows.

2) Columns or Fields or Attributes: A column is a set of data values of a particular simple type, one for
each row of the table.

3) Rows or Records or Tuples : A row also called a Record or Tuple represents a single data item in a table.
Every row in the table has the same structure.

4) Queries: Queries helps to define the data structure and also to manipulate the data in the database using
command.

5) Form: A form helps the user to systematically store information in the database.
form enables users to view, enter, and change data directly in database objects such as tables.
6) Report: To present the data in a summarized way and helps to take print of it.

Data Types :

Data types are used to identify which type of data (value) we are going to store in the database. Data types
in OpenOffice base are broadly classified into five categories listed below.

1. Numeric Types
2. Alphanumeric Types
3. Binary Types
4. Date time
5. Other Variable types

Numeric data types:

are used for describing numeric values for the field used in the table of a database. Numeric data types in a
database can be used for storing information such as mobile number, roll number, door number, year of
school admission statistical values, etc.
Alphanumeric Types:

Varchar, char

Binary Types: Binary data types are used for storing data in binary formats.

Binary data types in a database can be using for storing photos, music files, etc.

DATE TIME: Date time data types are used for describing date and time values for the field used in the
table of a database.

Boolean: True and False

Difference between Char and Varchar data type

i) Char- stores fixed length data , Varchar- store variable length data.

ii) char- Length varies from 0-255,Varchar- length varies from 0-65535

iii)char-static memory allocation, Varchar - Dynamic memory allocation

Referential Integrity :
Referential integrity is used to maintain accuracy and consistency of data in a relationship. In Base, data
can be linked between two or more tables with the help of primary key and foreign key.

Referential integrity helps to avoid:

1) Adding records to a related table if there is no associated record available in the primary key table.
2) Changing values in a primary if any dependent records are present in associated table(s).
3) Deleting records from a primary key table if there are any matching related records available in
associated table(s).

Relationship-A relationship refers to an association or connection between two or more tables.

There are three types of relationships which can be created in tables:

1) ONE to ONE Relationship : In this relationship, both the tables must have primary key columns.
2) One to Many Relationship : In this relationship, one of the table must have primary key column.
3) Many to Many Relationship : In this relationship, no table has the primary key column.

Types of SQL(Structured Query Language) Commands

MySQL/openoffice base follows SQL specifications for its commands . These SQL commands can be
categorized as-

Data Definition Language(DDL)

These SQL commands are used to create, alter and delete database objects like table, views, indexetc.
Example : CREATE , ALTER , DROPetc.

Data Manipulation Language(DML)

These commands are used to insert, delete, update and retrieve the stored records from thetable.
Ex. SELECT…., INSERT…, DELETE…, UPDATE….etc.

Creating a Database.
The following command will create School database in MySQL.
mysql> CREATE DATABASESchool;

Opening a database

To open an existing database, following command is used. mysql> USE school;

Getting listings of database and tables

mysql> SHOWDATABASES;
mysql> SHOWTABLES;

Deleting a Database and Table

mysql> DROP DATABASESchool;
mysql> DROP TABLE Student;

Viewing Table Structure

desc tablename

Creating Simple Tables:

CREATE TABLE < TableName>

(<Col name1><datatype>[(size)],….);
Inserting Records:

INSERT INTO <Table Name> VALUES (value1, vale2, …...);