Primary and Foreign Key | Different Types of Joins in MYSQL | Views

Learning Primary and Foreign Key | Different Types of Joins in MYSQL | Views

1 Like
  1. What is PK and FK ? How to create PK and FK after creating a table?

  2. What is JOIN ?

  3. Types of Join ?

  1. What is Indexing ?

  2. What is views ?

  3. Experiment with PK and FK ?

Ans1:- . Primary Key: A primary key is a column or set of columns in a table that uniquely identifies each row in that table. It ensures that each row has a unique identifier, and no two rows can have the same primary key value. The primary key serves as the main way to identify and access individual records in the table. In most databases, primary keys are also used as the basis for creating indexes, which improve data retrieval performance.Unique identifier for each row in a table, ensures uniqueness and serves as a basis for data retrieval.

For example, in a “Customers” table, the “CustomerID” column could be the primary key. Each customer would have a unique ID, and this ID would uniquely identify each customer record in the table.

CREATE TABLE Customers (
    CustomerID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
);

  1. Foreign Key: A foreign key is a column or set of columns in a table that establishes a link or relationship between the data in two tables. It creates a reference from one table’s primary key to another table’s primary key. The foreign key enforces referential integrity, meaning that the values in the foreign key column(s) must match a primary key in the referenced table or be null.Column(s) in a table that references the primary key of another table, establishing relationships between tables and maintaining data integrity.

Continuing with the “Customers” example, imagine a separate “Orders” table. The “CustomerID” column in the “Orders” table could be a foreign key that references the “CustomerID” primary key in the “Customers” table. This indicates that each order is associated with a specific customer.

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    CustomerID INT REFERENCES Customers(CustomerID),
    OrderDate DATE,
);

Using primary keys and foreign keys in database design helps create meaningful and structured relationships between tables, enabling efficient querying, data integrity enforcement, and accurate representation of real-world associations.

Ans2:- In the context of relational databases, a join is an operation that combines data from two or more tables based on a related column between them. The purpose of a join is to retrieve data that is distributed across multiple tables and present it as a unified result set, often by leveraging the relationships defined through primary keys and foreign keys.

Joins allow you to combine data from different tables to answer more complex questions or retrieve more comprehensive information than what a single table can provide. They are a fundamental part of querying and analyzing data in relational databases. Joining tables allows you to aggregate data, filter results, and answer complex questions that involve information from multiple related sources. The columns used for joining are typically the primary keys and foreign keys that establish the relationships between tables.
Ans3:- There are several types of joins, each serving a specific purpose:

  1. Inner Join: An inner join returns only the rows that have matching values in both tables. It combines rows from two tables based on a specified column or columns, discarding rows with no matching counterparts in the other table.
  2. Left Join (or Left Outer Join): A left join returns all the rows from the left table and the matching rows from the right table. If there is no match, the result will contain null values for the columns from the right table.
  3. Right Join (or Right Outer Join): A right join is similar to a left join but returns all the rows from the right table and the matching rows from the left table. If there is no match, the result will contain null values for the columns from the left table.
  4. Full Outer Join: A full outer join returns all the rows from both tables, with matching rows combined and non-matching rows filled with null values for the respective columns.
  5. Self Join: A self join is a join where a table is joined with itself. This can be useful when you have hierarchical data or when you want to compare rows within the same table.
1 Like

Ans1:- Indexing in the context of databases is a technique used to optimize the retrieval and querying of data from tables. An index is a data structure that provides a quick and efficient way to locate rows in a table based on the values in one or more columns. Just like an index in a book helps you quickly find a specific topic, a database index helps the database management system (DBMS) quickly locate the rows that match specific criteria.

Indexes significantly improve the performance of database operations, especially when dealing with large datasets, by reducing the need for the DBMS to scan the entire table to find the required data. Without indexes, the DBMS might need to perform full table scans, which can be slow and resource-intensive for complex queries.indexing is a vital optimization technique in databases that helps enhance data retrieval speed and query performance, making it possible to efficiently work with large volumes of data without sacrificing responsiveness.

Here’s a simple example to illustrate the concept:
Suppose you have a “Products” table with thousands of rows, and you often need to retrieve products based on their “Category” column. By creating an index on the “Category” column, the DBMS can quickly locate and retrieve the rows that match a specific category, improving query performance significantly.

**Ans2:-**In the context of databases, a view is a virtual table created by executing a predefined query on one or more existing tables or views. Unlike physical tables, views do not store data themselves; instead, they provide a way to present data from underlying tables in a specific, customized manner. Views allow users to simplify complex queries, hide sensitive information, and provide a more organized representation of data. views provide a way to create a customized, simplified, and secure representation of data in a database. They help users retrieve meaningful information while abstracting the complexity of underlying tables and queries.
Ans3:-
a simple example of creating and using primary keys and foreign keys in a relational database. Let’s consider a scenario involving two tables: “Students” and “Courses.”

1. Create the “Students” Table:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, the “StudentID” column is the primary key that uniquely identifies each student.

2. Create the “Courses” Table:

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

Here, the “CourseID” column is the primary key for the “Courses” table.

3. Add Foreign Key in “Students” Table:

Let’s say we want to associate students with the courses they are enrolled in. We’ll add a foreign key in the “Students” table that references the “CourseID” column in the “Courses” table.

ALTER TABLE Students
ADD COLUMN EnrolledCourseID INT,
ADD FOREIGN KEY (EnrolledCourseID) REFERENCES Courses(CourseID);

4. Insert Data:

Now, let’s insert some data into both tables:

INSERT INTO Students (StudentID, FirstName, LastName, EnrolledCourseID)
VALUES (1, 'John', 'Doe', 101);

INSERT INTO Students (StudentID, FirstName, LastName, EnrolledCourseID)
VALUES (2, 'Jane', 'Smith', 102);

INSERT INTO Courses (CourseID, CourseName)
VALUES (101, 'Mathematics');

INSERT INTO Courses (CourseID, CourseName)
VALUES (102, 'Science');

5. Query Using Primary and Foreign Keys:

You can now use primary and foreign keys to retrieve meaningful information and establish relationships between tables.

For example, to retrieve a list of students along with the names of their enrolled courses:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
JOIN Courses ON Students.EnrolledCourseID = Courses.CourseID;

This query uses a JOIN to combine data from both tables based on the relationship established by the foreign key.

In this example, the primary key “StudentID” in the “Students” table is referenced by the foreign key “EnrolledCourseID,” creating a relationship between students and their enrolled courses.

1 Like

Good and detail Notes. It’s gonna help al. :heart: