Database Project: ER Diagram, Normalization, and MySQL Implementation

This document presents a comprehensive database project for the North Coast Dragon Boat Association. The project encompasses the design and implementation of a relational database system using MySQL and phpMyAdmin. Part A focuses on constructing an Entity Relationship (ER) diagram to model the database, detailing entities such as Club membership, Club, Regatta, Race, Coach, Winner, and Boat, along with their respective attributes. The solution includes a data dictionary that outlines the attributes for each entity. Furthermore, the document addresses database normalization, identifying primary and foreign keys to ensure data integrity and efficient storage. Part B involves developing the database in MySQL using phpMyAdmin, with screenshots of the database tables provided. The solution also features SQL queries for data manipulation, retrieval, and maintenance. The project concludes with a summary of the database management principles applied and the successful implementation of a dynamic database system for the association. Overall, this project provides a practical application of database design principles and implementation techniques, offering valuable insights into database management.

Data Management (Database SQL)

Table of Contents
INTRODUCTION...........................................................................................................................3
PART A...........................................................................................................................................3
a) Construct the Entity relationship diagram as per the requirement of database system...........3
b) Create a relational database in the fully normalised form and also determine the primary
key, foreign key...........................................................................................................................6
PART B...........................................................................................................................................7
Develop the database in MYSQL using PhpMyadmin................................................................7
CONCLUSION................................................................................................................................8

INTRODUCTION
A database management is a type of software that help for creating, managing the entire
data in the form of database. It provides the users and programmer with systematic ways to
create, update, manage and retrieve data in proper manner. It makes possible for end users to
create data and collect in the system in systematic ways. This report will discuss about the North
coast Dragon Boat association because they will design an effective database that help to store
and collect the data of membership, race, boat, coach and regatta in the appropriate manner.
Furthermore, it will design and produce the entity relationship diagram or mention all the
entities, attributes etc. This report will use the MYSQL using the PhPMyadmin should create the
database system.
PART A
a) Construct the Entity relationship diagram as per the requirement of database system.
Entity relationship diagram is represented as the model when interrelated things of interest on the
specific knowledge about the domain. It is composed of entity type and also specific the
relationship in the form of ER diagram. We can say that it is a another data modelling technique
Illustration 1: ER Diagram

that represented in graphically information system. It can be determined the conceptual and
model of data that create the entity framework of overall infrastructure.
Data dictionary:
Entities Attributes
Club membership  Membership id (PK)
 name
 address
 contact no
 position status
Club  Club id (PK)
 name
 membership id (FK)
 boat id (FK)
 coach no (FK)
Regatta  Regatta no (PK)
 name
 date
 place
 club id (FK)
Race  Race no (PK)
 Race name
 starting time
 date
 Heat
 year
 Regatta no (FK)
Coach  Coach no (PK)
 coach name
 qualification

 date of training
 contact no
 membership id (FK)
Winner  Winner id (PK)
 Name
 Time
 club id (FK)
 Regatta no (FK)
 Race no (FK)
Boat  Boat no (PK)
 Name
 Length
 race no (PK)
Table : 1
This table is described the entities and attributes in the database tables. It has created a specific
relationship between the one or more column.

b) Create a relational database in the fully normalised form and also determine the primary key,
foreign key.
 Primary Key : It is a type of unique key for containing each records in the database
table. A relational database must be consists of one primary key that create a specific
relationship with another table. It depends on the preferences of administrator which
possible to change the key into the database according to need and requirement.
 Foreign Key : It is a field in one table that uniquely determine a row of another database
table. A foreign key is a group of column in relational database that provide the specific
link between two tables. The data must be added one or more tables to create relational
between them.
For Example-
In club membership table:
In this table, membership id is a primary key in the club membership table which create a
specific relationship with another table as a foreign key.
In club table:
In this table, club id is a primary key that contain unique values and not allowed the
repeated values in it. On the other hand, club id is a representing as foreign key in regatta table
which has established the relationship between two tables.
In Race table:
In this table, race no is a primary key but it has created a relationship with another winner
table. Therefore, it has created a relationship between two tables. It is the representing as a
foreign key to winner table.
In Coach table:
In this table, Coach no is a primary key because it has recoded the unique values and
cannot be repeated. It is representing as a foreign key into club table and also create a
relationship between them.

Insert command

PART B
Develop the database in MYSQL using PhpMyadmin.
Database tables:

SQL Queries:
Structural query language is standards that has been used to create, retrieve and maintain
the relational database system. It is particular apply to work with the structure data and
information. It is associated the relationship within the data itself.
In database system, it can be used the SQL statements that can use to generate the queries
form the client side while it allows the user to execute the wide range of data manipulation
process. It is the important language that control overall performance of database server to edit or
store the data in effective ways.