Database Systems Flash cards

Revision cards for data base systems theory and SQL

What are the 3 types of database models?

High level - conceptual, user level

Representational level - Logical
Low level - physical, physical data storage

what is relational modelling?

a logical representation of data represented as a table

what 3 aspects of data is relational modelling concerned with?

Data structure
Data integrity - RI, constraints

Data manipulation - Relational Algebra

what is a relation?

An entity/table

what is a tuple?

a Row / Record

What is an Attribute?

A Column

what is a relationship?

A connection between entities

what is a relational Schema?

A textual representation of a data model

what is a Domain?

a set of values an attribute can take

what is a key?

one or more attributes to uniquely identify a tuple

what is a Candidate Key?

minimum set of keys which uniquely identify each Tuple

what is a primary key?

the chosen candidate key used ot uniquely identify each tuple in an entity

what is an Alternate key?

the remaining candidate keys

what is a Foreign key?

key that matches the candidate key in a relation - used to represent the relationships in tables

what is a Composite key?

key consisting of two or more attributes

what is the Entity integrity rule?

Constraint on primary keys, must be unique and not be null

what is the referential integrity rule?

constraint on foreign keys - must match candidate keys or be null

what does intersection do?

gets tuples from both entities, only lists the specific attribute

what does select do?

it gives only a horizontal subset

what does project do?

it gives only a vertical subset

what must two tables have to be union compatible?

they must have the same number of attributes and each pair of corresponding attributes must have the same domain

what does join do?

combination of cartisian product and select

what does Equi Join do?

keep both join columns

what does natural join do?

gets rid of duplicate columns - Tuples not included when join attribute is null or when they dont have matching types in other relation

what does Left outer Join do?

all tuples from R1, even unmatched ones, missing values for R2 corresponding tuples are set to NULL

what are the 3 types of attributes?

single valued - attribute contains a single value for tuple (eg. date of birth, can only have one DOB)

multi-valued - attribute contains multiple values for a tuple (eg. hobby, can have multiple hobbies)

Derived - attribtue derived from another attribute (eg. age from date of birth)

what is Multiplicity?

the type of relationship (eg. one to many, one to one, etc)

participation - min number, cardinality - max number

what is a degree?

number of entities - binary, ternary, etc.

what are the 3 types of abstraction?

classification - is instance of

aggregation - is part of / has part of

specialisation - is a subclass / superclass

what are the two participation types for specialisation?

mandatory participation - each instance of superclass must be a member of subclass (total)

optional participation - instance of superclass doesnt need to be a member of subclass (partial)

what is a ternary relationship?

a relationship with 3 entities

what is a strong entity?

an entity that doesnt rely on another entity’s existence (has a minimum of zero)

what is a weak entity?

depends on the existence of another entity partially or wholly. cannot be uniquely identified by itself

how to we take a conceptual model to a logical model?

• resolve many-to-many relations

• resolve non-binary relations

• resolve hierarchal relationships

• map to relational schema

• add constraints

why dont we have many-to-many relations?

because of the atomicity of attribute values

how do we resolve to or hierarchy?

• add new relation with new pk thats used for all the relations

• flatten the table. add is_undergrad attribute, use Null where needed

what is meant by Fan Trap?

Rearrange the 3 tables

what is meant by Chasm Trap?

add another relation, done if you cant rearrange the relations.

what is Functional Dependency?

attribute depends on one another. A →B, B is FD on A

what is partial dependency?

only exists if PK is composite, full dependency

what does Normalisation do?

it eliminates undesired update anomalies and data redundancies

what is 3NF?

no transitive dependencies.

list the types of constraints.

• CHECK

• DEFAULT

• UNIQUE

• NOT NULL

in SQL, what does LIMIT mean?

it limits the amount of records returned

list the aggregation functions in SQL

• COUNT

• SUM

• AVG

• MAX

• MIN

what does COUNT do in SQL?

can be used to count rows - COUNT(*)

what does GROUP BY do?

can be used to group records by a record.

all other attributes in the query not being used in an aggregation function must be grouped by…

group by also excludes records with 0 count.

what does EXISTS do?

checks if there is at least one row in a subquery

what does the WITH keyword do?

allows the reuse of a query in many places.

what does the UNION keywork do?

same as the union used in relational algebra.

what does the INTERSECT keyword do?

what does the EXCEPT keyword do?

only things from the first table, not the second.

what does the CREATE_VIEW function do?

it creates an immediate view of the virtual tables. it reflects the most recent changes.

it can also be used to restrict certain rows and columns from users.

what are DB integrity constraints?

Entity + ref integrity

what are App Constraints?

based on App logic.

what is a Transaction?

a unit of work that is carried out by a user or program, which accesses or changes the contents of a database.

what is ACID?

• Atomicity - a transaction is either completed or aborted.

• Consistency - if a database is in a consistent state before a transaction it should still be in one after. aka only valid data.

• Isolation - partial effects of incomplete transactions should not be visible to other transactions. aka concurrent transactions do not affect eachother.

• Durability - Once a transaction is committed, it must be permanent.

what is meant by Schedule?

its a sequence of ops by set of concurrent transactions.

what is meant by serial scheduling?

there is no concurrency in transactions.

what are the two types of concurrency control?

• locking

• timestamping

Define Locking

locking is when you need to acquire a lock on a data item to access it. multiple people can have read locks, where as only one can have write locks.

what are the phases for locking?

• Growing phase - you keep getting locks incrementally. you cannot release locks.

• Shrinking phase - all locks release on commit or rollback. Cannot get new locks.

Define Timestamping

Timestamping - each transaction gets a timestamp when it starts, transactions are ordered by these time stamps. DBMS keep a list of these timestamps with the latest being allowed to access the data.

R(x) - timestamp of transaction with latest read on x.

W(x) - timestamp of transaction with latest write on x

Define optimistic methods.

check for serializability violation before commit. Efficient when conflicts are rare. Read validation Write.

define Query processing

transform high level SQL to correct execution strategy in lower level language.

what are the 6 types of failure?

• system crash

• hardware failure

• natural disaster

• Human error

• Transaction failure - deadlock

• Media failure - disk failure

what is primary reliability?

avoiding single point of failure, back up power, UPS

give some examples of secondary reliability.

operating procedures, access control, backups.

what are the three types of log based recovery?

• Normal - After normal shutdown, start from last log record.

• Warm - After system failure (such as an undo or redo) revert to last checkpoint, apply last committed transactions, undo effects of uncommitted transactions.

• Cold - After media failure (unexpected), restore from backup, apply log records.

what are the three types of file organisation?

• Head unordered - records placed on disk in insertion order

• sequential order - recorded placed on a disk in order of value of specified field.

• Hash files - based on hash function.

what are the pros and cons of using head unordered?

-pros_

• quick insertion

• good for bulk loading data

-cons-

• slow retrieval

• slow for selective retrieval, not good for tables with frequent changes.

what are the pros and cons of using sequential ordered?

-pros-

• efficient retrieval (on binary search)

-cons-

• bad insertion and deletion

what are the pros and cons of using hash files?

-pros-

• good for exact matches

-cons-

• bad for range retrieval, pattern matches, and searches on fields other than key.

define an index file.

each record contains search key values and address of the record in the data file containing search key value. this makes finding a row quicker. use indexes for columns often in the where clause.

what is meant by the operator NEW

store copies of values of new row

what is meant by the operator OLD

store copy of old row before update / deletion