Station(SationId, StationName, Line, Division, Latitude, Longitude)
ControlRemote(ControlAreaUnitId, ControlArea, RemoteUnit, StationId, LineName)
Relationship b/w control areas and turnstiles: http://goo.gl/QBzDg
the system consists of multiple lines (1/2/3, A/B/C, etc) and multiple stations
Subway station usage data is collected by one or more control areas (uniquely identified by a ControlAreaUnitId (artificial key) or ControlArea key.
A group of control areas (one or more) are managed by a remote unit (uniquely identified by RemoteUnit key)
Big stations MAY have multiple remote units
A group of small stations MAY share one remote unit
A control area collects data from one or more turnstiles
A turnstile is identified by SCP code within the control area
The red supporting relation is actually a weak one (I can't find a symbol for it in my drawing tool). SCP Data I mean Turnstile.
What do you guys think?
1. The original schema ControlRemote has StationID. Neither Remote nor ControlArea has a StationID key in it, so I guess the best way to identify is setup a supporting relation StationManager so we can identify which station a remote unit belongs to (and also Control Area).
2. When we convert them into SQL statements, we don't need to generate the red relation right? Since in a weak entity, the one in red is reduantant (many-to-one).
These are the relations I think we need to put into DB
Train(name) Station(station_id, station_name, logitude, latitude) TrainManager(train_name, station_id) RemoteUnit(remote_unit_key) StationRemoteManager(station_id, remote_unit_key) ControlArea(ctrl_area_unit_id, ctrl_area_key) ControlManager(remote_unit_key, ctrl_area_unit_key, ctrl_area_key) Turnstile(scp_code, ctrl_area_unit_id, ctrl_area_key)
3. Do you guys think this is a good design? I feel like having so many extra supporting relations seem unnecessary.