Database Design Concepts

Conceptual Design

• This stage provides a high-level view of the system and its

data requirements.

• The focus is on what data should be stored, not on how it

will be stored.

• The goal is to ensure that the design reflects real-world

business or user requirements.

• Entity Relationship Diagrams (ERDs) are commonly used to

represent the model.

Logical Design

• This stage translates the conceptual model into a detailed

relational schema.

• Logical design ensures data integrity and consistency

across the system.

• Normalization techniques are applied to organize data

and reduce redundancy.

• At this stage, the design remains independent of any

specific database software.

Physical Design

• This stage implements the logical design in a specific

DBMS such as MySQL, Oracle, or PostgreSQL.

• The design considers database performance, security,

and maintenance needs.

• Backup and recovery strategies are also planned at this

stage.

• Unlike the logical stage, this step is tied to hardware and

system requirements.

Database Design Process

Conceptual Design → Logical Design → Physical Design

Entities

• are the things about which we store data.

• “Something” of significance to the business about which

data must be known.

• A name for a set of similar things that you can list.

• Usually a noun and have Instances.

• Instance is a more specific example of an ______.

Entities can be:

• Tangible, like PERSON or PRODUCT

• Intangible, like SKILL LEVEL

• An Event, like CONCERT

Attributes

• Like an entity, an ____ represents something of

significance to the business.

• An ____ is a specific piece of information that helps:

• Describe, Quantify, Qualify, Classify, and Specify an Entity.

• An _____ has a single value that can be a number,

character string, a date, an image, a sound, etc.

Attributes

These are called “data types” or “formats.” Every attribute

stores one piece of data of one specific data type.

Volatile Attributes

Some attributes (such as age) have values that constantly

change.

Non-Volatile Attributes

Other attributes (such as order date) will rarely change, if

ever.

Mandatory Attribute

Must CONTAIN a value

Optional Attribute

may contain a value or be left null.

Identifiers

• A unique attribute or a set of attributes that

distinguishes one entity from another.

• It serves as a key element in maintaining data integrity

and ensuring accurate data retrieval.

• Critical in database design because they

allow relationships to be formed and maintained between

entities.

Primary Identifier

The unique attributes used to help distinguish one instance from

another. Commonly known as Unique Identifier (UID)

Secondary Identifier

Additional or alternative attributes used to supplement the primary

identifier.

Composite Identifier

A unique identifier formed by combining two or more attributes.

Natural Identifier

An attribute that already exists in the real world and uniquely

identifies an entity.

Artificial Identifier

A database-generated attribute used to uniquely identify an

entity when no natural identifier is available or practical.

Entity Relationship Modeling

• A theoretical framework or methodology used to describe data and their relationships conceptually.

• Defines the rules, principles, and concepts (entities, attributes, relationships, data types, constraints, etc.).

• “Implementation-Free Model”

Entity Relationship Diagram

• A visual representation or diagram of the ERM.

• It uses symbols and notations (Crow’s Foot, Chen, Barker’s, etc.) to illustrate entities and their relationships.

ERD has 3 components:

Entities, Attributes, Relationships

ERD has 3 types of notations

Chen, Barker, Crow’s Foot

Entity

• Must have a name (UPPERCASE)

• Will contain attributes

• Will have at least 1 relationship

Attributes

They have values and types.

Attribute - Barker’s

• Unique Identifier: #UID is an attribute whose value uniquely identifies an entity instance. Implemented as a Primary Key

• Mandatory Attribute: *a mandatory attribute is one whose value cannot be null.

• Optional Attribute: o an optional attribute is one whose value can be null.

Relationship

• The way in which two or more people or things are connected.

• Relationship have Optionality and Cardinality

Relationship - Barker’s

• Optionality

• Mandatory: represented by a solid line specifying that each instance from an entity MUST be related to another

instance.

• Optional: represented by a dashed line specifying that each instance from an entity MAY be related to another instance.

Cardinality

• Measures the quantity of something

• Ask the question how many?

ERD Drawing Conventions

• It is efficient to communicate information in a way that can easily be understood by many.

• ERD Drawing Convention follows a set of rules. An international standard.

• Entity

• Attribute

• Relationship

Steps for ERD Drawing Conventions

Step 1: Define all entities and draw them using Barker’s
Step 2: Define all attributes for both entities
Step 3: Define the relationship between entities

Step 4: Define the relationship between entities
Step 5: Define the relationship name/label.

ERDish

is the language we use to state relationships between entities in an ERD

ERDish Syntax

EACH <Entity A> <must/may> <relationship>

<one and only one/one or more> <Entity B>