CS307-Assignment 1 Database Design Solved

As we know, the China's rail network is complex, more than 5000 station distribute in 657 citys and connected by 140000km railway. There are so many passengers take the train each day and processing these informations is a real challeng. You, a SUSTech student who want to improve the experience of 12306 and refactor their database.

BEFORE START                                        
 

Although there are standards, database designing is still a highly subjective intellectual activity. As graders, we will set some rules for grading, but as long as your design can satisfy our requirements, you should be able to get the points.

Since table name and field name is case-insensitive, it is recommended to use underline “_” to separate words, instead of using camel naming.(e.g. Use my_great_table ,

  some_informative_field instead of MyGreateTable or SomeInformativeField .)

Example fields are only for reference. You may create new fields and combine/separate these fields to simplify your design, but the information needs to be remained at least.

DETAILS ABOUT THE RELATIONSHIPS    
 

Store data about rail lines and rail station in an organized and easy-to-maintain manner (see the keywords below).

 City and Station

A city have multiple rail stations A city can have no stations A station must be in a city. Train and Seat

Different trains have different seat count and different seat type.

 Price of different seat type in the same train is different and the number of remaining tickets should be calculated dynamically. Ticket

A concrete train in concrete date. For example, "G74" in Feb. 28th or Feb. 29th.

Needs arrive station and depart station, and their datetime. Need seat type... or other you think is necessary

The user must record it's ID card, the ID card number may have a 'x' in its last digit. Needs phone number Order

            The Order should record user, create date, order status, train num, the depart city, arrive city and price, etc. Other General Requirement:

The passengers can get on or get off the train in each station where the train will stop.

The ticket should be allocated to the city not the train, for example, there is a train from

Shenzhen to Guangzhou, which will stop 10 min at Dongguan, the left tickets from

Dongguan to Guangzhou should be larger than the left tickets from Shenzhen to Guangzhou.

   In each train, such as "G74", we can find all passing stations in it, and for one station in this train, we can find its former and next station from your design. For example, the former station of "WU HAN" in "G74" is "YUE YAND DONG".

 From your design, we can easily get all information as the graph below

 Design a database allowing to manage all information mentioned above in this document, and contains all fields in the table below.

 Your design needs to follow the requirements of the three normal forms

Use primary key and foreign keys to indicate important attributes and relationships about your data

   Every row in each table should to be uniquely identified by its primary key.(You may use simple or composite primary key).

        Every table should be involved in a link. No isolate tables included.

(ํᘏ౲҅Ძक़ํᥝᤒӻྯٌ՜ᤒጱक़Ძ೰ݻ)

        Your design should contain no circular links

ොᲫक़ጱᳵԏᤒԭ੒ҁݻ҅ӧᚆํሾ҂

   Each table should always have at least one mandatory (“Not Null”) column(including the primary key but not the id column)

        Table with only one column is not allowed(Not include the id column).

ࣁਂᚆӧҁݝํӞڜጱᤒ҅ӧ۱ތԆᲫid҂

   Tables with no other unique columns than possibly a system-generated ID is not allowed. ҁᴻԧԆᲫᛔीጱidํᥝᵱ҅क़ԏٌ՜uniqueጱ๳ᕅڜ҂

        Use appropriate types for different fields of data

       You need to use relative regular name format so that we can understand it easily. For instance, you need to use the field names mentioned in the table below unless you decide to split or merge them. The names of relationship tables need to reflect the related entities, etc   Arrange your model diagram in a way that helps understanding your design.

Fields must contain

Field
Explanation
Example
city_name
Name of city
Shenzhen, Beijing
station_name
Name of rail station
Shenzhen North station
depart_time
Time to leave the station(For each station)
2020.02.29-18:00:21
arrive_time
Time that will arrive the station(For each station)
2020.02.29-18:00:21
depart_station
The start station of the train
Shenzhen North station
arrive_station
The destination station of the train
Shenzhen North station
ticket_entrance
The ticket entrance is where to check your ticket
2A, 3B, 16A
seat_type
The type of the seat
Hard seat, Soft sleeper
rest_ticket
The rest of the ticket
32
ticket_price
The Price of the ticket
1145.14
create_date
The date that create the order
2020.02.29-18:00:21
order_status
The status of the order
0, 1, 2(correspond to multiple states)
order_price
The price of the order
1145.14
username
Name of the user
Shiyi Chen, Xin Yao
phone_number
phone number of user
13899998888
ID_card_num
The ID card number of user
11111120000229111x