Database Design and Modeling

Data Modeling

• 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

• 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

• 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

• 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 (Logical Design)

• 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 (Conceptual Design)

• 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.