ISYS114: Introduction to Database Design and ManagementISYS114 2018 – ASSIGNMENT TWO WORTH 20%The assignment includes the following componentsONLINE SUBMISSION WORTH 10%IN-CLASS QUIZ WORTH 10%ONLINE SUBMISSION DUE: 5PM FRIDAY 19 OCTOBER 2018IN-CLASS QUIZ DUE: DURING WEEK 11 IN YOUR SCHEDULED WORKSHOPISYS114 2018-Assignment 22 | PageTable of ContentsCASE BACKGROUND.....................................................................................................................3TASK 1 (4 MARKS) .............................................................................................................................3TASK 2 (4 MARKS) .............................................................................................................................4TASK 3 (3 MARKS) .............................................................................................................................6TASK 4 (3 MARKS) .............................................................................................................................6TASK 5 (3 MARKS) .............................................................................................................................6TASK 6 (4 MARKS) .............................................................................................................................6TASK 7 (4 MARKS) .............................................................................................................................7TASK 8 (3 MARKS) .............................................................................................................................7TASK 9 (3 MARKS) .............................................................................................................................7TASK 10 (4 MARKS) .........................................................................................................................7TASK 11 (5 MARKS) .........................................................................................................................7TASK 12 (5 MARKS) .........................................................................................................................7TASK 13 (5 MARKS) .........................................................................................................................7TASK 14 (5 MARKS) .........................................................................................................................8TASK 15 (5 MARKS) .........................................................................................................................8SUBMISSION FILE.........................................................................................................................9SUBMISSION INSTRUCTIONS ........................................................................................................9ASSIGNMENT-2 IN-CLASS ASSESSMENT ........................................................................................9MARKING CRITERIA.................................................................................................................... 10FAQS FROM LAST SEMESTER ...................................................................................................... 11ISYS114 2018-Assignment 23 | PageCase BackgroundUSSIA was chosen to host the Football World Cup 2018. Russians wanted to makeit the best organised world cup ever. They intended to avoid any reason to causenational shame. Therefore, they hired ‘you’ to be the RUSSIA2018 databaseadministrator to look after their data. As a part of your role, you need to do the followingtasks…Good LuckTask 1 (4 marks)Create the following tables based on the tables given below. You need to decide the rightorder to create these tables.Please note that the description of each table is for your understanding and not to beused in SQL table creation.TABLE NAME: STADIUMCOLUMN-NAME DATATYPE DESCRIPTIONSTADIUMID [PK] CHAR(3) A unique ID for each stadiumSTADIUMNAME VARCHAR(30) The name of the stadiumSTADIUMCITY VARCHAR(20) The city of the stadiumCAPACITY INT The maximum capacity of the stadiumOPENYEAR YEAR The year the stadium was openedTABLE NAME: FANCOLUMN-NAME DATATYPE DESCRIPTIONFANID [PK] CHAR(9) A unique ID for each fanFANNAME VARCHAR(25) The name of the fanFANNATIONALITY VARCHAR(30) The nationality of the fanFANCATEGORY CHAR(7) The category of the fanTABLE NAME: MATCHESCOLUMN-NAME DATATYPE DESCRIPTIONMATCHID [PK] CHAR(5) A unique ID for each matchFIRSTTEAM [FK] CHAR(4) The ID of the first team in a matchSECONDTEAM [FK] CHAR(4) The ID of the second team in a matchMATCHDATE DATE The date of playing a matchKICKOFF TIME The starting time of a matchSTADIUMID [FK] CHAR(3) The ID of the stadium to play a matchTABLE NAME: TEAMCOLUMN-NAME DATATYPE DESCRIPTIONTEAMID [PK] CHAR(4) A unique ID for each teamCOUNTRY VARCHAR(20) The country of the teamCONTINENT VARCHAR(15) The continent of the teamFIFARANK TINYINT FIFA rank to the teamTEAMGROUP CHAR(1) The group a team plays inMANAGER VARCHAR(20) The name of the manager of the teamNICKNAME VARCHAR(25) The nickname of the teamRISYS114 2018-Assignment 24 | PageTABLE NAME: TICKET (will be provided to you)COLUMN-NAME DATATYPE DESCRIPTIONTICKETID [PK] CHAR(15) A unique ID for each ticketBLOCKNO CHAR(4) The number of the blockROWNO CHAR(4) The location of the rowSEATNO CHAR(4) The location of the seatPRICE SMALLINT The price of the ticketMATCHID [FK] CHAR(5) The ID of the match of the ticketFANID [FK] CHAR(9) The ID of the fan who purchased the ticketTask 2 (4 marks)Populate the tables with values given below. Please insert the data in CAPITAL letters.Note that the fields STADIUMID, FANID or MATCHID (i.e. S01, F001 or M01) has zero init and not letter ‘O’STADIUM TABLESTADIUMID STADIUMNAME STADIUMCITY CAPACITY OPENYEARS01 LUZHNIKI STADIUM MOSCOW 80000 1956S02 SAINT PETERSBURGSTADIUMSAINTPETERSBURG67000 2017S03 FISHT STADIUM SOCHI 48000 2013S04 KAZAN ARENA KAZAN 45000 2013S05 SAMARA ARENA SAMARA 45000 2018S06 SPARTAK STADIUM MOSCOW 42000 2014FAN TABLEFANID FANNAME FANNATIONALITY FANCATEGORYF001 MONICA JONES AMERICAN SILVERF002 DMITRIY VLADIMIR RUSSIAN GOLDF003 ELLEN SMITH AUSTRALIAN SILVERF004 MR. BEAN BRISTISH GOLDF005 IGOR MIKHAIL RUSSIAN BRONZEF006 ALBERTO HERNANDEZ BRAZILIAN SILVERF007 NATALIA PAKLINA RUSSIAN BRONZEF008 NANCY AJRAM LEBANESE SILVERF009 JIE CHEN CHINESE GOLDF010 MAX CAMERON AMERICAN SILVERISYS114 2018-Assignment 25 | PageMATCHES TABLEMATCHID FIRSTTEAM SECONDTEAM MATCHDATE KICKOFF STADIUMIDM01 T9 T17 2018-06-14 15:00:00 S01M02 T5 T29 2018-06-14 16:00:00 S02M03 T10 T19 2018-06-16 18:00:00 S03M04 T10 T16 2018-06-19 20:00:00 S02M05 T1 T15 2018-06-19 20:00:00 S04M06 T2 T4 2019-06-20 15:00:00 S06M07 T11 T19 2018-07-15 20:00:00 S01M08 T15 T19 2018-07-15 16:00:00 S02TEAM TABLE (will be provided to you)TEAMID COUNTRY CONTINENT FIFARANK TEAMGROUP MANAGER NICKNAMET1 ARGENTINA SOUTHAMERICA 5 D JORGESAMPAOLIWHITE ANDSKY BLUEST10 AUSTRALIA OCEANS 36 C BERT VANMARWIJK SOCCEROOST11 BELGIUM EUROPE 3 G ROBERTOMARTíNEZ RED DEVILST12 BRAZIL SOUTHAMERICA 2 E TITE THESELECTIONT13 COLOMBIA SOUTHAMERICA 16 H JOSE PEKERMAN THE COFFEEGROWERST14 COSTA RICA SOUTHAMERICA 23 E LOS TICOST15 CROATIA EUROPE 20 D ZLATKO DALIC THEBLAZERST16 DENMARK EUROPE 12 C AGE HAREIDE DANISHDYNAMITET17 EGYPT AFRICA 45 A HECTOR CUPER PHARAOHST18 ENGLAND EUROPE 13 G GARETHSOUTHGATETHREELIONST19 FRANCE EUROPE 7 C DIDIERDESCHAMPS THE BLUEST2 GERMANY EUROPE 1 F JOACHIM LOWT20 ICELAND EUROPE 22 D HEIMIRHALLGRIMSSON OUR BOYST21 IRAN ASIA 37 B CARLOSQUEIROZ TEAM MELLIT22 JAPAN ASIA 61 H VAHIDHALILHODZICBLUESAMURAIT23 MEXICO SOUTHAMERICA 15 F JUAN CARLOSOSORIOELTRICOLORT24 MOROCCO AFRICA 41 B HERVE RENARD ATLASLIONST25 NIGERIA AFRICA 48 D GERNOT ROHR SUPEREAGLEST26 PANAMA SOUTHAMERICA 55 G HERNAN DARIOGOMEZTHE REDTIDET27 PERU SOUTHAMERICA 11 C RICARDOGARECA LOS INCAST28 POLAND EUROPE 8 H ADAMNAWALKATHE POLISHEAGLEST29PORTUGAL EUROPE 4 B FERNANDOSANTOSTEAM OFTHECASTLEST3 RUSSIA ASIA 70 A STANISLAVCHERCHESOVSBORNAYA(TEAM)ISYS114 2018-Assignment 26 | PageT30 SAUDIARABIA ASIA 67 A BERT VANMARWIJKTHE GREENFALCONST31SENEGAL AFRICA 27 H ALIOU CISSETHE LIONSOFTERANGAT32 SERBIA EUROPE 34 E MLADENKRSTAJIC THE EAGLEST4 SOUTH KOREA ASIA 57 F SHIN TAE-YONG ASIANTIGERST5 SPAIN EUROPE 10 B JULENLOPETEGUIT6 SWEDEN EUROPE 24 F JANNEANDISYS114作业代做、代写SQL程序设计作业、代做Database Design and Management作业、代ERSSONT7 SWITZERLAND EUROPE 6 E VLADIMIRPETKOVICTHE REDCRUSADERST8TUNISIA AFRICA 21 G NABILMAALOULTHE EAGLESOFCARTHAGET9 URUGUAY SOUTHAMERICA 14 A OSCARTABAREZ CHARRUASTICKET TABLETICKETID BLOCKNO ROWNO SEATNO PRICE MATCHID FANIDTic1 A112 23 19 150 M01 F001Tic10 D109 9 20 500 M06 F009Tic2 B75 45 11 185 M02 F003Tic3 D109 7 21 200 M02 F005Tic4 B80 41 8 250 M03 F001Tic5 E123 23 15 300 M03 F004Tic6 A266 17 24 375 M05 F002Tic7 B45 25 11 350 M05 F009Tic8 F11 23 14 300 M06 F007Tic9 C233 7 23 750 M08 F001Task 3 (3 marks)Write an SQL statement to display match ID for the matches that starts after 16:00. Sortthe result by kick-Off in descending order.Task 4 (3 marks)Write an SQL statement to display the country and its continent whose FIFA Rank withina range of 2 to 5. Sort the result by FIFA Rank in an ascending order. The country and thecontinent should be forced to be in lower case.Task 5 (3 marks)Write an SQL statement to display ticket ID and block number of the tickets that are eitherin block A or in block B. Sort the result by block number in an ascending order.Task 6 (4 marks)Write an SQL statement to display match ID and kick off time of the matches played atstadium KAZAN ARENA. Each kick off time should start with the text “starts at”.ISYS114 2018-Assignment 27 | PageTask 7 (4 marks)Write an SQL statement to display the first 3 characters of a country name whosemanager is blank ( change the alias to “COUNTRY INITIALS”), also display the match dateand kick off time. Sort the result by match date in descending order.[NOTE: manager name is blank means the name is not provided in the database]Task 8 (3 marks)Write an SQL statement to display fan ID and fan name for those who’ve purchased atleast one ticket. [JOIN MUST BE USED]Task 9 (3 marks)Write an SQL statement to display fan ID and fan name for those who’ve purchased atleast one ticket. [SUB-QUERY MUST BE USED]Task 10 (4 marks)Write an SQL statement to display fan categories, the number of tickets in each categorythe fan bought (change the alias to “NUMBER OF TICKETS PURCHASED”) along with thesum of prices (change the alias to TOTAL PRICES) in each category the fans paid. Sortthe result by total price in an ascending order.Task 11 (5 marks)Write an SQL statement to display match ID and the count of tickets sold for each matchONLY if the number of tickets sold of this match is more than 1 and the open year of thestadium on which the match will be played is not 2013.Task 12 (5 marks)Write an SQL statement to display the stadium name and the number of matches playedin each stadium (change the alias to “NUMBER OF MATCHES”). Display only the top twostadiums where most matches were held. Sort the result by the number of matches playedon each stadium in a descending order.[NOTE: Do Not use a static (fixed) value to determine the largest number of matches]Task 13 (5 marks)Write an SQL statement to display team name and its manager if Croatia is one of theteams in any match (either as a first or a second team). Manager’s name should bedisplayed as initialisation of first name followed by a dot and then the last name. The aliasof the manager column should be changed to “Manager Initials”. For example, ifmanager’s name is David Smith, it will be displayed as D. Smith.ISYS114 2018-Assignment 28 | PageThe output of task 13 should look like the following:Task 14 (5 marks)Create a view (named BRONZE) to display fan name, match ID and stadium name in theticket of matches booked by bronze fans.Task 15 (5 marks)Create a stored procedure (named FANMATCHES) to display match ID, stadium name,seat number and kick off time for the matches attended by a fan. The name of that fan willbe provided as an argument in the created stored procedure.ISYS114 2018-Assignment 29 | PageSubmission File Download the file ‘Submit_This.txt’ provided in your Assignment-2 folder on iLearn. Thiswill be the template for you to insert your SQL statements. Rename this file to your StudentID_Name.txt. Example: 12345678_AbrahamSmith.txt To achieve each of the tasks described above, you need to answer your tasks by writingqueries, run them, check the results, copy and insert the codes at the right place in thetemplate. Use the provided SQL code (i.e. TICKET table creation and TEAM records) in the fileAssignment2_TicketTable_TeamRecords.txt” on iLearn as a contribution to yourassignment ( no need to inset data about 32 teams).Submission Instructions You need to submit this file back on iLearn (Assignment-2 link) after you have insertedall the answers, before the due date. Format of the file will be .txt, do not change it to a .sql file.Assignment-2 In-class Assessment In-class assessment will be conducted in week 11 in the workshops youve enrolled in. In-class assessment will base on assignment-2 case study with similar queries as the onesin the online submission. A Student must physically attend the scheduled workshops to attempt the in-classassessment. A student should get his/her student card to sit the in-class assessment. In-class assessment is an individual assessment, so test/exam policy applies. Only one attempt is given to each student.ISYS114 2018-Assignment 210 | PageTask Marking Criteria Breaking downthe mark MarkTask 1 Four tables created (4X1) 4Task 2 Four tables populated correctly (4X1) 4Task 3Correct field displayed from the right table 1Correct condition 1 3Correct Sorting 1Task 4Correct field displayed from the right table 1Correct condition 1 3Right lower case to the displayed fields 1Task 5Correct field displayed from the right table 0.5Correct condition 2 3Correct Sorting 0.5Task 6Correct field displayed from the right table 1Text starts at is displayed before kick-off time 1 4correct condition 2Task 7correct country name, match date and kick-off for blank manager is displayed 14 Only first three character displayed 1Correct condition 1Correct Sorting 1Task 8 Correct field displayed from the right table 13Correct join 2Task 9Correct field displayed from the right table 13Correct subquery used 2Task 10Correct calculation of the number of tickets 1.5Correct calculation of sum of prices 1.5 4Correct Sorting 1Task 11Correct calculation to the count of tickets sold for each match 2Only more than 1 ticket sold 2 5Opening year is not 2013 1Task 12Top TWO largest stadiums with played matches 3Changing alias 1 5Correct Sorting 1Task 13 Correct country name and manager playing with Croatia 35manager initial is displayed 2Task 14Correct field displayed from the right table 1Correct condition 2 5A view is created with the right name 2Task 15Correct stored procedure with the right argument 2Correct field displayed from the right table 2 5Correct condition using the argument 1PENALTYTOTAL 60ISYS114 2018-Assignment 211 | PageFAQs from last semester1. When you say, display the details (id, name) of conference sessions, what does it mean?It means you need to print the details from the session table. But instead of every singlecolumn, you only must display what’s asked in the bracket.2. Sometimes, you are adding sentences that say, display ‘something else’ as well. What do wedo for that?Add that to your SELECT too!3. Can I submit a word document/pdf instead?No, we’re sorry! Please use the template and follow the guidelines4. Can I make my own data types up? I don’t like the ones you’ve givenNo, we’re sorry!5. Can I change the data to lower case! I feel like SQL is screaming at me.No, we’re sorry! Please stick to the case since it is data6. Can I display extra fields more than request in the task?Please display the ones that are asked. Nothing extra :)7. How to create a table with two PK? Or how to create a table with an FK?Please refer to lectures/workshops!8. You didn’t teach this or that in our lectures. How am I supposed to do this?90% of the assignment is based on whatever we have done in the lectures or workshops.You will have to do a bit of research and understand how SQL works. If the assignmentdoesn’t make you think, what’s the point?9. Do we need constraints in create query for PK and FKs?Yes, you should!10. What does ‘order of execution mean’?It means you put tables in the order in which you would create and populate them.11. What is the difference between data types, for example INT, SMALLINT and TINYINT?Please find more details about MySQL data type in this referencehttps://dev.mysql.com/doc/refman/8.0/en/data-type-overview.html转自：http://ass.3daixie.com/2018101920717868.html