Database Basic | Structure | MySql

1. Data
1. Store in a file.
2. Data Type Source
1. Data basically come into database two ways
1. Raw Data (From variable or constant)
2. Form Data (may have file Data)
3. Data Model
1. When client's idea stored in mySql server (Idea ->structure model->table model->database).
4. Query Standard
1. Query select data which are required. Extract unnecessary data is very bad practice.
5. Related Data
1. In a same table column's (fields) do not have any other's table data. Like employee table have't any department information.
6. Table
1. Table have rows and columns like spreadsheet. Tables are used for store data. 
2. Column value contain single value with same data type.
3. Cautions
1. Similar data with many tables
2. Related data are not allowed.
3. Combination of value in a single row. Like, multiple value or comma separated values in a single row. Puzzle primary key. Atomic property reqired. 
4. Fuzzy column or table name.
5. Poor planning and normalization.
7. Relational Database
1. Proposed 1069 by Edgar Codd (of IBM Research).
2. When data cross another table through reference.
8. MySql
1. Created 1995 by Michael Widenius.
2. MySql engine helps to store, handle, manipulate and retrieve data. 
3. InnoDB is default and major storage engine.
4. mysql>show engine shows MySql engines. SET default_storage_engine = MYISLAM command for set default storage engine.
5. Different engine have different capability like speed, storage, data integrity ...  
9. Data Types
1. Category
1. Numeric
2. String
3. Date 
2. Structure
1. Signed 
2. Unsigned -only positive and long range
10. Functional Dependency
1. If the column value references another column value in a same table/another table is called functional dependency.
2. Column student_name depends on student_id column because student_id column used to uniquely determine student_name.
11. Non-key field
1. Column value directly depend on primary key. On the other hand, column value partially or compositely depend on primary key is non-key field.
12. Relation Among Tables
1. One-to-Many

1. Example 1 : In a 'student management' database one student can take one or more course but course is taken not more one student. 
2. Example 2: In a 'civil engineer' database one engineer can have one or more than one construction project but construction project is taken by one engineer. 
2. Many-to-Many

1. Example : In a 'Library' database author have zero or more books but one book have one or more author.  
2. Junction Table: To support many-to-many relation demand to create a third table. 
3. One-to-One

1. In a 'student management' database student postal code refer another address table is one-to-one relationship.
13. Atomic Property
1. When table contain single value instead multiple value. 
14. Normalization
1. Divide larger table into smaller table with relational dependency. One table makes relation with other table. 
2. Benefits of Normalization
1. Minimize redundant data, but no information loss
2. Minimize dependency, improve business logic and figure related data, but no unusual data 
3. Minimize null value 
3. 1NF 
1. No repeat or duplicate column value. Atomic data.
2. No related data (5).
3. Row defined by Primary key and no repeated row.
4. Contain/create primary key.
4. 2NF
1. Non-key field must depend on the primary key
2. Should be in 1NF first.

                              After 2NF

5. 3NF
1. Should be in 2NF first.
2. Every non-field attribute can't depend on non-field attribute. Ensure primary key on every non-field attribute. 
3. Example: Address attributes make a new table; zip code can be a primary key.

15. Database Design
1. Purpose of the Database
2. Analysis Data
3. Organize tables
4. Determine fields
5. Specify primary key
6. Relation with other table
7. Normalization
8. Add Integrity constraint