DB(H) Database Systems Formative Assessment 3

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DB(H) Database Systems

Formative Assessment 3

Problem 1: Location-based Services

In Location-based Services (LBS), we are given a GPS point x = [x1, x2], i.e., our location in coordinates (longitude, latitude), and we try to find the closest  point q to our current point x from a set of points-of-interest. For instance, we would like to develop a LBS that, at every time instance, finds the closest Gas Station or Restaurant to our current position.

In order to quantify the distance between two location points, we adopt the geospatial Euclidean distance,  which  is the  square  root  of the  summation  of the  squared  difference  of the  location coordinates of the points x and q defined as:

d(x, q) = SQRT{ (x.x1 -q.x1 )2  + (x.x2 -q. x2 )2 }                                                                                      Eq(1)

where SQRT{z} is the square root of the non-negative number z.

Now,  back to the databases Assume that we store all the  points of interest in the following relation Point, where for each point we provide the two coordinates X1 and X2, corresponding to the longitude and latitude, and a Description, e.g., M&S,  Restaurant, etc. We also assign a unique  ID number for each point.

Point(ID, X1, X2, Description)

This is an instance of the relation Point and a corresponding example over the 2-dimensional plane:



Task: Assume that the blue point on the plane is our current position p = [2, 0], i.e., the longitude X1 =  2  and the  latitude X2  =  0.  Moreover, we  can  observe  the  other  points  corresponding to the locations of the Costa Café, RBS (Royal Bank of Scotland), an M&S. Our task is to retrieve the closest point q, i.e., which is the closest point-of-interest from the relation Point, to our current position p. Obviously, the provided SQL query should return only the point P3, which is the M&S.

Provide this SQL query given that the distance between two points is a built-in function as provided in Eq(1).

Hint: If the point q is the closest to a point p then there does not exist any other different point r closer to p than q (r ≠ q). Hence, dealing with EXISTS

Problem 2: DB Maintenance & Data Cleaning

Consider the known EMPLOYEE(SSN, FName …, DNO) relation, but … the designer did not declare the SSN to be the PK, thus, there might be duplicates of employees…Our task is to ‘clean’ the EMPLOYEE relation by finding the duplicate entries.

Task 1: Provide a SQL query which returns the duplicate employee entries.

As an example, the relation is e.g.,

EMPLOYEE

SSN FName

1

Philip

1

Philip

2

Stella

2

Stella

3

Iona

1

Philip

The SQL query then could show only the duplicate tuples, e.g.,

SSN FName

1

Philip

2

Stella

Task 2: Provide a SQL query which counts the duplicate employee entries. Considering the example in Task 1, the SQL should return the value of 3, since there are 3 extra employee entries (2 extra for Philip and one extra for Stella).

Problem 3: Analytics Query

Consider the known EMPLOYEE(SSN, FName, Salary, …, DNO) relation, with SSN (now) being the PK.

Task: Provide an analytics SQL query which shows the top-3 highest salary values without using the LIMIT operator .


2023-02-06