CST-363 Week 1

This is the first week of CST-363, also known as Intro to Database Systems.

Questions

This week we have been asked to answer a few questions.

Relational database tables and spreadsheets look similar with both having rows and columns. What are some important differences between the two?

Databases have access to more features than simple spreadsheets, such as the ability to handle user permissions, handle conflicts between conflicting or invalid changes, and restore the database in case of a failure.

Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file. What are some important reasons that makes a database a useful investment of time?

While reading and writing to a file might be more simple, databases have access to a wide variety of features that simple file systems do not have.

What do you want to learn in this course that you think will be useful in your future career?

I am hoping to learn how databases work, how to design one, and how to integrate it into applications. Databases are used everywhere. Understanding them and being able to use them would be a boon in almost any tech job.

Database Basics

Data

Data is numeric, textual, visual, or audio information. Data can be used to help in a variety of tasks.

Data varies in a few key ways:

Scope: The amount of data can vary. For example, small businesses are likely to process less data than larger businesses.
Format: Data can be numbers, text, images, audio, or video. For example, your phone's camera captures video, while its microphone captures audio.
Access: Some data is private while other data may be public. For example, a hospital may be required to keep client data private, but a public company might have to make certain financial data public.

Databases

A database is a structured collection of data. In the past databases may have been stored on paper or clay tablets, but today they are largely stored on computers.

Databases have to handle large amounts of data and users, so they require advanced software tools:

Database System: Also known as a Database Management System, or DBMS, Database Systems are software that read and write data in a database.
Query: A request to retrieve, add, delete, or update data in a database.
Query Language: A programming language designed specifically for database systems.
Database Application: A software that helps users interact with database systems.

Database Roles

People interacting with databases can do so in a variety of ways:

Database Administrator: Determines who is authorized and unauthorized to access a database.
Database Designer: Designs the format of data and the structure of databases, while taking storage, response time, and more into account.
Database Programmer: Develops software utilizing databases by incorporating query languages and general-programming languages.
Database User: The user of a database, such as a business.

Database Systems

File Systems and Database Systems

The larger and more complex a database is, the more requirements it has:

Performance: Database performance and query response time suffers as more users and applications access a given database.
Authorization: Users should have a varying amount of access, with most only having access to specific tables, columns, or rows of a database.
Security: Access to data should be restricted, and data should be encrypted, to help protect against unauthorized access.
Rules: Data must follow and be consistent with certain structural and business rules.
Recovery: Database systems must be able to recover from failures without losing data.

Transactions

Transactions are groups of queries that must be completed completely or not at all, not partially. A partial execution of a transaction results in inconsistent or incorrect data.

When processing transactions, database systems must:

Ensure transactions are processed completely or not at all.
Prevent conflicts between concurrent transactions.
Ensure transaction results are never lost.

Architecture

Query Processor: Interprets queries, modifies queries to ensure efficiency, passes them to the storage manager, and returns query results to the application.

Storage Manager: Translates query instructions low-level file-system commands to modify or retrieve data. Uses indexes to locate data.

Transaction Manager: Ensures that transactions are properly executed, prevents conflicts between transactions, and restores the database after a system failure.

Log: A file containing a complete record of inserts, updates, and deletes performed by the database.

Catalog: Also known as a data directory, is a directory of tables, columns, indexes, and other database objects.

Products

Relational Database: Stores data in tables, columns, and rows, similar to a spreadsheet. Ideal for databases that require accurate data.

SQL: Standing for Structured Query Language, a query language that includes statements that read and write data, create and delete tables, and administer the database system. All relational database systems support SQL.

Big Data: Massive volumes of online data that are often poorly structured or missing information.

NoSQL: Non relational database systems optimized for big data.

Open Source: Software that anyone can inspect, copy, and modify with no liscensing fee.

Query Languages

Common Queries

There are four common queries: Insert, Select, Update, Delete. They are sometimes referred to as CRUD, or Create, Read, Update, and Delete, operations.

Insert: Inserts a new row into a table.
Select: Selects and returns all rows from a table that match specified conditions.
Update: Updates an existing row in a table.
Delete: Deletes a row from a table.

Creating Tables With SQL

The SQL CREATE TABLE statement creates a new table by specifying table and column names. Each column is assigned a data type.

Some common data types include:

INT: Stores integer values.
DECIMAL: Stores fractional numeric values. Includes the number of digits and how many are after the decimal point. Ex: DECIMAL(10,3)
VARCHAR: Stores textual values. Includes the number of characters. Ex: VARCHAR(10)
DATE: Stores year, month, and day.

Database Design and Programming

Database Design is a specification of database objects such as tables, columns data types, and indexes, as well as the process used to develop the specification.

Database design often has three phases:

Conceptual Design: Specifies database requirements without regard to a specific database system.
Logical Design: Implements database requirements in a specific database system.
Physical Design: Adds indexes and specifies how tables are organized on storage media. Affects query performance.

Relational Model

Database Models

A database model is a conceptual framework for database systems, and has three parts:

Data structures that prescribe how data is organized.
Operations that manipulate data structures.
Rules that govern valid data.

Relational Data Structure

Set: An unordered list of elements closed in braces. Ex. {a,b,c}

Tuple: An ordered list of elements closed in parentheses. Ex. (a,b,c)

Table: Has a name, a fixed tuple of columns, and a varying set of rows.

Column: Has a name and a data type.

Row: An unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

Data Type: A named set of values, from which column values are drawn.

Relational Rules

Relational Rules: Govern data in every relational database. Some relational rules include:

Unique Primary Key
Unique Column Names
No Duplicate Rows

Business Rules: Based on business policy and specific to a particular business.

Structured Query Language

Sublanguages

SQL statements are grouped into five sublanguages:

Data Definition Language (DDL): Defines database structure.
Data Query Language (DQL): Retrieves data.
Data Manipulation Language (DML): Inserts, updates, and deletes data.
Data Transaction Language (DTL): Manages transactions.
Data Control Language (DCL): Specifies user access to data.

Language Elements

SQL is built from language elements:

Literal: Explicit value such as a character string or number.
Keyword: Word with a special meaning for the language processor.
Identifier: Name of a database object, such as a column, table, or database.
Expression: Sequence of literals, identifiers, and operations that evaluate to a single value.
Comment: Text that is ignored by the language processor.

Syntax Definitions

UPPERCASE: Indicates SQL Keywords.

lowercase: Indicates an expression or identifier provided by the user.

Square Brackets []: Enclose an optional language element.

Curly Braces {}: Enclose a series of alternative language elements, separated by vertical bars.

Ellipses ...: Indicates that the preceding language element may be repeated.

Managing Databases

CREATE: Creates a new database.

DROP: Deletes a database.

USE: Selects a default database for use in subsequent statements.

SHOW: Provides information about databases, tables, and columns.

Tables

Tables, columns, and rows

Table: Has a name, a fixed sequence of columns, and a varying set of rows.

Column: Has a name and a data type.

Row: An unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type.

Cell: A single column of a single row.

Rules Governing Tables

Tables must obey the following rules:

Exactly one value per cell.
No duplicate column names.
No duplicate rows.
No row order.

Table Statements

CREATE TABLE: Creates a new table by specifying the table name, column names, and column data types.

DROP TABLE: Deletes a table, along with all of the table's rows, from a database.

ALTER TABLE: Adds, deletes, or modifies columns on an existing table. The clause after 'ALTER TABLE' determines what should be altered:

ADD: Adds a column.
CHANGE: Modifies a column.
DROP: Deletes a column.

Null Values

NULL: Value that represents either unknown or inapplicable data.

NOT NULL: Constraint in a CREATE TABLE statement that prevents a column from having a NULL value.

Primary Keys

Primary Key: A column, or group of columns, used to identify a row. A primary key cannot be NULL, and must be unique to each row.

Simple Primary Key: A primary key that consists of one column.

Composite Primary Key: A primary key that consists of multiple columns.

PRIMARY KEY: A constraint in a CREATE TABLE statement that names the table's primary key.

Foreign Keys

Foreign Keys: A column, or group of columns, that refer to a primary key. A foreign key must be fully NULL or match some primary key value.

FOREIGN KEY (ColumnName) REFERENCES TableName(ColumnName): A constraint in CREATE TABLE that sets a foreign key.

Referential Integrity

Insert, update, or delete statements that violate referential integrity can be automatically corrected with any of four constraints:

RESTRICT: Rejects an insert, update, or delete that violates referential integrity.
SET NULL: Sets invalid foreign keys to NULL.
SET DEFAULT: Sets invalid foreign keys to the foreign key default value.
CASCADE: Propagates primary key changes to foreign keys.

Constraints

Constraint: A rule that governs allowable values in a database.

Column Constraint: Appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column.

Table Constraint: Appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns.

UNIQUE: Ensures that values in a column, or group of columns, are unique.

CHECK: Specifies an expression on one or more columns of a table. The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL.

CONSTRAINT: Optional keyword that names constraints.

Week 17