Please enable JavaScript.
Coggle requires JavaScript to display documents.
Relational Database Design - Coggle Diagram
Relational Database Design
entity
primary key
composite key
candidate key
is unique column, like primary key
alternative key (AK) is candidate key that is not primary key
foreign key in primary key
this entity type is
weak
, otherwise
strong
this is
identifying relationship
subtypes entity
mutual exclusiveness
artificial types
relationship
cardinality
one-to-may
child (max cardinality: 1) - parent (max card: many)
min cardinality: parent (1 parent without a child is not a parent lol), child (0 optional)
one-to-one
child (max cardinality 0 or 1) - parent (max card: 1)
many-to-many
recursive
is about entity type relation
to itself
arity
binary arity (2) when there is relationship between
two
entity types
ternary arity (3) relationship between
three
entity types
nominalization or objectizing
creating another entity type
ER model
Data model
facts (about this world)
rules for duplicates
template (<Member> plays <type of Sport>)
fact type
entity type
strong weak
key attributes
Creating ER model
gather info
analyzing mission statement
Highlight with marker:
Facts
constrains of facts
examples
Other
generalizing facts
placeholders
find entity types in facts
create template for facts types
Merits of ER model:
It's very easy to read
Cons of ER model:
Not enough details for implementation.
ER to Rational:
Entity types to Tables
attributes and keys to primary key or unique and columns
Relationship to foreign keys (one-to-one, many-to-many, one-to-many)
Relational model
Constrains
Check operator
Relational is not very easy to read,
but sufficient with details
Rational to ER:
Tables to Entity
Create relationships (and optimize it if possible like many-to many)
Normalization
Why to use?
prevents Non atomic values
prevents redundant elements
prevents modification anomalies
How to normalize
Apply normal forms
normal form of database = lowest normal form of all tables
First normal form (1NF) requirements:
Table must have a key
Every column stores
atomic
value
Second normal form (2NF) requirements:
All req of 1NF
Any non key attribute mustn't functionally depend on subset of composite key
Third normal form (3NF)
All req of 2NF
Non-key attribute is not functionally depend on non-key attribute
Functional dependencies
Func(A)->B = Attribute B functionally depends on Attribute A
Func(Name)->Date of birthday, By Name we can get Date of birthday or nothing
In this example: A is
Determinant
, B is
Dependent Attribute
Functional dependency is when we can convert one attribute to another attribute or non attribute
Properties of functional dependencies:
Can be mutual
a. most are not!
Can be on a combination of two or more attributes
a. Dave(Name) Smith(Last name) -> December 12 1982
Depend on "Universe of Discourse"
a. It depends on context, beware when making assumptions!
If X functionally depends on Y, then X functionally depends on superset of Y
a. Dependency on two or more attributes can sometimes be reduced
b.
Full
dependency: Functional dependency that cannot be reduces
Every attribute depends on itself (and on each superset of itself) (Dave->Dave)
a. it is called
trivial
dependency
Finding functional dependencies
part 1: Are candidate keys minimal (full dependency)
part 2: Missing candidate keys
part 3: Dependencies on subset keys
part 4: Verify 3NF
Further readings:
https://en.wikipedia.org/wiki/Database_normalization