Database systems · Chapter 2 · Data models · Study guide
A short, plain summary of every term you need from Chapter 2. Each entry is a definition and the one or two facts that usually get asked about. The longer worked pages in this section go deeper where a picture helps.
Proposed by E. F. Codd in 1970 and still the design almost every business database uses.
The relational model represents data as a collection of tables that are linked to one another by shared values rather than by stored pointers. Its foundation is a mathematical idea called a relation, which is a set of rows with no order and no duplicates. The user is not expected to think that way: the RDBMS presents each relation as an ordinary table and hides the storage details, and that simplicity is the reason the model displaced the hierarchical and network designs that came before it.
Relational model = the database structure and rules.
Relational diagram = the picture that shows that structure visually.
The same three things have a formal name, a name the user actually says, and an older file system name. The formal terms are worth knowing because they explain the behaviour: rows come back unordered unless you sort them, because a relation is a set.
| Relational model | What the user sees | File system |
|---|---|---|
| Relation | Table | File |
| Tuple | Row | Record |
| Attribute | Column | Field |
Related terms from the same chapter: a domain is the set of allowed values for an attribute, a primary key is the attribute or attributes that uniquely identify each row, and a foreign key is an attribute in one table whose values must match a primary key in another. Foreign keys are what hold the relationships together.
A schema is the description of the whole database: every table, view, and index, and how they relate, defined once. A subschema is the slice of that schema a particular application program is allowed to see and use. The pair comes from the network model, and the idea carried straight into relational systems as views. The schema together with this descriptive information is the database's metadata.
The software that manages the database, and the language you use to lay it out.
A DBMS, a database management system, is the software between the user and the stored data. It handles storage, access control, security, backup and recovery, and lets many users work at once without seeing the physical layout. Hierarchical and network systems are all DBMSs. An RDBMS, a relational database management system, does everything a DBMS does and adds the relational rules on top: data is held as related tables, integrity is kept through primary and foreign keys, and SQL is the language for defining and querying it. Every RDBMS is a DBMS; the extra letter is the relational rule set.
The DDL is the part of the database language used to create and change the structure of the database: the schema and subschemas, the tables, the columns and their data types, and the keys and constraints. In SQL the DDL statements are CREATE, ALTER, and DROP. It is the counterpart of the DML, the data manipulation language, which works with the rows inside those structures using SELECT, INSERT, UPDATE, and DELETE. Short version: DDL defines the containers, DML moves the contents.
Three named things that look similar and are easy to mix up, plus the notation they are usually drawn in.
A relational diagram is a picture of the tables in a relational database together with the primary and foreign keys that link them. It shows each table as a box with its columns listed, marks the keys, and draws a line from each foreign key back to the primary key it references. It is an implementation level drawing: everything on it corresponds to something you could actually build with DDL.
These three sit at different levels. The entity relationship model is the approach: the set of ideas, introduced by Peter Chen in 1976, for describing data as entities, attributes, and relationships. An ERD is a single diagram that applies that approach to one specific database. A relational diagram is a lower level drawing of the tables and keys that database turns into.
| Term | What it is | Can show M:N directly? |
|---|---|---|
| ER model | The modelling approach and its concepts | Yes, as an idea |
| ERD | A conceptual diagram drawn with that approach | Yes |
| Relational diagram | An implementation diagram of tables, keys, and links | No, it needs a bridge table first |
For a design as small as one customer and one invoice the ERD and the relational diagram look almost identical, which is why one drawing often serves both. They stop looking alike the moment the design contains a many to many relationship. The customer and invoice page walks through exactly this case.
Crow's Foot is the most common notation for drawing relationships. Each end of a relationship line carries two marks: the one touching the entity box is the maximum, one or many, and the one set just behind it is the minimum, zero or one. The four resulting symbols are exactly one, zero or one, one or many, and zero or many. Connectivity, whether the relationship is 1:1, 1:M, or M:N, comes only from the two maximums.
Where the model comes from, and the four words the model is built out of.
A business rule is a brief, precise, unambiguous statement of a policy or practice in the organization, written plainly enough to design from. Rules are gathered from managers, written procedures, and interviews with the people who do the work. They matter because they tell the designer what the data means and how it behaves, and they translate mechanically into the model: nouns tend to become entities, verbs tend to become relationships, and quantities and limits become constraints. A constraint is a restriction on the data that the database enforces, such as a value that may not be null, must be unique, or must fall in a range. Constraints are how business rules are kept true once the database is running.
These are the parts of an ER model, and the exam question is usually which is which.
| Term | What it is | In table terms |
|---|---|---|
| Entity | A person, place, thing, or event data is kept about; drawn as a rectangle, named with a singular noun | A table |
| Entity instance | One single occurrence of that entity, such as one particular customer | One row |
| Entity set | The whole collection of instances of one entity | All the rows of the table |
| Attribute | A characteristic of an entity | A column |
| Relationship | A named association between entities, read in both directions with a verb phrase | A foreign key link |
A later model that keeps data and the code that acts on it together in one place.
An object is a representation of one real world thing, holding both its data and its behaviour, for example one specific customer. A class is the template for a group of objects that share the same structure and behaviour; every object is an instance of some class. A method is a piece of code defined in the class that performs an action on an object's data, such as place order or pay. You run a method by sending the object a message. The class also lists the object's attributes, so a class is really attributes plus methods.
Inheritance is the ability of a class to take on the attributes and methods of a class above it in a hierarchy. A general class such as Person holds what is common; more specific classes such as Employee and Customer inherit that and add their own. It saves repetition and keeps shared behaviour in one place. UML, the Unified Modeling Language, is a standard set of diagrams and symbols, based on object oriented ideas, for modelling a system. Its class diagram shows classes, their attributes and methods, and the relationships and inheritance between them, so it is often used to draw a data design even outside pure object oriented work.
The end of the chapter, on data that does not fit the relational model comfortably and the tools built for it.
Big Data is the effort to store and get value from volumes of data, much of it generated on the web, that are too large and too fast moving for a traditional relational database to handle at reasonable cost. It is usually characterized by three properties, the 3 Vs.
Hadoop is an open source, Java based framework for storing and processing very large data sets across a cluster of ordinary, low cost machines, designed so that the failure of any one machine does not lose data or stop the job. Its two main parts are a storage layer, HDFS, and a processing layer, MapReduce.
HDFS, the Hadoop Distributed File System, splits each file into large blocks and spreads copies of every block, three by default, across the machines in the cluster. It follows a write once, read many pattern. The cluster has two kinds of node. The NameNode is the master: it holds only the metadata, meaning the directory structure and the record of which block lives on which machine, but none of the file contents. The DataNodes are the workers: they hold the actual blocks and serve them to clients. A client asks the NameNode where a file's blocks are, then reads or writes them straight from the DataNodes.
MapReduce is the processing model Hadoop uses to run a computation over data that is spread across many machines. It has two phases. The map phase runs on each machine in parallel, close to the data it already holds, and turns its slice of the input into key and value pairs. The pairs are then shuffled and sorted so that all values for the same key end up together. The reduce phase takes each key and its collected values and combines them into the final result, for example a count or a total per key.