1. DBMS coordinate all operations
2. ERD notations
   1. Entity and its attributes
   2. Relationships
   3. Cardinality = N, modality = 0
   4. Cardinality = N, modality = 1
   5. Cardinality = 1, modality = 1
   6. Cardinality = 1, modality = 0
3. MySQL syntax
   1. Create database structure
      1. CREATE SCHEMA AUTHORIZATION <creator>
      2. CREATE DATABASE <database name>;
   2. Create tables
      1. CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... PRIMARY KEY (column_name) )
   3. Data entry
      1. INSERT INTO table_name VALUES (value1, value2, value3,...)
   4. Save content
      1. COMMIT <table names>;
   5. List content
      1. SELECT column_name(s) FROM table_name WHERE conditions
         1. WHERE column_name IN (value1,value2,...)
            1. Sub-query
         2. BETWEEN value1 AND value2
         3. WHERE column_name LIKE pattern
            1. % = wildcards
         4. ORDER BY column_name(s) ASC|DESC
         5. GROUP BY column_name HAVING aggregate_function(column_name) operator value
   6. Join tables
      1. SELECT column_name(s) FROM table_name1, table_name2 WHERE table_name1.column_name=table_name2.column_name
   7. Aggregate functions
      1. SUM( )
      2. AVG( )
      3. MAX( )
      4. MIN( )
      5. COUNT(* )
   8. Stored procedure
      1. DELIMITER // CREATE PROCEDURE procedure_name() BEGIN ... END // DELIMITER ;
         1. SELECT
         2. RETURNS
         3. DECLARE
         4. SET
      2. CALL procedure_name()
4. Procedural database objects
   1. Benefits
      1. Performance
      2. Network traffic reduction
      3. Separate of database logic and application logic
      4. Maintainability
      5. Security
      6. Portability
   2. Stored procedure
      1. Consist declarative and procedural SQL statements
      2. Can be called by program or trigger
      3. Can have internal variables, and specify inputs and outputs
   3. Trigger
      1. Programmable events
      2. Executed before or after INSERT, UPDATE or DELETE statements
      3. Maintain integrity
      4. One trigger to one event
5. Hierarchy of data organization
   1. Database
      1. File
         1. Record
            1. Field
            2. Character
            3. Bytes
6. Aims
   1. Meet users' view towards data
   2. Tackle atomicity problem
   3. Concurrent access
      1. Reduce redundancy and inconsistency
   4. Secure data access
   5. Effective and efficient data management
   6. Make query easier
   7. Better user experience
7. Problems of traditional file systems
   1. Redundancy
   2. Anomalies
      1. Update
      2. Addition
      3. Deletion
   3. Inconsistency
   4. Sharing
   5. Security
   6. Structural dependance
8. People and database systems
9. Normalization
   1. Check the table created against database design rules
   2. Reduce redundancy and anomalies
   3. Stages
      1. 1NF
         1. Have candidate key present
         2. No repeat groups
         3. All attributes dependant on primary key
      2. 2NF
         1. No partial dependancies
         2. Write keys in separate lines
      3. 3NF
         1. No transitive dependancies
      4. BCNF
   4. Based on
      1. Functional dependancy
         1. An attribute depends on another on 1 to 1 basis
      2. Fully functional dependancy
         1. An attribute depends on another, instead of its functional dependant attribute
10. SQL
    1. Extract, manipulate and structure data
    2. Categories
       1. Data definition
          1. Define database structure and data schema
       2. Data manipulation
          1. Extract, add, modify and delete data inside tables
    3. Benefits
       1. Standard language
       2. Cross system
          1. Application portability
       3. Less training needed
          1. Reduce training cost
          2. Improve productivity
11. Relational database
    1. Reason
       1. Give users a simplier file concept
       2. Better database design
    2. Structure
       1. Relation (entity set)
       2. Entity
       3. Attribute
          1. Simple
          2. Composite
          3. Null
          4. Derived
          5. Keys
12. Entity relationship diagram
    1. Three basic elements
       1. Entity
          1. Normal
          2. Weak
             1. Existence dependent
             2. Derived primary key from parent
       2. Attributes
          1. Single valued
          2. Multivalued
          3. Derived
       3. Relationships
          1. Cardinality
             1. 1:1
             2. 1:N
             3. M:N
          2. Modelity
             1. Optional = 0
             2. Mandatory = 1
    2. Convertion to relations
       1. Basic conversion
          1. 1:1 - Single table
          2. 1:N - Primary key of 1 becomes attribute of N
          3. M:N - Each side has own table, plus an intersection table to hold primary keys of both sides
       2. Optional max conversion
          1. 0:1 --> 0:N
          2. To minimize null attributes