The goal of the relational database design is to create a database in a specific database management system that allows us to
Store information
Minimize unnecessary redundancies
Search information easily
Design database can be split into multiple phases.
Conceptual design
Focus on describing data and their relationships.
The outcome of this phase is an ER diagram.
It provides a graphic representation of the database design.
However, designing an ER diagram can be very subjective. Then, the next phase is optimizing the ER diagram using functional dependencies and normal forms.
Optimization
Optimizing the ER diagram using functional dependencies and normal forms.
Convert ER diagram to implementation data model
This phase is to convert an ER diagram to the implementation data model of the database system that will be used.
Logical design
Relational schemas will be produced.
The relational schemas of a database are called logical view.
Physical design
Designers will implement the relational schemas in the database management system using a particular data definition language.
Entity Set
The entity-relationship model contains three basic concepts:
entity sets
relationship sets
attributes
An entity is a “thing” or “object” in the real world, which is distinguishable from all other objects.
An entity set is the class or type of objects in our model.
Attribute
An entity can be described by a set of properties.
Each property is an attribute of the entity.
A set of attributes describes and distinguishes the entities in the same entity set.
One entity can have different attributes in different models for different applications.
Each entity has a value for each of its attributes.
The Domain of an attribute is the set of all possible values of the attribute.
ER-Diagram
rectangles represent entity sets
ellipses represent attributes
keys are underlined
lines link attributes to entity sets
a relationship set is denoted by a diamond
Key
The set of special attribute(s) is called key.
Formally, a key of an entity set is a set of attributes that can uniquely identify the entities.
Two entities are identical if and only if they have the same value for the key.
Multi-valued Attribute
An entity may have multiple values in an attribute which is multi-valued attribute, denoted by double ellipses.
It is possible that one student may have multiple phone numbers.
Composite Attribute
One may ask “why the course code is not selected as a key?”
The reason is that a course code is not atomic.
An attribute is atomic if each value of the attribute has only one unit of information.
If an attribute is not atomic, it is a composite attribute.
Relationship
A relationship set is a set of relationships of the same type.
Sets do not allow duplications.
This is also applied to entity sets.
Multi-ary Relationship
If a relationship associates 𝑛 entities, this relationship is 𝒏-ary.
𝑛 is the degree of the relationship
If 𝑛 = 2, the relationship is binary.
If 𝑛 = 3, the relationship is ternary.
Theoretically, 𝑛 can be any positive integer.
Attributes for Relationship Sets
The relationship set can also have some attributes.
Example:
Sometimes people are also interested in some information about relationships.
In the “students borrow books” example, we also want to know when the book is borrowed, when the book is returned, and how long the book is kept by the student.
This information does not belong to students or books. It is about the association.
Derived Attributes
In the previous example, the attribute duration is in a dashed ellipse because it is a derived attribute.
If one knows the date of borrow and the date of return, then the duration can be calculated from the two values.