Lesson 4: Physical
Database Design
Physical Database design
• Physical database design is the process of
transforming a data model into the physical data
structure of a particular database management
system (DBMS).
Physical Database design
• The physical design of your database optimizes
performance while ensuring data integrity by
avoiding unnecessary data redundancies.
During physical design, you transform the
entities into tables, the instances into rows,
and the attributes into columns.
Physical Database design vs Logical
Database design
• The main difference between logical database design
and physical database design is that logical database
design helps to define the data elements and their
relationships, but physical database design helps to
design the actual database based on the requirements
gathered during the logical data design.
Logical Database Design Physical Database Design
That describes the data without regard to That represents how the actual
how they will be physically implemented database is built.
in the database.
Defines the data elements and their Developing the actual database.
relationship.
Simpler than the Physical database Complex than the Logical database
design. design.
Physical Database design vs Logical
Database design
• Logical Database Design:
[Link]: Logical database design is primarily
concerned with defining the structure and
organization of data at a high level, without
considering the physical implementation details.
It's about creating a conceptual model of the data
that represents the business requirements.
Physical Database design vs Logical
Database design
• Abstraction: In logical design, you work with
abstract entities such as tables, relationships,
attributes, and constraints. For example, you
might define tables like "Customers," "Orders,"
and "Products" and specify relationships
between them like "One-to-Many" or "Many-to-
Many."
Physical Database design vs Logical
Database design
2. Abstraction: In logical design, you work with
abstract entities such as tables, relationships,
attributes, and constraints. For example, you
might define tables like "Customers,"
"Orders," and "Products" and specify
relationships between them like "One-to-
Many" or "Many-to-Many."
Physical Database design vs Logical
Database design
3. Normalization: Logical design involves
the process of normalizing the data to
eliminate redundancy and ensure data
integrity. This includes defining primary
keys, foreign keys, and specifying data
constraints.
Physical Database design vs Logical
Database design
4. Data Integrity Rules: Logical design
may include the definition of business
rules, constraints, and validation checks,
such as ensuring that order dates are
always later than the corresponding
customer registration date.
Physical Database design vs Logical
Database design
• Physical Database Design:
[Link]: Physical database design is concerned
with translating the logical design into a physical
database schema that can be implemented on a
specific database management system (DBMS). It
deals with aspects like storage, indexing, and
performance optimization.
Physical Database design vs Logical
Database design
[Link] and Indexes: In physical design, you
specify the actual database tables, their data
types, and the creation of indexes to optimize
query performance. For instance, you might
decide to use specific data types like VARCHAR
or INT for columns, and define clustered or non-
clustered indexes.
Physical Database design vs Logical
Database design
[Link] and Filegroups: Physical
design includes decisions about how to
partition large tables or utilize filegroups to
optimize storage and I/O performance. For
example, you may choose to partition a sales
table by date to improve query performance.
Physical Database design vs Logical
Database design
4. Normalization vs. Denormalization: While
logical design often aims for high normalization
to minimize data redundancy, physical design
may involve denormalization when performance
considerations require it. For example, you
might store redundant data in some tables to
reduce the number of joins in complex queries.
Physical Database design vs Logical
Database design
5. Hardware Considerations: Physical design
takes into account hardware-related factors
such as disk space, memory, and CPU
capabilities. It may involve decisions like data
compression and database partitioning
across multiple servers or storage devices.
Physical Database design vs Logical
Database design
In summary, logical database design focuses on defining
the structure and relationships of data at a conceptual
level, while physical database design involves the actual
implementation of the database on a specific DBMS,
considering performance, storage, and other system-
specific factors. The transition from logical to physical
design often involves trade-offs between data integrity,
query performance, and resource utilization.
• Here are two tables that illustrate
the difference between logical and
physical database design for a
simple e-commerce system:
Physical Database
Design:
• Designing tablesin a physical database
involves several steps to create a
structured and efficient database
schema. Here's a step-by-step guide
for beginners, along with explanations
and examples:
• Step 1: Identify Entities and Attributes
• Meaning: Start by identifying the entities
(objects or concepts) your database needs to
store information about. For each entity, list the
attributes (properties or fields) that describe it.
• Example: If you're designing a library
database, entities might include "Books" and
"Authors," and attributes for the "Books" entity
could be "Title," "ISBN," "Publication Year," etc.
• Step 2: Define Data Types
• Meaning: Determine the appropriate data
types for each attribute. Data types specify
the kind of data that can be stored in a
column (e.g., INT for integers, VARCHAR for
text).
• Example: The "Title" attribute for books can
use the VARCHAR data type, while "Publication
Year" might use INT.
• Step 3: Set Primary Keys
• Meaning: Choose a primary key for each
table. A primary key uniquely identifies each
record in the table. It ensures data integrity
and provides a way to retrieve and reference
data efficiently.
• Example: In the "Books" table, you can set
the "ISBN" as the primary key since it uniquely
identifies each book.
• Step 4: Establish Relationships
• Meaning: Identify and establish relationships
between tables. Relationships define how
different tables are connected. Common
relationship types include one-to-one, one-to-
many, and many-to-many.
• Example: In a library database, there's a one-to-
many relationship between "Authors" and "Books"
since one author can have multiple books.
• Step 5: Define Constraints
• Meaning: Specify constraints to enforce data
integrity rules. Constraints can include unique
constraints (ensuring uniqueness in a column),
foreign key constraints (ensuring referential
integrity), and check constraints (applying custom
rules).
• Example: You can add a unique constraint on the
"ISBN" column in the "Books" table to ensure each
book has a unique ISBN.
• Step 6: Consider Indexing
• Meaning: Decide which columns need
indexes to improve query performance.
Indexes allow the database system to
quickly locate and retrieve data.
• Example: You might create an index on
the "Title" column in the "Books" table to
speed up searches by book title.
• Step 7: Handle Normalization or Denormalization
• Meaning: Depending on performance requirements,
decide whether to normalize (reduce data redundancy)
or denormalize (introduce redundancy for faster
retrieval) the database schema. Normalization involves
organizing data into separate tables to eliminate
redundancy.
• Example: For highly normalized data, you might have
separate "Authors" and "Books" tables. For
denormalization, you might include the author's name
directly in the "Books" table to reduce joins in queries.
• Step 8: Document Your Design
• Meaning: Create documentation that includes the
schema diagram, table descriptions, and
explanations of relationships, constraints, and
indexes. This documentation is essential for
developers and database administrators.
• Example: Use tools like entity-relationship
diagrams (ERDs) or data modeling software to
create visual representations of your database
design
Table:
Books
Field Name Data Type Constraints
ISBN VARCHAR(13) Primary Key
Title VARCHAR(255)
PublicationYear INT
AuthorID INT Foreign Key (Authors)
PublisherID INT Foreign Key (Publishers)
Table: Authors
Table: Publishers
Field Name Data Type Constraints Field Name Data Type Constraints
AuthorID INT Primary Key PublisherID INT Primary Key
AuthorName VARCHAR(100) PublisherNa VARCHAR(10
me 0)
[Link]
hips-physical-database-design
[Link]
esign/#:~:text=Physical%20database%20design%20is%20the,da
tabase%20management%20system%20(DBMS)
.
[Link]
between-physical-and-logical-databases#:~:text=The%20main
%20difference%20between%20logical,during%20the%20logical
%20data%20design.