If you're diving into the world of databases and wondering how to structure data efficiently, you're in the right place. In this article, we’ll walk through foundational concepts in database design — from understanding what data is, to topics like normalization and joins. Whether you're building apps, handling data for projects, or prepping for tech interviews, these definitions will be your solid starting point.
📌 What is Data?
Data refers to raw facts, figures, or symbols that have not yet been processed or given context. For example, "23", "Alice", or "2025-04-18" are all data. When processed and organized, data becomes information.
📌 What is a Database?
A Database is an organized collection of data that can be easily accessed, managed, and updated. Think of it as a digital filing system for storing information efficiently.
📌 What is a Relational Database?
A Relational Database organizes data into tables (also known as relations) that are related to each other through keys. This model uses SQL for querying and is based on mathematical set theory and relational algebra.
📌 What is an Entity?
An Entity is a real-world object or concept about which data is stored in a database. For example: a Student, Book, or Order can be considered entities.
An Entity is also called Tuple, Row and Record.
📌 What are Attributes?
Attributes are the properties or characteristics of an entity. For a Student entity, attributes could be Name, Roll Number, Age, and Class.
The attributes are also called column or field.
📌 What is a Table?
A Table is a collection of rows and columns in a relational database. Each table stores data about a specific entity type.
The table is also called file.
📌 What are Entity Types and Attribute Types?
Entity Types: A category of entities sharing the same attributes (e.g., Student, Teacher).
Attribute Types: Attributes can be:
Simple: Cannot be divided (e.g., Age)
Composite: Can be split (e.g., Full Name)
Derived: Derived from other data (e.g., Age from DOB)
Multivalued: Can hold multiple values (e.g., Phone Numbers)
📌 What is DBMS and RDBMS?
DBMS (Database Management System): Software used to store and manage data (e.g., MongoDB, Redis).
RDBMS (Relational DBMS): A DBMS that stores data in tables and supports relationships between them (e.g., MySQL, PostgreSQL, Oracle).
📌 What is SQL?
SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows you to Create, Read, Update, and Delete data — often abbreviated as CRUD.
📌 What are SQL Languages?
SQL is divided into sub-languages:
DDL (Data Definition Language): CREATE, ALTER, DROP
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
📌 Why Database Design?
Good design ensures:
Efficiency in queries
Data integrity
Scalability
Avoidance of redundancy and anomalies
📌 What are Schemas and Types?
A Schema is the blueprint of a database — it defines how data is organized, including tables, columns, data types, and relationships.
Types of schemas:
Logical Schema: High-level design
Physical Schema: Storage-level design
View Schema: Customized user perspectives
📌 Naming Convention
Use clear, consistent names in lowercase with underscores:
Tables: student_data
Columns: first_name, created_at Avoid spaces, reserved keywords, and overly complex names.
📌 What are Data Types?
Each column has a data type which defines the kind of data it holds. Examples:
INT for integers
VARCHAR(n) for text
DATE, BOOLEAN, FLOAT, etc.
📌 What is Data Integrity and its Types?
Data Integrity ensures the accuracy and consistency of data. Types include:
Entity Integrity: Primary keys must be unique and not null
Referential Integrity: Foreign keys must refer to valid rows
Domain Integrity: Data must be valid for its field (e.g., age must be a number)
📌 What are Atomic Values?
Atomic values mean that each field should contain a single, indivisible value — not a list or multiple items. This is a core rule of First Normal Form (1NF).
📌 What is Normalization?
Normalization is the process of organizing data to reduce redundancy. It involves breaking down tables into smaller ones and establishing relationships.
Common forms:
1NF (First Normal Form): Eliminate repeating groups
2NF: Remove partial dependency
3NF: Remove transitive dependency
🔗 Relationships in a Relational Database
Relationships define how tables (entities) in a database are connected to each other. Understanding relationships is key to designing normalized and efficient databases. There are three main types of relationships:
📘 One-to-One (1:1) Relationship
In a One-to-One relationship, a row in Table A is related to only one row in Table B, and vice versa.
✅ Example Design:
Table: User
user_id (Primary Key)
username
Table: UserProfile
profile_id (Primary Key)
user_id (Foreign Key referencing User.user_id)
bio
avatar_url
Each user has exactly one profile, and each profile belongs to one user.
📘 One-to-Many (1:N) Relationship
In a One-to-Many relationship, a row in Table A can relate to multiple rows in Table B, but each row in Table B relates to only one row in Table A.
✅ Example Design:
Table: Customer
customer_id (Primary Key)
- name
Table: Order
order_id (Primary Key)
customer_id (Foreign Key referencing Customer.customer_id)
order_date
total_amount
One customer can place many orders, but each order belongs to one customer.
📘 Many-to-Many (M:N) Relationship
In a Many-to-Many relationship, multiple rows in Table A relate to multiple rows in Table B. This is implemented using a junction (join) table.
✅ Example Design:
Table: Student
student_id (Primary Key)
name
Table: Course
course_id (Primary Key)
course_name
Table: StudentCourse (Join Table)
student_id (Foreign Key referencing Student.student_id)
course_id (Foreign Key referencing Course.course_id)
A student can enroll in many courses, and a course can have many students.
🔑 What is a Primary Key?
A Primary Key is a column (or set of columns) that uniquely identifies each record in a table.
It must be unique
It cannot be null
A table can have only one primary key
🔗 What is a Foreign Key?
A Foreign Key is a column that creates a relationship between two tables. It refers to the primary key in another table.
It ensures referential integrity
It can accept nulls (unless otherwise restricted)
will be continue
Top comments (0)