Data Manipulation ppt. for BSIT students

For Programming students

1. Data Manipulation April 16, 2024 Instr. John Oliver Lacap

2. Introduction to Database

3. What is a database? A structured collection of data that is organized in a way that allows for efficient storage, retrieval, and management of information. It is a computerized system that enables users to store, manage, and access data according to specific requirements. A database can contain various types of information, including text, images, videos, and numerical data.

4. Importance of Database Data Organization - Databases provide a structured and organized way to store, manage and retrieve data. This allows for efficient access and retrieval of the required data, while minimizing data redundancy and inconsistency. Data Integrity - Databases use various techniques, such as validation rules, constraints, and referential integrity, to ensure that the data stored in them is accurate and consistent. This reduces the risk of data errors and inconsistencies, which can lead to incorrect decisions and wasted time and resources.

5. Importance of Database Scalability - Databases can handle large amounts of data and can scale to support increasing amounts of data over time. This is especially important for businesses that need to store and manage large volumes of data, such as customer information, sales data, and financial transactions. Security - Databases provide various security features, such as user authentication, access control, and data encryption, to protect sensitive data from unauthorized access and ensure data privacy and confidentiality.

6. Importance of Database Efficiency - Databases provide optimized mechanisms for data retrieval and manipulation, which helps improve the efficiency of data processing and analysis. This can result in faster and more accurate decision-making and can improve overall business performance.

7. Relational Database A relational database is a type of database that organizes data into one or more tables with a predefined structure of columns and rows The relationships between the tables are defined through keys, such as primary keys and foreign keys, which allow data to be linked and accessed across multiple tables.

8. Relational Database Relational databases use Structured Query Language (SQL) to manipulate and retrieve data. SQL allows users to insert, update, delete, and query data within the database, and supports a wide range of operations and functions for data analysis and reporting.

9. Components of a Relational Database

10. Tables Structured sets of data that organize information into rows and columns. Each table represents a single entity, such as a customer or a product, and contains specific attributes or fields that describe that entity.

11. Columns Also known as fields, are the vertical components of a table that contain specific types of data. Each column is associated with a data type, such as text, number, or date, which defines the type of data that can be stored in that column.

12. Data Types INT - Used to store integer values (whole numbers) within a specified range. FLOAT - Used to store floating-point numbers (numbers with a decimal point). CHAR - Used to store fixed-length character strings (strings of characters with a fixed length). VARCHAR - Used to store variable-length character strings (strings of characters with a varying length). DATETIME - Used to store both date and time values.

13. Data Types BIT - Used to store Boolean values (true/false values). VARBINARY - Used to store variable-length binary data. TEXT - Used to store large amounts of text data. DECIMAL - Used to store decimal numbers with a fixed number of digits before and after the decimal point. MONEY - Used to store currency values.

14. Rows Rows - also known as records, are the horizontal components of a table that represent individual instances of an entity. Each row contains data that corresponds to the attributes or fields defined by the columns.

15. Primary and Foreign Keys Primary Keys - Unique identifiers that are used to uniquely identify each row in a table. They ensure that each record is unique and can be referenced and linked to other tables within the database. Foreign Keys - attributes in a table that are linked to the primary key of another table. They are used to establish relationships between tables, allowing data to be retrieved and analyzed across multiple tables.

16. Database Management System A software system that allows users to store, organize, and manage data in a database. It provides a way to manage the interactions between the database and the applications that use it, allowing users to create, modify, and retrieve data in a controlled and efficient manner.

17. Database Management System The main components of a DBMS include: Data Definition Language (DDL) - used to define the structure of the database and its components Data Manipulation Language (DML) - used to insert, update, and delete data in the database, and the query language, which is used to retrieve and analyze data. There are various types of DBMS available, including relational, NoSQL, object-oriented, and hierarchical DBMS. Each type has its own strengths and weaknesses and is suited for different use cases and applications.

18. Database Management System There are various types of DBMS available, including relational, NoSQL, object-oriented, and hierarchical DBMS. Each type has its own strengths and weaknesses, and is suited for different use cases and applications.

19. Structured Query Language (SQL) A programming language used to manage and manipulate relational databases. SQL allows users to interact with databases to create, modify, and retrieve data, as well as perform various other tasks related to database management and administration.

20. Structured Query Language (SQL) SQL syntax is straightforward and intuitive, with commands such as SELECT, INSERT, UPDATE, and DELETE used to perform common database operations. SQL also includes a range of functions and operators for data analysis and manipulation, as well as the ability to join tables together to retrieve data across multiple tables.

21. Basic SQL Syntax

22. CREATE TABLE Used to create a new table CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype );

23. SELECT Used to retrieve data from a table or multiple tables SELECT column1, column2, column3 FROM table_name;

24. WHERE Used to filter the data based on a condition SELECT column1, column2 FROM table_name WHERE column3 = 'value';

25. INSERT Used to insert data into a table. INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

26. UPDATE Used to update data in a table UPDATE table_name SET column1 = 'new_value1', column2 = 'new_value2' WHERE column3 = 'value';

27. DELETE Used to delete data from a table DELETE FROM table_name WHERE column1 = 'value';

28. GROUP BY Used to group the data based on a column SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

29. ORDER BY Used to order the data based on a column by ascending or descending SELECT column1, column2 FROM table_name ORDER BY column1;

30. Practice (20 minutes) From the SalesLT.Customer, query all customer that has suffix Jr. From the SalesLT.Customer, update the company name record of Donald Blanton to UST Angelicum College From the SalesLT.Customer, delete the record of Ms. Erin Hagens From the SalesLT.Customer, filter the data by the sales person adventure-works\jillian0

31. Creating CRUD Operations in Console Application (Create, Retrieve, Update and Delete)

32. app.config A configuration file that is used by .NET applications to store application-specific settings and parameters. It is an XML file that contains key-value pairs , where the keys are the names of the settings, and the values are the corresponding values for those settings.

33. ConfigurationManager It is used to read configuration settings from various configuration files such as app.config or web.config files in .NET applications. Using the ConfigurationManager class, developers can retrieve configuration settings such as connection strings, application settings, and custom configuration sections defined in the configuration file.

34. System.Data.SqlClient A namespace in the .NET Framework that provides a set of classes for working with Microsoft SQL Server databases. The System.Data.SqlClient namespace includes classes such as SqlConnection , SqlCommand , SqlDataReader , and SqlDataAdapter . The SqlConnection class is used to establish a connection to a SQL Server database, while the SqlCommand class is used to execute SQL commands on the database.

35. “using” statement It is used to declare a code block that defines a scope in which a certain resource, such as a file or a database connection, can be used. The using statement is a convenient way to ensure that the resource is properly and automatically disposed of when it is no longer needed, even if an exception is thrown.

36. SqlConnection.Open Opens a database connection with the property settings specified by the ConnectionString.

37. Object Relational Mapping (ORM) A programming technique that enables developers to work with relational databases using an object-oriented programming language, such as Java, C#, or Python. ORM tools provide a mapping layer between a database and an object-oriented program, allowing developers to interact with the database using objects, rather than writing SQL statements directly.

38. Object Relational Mapping (ORM) This helps simplify the process of interacting with the database, and allows developers to focus on the object-oriented design of their code, rather than the details of database interactions.

39. Dapper ORM Dapper is an open-source, lightweight, and high-performance object-relational mapping (ORM) framework for .NET applications. It was developed by Stack Overflow , and its primary goal is to provide a simple, flexible, and fast way to map database records to objects in your code. It focuses on providing a lightweight and fast alternative that can handle most common database operations without getting in the way.

40. Dapper Features High performance Dapper is built with performance in mind, and it aims to be one of the fastest ORM frameworks available for .NET. Easy-to-use Dapper's API is designed to be simple and straightforward, with minimal overhead and a low learning curve. Flexible mapping Dapper supports a variety of mapping options, including automatic mapping based on naming conventions, attribute-based mapping, and manual mapping via custom code.

41. Dapper Features Raw SQL support Dapper allows you to execute raw SQL queries directly, giving you full control over your database operations. Multiple database support Dapper works with a wide range of databases, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite, and more.

42. Repository Pattern A software design pattern that provides an abstraction layer between the application and the data access layer . It is a way to separate the code that accesses the database from the business logic of the application.

43. Repository Pattern The repository pattern involves creating a repository class that acts as a mediator between the application and the data source. The repository class provides a clean interface that the application code can use to retrieve, store, update, and delete data, without having to know the details of how the data is stored and accessed .