Relational Schema#
By this point you may be wondering why our data tables have various id columns. The ERD we built in Q1 outlines the conceptual relationships across our data structures. To put that another way, ERDs are a conceptual model of a relational database structure.
But relationship database programs require matching data fields and unique identifiers to be able to manifest those conceptual relationships. These unique identifiers and matching fields are called keys.
Keys#
“The primary key is defined as a column (or set of columns) where each value is unique and identifies a single row of the table.” (Primary Key-Foreign Key Relationships, Oracle)
“A foreign key is a column or a set of columns in one table that references the primary key columns in another table.” (Primary Key-Foreign Key Relationships, Oracle)
Image from Foreign and Primary Key Differences (Visually Explained), EssentialSQL.
Relational Schema#
ERDs are a conceptual model of the relationships in a database. An ERD may not directly map onto the structure of tables, fields, etc. in a relational database.
But, we might also want to represent our database structure based on tables, primary keys, and foreign keys. Relational schema (RS) model the logical, machine-readable relationship across tables and fields (linked by primary/foreign keys).
Building a RS diagram involves listing fields by table, identifying relationships across tables, and which fields are serving as primary/foreign keys.
An image from StackExchange’s ER vs database schema diagrams page that illustrates the difference:
Visit StackOverflow’s What is the different between ER Diagram and Database Schema? page to learn more.
Application#
Q2A: What fields in our tables are functioning as keys? Which ones are primary keys and which ones are foreign keys? Include some explanation of your thought process.
Q2B: Build a relational schema for a relational database that includes the players, teams, locations, and transactions tables. Include your diagram as well as an explanation of your process.