Welcome Back!

 Relational databases are the de facto standard for database applications. The model for a relational database states that information must be stored in tables, and in each table, information is viewed in a two-dimensional matrix consisting of rows and columns, which are often referred to as records and fields. Tables in SQL are typically modeled after real-world entities, such as vendors, donations, or invoices. A column always represents some attribute of an entity, such as vendor name, donation amount, or invoice total. A row contains a set of values for a single instance of the entity, such as one vendor name, one donation amount, and one invoice total.

If a table has a column that uniquely identifies each row in the table, this is the primary key. There is a rule in SQL that states that each table in the database should have a primary key defined. Without the primary key, the core functionality and power of SQL depreciates. Always remember that SQL is about relationships. Primary keys is the first step toward relationship building, but there is another key that builds an even better bridge or link between tables. It’s called a foreign key. But foreign keys, that is a new concept, what are those? Foreign keys quite simply are one or more columns in a table that refer to a primary key in another table. Tables, therefore, are related to each other through the primary and foreign key columns. Let us look at two tables: Donations and Colleges.

Donor Donations Table

DonorID	DonorName	DonorAmount	DonorCity
100	Jane Lee	$250	Cincinnati
101	Mike Lee	$500	Dayton
102	Dee Lee	$650	Kettering
103	Regina Lee	$1,000	Centerville

College Table

CollegeID	CollegeName	Department
100	UC	English
101	Miami U	Art
102	NKU	Comp Sci
103	Xavier	Biology

If I asked you to tell me the relationship between the two tables, what would you say? You might say:

• Idk
• Who cares?
• Can we talk about something else?

Okay seriously, what would you say? You might say:

• One donor can donate to any college
• One college can receive a donation from a donor
• Many donors can donate to many colleges
• Many colleges can receive a donation from many donors

While we can visually see and understand the relationship between the two tables, there needs to be a relationship created that SQL understands. The most common type of relationship is a one-to-one or a many-to-many relationship with another table. Remember how I mentioned earlier that tables are related on their key columns? Well, looking at our tables we do in fact have primary keys identified in each table, the DonorID and CollegeID columns. Hoo-ray so that holy and sacred rule of having primary keys identified has been obeyed. However, we are missing foreign keys. These keys identify a related row in another table. To build the many-to-many relationship, we would simply add the DonorID column to the college table. Voila, we now have a valid foreign key!

Donor Donations Table

DonorID	DonorName	DonorAmount	DonorCity
100	Jane Lee	$250	Cincinnati
101	Mike Lee	$500	Dayton
102	Dee Lee	$650	Kettering
103	Regina Lee	$1,000	Centerville

College Table

CollegeID	CollegeName	Department	DonorID
200	UC	English	100
201	Miami U	Art	101
202	NKU	Comp Sci	102
203	Xavier	Biology	103

In this lesson, you’ve learned a little about the relational database model. You have learned that

• Information must be stored in tables
• Information is viewed in columns and rows, which are also referred to as records and fields
• Each table should have a primary key identified, which is a unique identifier
• Foreign keys provide a link between two tables/one or more columns in a table that refer to a primary key in another table
• Most common relationships between tables: one-to-one and many-to-many

Join me in the next blog to understand how columns in a table are defined.