2023年11月25日

数据库代写 | COMP1004.G51DBI-E1

本次数据库代写是创建相应的球员数据库并完成对应的web网页查询

COMP1004.G51DBI-E1

1. Relational Databases (40 marks)
a) Consider the following relations:
Players
ID pName Gender YearsPro
001 Pernille Female 16
002 Ronaldo Male 20
003 Stoney Female 12
004 Kane Male 08
005 Erin Female 16
Team
ID tTeam
001 Wolfsburg
002 Juventus F.C
003 Arsenal L.F.C
005 Chelsea FC Women
008 Juventus F.C
Give the resulting table, where applicable, of the following relational algebra expressions
(or explain why they are not valid):
i) πID(Players)-πID(Team)
[4 marks]
ii) σGender = Male(Players)
[4 marks]
iii) πtName(σID=004(Team))UπGender(σID=003(Players))
[4 marks]
Write a relational algebra expression to calculate:
iv) The Names and IDs of players who have played for more than 10 year and who are
male.
[6 marks]
v) Rename ‘pName’ and ‘YearsPro’ in the relation ‘Players’ to ‘Name’ and ‘Year’
respectively.
[6 marks]
b) Provide the following definitions:
i) ‘Outer Join between relations called R and S’;
[4 marks]
ii) ‘Foreign key constraints’ in the context of relational data integrity;
[4 marks]
Turn over
c) Complete the following table using the 3-valued logic adopted in the presence of missing
information:
a b a OR b a AND b a == b
True
False
Unknown
Unknown
Unknown
True
False
Unknown
Unknown
False
Unknown
Unknown
[8 marks]

2. Web and PHP Programming (20 marks)
Consider the following code.
<html>
<head>
<script>
function doIt() {
var TH = document.getElementById(“myform”);
var ix = Number(TH.elements[4].value) + 1;
alert(TH.elements[ix].value);
TH.elements[4].value = ix;
}
</script>
</head>
<body>
<form id=”myform”>
<br>First Name:
<input type=”text” name=”firstname” value=”John”>
<br>Last Name:
<input type=”text” name=”lastname” value=”Smith”>
<br>Age:
<input type=”number” name=”age” value=”25″>
<br>Height:
<input type=”number” name=”height” value=”186″>
<br>
<input type=”hidden” name=”secret” value=”0″>
<input type=”button” value=”Do it!” onClick=”doIt()”>
</form>
</body>
</html>
a) What will happen when the button “Do it!” gets clicked?
[4 points]
b) What will happen when the button “Do it!” gets clicked a second time?
[2 points]
Turn over
Consider the following tables, which are part of a MySQL database used for a university
courses enrolment web site. The database is named registrationDB. The Students table
lists individual students. The Courses table lists courses offered across university campuses.
The Enrolment table lists individual enrolments and relates courses to students; students
can be enrolled in multiple courses.

Assume that you are creating a web site using that is served from the same machine that is
running the database, and that you are using PHP together with the MySQL extensions to
communicate with the database. A connection to the database has been established as
follows:
$connection = new mysqli(“localhost”, “user1”, “pass1”, “registrationDB”);
Complete the following tasks for the website:
c) Write an SQL query that will return the codes of all courses offered in the Malaysia campus
and assign this to a PHP string variable called $sqlquery
[2 points]
d) Write a PHP code fragment that will submit the $sqlquery query to the database and
display the results each on a separate line.
[6 points]

e) The following web form has been designed to allow users to search the database and will
return a list of courses in any given country.
<form action=”search.php” method=”get”>
Country: <input type=”text” name=”country”><br>
<input type=”submit”>
</form>
When the “submit” button is clicked, the script in the search.php is invoked and the
“country” text field is passed to it. Write PHP code that will read the input text from the
form, construct the SQL query in a string called $sqlquery and then submit this to the
database.