Lecture 8 - Entity-Relationship Models

Lecture 8 - Entity-Relationship Models#

Announcements#

  1. Exam #1 is October 1st (next thursday), at 2pm.

    • Open book and notes, but nothing electronic.

    • Covers everything we have done.

    • If you need accommodation, this is your last chance to contact me.

  2. Lecture Exercise 8 is out later today, due on monday at 2pm.

  3. Homework #2 is due this friday at midnight.

Today’s topics#

  • Entity-Relationship Modeling

Entity-Relationship Models#

  • Entities are the main class of objects that we will store information about

    • Each entity has a name

    • A set of attributes, the information about that entity where

      • Each attribute should be atomic (i.e. simple valued)

      • Each attribute should be about that entity directly

    • Each entity should have a key, a set of attributes that imply all the other attributes (so entities should be in BCNF)

  • Relationships connect two or more entities.

School Example

World Cup Soccer Example

Converting ER Diagrams to Relational Data Model#

  • Map entities to relations

    • Key of the entity is a key to the relation

    • All attributes of the entity is mapped to the relation

  • Map relationships

    • One-to-one relationships

      • Map the key of one of the entities as an attribute in the other

    • One-to-many relationships

      • Map the key of the entity on the many side as an attribute in the other (see course notes)

    • Many-to-many relationships

      • Create a new relations which has all the keys of the connecting entities and the combination of these keys is the key

  • Ternary relationship: depends on the cardinalities. See course notes

Tournaments(Year, StartDate, EndDate)

Matches(Id, Type, Date, Location, TournamentYear, Team1CountryName, NumGoalsTeam1, Team2CountryName, NumGoalsTeam2)

Teams(CountryName, Uniform1, Uniform2)

Players(Name, Position, IsCaption, DoB)

Goals(Id, TimeScored, IsPenalty, MatchId, PlayerName, ForTeamCountry)

ParticipateIn(TournamentYear, CountryName)

PlayedIn(PlayerName, MatchId, nummins)

MemberOf(TournamentYear, PlayerName, TeamCountry)

MemberOf: Tournament, Team, Player

Tournament Player -> Team

ScoredBy:

Goal -> Player Team

Example of 1-1 relationship

ChairOf:

Faculty -> Deptforwhichtheyarechair

Dept -> Facultywhosischair

Can be mapped to either faculty or department, but it is better to map to department because for a given department, there is always a chair (so the corresponding attribute will always have a value)

Faculty(…, deptiamchairof… dept(…, chairrin…

Contents