Flashcards · 9618 · AS Topic 8

Databases (AS Level) flashcards

15 cards on AS Topic 8, subtopics 8.1 to 8.3. Pick a subtopic or study them all.

Study mode needs JavaScript. Every card is listed below, so you can still cover the answers and test yourself.

All 15 cards

For a quick read-through or printing. Cover the right-hand side to test yourself.

What are the limitations of a file-based approach to storing data?: Data is often duplicated across files (data redundancy), copies can disagree (inconsistency), and programs depend on the file structure (data dependency).
What is a primary key, and what is a foreign key?: Primary key: an attribute (or combination) that uniquely identifies each tuple in a table. Foreign key: an attribute in one table that is the primary key of another table, used to link them.
What is a candidate key and a secondary key?: Candidate key: any attribute that could act as the primary key. Secondary key: a candidate key not chosen as the primary key, often used as an index for searching.
What is referential integrity?: Every foreign key value must match an existing primary key value in the linked table, so no record refers to a record that does not exist.
What are first, second and third normal form?: 1NF: no repeated groups of attributes; all values atomic. 2NF: in 1NF and no partial dependencies (every non-key attribute depends on the whole primary key). 3NF: in 2NF and no non-key dependencies (non-key attributes depend only on the key).
What relationships can exist between entities in an E-R diagram?: One-to-one, one-to-many and many-to-many. A many-to-many relationship is resolved with a link table, giving two one-to-many relationships.

How does a DBMS address the problems of a file-based approach?: Data is stored once and shared, which reduces redundancy and inconsistency, and program–data independence means changes to the structure need not change every program.
What is the data dictionary in a DBMS?: Data about the data (metadata): table names, field names, data types, validation rules, relationships and access rights.
What do the developer interface and query processor of a DBMS do?: Developer interface: lets the developer create and maintain the database structure, forms and reports. Query processor: takes a query, works out how to carry it out and returns the results.
How does a DBMS help keep data secure?: Usernames and passwords, access rights (different views for different users), encryption, automatic back-ups, and an audit trail of changes.

What is the difference between DDL and DML?: Data Definition Language creates and changes the database structure (CREATE DATABASE, CREATE TABLE, ALTER TABLE). Data Manipulation Language queries and changes the data (SELECT, INSERT, UPDATE, DELETE).
What does this SQL do? SELECT Name, Age FROM Student WHERE Age > 16 ORDER BY Name;: Returns the Name and Age of every student older than 16, sorted alphabetically by name (ascending by default).
How do you add a record and change a record in SQL?: INSERT INTO Student (StudentID, Name) VALUES (1, 'Ali'); UPDATE Student SET Name = 'Sara' WHERE StudentID = 1;
What do GROUP BY and the aggregate functions do in SQL?: GROUP BY collects rows with the same value into groups. Aggregate functions summarise them: SUM, COUNT and AVG.
How do you join two tables in an SQL query?: With INNER JOIN … ON matching the foreign key to the primary key, e.g. SELECT … FROM Student INNER JOIN Class ON Student.ClassID = Class.ClassID;