INFOMAN

• Information Management - information systems
• Data Processing - collection and manipulation of data
• Data Processing

  validation

  sorting

  summarization

  aggregation

  analysis

  classification

  reporting
• Database Schema - description of database (via DBMS)
• Database Instance - actual data contained
• Database Instance - extension/state/snapshot
• Database Management

  Data definition

  Data manipulation

  App development

  Administration
• Data model - collection of concepts to describe database
• Data model

  1. structure
  2. integrity constraints
  3. operations
• Data model categories

  Conceptual - (high-level)

  Logical - implementation or representational

  Physical - (low-level) physical description
• SQL - Structured Query Language
• RDBMS - Relational Database Management System
• DBMS - Database Management System
• NoSQL - used to store Big Data
• RDBMS - DBMS that stores and provide access to relational databases (SQL databases)
• Command List

  show databases; - display all databases

  show schemas; - same as show databases

  use <database>; - choose specified database

  show tables; - display tables in current database

  show columns from <table> - display columns in table

  desc[ribe] <tablename> - alternative for show column

  help
• Start Server Command - mysqld
• Shutdown Server Command - mysqladmin -u root shutdown
• Check Server Status Command - mysql -u root status
• phpMyAdmin - web client interface by WampServer
• phpMyAdmin

  localhost/phpmyadmin/

  Structure tab - view table structure

  SQL tab - command-line interface
• MySQL Workbench - graphical user interface
• MySQL Workbench

  Test Connection - verify database connection

  Flash Icon (CTRL+ENTER) - execute MySQL commands
• Basic Queries

  • SELECT - data retrieval
  • FROM - specify table
  • DISTINCT - remove duplicate rows
  • column alias (AS) - rename column heading
  • WHERE - filter records -> followed by conditional expression
  • LIKE - .isEqual()
  • AND - both
  • OR - either
  • BETWEEN (inclusive of lower and upper bounds)
  • Logical Operator Precedence - NOT, AND, OR
  • ORDER BY - sort results (always last)
  • ASC - ascending (default)
  • DESC - descending
• 2 Types of SQL Functions

  1. Single-Row Functions
  2. Group Functions
• Single-Row Functions - one result per row
• Single-Row Functions:

  String - CONCAT, LOWER, UPPER, LENGTH

  Numeric - ABS, ROUND, TRUNCATE

  Date - ADDDATE, DATE_FORMAT, MONTH, YEAR, NOW

  Flow Control - CASE, WHEN, THEN, IF, IFNULL, NULLIF
• Group Functions - multiple-row or aggregate
• Group Functions:

  AVG - returns average

  MIN - lowest value

  MAX - highest value

  SUM - total of all values

  GROUP BY - divide table rows into sets
• Group Functions = Nested single-row functions
• WHERE - exclude rows before dividing into groups

  HAVING - filter after grouping

  TOP-N Queries - provide top n results

  LIMIT - limit

  ORDER BY - sort
• Table (Relation) - basic storage structure for data in a database.
• Table Structure:

  Heading = Column = Attributes = Fields

  Body
• SQL - straightforward approach for querying
• Row = Tuple = Record

  Column = Attributes = Fields
• Domain (data type): legal values for attributes
• Valid Relations:

  No Duplicate Tuples

  Insensitive to Order (rows and columns)

  Atomic Domain Values (ONE logical value in intersections)
• Nulls

  Not Applicable

  Unknown

  Unavailable
• Integrity Constraints - enforce rules to maintain correctness of data
• Integrity Constraints

  Application-Based: based on business rules on semantics

  Schema-Based: apply to all relational databases