A3.2.3 Different Data Types Used in Relational Databases Notes

Different Data Types Used in Relational Databases

String Data Types

1. CHAR(size)
   1. Description: Fixed-length string.
   2. Example: CHAR(6) for storing a fixed-length ID like P@ssWd.
2. VARCHAR(size)
   1. Description: Variable-length string.
   2. Example: VARCHAR(255) for names or email addresses.
3. TEXT
   1. Description: Large text field, up to 65,535 characters.
   2. Example: Storing product descriptions.
4. ENUM
   1. Description: String field with predefined values.
   2. Example: ENUM('Male', 'Female', 'Other') for gender.
5. SET
   1. Description: String field that can hold multiple values from a predefined list.
   2. Example: SET('Reading', 'Traveling', 'Sports') for hobbies.

Numeric Data Types

1. INT
   1. Description: Whole numbers.
   2. Example: INT for storing quantities or IDs.
2. FLOAT(p)
   1. Description: Floating-point numbers.
   2. Example: FLOAT(7,2) for storing prices like 19.99.
3. BOOL
   1. Description: Boolean values (0 for false, 1 for true).
   2. Example: BOOL for flags like isActive.
4. BIT (SIZE)
   1. Description: Stores binary values from 1 - 64 bits in size
   2. Example: BIT (4) to store the number 3: 0011

Date/Time Data Types

1. DATE
   1. Description: Stores dates in YYYY-MM-DD format.
   2. Example: DATE for birthdates.
2. DATETIME
   1. Description: Stores date and time in YYYY-MM-DD hh:mm:ss format.
   2. Example: DATETIME for recording transaction timestamps.
3. TIMESTAMP
   1. Description: Automatically stores the current date and time.
   2. Example: TIMESTAMP for tracking record updates.

Importance of Data Type Consistency

1. Data Integrity
   1. Ensures that data is stored and processed correctly.
   2. Example: Storing a date as a STRING prevents date-based calculations.
2. Performance Optimization
   1. Efficient data storage and retrieval.
   2. Example: Using INT instead of VARCHAR for IDs reduces storage space and improves query performance.
3. Validation and Constraints
   1. Enforces rules on data entry.
   2. Example: ENUM restricts values to a predefined list, preventing invalid entries.

Potential Effects of Choosing the Wrong Data Type

1. Data Inconsistency
   1. Storing numbers as strings can lead to errors in calculations.
2. Performance Issues
   1. Using TEXT for short strings wastes storage space and slows down queries.
3. Data Loss or Truncation
   1. Using CHAR(10) for a string longer than 10 characters will truncate the data.
4. Inaccurate Calculations
   1. Storing prices as INT instead of FLOAT can lead to rounding errors.

Practical Examples

1. Name: VARCHAR(100)
2. ID Number: INT
3. Vegan: BOOL
4. Height: FLOAT(3,2)
5. Address: TEXT
6. Ticket Cost: FLOAT(5,2)
7. Quantity: INT