I have written a blog on the original data model design of our plug-in, but more improvement has been made later. I’d like to summarize the improvement here.

Improved E-R Model and Normalization

E-R model

After group discussion, some improvement has been made to the original model. A new version of E-R model was designed. The new one is much simpler than the original one because there is no attributes concerning participation, instead, all those information stored in participation has been shown in the event entity set.

Two entity sets, student and event, are connected by a relationship set participation. An entity in student denotes a student in our department. The attribute sid is student ID. Similarly, the other attributes, name, gender, grade, and class, store the basic information of a student. An entity in event denotes a specific sport event, which is distinguished by eid, the ID of the event, defined by the database manager. The other attributes store the information of an event.

Venue: the location of a certain event.

Date: the time of the event.

Epic: the people in charge of the event.

Type: the sport type of the event, such as basketball, volleyball or table tennis.

Spic: the people in charge of the certain type of sport. Usually, the spic is determined by the sport type.

Level: the level of the event, such as a college-level competition like Mayuehan Cup games, or a department-level club activity.

Point: the value of point that awarded to each student who participates in this event. The amount of point of a certain event is determined by the event level. The higher the level of the event, the more points awarded to each participant.

Normalization

Convert the model into a relational model. The student relation has been efficient in space already. We don’t have to normalize it. The participation can be converted into a relation sprt_type.

The event entity set can be converted into a relation called event.

This relation, however, has to be normalized into three tables. Since the sport type determines the people in charge of that certain sport, the type and spic can be organized in a relation called sprt_type. Tid is defined as the ID of a certain type of sport, which is the primary key of the relation. Similarly, sprt_level is a new relation consists of lid, level and point, since a certain level of sport determines the value of point awarded. The columns of type and spic in the original event relation are substituted by the foreign key tid, while the columns of level and point in the original event relation are substituted by the foreign key lid.

Final model

After normalization and denormalization, we finally constructed our data model. There are five basic tables in our database. The schema diagram shows the relationship between the tables and gives the data type of each attribute.

Here is the schema diagram of the database model:

The primary key of each relation is marked in red, while foreign keys in brown. The data type of each attribute is analyzed.

Data type analysis

Student

Sid: The ID of each student consists of ten digits, but another digit is added to the data type to accommodate possible future growth in the number of students.

Name: the name of Chinese students usually consists of two or three characters, but taking international students into consideration, we use char(25) as the data type of the attribute.

Gender: the gender is recorded as either “男” or “女”, so the data type is designed to be char(1)

Grade: ‘grade’ shows the year the students belong to and would be represented by ‘工’ to indicate 工业工程 (Industrial Engineering) followed by the year number, for example ‘工9’ refers to students who are now in year three. Therefore, we chose char(2) as its data type.

Class: ‘class’ represents the particular class a student belongs to. It consists of three digits, for example ‘工01’, hence we set its data type to be char(3).

Sprt_type:

Tid: It has level ID ‘tid’ as its distinguishing primary key of different sport types. Since the number of sport types is no more than 100, the data type is designed as int(3).

Type: represents the different sports such as table tennis, basketball, volleyball, soccer, and a few others. Its data type is char(10).

Spic: is short for sport person in charge. As in the ‘student’ relation, its data type is char(25).

Sprt_level:

Lid: It has level ID ‘lid’ as its distinguishing primary key. There are only three levels of events so far, so we designed the data type to be int (2).

Level: is either ‘校级’, ‘院系级’, ‘俱乐部级’, therefore, its data type is set to be char(4).

Point: is set as an int(2) type, since the number of points given is within a range of one to two digits.

Event:

Eid: since there may be many event recorded, we don’t have to limit the scale. The data type is int.

Date: uses the date data type

Venue: We set its data type to be char(10) to accommodate longer names.

Epic: contains the person-in-charge’s name and is assigned a char(25) data type.

Tid: has the same data type, int(3) as the ‘tid’ of sprt_type.

Lid: has the same data type, int(2) as the ‘lid’ of sprt_level.

Prtp

Pid: An ID is assigned to each instance of participation in the attribute ‘pid’, which is also the primary key of the ‘prtp’ relation. Since there will be large amount of record of participation, there should be no restriction on the scale. The data type is int.

Sid: The foreign key is the student ID in the relation student, so the data type is int(11).