Database Concepts Final Exam
36 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What type of calculator is permitted in the exam venue?

  • Scientific calculator
  • Graphing calculator
  • Programmable calculator
  • Non-programmable calculator (correct)

    • When can students visit the Student Centre for calculator approval?

  • Mondays, Wednesdays, and Fridays from 9 am to 12 pm (correct)
  • Only during the final exam week
  • Any weekday between 8 am and 3 pm
  • Tuesdays and Thursdays from 10 am to 1 pm

    • Which topics are included in the exam?

  • Normalization and Query Processing (correct)
  • Database Security and Transactions
  • Relational Algebra and Sports Management
  • Integrity Constraints and Environmental Science

    • What is the minimum overall mark a student must achieve to pass the unit?

    <p>50%</p> Signup and view all the answers

    What must a student achieve in the final exam to be considered passing?

    <p>At least 40% in the exam</p> Signup and view all the answers

    What will happen to the records in the Film table after executing the DELETE query on filmID 101?

    <p>The record for filmID 101 will be removed.</p> Signup and view all the answers

    How many records will be left in the FilmActor table after the deletion of filmID 101?

    <p>Two records will remain.</p> Signup and view all the answers

    What will the nationality of the actors be in the Actor table after the deletion of filmID 101?

    <p>All nationalities will remain unchanged.</p> Signup and view all the answers

    Which actor's record will still be present in the Actor table after the delete operation?

    <p>Lily</p> Signup and view all the answers

    What is the film title associated with filmID 102 in the Film table?

    <p>Titanic</p> Signup and view all the answers

    After executing the DELETE query, which filmID will still have entries in both the Film and FilmActor tables?

    <p>Both 102 and 205</p> Signup and view all the answers

    What will be the data type of the releaseYear column in the Film table?

    <p>Integer</p> Signup and view all the answers

    What would be the result if an attempt is made to delete a non-existing filmID like 999?

    <p>No changes will occur.</p> Signup and view all the answers

    Which of the following execution schedules matches the steps in Figure 1?

    <p>r1(B),r2(A),r2(B),w2(C),w2(D),r1(A),r1(B),r1(A),r1(C),r2(A),r2(C)</p> Signup and view all the answers

    Which of the following functional dependencies is valid according to the data in the relation provided?

    <p>AB -&gt; C</p> Signup and view all the answers

    Is (AG) a superkey based on the results of its closure?

    <p>Yes, because it includes all attributes.</p> Signup and view all the answers

    Is (AG) a candidate key, and why?

    <p>Yes, because it is minimal.</p> Signup and view all the answers

    Which of the following is NOT true regarding the specified functional dependencies?

    <p>CG → H does not influence other dependencies.</p> Signup and view all the answers

    Which entry signifies that attribute B uniquely determines attribute C?

    <p>3, z, b</p> Signup and view all the answers

    According to the provided data, which functional dependency can be reliably established?

    <p>A -&gt; B</p> Signup and view all the answers

    What SQL command is used to find the names of students enrolled in both courses ‘COMP5138’ and ‘COMP5318’?

    <p>SELECT name FROM Student WHERE sid IN (SELECT sid FROM Enrolled WHERE uos_code='COMP5138' INTERSECT SELECT sid FROM Enrolled WHERE uos_code='COMP5318')</p> Signup and view all the answers

    In which scenario would you use the π (pi) operator in relational algebra?

    <p>To select specific columns from a relation</p> Signup and view all the answers

    What is the purpose of the σ (sigma) operator in relational algebra?

    <p>To filter rows based on specific criteria</p> Signup and view all the answers

    Which SQL approach retrieves the name of students enrolled in the unit of study with the highest credit points?

    <p>SELECT name FROM Student NATURAL JOIN Enrolled WHERE points = (SELECT MAX(points) FROM UnitOfStudy)</p> Signup and view all the answers

    Which SQL command returns the id of all students who are enrolled in the unit ‘COMP5138’?

    <p>SELECT sid FROM Enrolled WHERE uos_code='COMP5138'</p> Signup and view all the answers

    What does the following SQL query do: 'SELECT sid FROM Enrolled WHERE uos_code='COMP5138' INTERSECT SELECT sid FROM Enrolled WHERE uos_code='COMP5318'?'

    <p>Finds students enrolled in both courses</p> Signup and view all the answers

    What is the result of counting the students from countries with equal or more students than Australia?

    <p>It displays a list of countries sorted by name</p> Signup and view all the answers

    In the relational schema, what does the 'Supervises' relation represent?

    <p>The relationship between bosses and their employees</p> Signup and view all the answers

    What condition must hold true for a relation to be in Third Normal Form (3NF)?

    <p>For every functional dependency, either it is trivial, or the left side is a superkey, or the right side is a subset of a candidate key.</p> Signup and view all the answers

    Why does the functional dependency C → D comply with 2NF but violate 3NF?

    <p>C is not a superkey and D is not part of some key for R.</p> Signup and view all the answers

    What is the result if a relation is not in BCNF due to the functional dependency D → B?

    <p>Decomposition into two relations is necessary.</p> Signup and view all the answers

    Which of the following correctly identifies the keys of the relation R with attributes ABCDE?

    <p>A, E, and BC.</p> Signup and view all the answers

    In the context of relations, which statement is true regarding the decomposition of the relation contracts?

    <p>The intersection of R1 and R2 must include a key of R1.</p> Signup and view all the answers

    Which functional dependency in the provided relations suggests a violation of BCNF?

    <p>supplierID, deptID → itemID</p> Signup and view all the answers

    How do you determine whether a relation is in BCNF?

    <p>Verify that for every non-trivial FD, the left side is a superkey.</p> Signup and view all the answers

    What is an implication of a lossless join decomposition for a relation that violates BCNF?

    <p>It guarantees no information loss after decomposition.</p> Signup and view all the answers

    Study Notes

    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.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    COMP9120 Review Session 2024 PDF

    Description

    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.

    More Like This

    Database Management System Quiz
    14 questions

    Database Management System Quiz

    LightHeartedCrocus avatar
    LightHeartedCrocus
    SQL Join and Relational Algebra Quiz
    22 questions

    Relational Algebra Quiz: Outer Join Function and More

    SupremeComposite avatar
    SupremeComposite
    Relational Algebra in Database Management
    12 questions

    Relational Algebra in Database Management

    ChampionEuphonium avatar
    ChampionEuphonium
    Relational Algebra Flashcards
    10 questions

    Relational Algebra Flashcards

    CompliantMemphis avatar
    CompliantMemphis
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser