Assignment1 Database Design

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
User
The user must record it's ID card, the ID card number may have a 'x' in its last digit.
Needs phone number
OrderThe 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
RULES
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 dataYou 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
Name of rail station Shenzhen North station
station_name
depart_time
arrive_time
Time to leave the station(For each
station)
Time that will arrive the station(For
each station)
2020.02.29-18:00:21
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 order_price The price of the order 1145.14
Name of the user Shiyi Chen, Xin Yao
phone number of user 13899998888
The ID card number of user 11111120000229111x
username
phone_number
ID_card_num
0, 1, 2(correspond to multiple
states)
Submission
Please submit the following files, with prefix of your student ID:
.mwb file: The default MySQL Workbench model file
.pdf file: Export a PDF file of your modeling to prevent compatibility issues.