Sample Test
This is a sample test for an in-person test; in case of distance learning, there will be an online A/B/C/D test.
The sample test is difficult; this is the maximum difficulty level to expect on the test.
1. What is decomposition (use a sample to illustrate it)? (5p)
2. Present the operation of sorting in SQL (give a sample)! (8p)
3. Present the database anomalies (with samples)! (10p)
4. What is a typical sitation where we apply flexible table? Present the resulted schema as well. (15p)
5. Following schema is given:
measurement(mid: AutoNumber, sid: integer, value: float, date: date)
sensor(sid: AutoNumber, name: string, type: string, x: float, y: float)
Define the following queries by SQL (or relational algebra) (4x3p):
a. How many measurements were performed by sensor 1?
b. List and sort all sensors in ascending order!
c. What is the maximum measured value of sensor named "Measurement stamp 1"?
d. Filter for maximum daily averaged measurement values for each sensor and present the corresponding dates!
SQL solutions:
a. SELECT COUNT(*) FROM measurement;
b. SELECT name FROM sensor ORDER BY name;
c. SELECT MAX(measurement.value) FROM sensor NATURAL JOIN measurement WHERE sensor.name='Nyúlásmérő bélyeg 1';
d/1. SELECT sid, DAY(date), AVG(value) FROM measurement GROUP BY sid, DAY(date) ORDER BY AVG(value) DESC LIMIT 1;
d/2. SELECT sensor.name, DAY(measurement.date), AVG(measurement.value) FROM measurement NATURAL JOIN sensor GROUP BY sensor.name, DAY(measurement.date) ORDER BY AVG(measurement.value) DESC LIMIT 1;
d/3. SELECT measurement.value,sensor.name FROM (SELECT sensor.sid, AVG(measurement.value) AS v FROM sensor NATURAL JOIN measurement GROUP BY sensor.sid) AS avg INNER JOIN measurement ON avg.v=measurement.value AND avg.sid=measurement.sid ORDER BY measurement.value DESC LIMIT 1;
Relational algebra:
a. πCOUNT(*)(σsid=1(measurement))
b. τname(sensor)
c. πMAX(measurement.value)(σsensor.name='Nyúlásmérő bélyeg 1'(sensor⋈measurement)) vagy γMAX(measurement.value),sensor.name(σsensor.name='Nyúlásmérő bélyeg 1'(sensor⋈measurement))
d. τAVG(value),date(γdate, AVG(value)(measurement))
The sample test is difficult; this is the maximum difficulty level to expect on the test.
1. What is decomposition (use a sample to illustrate it)? (5p)
2. Present the operation of sorting in SQL (give a sample)! (8p)
3. Present the database anomalies (with samples)! (10p)
4. What is a typical sitation where we apply flexible table? Present the resulted schema as well. (15p)
5. Following schema is given:
measurement(mid: AutoNumber, sid: integer, value: float, date: date)
sensor(sid: AutoNumber, name: string, type: string, x: float, y: float)
Define the following queries by SQL (or relational algebra) (4x3p):
a. How many measurements were performed by sensor 1?
b. List and sort all sensors in ascending order!
c. What is the maximum measured value of sensor named "Measurement stamp 1"?
d. Filter for maximum daily averaged measurement values for each sensor and present the corresponding dates!
SQL solutions:
a. SELECT COUNT(*) FROM measurement;
b. SELECT name FROM sensor ORDER BY name;
c. SELECT MAX(measurement.value) FROM sensor NATURAL JOIN measurement WHERE sensor.name='Nyúlásmérő bélyeg 1';
d/1. SELECT sid, DAY(date), AVG(value) FROM measurement GROUP BY sid, DAY(date) ORDER BY AVG(value) DESC LIMIT 1;
d/2. SELECT sensor.name, DAY(measurement.date), AVG(measurement.value) FROM measurement NATURAL JOIN sensor GROUP BY sensor.name, DAY(measurement.date) ORDER BY AVG(measurement.value) DESC LIMIT 1;
d/3. SELECT measurement.value,sensor.name FROM (SELECT sensor.sid, AVG(measurement.value) AS v FROM sensor NATURAL JOIN measurement GROUP BY sensor.sid) AS avg INNER JOIN measurement ON avg.v=measurement.value AND avg.sid=measurement.sid ORDER BY measurement.value DESC LIMIT 1;
Relational algebra:
a. πCOUNT(*)(σsid=1(measurement))
b. τname(sensor)
c. πMAX(measurement.value)(σsensor.name='Nyúlásmérő bélyeg 1'(sensor⋈measurement)) vagy γMAX(measurement.value),sensor.name(σsensor.name='Nyúlásmérő bélyeg 1'(sensor⋈measurement))
d. τAVG(value),date(γdate, AVG(value)(measurement))