Physics & Computer Science
Version: 2019-10-06 07:10

CP363: Test 1 Sample Questions

General Comments

This document contains a list of sample questions for Test 1. Some of these questions, or questions very much like them, will appear on Test 1.


Name and Describe

For these questions you must name and/or describe the concepts discussed in lecture or in the text. You may write in point form. You need not memorize the descriptions given - your own words are preferable - but you must proper terminology. When asked for multiple names or descriptions provide only as many as requested. Extra items are either ignored, if correct, or deducted from your mark for the question, if incorrect. Generally one mark is given for each concept name (if requested) and the rest of the marks for the description.


  1. [7 marks] Name seven features common to most databases.


  2. [4 marks] Name four common database applications.


  3. [6 marks] Name and describe three problems with uncontrolled redundancy.


  4. [4 marks] Name and describe the two types of data independence.


  5. [4 marks] For the two types of data independence describe the kind of problem that can occur if a DBMS fails to provide that independence.


  6. [3 marks] Describe controlled redundancy and give an example of when it would be useful.


  7. [3 marks] Describe meta-data and how it supports data independence.


  8. [8 marks] Name and describe the four integrity constraints on a database.


  9. [9 marks] Name and describe each of the parts of the three-schema architecture.


  10. [4 marks] Describe how each of the parts of the three-schema architecture relate to each other.


  11. [4 marks] Describe the advantages and disadvantages of the three-schema architecture.


  12. [6 marks] Name six of the attributes of a database management system (DBMS).


  13. [12 marks] Name and describe the four software design concerns. Give both the formal and descriptive name for each concern.


  14. [3 marks] Describe null values and give an example of when they would be useful.


  15. [3 marks] Describe atomic values and give an example of one.


  16. [3 marks] Describe the term domain and give an example of one.


  17. [3 marks] Describe the term range and give an example of how one could be used.


  18. [3 marks] Describe the term entity integrity constaint and give an example of where it would apply.


  19. [3 marks] Describe the term foreign key and give an example of where it would apply.


  20. [3 marks] Describe what the acronym DDL stands for and what it means.


  21. [3 marks] Describe what the acronym DML stands for and what it means.


  22. [3 marks] Describe the term use case and give an example of one.


  23. [3 marks] Describe how a cascade works when deleting a tuple and give an example of one.


  24. [2 marks] Describe reference as it applies to relational algebra and give a simple SQL example.


  25. [2 marks] Describe selection as it applies to relational algebra and give a simple SQL example.


  26. [2 marks] Describe projection as it applies to relational algebra and give a simple SQL example.


  27. [2 marks] Describe renaming as it applies to relational algebra and give a simple SQL example.


  28. [2 marks] Describe cross product as it applies to relational algebra and give a simple SQL example.


  29. [2 marks] Describe set union as it applies to relational algebra and give a simple SQL example. All the tuples in both result sets are included in the final result set.


  30. [2 marks] Describe set intersection as it applies to relational algebra and give a simple SQL example.


  31. [2 marks] Describe set difference as it applies to relational algebra and give a simple SQL example.


  32. [4 marks] Name and describe in point form four of the additional relational operators.


  33. [2 marks] Describe the difference between an inner join and an outer join.


  34. [3 marks] Describe the differences between left, right and full outer joins.


  35. [3 marks] Name and briefly describe the three types of join anomalies.


  36. [2 marks] Describe what a spurious tuple is.


  37. [2 marks] Describe what functional dependency is.


  38. [5 marks] Name and describe five of the functional dependency rules.


  39. [3 marks] Describe what a minimal set of dependencies is.


  40. [2 marks] Describe full functional dependency.


  41. [2 marks] Describe what must be true about two selection statements before a union can be applied to them.


  42. [2 marks] Describe the relationship between a superkey and a primary key.



Other

The sample data or diagrams given on the test may differ from the versions given here, but the concepts covered are the same.

  1. The join questions are based upon the following simple tables:

    Table X
    A B
    a 1
    b 2
    Table Y
    B C
    1 f
    1 g
    3 h

    A and B are attribute names. Show all attributes in the result sets.

    1. [3 marks] Show the result of a natural join between tables X and Y.


    2. [3 marks] Show the result of the outer join SELECT * FROM X LEFT OUTER JOIN Y ON X.B = Y.B between tables X and Y.


    3. [3 marks] Show the result of the outer join SELECT * FROM X RIGHT OUTER JOIN Y ON X.B = Y.B between tables X and Y.


    4. [3 marks] Show the result of the outer join SELECT * FROM X FULL OUTER JOIN Y ON X.B = Y.B between tables X and Y.


    5. [3 marks] Show the result of a cross join between tables X and Y.


  2. The relational algebra questions are based upon the following simple tables:

    Table Q
    D E
    a 1
    b 2
    g 1
    Table R
    D E
    f 1
    b 2
    h 3

    D and E are attribute names. Show all tuples in the result sets.

    1. [2 marks] Show the result of a union between tables Q and R.


    2. [2 marks] Show the result of an intersection between tables Q and R.


    3. [2 marks] Show the result of a set difference between tables Q and R.


  3. [5 marks] Given the following table, list five of its possible functional dependencies and describe the semantics each represents.

    «table»
    Register
    Student_ID
    Course
    Dept
    Last_Name
    First_Name
    Instructor
    Grade

  4. [5 marks] Given the following table, describe one possible minimal set of dependencies for it and give the semantics this set represents.

    «table»
    Register
    Student_ID
    Course
    Dept
    Last_Name
    First_Name
    Instructor
    Grade

  5. [5 marks] Given the following table, indicate whether the following functional dependencies are true or false. (Circle the correct answer.) If false, briefly explain why.

    Fruit Colour Season Ready
    Apple Red Fall true
    Apple Green Summer true
    Orange Orange Winter true
    Grape Red Fall true
    Peach Orange Summer true
    Peach Green Summer false

    1. ( true / false ) Fruit → Colour
    2. ( true / false ) { Fruit, Colour } → Season
    3. ( true / false ) { Fruit, Season } → Ready
    4. ( true / false ) Ready → Season
    5. ( true / false ) { Fruit, Ready } → Colour
    6. ( true / false ) { Fruit, Ready, Season } → Colour
    7. ( true / false ) Colour → { Fruit, Ready, Season }