Databases

//Problems with file-based storage

Data duplication, data inconsistency when one copy is updated and another is not, no enforced integrity, difficulty sharing between programs, and every program needing to know the file format. A DBMS solves all of these centrally.

//The three normal forms, one line each

1NF: no repeating groups — every field holds a single atomic value. 2NF: in 1NF, and every non-key field depends on the WHOLE primary key (this only bites with composite keys). 3NF: in 2NF, and no non-key field depends on another non-key field.

Not 1NF:  Student(ID, Name, Subject1, Subject2, Subject3)
1NF:      Student(ID, Name)
          Takes(ID, Subject)

Not 3NF:  Order(OrderID, CustomerID, CustomerName)
          CustomerName depends on CustomerID, not OrderID
3NF:      Order(OrderID, CustomerID)
          Customer(CustomerID, CustomerName)

//DDL vs DML

DDL defines the structure. DML works with the data inside it. Questions often specify which is wanted, so read carefully.

-- DDL
CREATE TABLE Student (
   StudentID INTEGER PRIMARY KEY,
   Name      VARCHAR(30) NOT NULL,
   ClassID   INTEGER,
   FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);
ALTER TABLE Student ADD Email VARCHAR(50);

-- DML
INSERT INTO Student VALUES (1, 'Bat', 7);
UPDATE Student SET Name = 'Bataa' WHERE StudentID = 1;
DELETE FROM Student WHERE StudentID = 1;

SELECT S.Name, C.ClassName
FROM   Student S, Class C
WHERE  S.ClassID = C.ClassID
ORDER  BY S.Name;

//Referential integrity

A foreign key must either match an existing primary key in the other table, or be empty. It stops you creating an order for a customer who does not exist, or deleting a customer who still has orders.

CHECK YOURSELF