4.10 - Fundamentals Of Databases Flashcards
(19 cards)
In entity notation Student(StudentID, Name, DOB) which attribute is the primary key?
StudentID (underlined attribute is the entity identifier).
What is an entity relationship diagram (ERD) used for?
To visualise a data model by drawing each entity as a box and connecting them with relationship lines (1-to-1, 1-to-many, many-to-many).
Define the database term attribute.
A single named data item describing a property of an entity (a column in a table).
Define primary key.
A single attribute (or minimal set) that uniquely identifies each record in a relation.
Define composite primary key.
A primary key made of two or more attributes together when no single attribute is unique on its own.
Define foreign key.
An attribute in one table that references the primary key of another table, creating a relationship.
State the concept of a relational database.
A collection of normalised tables (relations) that are linked through foreign-key relationships so that data redundancy is minimised and integrity is maintained.
What is the goal of normalisation?
To reduce redundancy and anomalies, and ensure data integrity by organising data into well-structured relations.
List the three anomalies removed by normalising to 3NF.
Insertion, deletion and update anomalies.
What are the three tests a relation must pass to be in Third Normal Form (3NF)?
1NF: No repeating groups; 2NF: No partial key dependence; 3NF: No transitive dependence (all non-key attributes depend only on the whole key and nothing else).
Given a relation Order(OrderID, CustomerID, CustName, CustAddress), why is it not 3NF?
CustName and CustAddress depend on CustomerID (a non-key) not on the whole primary key → transitive dependence.
Write an SQL CREATE TABLE statement that defines table Customer with CustomerID (PK) and Name.
CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, Name VARCHAR(40) );
Write an SQL query to retrieve all orders placed after 1-Jan-2025 ordered by date.
SELECT * FROM Orders WHERE OrderDate > ‘2025-01-01’ ORDER BY OrderDate;
SQL to insert a new student (ID = 17, Name = ‘Jo’).
INSERT INTO Student (StudentID, Name) VALUES (17, ‘Jo’);
SQL to update a book’s price (ISBN = ‘123’) to £19.99.
UPDATE Book SET Price = 19.99 WHERE ISBN = ‘123’;
SQL to delete all sessions with a mark below 40.
DELETE FROM Session WHERE Mark < 40;
How can a client–server DBMS serve many users at once?
The DBMS runs on a server; clients send requests over a network. The server handles queries concurrently and returns results.
What risk occurs if two clients edit the same record simultaneously?
A lost update: the later write overwrites the earlier change.
Name four techniques a DBMS can use to control concurrent access.
Record locking, serialisation, timestamp ordering, commitment ordering.
