D426

Database Applications and Roles

• A database application is software that assists business users in interacting with database systems.

Database Roles

• Different roles individuals may have in relation to databases include:

  • Database Administrator (DBA):

  • Secures the database system against unauthorized users.

  • Enforces procedures for user access and database system availability.

  • Authorization:

  • Many database users have limited access to specific tables, columns, or rows.

  • Authorize each user to access specific data.

  • Rules:

  • Ensure data aligns with structural and business rules.

  • Query Processor:

  • Interprets queries and creates execution plans to modify or retrieve data.

  • Returns the results of queries to applications.

  • Performs query optimization for efficiency in data instruction execution.

  • Storage Manager: How data is physically stored and retrieved

  • Translates query processor instructions into low-level file system commands that modify or retrieve data.

  • Uses indexes for quick data location as database sizes can range from megabytes to terabytes.

  • Transaction Manager: How operations stay correct and safe; Ensures that all database transactions are processed reliably and adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability). It maintains the integrity of the database by managing concurrent access and ensuring that transactions are completed successfully or rolled back in case of failure.

  • Ensures proper execution of transactions and prevents conflicts between concurrent transactions.

  • Restores the database to a consistent state after a transaction or system failure.

MongoDB

• MongoDB is a NoSQL database and is Open Source.

Basic SQL Operations

• Insert statement: INSERT adds rows to a table. (EX. INSERT INTO users (name, email) VALUES (‘Alice’, ‘alice@email.com’);

• Select statement: SELECT Read or retrieve data from a table. (EX. SELECT * FROM users;)

• Update statement: UPDATE modifies existing data in a table. (EX. UPDATE users SET email = ‘alice.new@email.com WHERE name = ‘Alice’;)

• Delete statement: DELETE removes rows from a table. (EX DELETE FROM users WHERE name = ‘Alice’;)

CREATE TABLE Statement

• Used to create a new table with specified names and data types for columns.

• Data types can be:

  • INT: Stores integer values. (Whole numbers) ... Age, Quantity, ID numbers

  • DECIMAL: Stores fractional numeric values. (Precise values) ... Prices, Balances etc.

  • VARCHAR: Stores variable-length textual values. Names, emails etc.

  • DATE: Stores values representing year, month, and day.

Database Design Phases

Phases

1. Analysis/Conceptual design:

   • Specifies database requirements - Represented using entities, relationships, and attributes:

     • Entity: A person, place, activity, or thing.

     • Relationship: A link between entities.

     • Attribute: A descriptive property of an entity.

2. Logical Design: How the database is structured in a DBMS

• Implements database requirements within a specific database system.

• Converts entities, relationships, and attributes into tables, keys, and columns.

3. Physical Design: How data is stored

• Adds indexes and specifies how tables are organized in storage.

• Affects query processing speed but does not affect query results, known as data independence.

Application Programming Interface (API): How applications interact with the database

MySQL Command-Line Client

• Text-based interface lets you type and run SQL commands directly

• It will Return error codes and descriptions for syntactically incorrect SQL statements or execution failures.

Data Structure in Databases

Tables

• Table consists of:

  • A name

  • A fixed tuple (Row in a table) of columns

  • A varying set of rows

• Columns:

  • Have specific names and data types.

• Rows:

  • Are unnamed tuples of values corresponding to the columns' data types.

  • Rows have no inherent order.

Synonyms for Database Constructs

• Table: Also called File or Relation.

• Row: Also called Record or Tuple.

• Column: Also called Field or Attribute.

Business Rules

• Based on business policy and specific to a database.

Categories of Data Types

1. Literals: Explicit values that are string, numeric, or binary. (Exact what it is, right when its given)

   • Strings are enclosed in single’ or ‘double quotes’.

   • Binary values formatted as x'0', where 0 is a hex value.

2. Keywords: Defined words in SQL.

   • Examples: SELECT, FROM, WHERE.

3. Identifiers: Names of database objects like tables or columns.

   • Examples: City, Name, Population.

4. Comments: Statements meant for human readers, ignored during SQL parsing.

   • Single line: -- comment

   • Multi-line: /* comment */.

SQL Sublanguages

• SQL is divided into five sublanguages:

  • Data Definition Language (DDL): Defines structure.

  • Data Query Language (DQL): Retrieves data.

  • Data Manipulation Language (DML): Manipulates stored data.

  • Data Control Language (DCL): Manages access.

  • Data Transaction Language (DTL): Manages transactions.

Table Structure Details

• A table has:

  • At least one column and any number of rows.

  • An empty table contains no rows.

Data Independence

• Data independence allows modifications to improve query performance without altering results.

Table Commands

CREATE TABLE Command

• Syntax involves specifying the table name, column names, and their data types.

• Data type examples:

  • INT or INTEGER: integer values.

  • VARCHAR(N): variable-length strings up to N characters.

  • DATE: date values.

  • DECIMAL(M, D): numeric with M digits, D following the decimal point.

DROP TABLE Command

• Deletes a table and all associated rows from the database.

ALTER TABLE Command

• Adds, deletes, or modifies columns in an existing table.

Data Type Categories

Integer Types

• INTEGER Common Integer Types:

  • TINYINT: 1 byte, range -128 to 127.

  • SMALLINT: 2 bytes, range -32,768 to 32,767.

  • MEDIUMINT: 3 bytes, range -8,388,608 to 8,388,607.

  • INTEGER/INT: 4 bytes, range -2,147,483,648 to 2,147,483,647.

  • BIGINT: 8 bytes, range -2^{63} to 2^{63}-1.

Arithmetic Operators

Data Modification Commands

UPDATE Statement

• UPDATE modifies data in existing rows.

• SET new values are assigned

• WHERE Which rows to change/ specify rows.

DELETE Statement

• DELETE removes rows from a table.

• Requires the FROM (The table you’re deleting from) keyword followed by the table name and an optional WHERE (Which rows to remove) clause.

TRUNCATE Statement

• TRUNCATE deletes all rows in a table, similar to DELETE without a WHERE clause but with different underlying mechanics.

MERGE Statement

• MERGE selects data from a source table to insert into a target table.

Primary and Foreign Keys

Primary Keys

• A primary key identifies a row, typically the first column.

• Can be:

  • Simple Primary Key: single column.

  • Composite Primary Key: Multiple columns.

Auto-Increment Columns

• Automatically assigns incrementing values on new row insertion. Database automatically gives each new row a unique number.

Example: CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR (50)

);

INSERT INTO users (name) VALUES ('Alice');

INSERT INTO users (name) VALUES ('Bob');

-- The table now looks like:

-- id | name

-- 1 | Alice

-- 2 | Bob

Foreign Keys

• Foreign keys refer to primary keys and must have compatible data types. Explains how databases are linked together. Using this you can establish a link between two tables.

• Constraints ensure referential integrity, preventing violations on inserts, updates, and deletes. Ensuring referential integrity by making sure relationships between tables stay valid

Referential Integrity Actions

• Defined actions for maintaining referential integrity (Foreign Keys) include:

  • RESTRICT: Reject operations that violate constraints.

  • SET NULL: Sets invalid foreign keys to NULL.

  • SET DEFAULT: Sets invalid foreign keys to their default value.

  • CASCADE: Ensures that is if the primary key (Change/Delete) in the primary key it reflects in the related rows of the other table via foreign key.

Constraints

• Constraints govern allowable database values based on relational and business rules.

• Added and removed using the ALTER TABLE command followed by ADD, DROP, or CHANGE clauses.

• PRIMARY KEY – ensures each row is unique.

• FOREIGN KEY – ensures a value matches a value in another table.

• UNIQUE – ensures all values in a column are distinct.

• NOT NULL – ensures a column cannot have NULL values.

• CHECK – enforces a condition on values (e.g., age >= 18).

• DEFAULT – sets a default value if none is provided.

SQL Operators

BETWEEN Operator

• BETWEEN checks if a value lies between two others:

  • Syntax: value BETWEEN minValue AND maxValue, equivalent to value >= minValue AND value <= maxValue.

LIKE Operator

• LIKE matches text patterns using wildcards % = (any characters) and _ =(exactly one character).

Ordering and Aggregate Functions

ORDER BY Clause

• Orders query results by specified columns, defaulting to ascending order, or descending if accompanied by DESC.

Aggregate Functions

• Aggregate functions process multiple rows and return a summary value:

  • COUNT(): Counts rows in the result set.

  • MIN(): Finds the minimum value.

  • MAX(): Finds the maximum value.

  • SUM(): Sums numeric values.

  • AVG(): Computes the average value.

HAVING Clause

• Filters results after aggregation when used with GROUP BY.

SQL Joins

• A join combines data from two tables, utilizing comparisons between columns.

  • INNER JOIN: Only rows where there’s a math in both tables

  • FULL JOIN: Returns all rows in both tables, with NULLs for unmatched entries.

  • LEFT JOIN: Selects all left table rows, and matching right rows.

  • RIGHT JOIN: Selects all right table rows, and matching left rows.

Other Join Types

• Cross-Join: Combines all combinations of rows from two tables without conditions.

• Self-Join: comparing rows within the same table by separating the entities

• Subquery: A nested query executed within another SQL query. ( “Question within a question” )

• Alias: A temporary name assigned to columns or tables.

Entity-Relationship Models

Entities and Relationships

• Entity: Describes a person, place, product, concept, or activity.

• Relationship: A statement about entities how two or more entities are connected

• Attribute: Describes or the property of an entity

Entity-Relationship Diagram (ER Diagram)

• Visual representation of entities, relationships, attributes:

  • Entities are indicated by rectangles.

Types and Instances

• Types:

  • Entity Type: Set of similar entities (e.g., all employees).

  • Relationship Type: Set of relationships between entities (e.g., Employee manages Department).

• Instances:

  • Specific elements of types (e.g., individual employee).

Database Design Steps

Analysis Steps

1. Discover entities, relationships, and attributes.

2. Determine cardinality.

3. Distinguish strong and weak entities.

4. Create supertype and subtype entities.

Logical Design Steps

5. Implement entities.

6. Implement relationships.

7. Implement attributes.

8. Apply normal form.

Cardinality

• Defines the maximum and minimum instances of one entity relating to another.

Supertypes and Subtypes

• Subtype: A subset of another entity type (e.g., Managers as a subtype of Employees).

Normalization

Normal Forms

• Reduces redundancy and ensures data integrity.

• A table is in Third Normal Form if any non-key column's dependency on another column guarantees that the second column is unique.

• Boyce-Codd Normal Form: Ensures uniqueness of dependencies, ideal for applications with frequent updates.

Redundancy and Dependencies

• Trivial Dependencies: Simple dependencies that do not require normalization.

• Normalization: Process of structuring a database to reduce redundancy.

Table Structures

1. Heap Table: No order imposed, optimizing insert speed.

2. Sorted Table: Defined order based on a column.

3. Hash Table: Rows assigned to buckets.

4. Table Cluster: Interleaves rows of multiple tables in storage.

Database Operations

• Statement Descriptions:

  • CREATE INDEX - Creates an index on specified columns of a table.

  • CREATE TABLESPACE - Maps tables to one or more files based on logical design.