Database
Management
Systems (IBMS)
3 What is Database? 3
A database is an organized collection of data. You can visualize it as a
container of information.
A database is a structured grouping of data that has been arranged and is
often kept electronically in a computer system. A database management
system used to manage a database (DBMS).
A database is the collection of the information
What is a Database Management System?
restore
Data is stored, retrieved, and analyzed using software called database
management systems (DBMS). Users can create, read, update, and
remove data in databases using a Database Management System, which
acts as an interface between them and the databases.
⑲us
For example – Oracle, IBM DB2, Microsoft SQL Server, Microsoft
Access, PostgreSQL, MySQL, FoxPro, and SQLite.
Data can be organized into two types:
• Flat File: Data is stored in a single table. Usually suitable for
less amount of data. Text files without any markup often
make up flat files.
• 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
What is Database Server?
Database servers are specialised machines that exclusively operate the
DBMS and associated applications. They contain the actual databases.
Database servers are known as Back-ends, while Frontends are
programmes with a command line or graphical user interface that are
used to access databases that are available on them. A client-server
model is the name given to this kind of data access.
Advantages of Database
->
entral
Reduces Data Redundancy
Multiple files are contained in database management systems and must
be stored in various places inside a single system or even across multiple
systems. This resulted in data redundancy as there were occasionally
multiple copies of the same file.
(Note – Data redundancy happens when the same piece of information
appears more than once, but data inconsistency happens when the same
information appears in various formats across various tables.)
Sharing of Data
A database allows its users to exchange data among themselves. The
data can only be shared with users who have received the appropriate
degrees of authorization because there are different levels of access to
the data.
Data Integrity
Data accuracy and consistency in the database are terms used to describe
data integrity. A DBMS contains many databases, therefore data
integrity is important. Data that is accessible to many users is present in
each of these databases. In order to ensure that the data is accurate and
consistent throughout all databases and for all users.
Data Security
A database’s concept of data security is important. The database should
only be accessible to authorised users, whose identities must be verified
using a username and password.
Privacy
The privacy rule in a database states that only the authorized users can
access a database according to its privacy constraints. To secure data
levels are set in the database and a user can only view the data which is
allowed to be seen. For example – In social networking sites, access
constraints are different for different accounts a user may want to access.
Backup and Recovery
Backup refers to storing a copy of original data separately.
Recovery refers to restoring the lost data in case of failure.
opallows
same.
the
According to its privacy limitations, a database’s privacy rule states that
only authorised users are permitted access. A user can only view the data
that is permitted to be seen, and levels are configured in the database to
secure data. For instance, on social networking sites, different accounts
that a user may want to access have varying access restrictions.
Data Consistency
Database consistency is defined by a set of values that all data points within the
database system must align to in order to be properly read and accepted. Should any
&
data that does not meet the preconditioned values enter the database, it will result in
Data consistency
consistency errorsstates
for thethat different versions of the same data shouldn’t
dataset.
match. All data must be identical for every user viewing the database
and must be shown consistently throughout. Additionally, there are no
data inconsistencies because all users immediately see the effects of any
database changes.
Features of Database
There are some key features of a database:
1. One or more tables can be added in the database.
2. Decreased storage costs and space requirements
3. Userscan use query languages in a database to insert, modify,
search, and delete the data easily from the database.
4. Multiple users can access the data from the database .
5. All
of the data in a database can be accessed by several users,
but only one person can update a particular piece of data at
once.
6. Thereis no chance of data loss because Database Management
System stores all data files permanently.
7. One or more fields should be designated as keys in every
database table. For the purpose of identifying records with
possibly similar names or addresses, you can give this key a
special value.
8. Uniquenessaids in preventing accidental record duplication
brought on by human or technological error.
Primary Key, Composite Primary Key and Foreign
Key in a Database
In the RDBMS data can be integrated using keys. These are Primary
Key, Composite Primary Key, and Foreign Key, Key are used to make
the relationship between the tables.
1. Primary Key – This unique field is called the Primary Key
(PK). A primary key is a column in a table or a group of
columns that aids in uniquely identifying each record in that
table. In a table, there can only be one primary Key.
jiripei
2. Composite Primary Key – Composite primary key is the
term used when the primary key constraint is applied to one
or more columns.
3. Foreign Key – To create connections between two tables,
foreign keys are used. Each value in a column or collection
of columns containing a foreign key must match the Primary
Key of the referential table. Data and referential integrity are
preserved with the use of foreign keys.
What is RDBMS?
A database management system that is based on the relational model is
called an RDBMS (Relation Database Management System). Tables are
used to organise data in relational databases. A relational database
management system (RDBMS) is used to store, manage, query, and
retrieve data.
Database Objects
Tables
Data is arranged into rows and columns in a table, which is a type of
data structure. It can be applied to both the storage and presentation of
structured data.
:I
Columns or Fields or Attributes
Data is arranged vertically from top to bottom in columns. Each row of
the table has one column, which is a collection of data values of a
specific basic type. The structure by which the rows are put together is
provided by the columns.
Rows
N
or Records or Tuples
..
A row, also known as a Record or Tuple, in a table represents a single
data item. A database table can be represented graphically as being made
up of rows and columns, or fields. Every row in a table has the same
structure and represents a group of connected data.
L How to create a database using Open Office
Step 1 : Click on Start > Programs > OpenOffice > OpenOffice Base
Step 2 : Click on Create a new database
Step 3 : In the Database Wizard, Click on Finish
Step 4 : Specify a the Name of database and click on Save
2How to create a table in Database
The database’s tables are used to store data. In the database, In
OpenOffice Base the tables can be created in two different ways.
1. Create Table in Design View
2. Use Wizard to Create Table
Create Table in Design View
2
Step 1 : Click on Create Table in Design View
Step 2 : Select Field name and suitable datatype
Step 3 : Set the Primary Key
Step 4 : Change the table name
Step 5 : Save the table
2 Use Wizard to Create Table
Step 1 : Click on Table > Use Wizard to Create table
Step 2 : Click the Select Fields > Choose Category > Select the table >
Click on Next Button
Step 3 : Select data types from the given field
Step 4 : Set the Primary Key
Step 5 : Rename the table and Click on Finish
Data Types
The type of data (value) that will be stored in the database is defined by
its datatype. Important to know the different types of data helps to
ensure that each property’s value is as expected and that data is collected
in the correct format.
Data types in OpenOffice base are broadly classified into five categories
listed below.
• Numeric Types
• Alphanumeric Types
• Binary Types
• Date time
• Other Variable types
Numeric Types
Data that is presented as numbers rather than in any language or
descriptive form is referred to as numerical datatype. Numerical data,
also known as quantitative data, is gathered in number form and differs
from all other types of number data because it can be calculated
mathematically and statistically.
Alphanumeric Types
Data that has both letters and numbers is referred to as alphanumeric
type.
Binary Types
For storing data in binary formats, binary data types are utilised. In a
database, binary data types can be used to store things like music and
image files. The binary data type can generally be used to store files in
any format.
Date Time
When specifying date and time values for a column used in a database
table, date time data types are used. Information like dates of birth,
admissions, product sales, and other dates can be stored in databases
using date and time data types.
Other Data Types
Session 3 : Perform Operations on Table
-
In Base, data is kept in tables that may be added to, changed, or deleted
by using the proper options.
In this relationship, no table has the primary key column. It signifies that
all the columns of primary key table are associated with all the columns
-
of associated table.
Remove the Relationships
With the use of the Delete option, the relationships that have been
applied to the tables can also be deleted.
When you right-click a relationship thread, the Delete option will
appear.
she dies
Session 4 : Retrieve Data using Query
In order to describe the data structure and to modify the data in the
database, queries are used as instructions. A query enables the joining
and filtering of data from various tables.
Database Languages having two type:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
table
DDL Statements: defines structure of
the
• Create: Using this statement, a database or set of tables can be
created.
•
structure.
update
Alternate: This statement is used to change the table’s
• Drop: This statement is used to remove database objects from
the system.
I related in the
data
to table
DML statements:
• SELECT: The statement “SELECT” is used to get data from
the database.
• INSERT: The statement “INSERT” is used to add a new record
to the database.
• DELETE: The database can be cleaned out by using the
statement DELETE.
• UPDATE: This statement is used to modify the database’s
information.
Database Query –
Query is a computer languages. In order to describe the data structure
and to modify the data in the database, queries are used as instructions.
Query can extract particular data from a database. We can filter and join
data from various tables with the help of a query. By using the criteria
you supply query will filter the data.
Select Statement
A select query is a language in a database that displays data in Datasheet
view. Data from tables is displayed by a query rather than being stored
by it. A query may display data from one or more tables, from other
queries, or from both of these sources simultaneously.
The SELECT statement has many optional clauses:
• WHERE specifies which rows to retrieve.
• ORDER BY specifies an order in which to return the rows.
Syntax of Select Statement is –
SELECT * FROM <TABLENAME>;
Query related to Simple Select Statement –
Table Name – product
(
Product_No Product_Name Price Quantity -
25 Soap 40 80 -
2
i
31 Powder 80 30 ⑤
45 Shampoo 250 25 -
52 Soap Box 120 100 ⑨ 15
-
sat snows
Question – Write a Query to display all record from the table;
- -
↓
product
Select * from product;
-
=
↑
Output –
Product_No Product_Name Price Quantity
-
25 Soap 40 80
~
31 Powder 80 30
-
45 Shampoo 250 25
~
52 Soap Box 120 ~ 100
Question – Write a Query to display product name from the table;
11-.-
Select Product_Name from product;
Output –
Product_Name
Soap
Powder
Shampoo
Soap Box
Question – Write a Query to display Product_Name and Price from the
--
table;
-
Select Product_Name, Price from product;
... -
Output –
Product_Name Price
Soap 40
/
Powder 80
Shampoo 300
Soap Box 120
Query related to Select Statement with Mathematical function
– -
Table Name – product /
-1
Product_No Product_Name Price Quantity
1
25 Soap 40 80 -
31 Powder 80 30 ⑤
45 Shampoo 250 25 -
52 Soap Box 120 100 ⑱
-
-
->
Question – Write a Query to find the total no of quantity available in
a - -
table;
-
↓
Select sum(quantity) from product;
vv ~
Output – 235
->
Question – Display the total amount of each item. The amount must be
-
calculated as the price multiplied by quantity for each item.
1
.0
Select Product_No, Product_Name, Price * Quantity from product;
v =
--
Output –
I'l
Product_No Product_Name Price*Quantity
25 Soap 3200
31 Powder 2400
45 Shampoo 6250
52 Soap Box 12000
Sdectauglprices
for
rent
⑧·
Question – Write a query to find the average price of the total product;
price
- from product;
Select avg(Quantity)
Output – 58.75
-
Query related to Select Statement with Where clause –
Table Name – product
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Question – Write a Query to display the product whose price is less than
90
Select * from product where price < 90;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
Question – Write a Query to find the total amount of the Shampoo
product;
Select Price*Quantity from product where Product_Name = ‘Shampoo’;
Output – 6250
Or
Select Product_No, Product_Name, Price*Quantity from product where Product_Name =
'Shampoo';
Output –
Product_No Product_Nam Price*Quantity
45 Shampoo 6250
Question – Write a Query to display the data whose quantity is equal to
80.
Select * from product where quantity = 80;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
Question – Write a Query to display a list of Products whose Price
between 40 to 120.
Select * from product where Price >= 40 and Price <= 120;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
52 Soap Box 120 100
Query related to Select Statement with Order by –
Table Name – product
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Question – Write a Query to display the list of Product_Name in
alphabetical order.
Select * from product order by Product_Name ASC;
Or
Select * from product order by Product_Name;
Output –
Product_No Product_Nam Price Quantity
31 Powder 80 30
45 Shampoo 250 25
25 Soap 40 80
52 Soap Box 120 100
Question – Write a Query to display the list of Price in ascending order.
Select * from product order by Price ASC;
Or
Select * from product order by Price;
Output –
Product_No Product_Nam Price Quantity
25 Soap 40 80
31 Powder 80 30
52 Soap Box 120 100
45 Shampoo 250 25
Question – Write a Query to display the list of Price in descending
order.
Select * from product order by Price DESC;
Output –
Product_No Product_Nam Price Quantity
45 Shampoo 250 25
52 Soap Box 120 100
31 Powder 80 30
25 Soap 40 80
UPDATE statement
To edit or update already-existing records in a table, use the UPDATE
statement. Using the WHERE clause, you can either define a specific
subset of entries to edit or use it to update everything at once.
Syntax of Update Statement –
UPDATE <table name> SET = value [, column_name = value ...] [WHERE ];
Table Name – product
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Question – Write a Query to update the price of Shampoo in the product
table.
Update product Set Price = 300 where Price = 250;
Output –
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 300 25
52 Soap Box 120 100
Question – Write a Query to update the Quantity of Powder in the
product table.
Update product Set Quantity = 50 where Product_Name = ‘Powder’;
Output –
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 50
45 Shampoo 250 25
52 Soap Box 120 100
Create Table
To create a new table in the database you can use Create Table
Command.
Syntax of Create Table –
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype);
Question – Write a Query to create the following table in the database;
Table Name – product
Field DataType
Product_No Integer
Product_Name Varchar(20)
Price Integer
Quantity Integer
Create table product ( Product_No Int, Product_Name Varchar(20), Price Int, Quantity
Int);
Output –
Product_No Product_Name Price Quantity
Insert Table
Insert statement is primarily used to add a single or more rows to the
target table.
Syntax of Insert Table –
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3,
...);
Or
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Table Name – product
Question – Write a Query to add a new row with the following details
(72, “Hair Conditioner‟, 350, 60)
Insert into product (Product_No, Product_Name, Price, Quantity) values(72, “Hair
Conditioner‟, 350, 60);
Or
Insert into product values(72, “Hair Conditioner‟, 350, 60);
Output –
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 300 25
52 Soap Box 120 100
72 Hair Conditioner 350 60
X Session 5 : Create Forms and Reports using Wizard
Forms
The Form allows you greater control over your outcomes. The form
allows you to customize various parts of the form’s design and then
generates a form based on your instructions.
Steps To Create Form Using Wizard
Step 1 : Click Use Wizard to Create Form
Step 2 : Select the selective Fields using Arrow button
Step 3 : Click Next
Step 4 : Add Subform if you need to insert
Step 5 : Click Next
Step 6 : Arrange selected field in a form
Step 7 : Click Next
Step 8 : Click Finish
