DDMA Level II
Database flashcards
Read the question, say the answer to yourself, then turn the card to check. The first 10 of 49 cards are free; the rest come with the subject.
Card 1 of 49
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 39 come with the subject.
What do the degree and the cardinality of a relation measure?
Degree: the number of attributes (columns) the relation has. It describes the structure. Cardinality: the number of tuples (rows) it currently holds. It describes the content. A Students table with StudentID, Name, Course and Year holding 40 rows has degree 4 and cardinality 40; five new students raise cardinality to 45 and leave degree at 4.
Which problem does each of the first three normal forms remove?
1NF: repeating groups. Every column holds a single atomic value. 2NF: partial dependencies. Every non-key attribute depends on the whole primary key. 3NF: transitive dependencies. Every non-key attribute depends only on the key, not on another non-key attribute. They build in strict order: a table must be in 1NF before 2NF, and in 2NF before 3NF.
What are the four categories of SQL command, with their commands?
DDL (Data Definition Language): CREATE, ALTER, DROP. Defines structure. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE. Manipulates data. DCL (Data Control Language): GRANT, REVOKE. Controls access. TCL (Transaction Control Language): COMMIT, ROLLBACK. Manages transactions.
What do entity, referential and domain integrity each require?
Entity integrity: every table has a primary key, and it cannot be null. Referential integrity: a foreign key value must match an existing key in the referenced table, or be null. Domain integrity: a column's values must come from its defined valid set (its data type and any additional rules).
How does an ER diagram show entities, attributes and relationships?
Entities: boxes. Attributes: ovals attached to their entity. Relationships: diamonds connecting the related entities. It gives designers and stakeholders a shared picture of the data before any table is created.
Which rows do an inner, left, right and full join return?
Inner join: only rows with a match in both tables. Left join: all rows from the left table, with matching right-table data where it exists, else null. Right join: all rows from the right table, with matching left-table data where it exists, else null. Full join: all rows from both tables, matched where possible, else null on the unmatched side.
What are the functions of a DBMS?
Defining the database structure. Storing and retrieving data. Enforcing integrity constraints. Controlling concurrent access. Providing security. Supporting backup and recovery.
What are concurrency problems, and what forms do they take?
Problems that arise when multiple users or processes access the same data simultaneously. Forms: lock contention, deadlocks, and one long-running transaction blocking many others. Monitoring needs to surface them, because they are an access and scheduling issue rather than a raw capacity limit.
How does proactive monitoring differ from reactive monitoring?
Proactive: continuously tracks key metrics and alerts on early warning signs, before a problem is felt by users. Reactive: investigates after a problem has already been reported, working backward from the symptom to the cause. A mature operation relies mainly on proactive monitoring.
Name the five aggregate functions, and say why they are paired with GROUP BY.
COUNT, SUM, AVG, MIN and MAX: each summarises multiple rows into a single value. Without GROUP BY they give one figure for the whole table; with GROUP BY they give one figure per category, for example one average salary per department.
Define data, a database and a DBMS.
This answer comes with Database. Buy the subject
What are the everyday names for a relation, a tuple and an attribute?
This answer comes with Database. Buy the subject
A Products table has columns ProductID, Name, Price and Category and holds 250 rows. What are its degree and cardinality?
This answer comes with Database. Buy the subject
What are the characteristics of the database approach?
This answer comes with Database. Buy the subject
What is the purpose of a database?
This answer comes with Database. Buy the subject
How does a centralized database differ from a distributed one?
This answer comes with Database. Buy the subject
How does a hierarchical database differ from a network database?
This answer comes with Database. Buy the subject
What are a relational database and a cloud database?
This answer comes with Database. Buy the subject
What are the advantages of a database over separate flat files?
This answer comes with Database. Buy the subject
What are the four components of the DBMS environment?
This answer comes with Database. Buy the subject
What are the database design phases, in order?
This answer comes with Database. Buy the subject
In the ER model, what are an entity, an entity set and a relationship?
This answer comes with Database. Buy the subject
In the ER model, what are an attribute and a key?
This answer comes with Database. Buy the subject
What do Codd's rules define, and what requirements do they include?
This answer comes with Database. Buy the subject
What are a relation schema and a relation key?
This answer comes with Database. Buy the subject
What are the characteristics of a relational database?
This answer comes with Database. Buy the subject
What does logical database design translate the ER model into?
This answer comes with Database. Buy the subject
What is database normalisation for?
This answer comes with Database. Buy the subject
A table stores OrderID, Product1, Product2 and CustomerAddress, with a product in each product column. Which normal form does it violate, and what is the fix?
This answer comes with Database. Buy the subject
When can a table in 1NF fail 2NF?
This answer comes with Database. Buy the subject
In an Orders table, CustomerAddress depends on the customer, not on OrderID. What is this dependency called, and how does 3NF remove it?
This answer comes with Database. Buy the subject
How are tables and views maintained once they exist?
This answer comes with Database. Buy the subject
What is SQL, and what does it mean that it is declarative?
This answer comes with Database. Buy the subject
What are the advantages of SQL?
This answer comes with Database. Buy the subject
What are SQL data types and literals?
This answer comes with Database. Buy the subject
In what order are SQL operators evaluated in an expression?
This answer comes with Database. Buy the subject
How do a table, a view and an index differ?
This answer comes with Database. Buy the subject
What is a subquery, and how is it often used?
This answer comes with Database. Buy the subject
A Salaries table holds 45,000, 52,000, 38,000, 61,000 and 49,000. What do SELECT SUM(Salary) and SELECT AVG(Salary) return?
This answer comes with Database. Buy the subject
What do INSERT, UPDATE and DELETE do, and what normally targets them?
This answer comes with Database. Buy the subject
What happens if an UPDATE or DELETE has no WHERE clause, and how is it avoided?
This answer comes with Database. Buy the subject
What are a self join and a Cartesian join?
This answer comes with Database. Buy the subject
A report must list every customer, including the 20 of 100 who have never ordered. Which join is needed, and what would an inner join do?
This answer comes with Database. Buy the subject
What is a cursor used for?
This answer comes with Database. Buy the subject
What is the purpose of database monitoring?
This answer comes with Database. Buy the subject
What are the key metrics tracked in database monitoring?
This answer comes with Database. Buy the subject
What commonly makes a query slow, and why look there first?
This answer comes with Database. Buy the subject
What are hardware constraints, and why can tuning not fix them alone?
This answer comes with Database. Buy the subject
What are the database monitoring best practices?
This answer comes with Database. Buy the subject
How to revise with these cards
- Answer out loud or on paper before you turn the card: recalling is what fixes it.
- Go through the deck in short sessions of ten to fifteen minutes, every day.
- Shuffle once you know the order, so you are not answering from memory of the sequence.
- When a card catches you out, read the chapter it links to, then try the topic in a past paper.