Relational Model & SQL Fundamentals

1. The Relational Model: Organizing Data in Tables

At its heart, the relational model is a straightforward way to represent data: in tables. Think of a simple spreadsheet. This model, proposed by Edgar F. Codd in 1970, provides a formal structure for this idea.

To get familiar with the basic terminology, let's look at a visual example.

The key idea is that data is stored in these relations, and relationships between different sets of data (like students and the courses they take) can be represented by linking these tables.

2. ER Diagrams: The Blueprint for a Database

Before building tables, a database designer first creates a blueprint. In database management, this blueprint is the Entity-Relationship (ER) Diagram. It's a visual way to figure out what data we need to store and how different pieces of data relate to each other.

The following video provides a great tutorial on the components of an ER diagram.

To summarize the key takeaways from the video:

• Entities: Nouns, like Student, Car, Product.
• Attributes: Properties of those nouns, like Name, Color, Price.
• Relationships: Verbs that connect entities, like Enrolls in, Owns, Purchases.
• Cardinality: Defines the numerical relationship, such as "one student can enroll in many courses" (one-to-many).

An ER diagram allows stakeholders to agree on the database structure before a single line of code is written, which is far more efficient than making changes later.

3. Keys: Enforcing Uniqueness and Connecting Tables

You saw the term "key attribute" in the last video. Keys are one of the most critical concepts in the relational model. Their primary job is to uniquely identify each row in a table. Without them, you could have two identical rows for two different employees, leading to chaos when trying to update a specific person's salary.

The following video from Neso Academy does an excellent job explaining why keys are needed and defining the different types.

Let's consolidate these definitions with a clear visual summary.

Key Hierarchy Summary:

• Super Key: Any attribute (or set of attributes) that can uniquely identify a tuple.
• Candidate Key: A minimal super key (no attribute can be removed). A table can have multiple candidate keys.
• Primary Key: The single candidate key chosen by the database designer to be the main identifier for the table. It cannot contain NULL values.
• Alternate Key: Any candidate key that was not chosen to be the primary key.
• Foreign Key: An attribute in one table that references the primary key of another table. This is how relationships are implemented.

4. SQL: The Language of Relational Databases

Now that we know how to design a database (with ER diagrams) and structure its tables (with keys), how do we actually retrieve or manipulate the data? The answer is SQL (Structured Query Language).

SQL is the standard language for interacting with relational database management systems (RDBMS). Its syntax is designed to be somewhat readable, resembling English. The most fundamental SQL statement is the SELECT query.

To understand its structure, it's helpful to know that SQL is a practical implementation of a more formal theory called Relational Algebra. Let's review a brief document that connects the theory to the practice.

A basic SQL query has the following structure:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• SELECT: Specifies the columns you want to see.
• FROM: Specifies the table you are querying from.
• WHERE: Filters the rows based on a given condition.

For example, using the EMPLOYEE table from the BYJU'S resource, if we wanted to find the names and ages of employees who live in Delhi, the query would be:

SELECT NAME, AGE
FROM EMPLOYEE
WHERE ADDRESS = 'DELHI';

This would return the names and ages for Riya and Preeti.

Conclusion

In this whirlwind tour, we've covered the entire lifecycle of basic data management in a relational world: from conceptual design to practical querying.

Key Takeaways:

• Relational Model: Data is organized into tables called relations, with rows called tuples and columns called attributes.
• ER Diagrams: These are the visual blueprints for a database, defining the entities, their attributes, and the relationships between them.
• Keys: They are fundamental for data integrity. The Primary Key uniquely identifies each record in a table, while the Foreign Key creates links between tables.
• SQL: This is the standard language used to SELECT (query), INSERT, UPDATE, and DELETE data in a relational database.

Preview of the Next Lesson:
Now that we know how to create tables, a new question arises: is our design good? A poorly designed set of tables can lead to data redundancy and update anomalies. In the next lesson, we will address this by covering database normalization and indexing, which are techniques to improve the structure and performance of our database.