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 tablestructures EntitiesAttributes StudentStudentID StudentName StudentAddress
3 INTRODUCTION TO DATABASE SYSTEMS TeamID LeaderID StaffLeaderID StaffName TeamID Staff_address Team detailsTeamID TeamName LeaderID Members Rank Winner detailsWinner_ID Team_ID Score Yearofwinning RefereeRefereeID RefName RefContact RefName RefScore ResultRank TeamID
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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;
22 INTRODUCTION TO DATABASE SYSTEMS Queries for the tables: 3.SELECTrounddetails.Roundnumber,gamedetails.ID,teamdetails.TeamNameFROM rounddetailsINNERJOINgamedetailsONrounddetails.Roundnumber= gamedetails.RoundnumberINNERJOINwinnerdetailsONwinnerdetails.RoundID= rounddetails.RoundnumberINNERJOINteamdetailsONteamdetails.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
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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.