lundi 2 février 2015

Normalizing a MySQL database


I have the following busroutes table and need to find the bus route with the greatest number of stops. However, this table is not normalized - there are multiple routes in an entry. I would like to normalize the database by creating a new table that contains stop_id and routes. Is there a simple approach for extracting routes into a new table? I'm new to normalization so I apologize if this is an elementary question.



+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+
| stop_id | on_street | cross_street | routes | boardings | alightings | month_beginning | daytype | location |
+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+
| 9692 | GUNNISON | MELVINA (west leg) | 81W | 2.4 | 8.4 | 10/01/2012 | Weekday | (41.96853247, -87.78306761) |
| 9693 | GUNNISON | MOBILE | 81W | 1.9 | 7.7 | 10/01/2012 | Weekday | (41.96849308, -87.78544981) |
| 9694 | GUNNISON | NAGLE | 81W | 37.4 | 42.4 | 10/01/2012 | Weekday | (41.96847297, -87.78781359) |
| 9695 | NAGLE | STRONG | 86,91 | 2.9 | 4.4 | 10/01/2012 | Weekday | (41.97012700, -87.78789600) |
| 9697 | NAGLE | CARMEN | 86,91 | 0.2 | 2.8 | 10/01/2012 | Weekday | (41.97374900, -87.78782600) |
| 9698 | NAGLE | FOSTER | 86,91 | 1.5 | 15.2 | 10/01/2012 | Weekday | (41.97556813, -87.78779559) |
| 9700 | NAGLE | BERWYN | 86,91 | 0.1 | 0.7 | 10/01/2012 | Weekday | (41.97739122, -87.78776172) |
| 9701 | NAGLE | BALMORAL | 86,91 | 0.8 | 7.3 | 10/01/2012 | Weekday | (41.97918471, -87.78772953) |
| 9702 | NAGLE | CATALPA | 86,91 | 1 | 4.5 | 10/01/2012 | Weekday | (41.98100361, -87.78769164) |
| 9703 | NAGLE | BRYN MAWR | 86,91 | 2.6 | 148.4 | 10/01/2012 | Weekday | (41.98280027, -87.78762514) |
+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+




Aucun commentaire:

Enregistrer un commentaire