辅导Databases语言、辅导R程序、讲解R留学生程序 讲解Java程序|辅导R语言程序
- 首页 >> Java编程 University of Sussex Autumn 2020
Informatics
Databases
Assignment 2 (Deadline 04.01.2021, 4pm)
This assessed coursework should be submitted online as Canvas E-submission.
The due date of this coursework is 4pm on Monday, 4th of January 2021.
This coursework has two parts, and your submission should consist of two
separate files, one for your answers to the questions in Part 1 and one for
your answers to the questions in Part 2.
1. Your solutions to the questions in Part 1 should be inserted into the
file a2.sql which you can download from the submission point in
Canvas. You should insert your code for each question after the corresponding
question comment.
2. Your solutions to the questions in Part 2 should be submitted as the
pdf file a2.pdf. You must submit a pdf file.
All questions in both Parts 1 and 2 should be answered.
There is a total of 100 marks available with 70 marks in Part 1 and 30 in
Part 2.
You must work on this assignment on your own. The standard Informatics
rules for collusion, plagiarism and lateness apply. Any cases of potential
misconduct discovered will be reported and investigated.
1
Part 1
Answer all questions in Part 1.
Detailed Instructions (please read these instructions carefully)
• Part 1 of the assignment refers to an implementation of a motorsport
database. This implements the specification given in Figure 1 below.
• To be able to answer the questions in Part 1 of the assignment you
must first run the SQL scripts a2-setup.sql and
a2-setup-additional.sql that define the tablesthat your code
will rely on. These files are available on the Databases Canvas site.
For the completion of this assignment it will be helpful to inspect the
table structure set up by this script. Do not modify the structure of
the tables when you write your answers unless explicitly told to do so
below.
Note that a few data records have been inserted into the tables to help
you test your answers. It is recommended that you test your code
with additional sample data you insert into the tables yourself. However,
do not include any of the test data or the corresponding insert
statements in your submission. Also, you must not include the code
of a2-setup.sql and a2-setup-additional.sql in your
answer.
• All your code must run on our ITS server where it will be tested for
marking purposes, so make sure you get the expected results on our
ITS server. If you test it on other servers you might get different
behaviour. Don’t forget that in MySQL table names are case sensitive.
• You must not deviate from the requested order and name of the columns
in queries. Note that for every query the names and order of columns
is clearly specified in the paper.
• For each question you should give only one SQL statement as an
answer. Copy this SQL statement directly below the corresponding
question comment, e.g. --@@01 if you answer Question 1, on a new
line in the template file.
Below is an example where to put your answer your Question 1 and
Question 3 and how to leave Question 2 unanswered:
2
-- @@01
SELECT myanswertoQ1 FROM mytable WHERE 1=1
-- @@02
-- @@03
SELECT myanswertoQ3 FROM mytable WHERE 1=1
-- @@04
Do not remove any of the template comments.
• Where a query is very complex, you might wish to add comments to
help the marker appreciate what you have done in case your query is
not correct.
Marking Guidance
• Statements that throw a syntax error will receive 0 marks.
• Correctly running statements will receive marks proportional to how
close their result is to the correct answer.
There are 14 questions in Part 1 of this assignment which you will find on
the following pages.
3
Specification
A race has a name and must take place at a single race course at a
specific date and at a specific starting time. A race has a certain number
of laps (around the race course) and a total length in miles. No two
races of the same name take place on the same day.
A race course has a unique name, a location, and a length in miles.
For each driver, we keep the name, consisting of first and last name,
nationality, date of birth and a unique driver identifier. Drivers always
belong to a single racing team. We need to find out which driver drove
which car in which race. We also need to store in which place they arrived
at the finish (non-finishers are recorded as arrived in 0-th place).
Drivers drive at most one car in a race but not all drivers do. There are
no driver swaps during a race but at different races different drivers may
be driving the same car. The type of tyres on the car at the start of the
race is recorded as well as the car’s racing number for that particular
race.
Cars have a unique identifier and must belong to a racing team. The
make of the engine of a car is relevant too. Not every car is necessarily
driven in a race.
A car may complete a lap of a race in which case the lap time and fuel
consumption are to be recorded. A car may have a pit stop during a
lap in a race, in which case the duration of the pit stop and the items
that have been changed during the pit stop (tyres, front nose, etc.) are
recorded. A car may retire in a lap of a race, in which case the reason
for the retirement is to be recorded. In all these cases, we need to know
in which lap of which race this occurred.
Important aspects of a racing team are its name and the address of the
team’s headquarter consisting of postcode, street name, house number.
Racing teams may be entered on the database before any drivers
or cars are assigned to them.
Table 1: Description of the motor sport fan’s database for Question 1
4
1. Write one SQL statement to set up table MoSpo HallOfFame according
to the following Relational Schema:
MoSpo HallOfFame(hoFdriverId, hoFYear, hoFSeries, hoFImage,
hoFWins, hoFBestRaceName, hoFBestRaceDate)
primary key (hoFdriverId,hoFYear)
foreign key (hoFdriverId) references MoSpo Driver(driverId)
foreign key (hoFBestRaceName,hoFBestRaceDate) references
MoSpo Race(raceName,raceDate)
Your code must execute without error, assuming that all other tables
have been set up by running scripts a2-setup.sql and a2-setup-additional.sql.
[10 marks]
The data types you choose for the columns should be most appropriate
for the data they will contain. You must also accommodate the
following requirements:
(a) For table and column names you must pick exactly the names
used in the schema above (otherwise you will lose marks as tests
will fail).
(b) hoFYear is a 4-digit number representing a year between 1901
and 2155 (or 0000).
(c) hoFSeries is one of the following strings: BritishGT, Formula1,
FormulaE, SuperGT. Please make sure you use the correctspelling.
These column values, when ordered, should always appear in the
order they have been listed above. Values for this attribute must
not be missing.
(d) hoFWins is a positive integer number and never larger than 99.
The default is 0 but values can be missing.
(e) hoFImage is a path to an image document which is a string never
longer than 200 characters. This value can be missing.
(f) Equip any foreign key constraints with constraint names of your
choosing.
(g) Ensure that if a driver is deleted from the database their corresponding
hall of fame entries are deleted automatically too.
(h) Ensure that if a race is deleted from the database then foreign key
values in hall of fame entries that reference it are automatically
set to null.
5
Instructions for Question 2–13
For each of the tasks specified below write one single SQL query,
respectively, that solves the task. You can use nested queries (ie. subselects
and subqueries) wherever you like.
You must not CREATE any tables of any form and you must not use
(nor declare) any stored procedures or functions in this section.
You must produce column headings as specified for each query. Do
not change order or name of the columns as this will cause tests to
fail which will cost you marks.
Double check that headings are exactly as specified.
It is important that your queries will work correctly with any data
(according to the schema).
All references to time, when not explicit, are relative and refer to the
time of running the query.
2. The weight of drivers has been omitted from the MoSpo Driver table.
Without deleting and recreating the table, add a column driverWeight
to the already created table that allows values to be missing.
Take into consideration that a driver’s weight is always in the range
0.0 to 99.9.
[3 marks]
3. Change the postcode of the racing team Beechdean Motorsport to (the
following string) HP135PN.
[4 marks]
4. Remove all drivers with last name Senna and first name Ayrton (whatever
the capitalisation) from the database.
[4 marks]
5. Find out how many racing teams are on the database. The heading
must be numberTeams.
[2 marks]
6
6. List all racing drivers (driver id, name and dob) whose last name begins
with the same letter as their first name. The name of the driver
should be given as a string consisting of the initial from the first name,
followed by a blank, followed by their last name. So a driver with first
name Alan and last name Turing would be listed as A Turing. The
headings must look like this:
driverId driverName driverDOB
[4 marks]
7. List for each racing team how many drivers they have associated with
them. Only include teams with more than one driver. The headings
must look like this:
teamName numberOfDriver
[4 marks]
8. For each race list the fastest lap time. The information provided
should include race name, race date, lap time. No races must appear
for which there is no proper such minimal time available. The
headings must look like this:
raceName raceDate lapTime
[5 marks]
9. Given a race (name) and a year, ‘total pitstops’ is the total number of
pitstops of all cars in the given race that year. For each race name
compute the average of the number of ‘total pitstops’ based on the
years we have data for. The headings must look like this:
raceName avgStops
[5 marks]
7
10. A car (of a race entry) retires in a lap if the corresponding attribute
lapInfoCompleted has value 0. Find out all the (different) makes
of cars that had to retire in a race in the year 2018. The heading must
look like this:
carMake
[5 marks]
11. For each race, compute the highest number of pitstops any car had.
Provide race name and date as well as the highest number of stops.
Races with no pitstops recorded at all should appear with a 0. The
headings must look like this:
raceName raceDate mostPitstops
[5 marks]
12. List all drivers (id, last name) who had no retirement ever. Note that
the reason for not having had a retirement may well be that the driver
never participated in a race.
The headings must look like this:
driverId driverLastName
[5 marks]
13. For any given care make m and time period t, let RetirementsRatem(t)
be the total number of retirements of cars of make m divided by the
total number of cars of make m taking part in a race during time t.
In case no car of make m participated in race r during period t this
number is undefined (NULL).
For example, let t be the year 2000 and m = Porsche. Assume that
in the year 2000 there were two races with Porsche cars involved. In
the first race 2 cars of that make raced and 1 had a retirement. In the
second race 3 cars of that make raced with 0 retirements. Therefore,
we get that RetirementsRatePorsche (t) = 1
5 = 0.2.
8
For a period t, let AverageRetirementRate(t) be the average of retirement
rates for period t across all makes m, i.e. the average of
RetirementsRatem(t) ignoring undefined values, over all makes m.
List for each car make m the retirement rate RetirementRatem(t) where
t is 2018. Only select car makes m with a retirement rate above the
average retirement rate across all makes for the same period t, i.e.
where RetirementRatem(t) > AverageRetirementRate(t). The headings
must look like this:
carMake retirementRate
[6 marks]
Additional Instructions (Stored Procedures) Question 14
Note that successfully declaring a stored procedure does not necessarily
mean it runs without error. You need to run and test your procedures
to ensure that. Strictly name the stored procedure as indicated in
the question. You are not allowed to include any other stored routine
definitions.
14. Write a stored function totalRaceTime that, given a racing number,
the name of a race, and the date of a race, returns the total race
time for the car specified by the racing number in the given race. If
the given race does not exist, the routine should throw the error with
message procedure Race does not exist. If the specified racing number
did not take part in the existing race, the routine should throw an
error with message procedure RaceEntry does not exist.
In the case that not all required lap times for the (existing) car in the
(existing) race are available either until race finish or retirement, the
routine should throw an error with message procedure TimeForAllLaps
does not exist.
If the (existing) race was not completed by the (participating) car in
the race due to retirement but all lap times were available until retirement,
the routine must not throw an error but return null.
Note that in those error cases the function must not return a string but
produce an SQL error.
9
The total race time should be returned as an integer denoting milliseconds.
Note that this stored routine has three arguments and you must
declare them in the order given above.
[8 marks]
Please see the next page for Part 2 of the assignment.
10
Part 2
Please answer all questions and submit your answers as a PDF file called
a2.pdf.
1. What is the purpose of normalisation? What problems does normalisation
gets rid of? When is it used?
[4 marks]
2. Consider the relational schema R with key (a, b, c) and no alternative
keys. Below all its full functional dependencies are listed.
R(a, b, c, d, e, f, g, h)
{a, c} → e
{a, b} → d
b → {g, h}
{a, b, c} → f
g → h
(a) Explain in which normal form the schema is.
[3 marks]
(b) Normalise schema R above to 3rd Normal Form. Make sure you
declare primary and foreign keys correctly whenever you introduce
a new schema. Also state explicitly which schema(s) are
intermediate and which are part of the result.
[8 marks]
3. What is a transaction in databases?
[2 marks]
4. Explain ONE of the problems that can occur in a database table when
transactions are carried out concurrently.
[2 marks]
11
5. Explain TWO techniques a database programmer can use to avoid
running into the problems mentioned in the answer to Question 4.
[4 marks]
6. Let the following database schema be given.
Person(idnumber, name, dob, mother, father)
primary key idnumber
foreign key father references Person(idnumber)
foreign key mother references Person(idnumber)
Consider the following query:
For the person with idnumber 42 produce a list of all their ancestors.
Note that ancestors include grandparents, great-grandparents and so
on.
(a) Outline how you would solve this task programmatically, addressing
which particular SQL shortcoming you have to overcome.
[5 marks]
(b) What other kind of database might be more appropriate for the
above query and why?
[2 marks]
12
Informatics
Databases
Assignment 2 (Deadline 04.01.2021, 4pm)
This assessed coursework should be submitted online as Canvas E-submission.
The due date of this coursework is 4pm on Monday, 4th of January 2021.
This coursework has two parts, and your submission should consist of two
separate files, one for your answers to the questions in Part 1 and one for
your answers to the questions in Part 2.
1. Your solutions to the questions in Part 1 should be inserted into the
file a2.sql which you can download from the submission point in
Canvas. You should insert your code for each question after the corresponding
question comment.
2. Your solutions to the questions in Part 2 should be submitted as the
pdf file a2.pdf. You must submit a pdf file.
All questions in both Parts 1 and 2 should be answered.
There is a total of 100 marks available with 70 marks in Part 1 and 30 in
Part 2.
You must work on this assignment on your own. The standard Informatics
rules for collusion, plagiarism and lateness apply. Any cases of potential
misconduct discovered will be reported and investigated.
1
Part 1
Answer all questions in Part 1.
Detailed Instructions (please read these instructions carefully)
• Part 1 of the assignment refers to an implementation of a motorsport
database. This implements the specification given in Figure 1 below.
• To be able to answer the questions in Part 1 of the assignment you
must first run the SQL scripts a2-setup.sql and
a2-setup-additional.sql that define the tablesthat your code
will rely on. These files are available on the Databases Canvas site.
For the completion of this assignment it will be helpful to inspect the
table structure set up by this script. Do not modify the structure of
the tables when you write your answers unless explicitly told to do so
below.
Note that a few data records have been inserted into the tables to help
you test your answers. It is recommended that you test your code
with additional sample data you insert into the tables yourself. However,
do not include any of the test data or the corresponding insert
statements in your submission. Also, you must not include the code
of a2-setup.sql and a2-setup-additional.sql in your
answer.
• All your code must run on our ITS server where it will be tested for
marking purposes, so make sure you get the expected results on our
ITS server. If you test it on other servers you might get different
behaviour. Don’t forget that in MySQL table names are case sensitive.
• You must not deviate from the requested order and name of the columns
in queries. Note that for every query the names and order of columns
is clearly specified in the paper.
• For each question you should give only one SQL statement as an
answer. Copy this SQL statement directly below the corresponding
question comment, e.g. --@@01 if you answer Question 1, on a new
line in the template file.
Below is an example where to put your answer your Question 1 and
Question 3 and how to leave Question 2 unanswered:
2
-- @@01
SELECT myanswertoQ1 FROM mytable WHERE 1=1
-- @@02
-- @@03
SELECT myanswertoQ3 FROM mytable WHERE 1=1
-- @@04
Do not remove any of the template comments.
• Where a query is very complex, you might wish to add comments to
help the marker appreciate what you have done in case your query is
not correct.
Marking Guidance
• Statements that throw a syntax error will receive 0 marks.
• Correctly running statements will receive marks proportional to how
close their result is to the correct answer.
There are 14 questions in Part 1 of this assignment which you will find on
the following pages.
3
Specification
A race has a name and must take place at a single race course at a
specific date and at a specific starting time. A race has a certain number
of laps (around the race course) and a total length in miles. No two
races of the same name take place on the same day.
A race course has a unique name, a location, and a length in miles.
For each driver, we keep the name, consisting of first and last name,
nationality, date of birth and a unique driver identifier. Drivers always
belong to a single racing team. We need to find out which driver drove
which car in which race. We also need to store in which place they arrived
at the finish (non-finishers are recorded as arrived in 0-th place).
Drivers drive at most one car in a race but not all drivers do. There are
no driver swaps during a race but at different races different drivers may
be driving the same car. The type of tyres on the car at the start of the
race is recorded as well as the car’s racing number for that particular
race.
Cars have a unique identifier and must belong to a racing team. The
make of the engine of a car is relevant too. Not every car is necessarily
driven in a race.
A car may complete a lap of a race in which case the lap time and fuel
consumption are to be recorded. A car may have a pit stop during a
lap in a race, in which case the duration of the pit stop and the items
that have been changed during the pit stop (tyres, front nose, etc.) are
recorded. A car may retire in a lap of a race, in which case the reason
for the retirement is to be recorded. In all these cases, we need to know
in which lap of which race this occurred.
Important aspects of a racing team are its name and the address of the
team’s headquarter consisting of postcode, street name, house number.
Racing teams may be entered on the database before any drivers
or cars are assigned to them.
Table 1: Description of the motor sport fan’s database for Question 1
4
1. Write one SQL statement to set up table MoSpo HallOfFame according
to the following Relational Schema:
MoSpo HallOfFame(hoFdriverId, hoFYear, hoFSeries, hoFImage,
hoFWins, hoFBestRaceName, hoFBestRaceDate)
primary key (hoFdriverId,hoFYear)
foreign key (hoFdriverId) references MoSpo Driver(driverId)
foreign key (hoFBestRaceName,hoFBestRaceDate) references
MoSpo Race(raceName,raceDate)
Your code must execute without error, assuming that all other tables
have been set up by running scripts a2-setup.sql and a2-setup-additional.sql.
[10 marks]
The data types you choose for the columns should be most appropriate
for the data they will contain. You must also accommodate the
following requirements:
(a) For table and column names you must pick exactly the names
used in the schema above (otherwise you will lose marks as tests
will fail).
(b) hoFYear is a 4-digit number representing a year between 1901
and 2155 (or 0000).
(c) hoFSeries is one of the following strings: BritishGT, Formula1,
FormulaE, SuperGT. Please make sure you use the correctspelling.
These column values, when ordered, should always appear in the
order they have been listed above. Values for this attribute must
not be missing.
(d) hoFWins is a positive integer number and never larger than 99.
The default is 0 but values can be missing.
(e) hoFImage is a path to an image document which is a string never
longer than 200 characters. This value can be missing.
(f) Equip any foreign key constraints with constraint names of your
choosing.
(g) Ensure that if a driver is deleted from the database their corresponding
hall of fame entries are deleted automatically too.
(h) Ensure that if a race is deleted from the database then foreign key
values in hall of fame entries that reference it are automatically
set to null.
5
Instructions for Question 2–13
For each of the tasks specified below write one single SQL query,
respectively, that solves the task. You can use nested queries (ie. subselects
and subqueries) wherever you like.
You must not CREATE any tables of any form and you must not use
(nor declare) any stored procedures or functions in this section.
You must produce column headings as specified for each query. Do
not change order or name of the columns as this will cause tests to
fail which will cost you marks.
Double check that headings are exactly as specified.
It is important that your queries will work correctly with any data
(according to the schema).
All references to time, when not explicit, are relative and refer to the
time of running the query.
2. The weight of drivers has been omitted from the MoSpo Driver table.
Without deleting and recreating the table, add a column driverWeight
to the already created table that allows values to be missing.
Take into consideration that a driver’s weight is always in the range
0.0 to 99.9.
[3 marks]
3. Change the postcode of the racing team Beechdean Motorsport to (the
following string) HP135PN.
[4 marks]
4. Remove all drivers with last name Senna and first name Ayrton (whatever
the capitalisation) from the database.
[4 marks]
5. Find out how many racing teams are on the database. The heading
must be numberTeams.
[2 marks]
6
6. List all racing drivers (driver id, name and dob) whose last name begins
with the same letter as their first name. The name of the driver
should be given as a string consisting of the initial from the first name,
followed by a blank, followed by their last name. So a driver with first
name Alan and last name Turing would be listed as A Turing. The
headings must look like this:
driverId driverName driverDOB
[4 marks]
7. List for each racing team how many drivers they have associated with
them. Only include teams with more than one driver. The headings
must look like this:
teamName numberOfDriver
[4 marks]
8. For each race list the fastest lap time. The information provided
should include race name, race date, lap time. No races must appear
for which there is no proper such minimal time available. The
headings must look like this:
raceName raceDate lapTime
[5 marks]
9. Given a race (name) and a year, ‘total pitstops’ is the total number of
pitstops of all cars in the given race that year. For each race name
compute the average of the number of ‘total pitstops’ based on the
years we have data for. The headings must look like this:
raceName avgStops
[5 marks]
7
10. A car (of a race entry) retires in a lap if the corresponding attribute
lapInfoCompleted has value 0. Find out all the (different) makes
of cars that had to retire in a race in the year 2018. The heading must
look like this:
carMake
[5 marks]
11. For each race, compute the highest number of pitstops any car had.
Provide race name and date as well as the highest number of stops.
Races with no pitstops recorded at all should appear with a 0. The
headings must look like this:
raceName raceDate mostPitstops
[5 marks]
12. List all drivers (id, last name) who had no retirement ever. Note that
the reason for not having had a retirement may well be that the driver
never participated in a race.
The headings must look like this:
driverId driverLastName
[5 marks]
13. For any given care make m and time period t, let RetirementsRatem(t)
be the total number of retirements of cars of make m divided by the
total number of cars of make m taking part in a race during time t.
In case no car of make m participated in race r during period t this
number is undefined (NULL).
For example, let t be the year 2000 and m = Porsche. Assume that
in the year 2000 there were two races with Porsche cars involved. In
the first race 2 cars of that make raced and 1 had a retirement. In the
second race 3 cars of that make raced with 0 retirements. Therefore,
we get that RetirementsRatePorsche (t) = 1
5 = 0.2.
8
For a period t, let AverageRetirementRate(t) be the average of retirement
rates for period t across all makes m, i.e. the average of
RetirementsRatem(t) ignoring undefined values, over all makes m.
List for each car make m the retirement rate RetirementRatem(t) where
t is 2018. Only select car makes m with a retirement rate above the
average retirement rate across all makes for the same period t, i.e.
where RetirementRatem(t) > AverageRetirementRate(t). The headings
must look like this:
carMake retirementRate
[6 marks]
Additional Instructions (Stored Procedures) Question 14
Note that successfully declaring a stored procedure does not necessarily
mean it runs without error. You need to run and test your procedures
to ensure that. Strictly name the stored procedure as indicated in
the question. You are not allowed to include any other stored routine
definitions.
14. Write a stored function totalRaceTime that, given a racing number,
the name of a race, and the date of a race, returns the total race
time for the car specified by the racing number in the given race. If
the given race does not exist, the routine should throw the error with
message procedure Race does not exist. If the specified racing number
did not take part in the existing race, the routine should throw an
error with message procedure RaceEntry does not exist.
In the case that not all required lap times for the (existing) car in the
(existing) race are available either until race finish or retirement, the
routine should throw an error with message procedure TimeForAllLaps
does not exist.
If the (existing) race was not completed by the (participating) car in
the race due to retirement but all lap times were available until retirement,
the routine must not throw an error but return null.
Note that in those error cases the function must not return a string but
produce an SQL error.
9
The total race time should be returned as an integer denoting milliseconds.
Note that this stored routine has three arguments and you must
declare them in the order given above.
[8 marks]
Please see the next page for Part 2 of the assignment.
10
Part 2
Please answer all questions and submit your answers as a PDF file called
a2.pdf.
1. What is the purpose of normalisation? What problems does normalisation
gets rid of? When is it used?
[4 marks]
2. Consider the relational schema R with key (a, b, c) and no alternative
keys. Below all its full functional dependencies are listed.
R(a, b, c, d, e, f, g, h)
{a, c} → e
{a, b} → d
b → {g, h}
{a, b, c} → f
g → h
(a) Explain in which normal form the schema is.
[3 marks]
(b) Normalise schema R above to 3rd Normal Form. Make sure you
declare primary and foreign keys correctly whenever you introduce
a new schema. Also state explicitly which schema(s) are
intermediate and which are part of the result.
[8 marks]
3. What is a transaction in databases?
[2 marks]
4. Explain ONE of the problems that can occur in a database table when
transactions are carried out concurrently.
[2 marks]
11
5. Explain TWO techniques a database programmer can use to avoid
running into the problems mentioned in the answer to Question 4.
[4 marks]
6. Let the following database schema be given.
Person(idnumber, name, dob, mother, father)
primary key idnumber
foreign key father references Person(idnumber)
foreign key mother references Person(idnumber)
Consider the following query:
For the person with idnumber 42 produce a list of all their ancestors.
Note that ancestors include grandparents, great-grandparents and so
on.
(a) Outline how you would solve this task programmatically, addressing
which particular SQL shortcoming you have to overcome.
[5 marks]
(b) What other kind of database might be more appropriate for the
above query and why?
[2 marks]
12