IM101 MIDTERM LESSON 4

Database design

is the process of producing a detailed data model of a database.

data consistency and integrity (reduces data redundancy)

A well-designed database ensures

Conceptual Design

Logical Design

Physical Design.

Types of Database Design

Conceptual Design

This phase involves creating a high-level representation of the database that outlines the entities, their attributes, and the relationships between entities. The Entity-Relationship Diagram (ERD) is the primary tool used in this phase.

Entities

Attributes

Relationships

Entity-Relationship Diagram (ERD)

Entities

Objects or things in the system

Attributes

Properties of entities

Relationships

Connections between entities

Customer

Order

Product

Relationships

Online Store, the ERD would have entities such as:

Primary Key

A unique identifier for each record in a table

Foreign Key

A field in one table that references the primary key in another table, used to establish a relationship between tables

relational schema

After the conceptual design, the ERD is transformed into a __that can be implemented in a database. In this phase, tables are defined, and relationships between tables are formalized.

Tables

represent entities from the ERD.

table

Each column in a__ represents an attribute,

row

each __ represents a record.

Relationships

between tables are defined by primary and foreign keys.

Customer Table

Order Table

Product Table

Order Product Table

Example Relational Schema:

For the Online Store example:

data type

Each attribute in a table must be assigned a

Constraints

ensure data integrity, such as NOT NULL (a field must always have a value), UNIQUE (no duplicate values), and FOREIGN KEY constraints for relationships.

Physical Design

phase involves mapping the logical schema to the physical storage in the chosen Database Management System (DBMS). This phase focuses on optimizing the design for performance and storage efficiency.

data types

Choosing appropriate __ based on the expected data size and frequency of updates.

indexes

Implementing __ to speed up query performance.

data types

indexes

Storage Considerations

Normalization

denormalization

Optimization Techniques

Normalization

reduces redundancy and improves data integrity.

denormalization

in some cases to improve query performance, especially for read-heavy operations

Data Consistency

Data Redundancy Minimization

Data Integrity

Flexibility

Principles of Good Database Design

Data Consistency

Ensures that data remains accurate and consistent across the database.

Redundancy

occurs when the same data is stored in multiple places, leading to wasted space and potential inconsistencies.

Data Redundancy Minimization

minimizes redundancy by normalizing the data.

Data Integrity

Enforcing constraints (e.g., primary key, foreign key) ensures the accuracy and consistency of the data.

Referential Integrity

Ensures that foreign keys accurately reference primary keys in related tables.

Flexibility

allows for easy updates and modifications as business needs evolve.

Relational Database Management Systems (RDBMS)

are systems that manage data using a structured format of tables (or relations) with predefined relationships between them. They are the foundation for most modern database applications.

Relational Database

is a collection of data organized into tables (relations) where each table consists of rows (tuples) and columns (attributes).

RDBMS

is a software system that allows users to create, manage, and manipulate relational databases. is built on a set of components that work together to store and retrieve data efficiently

MySQL

PostgreSQL

Oracle

Microsoft SQL Server

Examples of popular software in DBMS use for RDBMS platforms include:

Tables (Relations)

Primary Key

Foreign Key

Relationships Between Tables

Components of RDBMS

Indexes

created to speed up data retrieval operations in a table. ➢ They allow the RDBMS to find data quickly without scanning the entire table.

SQL (Structured Query Language)

is the standard language used to interact with RDBMS. It allows users to perform various operations such as creating tables, inserting data, updating records, and retrieving data.

Create

Insert

Select

Update

Delete

Basic SQL Operations

Create

statement is used to create a new database object, such as a table, index, view, or database. ➢ It defines the structure of the object and specifies the data types for each column.

Insert

is used to insert new rows of data into a table. ➢ It can insert data into all columns or only specific columns.

Select

used to query and retrieve data from a database. ➢ It can fetch data from specific columns or all columns, and it can also apply filters to select specific rows.

Update

statement is used to modify existing records in a table. ➢ It allows you to change values in one or more columns for specific rows.

Delete

statement is used to remove rows from a table. ➢ It can delete specific rows based on conditions, or all rows if no condition is specified.

Normalization

is a process of organizing data to minimize redundancy and dependency. It involves dividing large tables into smaller, related tables and defining relationships between them.

Avoids data redundancy

Ensures data integrity

Why Normalize?

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Normal Forms

First Normal Form (1NF)

Ensures that each table column contains atomic (indivisible) values, and each entry in a column contains only one value.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and all non-key attributes are fully dependent on the primary key.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and all its attributes are directly dependent only on the primary key.

RDBMS properties

to ensure data reliability and consistency, especially in transaction processing.

Atomicity

Consistency

Isolation

Durability

RDBMS properties

Atomicity

Ensures that all operations in a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.

Consistency

Ensures that the database moves from one valid state to another. Any transaction will bring the database from one valid state to another, maintaining data integrity.

Isolation

Ensures that transactions are processed independently. Multiple transactions happening at the same time should not affect each other.

Durability

Ensures that once a transaction is committed, it remains in the system even in the event of a system failure.

Data Integrity and Security

Flexibility in Querying

Reduced Redundancy

Concurrency Control

Advantages of RDBMS

Data Integrity and Security

RDBMS ensures that data remains consistent and secure using constraints and access control.

Flexibility in Querying

SQL allows complex queries to be written easily, enabling data retrieval in various ways.

Reduced Redundancy

Data is stored only once, thanks to normalization, reducing redundancy.

Concurrency Control

Multiple users can access the database simultaneously, with the system ensuring isolation and consistency.

Complexity in Handling Large Datasets

Cost of Setup and Maintenance

Performance Bottlenecks with Complex Queries

Disadvantages of RDBMS

Complexity in Handling Large Datasets

As the data grows, managing and querying very large datasets can become complex and slower.

Cost of Setup and Maintenance

High-end RDBMS solutions can be costly in terms of licensing, hardware, and maintenance.

Performance Bottlenecks with Complex Queries

While RDBMS are efficient, highly complex queries with multiple joins can slow down performance.