Recap
- ER Diagram to Relation Implementation
-
- Entities -> Relation
- Attributes -> Attributes in Relation
- Relationships ->
-
- one-to-many => add a column to the entity
- many-to-many => create a new table
- IMPORTANT to identify: primary keys and foreign keys
- see tables with country, child, employee, animal - “born” and “likes”
- cardinality examples with arrows:
-
Course Structure
- Data Definition
-
- ER Diagram
- -> Relational Implementation (Relational Model)
- -> Relational Algebra => Formal Foundation (basis for querying)
- -> DB (SQL)
- Querying the data
-
How to turn an ER Diagram into a Relation Implementation
- With cardinality
-
-
-
- Problem with arrow notation: we cannot distinguish some cases therefore we use Crow's Feet Notation
- Crow’s Feet Notation (turn on option in MS Visio)
-
-
- Example in Relational Implementation
-
-
- animal to likes:
-
- solid because primary key on the many-side includes the primary key on the one-side.
- => “can’t exist without it”
- country to employee:
-
- dashed because the primary key on the many-side is not in the primary-key on the one-side
- => “could exist without it”
- A Relational Database consists of
-
- a set of tables with identifiers (primary keys)
- a set of many-to-one binary relationships between the tables, induced by foreign keys.
- in other words: it is a set of (in general partial) functions from table to table
- Practical Example using last week’s ER Diagram of the University - how to draw it in MS Visio
-
- we need to specify
-
- attributes that must not be NULL - written in bold letters
- Primary keys - PK
- Keys (beyond primary) - nothing specific
- Foreign keys and what they reference - FK, if more add a number e.g. FK1, FK2
- Cardinality constraints - Crow’s Feet Notation
- Relational Lines
-
- “can’t exist without it” - solid line
- “could exist without it” - dashed line
- ISA relation (circle with long line under)
-
- Some explanations to the Relational Implementation example
-
- car type - we do not need to add an additional table because it is an one-to-many relationship, instead we add foreign keys into the car table
- Prereq is a recursive relationship - two lines to identify the 2 courses, and also 2 FK (first, second)
- Required - FK ID# could reference person, but as only professors can decide which books are required we draw the line directly to professor