1 Relational Database Foundations
Understand how relational databases represent information with tables, how schemas and data types structure that information, and how a SQL definition creates a table.
The Relational Model
A organizes information into named tables, also called relations. The relational model describes each in terms of attributes and tuples. In a database table, an corresponds to a column, and a corresponds to a row. This gives each row a consistent structure: values are recorded according to the same column definitions.
A database can contain tables for different kinds of things, such as students, courses, or orders. The tables are distinct structures within the same database.
Tables, Rows, and Schemas
Columns describe the fields being recorded; rows contain individual records. For example, a Student table could have columns for a student identifier, name, birth date, and enrollment status. One illustrative row might contain the values 1042, Maya Chen, 2006-04-18, and true for those fields, respectively.
The table's is its structure: its name, column names, and column definitions. Its data is the set of rows currently stored. Column definitions are specified when a table is created, while rows can be added or changed over time.
The mathematical relational model treats a as a set of tuples, so duplicate tuples are excluded from that model. SQL tables may contain duplicate rows unless rules are added to prevent them. SQL also does not guarantee a particular row order unless a query explicitly sorts the results.
Takeaway: Columns define what information a table records; rows supply the values for individual records.
Data Types and a SQL Definition
A specifies what kind of value a column can store and how that value is interpreted. Common examples include INTEGER for whole numbers, VARCHAR(n) or TEXT for character strings, DATE for calendar dates, BOOLEAN for true-or-false values, and DECIMAL(p, s) for exact decimal values such as prices. Type names and available features vary among database systems.
Choosing a suitable type helps make stored values meaningful. For example, a date column represents dates directly rather than storing them as arbitrary text.
This SQL statement defines the Student table's columns and the kinds of values they accept. It does not insert any student rows. The relational model supplies the conceptual structure, while SQL databases implement that structure using features that can vary by system.
Takeaway: A table definition establishes its columns and their types; inserting records is a separate operation.