Code transcripted from the YouTube tutorial: SQL Tutorial - Full Database Course for Beginners
ER = Entity relationship
- Entity (square):
An object we want to model & store information about. - Attribute (oval):
Specific information about an entity. - Primary key:
An attribute which uniquely identifies each entry in the table. - Composite attribute:
An attribute which can be broken up into several sub-attributes. - Multi-valued attribute
- Derived attribute:
An attibute which can be derived from the other attributes. - Multiple entities
- Relationship (diamond):
- Total relationship (double lines):
All members must participate in the relationship. - Partial relationship (single line)
- Total relationship (double lines):
- Relationship attribute:
An attribute about the relationship. - Relationship cardinality:
The number of instances of an entity from a relation that can be associated with the relation. (1:1, 1:N, N:M) - Weak entity:
An entity that cannot be uniquely identified by its attributes alone. - Identifying relationship:
A relationship that serves to uniquely identify the weak entity.
- Mapping of regular entity types
For each regular entity type, create a relation (table) that includes all the simple attributes of that entity. - Mapping of weak entity types
For each weak entity type, create a relation (talbe) that includes all the simple attributes of that weak entity. The primary key of the new relation should be the partial key of the weak entity plus the primary key of its owner. - Mapping of the binary 1:1 relationship types
Include one side of the relationship as a foreign key in the other. (Favor total participation) - Mapping of the binary 1:N relationship types
Include the 1 side's primary key as a foreign key on the N side relation (table). - Mapping of the binary M:N relationship types
Create a new relation (table) whose primary key is a combination of both entities' primary keys. Also include any relationship attributes.