Lecture 7

Announcements
--------------

- Please fill the Google form for exam timing. If you do not fill
this form, I assume you are taking the exam during regular class
time: 2:30PM-4:20PM EST October 1, 2020

  https://forms.gle/CiT7p82sauxYqDSy5


- Next set of lectures:
  - thursday: more ER
  - next monday: no more recorded lectures:
       review session: 2:30PM-4:20PM
  - next thursday: exam

- Hw#3 due next monday


Potential Databases:
---------------------
Movies
AirBnb
Lyft
NetFlix
ClimateData
Instagram
Olympics
RickandMorty
Jeopardy
HeroUniverse

-------


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

** Trailers?


Pictures((Id), pictureFile, MovieId, TVShowId)

TVShows((Id), Name, ReleaseYear, ChannelName, Description)

Episodes((Id), SeasonNumber, Number, Name, ReleaseDate, TVShowId)

Movies((Id), Name, ReleaseYear, Description)

Genres((Name))

Actors((Id), Stage Name, Real Name, DateofBirth)

Subscribers((UserId), Email, Password, CCNo, CVC, Address, LastPaymentDate)

Profiles((Id), ProfileName, WhenCreated, ProfilePicture, UserId)

TVWatchList((id), dateadded, rank, TVShowId, ProfileId)

MovieWatchList((id), dateadded, rank, MovieId, ProfileId)

MovieStars((MovieId, ActorId))
TVStars((TvShowId, ActorId))
MovieGenres((MovieId, GenreName))
TVShowGenres((TVShowId, GenreName))

MovieRating((MovieId, ProfileId), rating, rdate)
TVShowRating((TVShowId, ProfileId), rating, rdate)

MovieWatched((MovieId, ProfileId), minutes, wdate)
TVWatched((EpisodeId, ProfileId), minutes, wdate)

------------

We will revisit this model in Lecture 8, especially ratings and movielistitems