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),

12
INTRODUCTION TO DATABASE SYSTEMS
(10, 8, 1800);
-- --------------------------------------------------------
--
-- Table structure for table `rounddetails`
--
CREATE TABLE `rounddetails` (
`Roundnumber` int(11) NOT NULL,
`Roundname` varchar(30) NOT NULL,
`roundtime` datetime NOT NULL,

13
INTRODUCTION TO DATABASE SYSTEMS
`RefereeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `rounddetails`
--
INSERT INTO `rounddetails` (`Roundnumber`, `Roundname`, `roundtime`, `RefereeID`)
VALUES
(1, 'Show Down ', '2019-12-28 04:51:00', 1001),
(2, 'Face off ', '2019-12-26 02:00:00', 1002),
(3, 'Semi final ', '2019-11-24 02:00:00', 1001),
(4, 'Final', '2019-11-28 02:00:00', 1001),
(5, 'fire', '2019-12-03 00:00:00', 1004),
(6, 'Fire2', '2019-12-03 00:00:00', 1007),
(7, 'Fire3', '2019-12-04 00:00:00', 1007),
(8, 'Fire4', '2019-12-05 00:00:00', 1010),
(9, 'Hits1', '2019-12-06 00:00:00', 1003),
(10, 'Hits2', '2019-12-06 00:00:00', 1006);

14
INTRODUCTION TO DATABASE SYSTEMS
-- --------------------------------------------------------
--
-- Table structure for table `staff`
--
CREATE TABLE `staff` (
`LeaderID` int(30) NOT NULL,
`Staffname` varchar(30) NOT NULL,
`TeamID` int(30) NOT NULL,
`Staffaddress` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `staff`
--

15
INTRODUCTION TO DATABASE SYSTEMS
INSERT INTO `staff` (`LeaderID`, `Staffname`, `TeamID`, `Staffaddress`) VALUES
(201, 'Garry Knowles', 1, 'Mellbourne'),
(202, 'Ben Franklin', 2, '34, Capetown'),
(203, 'David Foster', 3, '33, Mellbourne Street'),
(204, 'Ben Mick', 4, 'New mellbourne road'),
(205, 'Kevin Steven', 1, '5 bg road'),
(206, 'Luis Williams', 4, 'down street'),
(207, 'Kelly Brooke', 3, '8 Turd Street'),
(208, 'David Hare', 2, '90 PF End'),
(209, 'Kevin Persie', 6, 'NA'),
(210, 'David Willian', 2, 'NA');
-- --------------------------------------------------------

16
INTRODUCTION TO DATABASE SYSTEMS
--
-- Table structure for table `student`
--
CREATE TABLE `student` (
`StudentID` int(30) NOT NULL,
`StudentName` varchar(30) NOT NULL,
`StudentAddress` varchar(30) NOT NULL,
`TeamID` int(30) NOT NULL,
`LeaderID` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

17
INTRODUCTION TO DATABASE SYSTEMS
--
-- Dumping data for table `student`
--
INSERT INTO `student` (`StudentID`, `StudentName`, `StudentAddress`, `TeamID`,
`LeaderID`) VALUES
(101, 'Harry Wilson ', 'California east ', 1, 201),
(102, 'David Pietreson', 'Red cross road ', 2, 202),
(103, 'Harry Redknapp ', '6th Rusell Colony', 2, 202),
(104, 'Edi Becker', 'Melbourne ', 1, 201),
(105, 'James Wilson', '45, Suzzane Strreet ', 3, 203),
(106, 'Harry Willis', 'Rowdown street ', 3, 203),
(107, 'Billy Parker', '21, Bens colony', 4, 204),
(108, 'Fenny Williams', '43, North Colony ', 4, 204),
(109, 'Sherry Jameson', 'Northern Avenue ', 4, 204),
(110, 'Barry Roberts', 'Southern Valley street', 1, 201);
-- --------------------------------------------------------

18
INTRODUCTION TO DATABASE SYSTEMS
--
-- Table structure for table `teamdetails`
--
CREATE TABLE `teamdetails` (
`TeamID` int(30) NOT NULL,
`TeamName` varchar(30) NOT NULL,
`LeaderID` int(30) NOT NULL,
`Members` varchar(30) NOT NULL,
`Rank` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

19
INTRODUCTION TO DATABASE SYSTEMS
--
-- Dumping data for table `teamdetails`
--
INSERT INTO `teamdetails` (`TeamID`, `TeamName`, `LeaderID`, `Members`, `Rank`)
VALUES
(1, 'Team A', 201, '8', 3),
(2, 'Team B', 202, '6', 2),
(3, 'Team C', 203, '7', 3),
(4, 'Team D', 204, '8', 1),
(5, 'Team E', 201, '6', 4),
(6, 'Team F', 202, '8', 4),
(7, 'Team G', 202, '6', 3),
(8, 'Team H', 203, '6', 3),
(9, 'Team I', 201, '6', 2),
(10, 'Team J', 204, '6', 3);
-- --------------------------------------------------------

20
INTRODUCTION TO DATABASE SYSTEMS
--
-- Table structure for table `winnerdetails`
--
CREATE TABLE `winnerdetails` (
`WinnerID` int(30) NOT NULL,
`TeamID` int(30) NOT NULL,
`Score` int(20) NOT NULL,
`Yearofwinning` date NOT NULL,
`RoundID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

21
INTRODUCTION TO DATABASE SYSTEMS
--
-- Dumping data for table `winnerdetails`
--
INSERT INTO `winnerdetails` (`WinnerID`, `TeamID`, `Score`, `Yearofwinning`, `RoundID`)
VALUES
(1005, 5, 5000, '2019-12-11', 5),
(1006, 6, 4000, '2019-08-06', 6),
(1007, 7, 8000, '2019-08-06', 7),
(1008, 8, 7000, '2019-10-02', 8),
(1009, 9, 2000, '2019-10-04', 9),
(1010, 10, 5000, '2019-10-05', 10),
(10001, 1, 200000, '2019-11-21', 1),
(10002, 2, 25000, '2019-09-13', 2),
(10003, 3, 20940, '2019-07-23', 3),
(10004, 4, 15000, '2019-08-26', 4);
--

22
INTRODUCTION TO DATABASE SYSTEMS
Queries for the tables:
3. SELECT rounddetails.Roundnumber, gamedetails.ID, teamdetails.TeamName FROM
rounddetails INNER JOIN gamedetails ON rounddetails.Roundnumber =
gamedetails.Roundnumber INNER JOIN winnerdetails ON winnerdetails.RoundID =
rounddetails.Roundnumber INNER JOIN teamdetails ON teamdetails.TeamID =
winnerdetails.TeamID

23
INTRODUCTION TO DATABASE SYSTEMS
4. SELECT teamdetails.TeamName, SUM(gamedetails.Scoredetails)
FROM gamedetails
INNER JOIN teamdetails ON teamdetails.TeamID = gamedetails.TeamID
GROUP BY teamdetails.TeamName
ORDER BY SUM(gamedetails.Scoredetails) DESC;
5. SELECT referee.refname, rounddetails.Roundname FROM referee INNER JOIN rounddetails
ON rounddetails.RefereeID = referee.RefereeID where referee.refname = "Ben Louis"
6. Select * from teamdetails
WHERE TeamID NOT IN (Select TeamID FROM winnerdetails);
7. SELECT teamdetails.`TeamName`, student.StudentName
FROM teamdetails

24
INTRODUCTION TO DATABASE SYSTEMS
INNER JOIN student ON student.TeamID = teamdetails.TeamID;
8. SELECT rounddetails.Roundnumber, `winner_details`.`Score`, gamedetails.Scoredetails
FROM rounddetails
INNER JOIN winnerdetails ON winnerdetails.RoundID = rounddetails.Roundnumber
INNER JOIN gamedetails ON gamedetails.Roundnumber = rounddetails.Roundnumber;
9. SELECT rounddetails.Roundnumber, rounddetails.Roundname, COUNT(gamedetails.ID)
FROM rounddetails

25
INTRODUCTION TO DATABASE SYSTEMS
INNER JOIN gamedetails ON rounddetails.Roundnumber = gamedetails.Roundnumber
GROUP BY rounddetails.Roundnumber, rounddetails.Roundname;
10. SELECT teamdetails.TeamName, gamedetails.Scoredetails
FROM gamedetails
INNER JOIN teamdetails ON gamedetails.TeamID = teamdetails.TeamID;
11. SELECT * FROM winnerdetails;

26
INTRODUCTION TO DATABASE SYSTEMS
12. SELECT * FROM `referee` ;

27
INTRODUCTION TO DATABASE SYSTEMS
Bibliography
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.