Database Design and Implementation: VU University Assignment - NIT1201

This assignment presents a comprehensive database design for a university table tennis tournament, addressing the needs of the organizer to record data for better management of the annual activity. It begins by outlining the business rules, defining entities such as TeamDetails, Student, Staff, Winner details, Referee, Result, Game details, and Round details, and establishing their relationships. The assignment includes an ER diagram, table structures, and a data dictionary detailing attributes, data types, and keys. Furthermore, it provides a 3NF dependency analysis and SQL commands for table creation, data insertion, and various queries to retrieve and manipulate data, such as retrieving game details, referee information, and team statistics. The solution concludes with a bibliography of relevant database systems resources.

Running head: INTRODUCTION TO DATABASE SYSTEMS
INTRODUCTION TO DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1
INTRODUCTION TO DATABASE SYSTEMS
List of Business Rules
The business rules for to be followed for the development of the database are:
 The system should be able to store the details of the team along with the information
about the ranks and the members of the system.
 The system should also store the details of the games and the rounds which would takes
place.
 The details of the winners are also required to be stored in the system.
Entities along with the relationship in the system
TeamDetails (TeamID (pk), TeamName, LeaderID (fk), Members, Rank)
Student (StudentID (pk), StudentName , StudentAddress, TeamID(fk), LeaderID(fk))
Staff (LeaderID (pk), StaffName, TeamID (fk), Staff_address)
Winner details (Winner_ID (pk), Team_ID (fk), Score, Yearofwinning)
Referee (RefereeID (pk), RefName, RefContact, RefName, RefScore)
Result (Rank (pk), TeamID (fk), TeamScore)
Game details (ID (Pk), TeamID (fk), ScoreDetails, Roundnumber(fk))
Round details (RoundNumber(pk), RoundName, Roundtime , RefereeID (fk))

2
INTRODUCTION TO DATABASE SYSTEMS
Characteristics of the system
The system consists of 8 major entities which would be useful for description of the
system. In addition to this, attributes are to be linked with each other so that the relationships
would be helpful in getting the entire details of a particular record.
ER diagram for the system
ERD model table structures
Entities Attributes
Student StudentID
StudentName
StudentAddress

3
INTRODUCTION TO DATABASE SYSTEMS
TeamID
LeaderID
Staff LeaderID
StaffName
TeamID
Staff_address
Team details TeamID
TeamName
LeaderID
Members
Rank
Winner details Winner_ID
Team_ID
Score
Yearofwinning
Referee RefereeID
RefName
RefContact
RefName
RefScore
Result Rank
TeamID

4
INTRODUCTION TO DATABASE SYSTEMS
TeamScore
Game details ID
TeamID
ScoreDetails
RoundNumber
Round details RoundNumber
RoundName
RoundTime
RefereeID
3NF dependency
Dependencies Attributes
StudentID StudentName, StudentAddress, TeamID, LeaderID
TeamID TeamName, LeaderID, Members, Rank
LeaderID StaffName, TeamID, Staff_address
Winner_ID Team_ID, Score, Yearofwinning
RefereeID RefName, RefContact, RefName, RefScore
Rank TeamID, TeamScore
ID, TeamID ScoreDetails, Roundnumber
RoundNumber RoundName, Roundtime , RefereeID

5
INTRODUCTION TO DATABASE SYSTEMS
Data dictionary
Entities Attributes Data type Key
Student StudentID Number Primary Key
StudentName Varchar
StudentAddress Varchar
TeamID Number Foreign Key
LeaderID Varchar
Staff LeaderID Number Primary Key
StaffName Varchar
TeamID Number Foreign Key
Staff_address Varchar
Team details TeamID Number Primary Key
TeamName Varchar
LeaderID Number Foreign Key
ID Number Foreign Key
Members Varchar
Rank Number
Winner details WinnerID Number Primary Key
TeamID Number Foreign Key
Score Number
Yearofwinning Date
Referee RefereeID Number Primary Key

6
INTRODUCTION TO DATABASE SYSTEMS
RefName Varchar
RefContact Number
RefName Number
RefScore Number
Result Rank Number Primary Key
TeamID Number Foreign Key
TeamScore Number
Game details ID Number Primary Key
TeamID Number Foreign Key
ScoreDetails Number
RoundNumber Number Foreign Key
Round details RoundNumber Number Primary Key
RoundName Varchar
RoundTime Time
RefereeID Number Foreign Key
Write SQL commands
--
-- Table structure for table `gamedetails`
--

7
INTRODUCTION TO DATABASE SYSTEMS
CREATE TABLE `gamedetails` (
`ID` int(30) NOT NULL,
`TeamID` int(30) NOT NULL,
`Scoredetails` int(30) NOT NULL,
`Roundnumber` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `gamedetails`
--
INSERT INTO `gamedetails` (`ID`, `TeamID`, `Scoredetails`, `Roundnumber`) VALUES
(1, 1, 1300, 1),
(2, 4, 1350, 1),
(3, 2, 1200, 1),
(4, 3, 1100, 1),
(5, 4, 1000, 2),
(6, 4, 1700, 3),

8
INTRODUCTION TO DATABASE SYSTEMS
(7, 2, 2000, 4),
(8, 4, 2100, 4),
(9, 9, 2200, 4),
(10, 3, 2400, 6);
-- --------------------------------------------------------
--
-- Table structure for table `referee`
--
CREATE TABLE `referee` (

9
INTRODUCTION TO DATABASE SYSTEMS
`RefereeID` int(30) NOT NULL,
`Refname` varchar(30) NOT NULL,
`Refcontact` int(30) NOT NULL,
`Refscore` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `referee`
--
INSERT INTO `referee` (`RefereeID`, `Refname`, `Refcontact`, `Refscore`) VALUES
(1001, 'Ben Louis', 63794021, 200),
(1002, 'Jem Jaz', 37947821, 500),
(1003, 'Vick john ', 63790136, 500),
(1004, 'Derreck Stevens', 37940456, 350),
(1005, 'Neymar', 63797325, 100),
(1006, 'Ever Banega', 37944769, 400),
(1007, 'Kevin Friend', 63794301, 250),

10
INTRODUCTION TO DATABASE SYSTEMS
(1008, 'Daily Blind', 637947860, 400),
(1009, 'Freddy Stevens', 37941230, 600),
(1010, 'Deffy Son', 63794120, 400);
-- --------------------------------------------------------
--
-- Table structure for table `result`
--
CREATE TABLE `result` (
`Rank` int(30) NOT NULL,

11
INTRODUCTION TO DATABASE SYSTEMS
`TeamID` int(30) NOT NULL,
`TeamScore` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `result`
--
INSERT INTO `result` (`Rank`, `TeamID`, `TeamScore`) VALUES
(1, 4, 2100),
(2, 2, 2000),
(3, 3, 1900),
(4, 1, 1870),
(5, 10, 1840),
(6, 6, 1830),
(7, 5, 1810),
(8, 9, 1805),
(9, 7, 1802),