Data Organization and Relationships

Key Concepts

1. Primary Keys

A primary key (PK) is a unique identifier for each record in a table. It ensures that no two rows have the same value for the primary key column.

Example:

Students Table
StudentID (PK)
Name
Grade

• StudentID is the primary key. Each student has a unique ID.

---

2. Foreign Keys

A foreign key (FK) is a column in one table that references the primary key of another table. It establishes a relationship between two tables.

Example:

Grades Table
GradeID (PK)
StudentID (FK)
Subject
Score

• StudentID in the Grades table is a foreign key that references the StudentID in the Students table.

---

3. One-to-Many Relationships

A one-to-many relationship occurs when a single record in one table can be associated with multiple records in another table.

Example:

Stations Table	Measurements Table
StationID (PK)	ReadingID (PK)
Location	StationID (FK)
Elevation	Temperature
	Date

• One weather station (StationID) can have many temperature measurements.

---

4. Many-to-Many Relationships

A many-to-many relationship occurs when multiple records in one table can be associated with multiple records in another table. This requires an intermediary table (also called a junction table).

Example:

Observers Table	Observations Table	Observer_Observations Table
ObserverID (PK)	ObservationID (PK)	ObserverID (FK)
FirstName	Site_ID (FK)	ObservationID (FK)
LastName	Velocity
	Depth
	Date

• Multiple observers can be associated with multiple observations through the Observer_Observations table.

---

Practical Examples

1. Real Estate Database

• Tables:
  • Properties: Stores property details.
  • Transactions: Stores sales records.

Properties Table	Transactions Table
PropertyID (PK)	TransactionID (PK)
Address	PropertyID (FK)
Features	SaleDate
	SalePrice

Explanation:

• Each property has a unique PropertyID.
• Each transaction references a property using PropertyID as a foreign key.

---

2. School Management System

Tables:

• Students: Stores student details.
• Grades: Stores grades for each student.

Students Table	Grades Table
StudentID (PK)	GradeID (PK)
Name	StudentID (FK)
Grade	Subject
	Score

Explanation:

• Each student has a unique StudentID.
• Each grade record references a student using StudentID as a foreign key.

---

3. River Monitoring System

Tables:

• Sites: Stores river monitoring sites.
• Observations: Stores observations for each site.

Sites Table	Observations Table
ID (PK)	ID (PK)
Shape	Site_ID (FK)
Site_Name	Velocity
	Depth
	Observer
	Date

Explanation:

• Each site has a unique ID.
• Each observation references a site using Site_ID as a foreign key.

---

4. Advanced Example: Many-to-Many Relationship

Tables:

• Observers: Stores observer details.
• Observations: Stores observation details.
• Observer_Observations: Links observers to observations.

Observers Table	Observations Table	Observer_Observations Table
ObserverID (PK)	ObservationID (PK)	ObserverID (FK)
FirstName	Site_ID (FK)	ObservationID (FK)
LastName	Velocity
	Depth
	Date

• Explanation:
  • Multiple observers can be associated with multiple observations.
  • The Observer_Observations table acts as a bridge between the two tables.

---

Summary

• Primary Keys uniquely identify records in a table.
• Foreign Keys establish relationships between tables.
• One-to-Many Relationships link a single record in one table to multiple records in another.
• Many-to-Many Relationships require an intermediary table to link multiple records across tables.

---

Practice Exercise

1. Create a database schema for a Library Management System with the following tables:

   • Books (BookID, Title, Author)
   • Members (MemberID, Name, Email)
   • Borrowings (BorrowingID, BookID, MemberID, BorrowDate, ReturnDate)

2. Identify the primary keys and foreign keys in your schema.

3. Determine the relationships between the tables (one-to-many or many-to-many).