D426 Study Sheet Summary

D426 Study Sheet Summary

The D426 OA is ALL multiple choice and heavily definition based. Understanding the Zybooks material will help set you up for the D427 course which will require you to write SQL code. A database application is software that helps business users interact with database systems.

Database Roles

1. Database Administrator

• Responsibility: Securing the database system against unauthorized users.

• Functions: Enforces procedures for user access and database system availability.

2. Authorization

• Many database users should have limited access to specific tables, columns, or rows of a database. Database systems authorize individual users to access specific data.

3. Rules

• Database systems ensure data is consistent with structural and business rules.
    - Example: When multiple copies of data are stored in different locations, copies must be synchronized as data is updated.
    - Example: When a course number appears in a student registration record, the course must exist in the course catalog.

4. Query Processor

• Function: Interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

• Optimization: The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

5. Storage Manager

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

• Importance of Indexes: Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

6. Transaction Manager

• Function: Ensures transactions are properly executed.

• Conflict Prevention: Prevents conflicts between concurrent transactions.

• Restoration: Restores the database to a consistent state in the event of a transaction or system failure.

SQL CREATE TABLE Statement

The CREATE TABLE statement creates a new table by specifying the table and column names. Each column is assigned a data type that indicates the format of column values. Data types can be:

• INT: Stores integer values.

• DECIMAL: Stores fractional numeric values.

• VARCHAR: Stores textual values (single quotes around data you INSERT into a table).

• CHAR: Stores textual values (single quotes around data you INSERT into a table).

• DATE: Stores year, month, and day (single quotes around data you INSERT into a table).

Database Requirements Analysis Phase

The analysis phase specifies database requirements without regard to a specific database system. Requirements are represented as:

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

• Relationships: A link between entities.

• Attributes: A descriptive property of an entity.

Design Phases

STEP 1: Conceptual Design

• Alternative Names: Analysis, data modeling, requirements definition.

• Specifies database requirements without regard to a specific database system.

STEP 2: Logical Design

• Function: Implements database requirements in a specific database system.

• For relational database systems, converts entities, relationships, and attributes into tables, keys, and columns.

STEP 3: Physical Design

• Function: Adds indexes and specifies how tables are organized on storage media.

• Impact on Query Processing Speed: Physical design affects query processing speed but never affects the query result.

• Data Independence Principle: States that physical design never affects query results.

Application Programming Interface (API)

To simplify the use of SQL with a general-purpose language, database programs typically use an Application Programming Interface (API).

Data Structure in Databases

• Tuple: An ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different due to order.

• Table: A data structure with:
    - A name.
    - A fixed tuple of columns.
    - A varying set of rows.

• Row: An unnamed tuple of values corresponding to columns belonging to the column's data type.

• Cell: A single column of a single row – a data point.

Synonyms in Database Terminology

• Table: File, Relation.

• Row: Record, Tuple.

• Column: Field, Attribute.

A table must have at least one column but any number of rows. A table without rows is referred to as an empty table.

Data Independence in Databases

Data independence allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.

SQL Statements for Table Manipulation

CREATE TABLE Statement

• Function: Creates a new table, specifying the table name, column names, and column data types. Example data types include:
    - INT or INTEGER: For integer values.
    - VARCHAR(N): For textual values with 0 to N characters.
    - DATE: For date values.
    - DECIMAL(M, D): For numeric values with M digits, of which D digits follow the decimal point.

DROP TABLE Statement

• Function: Deletes a table, along with all its rows, from the database.

DROP VIEW Statement

• Function: Deletes a virtual view from a database.

ALTER TABLE Statement

• Function: Modifies an existing table by adding, deleting, changing, or modifying columns.

Business Rules

• Based on business policy, specific to a particular database.

Types of SQL Components

SQL Commands / Clauses Overview

Queries and Joins

• SELECT: Select data from database.

• FROM: Specify table we're pulling from.

• WHERE / LIMIT / IN / LIKE: Filter query to match a condition, limit rows returned, or specify multiple values for filtering.

• JOIN Clauses: Combine rows from two or more tables.
    - INNER JOIN: Combines only matching pairs.
    - LEFT JOIN: Combines all left rows with matching right rows.
    - RIGHT JOIN: Combines all right rows with matching left rows.
    - FULL OUTER JOIN: Combines all rows regardless of match.

Data Manipulation Language (DML)

• UPDATE: Updates existing rows in a table, using the SET clause to specify new values.

• DELETE: Deletes specified rows from a table.

• INSERT: Inserts new rows into a table.

Aggregate Functions

• COUNT(): Counts the number of rows in the selected dataset.

• SUM(): Sums all the values in a specified column.

• AVG(): Calculates the average of values in a column.

• MIN(): Finds the minimum value in a specified column.

• MAX(): Finds the maximum value in a specified column.

HAVING Clause

• Used with GROUP BY clause to filter grouped results. The optional HAVING clause follows the GROUP BY clause and precedes the ORDER BY clause.

Entity-Relationship (ER) Models

An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. It includes:

• Entities: Represented as rectangles, they denote people, places, products, concepts, or activities.

• Relationships: Statements about two entities which may either be different or the same, known as reflexive relationships.

• Attributes: Descriptive properties of entities.

Relationship Types

• One-to-One (1:1): One entity relates to one other entity.

• One-to-Many (1:M): One entity relates to many entities.

• Many-to-Many (M:M): Many entities relate to many other entities.

Normalization and Normal Forms

Normalization eliminates redundancy by structuring databases more efficiently:

• First Normal Form (1NF): Each table has a primary key and entries in the table are atomic.

• Second Normal Form (2NF): Meets 1NF criteria, with non-key columns not depending only on part of a composite key.

• Third Normal Form (3NF): All non-key columns depend on the primary key, eliminating transitive dependencies.

• Boyce-Codd Normal Form (BCNF): Every determinant is a candidate key. This form handles all redundancy effectively.

Conclusion

By thoroughly understanding the definitions, structures, rules, and types of database relationships, a student can gain the expertise needed for database applications, including potential future courses that delve into SQL coding and management.