Basic Concepts underlying Relational Databases

Here are some key points to explain how relationships work in relational databases:
Types of Relationships in Relational Databases:

1. One-to-One (1:1):
   • Each record in one table corresponds to one, and only one, record in another table. This is less common but useful for splitting data for security or performance reasons. For example, a user table might have a one-to-one relationship with a user_profile table where sensitive information is stored separately.
     

     CREATE TABLE Users (
         user_id INT PRIMARY KEY,
         username VARCHAR(50)
     );
     
     CREATE TABLE UserProfiles (
         profile_id INT PRIMARY KEY,
         user_id INT UNIQUE FOREIGN KEY REFERENCES Users(user_id),
         birth_date DATE
     );
     

2. One-to-Many (1:N):
   • A record in one table can be associated with multiple records in another table. This is very common. For instance, one department might have many employees.
     

     CREATE TABLE Departments (
         dept_id INT PRIMARY KEY,
         dept_name VARCHAR(100)
     );
     
     CREATE TABLE Employees (
         emp_id INT PRIMARY KEY,
         emp_name VARCHAR(100),
         dept_id INT,
         FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
     );
     

3. Many-to-Many (N:M):
   • Records in one table can relate to multiple records in another table, and vice versa. This requires a junction or linking table to manage the relationship. An example would be students and courses where a student can take many courses, and a course can have many students.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

More information on these types of relationships can be found in the ERD module at Three Relationship Types .
Enforcing Relationships:

• Foreign Keys: These are used to enforce relationships. They ensure that values in one table must correspond to values in another table. If you try to insert a value in the foreign key column that doesn't exist in the primary key column of the referenced table, the DBMS will reject the operation.
• Referential Integrity: This ensures that relationships between tables remain consistent, preventing actions like deleting a record that is referenced by another table unless you use operations like CASCADE which automatically delete or update related data.
• Cascade Operations:
  • ON DELETE CASCADE: Automatically deletes related rows in the child table when a row in the parent table is deleted.
  • ON UPDATE CASCADE: Updates the foreign key automatically when the primary key is updated.

Understanding and implementing these relationships correctly is crucial for database design to ensure data integrity and to make data retrieval more efficient through joins and queries.

A database is made up of one or more tables. Multiple tables in a database share a relationship in some way; that's why such a database is called a relational database.
As the database designer, you decide how the tables will relate to each other, although not every table will relate to every other table. Once you determine the relationships between tables, you can have the database management system (DBMS) enforce the relationship. SQL Server 2012 is the DBMS that we will be using throughout this course. Enforcement of relationships is known as referential integrity, which we will cover later in this course.

• Logical Database Design
  When you design a relational database, the easiest way to begin is to construct a logical database design, also known as a logical model. In your logical model, you note on paper the major categories of information that are to be stored in the database. You can break down your logical model further into the actual tables that will store data, but this depends on your application. The following diagram shows a logical model for the database you will be developing for the course project: