Database Basics

Database Basics

• Data is information describing real-world systems, represented numerically, textually, visually, or as audio.
• Analog data is encoded as continuous variations on physical media
• Digital data: encoded as zeros and ones on electronic and magnetic media.
• A database: structured data collection in a structured format, typically stored on computers
• A database system/database management system (DBMS): software that reads and writes data in a database, ensures data is secure, consistent, and available

Database System Components

• Query language is a specialized programming language for database systems.
• Database application: software to interact with database systems.
• Database administrator: secures the database system, enforces user access procedures.
• Database designer: determines the format of data and database structure, balances storage, response time and rules
• Database programmer: develops computer programs that use a database.
• Database user: a consumer of data in a database who requests, updates, or uses data via applications or queries.

Database Systems: Key Concepts

• Authorization: limits user access to specific parts of a database
• Rules: ensure data consistency with structural and business rules.
• Transaction: a group of queries that must be fully completed or rejected to maintain data integrity.
• Architecture: defines the internal components and relationships within a database system.

Query Processing

• Query processor: interprets queries, plans modifications/retrieval, and returns results
• Query optimization: ensures efficient execution of instructions on data.
• Storage manager: translates query instructions into low-level file-system commands.
• Indexes: used by the storage manager for quick data location.
• Transaction manager: ensures proper execution of transactions.
• Metadata: data about the database (e.g., column names, row counts).
• Log: a file recording all database inserts, updates, and deletes.
• Catalog/data dictionary: directory of tables, columns, indexes, and other database objects.

Database Types and Languages

• Relational database: stores data in tables with rows and columns, supports SQL.
• SQL: Structured Query Language for data manipulation and system administration.
• Big data: massive volumes of online data, often unstructured, generated since the 1990s.
• NoSQL: non-relational systems optimized for big data.
• MongoDB: big data, open source, NoSQL database.
• Open source software: allows inspection, copying, and modification without licensing fees.

Query Languages

• Query: a database command to insert, retrieve, update, or delete data.
• Query language: programming language for writing database queries.
• CRUD operations: Create, Read, Update, and Delete the four common queries
• SQL: the standard query language for relational database systems.
• Statement: an SQL command (e.g., INSERT, SELECT, UPDATE, DELETE).

SQL Operations

• INSERT: adds rows into a table

  INSERT INTO table_name (column_name1, column_name2, ...) VALUES (DEFAULT, 'bob', 30, 150);
  

• SELECT: retrieves data from a table

  SELECT column1, column2, ... FROM table_name; -- return all data from those columns.
  

• UPDATE: modifies data in a table

  UPDATE table_name SET column_name = 2 WHERE column_id = 3;
  

• DELETE: deletes rows from a table

  DELETE FROM table_name; -- all rows deleted!
  DELETE FROM table_name WHERE column_name = 'value'; -- delete row
  

• CREATE TABLE: creates a new table by specifying the table and column names

  CREATE TABLE Customers (
      customerId INT NOT NULL UNIQUE,
      first_name VARCHAR(255) NOT NULL,
      age INT CHECK(age > 18)
      driverid INT NOT NULL,
      PRIMARY KEY (customerId),
      FOREIGN KEY (LOCAL_COLUMN_NAME) REFERENCES TABLE_NAME (COLUMN_NAME),
  );
  

• Data type specifies format of column values, can be numeric, textual, or complex.

Database Design Phases

• Database design specifies tables, columns, data types, and indexes.
• Analysis phase defines database requirements independently of specific systems, and uses entities, relationships, and attributes.
• Logical design implements database requirements in a specific database system, and converts entities, relationships, and attributes into tables, keys, and columns called a database schema
• Keys identify individual rows in tables using SQL with CREATE TABLE.
• The physical design phase adds indexes and specifies storage media organization to affect query processing speed, while preserving data independence

Additional Concepts

• Data independence: physical design does not affect query results
• API: application programming interface simplifies SQL use with general-purpose languages.
• MySQL: Oracle-sponsored relational database system.
• MySQL Community/Server: a free edition.
• MySQL Enterprise: paid version with commercial database management.
• Root account: administrative account with full MySQL control.
• MySQL Command-Line Client: text interface for database administration and SQL execution.
• Workbench: allows SQL command execution using an editor.

Relational Model

• Relational Model: with Data structures, integrity and manipulation constraints, and operations
• Relational databases store data in Data type specific tables with primary and foreign keyed columns
• Data structures form the backbone of efficient and organized information storage.
• Indexes are used to help retrive data effieicntly
• Relational Model was was first proposed in 1970 by Edgar Codd of IBM and first implemented in 1980s
• Standard quer language is SQL for manipulating data
• Sets have unordered elements, e.g. {a, b, c} is equal to {c, b, a}
• Tuples have ordered elements, e.g. (a, b, c) is not equal to (c, b, a) and are put in parenthesis

Relational Model Tables

• Table also called File,Relation
• Column also called Field,Attribute
• row also called Record, Tuple
• Relational data types are are named set of values, from which column values are drawn.
• Operations are collectively knownRelational algebra, SQL language is built onto the fundamentals.
• Relational rules ensures data is valid, as logical constraints on data value

SQL Components and Literals

• Structured Query Language (SQL) is a high-level computer language for storing, manipulating, and retrieving data to use with relational and non relational datasets
• Common SQL Literals are Strings, Numbers, Binary
• Strings use ’ or ”
• Numbers use integers and decimals
• Binary uses x’ofa2’

SQL Grammar

• Keyword are special meaning words eg. SELECT, FROM, WHERE
• Identifiers are the referenced database items eg City, Name, Population
• Statementsare 1 or more clauses (command(s)
• Clause group up keywords into conditional groups SELECT, FROM, and WHERE
• Data Definitio, language (DDL) are used to define the structure such as CREATE, ALTER, DROP
• Data manipuilation (DML) are for working on (manipulation) datastored eg,. INSERT, UPDATE, DELETE
• Data query Languge (DQL) is a method to retrieve data suchas SELECT Data control Languge (DCL) controls user access with GRANT, REVOKE
• Data tx Languge (DTL) manages db tnx using **SAVEPOINT, ROLLBACK, COM

Instance and Management

• Database System Instance is when you have access to a database, (instance)
• Instance can be for local, or cloud
• Command CREATE DATABASE database_name Creates a named database
• Command DROP DATABASE database_name drop a database and all tables inside/related components table
• Command SHOW DATABASES List out all available databases
• Command SHOW TABLES Show all tables in database
• Command SHOW COLUMNS FROM table_name List out all availablecolumns/attributes of table
• CommandSHOW CREATE TABLE table_name List out the DDL that was required to create a table

Tables

• Tables have a name, sequence of columns (tuple), and a list of rows
• Columns and rows have individual datatypes
• Exact on value for each cell (attributes/value)
• No duplicated columns (same or diff table)
• No duplicated rows (same columns of a new row)
• No row order (how physical organized not important
• The above concept is refered to as data independence from performance
• Data definition Languge (DDL) commandCREATE TABLE creates a new table
• Data definition Languge (DDL) command DROP TABLE TableName;Delete's the table
• DDL command ALTER TABLE TableName Modifes the tables, such as columns/attributes and their definitions

SQL Data Types

• Data types used to draw coul values from an assigned value
• INTorINTEGER`` Integer with (+/-) numbers VARCHAR(N) strings with N chars
• *CHAR(N)fixed valued char string with N` chars
• DATE stored as mm/dd/yyyy
• DECIMAL(N, D) Total value N with D decimal places

Data Operators and Selecting Rows

• Expressions use operators and operands , and parentheses of different datatypes
• Order of operations(PEMDAS, BODMAS)
• SELECT statement will SELECT columns and FROM tables
• WHERE statement will SELECT * FROM when * meets conditions and is TRUE,orNULL
• Can use LIMIT to define how many values returned at max

Null Values

• Have NULL as representation, where its special cased depending
• NOT NULL constraint prevents NULL data, and will reject values
• to check if there is a value or its IS NULL

Insert Update and Delete

• MySQL
  • INSERT statement adds rows
  • INSERT clause names table and column
  • VALUES values
• Keywords from UPDATE
  • Update table
  • Can use optional where clause Keywords from DELETE
  • Delete from table
  • Optional where value
• TRUNCATE keywords, will`Delete allrows but keep tables
• MERGE: will select from one and insert somewhere else

Primary Keys

• Composite primary key: uses multi-columns
• Use minimal to remove redundency
• FOREIGN KEY column for referencing
• ON UPDATE,ON DELETE statements

constraints

• Table constraints (not null) use separate cause CREATE TABLE, to govern multi- and column

  Operators

• Used with where clauses
  IN() , when comparing one or the list of values
  BETWEEN, for all values

• Note that column LIKE and BINARY() operators function differently, with case sensitivity
  DISTINCT statement, used to return only unique values ORDER BY statements, order DESC keyword can used to reverse

function arguments

ABS() returns value from negative with number LOWER lower case TRIM () removes whitespace HOUR, MINUTE, SECOND

Aggregate Functions

• These functions is from set rows and returns summar value COUNT() MIN() MAX() SUM() AVG ()
• Can also use GROUP BY and filter, HAVING condition

Joins

• SELECT column name `AS`` (use as), used to simplify, query
• join from table X and Y table, to output, where X AND Y column Z meet/match criteria
• Join methods
  • INNER only matching left and right table
  • FuLL: all
  • ON:specifies join

outer join

• When joined select is unmatched row including left, right
• SELECT``X.name, Y.name FROM X, Y WHERE `X.id=Y.id, UNION

Equi joins, self joins and cross joins

• equi jjoin comparess columns or multiple table columns with = operator,A.x=``B.y
• non-equi is different with < and >
• for self joins it to itself SELECT x.name B.manager.. FROM employee X join employee Y ...

Sub queries

• subquery = nested or inner query Ex using IN

SELECT * FROM TABLE1
WHERE percentage IN (SELECT percentage
FROM TABLE1
WHERE columnname = ‘ABW’);

Views

• Views = virtual tables that will get created on runtime

CREATE VIEW NAME AS  SELECT DEPT FROM EMP

• Materialize views are data that are pre computed and stored separately And use in select statements
• The difference to view is that those compute and return when called The materialzied just gets read

##Replicational ALgebra

• Consists of many small operation to create something complex
• SELECT - choose row base don conditin
• PROJECT - chooses base on columns
• Prod uct operation combine tables
• JOIN: combine select row and filter
• Set operation used for union, intersect, difference
  • Similar as venn diagrams

ER ALgrebra Model

• Database design starts with what the design as specified
• The relational algebra is ER model
• Entity = noun/things relationship - verb (join) Attributes = describes entities

Other Modeling Details

• Each entity has a relationship
• Entity relationship diagram
• Cardinality is attribute relationship, And has min and max values

-Super type and sub type

• Use single or plural
• ERDs often use a min or max values

Other Database Design

• To select keys Stable should not change value simple easy store meaninglessNo descriptive Select for type based on table and weak/composite constraints