1 of 39

དཔལ་ལྡན་འབྲུག་གཞུང་། ཤེས་རིག་དང་རིག་རྩལ་གོང་འཕེལ་ལྷན་ཁག།

Department of School Education

Ministry of Education & Skills Development

Online Training for ICT Teachers

17 February 2023

Classes XI & XII ICT Curriculum

SQLite SESSION I

2 of 39

SESSION I

3 of 39

Session Outline

ICT Curriculum

Introduction to Database

Comparing Spreadsheet and Database

Creating Database using SQLite

Stock Register Database

1

Student Details Database

SQLite

2

3

4

5

6

4 of 39

ICT Curriculum

5 of 39

Competency

Objectives

Use a database management system in an application for easy access, and secure data storage and management.

Develop a CRUD application using Tkinter as frontend, Python as backend and SQLite for the database

Insert, delete, update, and Select data from the SQLite database using SQL.

Develop a database application using the SQLite module.

Create a SQLite database.

Explain the functions of the database system.

6 of 39

Content Scope

Database Management System

Database Application

Definition of Database

Importance of Database

Terminology in Database

CRUD Operations

Database

Tuples

Attributes

Tables

Create

Read

Update

Delete

Create table

Read Record

Update Record

Delete Record

Drop Table

Select Record

Insert Record

Import sqlite3 library

connect()

execute()

commit()

Close()

SQLite Browser

SQLite Database

SQLite Query

Connect to Database

SQLite Database with Tkinter

7 of 39

Career Opportunities

Data Administrator

Data Analyst

Business Intelligence Analyst

Database Developer

Data Scientist

Software Developer

Cloud Database Engineer

Database Administrator: Database administrators (DBAs) are responsible for the design, implementation, maintenance, and security of an organization's databases. They ensure that the databases are running efficiently and effectively, and they troubleshoot any issues that arise.
Data Analyst: Data analysts use databases to extract, analyze, and interpret data, helping organizations make data-driven decisions.
Business Intelligence Analyst: Business intelligence (BI) analysts use databases to gather, store, and analyze data for the purpose of identifying and understanding business trends and patterns.
Data Scientist: Data scientists use databases to gather and analyze large sets of data, using statistical techniques and machine learning algorithms to extract insights and make predictions.
Database Developer: Database developers are responsible for designing, developing, and maintaining databases. They also write SQL scripts and optimize databases for performance.
Software Developer: Many software applications rely on databases to store and retrieve data. As a software developer, learning database management can be valuable in creating and maintaining such applications.
Cloud Database Engineer: With the increasing adoption of cloud computing, there is also a demand for engineers who can design, implement and manage databases in cloud environments such as AWS, Azure, and GCP.

8 of 39

Introduction to Database

9 of 39

A database is an organized collection of data, which is stored on a computer system.

1

It is used to store, manage, and manipulate data in a structured format.

2

They are an essential part of any organization’s IT infrastructure.

3

Uses SQL(Structured Query Language) for managing and manipulating databases.

4

Examples of Database Management System (DBMS): MySQL, MongoDB, SQLite, MS-Access, Oracle

5

Database

10 of 39

Features

Data Reliability

1

Data Security

2

Data Integrity

3

Data Manipulation

4

Collaboration among users

5

Data Redundancy

6

Features

Data reliability: Ability of a database to accurately and consistently store, retrieve, and manage data. This includes ensuring data integrity, consistency, and availability, as well as protecting against data loss or corruption.
Data security: Protection of sensitive or confidential information from unauthorized access, use, disclosure, disruption, modification, or destruction. This includes protecting data both in storage and in transit.
Data integrity: Accuracy and consistency of data stored in a database. It ensures that the data is complete, correct, and consistent, and that it can be trusted to be accurate and reliable.
Data manipulation: Process of changing, adding, or removing data in a database. This can include tasks such as inserting new records, updating existing records, deleting records, and querying data.
Collaboration of users: Ability for multiple users to access and manipulate data simultaneously, without interfering with each other's work.
Data redundancy: Storage of the same data multiple times in a database. This can occur when multiple copies of the same data are stored in different tables or when the same data is stored in multiple locations within the same table. Data redundancy can lead to several issues. To avoid data redundancy, database designers use techniques such as normalization and de-normalization. Normalization is the process of organizing data in a way that eliminates data redundancy, while de-normalization is the process of adding redundancy back into the data in order to improve performance.

11 of 39

SQLite

It is an embedded SQL database engine and it is not a standalone application, but rather a library that software developers embed in their applications.

3

It is a self-contained, serverless, zero-configuration, and transactional SQL database engine.

2

Software library that provides a database management system.

1

12 of 39

Comparing Spreadsheet and Database

13 of 39

Database and Spreadsheet

Spreadsheet

SQLite

Discuss how are these two similar?

https://padlet.com/gsrgurung/21dw9odbjqfhpmqe

Discuss the similarities between spreadsheet and SQLite
Relate the concepts learned in spreadsheet like creating a table, rows, columns, data types to SQLite
A spreadsheet is a tool for organizing and manipulating data in a tabular format, with rows and columns that can be used to input, calculate, and analyze data. Spreadsheets are widely used for tasks such as budgeting, accounting, and data analysis, and are often used to create charts and graphs to visualize data. They are easy to use and widely available, and can be opened and edited with a variety of software programs, such as Microsoft Excel, Google Sheets, and Apple Numbers.
A DBMS, on the other hand, is a software system for creating, maintaining, and interacting with databases. Databases are used to store and organize large amounts of data in a structured way, and are used to support a wide range of applications and systems, such as e-commerce websites, customer relationship management systems, and data analytics platforms. DBMSs provide a variety of features and capabilities, such as data security, data integrity, and the ability to handle large amounts of data, and are typically used for more complex data management tasks.

14 of 39

Store large amounts of data

1

Search and retrieve specific data

2

Sort and filter data

3

Perform calculations and analysis on data

4

Use tables to organize data.

5

Similarities

15 of 39

Differences

Database

Spreadsheet

store, organize, and retrieve large amounts of data.
used for complex data management and organization. (Trillion rows of data)
Uses specific query languages like SQL to interact with the data in a database
Designed for use by teams or organizations
used for tasks such as storing customer information and managing online stores.

organize, store, and manipulate data in a tabular format
used for simple data manipulation and analysis (Million rows of data)
Relies on simple formulas and functions
Designed for use by individuals
used for tasks such as financial analysis and budgeting

16 of 39

Creating Database using SQLite

17 of 39

First database in SQLite

Output

Sample Code

Variable_name = sqlite3.connect(‘DatabaseFileName.db’)

Import the sqlite3 module

18 of 39

Stock Register Database

19 of 39

Stock Register Database

Write a python program to create a table named Stocks in the StockRegister.db with the following fields:

StockNo
StockName
Quantity
PricePerItem

Add 4 records in the table Stocks and view all the records from the table.

20 of 39

Sample Code

Output

Data types in SQLite

NULL: For storing NULL values
INTEGER: For storing integers
REAL: For storing floating-point values
TEXT: For storing text strings
BLOB: For storing binary data

“CREATE TABLE Stocks (StockNo INTEGER, StockName TEXT, QUANTITY INTEGER, PricePerItem INTEGER)”

The "CREATE TABLE" statement is used to create a new table in the database. The name of the table is "Stocks" and it has four columns: "StockNo", "StockName", "Quantity", and "PricePerItem".

The syntax for each column is "column_name data_type" where "column_name" is the name of the column, and "data_type" is the data type of the column, in this case:

“INSERT INTO Stocks (StockNo, StockName, Quantity) VALUES (?,?,?,?)”

The keyword "INSERT INTO" is used to insert data into a table and create it if it doesn't exist. The name of the table is "Stocks" and the columns of the table are "StockNo", "StockName", and "Quantity". The "VALUES" clause is used to specify the values to be inserted into the table. The question marks (?) are placeholders for the actual values.

“SELECT * FROM Stocks”

The keyword "SELECT" is used to indicate that we want to retrieve data from the database. The "*" symbol is used to select all the columns from the table. The keyword "FROM" is used to specify the table from which we want to retrieve the data, in this case, the table name is "Stocks".

It's important to note that if the table doesn't exist in the database, running this query will throw an error.

cursor():It creates a cursor object that can be used to execute SQL statements on the database. The cursor object is used to execute SQL commands and retrieve the results of the query
execute(): It is a method of the cursor object and can be used to execute various types of SQL commands. It takes a single parameter, which is the SQL statement to be executed.
executemany(): It is a method that allows you to execute a single SQL statement multiple times with different parameter values. The method takes two arguments: the SQL statement and a list of parameter values. The statement is executed once for each set of parameter values in the list.
commit(): t is a method of the connection object. When a SQL statement is executed using the cursor's execute() method, the changes made to the database are not immediately saved to the disk. Instead, they are held in memory and can be rolled back if necessary. The commit() method is used to save the changes to the disk, making them permanent.
fetchall(): It is a method that retrieves all the rows of a query result as a list of tuples. This method should be used after a SQL SELECT statement is executed using the execute() method of a Cursor object.
close(): It closes a connection to the database and releases the resources associated with it. It is a method of the connection object and cursor object.

21 of 39

Stock Register Database

Write a python program to create a table named Stocks in the StockRegister.db with the following fields:

StockNo
StockName
Quantity
PricePerItem

22 of 39

Sample Code

Output

23 of 39

Stock Register Database

Write a python program that connects to the StockRegister.db and add 4 records in the table Stocks(Add few duplicate data).

24 of 39

Sample Code

Output

25 of 39

Stock Register Database

Write a python program that connects to the StockRegister.db database and to view all the records in the Stocks table.

26 of 39

Sample Code

Output

27 of 39

Stock Register Database

Write a python program and connect to StockRegister.db.

Retrieve and view the records where StockNo=102

28 of 39

Sample Code

Output

“SELECT * FROM Stocks where StockNo=102”

The keyword "SELECT" is used to indicate that we want to retrieve data from the database. The "*" symbol is used to select all the columns from the table. The keyword "FROM" is used to specify the table from which we want to retrieve the data, in this case, the table name is "Stocks". The "WHERE" keyword is used to filter the rows based on a certain condition. In this case, the condition is that the value of the "StockNo" column must be equal to 102. This query will retrieve all the rows from the table "Stocks" where the value of "StockNo" is equal to 102.

As there are two records where StockNo=102, so two records will be displayed.

Discuss how this would cause a problem, StockNo is the only realistic way of retrieving certain required records other fields can have duplicating value. So StockNo field should be having unique data but since there was no condition to check whether the data entered where unique or not, user could add duplicate data. The way to prevent this is having a Primary Key in the table

29 of 39

A primary key is a special type of column in a table that is used to uniquely identify each row in the table.

1

Special identifier for each record in the table.

2

Think of a primary key as a special type of ID number, like a CID No or a passport number.

3

Primary keys are used to maintain the integrity of the data in a table, by ensuring that each row has a unique identifier.

4

Primary key values must be unique and can't be null.

5

Primary Key

30 of 39

Identify the Primary Key

CustID	Name	Bill Amount	Remarks

001	Sonam	12000	Paid
002	Tshering	23000	Not paid
003	Sonam	23000	Not Paid

31 of 39

Student Details Database

32 of 39

Student Details Database

Write a python program to create a new database StudentDetails.db and create a table named Details with the following fields:

StdCode
StdName
Class
Age
Gender

Set StdCode as the Primary Key.

Add four records and the view the records

“UPDATE Details SET StdName=’Karma Lhamo’ WHERE StdCode=1002”

The query updates a record in the "Details" table where the value of the "StdCode" column is 1002. The value of the "StdName" column for this record is set to "Karma Lhamo". The "UPDATE" keyword is used to make changes to existing records in a table, the "SET" keyword is used to specify the column whose value is to be changed and the "WHERE" keyword is used to specify which record or records should be updated.

“DELETE FROM Details where StdCode=1003”

This SQL query deletes a record from the "Details" table, specifically the record where the value of the "StdCode" column is 1003. The "DELETE" keyword is used to delete existing records in a table and the "FROM" keyword is used to specify which table to delete the records from. The "WHERE" keyword is used to specify which record or records should be deleted. This query will remove the entire row of the student with StdCode=1003 from the Details table.

“DROP TABLE Details”

The query is used to delete a table from the database. The "DROP TABLE" statement is used to remove a table permanently from the database. Once a table is dropped, it cannot be recovered, so it is important to be very careful when using this command.

Mention the students to backup their database before making any changes by creating another copy of the file and it can be used later if needed.

33 of 39

Sample Code

Output

34 of 39

Student Details Database

Write a python program to connect to the database StudentDetails.db

Update the name of the student as “Karma Lhamo” where StdCode=1002

“UPDATE Details SET StdName=’Karma Lhamo’ WHERE StdCode=1002”

The query updates a record in the "Details" table where the value of the "StdCode" column is 1002. The value of the "StdName" column for this record is set to "Karma Lhamo". The "UPDATE" keyword is used to make changes to existing records in a table, the "SET" keyword is used to specify the column whose value is to be changed and the "WHERE" keyword is used to specify which record or records should be updated.

“DELETE FROM Details where StdCode=1003”

This SQL query deletes a record from the "Details" table, specifically the record where the value of the "StdCode" column is 1003. The "DELETE" keyword is used to delete existing records in a table and the "FROM" keyword is used to specify which table to delete the records from. The "WHERE" keyword is used to specify which record or records should be deleted. This query will remove the entire row of the student with StdCode=1003 from the Details table.

“DROP TABLE Details”

The query is used to delete a table from the database. The "DROP TABLE" statement is used to remove a table permanently from the database. Once a table is dropped, it cannot be recovered, so it is important to be very careful when using this command.

Mention the students to backup their database before making any changes by creating another copy of the file and it can be used later if needed.

35 of 39

Sample Code

Output

36 of 39

Student Details Database

Write a python program to connect to the database StudentDetails.db

Delete the record where StdCode=1003
Drop the table

37 of 39

Sample Code

Output

38 of 39

Post Session Activity

Complete the tasks Activity I and Activity II under SQLite topic in the Google Classroom.

1 of 39

2 of 39

3 of 39

4 of 39

5 of 39

6 of 39

7 of 39

8 of 39

9 of 39

10 of 39

11 of 39

12 of 39

13 of 39

14 of 39

15 of 39

16 of 39

17 of 39

18 of 39

19 of 39

20 of 39

21 of 39

22 of 39

23 of 39

24 of 39

25 of 39

26 of 39

27 of 39

28 of 39

29 of 39

30 of 39

31 of 39

32 of 39

33 of 39

34 of 39

35 of 39

36 of 39

37 of 39

38 of 39

39 of 39