DATA2001 Database Systems

Entity Relationship Diagram

The below is the database entity relationship diagram.

Part A: Analysis and Design

Client Business Rules

1. Rule 1: A team cannot be recorded in the database more than once.
2. Rule 2: Each team cannot have more than one coach.
3. Rule 3: Each coach should valid certification and police check documents
4. Rule 4: Each swimmer must have participated in a certain competition.
5. Rule 5: Each swimmer cannot be recorded more than once in the database.
6. Rule 6: Each team is required to have at least one medical officer.
7. Rule 7: A race competition cannot be recorded more than once.
8. Rule 8: Each race competition should have one winner of gold, silver and diamond in each event.
9. Rule 9: A swimmer can win more than one race prices.

Assumptions Made

While designing and implementing the swimming database the following assumptions were made.

1. Each country should not have more than one team in the competition.
2. Each team can have more than one swimmer.
3. Each swimmer can participate in several races.
4. One swimmer can win one or more races awards.
5. Each team must have at least one medical officer.
6. Each team must have at least one coach staff.
7. Each team must have one manager.
8. Each team should have one team leader.
9. Each team should have one contact name.

Naming Conventions

To keep consistency, the database will use strict naming conventions that will allow easy implementation and maintenance of the database when completed. The following conventions apply:

1. Convention 1: while naming the database the name must be in singular form.
2. Convention 2: While naming the database tables the name must be singular.
3. Convention 3: While naming the database or the tables the names should be in upper case.
4. Convention 4: All the columns names in the table must be singular.
5. Convention 5: There should be no use of prefix while naming the tables and databases.
6. Convention 6: The foreign key columns should have sane name with primary key of the table they reference.
7. Convention 7: The primary key column should not be named as “id”.

Data Types Chosen

While implementing the database there were two main data types that were used for various purposes as described here below.

1. INT data type:  

The INT is one of the data types used and it is used to for the following purposes.

• It was used to hold values of the primary key attributes for each table which is auto incremented.
• To hold values of the foreign keys attributes in order to reference other tables.

1. VARCHAR data type:

The VARCHAR is the other data type that was used and it is used to for the following purposes.

• To hold values of the attributes that has letters or characters only.
• To hold values of the attributes that has alphanumeric character like the phone number.
• To hold values of the attributes that has combination of letters, numbers and symbols like the email addresses and the dates.

Part B: Testing Queries

1. Team Member list

Query:

Select Distinct m.Name, m.Email, m.Phonenumber,m.MemberType from Medicalofficer m,Swimteam st where st.TeamID=m.TeamID order by m.Name DESC;

Select Distinct c.Name, c.Email, c.Phonenumber,c.MemberType from Coach c,Swimteam st where st.TeamID=c.TeamID order by c.Name DESC;

Select Distinct s.Name, s.Email, s.Phonenumber,s.MemberType from Swimmer s,Swimteam st where st.TeamID=s.TeamID order by s.Name DESC;

Result:

2. Country swimmer count

Query:

Select st.Country, st.Maincontactname, Count(s.TeamID) AS SWIMMERS from Swimteam st, Swimmer s where s.TeamID= st.TeamID group by st.Country  order by st.Country DESC;

Result:

3. Medical Officer Information

Query:

Select m.Name, m.Email, m.Phonenumber, st.Country, m.Specialisation from Medicalofficer m,Swimteam st where m.TeamID= st.TeamID order by m.Specialisation DESC, m.Name DESC;

Result:

4. Race information

Query:

SELECT Eventname,Racename,Starttime,Location FROM Race ORDER BY Heatnumber DESC;

Result:

5. Event Winner list

Query:

SELECT r.Eventname,s.Name,w.Recordedtimes,w.Award FROM  Race r, Swimmer s, Winner w,Swimteam st where w.heatnumber=r.heatnumber AND s.SwimmerID=w.SwimmerID and s.TeamID= st.TeamID ORDER BY r.Eventname DESC,r.Location  ASC;

Result:

6. Qualification check-list

Query:

select st.Country,c.Name,c.Email,c.Phonenumber,c.Certificationdate AS Training_Date from Swimteam st, Coach c where st.TeamID=c.TeamID;

Result:

Are you stuck with an overly convoluted assignment based on a particular subject matter? Are you looking for a dedicated team of subject matter experts to help you through the hurdle? Take a look here. MyAssignmenthelp.co.uk is right here to back you up with the following services and beyond. 

• Finance assignment help
• Law assignment help
• Accounting assignment help
• Management assignment help
• Biotechnology assignment help 
• Engineering assignment help and more 

So, get in touch with us right away, place your order and have the best SME by your side to provide you with impeccable assignment help online.