Database Concepts and SQL

Main Idea

A database is an organized collection of related data that can be stored, searched, updated, and managed efficiently. In the relational model, data is arranged in tables consisting of rows and columns, with relationships established through keys. SQL provides commands for defining database structures, retrieving information, inserting new records, modifying existing records, and deleting records, while data types and constraints support accuracy, consistency, and validity.

Key Concepts and Definitions

• Database: An organized collection of related data that can be accessed and managed efficiently.
• DBMS: Database Management System; software used to create, store, retrieve, update, and control databases.
• Relational Database: A database that represents data in tables and connects related tables through common fields.
• Table: A collection of related data arranged in rows and columns.
• Field or Column: A named attribute that describes one property of the data, such as Name, Age, or Salary.
• Record or Row: A complete set of related values describing one item or entity in a table.
• Value: The actual data stored at the intersection of a row and a column.
• Domain: The permitted set of values for a column, such as whole numbers for Age or valid dates for DateOfBirth.
• Primary Key: A column or combination of columns that uniquely identifies each row and cannot contain NULL values.
• Candidate Key: Any column or set of columns that can uniquely identify records and could be selected as the primary key.
• Alternate Key: A candidate key that is not selected as the primary key.
• Foreign Key: A column that refers to the primary key of another table and helps establish a relationship between tables.
• Composite Key: A key formed by combining two or more columns to uniquely identify a record.
• SQL: Structured Query Language, used to define, manipulate, and retrieve data in relational databases.
• DDL: Data Definition Language; commands such as CREATE, ALTER, and DROP that define or change database structures.
• DML: Data Manipulation Language; commands such as INSERT, UPDATE, and DELETE that change table data.
• DQL: Data Query Language; mainly represented by SELECT, which retrieves data from one or more tables.
• DCL: Data Control Language; commands such as GRANT and REVOKE that control access permissions.
• Data Type: The kind of data allowed in a column, such as INT, DECIMAL, CHAR, VARCHAR, DATE, or BOOLEAN.
• Constraint: A rule applied to a column or table to restrict invalid data and maintain integrity.
• NOT NULL: Ensures that a column must contain a value.
• UNIQUE: Ensures that all non-NULL values in a column or group of columns are different.
• DEFAULT: Automatically supplies a specified value when no value is provided.
• CHECK: Requires values to satisfy a stated condition, such as Marks >= 0.
• NULL: Represents a missing, unknown, or unavailable value; it is different from zero, an empty string, or a blank space.
• SELECT: Retrieves selected columns and rows from one or more tables.
• WHERE: Filters rows according to a condition.
• DISTINCT: Removes duplicate values from the query result.
• ORDER BY: Arranges query results in ascending or descending order.
• GROUP BY: Combines rows with the same values so that aggregate functions can be applied to each group.
• HAVING: Filters groups created by GROUP BY.
• Aggregate Functions: Functions that calculate a result from multiple rows, including COUNT, SUM, AVG, MIN, and MAX.
• INSERT: Adds new rows to a table.
• UPDATE: Changes existing values in selected rows.
• DELETE: Removes selected rows from a table.
• ALTER TABLE: Changes the structure of an existing table, such as adding, modifying, or dropping a column.
• DROP: Permanently removes a database object, such as a table or database.
• JOIN: Combines related rows from two or more tables using a matching condition.
• Referential Integrity: Ensures that a foreign-key value matches an existing referenced key or is NULL when allowed.

Supporting Arguments and Evidence

• A relational table consists of rows, also called records or tuples, and columns, also called fields or attributes. Well-designed tables reduce unnecessary duplication by storing each fact in an appropriate place.

• Keys provide the basis for identifying records and connecting tables. A primary key must uniquely identify every record and cannot contain NULL values. A foreign key creates a link between tables and helps prevent references to nonexistent records.

• Common SQL data types include INT for whole numbers, DECIMAL for precise numeric values, CHAR for fixed-length text, VARCHAR for variable-length text, DATE for dates, and BOOLEAN for true or false values.

• A database schema describes the structure of a database, including its tables, columns, data types, keys, and constraints. Constraints should prevent invalid entries rather than relying only on users or applications to check data. Data integrity means that data remains accurate, valid, consistent, and reliable.

• The basic table-creation syntax is:

   CREATE TABLE table_name (
       column1 datatype constraint,
       column2 datatype constraint
   );
   

• Data can be retrieved using:

   SELECT column_list
   FROM table_name
   WHERE condition
   ORDER BY column_name ASC or DESC;
   

To retrieve every column, use:

   SELECT * FROM table_name;
   

• Conditions can use the comparison operators =, <>, !=, <, >, <=, and >=, together with the logical operators AND, OR, and NOT. BETWEEN checks whether a value lies within an inclusive range, whereas IN checks whether a value matches one of a listed set of values.

• LIKE performs pattern matching: % represents any number of characters and _ represents exactly one character. IS NULL and IS NOT NULL must be used to test NULL values; expressions such as column = NULL are not correct.

• The DISTINCT keyword removes duplicate values. ORDER BY arranges results in ascending or descending order. GROUP BY combines rows with the same values, allowing aggregate functions to be applied to each group, while HAVING filters the resulting groups.

• COUNT(*) counts rows, whereas COUNT(column) counts non-NULL values in a column. SUM, AVG, MIN, and MAX operate on suitable numeric data. Grouping and aggregate functions support analysis such as finding totals, averages, minimums, maximums, and counts.

• The usual logical order of a query is FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY, although SQL is written with SELECT first. SQL keywords are generally written in uppercase for readability, but SQL is commonly not case-sensitive for keywords; text values may be case-sensitive depending on the database system. A semicolon is commonly used to mark the end of an SQL statement.

• New records are added using:

   INSERT INTO table_name (column1, column2)
   VALUES (value1, value2);
   

Existing records are modified using:

   UPDATE table_name
   SET column1 = value1
   WHERE condition;
   

Records are removed using:

   DELETE FROM table_name
   WHERE condition;
   

• A suitable WHERE condition should always be used with UPDATE and DELETE. An omitted or incorrect WHERE clause can change or remove every row unintentionally.

• ALTER TABLE changes the structure of an existing table, for example by adding, modifying, or dropping a column. DROP permanently removes a database object, such as a table or database.

• A JOIN combines related rows from two or more tables through a matching condition. Referential integrity ensures that a foreign-key value corresponds to an existing referenced key or is NULL when permitted.

• DDL, DML, and DQL separate major database operations: DDL defines structures, DML changes table data, and DQL retrieves data. DCL, including GRANT and REVOKE, controls access permissions. Database security also requires controlling user permissions and protecting sensitive information from unauthorized access.

• Primary keys and indexes can improve record identification and query performance, although excessive indexing may slow data modification. Transactions commonly follow atomicity, consistency, isolation, and durability, known together as ACID properties.

What to Remember

A relational database organizes related data into tables, using primary keys to identify records and foreign keys to connect tables. SQL provides distinct commands for defining structures, retrieving and grouping data, inserting, updating, and deleting records, while data types and constraints maintain integrity. For examinations, remember the SQL syntax and the need for precise conditions, particularly the safe use of WHERE with UPDATE and DELETE and the correct testing of NULL values.