Introduction to SQL and Database Management

• SQL is a fundamental skill for data management, software development, and business analysis.
• SQL provides a standardized way to communicate with databases for data-driven decisions.
• SQL is a language, while MySQL is a system.
• SQL is used to retrieve, manipulate, and analyze data in relational databases.

Most Popular Databases (2024 Stack Overflow Survey)

• PostgreSQL: 41.55%
• MySQL: 41.09%
• SQLite: 30.9%
• MongoDB: 25.52%
• Microsoft SQL Server: 25.45%
• Redis: 20.41%
• MariaDB: 17.61%
• Elasticsearch: 13.39%
• Oracle: 9.8%
• DynamoDB: 8.87%

Library Management ER Diagram Issues

• One-to-many relation between students and borrowed books.
• Space required for upgrading students to members (Granter).
• Need for unique IDs for identifying each book.
• Unique IDs required for identifying all enrolled members/students.
• Due date tracking for issued books requires an ER Diagram addition.

Creating an ERD for a Library System

• Step 1: Identify entity sets: Member, Book, Granter (Library staff), Section, Publisher.
• Step 2: Associate attributes:
  • Member: Member ID, Name, Birthday, Address, Age, Contact_no.
  • Book: ISBN, Title, Author, Price, Category.
  • Granter: Name, NIC, Phone Number, Address, Post.
  • Section: SID, Name.
  • Publisher: PID, Name, Address, Phone.
• Step 3: Find key attributes, using a primary key constraint:
  • Member: Member ID.
  • Book: ISBN.
  • Granter: NIC.
  • Section: SID.
  • Publisher: PID.
• Step 4: Identify relationships between entities to analyze the database clearly.

Data Integrity

• Data Integrity refers to overall completeness, accuracy, and consistency.
• Data Integrity is indicated by the absence of alteration

How to Maintain Data Integrity

• Normalization should be an integral part of the design process.
• Ensure that proposed entities meet required normal form before table structures are created.
• Existing databases have been improperly designed or burdened with anomalies if improperly modified during course of time.
• Redesign and modify existing databases
• Maintain data integrity when performing data insertion and updates.
• Use integrity constraints to guard against accidental database damage.

Types of Integrity Constraints: Domain Constraints

• Domain Constraints define attribute value sets (e.g., string, integer, date).
• Attribute values must be available in the corresponding domain.

Entity Integrity Constraints

• Primary key values cannot be null.
• Primary keys are used to identify individual rows.
• Tables can contain null values other than the primary key.

Referential Integrity Constraints

• Enforces relationships between tables, with foreign keys in one table referencing primary keys in another.
• Foreign key values must be null or present in the referenced primary key table.

Key Constraints

• Key Constraints are used to uniquely identify an entity within its entity set.
• An entity set can have multiple keys (one primary).
• Primary key can contain a unique and null value in the relational table.

MySQL

• MySQL is widely used, open-source relational database management system (RDBMS).
• MySQL is known for reliability, performance, and ease of use.
• MySQL follows the relational model, storing data in tables.
• Relationships are maintained using primary and foreign keys.
• MySQL utilizes SQL to perform database operations.
• MySQL follows a client-server architecture.

MySQL Database Components

• Database: Collection of related tables.
• Table: Collection of related data in rows and columns.
• Row: Represents a single record.
• Column: Defines an attribute and its data type.
• Primary Key: Uniquely identifies each record, cannot contain nulls, and is unique for every row.
• Foreign Key: Links tables by referencing the primary key in another table, enforcing referential integrity.

Interacting with MySQL Using SQL

• Data Definition Language (DDL): Defines and modifies database structure and objects.
  • CREATE: Creates a new database or table.
  • ALTER: Modifies an existing table (e.g., adding a column).
  • DROP: Deletes a table or database.
• Data Manipulation Language (DML): Manipulates data within tables.
  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing records.
  • DELETE: Removes records from a table.
• Data Query Language (DQL): Retrieves data from the database.
  • SELECT: Retrieves data from one or more tables.
• Data Control Language (DCL): Controls database access.
  • GRANT: Grants specific privileges to users.
  • REVOKE: Revokes previously granted privileges.

Key Features of MySQL

• Storage Engines: InnoDB (default, supports transactions) and MyISAM (fast read operations, no transactions).
• Transactions: Ensure a group of SQL statements executed as a single work unit, following ACID properties.
• Indexes: Speed up data retrieval.
• Replication: Copies data from master to slave servers improving data availability.

Common Use Cases of MySQL

• Web Applications:Backend database for dynamic websites.
• E-Commerce Systems:Manages customer, product, and order data.
• Data Warehousing: Used for analytical applications and reporting.
• Content Management Systems (CMS): Used by many CMSs.

MySQL Data Types

• Numeric
• Date and Time
• String

MySQL Data Types - Numeric

• Integer Types:
  • TINYINT: -128 to 127 (signed) or 0 to 255 (unsigned).
  • SMALLINT: -32,768 to 32,767 (signed) or 0 to 65,535 (unsigned).
  • MEDIUMINT: -8,388,608 to 8,388,607 (signed) or 0 to 16,777,215 (unsigned).
  • INT (INTEGER): -2,147,483,648 to 2,147,483,647 (signed) or 0 to 4,294,967,295 (unsigned).
  • BIGINT: - -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed) or 0 to 18,446,744,073,709,551,615 (unsigned).
• Floating-Point and Fixed-Point Types
  • FLOAT: Single-precision for approximate numbers.
  • DOUBLE: Double-precision for greater precision.
  • DECIMAL (NUMERIC): Fixed-point with specified digits before and after the decimal.

MySQL Data Types - Date and Time

• DATE: YYYY-MM-DD ('1000-01-01' to '9999-12-31').
• DATETIME: YYYY-MM-DD HH:MM:SS ('1000-01-01 00:00:00' to '9999-12-31 23:59:59').
• TIMESTAMP: YYYY-MM-DD HH:MM:SS (seconds since '1970-01-01 00:00:01' UTC), affected by time zones.
• TIME: HH:MM:SS ('-838:59:59' to '838:59:59').
• YEAR: Two-digit (YY) or four-digit (YYYY) format (1901 to 2155).

MySQL Data Types - String

• CHAR: Fixed-length string (max 255 characters), padded with spaces if necessary.
• VARCHAR: Variable-length string (up to 65,535 characters), uses only required space.
• TEXT:
  • TINYTEXT: Up to 255 bytes.
  • TEXT: Up to 65,535 bytes.
  • MEDIUMTEXT: Up to 16,777,215 bytes.
  • LONGTEXT: Up to 4,294,967,295 bytes.
• BLOB (Binary Large Object):
  • TINYBLOB: Up to 255 bytes.
  • BLOB: Up to 65,535 bytes.
  • MEDIUMBLOB: Up to 16,777,215 bytes.
  • LONGBLOB: Up to 4,294,967,295 bytes.

Choosing Data Types - Best Practices

• Choose the smallest data type possible.
• Use fixed-length types for fixed data.
• Consider the range of values.
• Be mindful of timezone requirements.
• Avoid over-indexing on large text fields.

MSQL Basic Commands

• Creating a Table: collection of data organized in rows and columns, defining specific data types.
• Modifying an Existing Table: ALTER TABLE to change the structure (ADD, MODIFY, RENAME, DROP COLUMN).
• Renaming a Table: RENAME TABLE.
• Dropping a Table: DROP TABLE (deletes the table and its data).
• Copying a Table:
  • Structure only: CREATE TABLE new_tbl LIKE existing_tbl.
  • With data: CREATE TABLE new_tbl LIKE existing_tbl; INSERT INTO new_tbl SELECT * FROM existing_tbl.

Adding and Dropping Constraints

• Constraints enforce data integrity.
• Adding a Primary Key: ALTER TABLE tbl_name; ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
• Dropping a Constraint: ALTER TABLE tbl_name; DROP CONSTRAINT constraint_name;

MySQL Relational Tables

• Relational Tables form the backbone of a relational database.
• Designing effectively ensures that data is organized, stored, and retrieved efficiently.
• Use of integrity constraints and referential constraints which enforce rules that help prevent data anomalies.

Designing Integrity and Referential Constraints

• Enforce data accuracy.
• Preserve relationships.
• Prevent data anomalies.
• Support data integrity.

Types of Integrity Constraints

• Primary Key Constraint: uniquely identifies each record in a table.
• Unique Constraint: ensures that all values in a specific column are unique across the table.
• Not Null Constraint: ensures that a column cannot contain null values
• Check Constraint: ensures that all values entered into a column meet specific criteria and conditions.
• Default Constraint: provides a default value for a column
• Foreign Key Constraint: enforces a link between two tables

Referential Constraints

• Foreign Key Constraint Ensures that a value in one table corresponds to a valid value in another table

Referential Integrity Actions

• ON DELETE CASCADE
• ON DELETE SET NULL
• ON UPDATE CASCADE

Referential Integrity Rules

• Insert Rule
• Update Rule
• Delete Rule

Designing Relational tables

• Choose Meaningful Primary Keys: Unique, meaningful, and surrogate keys if no natural key.
• Enforce Relationships with Foreign Keys: Maintain relationships with cascading actions.
• Apply Constraints for Business Rules: CHECK and NOT NULL for required conditions.
• Normalize the Schema First, for data integrity then Denormalize if necessary.
• Document Constraints and Schema Design to help maintain the database and allow future developers to understand the design.

ON DELETE options

• CASCADE: automatically deletes in child table
• SET NULL: sets the foreign key column in the child table to NULL
• RESTRICT: prevents deletion
• NO ACTION: similar to RESTRICT, but differs the enforcement of referential integrity
• SET DEFAULT: sets the foreign key column to a default value (rarely used in MySQL)

MSQL Operators

Operators: Special symbols or keywords.

Types of MySQL Operators

• Arithmetic Operators: Perform math calculations.
• Comparison Operators: Compare two values.
• Logical Operators: Combines multiple conditions.
• Assignment Operators: Assign values to variables.

SELECT Statement

• Used for fundamental commonly used for SQL commands in MySQL
  • SELECT specifies the columns to retrieve.
  • FROM specifies the tables from which to retrieve data.
  • WHERE filters row based on the condition
  • ORDER BY sorts the result set by one or more columns
• Using Aliases:
  • Used to give a column or a table a temporary name
  • AS keyword
• Limiting Results: the LIMIT clause restricts the number of rows returned by the query
• Using Aggregate Functions: performs calculations on a set of values (COUNT(), SUM(), AVG(), MIN(), MAX()
• Grouping Results - the GROUP BY clause groups rows that have the same values in specified columns and allows you to use aggregate functions on those groups
• Filtering Grouped Data - HAVING clause filters the results of a GROUP BY operation
• Using Joins in SELECT Queries used to combine rows from two or more tables based on a related column between them
  • Inner Join returns rows from both tables that match the specified condition
  • Left Join returns all rows from the left table and the matching rows from the right table.

MSQL CRUD Operation

• CRUD operations Create, Read, Update, and Delete are the four fundamental operations for managing data in database -Create adds new records to a table -Read SELECT statement is used to query data from a table -Update modify existing records in a table -Delete remove records from a table

Best Practices for CRUD Operations in MySQL

• Best Practices allow you to perform more complex data retrieval operations beyond simply selecting records from a table
• Most commonly used clauses include WHERE, ORDER BY, GROUP BY, and HAVING

Best Practices for Using WHERE, ORDER BY, GROUP BY, and HAVING

1. Use WHerE to filter rows before grouping.
2. Optimize with indexes.
3. Use GROUP BY for aggregation.
4. Combine clauses for complex queries.

• Use WHERE clause carefully.
• Backup data.
• Use transactions for safety.
• Use parameterized queries.
• Limit data when reading.

MSQL Clause

• Clauses: keywords used to filter, sort, group, and manage data in SQL queries.