Unit V - DBMS

DBMS
Introduction
What Is a Database?
• Database
– A collection of related data stored in a manner that
enables information to be retrieved as needed
• Database Management System (DBMS)
– Used to create, maintain, and access databases
– Example:Microsoft Access, OpenOffice Base, Corel
Paradox, Oracle Database, etc.
2
What Is a Database?
• A database typically consists of:

– Tables
• Collection of related records
– Fields (columns)
• Single category of data to be stored in a database
(name, telephone number, etc.)
– Records (rows)
• Collection of related fields in a database (all the fields
for one customer, for example)
3
What Is a Database?
• A Simple Relational Database Example
4
What is a Database?
– Primary Key
• Field that uniquely identifies the records in a table
• Field in a table that is used to relate that table to other
tables
5
What Is a Database?
Individuals Involved with a Database Management System

 Database Designers
• Design the database
 Database Developers
• Create the database
 Database Programmers
• Write the programs needed to access the database or tie the database to
other programs
 Database Administrators
• Responsible for managing the databases within an organization
 Users
• Individuals who enter data, update data, and retrieve information from
the database
6
Advantages and Disadvantages of the DBMS
– Advantages
• Low level of redundancy
– Faster response time
– Lower storage
requirements
– Easier to secure
– Increased data
accuracy
– Disadvantages
• Increased vulnerability
(backup is essential) 7
Data Hierarchy
– Fields/columns
• Hold single pieces of data
– Records/rows
• Groups of related fields
– Tables
• Collection of related records
– Database
• Contains a group of related tables
8
Database Models
• The Hierarchical and Network Database Models

– Hierarchical Databases
• Organizes data in a tree structure
• Typically a one-to-many relationship between data
entities
– Network Databases
• Allow both one-to-many and many-to-many
relationships between data elements
– Most databases today are neither hierarchical
or network
models
9
The Relational Database Model
(RDBMS)
• The Relational Database Model (RDBMS)

– It organizes the data into tables where each row holds a
record and is called a tuple.
– And each column represents an attribute for which each
record usually holds a value.
– Most widely used database model today.
– There are many RDBMS like MYSQL, Postgres, SQL Server,
etc. which use SQL for accessing the database
10
The Relational Database Model
(RDBMS)
11
What is SQL?
• Query -A request to see information from a database

that matches a specific criteria.
• SQL stands for Structured Query Language.
• It is a querying language designed for accessing and

manipulating information from RDBMS.
• SQL lets us write queries or sets of instructions to
either create a new table, manipulate data or query on
the stored data.
12
• SQLite is a relational database management system based on SQL.
• It is serverless, lightweight, and requires zero-configuration.
• Also, it reads and writes directly to a disk file that can be easily copied.
• It is platform-independent.
• SQLite stores data in variable-length records which requires less

memory and makes it run faster.
• It is designed for improved performance, reduced cost, and optimized

for concurrency.
• The sqlite3 module facilitates the use of SQLite databases with

Python.
Data Types
Storage Meaning
Class
NULL NULL values mean missing information or unknown.
INTEGER Integer values are whole numbers (either positive or negative). An
integer can have variable sizes such as 1, 2,3, 4, or 8 bytes.
REAL Real values are real numbers with decimal values that use 8-byte
floats.
TEXT TEXT is used to store character data. The maximum length of TEXT
is unlimited. SQLite supports various character encodings.
BLOB BLOB stands for a binary large object that can store any kind of
data. The maximum size of BLOB is, theoretically, unlimited.
Connecting to an SQLite
Database
The first step to working with your database is to create a connection with it.
We can do this by using the connect() method that returns a Connection object. It

accepts a path to the existing database.
If no database exists, it will create a new database on the given path.
The next step is to generate a Cursor object using the cursor() method which allows

you to execute queries against a database:
import sqlite3

# create new database
conn = sqlite3.connect('table.db')

# create Cursor to execute queries
cur = conn.cursor()

print('Database created.')
Commit() & Close()
You are now ready to execute queries against the database and manipulate the data.
But after we have done that, it is very important to do two things:
1.Commit/save the operations that we performed on the database using

the commit() method. If we don’t commit our queries, then any changes we made to
the database will not be saved automatically
2. Close the connection to the database to prevent the SQLite database from getting
locked. When an SQLite database is locked, it will not be accessible by other users
and will give an error.
# save changes
conn.commit()
print('Changes saved.’)
# close database connection

conn.close()
print('Connection closed.')
CREATE TABLE
A table in SQL is created using the CREATE TABLE command.

Here a table called Customer is created with the following attributes:
 User_ID – Id to identify individual customers. This is an Integer data

type, Primary key and is defined as Not Null
The Primary key is an attribute or set of attributes that can determine individual
records in a table.
Defining an attribute Not Null will make sure there is a value given to the attribute
(otherwise it will give an error).
 Product_ID – Id to identify the product that the customer bought. Also defined as
Not Null
 Name – Name of a customer of Text type
 Gender – Gender of a customer of Integer type
 Age – Age of the customer of Integer type
Note:SQL keywords are case-insensitive so you can write the commands in

UPPERCASE IF YOU WANT!
execute()
 Any SQL command can be executed

using the execute() method of # create table in database
cur.execute
the Cursor object.
('''CREATE TABLE CUSTOMER
(
 Write the query inside quotes and User_ID INTEGER PRIMARY KEY NOT
include a ; which is a requirement in NULL,
some databases but not in SQLite. Product_ID INTEGER NOT NULL,
Name TEXT NOT NULL,

 So, using the execute() method, we
Gender TEXT NOT NULL,
can create our table as shown here: AGE INTEGER NOT NULL,
#connect to existing database CITY TEXT); '‘’)
conn = sqlite3.connect(‘table.db’) # commit and save changes to database
cur = conn.cursor() conn.commit()
#
Inserting values in a SQL table
INSERT INTO
 A database table is of no use without values. So, we can use

the INSERT INTO SQL command to add values to the table.
The syntax for the command is as follows:
INSERT INTO table_name (column1, column2, column3 …)
VALUES (value1, value2, value3 …);
 But if we are adding values for all the columns in the table, we can just
simplify things and get rid of the column names in the SQL statement:
INSERT INTO table_name
VALUES (value1, value2, value3 …);
 We can execute SQL INSERT INTO statements using

the execute() method.
cur.execute('''Insert Into Customer
('User_ID','Product_ID','Name','Gender','AGE','CITY') Values (1006, 3, 'Princess
Diana', 'Female', 28, 'Amazons');''')
Inserting values in a SQL table
executescript() & executemany()
 If we want to write multiple Insert commands in a single go, we could use

the executescript() method instead:
# Execute multiple commands at once
cur.executescript('''Insert Into CUSTOMER Values (1005, 3, 'Clark Kent', 'Male', 36,
'Metropolis');
Insert Into CUSTOMER Values (1003, 4, 'Bruce Wayne', 'Male', 39,
'Gotham City'); '‘’)
 Or just simply use the executemany() method without having to
repeatedly write the Insert Into command every time!
executemany() actually executes an SQL command using an iterator to

yield the values:
# Insert multiple values into table at once
customers = [(1004, 2, 'John Wick', 'Male', 32, 'New York’),
(1001, 1, 'Tony Stark', 'Male', 35, 'New York’),
(1002, 3, 'Gordon Ramsey', 'Male', 38, 'London’)]
cur.executemany('Insert Into CUSTOMER Values (?,?,?,?,?,?)', customers)
Fetching Records from a SQL table
fetchone()
 For fetching values from the database, we use the SELECT command

and the attribute values we want to retrieve:
SELECT column1, column2 … FROM table_name;
 If you instead wanted to fetch values for all the attributes in the table, use
the * character instead of the column names:
SELECT * FROM table_name;
 To fetch only a single record from the database, we can use

the fetchone () method:
# Fetch all rows of query result
cur.execute('SELECT * FROM CUSTOMER;’).fetchone()
 To fetch multiple rows, you can execute a SELECT statement and iterate
over it directly using only a single call on the Cursor object:
Fetching Records from a SQL table
fetchall()
 To fetch multiple rows, execute a

SELECT statement and iterate over
directly using only a single call on the
Cursor object:
# iterate over the rows

for row in cur.execute('SELECT Name  But a better way of retrieving multiple
FROM CUSTOMER;'): records would be to use
print(row) the fetchall() method which returns all
the records in a list format:
#Fetch all rows of query result which returns

a list
cur.execute('SELECT * FROM
CUSTOMER;’).fetchall()
Update, Delete & Drop Table
Commands
UPDATE SQL command.
 The UPDATE command is used to modify existing records in a table. However,
always make you sure you provide which records need to be updated in the
WHERE clause otherwise all the records will be updated!
UPDATE table_name
SET column1 = value1, column2 = value2 …
WHERE condition;
Delete statement
 To delete the records from the database, we use the DELETE statement. However,
make sure to use the WHERE clause otherwise all the records will be deleted from
the table!
DELETE FROM table_name
WHERE condition;
Drop Table statement
 Finally, if we had to drop an entire table from the database and not just its records,
we use the DROP TABLE statement. All the records in the table along with the
table structure will be lost after this!
Drop table table_name;

Unit V - DBMS

Uploaded by

Copyright:

Available Formats

Unit V - DBMS

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit V - DBMS

Uploaded by

Copyright:

Available Formats

DBMS

• A database typically consists of:

• A Simple Relational Database Example

Individuals Involved with a Database Management System

• The Hierarchical and Network Database Models

• The Relational Database Model (RDBMS)

• Query -A request to see information from a database

• SQL stands for Structured Query Language.

• It is a querying language designed for accessing and

• It is serverless, lightweight, and requires zero-configuration.

• SQLite stores data in variable-length records which requires less

• It is designed for improved performance, reduced cost, and optimized

• The sqlite3 module facilitates the use of SQLite databases with

We can do this by using the connect() method that returns a Connection object. It

If no database exists, it will create a new database on the given path.

The next step is to generate a Cursor object using the cursor() method which allows

1.Commit/save the operations that we performed on the database using

# close database connection

A table in SQL is created using the CREATE TABLE command.

 User_ID – Id to identify individual customers. This is an Integer data

Note:SQL keywords are case-insensitive so you can write the commands in

 Any SQL command can be executed

Name TEXT NOT NULL,

 A database table is of no use without values. So, we can use

 We can execute SQL INSERT INTO statements using

 If we want to write multiple Insert commands in a single go, we could use

executemany() actually executes an SQL command using an iterator to

 For fetching values from the database, we use the SELECT command

 To fetch only a single record from the database, we can use

 To fetch multiple rows, execute a

# iterate over the rows

#Fetch all rows of query result which returns

You might also like