Database Concepts Final Exam

Exam Information

• Students need to bring a 16-page answer book and a non-programmable calculator
• Approvable calculators include a list of models available on Canvas
• The Student Centre approves calculators and linguistic dictionaries on Mondays, Wednesdays, and Fridays from 9 am to 12 pm
• The final exam will be available on the Canvas page
• The exam covers 50% of the final mark
• Students need to achieve at least 40% in the final exam and an overall mark of at least 50% to pass the unit

Question Types

• 3 MCQ questions worth 6 marks
• 9 Essay-type questions worth 44 marks
• Topics include:
  • Entity-Relationship Diagram (ERD)
  • Relational Model
  • Relational Algebra
  • SQL
  • Integrity Constraints
  • Transactions
  • Normalization
  • Storage
  • Indexing
  • Query Processing

Relational Algebra

• Example question: Find the names and salaries of all bosses who have an employee earning more than 1000
• Sample solution: 𝜋𝑛𝑎𝑚𝑒,𝑠𝑎𝑙𝑎𝑟𝑦 (𝜎𝑏𝑜𝑠𝑠𝐼𝐷=𝑒𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝐼𝐷 (𝜋𝑏𝑜𝑠𝑠𝐼𝐷 𝜎𝑠𝑎𝑙𝑎𝑟𝑦>1000 𝐸𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝑠 ⋈ 𝑆𝑢𝑝𝑒𝑟𝑣𝑖𝑠𝑒𝑠 × 𝐸𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝑠)

SQL

• Example question: Find the id of all students who are enrolled in both ‘COMP5138’ and ‘COMP5318’.

• Sample solution:

  SELECT sid 
  FROM Enrolled 
  WHERE uos_code=‘COMP5138’
  INTERSECT
  SELECT sid 
  FROM Enrolled 
  WHERE uos_code=‘COMP5318’
  

• Example question: Find the names of students who are enrolled in both ‘COMP5138’ and ‘COMP5318’

• Sample solution:

  SELECT name
  FROM Student
  WHERE sid IN ( SELECT sid 
                  FROM Enrolled 
                  WHERE uos_code=‘COMP5138’
                  INTERSECT 
                  SELECT sid 
                  FROM Enrolled 
                  WHERE uos_code=‘COMP5318’ 
                )
  

• Example question: Find the name of the students who are enrolled in the unit of study that has the highest credit points

• Sample solution (Approach 1):

  SELECT name
  FROM Student
  WHERE sid IN ( SELECT sid
                  FROM Enrolled
                  WHERE uos_code IN ( SELECT uos_code
                                      FROM UnitOfStudy
                                      WHERE points = ( SELECT max(points) FROM UnitOfStudy )
                                    )
                )
  

• Sample solution (Approach 2):

  SELECT name
  FROM Student NATURAL JOIN Enrolled NATURAL JOIN UnitOfStudy 
  WHERE points = ( SELECT max(points) FROM UnitOfStudy)
  

DDL (Data Definition Language)

• Example of DELETE statement:

  DELETE FROM Film 
  WHERE filmID = 101;
  

• Example of database schema: sql Film FilmActor filmID title releaseYear filmID actorID 102 Titanic 1998 102 965 205 Baby Day out 2000 205 901

Transactions

• Example of transaction execution schedule
  • 1. r1(B),r2(A),r2(B),w2(C),w2(D),r1(A),r1(B),r1(A),r1(C),r2(A),r2(C)
  • 2. r1(B),r2(A),r2(B),r2(C),w2(C),r2(D),w2(D),r1(A),r1(B),r1(A),r1(C),r2(A),r2(C)

Functional Dependencies

• Example relation with attributes A, B, C, D:

  A       B         C     D
  1       x        a     10
  2       y        b     20
  3       z        b     20
  1       z        c     30
  2       y        b     20
  

• Identify valid functional dependencies for the given relation:
  • AB -> C
  • C -> D
  • A -> B
  • ABC -> D
  • B -> C
  • D -> C

Normalisation

• Relation R has attributes (A, B, C, G, H, I) with functional dependencies:
  • A → B
  • A → C
  • CG → H
  • CG → I
  • B → H
• Attribute closure (AG)+ :
  • AG = ABCGHI (all attributes in R)
• Is (AG) a superkey? True.
• Is (AG) minimal? True
  • (A)+ = ABCH ≠ R
  • (G)+ = G ≠ R
• For a relation to be in 3NF, for each dependency X→ Y in F+, at least one of the following must hold:
  • X → Y is a trivial FD (Y ⊆ X)
  • X is a superkey for R
  • Y ⊂ (is a proper subset of) a candidate key for R

BCNF

• For a relation to be in BCNF, the left-hand side of any functional dependency must be a superkey.

• Example relation R with attributes (A, B, C, D, E) and FDs:

  • A → BC
  • BC → E
  • E → DA

• R is in BCNF because (A, E, BC) are keys.

• Example relation S with attributes (A, B, C, D, E) and FDs:

  • A → CE
  • D → B
  • E → DA

• S is not in BCNF because D → B violates the condition.

• Decompose S into:

  • S1 = (D, B)
  • S2 = (A, C, D, E)

• This is a lossless join decomposition because the intersection of S1 and S2 is a key to S1.

• Example relation ‘contracts’ with attributes: (contractID, supplierID, projectID, deptID, itemID, quantity, value) and FDs:

  • contractID → supplierID, projectID, deptID, itemID, quantity, value
  • supplierID, deptID → itemID
  • projectID → supplierID

• This relation is not in BCNF because supplierID, deptID → itemID and projectID → supplierID violate the condition.

• Decompose ‘contracts’ into:

  • R1 = (supplierID, deptID, itemID)
  • R2 = (supplierID, deptID, contractID, projectID, quantity, value)

• R1 is in BCNF, but R2 is not.

Prepare for your final exam covering key database topics such as Entity-Relationship Diagrams, SQL, and normalization. This format includes multiple-choice and essay questions that will test your understanding of relational models and query processing. Ensure you're equipped with an approved calculator and ready to demonstrate your knowledge.