ISYS114: Introduction to Database Design and Management
ISYS114 2018 – ASSIGNMENT TWO WORTH 20%
The assignment includes the following components
ONLINE SUBMISSION WORTH 10%
IN-CLASS QUIZ WORTH 10%
ONLINE SUBMISSION DUE: 5PM FRIDAY 19 OCTOBER 2018
IN-CLASS QUIZ DUE: DURING WEEK 11 IN YOUR SCHEDULED WORKSHOP
ISYS114 2018-Assignment 2
2 | Page
Table of Contents
CASE BACKGROUND.....................................................................................................................3
TASK 1 (4 MARKS) .............................................................................................................................3
TASK 2 (4 MARKS) .............................................................................................................................4
TASK 3 (3 MARKS) .............................................................................................................................6
TASK 4 (3 MARKS) .............................................................................................................................6
TASK 5 (3 MARKS) .............................................................................................................................6
TASK 6 (4 MARKS) .............................................................................................................................6
TASK 7 (4 MARKS) .............................................................................................................................7
TASK 8 (3 MARKS) .............................................................................................................................7
TASK 9 (3 MARKS) .............................................................................................................................7
TASK 10 (4 MARKS) .........................................................................................................................7
TASK 11 (5 MARKS) .........................................................................................................................7
TASK 12 (5 MARKS) .........................................................................................................................7
TASK 13 (5 MARKS) .........................................................................................................................7
TASK 14 (5 MARKS) .........................................................................................................................8
TASK 15 (5 MARKS) .........................................................................................................................8
SUBMISSION FILE.........................................................................................................................9
SUBMISSION INSTRUCTIONS ........................................................................................................9
ASSIGNMENT-2 IN-CLASS ASSESSMENT ........................................................................................9
MARKING CRITERIA.................................................................................................................... 10
FAQS FROM LAST SEMESTER ...................................................................................................... 11
ISYS114 2018-Assignment 2
3 | Page
Case Background
USSIA was chosen to host the Football World Cup 2018. Russians wanted to make
it the best organised world cup ever. They intended to avoid any reason to cause
national shame. Therefore, they hired ‘you’ to be the RUSSIA2018 database
administrator to look after their data. As a part of your role, you need to do the following
tasks…Good Luck
Task 1 (4 marks)
Create the following tables based on the tables given below. You need to decide the right
order to create these tables.
Please note that the description of each table is for your understanding and not to be
used in SQL table creation.
TABLE NAME: STADIUM
COLUMN-NAME DATATYPE DESCRIPTION
STADIUMID [PK] CHAR(3) A unique ID for each stadium
STADIUMNAME VARCHAR(30) The name of the stadium
STADIUMCITY VARCHAR(20) The city of the stadium
CAPACITY INT The maximum capacity of the stadium
OPENYEAR YEAR The year the stadium was opened
TABLE NAME: FAN
COLUMN-NAME DATATYPE DESCRIPTION
FANID [PK] CHAR(9) A unique ID for each fan
FANNAME VARCHAR(25) The name of the fan
FANNATIONALITY VARCHAR(30) The nationality of the fan
FANCATEGORY CHAR(7) The category of the fan
TABLE NAME: MATCHES
COLUMN-NAME DATATYPE DESCRIPTION
MATCHID [PK] CHAR(5) A unique ID for each match
FIRSTTEAM [FK] CHAR(4) The ID of the first team in a match
SECONDTEAM [FK] CHAR(4) The ID of the second team in a match
MATCHDATE DATE The date of playing a match
KICKOFF TIME The starting time of a match
STADIUMID [FK] CHAR(3) The ID of the stadium to play a match
TABLE NAME: TEAM
COLUMN-NAME DATATYPE DESCRIPTION
TEAMID [PK] CHAR(4) A unique ID for each team
COUNTRY VARCHAR(20) The country of the team
CONTINENT VARCHAR(15) The continent of the team
FIFARANK TINYINT FIFA rank to the team
TEAMGROUP CHAR(1) The group a team plays in
MANAGER VARCHAR(20) The name of the manager of the team
NICKNAME VARCHAR(25) The nickname of the team
R
ISYS114 2018-Assignment 2
4 | Page
TABLE NAME: TICKET (will be provided to you)
COLUMN-NAME DATATYPE DESCRIPTION
TICKETID [PK] CHAR(15) A unique ID for each ticket
BLOCKNO CHAR(4) The number of the block
ROWNO CHAR(4) The location of the row
SEATNO CHAR(4) The location of the seat
PRICE SMALLINT The price of the ticket
MATCHID [FK] CHAR(5) The ID of the match of the ticket
FANID [FK] CHAR(9) The ID of the fan who purchased the ticket
Task 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 in
it and not letter ‘O’
STADIUM TABLE
STADIUMID STADIUMNAME STADIUMCITY CAPACITY OPENYEAR
S01 LUZHNIKI STADIUM MOSCOW 80000 1956
S02 SAINT PETERSBURG
STADIUM
SAINT
PETERSBURG
67000 2017
S03 FISHT STADIUM SOCHI 48000 2013
S04 KAZAN ARENA KAZAN 45000 2013
S05 SAMARA ARENA SAMARA 45000 2018
S06 SPARTAK STADIUM MOSCOW 42000 2014
FAN TABLE
FANID FANNAME FANNATIONALITY FANCATEGORY
F001 MONICA JONES AMERICAN SILVER
F002 DMITRIY VLADIMIR RUSSIAN GOLD
F003 ELLEN SMITH AUSTRALIAN SILVER
F004 MR. BEAN BRISTISH GOLD
F005 IGOR MIKHAIL RUSSIAN BRONZE
F006 ALBERTO HERNANDEZ BRAZILIAN SILVER
F007 NATALIA PAKLINA RUSSIAN BRONZE
F008 NANCY AJRAM LEBANESE SILVER
F009 JIE CHEN CHINESE GOLD
F010 MAX CAMERON AMERICAN SILVER
ISYS114 2018-Assignment 2
5 | Page
MATCHES TABLE
MATCHID FIRSTTEAM SECONDTEAM MATCHDATE KICKOFF STADIUMID
M01 T9 T17 2018-06-14 15:00:00 S01
M02 T5 T29 2018-06-14 16:00:00 S02
M03 T10 T19 2018-06-16 18:00:00 S03
M04 T10 T16 2018-06-19 20:00:00 S02
M05 T1 T15 2018-06-19 20:00:00 S04
M06 T2 T4 2019-06-20 15:00:00 S06
M07 T11 T19 2018-07-15 20:00:00 S01
M08 T15 T19 2018-07-15 16:00:00 S02
TEAM TABLE (will be provided to you)
TEAMID COUNTRY CONTINENT FIFARANK TEAMGROUP MANAGER NICKNAME
T1 ARGENTINA SOUTH
AMERICA 5 D JORGE
SAMPAOLI
WHITE AND
SKY BLUES
T10 AUSTRALIA OCEANS 36 C BERT VAN
MARWIJK SOCCEROOS
T11 BELGIUM EUROPE 3 G ROBERTO
MARTíNEZ RED DEVILS
T12 BRAZIL SOUTH
AMERICA 2 E TITE THE
SELECTION
T13 COLOMBIA SOUTH
AMERICA 16 H JOSE PEKERMAN THE COFFEE
GROWERS
T14 COSTA RICA SOUTH
AMERICA 23 E LOS TICOS
T15 CROATIA EUROPE 20 D ZLATKO DALIC THE
BLAZERS
T16 DENMARK EUROPE 12 C AGE HAREIDE DANISH
DYNAMITE
T17 EGYPT AFRICA 45 A HECTOR CUPER PHARAOHS
T18 ENGLAND EUROPE 13 G GARETH
SOUTHGATE
THREE
LIONS
T19 FRANCE EUROPE 7 C DIDIER
DESCHAMPS THE BLUES
T2 GERMANY EUROPE 1 F JOACHIM LOW
T20 ICELAND EUROPE 22 D HEIMIR
HALLGRIMSSON OUR BOYS
T21 IRAN ASIA 37 B CARLOS
QUEIROZ TEAM MELLI
T22 JAPAN ASIA 61 H VAHID
HALILHODZIC
BLUE
SAMURAI
T23 MEXICO SOUTH
AMERICA 15 F JUAN CARLOS
OSORIO
EL
TRICOLOR
T24 MOROCCO AFRICA 41 B HERVE RENARD ATLAS
LIONS
T25 NIGERIA AFRICA 48 D GERNOT ROHR SUPER
EAGLES
T26 PANAMA SOUTH
AMERICA 55 G HERNAN DARIO
GOMEZ
THE RED
TIDE
T27 PERU SOUTH
AMERICA 11 C RICARDO
GARECA LOS INCAS
T28 POLAND EUROPE 8 H ADAM
NAWALKA
THE POLISH
EAGLES
T29
PORTUGAL EUROPE 4 B FERNANDO
SANTOS
TEAM OF
THE
CASTLES
T3 RUSSIA ASIA 70 A STANISLAV
CHERCHESOV
SBORNAYA
(TEAM)
ISYS114 2018-Assignment 2
6 | Page
T30 SAUDI
ARABIA ASIA 67 A BERT VAN
MARWIJK
THE GREEN
FALCONS
T31
SENEGAL AFRICA 27 H ALIOU CISSE
THE LIONS
OF
TERANGA
T32 SERBIA EUROPE 34 E MLADEN
KRSTAJIC THE EAGLES
T4 SOUTH KOREA ASIA 57 F SHIN TAE-YONG ASIAN
TIGERS
T5 SPAIN EUROPE 10 B JULEN
LOPETEGUI
T6 SWEDEN EUROPE 24 F JANNE
ANDERSSON
T7 SWITZERLAND EUROPE 6 E VLADIMIR
PETKOVIC
THE RED
CRUSADERS
T8
TUNISIA AFRICA 21 G NABIL
MAALOUL
THE EAGLES
OF
CARTHAGE
T9 URUGUAY SOUTH
AMERICA 14 A OSCAR
TABAREZ CHARRUAS
TICKET TABLE
TICKETID BLOCKNO ROWNO SEATNO PRICE MATCHID FANID
Tic1 A112 23 19 150 M01 F001
Tic10 D109 9 20 500 M06 F009
Tic2 B75 45 11 185 M02 F003
Tic3 D109 7 21 200 M02 F005
Tic4 B80 41 8 250 M03 F001
Tic5 E123 23 15 300 M03 F004
Tic6 A266 17 24 375 M05 F002
Tic7 B45 25 11 350 M05 F009
Tic8 F11 23 14 300 M06 F007
Tic9 C233 7 23 750 M08 F001
Task 3 (3 marks)
Write an SQL statement to display match ID for the matches that starts after 16:00. Sort
the 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 within
a range of 2 to 5. Sort the result by FIFA Rank in an ascending order. The country and the
continent 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 either
in 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 at
stadium "KAZAN ARENA". Each kick off time should start with the text “starts at”.
ISYS114 2018-Assignment 2
7 | Page
Task 7 (4 marks)
Write an SQL statement to display the first 3 characters of a country name whose
manager is blank ( change the alias to “COUNTRY INITIALS”), also display the match date
and 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 at
least 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 at
least 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 category
the fan bought (change the alias to “NUMBER OF TICKETS PURCHASED”) along with the
sum of prices (change the alias to 'TOTAL PRICES') in each category the fans paid. Sort
the 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 match
ONLY if the number of tickets sold of this match is more than 1 and the open year of the
stadium 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 played
in each stadium (change the alias to “NUMBER OF MATCHES”). Display only the top two
stadiums where most matches were held. Sort the result by the number of matches played
on 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 the
teams in any match (either as a first or a second team). Manager’s name should be
displayed as initialisation of first name followed by a dot and then the last name. The alias
of the manager column should be changed to “Manager Initials”. For example, if
manager’s name is David Smith, it will be displayed as D. Smith.
ISYS114 2018-Assignment 2
8 | Page
The 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 the
ticket 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 will
be provided as an argument in the created stored procedure.
ISYS114 2018-Assignment 2
9 | Page
Submission File
Download the file ‘Submit_This.txt’ provided in your Assignment-2 folder on iLearn. This
will 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 writing
queries, run them, check the results, copy and insert the codes at the right place in the
template.
Use the provided SQL code (i.e. TICKET table creation and TEAM records) in the file
"Assignment2_TicketTable_TeamRecords.txt” on iLearn as a contribution to your
assignment ( 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 inserted
all 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 you've enrolled in.
In-class assessment will base on assignment-2 case study with similar queries as the ones
in the online submission.
A Student must physically attend the scheduled workshops to attempt the in-class
assessment. 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 2
10 | Page
Task Marking Criteria Breaking down
the mark Mark
Task 1 Four tables created (4X1) 4
Task 2 Four tables populated correctly (4X1) 4
Task 3
Correct field displayed from the right table 1
Correct condition 1 3
Correct Sorting 1
Task 4
Correct field displayed from the right table 1
Correct condition 1 3
Right lower case to the displayed fields 1
Task 5
Correct field displayed from the right table 0.5
Correct condition 2 3
Correct Sorting 0.5
Task 6
Correct field displayed from the right table 1
Text "starts at" is displayed before kick-off time 1 4
correct condition 2
Task 7
correct country name, match date and kick-off for blank manager is displayed 1
4 Only first three character displayed 1
Correct condition 1
Correct Sorting 1
Task 8 Correct field displayed from the right table 1
3
Correct join 2
Task 9
Correct field displayed from the right table 1
3
Correct subquery used 2
Task 10
Correct calculation of the number of tickets 1.5
Correct calculation of sum of prices 1.5 4
Correct Sorting 1
Task 11
Correct calculation to the count of tickets sold for each match 2
Only more than 1 ticket sold 2 5
Opening year is not 2013 1
Task 12
Top TWO largest stadiums with played matches 3
Changing alias 1 5
Correct Sorting 1
Task 13 Correct country name and manager playing with Croatia 3
5
manager initial is displayed 2
Task 14
Correct field displayed from the right table 1
Correct condition 2 5
A view is created with the right name 2
Task 15
Correct stored procedure with the right argument 2
Correct field displayed from the right table 2 5
Correct condition using the argument 1
PENALTY
TOTAL 60
ISYS114 2018-Assignment 2
11 | Page
FAQs from last semester
1. 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 single
column, 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 we
do 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 guidelines
4. Can I make my own data types up? I don’t like the ones you’ve given
No, 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 data
6. 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 assignment
doesn’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 reference
https://dev.mysql.com/doc/refman/8.0/en/data-type-overview.html
版权所有：编程辅导网 2021 All Rights Reserved 联系方式：QQ:99515681 微信：codinghelp 电子信箱：99515681@qq.com
免责声明：本站部分内容从网络整理而来，只供参考！如有版权问题可联系本站删除。
