ICT Grade 11 PPT 2024 PDF

2 1st Semester @Amboo Ifa Boruu SBS Chapter One:- Database Management Systems I am Teacher of AIBSBS in 2025 My Email Address: [email protected] TE...

2 1st Semester @Amboo Ifa Boruu SBS Chapter One:- Database Management Systems I am Teacher of AIBSBS in 2025 My Email Address: [email protected] TEMPORARY NOTE Prepared by: Tolesa Sori Huluka (Msc in Csc) Learning Outcomes/Objectives 3 Students will able to: ≈ What is Data, Database and Types Database? ≈ What is the important of Database? ≈ Terms is Database? ≈ What is SQL and Its Advantages? ≈ What is the difference between SQL vs NoSQL? ≈ MySQL installation on Window 10/11. ≈ Introduction to MySQL Workbench. ≈ SQL Commands and Common Aggregate Functions. What is a Data? 4 ≈ Data is a collection of raw, unorganized, unprocessed facts and details. ≈ ‘Data’ → Datum that means “Single piece of information” ≈ Examples: Text, Media, Figures, Symbols, Observations and Descriptions of things, Test Scores of Student, Temperature Reading, Name, Age etc. ≈ Data does not carry any specific purposes and has no significance by itself. ≈ Raw data alone is insufficient for decision making. ≈ Data is Information that can be translated into a form that is efficient for movement and Processing. What is a Database? 5 Definition Database in different terminology ❖ “A set of information held in a computer” Oxford English Dictionary ❖ “One or more large structured sets of persistent data, usually associated with software to update and query the data” Free On- Line Dictionary of Computing ❖ “A collection of data arranged for ease and speed of search and retrieval” Dictionary.com ❖ “A database is information that's set up for easy access, management and updating.” Internet What is a Database? 6 ≈ Database is a collection of data stored in some organized fashion so that it can be easily accessed and managed. ≈ Database is a container (usually a file or set of files) to store organized data. ≈ Confusions! We often use the term “Database” to refer to the database software we are using. ≈ Database Software is actually called the Database Management System( or DBMS). ≈ The DB is the container created and Manipulated via the DBMS. Example of Databases 7 ≈ A Database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval, manipulation, and management of the data. ≈ It is designed to store large amounts of information in a systematic manner, making it easier to access and manipulate data as needed. ≈ Databases are used in various applications and industries to store and manage information. Example: ≈ In a school situation, a database can be used to store student records, including their names, grades, attendance, and other relevant information. ≈ In a business context, a database can be used to store customer data, product information, sales records, and more. Example of Databases 8 School Registry: ≈All the details of the students are entered in a single file. You get the details regarding the students in this file. ≈It needs to store, manipulate, and present data related to users, their friends, users, activities, messages, Advertisements, and a lot more. ≈They store data related to customer, product, orders, payment, carts and order tracking details etc… Importance of Databases 9 1. Data Retrieval and Analysis ❖ Databases offer powerful tools for searching, retrieving, and analyzing data. 2. Data Consistency and Integrity ❖ Databases provide rules and constraints that ensure data remains accurate and valid. 3. Data sharing and Collaboration ❖ Databases enable multiple users to access and share data simultaneously. 4. Business efficiency and Decision making ❖ Databases provide businesses with the ability to organize, manage, and analyze their data effectively. Some activity Question 2:00 10  Explain why we need database in various industries?  Write at least three example of DB with Full explanation? Note ≈ Data: facts that can be record. E.g text, numbers, images. ≈ Database:- A collection of related data that represent some real world entities. ≈ Information:- Meaningful/Processed data. ≈ A database is a collection of organized data, information and records. ≈ Database is information that a person needs is his personal , business, social and religious life and the power and the power and purpose of information is not only in collecting and finding them but more importantly in using them. Types of Databases 11 1. Relational Databases (RDBMS) RDBMSs are the most widely used type of databases. Data is organized into tables (relations) with rows and columns. Relationships between tables are defined using Foreign keys. Examples include MySQL, Oracle, SQLite, PostgreSQL… 2. NoSQL Databases NoSQL (Not Only SQL) designed to handle unstructured or semi- structured data often used in big data applications. It refers to non-relational databases that store data in a non- tabular format, rather than in rule-based, relational tables like relational databases do. Examples: MongoDB, Cassandra, Redis, CouchDB, Neo4j, Amazon Neptune, Amazon DynamoDB etc. Types of Databases 12 3. Network DBMS Uses a graph structures to represent relationships. Examples: Integrated Data store (IDS), TurboIMAGE. 4. Hierarchical DBMS Organizes data in a tree like structure, where each record has a single parent and can have multiple children. Examples: IBM Information Management System, Windows Registry. 5. Time-series Databases Designed to handle time-stamped data. Such as sensor data, financial market data, or log files. Examples: InfluxDB, TimescaleDB 6. Objected-Oriented DBMS Integrates OOP principles with database technology. Database Management Systems 13 ≈ Collection of interrelated data. ≈ Set of programs to access the data. ≈ DBMS contains information about a particular enterprise. ≈ DBMS provides an environment that is both convenient and efficient to use. ≈ DBMS is software that enables the creation, manipulation, and management of databases. ≈ DBMS is serves as an intermediary between users and the database, allowing for efficient data storage, retrieval, and management. ≈ Some popular DBMSs include MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL, and MongoDB. Database Management Systems 14 ≈ Short name for database management system is DBMS. ≈ We can break it like this DBMS = Database + Management System ≈ Database Applications: ≈ Banking: All transactions. ≈ Airlines: Reservations, Schedules. ≈ Universities: Registration, Grades ≈ Sales: Customers, Products, Purchases. ≈ Manufacturing: Production, Inventory, Orders, Supply chain. ≈ Human Resources: Employee records, Salaries, Tax deductions. ≈ Databases touch all aspects of our lives. Database VS DBMS 15 ≈ A database is a collection of data. ≈ A database usually consists of data one or more files on one or more disks of a computer or its may be distributed across multiple computers. ≈ Database is not a software. ≈ DBMS on the other hand is a software. ≈ DBMS is used to manage databases. ≈ DBMS usually consists of one or more programs (executables and libraries) written in programming languages such as C, C++ and Java Note: Database != DBMS Important terms of Database 16 Tables  In RDBMS, a table is a fundamental component where data is stored.  A tables is a structured collection of data stored in a database.  Tables are also called “Relations”.  It consists of rows columns and represents an entity or a type of data.  In a table, there could be any number of rows and specified number of columns. Characteristics  Structure:- Each table has a unique name and is made up of multiple columns and rows.  Schema: The structure of a table is defined by its schema, which specifies names and data types of each column.  Relationships: Tables can be related to one another through keys, allowing for complex data structures. Example: A students table might store information about students in School, Examples of Tables 17 Columns/Fields/Attributes 18  A column (field) is a vertical entity in a table that contains all the values for a specific attribute of the entity represented by the table.  A single field in a table. All tables are made up of one or more columns. Characteristics  Data Type:- Each column has a defined data type (e.g integer, varchar, date) that determines what kind of data it can hold.  Name: Each column must have a unique name within the table to identify it.  Constraints: Columns can have constraints (e.g not null, unique) that enforce rules on the data entered. Example: A students table, you might have columns like: StudentID (integer), FirstName(varchar), LastName(varchar) , Email(varchar). Row/Records/Tuple 19  A single entry in a table is called a Tuple or Record or Row.  A row (or record) is a horizontal entity in a table that contains all the information for a single instance of the entity represented by the table.  Data in a table is stored in rows; each record saved is stored in its own row. A tuple in table represents a set of related data.  Think table as a Excel sheet grid so the horizontal rows are the table rows Characteristics  Data Entry:- Each row represent one complete set of related data corresponding to the columns defined in the table.  Uniqueness: Typically, each row is uniquely identifiable by a primary key, which is often a column (or combination of columns) designated to uniquely identify each record. Note: A row are also know as TUPLE. Primary Key and Foreign Key 20 Primary Key  A column whose values uniquely identify every row in a table.  A set of attributes that can be used to uniquely identify every tuple is a primary key.  It is the unique identifier for each row in a table. There can only be one primary key in a table, and it can't be null. ID is the primary key for the above table. Each student is uniquely identified by their ID. Now two rows have the same ID. The first column is usually the primary key of the table. Foreign Key 21  A foreign key is a column or a set of columns in a table that refers to the primary key of another table.  It establishes the relationship between the table.  A foreign key can be null and there can be more than one foreign key in the table.  For example, in the Department table you might have an emp_id column as a foreign key. This allows you to link departments to specific employees. Summary of primary key, tables, columns and rows 22 Figure: Components of a database Relationships between Rows, Columns, and Tables. 23  Tables as Entities: Each table represents a distinct entity.  Column as Attributes: Each column represents an attribute of that entity.  Rows as Instances: Each row in a table represents a single instance or record of the entity defined by the table. Summary Tables organize data into structured collections. Columns define the attributes of the entities represented by those tables. Rows contain the actual data entries for each instance of the entity. What is SQL ? 24  SQL was developed at IBM by Donald D. Charberlin and Raymond F. Boyce in the early 1970s.  SQL (pronounced as the letters S-Q-L or as Sequel) is an abbreviation for Structured Query Language.  It was initially called SEQUEL (Structured English QUEry Language)  SQL is a powerful Language that uses Simple English sentences.  It used to communicate the with Relational Database.  SQL is made up of very few words.  SQL provide you with a simple and efficient way to read and write data from a database.  All the RDBMS like MySQL, Oracle, MS Access, SQL Server and Imformix use SQL as their standard database Language.  Extensions Provide additional functionality to the language.  SQL depends on tuple relational calculus and relational algerbra.  SQL is easy to learn and SQL has well defined standards. Structured Query Language 25 SQL is a standardized programming language used for managing and manipulating relational databases. SQL provides a set of commands, or statements, that allow users to interact with databases to perform various operations, such as creating, modifying, updating, inserting, deleting and retrieving data. SQL is a widely adopted and standardized language used across different database management systems, including MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and others. Note: SQL is the language used to interact with databases. Advantages of SQL ? 26  SQL is not a language used by specific database.  Almost every major DBMS supports SQL.  SQL is easy to learn and don’t need any coding skills.  SQL is also portable. Like PCs, Server, Laptops independent of any platform. (operating System, etc.)  SQL is standardized Language.  SQL is a very powerful language which can perform very complex and sophisticated database operations.  SQL keywords are NOT case sensitive. Select is the same as SELECT. Generally, keywords of SQL are written in uppercase.  Statements of SQL are dependent on text lines. We can use a single SQL statement on one or multiple text line. SQL with MySQL 27  When comparing MySQL and SQL, it’s important to clarity that SQL is a language, while MySQL is a DBMS that uses SQL.  MySQL is an open-source relational database management system (RDBMS) that uses SQL as its standard database language.  MySQL is an Open source, Fast and Reliable RDBMS software like Oracle, Sybase, MS SQL Server etc.  It was developed by Michael Widenius and AKA Monty and alternative to many of the commercial RDBMS.  It is widely used for web applications and is known for its reliability, performance, and ease of use.  SQL provides the language structure for interacting with relational databases, while MySQL is a specific implementation of that language in an RDBMS context. Understanding both SQL and MySQL is essential for managing and manipulating data effectively in various applications. Introduction to MySQL 28 The main features of MySQL are:-  Open Source and Free of cost: it is open source and available at free of cost.  Portability: It can be install and run on any types of Hardware and OS like Linux, MS Windows or Mac Etc.  Security : it creates secured database protected with password.  Connectivity: It may connect various types of network client using different protocols and programming Languages.  Query Language: it uses SQL for handling database.  High Performance: To handle large volumes of data efficiently.  Scalability: It can manage large databases and supports high-traffic applications.  Community Support: Being open-source, MySQL has a large community that contributes to its development and provides support. MySQL Workbench 29 There are various panels in MySQL Workbench to work with. Such as 1.SQL editor: User may use SQL editor to retrieve, create, change, or delete data in a database, if user has database permissions to perform these actions. 2.Result Grid: It displays the retrieved data in a tabular format, allowing you to analyze and manipulate the query results. 3.Schemas panel: Provides a visual representation of the database schema. It shows the tables, columns, indexes, and other components within a selected schema. 4.Server Administration panel: Provides access to the server administration tasks, such as starting and stopping the server, managing user accounts and privileges, and monitoring server status and performance. MySQL Workbench in Image 30 MySQL Workbench: Default port and Version 31  MySQL Workbench is provides a graphical interface to manage MySQL databases, allowing users to design, model, generate, and manage databases.  Default port: The default for MySQL is 3306. This is the port through which MySQL server listens for incoming connections.  When you connect to a MySQL database using MySQL Workbench or any other client, you typically specify this port unless it has been changed in the server configuration.  Version : MySQL Workbench has multiple versions that are updated and periodically. Each version may include new features, improvements, and bug fixes.  The version number can be found in the application itself usually under the “Help” menu or in the “About” section.  The latest stable version of MySQL Workbench was 8.0.x, where “x” represents the mirror version number. MySQL Workbench Functionalities 32  SQL Development: Enables you to create and manage connections to database servers.  Data Modelling: Enables Reverse and Forwarding Engineering, allows you to create models of database schema graphically.  Server Administration: Enables you to administer MySQL server instances by monitoring MySQL server Performance.  Data Migration: Allows to migrate from Microsoft SQL, Microsoft Access, Sybase ASE, SQLite, and other RDBMS tables, objects and data to MySQL.  MySQL Enterprise Support: Support for Enterprise products such as MySQL Enterprise Backup, MySQL Firewall, and MySQL audit. MySQL and SQL 33  In order to access data from the MySQL database, all program and user must use SQL ( Structured Query Language).  SQL is a set of commands that are recognized by all the RDBMSs and has become a standard language for database handling.  SQL is a language that enables you to create and manage a relational database, in which all the information are kept in tables.  There are numerous version of SQL. The original version was developed at IBM’s San Jose Research Laboratory with a name of Seqeul, as a part of system R project in 1970s. It was standardized by ANSI in 1986 by the name of SQL. Note: SQL is a Standard Query Language whereas MySQL is a Database Management System Software based on SQL. SQL Statements and SQL Commands 34 SQL Statements  An SQL statement is a complete instruction or query written in the SQL language.  SQL statements tell the database what operation you want to perform on the structured data and what information you would like to access from the database.  The statements of SQL are very simple and easy to use and understand. They are like plain English but with a particular syntax. SQL Commands  An SQL Command is instruction used to perform operations on a database.  The term ‘command’ is often used more broadly to refer to any SQL instruction or operation, such as modifying data, creating or altering database objects, granting privileges, and more. SQL Commands 35 The most basic commonly used SQL commands are: SELECT: Retrieves data from one or more tables in a database. INSERT: Adds new rows of data to a table in a database. UPDATE: Modifies existing rows of data in a table in a database. DELETE: Removes rows of data from a table in a database. CREATE: Creates new tables, views, or other database objects. ALTER: Modifies existing database objects, such as tables, views, or columns. DROP: Deletes or removes existing database objects. GRANT: Assigns permissions or access rights to users or roles in a db. REVOKE: Removes permissions or access rights from users or roles in a db. SQL Syntax 36 ≈ The syntax of the SQL is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax is defined and maintained by the ISO and ANSI standards. Some most important points about the SQL syntax which are to remember: ≈ You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query. ≈ SQL statements or syntax are dependent on text lines. ≈ You can perform most of the action in a database with SQL statements. ≈ SQL syntax depends on relational algebra and tuple relational calculus. SQL Commands 37 Note: SQL Commands must end with a semicolon. And they are not Case- Sensitive. SQL Commands in the SQL editor can be saved in a file. You can also get other SQL files from different sources to view, edit and execute them here. SHOW DATABASES; syntax is used to show all the databases. Saving SQL Commands ✓ Press Ctrl + S or the File menu to save the Queries inside an SQL Editor tab. ✓ File Extension for SQL is dot sql [Filename.sql] The SELECT Commands 38 ❖ The SELECT command is a statement used to retrieve data from one or more tables in a database. ❖ It allows you to specify which column to retrieve data from and any conditions or filters to apply to the data. The SELECT statement is used to select data from a database. Here, Column1, column2, … are the field name of the table you want to select data form. The table_name represents the name of the table you want to select data form. If you want to return all columns, without specifying every column name, you can use the SELECT * syntax: SELECT * FROM table_name; The USE Command 39 ≈ USE statement is used to select the database in SQL. ≈ It used to specify the database that will be used for subsequent SQL statements in the same SQL file. ≈ The beginning of an SQL script or file, to specify the database. SQL Clauses 40  Clauses are used to modify the behavior of SQL commands and help define the operations performed on the data. Some commonly used SQL clauses. SELECT: Specifies the columns to retrieve from a table. FROM: Identifies the table or tables from which data is being retrieved. WHERE: Filters data based on the specified conditions. ORDER BY: Sorts the result set of a SELECT statement based on one or more columns in ascending or descending order. JOIN: Combines rows from two or more tables based on the related column between them. LIMIT: Limits the number of rows returned by the SELECT statement. The WHERE Clause 41  The WHERE clause is used to filter records, it is used to specify any conditions or filters to apply to the data. Only rows that meet the specified condition(s) will be returned. Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc… SQL Comparison Operators 42  SQL operators that are used for comparison and logical operations within SQL statements to evaluate conditions and filter data. SQL Logical Operators 43  Logical operators allow you to create more complex conditions for filtering and manipulating data. Some Activity Time 2:00 44 SQL Question SQL is a standard language which stands for structured Query Language based on the English Language. SQL is the core of relational database which is used for accessing and managing database. **Question One** What is the VS difference between SQL MySQL is an open-source relational database and MySQL? management system that words on many platforms. It provides multi-user access to support many storage engines and is backed by Oracle. Some Activity Time 2:00 45 SQL is responsible for maintaining the relational data and the data structures SQL Question present in the database. Some of common usages are given below:  To execute queries against a database. **Question Two**  To retrieve data from a database.  To inserts records in a database.  To Updates records in a database. What are the  To delete records from a database. Usages of SQL ?  To create new database.  To create new tables in database.  To create views in a database.  To perform complex operations on the database. Common Aggregate Functions 46  Aggregate functions are functions that perform calculations on a set of values in a database table.  They take multiple values as input and return a single value as output, summarizing the data in a sum way.  Aggregate functions allow you to derive useful information and perform calculations on groups of rows or the entire dataset.  Think of it this way: Imagine you have a group of numbers, and you want to know the sum, average, minimum, or maximum value of those numbers.  Aggregate functions provide a way to perform these calculations without having to manually iterate through each value.  Most common aggregate functions in SQL 47 » COUNT() » Returns the number of rows that match a specified condition. » Example: SELECT COUNT(*) AS total_orders FROM orders; » This query returns the total number of orders in the orders table. » SUM() » Returns the total sum of a numeric column. » It adds up all the values in a numeric column. » Example: SELECT SUM(order_amount) as total_sales FROM orders. » This query calculates the total sales amount from the order_amount column in the orders table.  Most common aggregate functions in SQL 48 » AVG() » Returns the average (Mean) value of a numeric column. » Example: SELECT AVG (order_amount) AS average_order_value FROM orders; » This query computes the average order value from the order_amount column. » MIN() » Returns the smallest value in a set of values. » Example: SELECT MIN(order_date) AS first_order_date FROM orders. » This query retrieves the date of the first order placed from the order_date column.  Most common aggregate functions in SQL 49 » MAX() » Returns the largest value in a set of values. » Example: SELECT MAX (order_amount) AS highest_order_value FROM orders; » This query finds the highest order value from the order_amount column. » GROUP_CONCAT() (MySQL specific) » Returns a concatenated string of values from a group. » Example: SELECT customer_id, GROUP_CONCAT(order_id) AS order_ids FROM orders GROUP BY customer_id; » This query returns a list of order IDs for each customer, concatenated into a single string.  Most common aggregate functions in SQL with Examples 50 Summary of Common aggregate function in SQL with Examples. » COUNT(): Returns the number of rows that match a specified criterion. » Example: SELECT COUNT(*) FROM employees; » SUM(): Returns the total sum of a numeric column. » Example: SELECT SUM(salary) FROM employees; » AVG(): Calculates the average value of a numeric column. » Example: SELECT AVG(salary) FROM employees; » MIN(): Returns the smallest value in a specified column. » Example: SELECT MIN(salary) FROM employees; » MAX(): Returns the largest value in a specified column. » Example: SELECT MAX(salary) FROM employees; Using Aggregate Function with GROUP BY 51 » Aggregate functions are often used with the GROUP BY clause to group rows that have the same values in specified columns. » Here’s an Example: » This query returns the total number of orders and total amount spent by each customer. » Aggregate functions are powerful tools in SQL for summarizing data. » The allow you to perform calculations across multiple rows and can be combined with other SQL Clauses such as GROUP BY to analyze data more effectively. Modifying Data with SQL 52 » Modifying Data with SQL involves using specific commands to change, add, or remove records in a database. » Modifying Data with SQL is a fundamental aspect of DBMS. » The primary SQL statements for modifying data are INSERT, UPDATE, and DELETE. » The INSERT INTO statement in SQL is used to insert one or more new rows into a table. » The UPDATE statement in SQL is used to modify existing records in a table. » The DELETE statement is a SQL statement that is used to remove one or more rows from a table. » Below is the a comprehensive overview of each of these commands, along with best practices and examples. The SQL INSERT INTO Statement 53 » The INSERT INTO statement is used to insert new records in a table. » To copy the content from one table into another existing table. INSERT INTO Syntax » It is possible to write the INSERT INTO statement in two ways: 1. Specify both the column names and the values to be inserted: Example: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); 2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: Example:INSERT INTO table_name VALUES (value1, value2, value3,...); The SQL INSERT Statement 54 » The INSERT statement is used to add new rows to a table. » INSERT Syntax Example: Best Practices for INSERT : ≈ Specify Columns: Always specify the columns you are inserting values into to avoid errors if the table structure changes. ≈ Use Transactions: For bulk inserts, consider using transactions to maintain data integrity. ≈ Validate Data: Ensure that data being inserted meets the table's constraints (e.g., NOT NULL, UNIQUE). The SQL UPDATE Statement 55 » The UPDATE statement is used to modify existing records in a table. » Syntax: » Example: Best Practices: ≈ Always use a WHERE clause to specify which records to update; otherwise, all records in the table will be modified. ≈ Consider using transactions when updating multiple records to maintain data integrity. ≈ Test updates on a small dataset or in a development environment before executing them on production data. The SQL DELETE Statement 56 » The DELETE statement is used to remove records from a table. » Syntax: Best Practices: ≈ Always include a WHERE clause to avoid deleting all records in the table. ≈ Use transactions when deleting multiple records to ensure that you can roll back changes if necessary. ≈ Consider implementing soft deletes (e.g., using an is_deleted flag) instead of hard deletes for better data recovery options. 57 “Learn Something new every day, and find something to laugh about every day. To quote Muhammad Ali, ‘Don’t count the days, make the days count’” – Susan Young