Riyadh Metro is considered to be one of the leading project in the region. It connects the city districts through a huge transportation system that contains metros and buses. Riyadh citizens will be able to travel around the city easily. For more information about the project visit the website http://riyadhmetro.sa/en/.

You are part of a team which is responsible for application design and development of a tracking and planning system that involves mobile application. The system should provide customers the metro schedule information. Your role is to design and develop a database for the application. The metro system needs to model the following: Trains, Stations, Routes, Tracks, Train schedule. For simplicity you can assume that:

• Only one track exists between any two stations.
• Every train runs every day
• Each train, for each station on its route has a Time in and Time out (NULL if the train does not stop in the station).

Each train must have a name, route color and uniquely identified by an ID. Each station has a name, location and identified by an ID. Each route must have a color, starting and ending stations and a unique ID. A sequence number is needed to connect and order the tracks of each route. Train schedule stores what time a train passes through each station on its route. You can make any other additional assumptions that make sense in the real world.

Question One :

Design an Entity-Relationship diagram for Riyadh Metro database.

Question Two:

Produce a relation schema for the Entity-Relation in Question one that eliminate redundancies and identify the keys and foreign keys.