|What is a relation also known as?
|What is an attribute also known as?
|What is a tuple also known as?
|What is the domain for an attribute?
|The set from which all possible values for that column can come.
|Define the degree of a relation:
|How long each tuple is, or how many columns the table has.
|Define the cardinality of a relation:
|How many different tuples there are, or how many rows a table has.
|What does a schema do?
|It is the heading of the table. It defines the relations attributes.
|A set of attributes in a relation is a candidate key if and only if:
|Every tuple has a unique value for that set of attributes, and no proper subset of the set has the uniqueness property.
|What is a foreign key?
|They are used to link the data in two relations. A set of attributes in the first relation is a Foreign Key if it's value matches a candidate key in a second relation.
|What is referential integrity?
|When every value of an attribute in one table, is present as an attribute in a second table.
|Define "Closure" in terms of relational algebra:
|When you ask a query over a relation and you get back a relation.
|Define "Composition" in terms of relational algebra:
|When you ask a query over a relation and use the result as input for another query.
|What does "Filter" mean in terms of relational algebra?
|What does "Slice" mean in terms of relational algebra?
|What does "Combine" mean in terms of relational algebra?
|Combine rows or columns to form one relation.
|What does the select operator do?
|It picks out certain rows (filtering) and is denoted with a ?.
|What does the project operator do?
|It picks out certain attributes (slicing) and is denoted with a ?.
|What happens to duplicates in relational algebra compared with duplicates in SQL?
|They are eliminated. However in SQL this is not the case.
|What does the Cartesian Product operator do?
|It produces a scheme of which is the result of the union of the schema of two relations. The results are all combination of tuples from the two relations. It is denoted with a x.
|What does the natural join operator do?
|This is the same as the Cartesian product but it enforces equality on all attributes. It is denoted with a ?.
|What does the Theta symbol do in a join?
|It allows you to specify a condition that the resulting tuples must satisfy.
|What does the difference operator do?
|It removes all values that exist in both tables from the first table. It is denoted with a -.
|What does the intersection operator do?
|It produces a relation containing only the values that exist in both of the two tables.
|What is an entity in an E/R diagram?
|A object or item of interest.
|What is an attribute in an E/R diagram?
|Properties of an entity.
|What is a relationship in a E/R diagram?
|The links between various entities.
|Why are E/R diagrams useful?
|They give a conceptual view of the database. They are independent of the choice of DBMS. They can identify some issues in the design.
|What does a DBMS do?
|Allows the user to access data using SQL. Allow connections from other programming languages. Provide additional functionality like concurrency.
|What is redundant data?
|Data that exists in addition to the actual data.
|What can redundant data lead to?
|INSERT anomalies. UPDATE anomalies. DELETE anomalies.
|Where is data redundancy expressed and how can it be reduced?
|In functional dependencies. Normalisation aims to reduce redundant data by removing specific types of functional dependency.
|What is atomic data?
|Entries should be single values and not contain sets or composite objects.
|What is required for a relation to be in 1NF?
|All data values are atomic.
|What is a functional dependency?
|A set of attributes A, functionally determines another set B, if whenever two rows of the relation have the same values for all the attributes in A ten they also have the same values for all the attributes in B.
|What is a partial functional dependency?
|If some attribute of the set A can be removed and the functional dependency still holds.
|What is required for a relation to be in 2NF?
|It is already in 1NF and no non-key attribute is partially dependent on a candidate key.
|What is a transitive functional dependency?
|A -> C is a transitive FD, if there is some set B such that A -> B and B -> C
|What is lossless decomposition?
|If we can recover the original relation through a join. It ensures that we haven't removed any data from our database.
|What is a Transaction?
|An action, or series of actions carried out on a database that reads or updates it's contents.
|What does ACID stand for?
|Atomicity, Consistency, Isolation, Durability.
|What does Atomicity mean in terms of ACID?
|A transaction is either carried out fully or it is not at all.
|What does Consistency mean in terms of ACID?
|A transaction take the database from a valid state to another valid state.
|What does Isolation mean in terms of ACID?
|The effects if a particular transaction are invisible to other transactions until it is complete.
|What does Durability mean in terms of ACID?
|Once a transaction is complete, it's changes are permanent.
|What enforces the ACID properties?
|A transaction subsystem.
|What does the transaction log store?
|Details of all transactions. What changes were made. How to undo these changes. When transactions were complete and how.
|Where is the transaction log stored?
|On the disk, not memory. This is so it is preserved if the system crashes.
|What is the write ahead log rule?
|The entry in the log must be made before COMMIT processing can be complete.
|What is a checkpoint?
|All transactions are written to the disk. A record is made on the disk of all transactions that are currently running.
|What happens to transactions that were still running when the system crashed?
|They need to be undone and restarted.
|What happens to transactions that were completed after the previous checkpoint but before the system crash?
|They need to be redone.
|What is ROLLBACK?
|Working backwards through the log, we undo every operation by any transaction on the UNDO list. Returning the database to a consistent state.
|What is ROLLFORWARD?
|Some transactions need to be redone. Working forwards through the log we redo any operation by a transaction on the REDO list. This brings the database up to date.
|What is a backup?
|The transaction log and entire database is written to secondary storage.
|What does COMMIT mean?
|It signals the successful end of a transaction. Changes are made permanent and visible to other transactions.
|What does ROLLBACK mean?
|Signals the unsuccessful end of a transaction. Changes are undone.
|What is concurrency control?
|The process of managing simultaneous operations on the database without having them interfere with each other.
|What is a schedule?
|A sequence of the operations in a set of concurrent transactions that preserves the order of operations in each of the individual transactions.
|What is a serial schedule?
|The operations of each transaction are executed consecutively without any interleaves operations from other transactions.
|When does the DBMS verify passwords and check permissions?
|When a user tries to: retrieve data, modify data, or modify the database structure.
|What are privileges?
|They are used to control access to database tables and other database objects.
|What is an abandoned privilege?
|Privileges that are left with a user who was granted privileges from a user who has been dropped from the database/had their privileges revoked.
|What is a RAID array?
|A Redundant Array of Inexpensive Disks.
|What does a RAID array do?
|It stores information across more than one physical disk. It can access more than one disk. Disk failure doesn't always mean data is lost.
|What is RAID level 1?
|Files are duplicated over all disks (Mirroring). Provides improved read performance but no increase in write performance. Large amounts of redundancy.
|What is RAID level 3?
|Data is striped over all but one disk, with a parity disk for redundancy. Data is split into bytes and written to separate disks. Extremely fast and allows for 1 disk failure.
|What is RAID level 5?
|Data is striped over disks with a distributed parity. Data is split into blocks, parity blocks are distributed throughout all disks. Will allow continued use even after 1 disk failure.
|What are some considerations with RAID systems?
|Cost of disks. Do you need speed or redundancy? How reliable are individual disks?
|What is an index?
|A data structure that resides alongside table, providing faster access to the rows. It is associated with one or more fields, improving searches involving those fields.
|What is a primary index?
|A sequential file ordered by a unique key.
|What is a clustered index?
|A sequential filed ordered by some fields that may not be unique.
|What is a secondary index?
|This exists separately to the data ordering.
|What is database security?
|Makes sure that the user is authorised to access information.
|What is database integrity?
|Ensures that authorised users only input consistent data into the database. Usually consists of a series of constraints and assertions on data.
|What is a distributed database?
|Several sites connected by a network that can each hold and process data. The system is still seen as a single entity however.
|What are the advantages of a distributed database?
|Distributed processing. Aim for locality of data. Improves local control. May keep local copies. Improves reliability. May be cheaper than one large database.
|What are the disadvantages of a distributed database?
|Cost? Complexity. Security issues. Integrity control is complex. Lack of current standards.
|What is an Object Orientated database?
|A collection of persistent objects. It manages this collection, allowing the objects to be made persistent. Permits queries to be made of the objects. Does all the normal DBMS things as well.
|What is a persistent object?
|Objects that exist independently of any program.
|What are the advantages of an Object Orientated Databse?
|Good integration with Java, C++. Has the advantages of the object paradigm. Better modelling of real world objects. Easier addition of types and schema changes. Can store complex information. Fast to get whole objects.
|What are the disadvantages of an Object Orientated Database?
|There is no underlying data model unlike in the relational model.