The project presentation should demonstrate knowledge in the chosen area. The presentation should be formatted as follows:12-point font using Times New RomanUse APA style correctly throughout the pres

1 NIT 1201 Introd uction to D atabase Systems Assign m ent Specificat io n (3 0%) Group work only- Group of four Due date: 7 October at 23:59 pm, week 11, trimester 2 20 18 Submission via drop box on VU C ollaborate only, no email su bmiss ion will be accepted. Intro duction The objective of th is assi gn ment is for you to pu t into practi ce the many different skills that you a re lear ning in th is u nit in to a sing le c ohesive data base project. You will be desig ning a datab ase to meet a specif ic organizat ional need. To do this you will w ork thro ug h the various stages of datab ase design , includ ing identifying user req uirement s, developing an und ers tand ing of the entities req uired and the rel ation sh ips betw een th em, as well as ide ntifying the bu siness rules associated with the processes that a re driving the need for t he database. You will then develop app ropriate data mo dels and design and implement the database. You will demonstr ate that yo ur d atabase implementation is viable throug h a ser ies of queries and upd ates on the data base. Sc enario VU University (VU) organ izes table tenn is mat ch at univer sity wide for both staff a nd stu dent s. Th is is an annual activity that has a history of 11 yea rs. You are asked to de sign a sm all data base for the org anizer to record data for better manag ement service in t he futur e. Every year, there are 20 teams parti cipat ing in this tab le tenn is ma tch. The team can be form ed by staff and studen ts together. For each team, there is at least one staff to be the lea der of the team, who will com mun icate with organ izer and leade rs from the other t eam s. A team needs 4 to 8 membe rs, i nclu ding the leader. A fo rm was u sed to collect the member informat ion of ea ch team. The leader ne eds to subm it this form to orga nizer two wee ks before the first game. In the form, all me mber s’ name, co ntact email addre ss, role (lea der, pri mary player, or sub stitute), staff/ student ID a re pro vided. One staff or stud ent can on ly play for one t eam. Each team has a na me for it self, which is also pr ovid ed in the form. 2 The ma tch lasts four weeks for four round s. G ames are all pla yed at weeken ds. Four teams will be put in one group. Each team ne eds to play with all other th ree tea ms in its group. E ach group has two top te ams to enter the ne xt round. In the second rou nd, 10 tea ms will be put into two group s. Each team plays with other four teams, and the top two teams will enter into the semi-final game s. Four teams in the semi-final game s, and each team plays with other three te ams, the top two tea ms will enter into the final game. Each game has thr ee sections. In each section, the team who firs tly achi eve 11 points wins. Out of three sectio ns, the team who firstly wins two section wins the game. Both single and pair games are played. Each team on ly allow four me mbers to play the single gam es. That is four single games will be played bet ween two teams. For pair games, there are also four players allowed. Th en two pa ir games will be played between t wo teams. Therefor e, tota lly six games will be play ed bet ween two tea ms. All the section sco res need to be recor ded toge ther with team names, playe rs and date of the game. Please be not iced that not all gam es play three section s. If one team wins the f irst two sectio ns, t hen the game is over. By end of the match, all teams are ra nked based on th eir tot al points achieved. This ranking list is recorded with the ranking n um bers and team nam es in ascend ing o rder. The tournament has 15 referees; a referee is assigned to a single match at the first round. The organizers keep records for referee performance for each match (you can measure performance on a scale from 1 to 10). For th e second round, four referees with highest score will be selected. For the semi -final, two referees will be chosen, and for the final the referee with highest score will be assigned. Yo ur task You have been comm issi oned to develop a datab ase system that is capable of keep ing rec ords for FU’s tab le ten nis matches from now on. The datab ase needs to keep a record of: • All team information, includ ing players’ information • All games, secti ons, the players invo lved and the scores • The win ner team of each game • The tea ms pl ay in each round • The match win ner team • The ranking of the teams of each yea r’s match • The referee assigned for each game and their performance score Further, it should be possi ble to gener ate a report on : • Ga me scores aft er each roun d, inc lud ing secti on scores • The total n umber of g ames that each team played in a match • The total scores of each team after a match • The ranking list of teams after a match • The win ner list of all recorded matches • Referees ranking, referee with best performance at all matches Steps you need to take to de velop yo ur d atabase app licati on 1. Complete the analysis and design of yo ur data base applicati on a. List the bu siness rules for your s ystem. b. Identify the ent ities and relati onsh ips in yo ur system. 3 c. Identify the ch aracterist ics of the entities in your s ystem. d. Develop an ER diag ram to mo del your system. e. Develop table structu res from the ER model. f. Condu ct a depen dency analysis of the tab le structu res and norm alize yo ur tab les where app ropriate, to at least 3NF. g. Create a data dicti onary for yo ur data base. 2. Im plement your p roject a. Create a datab ase that hosts your ap plicat ion data b. Create tables in your data base. These must be consis tent with your desi gn . c. Po pu late all tab les with s ample da ta (at lea st 10 entr ies in each) d. Create the req uired views, stored proced ures etc. to meet the req uirements of your system 3. to write SQL commands 1. SQL codes are provided for database and table creation 2. SQL codes are pr ovided for data record insertion Reports by SQL: 3. to show the winner of each game in each round 4. to sor t all teams according to their scores (ascending) 5. to provid e a report for a referee with all the game s he served 6. to provide a list of the loser teams 7. to provide a list of a ll players in all teams 8. Ga me scores aft er each roun d, inc lud ing secti on scores 9. The total n umber of g ames that each team played in a match 10. The total scores of each team after a match 11. The win ner list of all recorded matches 12. Referees ranking, referee with best performance at all matches You ne ed to be able to demo nstra te that your data base app licati on meets the req uire ments detailed in the scena rio as well as be consistent with the model yo u have developed. Su bmission Re quireme nts Your a ssignment sh ould be compo sed of the following parts: 1. Project document which includ es the followin g: 1.1. A l ist of the business rules; 1.2. ER Diagram(s) prep ared using software such as MS Office Visio or any other ER diagram tool. The se should includ e all nec essary information about the entit ies, attributes and relati onships. Please prov ide clear an d eas y-to-read screensh ot of you r ER Diagra m(s). If you draw your diagram(s) in MS Visio, please sub mit the Visio fi le too; 1.3. Data di ctionary in t he format sh own in the lecture notes; 1.4. Datab ase design and tab le structu res showing tab le names, as well as any rel ated entity integrity and referential integr ity constrai nts. Includ e in the tab le structu res attribute data types, sizes, pr imary keys, forei gn keys and any other rele vant information; 4 1.5. Diagrams sh owing the depen dency analysis for each of the tab les. You need to demonstra te that all tab les are in 3NF. Show the process of normal iza tion includ ing tables in 1NF, 2NF an d 3NF respe ctively. Please refer to the examp les in lect ure notes; 1.6. The SQL code yo u used to: 1.6. 1. Create and popu late the database; 1.6. 2. Create each of the rep orts identified in the assi gnm ent specificat ion. 2. Datab ase implementat ion in XAMPP 2.1. A ll the tab les of your assign ment must be implemen ted in XA MPP. Please prov ide screens hots as evide nce of you r implementati on . 2.2. All the tables must be well defi ned with app ropriate primary keys and foreign key s whe re app lica ble. Please prov ide screensh ot of tab le structures. 2.3. A ll tab les must be popu lated with sample data (at least 10 ent ity instances – rows – in each ta ble). Please prov ide screens hots of table d ata records. End of Assignment 03/09/2018