Relational Databases

🗃️ Databases and the DBMS

Data is raw facts (85, "Amani"); information is data with meaning ("Amani scored 85 in Maths"). A database is an organised collection of related data. A database management system (DBMS), such as MySQL, PostgreSQL, Microsoft Access or Oracle, lets you create, store, search and secure it. Metadata (stored in a data dictionary) is data about the data, such as each field's name and type.

🌳 Database models

Hierarchical databases store data as a tree (one parent, many children). Network databases allow many links between records. Relational databases, the most common today, store data in tables that are linked by shared values. Object-oriented databases store data as objects, like OOP programs.

📊 Tables, records and fields

In a relational database each table holds one entity, such as Students. Each row is a record (one student), and each column is a field or attribute (name, admission number, class). Every field has a data type: text, integer, decimal, date or yes/no.

🔑 Keys

A primary key uniquely identifies each record, such as an admission number; it can never be blank or repeated. A foreign key is a field in one table that refers to the primary key of another, which is how tables link. A composite key combines two or more fields to be unique. A secondary key is used for searching, such as a surname.

🔗 Relationships and ER diagrams

Tables relate one-to-one (a student has one ID card), one-to-many (one class has many students) or many-to-many (students take many subjects, and each subject has many students). A many-to-many link needs a linking table. An entity-relationship (ER) diagram plans all this: rectangles for entities, ovals for attributes, diamonds for relationships.

Key points

• Data is raw facts; information is data with meaning.
• A DBMS such as MySQL creates, stores and secures databases.
• Relational databases store data in linked tables.
• A row is a record, and a column is a field.
• A primary key uniquely identifies each record; a foreign key links to another table.
• Relationships can be one-to-one, one-to-many or many-to-many.
• ER diagrams use rectangles for entities and diamonds for relationships.

Try it for real: Design a school database

• Design two tables, Students and Classes: list the fields, their data types, the primary key of each, and the foreign key that links them.
• Draw an ER diagram showing that one class has many students.