Database Management System
A database is an organized collection of data. You can
visualize it as a container of information.
Eg- MS. Access, MySQL, FoxPro
Data base management system is a package that controls
the creation, maintenance and storing of data. It allows
organizations to conveniently develop database for
various applications.
A DBMS allows different user application programs to
concurrently access the same database.
Data can be organized into two types:
• Flat File: Data is stored in a single table. Usually
suitable for less amount of data.
• 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 are dedicated computers that hold the
actual databases and run only the DBMS and related
software. They are accessed through command line or
graphic user interface tools referred to as Frontends. Such
type of data access is referred to as a client-server model.
Advantages of database-
Reduce data duplication- there is only a single
database and any change in it is reflected
immediately.
Sharing of data- the users of database can share
the data among themselves. The data can only be
shared based with the authorized users.
Data integrity- it means that the data is accurate
and consistent in database.
Data security- Only authorised users should be
allowed to access the
this is taken care of by the DBMS.
Data consistency- Data consistency is ensured in a
database because there is no data redundancy.
database and their identity should be authenticated
using a username and password.
Privacy- The privacy rule in a database states that
only the authorized users can access a database.
Backup and recovery- The users don't need to
backup data periodically because
Item- each
Field-
Record-
Value-
Features of database-
A database has one or many tables
Each table contains information about one type of item
Database record uniqueness in ever table, so it becomes
easy to distinguish between duplicate values
Every database has one or more field designated as key
Features (notebook)-
Simple and complex sorting can be performed on data
Powerful queries to retreat data depending on the given
conditions
Effective reports can be created using report wizard
Data filtration can be done easily
Front end- the interface through which, the database is
accessed
Back end- the database available on database server
Primary key- it is a unique identifier in a table. It identifies row
in a table (makes it faster to search a record)
Composite primary key- when primary key is applied on one
or more columns
Foreign key- it identifies a column or set of columns in one
table that refers to a column of other table
Data in RDBMS is organized in the forms of tables
Database objects:
Tables- A table is a set of data elements that is
organized using a model of vertical columns and
horizontal rows
Column/fields/attributes- it is a set of data, one for
each row of the table
Row/records/tuples- each row in table represents a
set of related data and every row in the table ha
Creating a table-
Design view
Wizard
Data types-
It is used to identify which type of data we are going to
store in data base
Numeric types
Alphanumeric types
Binary type
Date time
Other variable types
Numeric type- 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, true or
false statements, statistical values, etc.
Alphanumeric type- saves alphabet and numeric both.
Eg- varchar
Binary type- 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.
Other data types- stores serialization java objects
Tables-
In Base, data is stored in tables which can be inserted,
modified and removed using appropriate options.
The following operations can be performed in a table-
Inserting data in table
Editing records in table
Deleting records from tables
Field properties
It can be changed in the design view
Properties -
Auto Value – if set to yes then field will get the auto
numeric values
Length – By default length of the field is 10 but the size of
the field can be set to maximum length
Default Value – A default value can be set for a field if
user don’t provide any value while entering the values in
the table.
Format example – This property helps to set the format
of the data entered in the field such as 91-222-333
Entry Required – if set to yes then it will be must to
insert the value in the field which means that field cannot
be left blank
Sorting data-
Sorting means to arrange the data in either ascending
order or descending order
Referential integrity-
Used to maintain accuracy and consistency of data in a
relationship
Data can be linked between two or more tables with the
help pf primary key and foreign key
Referential integrity helps to avoid:
Adding records to a related table if there is no associated
record available in the primary key table.
Changing values in a primary if any dependent records are
present in associated table(s).
Deleting records from a primary key table if there are any
matching related records available in associated table(s).
-A relationship between tables means associating or connecting
two or more tables. When you relate two tables you don’t need
to enter the same data in two separate tables.
Relationship between tables help to-
Save time
Reduce data-entry errors
Summarize data from related tables
There are 3 types of relationships-
One to One- In this relationship, both the tables must
have primary key columns
One to many- In this relationship, one of the table must
have primary key column
Many to many- In this relationship, no table has the
primary key column
-The relationships applied on data can be removed by using
delete option
RETREIVE DATA USING QUERY
We use query database to extract some information
Query is to collect specific information from a pool of data.
It helps us to join information from different tables and
filter that information
Filtering means that query uses some criterion you
provide to hide some information and present only what
you want to see
Ways to create query-
-wizard
-design view
Creation of query using design view-
SQL- structural query language
Query language are computer languages used to make
queries in the database
Queries are demands that are used to define the data
structure and also manipulate data in the database
SQL helps to work at backend
1. Create command-
2. Insert statement-
-used to insert data or record in a table
Eg- insert into emp(emp no., name, salary, city
Value (01, “Aarti”,45000, “Delhi”)
3. Select statement-
-It is use to select or fetch a statement from table
-it is the most commonly used data manipulation language
(DML) command
-To retrieve all the columns in a table the syntax is:
SELECT * FROM <Table Name>
4. Clauses-
-the select statement has optional clauses:
WHERE CLAUSE- specifies which row to retrieve
ORDER BY- specifies an order in which to return
the rows
Grouping data-
To display the records containing the same type of values
“WHERE” clause can be used with the Select SQL
Command.
Eg- select * from SDetails where Color=’Blue’;
Order by clause
Used to arrange data in ascending order
select * from SDetails order by “Rollno” DESC;
Update
Set Salary = 55000
Where name = “Aditi”
UPDATE STATEMENT
update statement is used to modify records in a database
the syntax of update statement is-
1. UPDATE <table name>
2. SET <column name> = value
3. WHERE <condition>
FORMS-
A form provides the user a systematic way of storing
information into the database
REPORT-
Report helps to organize data in a summarized manner. It
is used to generate the overall work outcome in a clear
format.
