DATABASE
DESIGN
INTRODUCTION:
IDENTIFYING ENTITIES TO BE REPRESENTED IN THE
DATABASE AND REPRESENTATION OF HOW THOSE ENTITIES ARE
RELATED.
A BLUEPRINT THAT DESCRIBES HOW DATA IS STRUCTURED
AND CONNECTED IN A DATABASE.
Design phases:
Requirement analysis.
Conceptual database design
Logical database design
Schema refinement
Physical database design
Application & security design
ER MODEL
OVERALL LOGICAL STRUCTURE OF DB
1) ENTITY SETS
STUDENT
2)RELATIONSHIP SETS
3)ATTRIBUTES
TYPES OF ATTRIBUTES
• Simple Attribute
• Composite Attribute
• Single-Valued Attribute
• Multi-Valued Attribute
• Derived Attribute
Simple attributes
Basic attributes that hold a single value that cannot be broken
down further .
Composite attributes
split into components is a composite attribute.
Single & Multi valued Attributes
Derived attributes
attribute that can be derived
from other attributes is derived attributes .
Mapping Cardinality
defines the relationship between entities
specifying how many instances of one entity can be linked to
instances of another entity.
One-to-One: Each entity in one set is associated with exactly one
entity in the other set.
One-to-Many: One entity in one set can be associated with
multiple entities in the other set, but each entity in the other set is
associated with only one entity in the first set.
Many-to-One: Multiple entities in one set can be associated with
one entity in the other set.
Many-to-Many: Each entity in one set can be associated with
multiple entities in the other set, and vice versa.
Types
One-to-One:
Each entity in one set is associated with exactly one
entity in the other set.
One to many
Many to one
Many to many
ER – Diagrams:
graphical representation of the conceptual design of a database.
gives a standard solution for visualizing the data logically.
BINARY & TERNARY
RELATIONSHIPS
Binary Relationships:
A relationship that connects two entities
Ternary Relationships:
Weak Entity & Strong Entity
Strong Entity
does not depend on other Entity in the Schema.
it is represented by a rectangle.
primary key, that helps in identifying it uniquely
Weak Entity
cannot identifying it uniquely.
Enhanced Entity-Relationship
(EER
an extension of the original Entity-Relationship (ER)
model
Generalization:
process of extracting common properties from a set of
entities and creating a generalized entity from it.
Specialization:
an entity is divided into sub-entities based on its
characteristics.
It is a top-down approach where the higher-level entity is
specialized into two or more lower-level.
Generalization &
Specialization
Constraints:Generalization &
Specialization
constraints ensure the relationships between super classes and
subclasses are well-defined, with the primary constraints
Types:
Membership constraints
Disjoint constraints
Completeness constraints
Overlapping constraints
constraints Types:
Member
where a subclass can have constraints on its membership in the
superclass.
Disjoint:
subclasses are disjoint, an entity can only belong to one of them.
Completeness:
constraint determines if a superclass instance must also be a member
of at least one subclass
Overlapping
higher level entity may belong to more than one lower-level entity set
Aggregation
Not capable of representing the relationship between an entity.
abstraction through which we can represent relationships as
higher-level entity sets.
