CO532 Database Systems

Card Set Information

CO532 Database Systems
2013-05-05 15:39:37
Database Systems

Revision cards for CO532 Database Systems
Show Answers:

  1. What is a logical data model?
    This describes the structure of the database. It consists of mainly tables and columns of data.
  2. What is a physical data model?
    This describes details of physical data storage; formats, access paths, ordering
  3. What is a conceptual data model?
    A conceptual model can describe the semantics (meaning) of a system. It consists of entity classes and relationships between entity classes.
  4. What is a database?
    A database is a collection of related data
  5. What is a database management system (DBMS)?
    A DBMS is the software that allows the database to function.
  6. What are the advantages of using a database?
    • Controlled redundancy
    • Data consistency
    • Sharing of data
    • Improved security
  7. What are the disadvantages of using a database?
    • Complexity
    • Size (of software and application)
    • Cost
    • Performance
    • Risk of failure
  8. What is a DDL?
    • Data Definition Language
    • Structures and defines data in a database.
  9. What is a DML?
    • Data Manipulation Language
    • Allows insertion, updating, deleting and querying of data in a database.
  10. Give the three Character data types in the SQL language.
    • CHAR        - fixed length
    • VARCHAR  - variable length limited to max
    • TEXT        - variable length with no limit
  11. Give the four Numeric data types in the SQL language.
    • INTEGER   - Integer
    • FLOAT       - Floating Point Number
    • DOUBLE    - Floating Point Number
    • NUMERIC   - Number (precision, decimal)
    • OID          - Object Identifier
  12. Give the four Temporal data types in the SQL language.
    • DATE           - Date
    • TIME           - Time
    • INTERVAL    - Interval of time 
    • TIMESTAMP  - Date and Time
  13. Give the one Logical data type in the SQL language.
    BOOLEAN - True or False
  14. Which is an example of a relational schema for a database table with two attributes?

    a) Customers (INTEGER cid, VARCHAR(15) name)
    b) Customers (cid, name)
    c) Customers
    b) Customers (cid, name)
  15. Using the aggregate function COUNT, how would we find out how many customers are in the Customers table that come from Margate given this schema?

    customers (id, name, age, hometown) 
    SELECT COUNT * FROM customers WHERE hometown = 'Margate';
  16. What is aliasing in terms of Database Systems?
    Using prefixed constants to differentiate between tables that may or may not have the same attribute names.
  17. In SQL, what does the set operation UNION do?

    SELECT name, city FROM customers
    SELECT name, city FROM products;
    • Acts as OR
    • Returns all names and cities from both 'customers' and 'products' table.
  18. In SQL, what does the set operation INTERSECT do?

    SELECT name, city FROM customers
    SELECT name, city FROM customers
    • Acts as AND
    • Returns the names and cities that belong to both 'customers' and 'products' table.
  19. In SQL, what does the set operation EXCEPT do?

    SELECT name, city FROM customers
    SELECT name, city FROM customers

    • Acts as NOT
    • Returns the names and cities that are present in 'customers' table but excludes the ones that are also present in the 'products' table.
  20. Set operations can be used when...
    The two tables have the same attributes (Columns) and domains (Data Types).
  21. Integrity Constraints: How can we ensure an attribute is mandatory?
  22. Integrity Constraints: How can we constrain the data that is allowed to fill an attribute?

    • Examples:
    • discount   REAL CHECK (discount <= 15.00)
    • college CHAR (1) CHECK (college IN(’D’,’E’,’K’,’R’))
  23. Integrity Constraints: How can we set a default value for an attribute?

    • Example:
    • discount REAL DEFAULT 0.0
  24. Integrity Constraints: How can we ensure that a non-primary-key field is unique?

    • Example:
    • chassis_no VARCHAR(32) NOT NULL UNIQUE
  25. Referential integrity: When we make changes to a primary key being used in another table as a foreign key, what action can we choose to stop the system breaking?
    • CASCADE        - Update records
    • SET NULL       - Set Null
    • SET DEFAULT  - Set default value
    • NO ACTION    - Leave unchanged
  26. What is a view?
    • A view is a virtual table.
    • A logical window drawn from the base tables.
  27. How do we create a view?
    • CREATE VIEW <view name> AS
    • <SQL query>
  28. List the stages in database design.
    • Problem investigation
    • Data modelling
    • Database design
    • Database implementation
    • Database monitoring/tuning
  29. Define Cardinality.
    Cardinality specifies the minimum and maximum number of times that an instance of an entity can participate in a relationship
  30. Define an atomic value
    A single value that derives its type from the domain of the attribute.
  31. Define a composite key
    A key consisting of more than one attribute.
  32. Define a candidate key
    An attribute that satisfies both of the following:

    • Uniqueness
    • Irreducibility (or minimality)
  33. Define an alternate key
    If there is more than one candidate keys and one of these has been chosen as the primary key, the rest are referred to as alternate keys.
  34. why should we normalise?
    • Eliminates redundancy
    • Removes update anomalies
    • Optimal use of storage space
  35. Define First Normal Form (1NF)
    A database is in first normal form if all underlying domains contain only atomic values
  36. Define Second Normal Form (2NF)
    A database is in second normal form if it is in 1st Normal form and every non-key attribute is fully functionally dependant on the primary key.
  37. Define Third normal form (3NF)
    A database is in third normal form if it is already in second normal form and all non-key attributes are non-transitively dependent on the primary key.
  38. In terms of Relational Algebra, where does 'Projection', 'Cartesian Product' and 'Restriction' occur in the following statement?

    SELECT p.patient_id, d.doctor_id FROM patient p, doctor d WHERE p.patient_id = 001
    • Projection = SELECT p.patient_id, d.doctor_id
    • Cart. Product = FROM patient p, doctor d
    • Restriction = WHERE p.patient_id = 001
  39. In database systems, what is a transaction?
    An action, or series of actions, carried out by a user or application which accesses or changes the contents of a database
  40. The database is required to be in a ..?.. state before and after a transaction process.

    A) volatile
    B) consistent
    C) changing
    D) different
    B) consistent
    (this multiple choice question has been scrambled)
  41. Which commands, submit a transaction and discard a transaction?
    • COMMIT - Submit
    • ROLLBACK - Discard
  42. In transactions for database systems, what is a Dirty Read?
    When the intermediate state of an aborted transaction is visible to and used by another transaction
  43. In transactions for database systems, what is a Lost Update or Dirty Write?
    When two transactions update the same data, one update is lost.
  44. In transactions for database systems, what is Inconsistent Analysis?
    A value is updated by one transaction whilst another is performing an aggregate function
  45. What is a serializable transaction?
    A transaction that is executed sequentially alongside other transactions/requests and not in parallel.

    This is because Serializable transactions do not conflict
  46. Name and define the four desirable ACID properties of transactions.
    • Atomic: The set of actions that from a transaction is indivisible; transaction is either completed or aborted.
    • Consistent: The database must be in a consistent state before and after the transaction
    • Isolated: Transactions must be independant of each other (serializable). 
    • Durable: The effects of a committed transaction must be permanent.
  47. In terms of concurrency control methods, describe how two-phase locking works.
    • Growing phase: Locks are accumulated incrementally during a transaction by different parties.
    • Shrinking phase: All locks released on commit/rollb
  48. Concurrency control methods:
    Explain how Locking works.
    Data items are locked during access to deny access to other transactions

    • Read lock - shared lock; both parties can read but none can write
    • Write lock - exclusive lock; only one party can write
  49. Concurrency control methods:
    Explain how Timestamping works.
    • Transactions ordered by time.
    • If a conflict occurs, latest is aborted.
  50. How is PostgreSQL different to other database query languages?
    • Inheritance
    • Multi-Version (Pre-transaction snapshots available for reading)
  51. What are the problems with two-phase locking?
    • Deadlock: can be caused by two or more transactions each waiting for another to release its lock on a granule.
    • Livelock: Transactions wait indefinitely for a lock despite the sate of the transactions changing.
  52. How can you ensure that a database system is reliable in the face of system failure?
    • Primary Reliability:
    • Avoid single point of failure (Hardware replication, backup power sources)
    • Secure, air-conditioned machine rooms
    • Carefully designed operating procedures
    • Secondary Reliability:
    • Rigorously implemented operating procedures
    • Access control
    • Reliable (tested!) backup and recovery mechanisms