Entity-Relationship (ER) Diagrams =================================== Entity-Relationship (ER) Diagrams Converting ER Diagrams to relational data model: 1. Convert each entity to a new relation R Map entity keys to key for relation R Map all other attributes to attributes for relation R 2. Convert relationships based on cardinality One-to-one/One-to-many: Map to the entity E1 that has one of the other entity E2 by adding E2's key as an attribute. One-to-one: You can add in either direction, but if one side is 1..1, then use the key for that entity (since it will always have values.) Many-to-many: Create a new relation R: include in R the keys of all joining entities. The key must include the key of all entities that have an N participation. -> If relationships had attributes, then add these as attributes to the relation that this relationship was mapped to. ---------------------- Students(RIN, Name, Class, Email) Key: RIN Classes(CN, Semester, Year, Section, CourseCode) Key: CRN Major(Name, offeredby_dcode) Key:Name Departments(dcode, name, phone, location, head_RIN, headStartDate, termLength) Key: dcode FacultyStaff(RIN, Name, Email, Title, WorkIn_dcode, family_RIN) Key: RIN Courses(code, title) Key: code StudentsHaveMajors(RIN, MajorName) Key: RIN, MajorName TakesClasses(RIN, CRN, Grade) Key: RIN, CRN AdvisedBy(StudentRIN, FacultyRIN, MajorName) Key: StudentRIN, MajorName ---- Close up on participation constraints: -------------------------------------- Ternary relationship: AdvisedBy(StudentRIN, MajorCode, FacultyRIN) Key: StudentRIN, MajorCode Students Major -> Faculty Binary Model: R1(StudentRIN, MajorCode) R2(StudentRIN, FacultyRIN) R3(FacultyRIN, MajorCode) Decomposition! S F M s f1 m s f m2 s3 f m Cannot apply s m -> f Lossy decomposition: representing ternary relationship in three binary relationship does not give me the same exact result! ---------------- Alternate model: Ternary relationship: AdvisedBy(StudentRIN, MajorCode, FacultyRIN) Keys: StudentRIN MajorCode, StudentRIN FacultyRIN Students Major -> Faculty Faculty -> Major Binary Model: R1(StudentRIN, MajorCode) R2(StudentRIN, FacultyRIN) R3(FacultyRIN, MajorCode) Decomposition! S F M s f1 m s f m2 s3 f m f -> m S F M s f1 m s f m <-- lossless decomposition s3 f m ClimateData MeasurementType key: mtype, unit Stations: key:id, latitude, longitude Measurements: key: id, value Convert to relational data model: Measurements(id, value, timestamp , takenby_stationid , recordedby_deviceid , arefor_measurementtypeid) key: id MeasurementTypes(mtype, unit, parent_mtype) key: mtype Stations(id, lat, long, altitude) key: id Devices(id, name, precision, stationid) key: id Stations may instruments for different measurement types ------------------------- NetFlix Pictures: (Id), pictureFile TVShows: (Id), Name, ReleaseYear, ChannelName, Description Episodes: (Id), SeasonNumber, Number, Name, ReleaseDate Movies: (Id), Name, ReleaseYear, Description Genres: (Name) Actors: (Id), Stage Name, Real Name, DateofBirth Subscribers: (UserId), Email, Password, CCNo, CVC, Address, LastPaymentDate Profiles: (ProfileName), WhenCreated, ProfilePicture ProfileRatings: (id), rating ProfileWatchHistory: (id), date ProfileWatchList: (id), dateadded, rank ------------ Revisiting Entities: ----------------------- Multi-valued attributes Weak entities Example: ------------ Albums have: multiple genres multiple styles multiple userratings multiple similar albums multiple releases (each with year/catalog/format) multiple tracks (each track, 1,2,3,... features one song) Weak Entities ----------------------------- Drawn with a box with two lines around it: A weak entity has a key and a set of attributes. The key of a weak entity is not guaranteed to be unique in the database. A weak entity depends on one of more entities with a many-to-one relationships called supporting entities. The key of all supporting entities combined with the key of the weak entity is guaranteed to be unique in the database. The supporting entities connect to the weak entity by supporting relationships, drawn with two lines around them. Each weak entity and all supporting relationships together are mapped to a new relation. The key of the relation is the combination of the weak entity key and the key of all the supporting entities. Track as weak entity (model 2): Track(AlbumId, TrackNo, duration, notes, songid) Key: AlbumId, TrackNo Track as strong entity (model 1): Track(Id, TrackNo, duration, notes, songid, albumid) Key: Id Entity Hierarchies -------------------- Type hierarchy: Any subclass will inherit the attributes of the super class Instance Hierarchy: All capitals are in Cities. Objects in the subclasses also belong in the parent hierarchy. Movies union TVShowEpisodes = ? = AllMedia No...: the hierarchy is covering Movies intersection TVShowEpisodes = emptyset Yes: the hierarchy is disjoint Converting Entity Hierarchies to Relational Data Model ========================================================= Given a parent entity and subclass entities: Option 1: ----------- Convert each subclass entity to a separate relation (if the hierarchy is covering, then the parent entity does not need a separate relation) Cities(id, name, population) Capitals(id, name, population, country) Movies(id, mediafile, title) TVShowEpisodes(id, mediafile, title) Pictures(id, image, movieid, tvshowepisodeid) PicturesM(id, image, movieid) PicturesTV(id, image, tvshowepisodeid) Students(RIN, name, address, email, class) Faculty(RIN, name, address, email, title, rank) Staff(RIN, name, address, email, title) Pros? Precise! Cons? Multiple entities copy the same information ------------- Option 2: --------- Convert the parent entity to its relation and then create a new relation for each subclass, only listing the unique attributes Cities(id, name, population) Capitals(id, country) Media(id, mediafile, title) Movies(id) TVShowEpisodes(id, seasonNo, showName) Pictures(id, image, mediaid) People(RIN, name, address, email) Students(RIN, class) Faculty(RIN, title, rank) Staff(RIN, title) Pros? No repeated tuples Cons? Need a lot of joins to get the same info Option 3: ---------- Flatten and create a single entity for the whole hierarchy. We need an attribute(s) to identify the class(es) the entity belongs to. Cities(id, name, population, country, isCapital?) Media(id, title, mediafile, mediaType(isMovie), seasonNo, showName) disjoint People(RIN, name, address, email, class, title, rank, isStudent, isFaculty, isStaff) not disjoint Pros? No additional joins Cons? Waste space with lots of empty attributes Not elegant: not modular --------- Design Principles: - Merge entities that are very similar into a single one - Break apart entities that are different (different relationships or attributes) - If information is present from other part of the ER diagram, do not add them - Only represent the information that is necessary Content to review today: --------------------------- Hierarchies Weak Entities ----- Albums(Id) key:id Track as strong entity Tracks(Id, trackno) key: id Track as weak entity: Albums(Id,Title) key:id Tracks(AlbumId, TrackNo, notes, duration, songId) Key: AlbumId, TrackId Song(Id, Name) Key: id Potential weak entities: -> Episodes (supporting entity: TVShows), key: SeasonNo, EpisodeNo -> ProfileListItems (supporting entity: Profile), key: rank -> Profiles (supporting entity: Subscribers), key: name -> Pictures (supporting entity: Media), key: ???? No natural way to map to weak entities ------- Convert the new ER diagram to relational data model ------------------------------------------------- Media((mediaid), name, releaseyear, desc) Movies((mediaid)) TVShows((mediaid), channel) Genre((name)) MediaHasGenre((mediaid, genrename)) Pictures((id), file, mediaid) Actors((actorid)) StarsIn((actorid, mediaid) Subscribers((userid)) Profiles((userid, name)) ProfileListItems((userid, name, rank), mediaid) Episodes((mediaid, seasonno, epno), ...) WatchEpisodes((userid, name, mediaid, seasonno, epno), date, minutes) WatchMovies((userid, name, mediaid), date, minutes)