Databases, big data and functional programming

7.1 · Free notes

Databases, normalisation and SQL

Key points

• Normalisation organises data to reduce redundancy: 1NF requires atomic values with no repeating groups; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies.
• Entity relationship diagrams model relationships (one-to-one, one-to-many, many-to-many), with many-to-many usually resolved using a link table.
• SQL supports SELECT with JOIN, aggregate functions (COUNT, SUM, AVG), GROUP BY, and data modification with INSERT, UPDATE and DELETE.
• Transactions should satisfy ACID properties (atomicity, consistency, isolation, durability); record locking prevents conflicting simultaneous updates.
• Referential integrity ensures a foreign key always refers to an existing record.

In more detail

• Worked normalisation: (StudentID, StudentName, CourseID, CourseName, Grade) becomes Student(StudentID, StudentName), Course(CourseID, CourseName) and Enrolment(StudentID, CourseID, Grade).
• Worked nested query: SELECT Name FROM Student WHERE StudentID IN (SELECT StudentID FROM Enrolment WHERE CourseID = 'CS');
• Worked grouping: SELECT CourseID, AVG(Grade) FROM Enrolment GROUP BY CourseID; gives the average grade for each course.
• Worked table definition: CREATE TABLE Enrolment (StudentID INT, CourseID CHAR(4), Grade INT, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID));
• Worked anomaly: in an unnormalised table, deleting the last student on a course also loses the course's name — a deletion anomaly.