G-12th Chapter-3 Notes PDF

Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa Notes on Database Management System Importance of Databases  Almost all information today is stored in databases.  Databases play a crucial role in organizations for effective data management.  Knowledge and skills for desig...

Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa Notes on Database Management System Importance of Databases  Almost all information today is stored in databases.  Databases play a crucial role in organizations for effective data management.  Knowledge and skills for designing databases are essential. Database Fundamentals  A database has a data model that dictates how data is: o Stored o Organized o Manipulated SQL (Structured Query Language)  SQL enables manipulation of records within a database. 3.1 Overview of Relational Database Management System (RDBMS)  RDBMS refers to software systems for managing databases.  Functions of RDBMS: o Create databases o Maintain databases o Provide controlled access to databases Relational Database Characteristics  Based on a relational data model.  Data is stored in two-dimensional tables consisting of: o Columns (fields): Represent attributes or data values. o Rows (records): Represent tuples or individual records. Table Structure  A database may contain one or more tables for maintaining records.  Attributes: Set of properties describing entity instances. o Example: A student as an entity with attributes like:  Student ID  Name  Age  Grade level  Sex Record Definition  A record is a row or tuple within a table.  Each record contains a single data value per column. Field Attributes  Each field in a table must have: o A name o A data type: Specifies the kind of data stored.  Example:  Residence ID: Four-digit number. 1  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  First Name: Text form for storing names. Key Concepts  Data Model: Framework for organizing data in a database.  Attributes: Characteristics of an entity.  Tuples: Individual records in a database table. Conclusion  Understanding the structure and management of databases is essential for effective data handling in modern organizations.  Mastery of SQL and RDBMS concepts is crucial for database design and maintenance. Data Types in a Database o The data type defines the nature of data stored in fields of a table. o Common examples of data types include:  Integer: Whole numbers (e.g., age)  Float/Real: Decimal numbers (e.g., price)  Text: Alphanumeric characters (e.g., name)  Date: Calendar dates (e.g., date of birth)  Currency: Monetary values (e.g., cost)  Time: Represents time (e.g., opening hour)  Yes/No: Boolean values indicating true or false  Sample Fields and Data Types (Table 3.1) Fields Data Type Data Values (Example) Age Integer 19 Name Text Abdu Price Real/Currency 32.5 Date of Birth Date 10/12/2008 Height Integer/Real 175 or 165.6 Grade Level Integer 11 or 12 Telephone Number Text +251 900 00 00 00 Defining Data Types in MS Access o All DBMS provide a selection of data types to define fields. o In MS Access, when creating a new table:  Specify the type of data in each field.  Data types include:  Text  Integer  Real Numbers  Dates  Currency  Yes/No fields o Refer to Figure 3.2 for a sample of data types in MS Access.  Activity 3.1 2  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa Fields Data Type Example Book Title Text [Example Title] Email Text [[email protected]] Sales Date Date [Sales Date] Body Temperature Integer/Real [Temperature Value] Weight Integer/Real [Weight Value] Cost of Material Real/Currency [Cost Value] Expire Date Date [Expiration Date] Opening Hour Time [Opening Time] 1. Provide sample data values for the following data types: 2. Given a patient table, write three fields and their data types to describe a patient:  Patient ID: Integer  Patient Name: Text  Patient DOB: Date Database Structure  The database depicted in Figure 3.3 contains five tables:  Students  MarkList  Subject  SubjectAssigned  Teachers  Students Table o Contains three fields:  Sid (Primary Key): Unique identifier for each student.  SName: Student’s name.  FName: Student’s family name. o Example record in Students table:  Sid: S001  SName: Chaltu  FName: Gemeda o Primary Key: Sid is unique; no duplicate records allowed (e.g., S001 cannot repeat).  Relational Database Features o Each table with a key field (Primary Key) uniquely identifies records. o Key fields can establish relationships between tables:  Foreign Key: A field in one table that links to the primary key in another (e.g., Sid in MarkList).  Example: The MarkList table records student marks for enrolled courses.  A student may have multiple records for different courses, leading to non-unique Sid in MarkList.  Popularity of Relational Databases o Relational databases are widely used and have been around for over four decades. o Examples of popular RDBMS include: 3  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  Microsoft Access  Oracle  Microsoft SQL Server  MySQL  SQLite  IBM DB2  PostgreSQL Key Concepts  Primary Key: Uniquely identifies each record in a table (e.g., Sid in Students).  Foreign Key: Links to the primary key in another table (e.g., Sid in MarkList).  Data Type: Defines the kind of data a field can store (e.g., Integer, Text).  DBMS: Software for creating and managing databases (e.g., MS Access). Summary  Data types are crucial for defining and organizing data in a database.  Understanding how to structure tables and relationships is essential for effective database management.  The relational model facilitates connections between different data entities, enhancing data integrity and accessibility. 3.2 Database Manipulation Using SQL  SQL (Structured Query Language): o Standard language for accessing and manipulating databases. o Special-purpose query language for interacting with relational databases (e.g., Microsoft Access). o Understanding SQL enhances query creation and troubleshooting. SQL Command Categories 1. Data Definition Language (DDL): o Commands for creating or modifying tables. o Establishes relationships between tables in the database structure. 2. Data Manipulation Language (DML): o Commands for managing data within a database. o Operations include:  Inserting data.  Updating existing data.  Deleting data.  Navigating through data. 3. Data Query Language (DQL): o Commands specifically for querying or selecting data from a database.  Additional commands may be offered by the DBMS (Database Management System) for extra features, but the three categories above are standard across all DBMSs. SQL Command Applications 4  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa 1. Commands to use: o Create a table: CREATE TABLE o Modify a table: ALTER TABLE o Delete a table: DROP TABLE o Add a record to a table: INSERT INTO o Modify a record in a table: UPDATE o Remove a record in a table: DELETE FROM 2. Command Categorization: o DDL: CREATE TABLE, ALTER TABLE, DROP TABLE o DML: INSERT INTO, UPDATE, DELETE FROM o DQL: SELECT 3.2.1 Using SQL in Microsoft Access  Microsoft Access: o Default relational database program in the Microsoft Office package. o Provides database functionality and programming capabilities for creating and navigating databases. o SQL commands can be implemented and manipulated within Access. Checking Microsoft Access Installation  Steps to verify installation and version: 1. Click the Windows icon on the taskbar. 2. Select All apps. 3. Find and click on Microsoft Office. 4. Locate Access followed by the year/version to confirm installation. Creating a Database in Microsoft Access  Launch Access by typing “access” in the Search box under the Start menu.  Two methods to create a database in Microsoft Access: 1. Create a Database from Template: Use pre-defined templates for quick setup. 2. Create a Blank Database:  Select this option to start from scratch, allowing full customization of the database structure. Summary  SQL is essential for effective database management and manipulation.  Understanding the categories of SQL commands (DDL, DML, DQL) is crucial for efficient database operations.  Microsoft Access offers a user-friendly interface for utilizing SQL commands, making database creation and management accessible to users.  Checking the installation and version of Microsoft Access ensures compatibility and access to its features.  Creating a database can be streamlined through templates or initiated from a blank slate, depending on user requirements. Creating a Table in Microsoft Access o First step after creating a database is to create a table for data. 5  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o There are three methods to create tables in Access: 1. Create a Table from Design View 2. Create a Table from Datasheet View 3. Create a Table from SQL (focus of this section) Writing a Query o To start writing a query:  Click on the Create tab.  Click on the Query Design button. o Show Table Dialog Box  Close the Show Table dialog box that appears after clicking Query Design.  Accessing SQL View o Click on the Dropdown of the SQL View located in the upper left corner of the window. o Select SQL View from the displayed options to open the query writing window.  Writing SQL Commands o In the query writing window, you can write various SQL commands, including:  CREATE: Used to create new database objects.  SELECT: Used to retrieve data from one or more tables.  UPDATE: Used to modify existing data.  INSERT: Used to add new records to a table.  Running SQL Commands o After writing your query using SQL commands, you can:  Save your query for future use.  Run the query to execute the commands written. This structured approach helps to efficiently create tables and manage data within Microsoft Access, facilitating better database management and operations. How to Run SQL Commands  Running Queries o After writing SQL queries, users can save or run the query to view results. o If there are errors, Access displays a pop-up window indicating the error type.  Alternatives to Run SQL Commands 6  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o Alternative 1:  Navigate to the Design tab.  In the Results group, click on the Run option. o Alternative 2:  Double-click the saved query (e.g., “Male Students”) to execute it.  Activity 3.4 Instructions: o Open MS Access. o Create a blank database named ‘School’. o Create and save a new query as ‘student query’. Data Definition Language (DDL)  Overview of DDL o DDL is a subset of SQL used for creating and restructuring databases. o Key DDL commands include:  CREATE TABLE  ALTER TABLE  DROP TABLE  CREATE TABLE Command o The CREATE TABLE command is utilized to create a new table in a database. o General syntax for CREATE TABLE: o CREATE TABLE table_name( o Column1 datatype [Primary Key], o [Column2 datatype] [REFERENCES table_name2(Column1)], o [Column3 datatype], o [Column4 datatype]… o ) o Components of the Syntax: 1. CREATE TABLE: The command to initiate table creation. 2. table_name: The designated name of the table (e.g., Farmer). 3. Column1, Column2, Column3: Fields within the table (e.g., name, age, subject). 4. [Primary Key]: An optional command to define a unique identifier for the table. 7  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa 5. datatype: Specifies the type of data a field can contain (e.g., CHAR, INT).  Notes on Table Creation o Each column must have a defined name and data type. o Different Database Management Systems (DBMS) may use various data type names. o Some data type names may be identical across systems but differ in size and details; always consult the respective documentation for accuracy.  Specialized Data Types o Various specialized data types are utilized in SQL statements within Access.  SQL Commands: o Used to create tables in a database. o Syntax for creating a table:  Format: CREATE TABLE table_name (attribute1 data_type, attribute2 data_type,...);  Each attribute definition is separated by a comma.  Table name must be followed by an opening parenthesis and closed with a closing parenthesis.  Attributes and Data Types: o Each column in a table must specify an appropriate data type. o Example of specifying character size:  C_Name TEXT (20) restricts the data value to 20 characters. Exceeding values are truncated.  Spaces are counted as characters.  Example Tables: o DEPARTMENT Table:  Created with three attributes. o COURSE Table:  Created with five attributes.  MS Access Query View: o Used to write SQL statements for creating tables. o Example provided for creating DEPARTMENT (upper) and COURSE (lower) tables. 8  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa Activity 3.5: SQL Table Creation 1. Create Tables: o Student Table:  Attributes:  student_id: CHAR(10) (Primary Key)  name: CHAR(20)  sex: CHAR(1)  age: INTEGER  grade_level: INTEGER o Teacher Table:  Attributes:  teacher_id: CHAR(10) (Primary Key)  name: CHAR(20)  sex: CHAR(1)  age: INTEGER  specialization: CHAR(15) o Grade Table:  Attributes:  student_id: CHAR(10)  teacher_id: CHAR(10)  course_code: CHAR(20) (Foreign Key referencing COURSE table)  mark: INTEGER 2. Execution of SQL Commands: o Run the SQL commands to create the student, teacher, and grade tables. o Verify the resulting tables are created successfully. 3. Syntax Error Correction: o Review the following CREATE statement for syntax errors:  Original Statement:  CREATE TABLE Patient (pid INTEGER, P_Name TEXT P_Age INT  CREATE Physician (id CHAR, name TEXT, DoB DATETIME, PRIMARY KEY(id)) 9  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  CREATE TABLE Treatment (ptid INTEGER REFERENCES Patient pid, drId CHAR REFERENCE id)  Issues Identified:  Missing commas between attributes.  Incorrect reference syntax.  Corrected Statements:  CREATE TABLE Patient (pid INTEGER, P_Name TEXT, P_Age INTEGER);  CREATE TABLE Physician (id CHAR(10), name TEXT, DoB DATETIME, PRIMARY KEY(id));  CREATE TABLE Treatment (ptid INTEGER REFERENCES Patient(pid), drId CHAR(10) REFERENCES Physician(id)); 4. Create Table SQL Statement for Physician: o Based on the provided records, specify appropriate data types and sizes: o Physician Table Structure:  Attributes:  employee_id: CHAR(10)  Name: CHAR(50)  position: CHAR(30)  bank_account: CHAR(20)  bank: CHAR(40) o SQL Statement: 5. CREATE TABLE Physician ( 6. employee_id CHAR(10), 7. Name CHAR(50), 8. position CHAR(30), 9. bank_account CHAR(20), 10. bank CHAR(40) 11. ); Summary of Key Concepts 10  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  CREATE TABLE Command: Essential for defining the structure of a database table.  Data Types: Crucial for ensuring data integrity and appropriate storage.  Primary Key: Uniquely identifies each record in a table.  Foreign Key: Establishes a relationship between two tables, ensuring referential integrity.  Syntax Accuracy: Important for the successful execution of SQL commands. Vocabulary Terms  Table: A structured set of data held in a database.  Attribute: A column in a table, representing a data field.  Data Type: Defines the kind of data that can be stored in a column (e.g., INTEGER, CHAR, and TEXT).  Primary Key: A unique identifier for records in a table.  Foreign Key: A field in one table that uniquely identifies a row of another table.  Syntax Error: Mistakes in the SQL command structure that prevents execution. Conclusion Understanding the fundamentals of SQL and database management is essential for effective data organization and manipulation. The ability to create tables with proper structure, define relationships, and ensure data integrity is critical skills in database management systems. ALTER TABLE Command Purpose: The ALTER TABLE command allows modifications to an existing table. Key Operations: o Add Column(s): Introduce new columns to the table. o Drop Column(s): Remove existing columns from the table. o Change Column Definitions: Alter the datatype or attributes of existing columns. o Establish Relationships: Define foreign keys to create relationships between tables.  Example of Modifying Data Types: o When creating relationships, ensure compatible datatypes. o For instance, if D_Number is CHAR(10) and C_Department is CHAR(15), an error occurs when relating the two. o To resolve, modify C_Department in the COURSE table:  Command to change datatype:  ALTER TABLE COURSE ALTER COLUMN C_Department CHAR(10); 11  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  Alternatively, drop and re-add the column:  Drop Command: ALTER TABLE COURSE DROP COLUMN C_Department;  Add Command: ALTER TABLE COURSE ADD COLUMN C_Department CHAR(10);  Setting a Primary Key: o If a primary key was overlooked during table creation, it can be added via the ALTER command. o For example, to set teacher_id as the primary key in the TEACHER table:  Command: ALTER TABLE TEACHER ADD PRIMARY KEY (teacher_id); Creating Relationships with Foreign Keys: o The ALTER command can also be used to establish foreign key relationships. o Example to modify the COURSE table to link with the DEPARTMENT table through D_Number:  Command: ALTER TABLE COURSE ADD FOREIGN KEY (C_Department) REFERENCES DEPARTMENT (D_Number); Activity Modifications 1. Physician Table Modifications: o Modify employee id:  Change datatype to CHAR with a size of 7 (e.g., ‘Py/1001’):  Command: ALTER TABLE Physician ALTER COLUMN employee_id CHAR(7);  Set employee id as PRIMARY KEY:  Command: ALTER TABLE Physician ADD PRIMARY KEY (employee_id); o Add a new column for date of hire with DATE-TIME datatype:  Command: ALTER TABLE Physician ADD COLUMN date_of_hire DATETIME; 2. Student and Teacher Tables Modifications: o Create relationships with the grade table:  For Student table:  Add student_id as a foreign key:  Command: ALTER TABLE Student ADD FOREIGN KEY (student_id) REFERENCES grade (student_id); 12  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  For Teacher table:  Add teacher_id as a foreign key:  Command: ALTER TABLE Teacher ADD FOREIGN KEY (teacher_id) REFERENCES grade (teacher_id); DROP TABLE Command  Purpose: The DROP TABLE command is utilized to delete an existing table from the database.  Usage: o This command is straightforward when a table is no longer needed or should be removed. o Example command to delete the COURSE table:  Command: DROP TABLE COURSE;  Caution: o Deleting a table with existing records is irreversible; once executed, the data cannot be recovered. Summary  The ALTER TABLE command is essential for modifying table structures, ensuring proper relationships between tables, and setting primary keys.  The DROP TABLE command is a powerful tool for removing tables but should be used with caution due to its irreversible nature. Creating Relationships Visually in MS Access o DATABASE TOOL tab allows for graphical creation of relationships between tables. o Relationships help in combining data from two or more different tables. o Each relationship comprises fields in two tables with corresponding data.  Types of Relationships o Relationships can be categorized as:  One-to-One  One-to-Many  Many-to-Many  Steps to Create a Relationship 1. Navigate to the ‘Database Tools’ tab. 2. In the ‘Relationships’ group, click ‘Relationships’. 3. On the pop-up ‘Show Table’ window:  Select one or more tables or queries.  Click ‘Add’. 13  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  After adding tables/queries, click ‘Close’. 4. To create a relationship:  Drag a field (typically the Primary Key) from one table to the corresponding Foreign Key in the other table.  For multiple fields, press the CTRL key, select each field, then drag the Primary Key over the Foreign Key.  Example of Relationships o In a school database:  DEPARTMENT table includes D_NUMBER (Primary Key).  Student, Course, and Teacher tables reference D_NUMBER as a Foreign Key.  Offer_Course table uses T_IDNO as a Foreign Key.  C_Code serves as a Foreign Key in both Offer_Course and Take_Course tables.  S_IDNO is a Foreign Key in the Take_Course table.  Key Considerations o Primary keys (e.g., D_NUMBER in DEPARTMENT table) and Foreign keys (e.g., T_department in Teacher table) must have the same data type. o Records should not be added before establishing relationships; otherwise, errors may occur if the records do not meet relationship requirements.  Relationship Representation o The infinity symbol (∞) and ‘1’ in diagrams indicate the nature of relationships:  ∞ signifies the many side of a relationship.  1 indicates the one side. o Example: The relationship between Department and Teacher is one-to-many:  One record in the DEPARTMENT table corresponds to many records in the Teacher table.  Relational Database Model o All related tables must have a common data item (key field) to enable the linking of information across different tables. 3.2.3 Data Manipulation Language (DML)  Definition of Data Manipulation: 14  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o Data Manipulation Language (DML): A set of commands used to manage and manipulate data within a database. o Key operations include:  INSERT: Adding new records to a table.  UPDATE: Modifying existing records.  DELETE: Removing records from a table. INSERT Command Purpose: o The INSERT command is used to add new records to a database table.  Options for INSERT Command: o Option 1:  Syntax: INSERT INTO table_name VALUES (value1, value2, value3,...)  Used when all fields in the record are being replaced with new values. o Option 2:  Syntax: INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...)  Used when only selected fields are being updated with new values.  Important Considerations: o When using the INSERT INTO command:  The column value must match the column data type of the table.  Example: Cannot insert a text value into a numerical field.  String values must be enclosed in single or double quotation marks (e.g., ‘Chemistry’ or “Chemistry”).  Numeric fields (e.g., Age INT) do not require quotation marks (e.g., 20). Examples of INSERT Command  Example 1: Inserting into a COURSE table: o Command: o INSERT INTO COURSE VALUES (3, ‘Chemistry’, ‘Organic chemistry’, ‘105’, ‘NS1421’); o Explanation:  This command inserts a record into the COURSE table, which has five attributes:  Credit Hours (INT data type)  Course Name (string)  Course Description (string)  Department (string)  Course Code (string)  Values correspond to the respective columns. Note that:  The credit hours are of INT type, while other fields are strings.  ‘105’ is treated as a string, not a numeric value.  Example 2: Inserting into a DEPARTMENT table: o Command: o INSERT INTO DEPARTMENT (D_Number, D_Name, D_Location) VALUES (‘D101’, ‘Biology’, ‘NBR First Floor’); o Explanation: 15  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  This command demonstrates the use of Option 2 for the INSERT command syntax.  The command specifies column names in parentheses followed by the VALUES section.  Fields not specified in the INSERT statement will be left blank or default values will be recorded.  Additional Example of Partial INSERT: o Command: o INSERT INTO COURSE (Contact_Hr, C_Name, C_Department, C_Code) VALUES (4, ‘Biology’, ‘102’, ‘NS2323’); o Explanation:  In this command, the value for C_DESCRIPTION is omitted.  Consequently, when the query is executed, C_DESCRIPTION for the Biology course will be left blank in the table. Summary of Key Terms  Data Manipulation Language (DML): Commands for managing data in databases.  INSERT Command: Adds new records to a table.  Option 1: Inserts values for all fields.  Option 2: Inserts values for specific fields.  Data Type: The type of data that can be stored in a field (e.g., string, INT).  Quotation Marks: Used for string values; unnecessary for numeric values. Conclusion  DML is essential for effective database management, allowing users to perform critical operations on data.  Understanding the INSERT command and its syntax options is fundamental for adding records accurately to database tables.  Proper management of data types and values is crucial for maintaining data integrity within the database. Activity 3.9 Summary Notes Inserting Records into Tables  Student Table o Records to Insert:  Student_id: 1001/2013, Name: Halima Ali, Sex: F, Age: 16, Grade_level: 11  Student_id: 1002/2013, Name: Demess Lema, Sex: M, Age: 16, Grade_level: 11  Student_id: 1010/2013, Name: Shewit Belay, Sex: F, Age: 17, Grade_level: 12  Student_id: 1011/2013, Name: Kena Elias, Sex: M, Age: 17, Grade_level: 12  Teacher Table o Records to Insert:  Teacher_id: 2001/2010, Name: Roman Nesibu, Sex: F, Age: 31, Specialization: Physics  Teacher_id: 2002/2010, Name: Helen Alemu, Sex: F, Age: 30, Specialization: ICT  Teacher_id: 2010/2010, Name: Belay Getaneh, Sex: M, Age: 30, Specialization: Geography  Teacher_id: 2011/2010, Name: Demeke Mulat, Sex: M, Age: 31, Specialization: Mathematics 16  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  Grade Table o Records to Insert:  Student_id: 1001/2013, Teacher_id: 2001/2010, Subject: Physics, Mark: 80  Student_id: 1002/2013, Teacher_id: 2002/2010, Subject: ICT, Mark: 85  Student_id: 1010/2013, Teacher_id: 2010/2010, Subject: Geography, Mark: 79  Student_id: 1011/2013, Teacher_id: 2011/2010, Subject: Mathematics, Mark: 90 Errors in INSERT Statements  Identified Problems: o Incorrect Syntax for INSERT Statement:  INSERT IN TO GRADE VALUES(‘1012/13’, ‘2023/2010’, ‘English’)  Correction: INSERT INTO GRADE VALUES(‘1012/13’, ‘2023/2010’, ‘English’); o Incorrect Column References in INSERT Statement:  INSERT INTO TEACHER VALUES(‘teacher_id’, ‘Sex’, ‘Age’)  Correction: Specify actual values instead of column names. o Missing Columns in INSERT Statement:  INSERT INTO STUDENT (Student_id, Name, Sex) VALUES(‘1015/13’, ‘Yared’)  Correction: Include all required columns, e.g., Age and Grade_level.  INSERT Statements for New Records: o Records to Insert:  Student_id: 1001/2014, Name: Meymuna, Age: 20  Student_id: 1002/2014, Name: Alehegne, Age: 20  Student_id: 1003/2013, Name: Kello, Age: 19 UPDATE Command Overview Purpose of the UPDATE Command: o Modifies existing records in a table without adding or removing them. o Changes values in one or more fields of existing rows.  General Syntax:  UPDATE table_name  SET column1 = value1, column2 = value2...  WHERE condition;  Important Considerations: o Understand the implications of modifying records. o Ensure the WHERE clause is correctly specified to affect only desired records.  Example of UPDATE Command Usage: o Updating salaries: o UPDATE Teacher SET T_Salary = 15000 WHERE T_Salary < 10000; Activity 3.10: Updating Records  Records to Update: 17  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o Student Table: Change name to Halima Mohammed for student_id: 1001/2013. o Teacher Table: Change specialization to Economics for teacher_id: 2010/2010.  Updating Multiple Rows: o The UPDATE command can modify multiple records simultaneously based on the WHERE clause. o Example: o UPDATE Teacher SET T_Salary = 20000 WHERE T_Sex = 'female'; o Ignoring the WHERE clause updates all teacher salaries: o UPDATE Teacher SET T_Salary = 15000; This structured approach ensures clarity when inserting and updating records in the database management system as outlined in the activities. DELETE Command Overview o Used to delete records from a database. o Does not remove the table structure; only deletes the data. o Allows deletion of records based on conditions or removal of all records. o Cannot delete specific fields within a record.  General Syntax o Format: DELETE FROM table_name WHERE condition;  Caution with Relationships o Important to be cautious when deleting records from tables in a one-to-many relationship. o Example: Deleting a department (one side) related to other tables (many sides) requires:  Deleting all related records in Students, Teacher, and Course tables before deleting the department. o Deleting a table where the primary key acts as a foreign key does not impact the parent table. o Can delete from Students, Course, and Teacher tables if primary key records are not further related.  Example Scenario o If a teacher named Zemikael no longer works at the school:  If assigned to a course, the course should be transferred to another teacher using the UPDATE statement.  If not assigned to any course, the record for Zemikael can be deleted. o SQL Command Example:  DELETE FROM Teacher WHERE T_IDNO = 'Teach/2312/91';  Key Considerations o When using the DELETE statement, always refer to the record by its primary key to avoid unintentional deletion. o Other fields may have similar values, which could lead to data loss. 18  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  Deleting Multiple Rows o Conditional clause in DELETE is optional but highly recommended. o Not using a condition results in deletion of all records, which is rarely valid. o To delete all records from a table, use one of the following commands:  DELETE FROM table_name;  DELETE * FROM table_name;  Activity Examples o Write SQL command to delete a record from the Student table where student_id is 1010/2013. o Write SQL statement to delete all records in the Grade table. 3.2.4 Data Query Language – SELECT Command  Data Query Language (DQL): A language for querying relational Database Management Systems (DBMS).  SELECT Command: o Provides functionality to query all or a subset of records. o Can query from one or more tables in a database. o Offers options for filtering and obtaining meaningful results. o Flexibility of SQL is a key reason for its success in relational DBMSs. Purpose of SELECT Command  Used to write SQL statements that describe the desired data from a database.  Includes the following considerations: o Data Type: What type of data does a table contain? o Data Relationships: How are data from different sources related? o Data Fields/Calculations: Which fields or calculations will produce the data? o Criteria: What criteria must be met for data to be included? o Sorting: How to sort the results, if applicable. SQL Statement Structure  SQL statements are made up of clauses, each serving a specific function.  Some clauses are mandatory in a SELECT statement. Common SQL Clauses SQL Clause Function Required SELECT Lists fields containing data of interest. Yes FROM Lists tables containing the fields listed in SELECT. Yes WHERE Specifies criteria that each record must meet. Optional ORDER BY Specifies how to sort the results. Optional Basic SQL Clauses: SELECT, FROM, WHERE  Focus on queries executed on a single table.  Multiple tables can be queried using a single statement.  Syntax to Retrieve All Records: o SELECT * FROM table_name; o Reads as: “Select all records from a table.” o Asterisk ()*: Represents all columns in the table. 19  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o The asterisk can be replaced by listing all fields explicitly. Example of SELECT Statement  To select all records from the COURSE table: o SELECT * FROM COURSE;  This command retrieves all records from the specified table. Alternative SELECT Statement  Listing all fields explicitly, equivalent to using an asterisk: o SELECT CONTACT_HR, C_NAME, C_DESCRIPTION, C_DEPARTMENT, C_CODE FROM COURSE;  The output from both statements is the same. Best Practices  When a table contains many fields, using * to specify all columns is efficient.  Ensure field names in the SELECT statement match exactly with those in the database schema. Additional Notes  It is important to understand the structure and relationships of the data within the database to effectively utilize the SELECT command.  Mastery of the SQL SELECT command is fundamental for anyone working with databases, as it forms the core of data retrieval operations. Selecting Columns in SQL o It is often unnecessary to select all fields from a table; a limited set of columns may be preferred. o This can be achieved by explicitly naming the fields in a comma-separated list.  Basic SQL SELECT Syntax o The general form of a SELECT SQL statement:  SELECT field1, [field2]  FROM table  [WHERE criterion]; o [WHERE criterion]: Optional clause for filtering output based on specified conditions.  WHERE Clause o The WHERE keyword is followed by an expression that evaluates to TRUE, FALSE, or UNKNOWN. o Expressions can utilize:  Mathematical Comparison Operators:  =, >, >=, =: For values greater than or equal to the comparison value.  = '15-03-2020';  Summary o Selecting a limited number of columns improves the relevance and clarity of SQL query outputs. o The use of the WHERE clause enhances data filtering capabilities, allowing for precise data retrieval based on specified criteria. o Understanding comparison operators and their application to DATETIME fields is crucial for effective data management in SQL. Sales Selection Criteria: 21  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o Select sales made after May 01, 2020 with an amount greater than 12000. o Refer to Figure 3.20 for SQL SELECT statement examples and output based on the SALES table in Figure 3.19.  SQL Query Example: o Query: o SELECT CustomerId, Sales-Date, Amount o FROM SALES o WHERE SalesDate >= '01-05-2020' AND Amount > 12000; o Result: Only one record meets the criteria of a sales date on or later than May 01, 2020, with a sales amount greater than 12000.  Additional SQL Queries: o Query for sales within a date range: o SELECT CustomerId, Sales-Date o FROM SALES o WHERE SalesDate BETWEEN '01-01-2020' AND '30-02-2020'; o Query for sales amounts outside a specified range: o SELECT CustomerId, Sales-Date, Amount o FROM SALES o WHERE Amount < 1000 OR Amount > 15000; o Result: Includes all sales amounts less than 1000 or greater than 15000. There are four rows that meet these criteria based on the SALES table.  Understanding the BETWEEN…AND Condition: o The BETWEEN…AND clause is utilized to set a range condition. o It tests the specified value (e.g., SalesDate) against a range of values defined by two endpoints.  Start point: ‘01-01-2020’  End point: ‘30-02-2020’ o Both endpoints are included in the range. o A record is included in the output if the specified value (e.g., SalesDate) falls within this range.  Activity 3.14: o Tasks involving SQL queries on Student and Teacher tables from Activity 3.10: 1. Select all students with marks above 50. (Hint: marks > 50) 2. Select all female teachers. (Hint: sex = 'F') 3. Select names of students in grade 12. (Hint: grade = 12)  SELECT Command with ORDER BY Clause: o General Syntax: o SELECT column1, [column2], … 22  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o FROM table_name o ORDER BY [column] [ASC|DESC] o The ORDER BY clause allows ordering records of a table based on specific field categories.  Ordering can be alphabetical or numerical.  The ORDER BY keyword is followed by the desired field name ([column]) to order records.  ASC indicates ascending order, while DESC indicates descending order.  Ascending order is the default if no specification is provided.  Example of Using ORDER BY: o A query to retrieve teacher names and salaries in ascending order: o SELECT FullName, Salary o FROM Teachers o ORDER BY Salary ASC; o In this example, the salaries are sorted in ascending order.  Key Concepts: o SQL SELECT Statement: Used to query data from a database. o WHERE Clause: Filters records based on specified conditions. o BETWEEN…AND: Used to filter records within a specific range. o ORDER BY Clause: Used to sort the result set by one or more columns.  Important Vocabulary: o CustomerId: Identifier for customers in the sales table. o SalesDate: Date when the sale was made. o Amount: Total sales amount for each transaction. o ASC: Ascending order. o DESC: Descending order. o Range Condition: A condition that specifies a range of values.  Conclusion: o Understanding these SQL commands and structures is essential for effectively managing and querying databases. The ability to filter and sort data allows for efficient data analysis and reporting. SELECT Statement: Used to query data from a database. o Syntax: SELECT column1, column2 FROM table;  Example Query: o SELECT T_First_Name, T_Middle_Name, T_Sex, T_Salary FROM TEACHER ORDER BY T_Salary DESC;  Retrieves teachers’ first name, middle name, sex, and salary.  Orders results by T_Salary in descending order.  Database Table Structure: o Order of records and columns within a database table is irrelevant. 23  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa o The ORDER BY clause allows for sorting output results:  ASC: Ascending order (default)  DESC: Descending order  Activity Examples: 1. Select teachers specialized in Mathematics and order by their names:  SELECT * FROM TEACHER WHERE Specialization='Mathematics' ORDER BY T_First_Name; 2. Select all records in the Teacher table and order by sex:  SELECT * FROM TEACHER ORDER BY T_Sex; 3. Patient Data Management:  Select patients and order by Temperature in descending order:  SELECT * FROM PATIENT ORDER BY Temperature DESC;  Select patients and order by VisitDate in ascending order: SELECT * FROM PATIENT ORDER BY VisitDate ASC;  Patient Data Example: o Sample Patient Table:  PatientId | VisitDate | Physician | Temperature | BodyPressure  89101 | 10-06-2021 | Amare | 38 | 120/70  89009 | 13-02-2022 | Negesti | 37.5 | 120/70  89203 | 15-02-2021 | Murad | 39 | 150/60  90555 | 04-10-2021 | Azeb | 38.5 | 130/80  89777 | 29-01-2022 | Chala | 39 | 160/70  Selecting Records from Two Tables: o SELECT Command: Can retrieve records from two tables linked by a common field. o General Syntax:  SELECT table1.column1, [table1.column2], table2.column1, [table2.column2] FROM table1, table2 WHERE table1.column1 = table2.column2;  Key Points for Selecting from Two Tables: o Specify column names with their respective tables to avoid naming conflicts. o Example: table1.column1 refers to column1 in table1.  Example Scenario: o School director requests courses offered in the first semester. o Course details are in COURSE table; semester offerings are in CourseOffering table. o Course_id serves as a primary key in COURSE and a foreign key in CourseOffering.  Query to Fulfill Director’s Request: o SELECT COURSE.Course_id, COURSE.Course_name, COURSE.Grade, CourseOffering.Semester FROM COURSE, CourseOffering WHERE COURSE.Course_id = CourseOffering.Course_code; 24  Grade 12th IT Short notes Prepared by Teacher Motuma Dugasa  COURSE Table Example: o Course_id | Course_name | Grade | Contact_Hr NS103 | Physics | 11 | 4 NS104 | Biology | 11 | 4 NS102 | Biology | 10 | 4 NS105 | English | 11 | 5  Conclusion: o Understanding the SELECT statement, ORDER BY clause, and how to link tables is crucial for effective database management. o Proper usage of SQL commands can streamline data retrieval and improve data handling processes within databases. 25