1) Given relation R(A,B,C,D,E) with functional dependencies {AB→C, C→D, D→E}.
a. Show how to determine the candidate key
b. What is the reason that R is not BCNF?
c. Show ALL steps needed to decompose R into BCNF relations, if possible.
1) Many of the No-SQL systems that support large-scale parallel execution guarantee “eventual consistency” rather than the ACID properties. What does this mean? Explain briefly how this can produce different results compared to a parallel SQL system that guarantees the ACID properties.
2) Consider these schedules involving 3 transactions: S1: r1(x); r2(y); r1(z); r3(z); r2(x); r1(y)
S2: r1(x); w2(y); r1(z); r3(z); w2(x); r1(y)
S3: r1(x); w2(y); r1(z); r3(z); w1(x); r2(y)
S4: r1(x); r2(y); r1(z); r3(z); w1(x); w2(y)
For each schedule, draw the precedence graph and decide if the schedule is conflict-serializable.
Given three simple tables with FDs A → B, B → C, C → D 1) Create table t1 ( a integer, b integer );
Create table t2 ( b integer, c integer ); Create table t3 ( c integer, d integer );
data is provided in 3 CSV files for importing, or 3 SQL “insert” files
a. create the tables as defined above and import the data (10K rows) using
LOAD DATA (or equivalent). SQL insert statements are provided as an alternative. Consider these three different but equivalent queries.
— 1
select a from t1
where mod(a,5) = 0 and b in ( select b from t2
where c in ( SELECT c FROM t3 where mod(d,5) = 0 ));
— 2
select t1.a from t1, t2, t3 where
t1.b = t2.b and t2.c = t3.c and mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
— 3
select t1.a from (t1 join t2 on t1.b = t2.b ) join t3 on t2.c = t3.c where mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
a. Which one of the above select statements has the best execution
performance? Explain your decision, supported by facts, not just opinion.
1) Consider an internet voting system where a voter must enter a PIN which is checked to ensure it is valid and not already used. Two tables are used: PIN (number, if_voted) and VOTES (candidate, count). Consider a new read-only transaction that outputs the entire vote tally table. Discuss the issues involved in selecting the appropriate isolation level to ensure correctly reporting the voting results.
