Skip to content

Latest commit

 

History

History
263 lines (183 loc) · 10.6 KB

Day-z-11.md

File metadata and controls

263 lines (183 loc) · 10.6 KB
Raw

Databases

A database is a structured collection of data organized in a way that it can be easily accessed, managed, and updated. Databases are used to store and retrieve information for various purposes such as managing business operations, storing user information in web applications, and more.

Relational Models:

The relational model is a way of organizing data in tables (relations) with rows (tuples) and columns (attributes). It was introduced by Edgar Codd in the early 1970s. In a relational database, data is organized into one or more tables, and relationships between the tables are established using keys.

Tables, Rows, and Columns:

  • Table: A table is a collection of related data organized in rows and columns. Each table in a database has a unique name.

  • Row (Tuple): A row represents a single record or entry in a table. It contains a set of values corresponding to the attributes (columns) defined for that table.

  • Column (Attribute): A column represents a specific type of data in a table. It defines the kind of information that will be stored in that column.

Keys:

  • Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each row can be uniquely identified. Primary keys are used to enforce entity integrity.

  • Foreign Key: A foreign key is a field in a table that refers to the primary key in another table. It establishes relationships between tables.

Examples for primary keys and foreign keys with accompanying table diagrams.

Example with Primary Key:

Scenario: Customers and Orders

In this example, we have two tables, one for customers and one for orders. Each customer has a unique identifier, and each order is associated with a specific customer.

Tables:

  1. Customers Table:

    • Columns: customer_id (Primary Key), customer_name, email

  2. Orders Table:

    • Columns: order_id (Primary Key), customer_id (Foreign Key), order_date, total_amount

Table Diagram:

Customers Table
+-------------+-------------------+----------------------+
| customer_id | customer_name     | email                |
+-------------+-------------------+----------------------+
|     1       |   John Doe        | [email protected]     |
|     2       |   Jane Smith      | [email protected]     |
|     3       |   Bob Johnson     | [email protected]      |
+-------------+-------------------+----------------------+

Orders Table
+------------+--------------+---------------------+---------------+
| order_id   | customer_id  | order_date          | total_amount  |
+------------+--------------+---------------------+---------------+
|    101     |      1       |  2023-11-01         |     100       |
|    102     |      2       |  2023-11-02         |     150       |
|    103     |      1       |  2023-11-02         |     200       |
+------------+--------------+---------------------+---------------+

In this example, customer_id is the primary key in the Customers table, ensuring each customer has a unique identifier. The Orders table has a customer_id column, which is a foreign key referencing the primary key in the Customers table, establishing a relationship between the two tables.

Example with Foreign Key:

Scenario: Employees and Departments

In this example, we have two tables, one for employees and one for departments. Each employee belongs to a specific department, which is represented by a foreign key in the Employees table.

Tables:

  1. Employees Table:

    • Columns: employee_id (Primary Key), employee_name, department_id (Foreign Key), position

  2. Departments Table:

    • Columns: department_id (Primary Key), department_name

Table Diagram:

Employees Table
+-----------------+------------------+------------------+----------------+
| employee_id     | employee_name    | department_id    | position       |
+-----------------+------------------+------------------+----------------+
|       1         |   John Doe       |        101       |  Manager       |
|       2         |   Jane Smith     |        102       |  Sales Rep     |
|       3         |   Bob Johnson    |        101       |  Analyst       |
+-----------------+------------------+------------------+----------------+

Departments Table
+------------------+----------------------+
| department_id   | department_name     |
+------------------+----------------------+
|       101        |   Sales             |
|       102        |   Marketing         |
+------------------+----------------------+

In this example, employee_id is the primary key in the Employees table. The department_id column in the Employees table is a foreign key referencing the primary key in the Departments table. This establishes a relationship between employees and their respective departments.

These examples demonstrate the use of primary keys and foreign keys to establish relationships between tables in a relational database.

Relationships:

  • One-to-One: A relationship where one record in a table is associated with exactly one record in another table.

  • One-to-Many: A relationship where one record in a table can be associated with multiple records in another table.

  • Many-to-Many: A relationship where multiple records in a table can be associated with multiple records in another table.

Examples for each type of relationship along with a simple table diagram.

One-to-One Relationship:

Example: Users and User Profiles

In a one-to-one relationship, one record in a table is associated with exactly one record in another table.

Tables:

  1. Users Table:

    • Columns: user_id (Primary Key), username, email

  2. User Profiles Table:

    • Columns: profile_id (Primary Key), user_id (Foreign Key), first_name, last_name, date_of_birth

Table Diagram:

Users Table
+---------+-----------+---------------------+
| user_id | username  | email               |
+---------+-----------+---------------------+
|   1     | john_doe  | [email protected]    |
|   2     | jane_doe  | [email protected]    |
+---------+-----------+---------------------+

User Profiles Table
+------------+---------+------------+-----------+
| profile_id | user_id | first_name | last_name |
+------------+---------+------------+-----------+
|    1       |   1     |   John     |   Doe     |
|    2       |   2     |   Jane     |   Doe     |
+------------+---------+------------+-----------+

One-to-Many Relationship:

Example: Departments and Employees

In a one-to-many relationship, one record in a table can be associated with multiple records in another table.

Tables:

  1. Departments Table:

    • Columns: department_id (Primary Key), department_name

  2. Employees Table:

    • Columns: employee_id (Primary Key), department_id (Foreign Key), employee_name, position

Table Diagram:

Departments Table
+------------------+----------------------+
| department_id   | department_name     |
+------------------+----------------------+
|       1          |   Sales             |
|       2          |   Marketing         |
+------------------+----------------------+

Employees Table
+-----------------+------------------+----------------+
| employee_id     | department_id    | employee_name  | position   |
+-----------------+------------------+----------------+
|       1         |   1              |   John Doe     |  Manager   |
|       2         |   1              |   Jane Smith   |  Sales Rep |
|       3         |   2              |   Bob Johnson  |  Analyst   |
+-----------------+------------------+----------------+

Many-to-Many Relationship:

Example: Students and Courses

In a many-to-many relationship, multiple records in a table can be associated with multiple records in another table.

Tables:

  1. Students Table:

    • Columns: student_id (Primary Key), student_name

  2. Courses Table:

    • Columns: course_id (Primary Key), course_name

  3. Enrollment Table (Join Table):

    • Columns: enrollment_id (Primary Key), student_id (Foreign Key), course_id (Foreign Key)

Table Diagram:

Students Table
+-------------+-------------------+
| student_id  | student_name      |
+-------------+-------------------+
|     1       |   John Doe        |
|     2       |   Jane Smith      |
|     3       |   Bob Johnson     |
+-------------+-------------------+

Courses Table
+------------+---------------------+
| course_id  | course_name         |
+------------+---------------------+
|    101     |   Math 101          |
|    102     |   English 101       |
|    103     |   History 101       |
+------------+---------------------+

Enrollment Table
+----------------+-------------+------------+
| enrollment_id  | student_id  | course_id  |
+----------------+-------------+------------+
|      1         |      1      |     101    |
|      2         |      1      |     102    |
|      3         |      2      |     101    |
|      4         |      3      |     103    |
+----------------+-------------+------------+

These examples illustrate different types of relationships in a relational database. Understanding these concepts is crucial for designing and working with databases effectively.

Normalization:

Normalization is a process used in database design to organize a database into tables in such a way that redundancy is minimized. This is achieved by dividing a large table into smaller tables and defining relationships between them.

SQL (Structured Query Language):

SQL is a domain-specific language used for managing and querying relational databases. It provides commands for creating, retrieving, updating, and deleting data from databases. Some common SQL commands include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and more.

Examples:

Let's illustrate some of these concepts with a simple example. Consider two tables, Customers and Orders:

Customers Table:

CustomerID Name Email
1 Alice [email protected]
2 Bob [email protected]
3 Charlie [email protected]

Orders Table:

OrderID CustomerID Product Quantity
101 1 Product A 2
102 2 Product B 3
103 1 Product C 1

In this example, CustomerID is the primary key in the Customers table, and it is also a foreign key in the Orders table, establishing a one-to-many relationship.

These are some of the basic concepts related to databases and the relational model. There are many more advanced topics in database management and design, but understanding these fundamentals is a great starting point.