Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/11

Click to flip

11 Cards in this Set

  • Front
  • Back

What are the major components of the relational model according to Date?

1. Structural: data as tables


2. Integrity: tables satisfy integrity constraints


3. Manipulative: relational algebra operators used

What is entity integrity? (2)

- Each table must have a key that uniquely identifies each row of the table.


- No attribute in the primary key may have null values.

What is the referential integrity?

What is referential integrity?

Foreign key values have matching candidate key values: every value of an attribute of a relation exists as a value of another attribute in a different (or the same) relation.




Answer: supplier and supply values do not match: S1 in SUPPLIER and S4 in SUPPLY.

How do relational algebra and calculus differ?

Calculus is declarative (no control flow), algebra is procedural.

What are the 4 ways of writing applications that use SQL?

1. Direct invocation


2. Embedded


3. Module language - like embedded but a single SQL statement


4. Call level interface - set of routines that are immediately compiled, optimised and executed.

How to start an embedded block of SQL?

EXEC SQL

What is a cursor?

Allows a set of rows to be retrieved from a embedded SQL statements by processing the query result row-by-row in the host language (e.g. while loop).

What is the difference between a scroll and non-scroll (default) cursor?

Scroll cursors can start from any row of the database and traverse it in either direction. This allows rows to be accessed multiple times - data modifications may cause the outcome to be order-dependent.


In contrast, cursors FETCH each row once only and then automatically move on to the next row.

Which commands to use with a cursor?

DECLARE C1 CURSOR FOR ...


OPEN C1


CLOSE C1

What happens when PREPARE is interpreted in Dynamic SQL?

The SQL statement is parsed, compiled and optimised.

What happens when EXECUTE is interpreted in Dynamic SQL?

The statement is run as it has already been parsed, compiled and optimised.


This will improve efficiency if the statement is executed many times, but will reduce it if only executed once.