Database Design and Modeling

Study Notes

Data modeling is the process of creating a conceptual representation of data structures and relationships.
It involves identifying entities, attributes, and relationships between them.
Goals of data modeling:
- To understand the structure and organization of data.
- To identify data redundancy and inconsistencies.
- To create a common understanding of data among stakeholders.

SQL (Structured Query Language) is a standard language for managing relational databases.
SQL is used for:
- Creating and modifying database structures (e.g., tables, indexes).
- Inserting, updating, and deleting data.
- Querying data (e.g., selecting, filtering, sorting).
Basic SQL syntax:
- SELECT: retrieves data from a database table.
- INSERT: adds new data to a database table.
- UPDATE: modifies existing data in a database table.
- DELETE: deletes data from a database table.

Normalization is the process of organizing data in a database to minimize data redundancy and dependency.
Goals of normalization:
- To eliminate data redundancy and inconsistencies.
- To improve data integrity and scalability.
- To reduce data storage requirements.
Normalization rules:
- First Normal Form (1NF): each table cell contains a single value.
- Second Normal Form (2NF): each non-key attribute depends on the entire primary key.
- Third Normal Form (3NF): if a table is in 2NF, and a non-key attribute depends on another non-key attribute, then it should be moved to a separate table.

Database design is the process of creating a detailed blueprint for a database.
Database design involves:
- Identifying data entities and relationships.
- Defining data structures and schema.
- Determining data storage and indexing strategies.
- Optimizing database performance and security.
Database design principles:
- Data independence: separating data from application logic.
- Data abstraction: representing data in a simplified and abstract way.
- Data normalization: organizing data to minimize redundancy and dependency.

Projeto Lógico is the process of translating a conceptual data model into a logical data model.
It involves:
- Defining database schema and structure.
- Identifying data relationships and constraints.
- Determining data storage and indexing strategies.
- Optimizing database performance and security.
Logical design outputs:
- Entity-Relationship (ER) diagrams.
- Relational database schema.
- Data normalization and denormalization strategies.

Projeto Conceitual is the process of creating a high-level conceptual representation of data.
It involves:
- Identifying data entities and relationships.
- Defining data attributes and constraints.
- Determining data scope and boundaries.
- Identifying data requirements and constraints.
Conceptual design outputs:
- Entity-Relationship (ER) diagrams.
- Conceptual data models.
- Data requirements and constraints documentation.