Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Nyc Subway System E/r Diagram


  • Please log in to reply
4 replies to this topic

#1 jwxie518

jwxie518

    Speaks fluent binary

  • Senior Member
  • PipPipPipPipPipPip
  • 517 posts

Posted 14 May 2012 - 09:15 PM

The following are the schema provided, and after each I provide the sample data. The objective is to design a good database that clearly shows the relationships among different entity sets
(1)


Station(SationId, StationName, Line, Division, Latitude, Longitude)
http://goo.gl/EiauX

(2)


ControlRemote(ControlAreaUnitId, ControlArea, RemoteUnit, StationId, LineName)
http://goo.gl/Kyxph

(3)


ControlSCP(ControlAreaUnitId, SCP)
Relationship b/w control areas and turnstiles: http://goo.gl/QBzDg

Basic outline:

  • 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
Here is my diagram. Posted Image
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?

Question

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.



Thanks.
  • 0

#2 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 15 May 2012 - 12:03 AM

I believe you are lacking some things here.
  • "Line" would probably be an entity, as the first statement claims. a line consists of one or more stations, and a a station can belong to one or more lines.
  • I believe that a train usually runs on a line, and is therefore connected to line, rather than station.
  • The connection between Station and Control area is lost in your graph, as it connects via Remote Unit. The statements never say that a station is connected to a remote unit, but to one or more control areas.
  • The name SCP Data is apparently not named in the statements, it's called Turnstile, being identified with a SCP id together with the control area id, so it's a weak connection as you say, a Turnstile can't exist without a control area.
  • To be a complete ER diagram, as far as I know, the cardinalities needs to be put there to be complete.
  • In my experience, the Rhombuses shall contain a verb describing the relation between the connecting entities.
This are some advice to you. There might be more, and there might be different systems used to create ER models, but the most of them I've used work the same, and not like yours
  • 1

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#3 jwxie518

jwxie518

    Speaks fluent binary

  • Senior Member
  • PipPipPipPipPipPip
  • 517 posts

Posted 19 May 2012 - 06:39 PM

Hi Orjan,

Thanks. I've updated my E/R diagram
http://i.stack.imgur.com/8YzQ9.png

Advices number 1 and 2 are not applicable because my application do not need them. So we can safely disregard those relations.


I think these are my relations.

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)


Should I generate a table for the red rhombus?

Thanks,



These are my SQL statements (mysql)


CREATE TABLE Train (
    name    VARCHAR(5) PRIMARY KEY
);



CREATE TABLE Station(
    station_id    INT PRIMARY KEY,
    station_name   VARCHAR(60),
    logitude    DECIMAL(8,5),
    latitude	 DECIMAL(8,5)
);


CREATE TABLE TrainManager(
    train_name    VARCHAR(5) REFERENCES Train(name),
    station_id	  INT REFERENCES Station(station_id),
    UNIQUE (station_id)
);


CREATE TABLE RemoteUnit(
    remote_unit_key VARCHAR(10) PRIMARY KEY
);




CREATE TABLE StationControlManager(
    station_id INT REFERENCES Station(station_id),
    ctrl_area_unit_id INT REFERENCES ControlArea(ctrl_area_unit_id),
    UNIQUE(station_id, ctrl_area_unit_id)
);


CREATE TABLE ControlArea(
    ctrl_area_unit_id    INT UNIQUE,
    ctrl_area_key	  VARCHAR(10) UNIQUE
);


CREATE TABLE ControlManager(
    remote_unit_key    VARCHAR(10) REFERENCES RemoteUnit(remote_unit_key),
    ctrl_area_unit_id    INT REFERENCES ControlArea(ctrl_area_unit_id),
    ctrl_area_key	  VARCHAR(10) REFERENCES ControlArea(ctrl_area_key),
    UNIQUE (remote_unit_key, ctrl_area_unit_id, ctrl_area_key)
);

CREATE TABLE Turnstile(
    scp_code    VARCHAR(20),
    ctrl_area_unit_id    INT REFERENCES ControlArea(ctrl_area_unit_id),
    ctrl_area_key	  VARCHAR(10) REFERENCES ControlArea(ctrl_area_key),
    date DATE,
    time time,
    descn BIT(1),
    entries_n BIGINT UNSIGNED NOT NULL,
    exists_n BIGINT UNSIGNED NOT NULL,
    UNIQUE (ctrl_area_unit_id, scp_code, date, time)
);

  • 0

#4 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 20 May 2012 - 12:39 AM

No, the red rhombus is weak, and is implemented as you did in the turnstile table, as a foreign key.A turnstile can not exist without a control area, if I interpet the statements correctly, and then turnstile id and control area id is together the primary key of that table.
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#5 jwxie518

jwxie518

    Speaks fluent binary

  • Senior Member
  • PipPipPipPipPipPip
  • 517 posts

Posted 20 May 2012 - 01:45 AM

Ah. Yes. You are right. I made a mistake. I thought turnstile = turnstile data. In fact, I need another relation.
Thanks.
  • 0




Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download