CISSE Intermediate Level

Data Management Information Systems flashcards

Read the question, say the answer to yourself, then turn the card to check. The first 10 of 71 cards are free; the rest come with the subject.

Card 1 of 71

Jump to a card

Complete Flashcard Reference

Review every card in this deck. Open any card to reveal its answer. The first 10 are free; the other 61 come with the subject.

What are the four levels of the DIKW pyramid, in order?

Data: raw, unprocessed facts with no context, e.g. the number 42. Information: data given context and meaning, e.g. "42 units sold today". Knowledge: information combined with an understanding of patterns, e.g. sales rise every Friday. Wisdom: applied judgement on how to act, e.g. adding Friday staff because of the pattern.

What do the four ACID properties of a transaction mean?

Atomicity: the transaction executes completely or not at all. Consistency: it takes the database from one valid state to another, never breaking integrity constraints. Isolation: concurrent transactions do not interfere with each other's uncommitted state. Durability: once committed, its effect survives any later failure.

What are the three cardinalities of a relationship, with an example of each?

One-to-one (1:1): one Employee has one Employee Profile. One-to-many (1:N): one Department has many Employees, each in one Department. Many-to-many (N:N): many Students enrol in many Courses.

What does first normal form (1NF) require, and what is a typical violation?

Every attribute holds a single, indivisible (atomic) value, with no repeating groups or multi-valued fields in a row. Violation: a Student table with one "Phone Numbers" column holding several numbers separated by commas. Each number belongs in its own row or a related table.

What is the difference between an INNER JOIN and a LEFT (OUTER) JOIN?

INNER JOIN: only rows with a match in both tables. LEFT JOIN: every row from the left table, with matched rows from the right and NULL where there is no match. So a LEFT JOIN keeps the unmatched left-side rows that an INNER JOIN drops.

What do the five SQL integrity constraints enforce?

PRIMARY KEY: uniquely identifies each row and cannot be NULL. FOREIGN KEY: the value must exist in a referenced table's key (referential integrity). UNIQUE: no two rows share the same value in the column. NOT NULL: the column cannot hold NULL. CHECK: a custom rule the value must satisfy.

Which SQL commands make up the Data Definition Language, and what does each do?

CREATE TABLE: builds a new table, with its columns, types and constraints. ALTER TABLE: changes an existing structure. DROP TABLE: removes a table entirely, e.g. DROP TABLE archive_enrolments;

What are the four components of a DBMS?

Query processor: interprets and executes SQL queries. Storage manager: manages how data is physically stored and retrieved. Transaction manager: ensures transactions complete reliably. Data dictionary (catalog): stores metadata, the schema, constraints and structure describing the data.

How does OLTP differ from OLAP?

OLTP: records and processes live transactions; current, detailed, normalised data; simple one-record operations; millisecond responses because a user is waiting. OLAP: analyses historical data for decisions; aggregated, denormalised data; complex multi-table aggregation queries; can take longer.

When does an index suit a query better than hashing, and why?

An index (commonly a B-tree) finds matching rows without scanning the whole table and supports range and sorted queries, at the cost of extra storage and slightly slower writes. Hashing computes a record's location from its key, giving very fast exact-match lookup but poor support for ranges. So "orders between 1,000 and 5,000" needs an index.

What is data management?