Commonwealth Games (CWG) JAVA程序辅导、数据库ER图代画

This assignment consists of design and development of an information system.

The first part of the assignment consists of the design, which includes creating a DFD,

ER diagram, mapping to the relational schema and further normalization of the

relational schema if required. The necessary information for schema design is given

in the Universe of Discourse (UoD) described below. This part can be done in a

group.

In the second part of the assignment, the designed schema will be implemented in

MySQL. The implementation will include creating tables, constraints, uploading or

entering sample data, and writing queries. The details are given below in the

implementation section. This part is done individually by each student.

1 Universe of Discourse

The Commonwealth Games (CWG) is an international multi-sport event that involves

athletes from the Commonwealth of Nations. These games were started in 1930 and

take place every four years. In April this year, the 2018 Commonwealth Games will

be hosted on the Gold Coast, welcoming over 6000 athletes and team officials from

70 Commonwealth nations and territories. The Games will be the largest sporting

event to be staged in Australia this decade and will feature the largest integrated

sports program in Commonwealth Games history.

You have been tasked with the planning and development of a system for the

effective management of the Games. The following description provides a

specification for this system.

There are a number of athletes who participate in the Commonwealth Games. When

athletes are registered in the system, each athlete is given a unique athlete ID. Basic

information about each athlete is also stored, including their full name (first name,

middle name, last name), representing country, gender, date of birth, contact details

(phone number(s), email, and emergency contact (name and phone number)). There

are two categories of athletes, paraplegic and non-paraplegic. For paraplegic

athletes, a list of additional medical details will also be stored.

1

A number of details are kept for each country participating in the CWG.

There are over 20 different sports at the CWG. Every sport also has a world record associated with it. Some are team sports

and others are individual competitions. Some very talented athletes may also

compete in multiple sports during the Games. When athletes are required to

compete in teams, their team name is stored, which may be different from their

country name.

In addition to the basic information on the sports, information for the events

associated with each sport during the games is also maintained. All events are

hosted in at least one venue, and are identified by a unique event ID. They also have

a name, description, and are held on a certain date and time (both the start and end

times are recorded).

Figure 1 shows an example of the schedule for Athletics Final to identify what data

needs to be stored to create event schedules. This snapshot was available from

www.gc2018.com/sport/athletics.

Some events also have sponsors, that is, corporations, groups or individual people

who support the event in exchange for advertising throughout the event. Sponsors

are able to sponsor specific as well as multiple events. Every sponsor is uniquely

named.

Due to the different ways in which

performance in events are recorded, each event can either be timed or scored. For

timed events, the athlete’s/team’s performance time is recorded; whereas for

scored events, the score is recorded instead. Teams and individual athletes with the

best performances will

be awarded medals (gold, silver and bronze).

Venues can host many different types of events. Every venue has a unique name

within the Games. Each venue also has a maximum capacity and location. Each

venue is either indoor or outdoor. For indoor venues, the facilities available (PA

system, Data projection, and Internet) are Boolean values which are recorded. For

outdoor venues, the dimensions (length and width) in meters are recorded instead.

Figure1:SnapshotofanEventScheduleintheGames

2

In order to attend an event, customers need to buy tickets. Each ticket is identified

by a unique ticket ID and has a price associated with it. Most tickets allow access to a

single event; however, some tickets are multi-passes, allowing access to several

events. All events (even free ones) require tickets in order to control crowd numbers.

When a customer buys a ticket, their name and billing address is stored.

2 Schema Design (PART 1 – Group Work Allowed)

The following describes what is expected in the design part of the assignment.

2.1 DFD and ER Diagram

Create a context level DFD. You can take your DFD to your tutorial for feedback

before starting on the ER.

Create an ER diagram using the notation taught in lectures to represent the

conceptual schema described by the Universe of Discourse. For cardinality and

participation dependencies you may use either standard notation, or the alternate

(min,max) notation, but not both. Depending on your design choices, you may need

to use the extended ER diagram.

State clearly any assumptions you make regarding your design approach. Please note

that you cannot make assumptions to simplify or compromise the completeness of

the Universe of Discourse. If there are any points that need clarification, in the

specification of the Universe of Discourse as given above, you must first attempt to

clarify them with your tutor and/or lecturer.

2.2 Mapping

Map the ER diagram created in 2.1 to a relational schema. Document the mapping

steps. The final schema should be given in the notation: R1 (keyattr, attr1, attr2, ...).

In order to identify referential integrity constraints, be sure to either use the same

names of the referenced/referencing attributes or write this separately (e.g. R1(attr1)

references R2(somekeyattr)).

2.3 Normalization

All relations resulting from the ER-Relational mapping should be normalized (if

required) up to the third normal form (3NF). For each relation given in the final

schema from 2.2, show the following:

1. Identify non-obvious functional dependencies

2. Determine if there are any partial/transitive dependencies

3. If necessary, decompose the relation to make 3NF relations

4. Clearly state the normal form of the original relation, and of the resulting

relation(s).

3

Tip: It is likely that the relations in the schema you come up with at the end of the

ER-Relational mapping (see section 2.2) are already in 3NF. However, you still need

to write the (non-trivial) FDs between the attributes of each of the relations you

designed, and demonstrate that the relation is actually in 3NF by running the 3NF

test on it.

Here is an example of what you may document in this part:

R1 (A, B, C, D)

FD: AàA, B, C, D

No partial or transitive dependencies found, R1 is in 3NF