Data Definition in DBMS

Introduction to Data Definition

Definition
• Data definition in DBMS is the process of defining the structure, and
properties of the database.
Importance
• Proper data definition is crucial for organizing and managing data
efficiently.
Benefits
• Ensures data integrity, reduces redundancy, improves data retrieval
speed, and enhances data consistency.
• Definition: Indexing is a technique used to improve data retrieval
speed in databases.
• Purpose: Indexes help the database find specific data faster, reducing
query response times.
Data Types
• Data types define the type of data that can be stored in each column
of a table.
• Common Data Types: Integer, Floating-point, String, Date, Boolean,
etc.
• Choosing Data Types: Select appropriate data types based on the
nature and size of the data.
• Different data types consume varying amounts of storage space.
Numeric Data Types
• Examples: INT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL, etc.
• Usage: Storing whole numbers, decimal numbers, or floating-point
values.
• Benefits: Efficient storage and arithmetic operations.
TINYINT
• Range: The TINYINT data type can store integer values from -128 to
127 for a signed TINYINT or 0 to 255 for an unsigned TINYINT.
• Storage: It uses 1 byte of storage for both signed and unsigned
TINYINT.
• Usage: TINYINT is commonly used for columns that require small
integer values, like flags, statuses, or small counters, when the range
of values is limited.
INT
• Range: The INT data type can store integer values from -2147483648
to 2147483647 for a signed INT or 0 to 4294967295 for an unsigned
INT.
• Storage: It uses 4 bytes of storage for both signed and unsigned INT.
• Usage: INT is suitable for most cases where you need to store integer
values that might be larger than what TINYINT can accommodate
BIGINT
• Range: BIGINT can store integer values from -9223372036854775808
to 9223372036854775807 for a signed BIGINT or 0 to
18446744073709551615 for an unsigned BIGINT.
• Storage: It uses 8 bytes of storage for both signed and unsigned
BIGINT.
• Usage: BIGINT is used when you need to store large integer values,
especially when the range of values exceeds what INT can
accommodate.
FLOAT
• FLOAT is a numeric data type that stores floating-point numbers with
a floating precision. It is a 4-byte data type.
• FLOAT is an approximate numeric data type that stores floating-point
numbers with a floating precision. It is a 4-byte data type.
DECIMAL
• DOUBLE is similar to FLOAT but has double precision, providing
greater accuracy. It is an 8-byte data type.
• Precision: The precision of a DOUBLE is approximately 15 decimal
places.
Character Data Types
• Examples: CHAR, VARCHAR, TEXT
• Usage: Storing text or alphanumeric data like names, addresses, or
descriptions.
• Features: CHAR for fixed-length strings, VARCHAR for variable-length
strings.
CHAR and VARCHAR
• Fixed-Length: CHAR is a fixed-length data type, which means that it
always reserves a specific amount of storage space, regardless of the
actual data length.
• Variable-Length: VARCHAR is a variable-length data type, meaning it
only uses as much storage space as needed to store the actual data,
plus one or two bytes to store the data length.
•How TEXT datatype is different from
CHAR AND VARCHAR……?
TEXT
• Large Data Storage: TEXT is a special data type designed to handle
large blocks of text, allowing for more extensive storage compared to
CHAR and VARCHAR.
• Size Limit: The maximum length of a TEXT column is much larger than
CHAR and VARCHAR. It can store up to 65,535 characters in MySQL
versions earlier than 5.0.3, and up to 4GB in newer versions (since
5.0.3).
• Usage: TEXT is commonly used for storing large text-based data, such
as article content, blog posts, or comments.
Date and Time Data Types
• Examples: DATE, TIME, TIMESTAMP, DATETIME
• Usage: Storing dates, times, or combined date and time values.
• Benefits: Facilitating date and time operations and comparisons.
DATE
• Format: The DATE data type is used to store date values in the format
'YYYY-MM-DD'.
• Usage: Use DATE when you only need to store dates without any time
information, such as birthdates, event dates, or booking dates.
• CREATE TABLE events (
• event_id INT PRIMARY KEY,
• event_date DATE
• );

• INSERT INTO events (event_id, event_date)VALUES (1, '2023-08-15');

TIME
• Format: The TIME data type is used to store time values in the format
'HH:MM:SS'.
• Usage: Use TIME when you only need to store time durations or event
timings without date information.
TIMESTAMP
• Format: The TIMESTAMP data type is used to store date and time
values in the format 'YYYY-MM-DD HH:MM:SS'.
• Usage: Use TIMESTAMP when you need to store date and time
together and want automatic update of the value when the row is
inserted or updated.
DATETIME
• Format: The DATETIME data type is used to store date and time
values in the format 'YYYY-MM-DD HH:MM:SS'.
• Usage: Use DATETIME when you need to store date and time together
with a broader range than TIMESTAMP.