WEEK3 NOTES AND EXAMPLES

WEEK3 PART1
Basic Table Creation

Creating a table involves defining the table name, columns, data types, and any
constraints like primary keys or foreign keys. Below are different examples and methods
to create a table in.

1. Basic Table Creation

This is the most straightforward way to create a table.

CREATE TABLE Employees (

EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);

This creates a table named `Employees` with five columns: `EmployeeID`, `FirstName`,
`LastName`, `HireDate`, and `Salary`.

2. Table Creation with Primary Key

Adding a primary key constraint ensures that each row in the table is unique.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);

The `EmployeeID` column is defined as the primary key, ensuring that each employee has a
unique ID.

3. Table Creation with Foreign Key

Creating relationships between tables using foreign keys.

CREATE TABLE Departments (

 DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

`DepartmentID` in the `Employees` table is a foreign key that references the `DepartmentID` in
the `Departments` table, establishing a relationship between the two tables.

4. Table Creation with Constraints

Adding constraints like `NOT NULL`, `UNIQUE`, and `CHECK`.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
HireDate DATE,
Salary DECIMAL(10, 2) CHECK (Salary > 0),
Email VARCHAR(100) UNIQUE
);

- `NOT NULL` ensures that the column cannot have `NULL` values.
- `CHECK (Salary > 0)` ensures that the salary must be greater than 0.
- `UNIQUE` ensures that the email addresses are unique across all records.

5. Table Creation with Default Values

Setting default values for columns.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
HireDate DATE DEFAULT CURRENT_DATE,
Salary DECIMAL(10, 2) DEFAULT 50000
);
 - `HireDate` will default to the current date if not provided.
- `Salary` will default to 50000 if not provided.

6. Temporary Tables
Temporary tables are used for storing data temporarily.

CREATE TEMPORARY TABLE TempEmployees (

EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);

A temporary table named `TempEmployees` is created, which is automatically dropped at the

end of the session.

7. Table Creation Using `LIKE`

Creating a table with the same structure as an existing table.

CREATE TABLE EmployeesCopy LIKE Employees;

This creates a table `EmployeesCopy` with the same structure as the `Employees` table but
without copying the data.

8. Table Creation with `SELECT` Statement

Creating a table and populating it with data from another table.

CREATE TABLE SeniorEmployees AS

SELECT * FROM Employees WHERE Salary > 70000;

This creates a table `SeniorEmployees` and populates it with records from the `Employees`
table where the salary is greater than 70000.

Primary Key Constraint

Here are four different ways to create a primary key constraint in :

1. Defining Primary Key within Column Definition

This is the most common way to define a primary key directly within the column definition.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
 LastName VARCHAR(50)
);

The `EmployeeID` column is defined as the primary key during the table creation.

2. Defining Primary Key as a Table Constraint

You can define the primary key constraint separately at the end of the column definitions.

CREATE TABLE Employees (

EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
PRIMARY KEY (EmployeeID)
);

The primary key is specified at the table level after defining all columns.

3. Composite Primary Key

You can create a primary key on multiple columns, known as a composite primary key.

CREATE TABLE EmployeeProjects (

EmployeeID INT,
ProjectID INT,
PRIMARY KEY (EmployeeID, ProjectID)
);

The combination of `EmployeeID` and `ProjectID` forms the primary key, ensuring that each pair
is unique.

4. Adding Primary Key Constraint to an Existing Table

You can add a primary key constraint to an existing table after it has been created.

ALTER TABLE Employees

ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID);

This adds a primary key constraint named `PK_Employee` to the `EmployeeID` column in the
`Employees` table after the table is already created.

Composite Key

A composite key is a primary key that consists of more than one column.
CREATE TABLE OrderDetails (
OrderID INT,
 ProductID INT,
Quantity INT,
Price DECIMAL(10, 2),
PRIMARY KEY (OrderID, ProductID)
);

The `OrderID` and `ProductID` together form the primary key, ensuring that each combination is
unique.

Unique Key
A unique key ensures that all the values in a column (or a combination of columns) are unique
across the table.

1.Unique Key within Column Definition

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);

The `Email` column must have unique values across the table.

2.Unique Key as a Table Constraint

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Email VARCHAR(100),
FirstName VARCHAR(50),
LastName VARCHAR(50),
UNIQUE (Email)
);

The unique constraint is defined at the table level, ensuring the `Email` column's uniqueness.

Foreign Key Constraint

1. Foreign Key in Column Definition

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
 CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

The `CustomerID` column is a foreign key referencing the `CustomerID` column in the
`Customers` table.

2. Foreign Key as a Table Constraint

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

The foreign key is defined as a table constraint after the columns.

3. Named Foreign Key Constraint

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID)
);

The foreign key constraint is named `FK_CustomerOrder`, which can make it easier to
reference and manage.

4. Foreign Key with ON DELETE and ON UPDATE Clauses

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
The `ON DELETE CASCADE` and `ON UPDATE CASCADE` clauses ensure that when a
record in the `Customers` table is deleted or updated, related records in the `Orders` table are
automatically deleted or updated.

Examples on selecting the right constraints

Here are a few examples that demonstrate choosing the correct key constraints in , similar to
the ones you provided:

Example 1: Single Course Enrollment Constraint

CREATE TABLE Enrollment (

student_id INTEGER,
course_id CHAR(8),
semester CHAR(6),
PRIMARY KEY (student_id, course_id, semester)
);

* “For a given student in a specific course during a specific semester, there can be only one
enrollment record.”
* This constraint allows a student to enroll in the same course again in a different semester.

Example 2: Enforcing Unique Email per Student

CREATE TABLE Students (

student_id INTEGER PRIMARY KEY,
email VARCHAR(100) UNIQUE
);

* “Each student must have a unique email address.”

* The `UNIQUE` constraint ensures that no two students can share the same email address.

Example 3: Restricting Multiple Memberships with the Same Role

CREATE TABLE ProjectMembers (

member_id INTEGER,
project_id INTEGER,
role VARCHAR(50),
PRIMARY KEY (member_id, project_id),
UNIQUE (member_id, role)
);

* “A member can have only one role in a project, but can be involved in multiple projects.”
* The `UNIQUE` constraint ensures that the same member cannot have multiple roles within the
same project.

Example 4: Composite Key with Unique Constraint

CREATE TABLE LibraryBooks (

isbn CHAR(13),
library_id INTEGER,
copy_number INTEGER,
PRIMARY KEY (isbn, library_id),
UNIQUE (isbn, copy_number)
);

* “Each library may have multiple copies of the same book, but each copy must have a unique
identifier within the library.”
* This ensures that while multiple copies of a book can exist, they are uniquely identified by their
`copy_number` in each library.

Example 5: Conditional Unique Constraint

CREATE TABLE Assignments (

assignment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id CHAR(8),
submission_date DATE,
UNIQUE (student_id, course_id, submission_date)
);

* “A student can submit only one assignment per course on a specific date.”
* This constraint prevents duplicate submissions on the same day for the same course by a
single student.

Check, Insert, Alter, Update, Drop, Delete Examples

1. CHECK Constraint

Example 1: Age Restriction

CREATE TABLE Employees (

emp_id INTEGER PRIMARY KEY,
emp_name VARCHAR(50),
emp_age INTEGER,
CHECK (emp_age >= 18 AND emp_age <= 65)
);

This ensures that the `emp_age` must be between 18 and 65 for any employee record.

Example 2: Grade Validation

CREATE TABLE Students (

student_id INTEGER PRIMARY KEY,
student_name VARCHAR(50),
grade CHAR(1),
CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

This restricts the `grade` values to only valid entries: 'A', 'B', 'C', 'D', or 'F'.

2. INSERT

Example 1: Inserting a Record

INSERT INTO Employees (emp_id, emp_name, emp_age)

VALUES (101, 'Alice', 30);

This inserts a new employee record into the `Employees` table.

Example 2: Inserting a Record with Default Values

CREATE TABLE Orders (

order_id INTEGER PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
amount DECIMAL(10, 2)
);

INSERT INTO Orders (order_id, amount)

VALUES (1, 250.00);

The `order_date` will automatically be set to the current date if not provided in the `INSERT`
statement.

3. ALTER

Example 1: Adding a New Column

ALTER TABLE Employees

ADD COLUMN salary DECIMAL(10, 2);
This adds a new column `salary` to the `Employees` table.

Example 2: Modifying a Column Data Type

ALTER TABLE Students

ALTER COLUMN grade TYPE CHAR(2);

This changes the data type of the `grade` column in the `Students` table from `CHAR(1)` to
`CHAR(2)`.

Example 3: Adding a CHECK Constraint

ALTER TABLE Employees

ADD CONSTRAINT chk_age CHECK (emp_age >= 18 AND emp_age <= 65);

This adds a `CHECK` constraint to ensure that employee ages remain within a specified range.

4. DROP

Example 1: Dropping a Column

ALTER TABLE Employees

DROP COLUMN emp_age;

This removes the `emp_age` column from the `Employees` table.

Example 2: Dropping a Table

DROP TABLE Students;

This deletes the entire `Students` table and all of its data.

Example 3: Dropping a Constraint

ALTER TABLE Employees

DROP CONSTRAINT chk_age;

This removes the `chk_age` constraint from the `Employees` table.

5. DELETE

Example 1: Deleting a Specific Record

DELETE FROM Employees
WHERE emp_id = 101;

This deletes the employee record with `emp_id` 101 from the `Employees` table.

Example 2: Deleting All Records

DELETE FROM Orders;

This deletes all records from the `Orders` table, but the table structure remains.

Example 3: Deleting Records with a Condition

DELETE FROM Students

WHERE grade = 'F';

This deletes all students who have received an 'F' grade.

6. UPDATE

Example 1: Updating a Single Record

UPDATE Employees
SET salary = 60000
WHERE emp_id = 101;

This updates the salary of the employee with `emp_id` 101 to 60,000.

Example 2: Updating Multiple Records

UPDATE Students
SET grade = 'B'
WHERE grade = 'C';

This changes all students who have a 'C' grade to a 'B' grade.

Example 3: Updating with a Computed Value

UPDATE Employees
SET salary = salary * 1.10
WHERE emp_age > 50;
This gives a 10% raise to all employees over the age of 50.

WEEK3 PART2

Mapping Composite and Multi-Valued Attributes

Mapping Entities with Composite Attributes

When you have an entity with a composite attribute, you break it down into its simpler
components for easier storage in a relational database. Instead of storing the composite
attribute as a whole, each component becomes a separate column in the table.

Consider an entity `Student` with a composite attribute `FullName` which consists of

`FirstName`, `MiddleName`, and `LastName`.

- `Student` entity with `FullName` as a composite attribute.

- `FullName` → `FirstName`, `MiddleName`, `LastName`

CREATE TABLE Student (

StudentID INTEGER PRIMARY KEY,
FirstName VARCHAR(50),
MiddleName VARCHAR(50),
LastName VARCHAR(50)
);

In this table, `FullName` is represented by the `FirstName`, `MiddleName`, and `LastName`

columns.

Mapping Entities with Multivalued Attributes

For multivalued attributes, you create a separate table to handle the multiple values. This new
table will have a foreign key reference to the original entity's table.

Consider an entity `Employee` with a multivalued attribute `PhoneNumbers`.

- `Employee` entity with `PhoneNumbers` as a multivalued attribute.

1. Create the `Employee` table.

2. Create a separate `EmployeePhone` table to store the multiple phone numbers.

CREATE TABLE Employee (

EmployeeID INTEGER PRIMARY KEY,
 EmployeeName VARCHAR(100)
);

CREATE TABLE EmployeePhone (

EmployeeID INTEGER,
PhoneNumber VARCHAR(15),
PRIMARY KEY (EmployeeID, PhoneNumber),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

In this example, each employee can have multiple phone numbers, stored in the
`EmployeePhone` table. The composite primary key `(EmployeeID, PhoneNumber)` ensures
that each phone number is unique to each employee.

Mapping Weak Entities

A weak entity is an entity that cannot be uniquely identified by its own attributes alone and relies
on a "strong" or "owner" entity to provide context. The weak entity typically has a partial key (a
unique identifier within the context of its owner) and a foreign key referencing the owner entity.

Example 1: `Order` and `OrderItem`

- Consider an `Order` entity and a weak entity `OrderItem`. Each order can have multiple items,
but `OrderItem` alone cannot be uniquely identified without the `Order` it belongs to. The
combination of `OrderID` and `ItemID` can uniquely identify an `OrderItem`.

- `Order` (strong entity) with `OrderID` as the primary key.

- `OrderItem` (weak entity) with `ItemID` as a partial key.

CREATE TABLE Order (

OrderID INTEGER PRIMARY KEY,
OrderDate DATE
);

CREATE TABLE OrderItem (

OrderID INTEGER,
ItemID INTEGER,
ProductName VARCHAR(100),
Quantity INTEGER,
PRIMARY KEY (OrderID, ItemID),
FOREIGN KEY (OrderID) REFERENCES Order(OrderID)
);

In this example, `OrderItem` relies on `Order` for its identification, with `OrderID` and `ItemID`
together forming the composite primary key.
Example 2: `Building` and `Apartment`
- Consider a `Building` entity and a weak entity `Apartment`. An apartment is uniquely identified
only within the context of the building it belongs to. Therefore, the `BuildingID` and
`ApartmentNumber` together can uniquely identify an apartment.

- `Building` (strong entity) with `BuildingID` as the primary key.

- `Apartment` (weak entity) with `ApartmentNumber` as a partial key.

CREATE TABLE Building (

BuildingID INTEGER PRIMARY KEY,
BuildingName VARCHAR(100)
);

CREATE TABLE Apartment (

BuildingID INTEGER,
ApartmentNumber INTEGER,
Rent DECIMAL(10, 2),
PRIMARY KEY (BuildingID, ApartmentNumber),
FOREIGN KEY (BuildingID) REFERENCES Building(BuildingID)
);

Here, `Apartment` is a weak entity that depends on `Building` for its identification. The
combination of `BuildingID` and `ApartmentNumber` serves as the primary key.

Mapping Many-to-Many Relationships

In a many-to-many relationship, one record in a table can be associated with multiple records in
another table, and vice versa. This type of relationship is typically represented using a junction
table that holds the foreign keys referencing both entities.

`Student` and `Course`

- Scenario: A student can enroll in multiple courses, and each course can have multiple
students enrolled. This requires a junction table to model the many-to-many relationship.

ER Diagram:
- `Student` with `StudentID` as the primary key.
- `Course` with `CourseID` as the primary key.
- A junction table `Enrollment` holds `StudentID` and `CourseID` as foreign keys.

CREATE TABLE Student (

StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(100)
);

CREATE TABLE Course (

CourseID INTEGER PRIMARY KEY,
CourseName VARCHAR(100)
);

CREATE TABLE Enrollment (

StudentID INTEGER,
CourseID INTEGER,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Here, the `Enrollment` table acts as the junction table to link `Student` and `Course`, with a
composite primary key made up of `StudentID` and `CourseID`.

Example 2: `Author` and `Book`

- Scenario: An author can write multiple books, and each book can be co-authored by multiple
authors.

ER Diagram:
- `Author` with `AuthorID` as the primary key.
- `Book` with `BookID` as the primary key.
- A junction table `AuthorBook` holds `AuthorID` and `BookID` as foreign keys.

CREATE TABLE Author (

AuthorID INTEGER PRIMARY KEY,
AuthorName VARCHAR(100)
);

CREATE TABLE Book (

BookID INTEGER PRIMARY KEY,
Title VARCHAR(100)
);

CREATE TABLE AuthorBook (

AuthorID INTEGER,
BookID INTEGER,
PRIMARY KEY (AuthorID, BookID),
FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID),
 FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

The `AuthorBook` table is the junction table that models the many-to-many relationship between
authors and books.

Mapping Many-to-one Relationships

In a many-to-one relationship, multiple records in one table are associated with a single record
in another table. This is modeled by having a foreign key in the table representing the "many"
side, referencing the primary key of the table on the "one" side.

Example 1: `Employee` and `Department`

- Scenario: Many employees work in one department, but each department can have multiple
employees.

ER Diagram:
- `Department` with `DepartmentID` as the primary key.
- `Employee` with `EmployeeID` as the primary key and `DepartmentID` as a foreign key.

CREATE TABLE Department (

DepartmentID INTEGER PRIMARY KEY,
DepartmentName VARCHAR(100)
);

CREATE TABLE Employee (

EmployeeID INTEGER PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INTEGER,
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

Here, `Employee` has a foreign key `DepartmentID` that references the primary key of
`Department`, modeling the many-to-one relationship.

Example 2: `Order` and `Customer`

- Scenario: Many orders can be placed by a single customer, but each order is associated with
only one customer.

ER Diagram:
- `Customer` with `CustomerID` as the primary key.
- `Order` with `OrderID` as the primary key and `CustomerID` as a foreign key.
CREATE TABLE Customer (
CustomerID INTEGER PRIMARY KEY,
CustomerName VARCHAR(100)
);

CREATE TABLE Order (

OrderID INTEGER PRIMARY KEY,
OrderDate DATE,
CustomerID INTEGER,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

In this case, `Order` has a foreign key `CustomerID` that links it to `Customer`, forming a
many-to-one relationship.

Mapping Many-to-One Relationships with a Join Table

In some cases, even in a many-to-one relationship, you might use a join table to capture
additional details about the relationship or to enforce certain constraints. Below are two
examples where a join table is used in a many-to-one relationship.

1. Example 1: `Product`, `Category`, and `ProductCategory`

- A product belongs to a single category, but the relationship between `Product` and `Category`
needs to store additional information like the date when the product was added to the category.

- `Category` with `CategoryID` as the primary key.

- `Product` with `ProductID` as the primary key.
- A join table `ProductCategory` holds `ProductID`, `CategoryID`, and an additional attribute
`DateAdded`.

CREATE TABLE Category (

CategoryID INTEGER PRIMARY KEY,
CategoryName VARCHAR(100)
);

CREATE TABLE Product (

ProductID INTEGER PRIMARY KEY,
ProductName VARCHAR(100)
);

CREATE TABLE ProductCategory (

ProductID INTEGER,
 CategoryID INTEGER,
DateAdded DATE,
PRIMARY KEY (ProductID, CategoryID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID),
FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);

In this scenario, the `ProductCategory` table serves as a join table that not only links products to
their respective categories but also stores additional information (`DateAdded`). This setup is
useful when the relationship itself has attributes.

-- Retrieve all products with their categories and the date they were added to the category
SELECT p.ProductName, c.CategoryName, pc.DateAdded
FROM Product p
JOIN ProductCategory pc ON p.ProductID = pc.ProductID
JOIN Category c ON pc.CategoryID = c.CategoryID;

2. Example 2: `Employee`, `Office`, and `EmployeeOffice`

- Scenario: Each employee is assigned to a single office, but the relationship between
`Employee` and `Office` needs to capture the date the employee was assigned to the office.

ER Diagram:
- `Office` with `OfficeID` as the primary key.
- `Employee` with `EmployeeID` as the primary key.
- A join table `EmployeeOffice` holds `EmployeeID`, `OfficeID`, and an additional attribute
`AssignmentDate`.

CREATE TABLE Office (

OfficeID INTEGER PRIMARY KEY,
OfficeLocation VARCHAR(100)
);

CREATE TABLE Employee (

EmployeeID INTEGER PRIMARY KEY,
EmployeeName VARCHAR(100)
);

CREATE TABLE EmployeeOffice (

EmployeeID INTEGER,
OfficeID INTEGER,
AssignmentDate DATE,
PRIMARY KEY (EmployeeID, OfficeID),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
 FOREIGN KEY (OfficeID) REFERENCES Office(OfficeID)
);

In this case, the `EmployeeOffice` table is used as a join table to record not only which office an
employee is assigned to but also the date of that assignment (`AssignmentDate`).

-- Retrieve all employees with their assigned offices and the assignment dates

SELECT e.EmployeeName, o.OfficeLocation, eo.AssignmentDate

FROM Employee e
JOIN EmployeeOffice eo ON e.EmployeeID = eo.EmployeeID
JOIN Office o ON eo.OfficeID = o.OfficeID;

1. Join Table Usage: The join table (`ProductCategory` or `EmployeeOffice`) is used to link the
`Product` or `Employee` to a specific `Category` or `Office`. This is especially useful when the
relationship has attributes like `DateAdded` or `AssignmentDate`.

2. Many-to-One Relationship: The relationship is still many-to-one because each `Product` or

`Employee` can belong to only one `Category` or `Office`, respectively, but the relationship's
details (such as the date of assignment) require additional attributes that the join table can
accommodate.

3. Why Use a Join Table: The join table allows for storing extra information about the
relationship itself, which would be difficult or messy to store in either the `Product`/`Employee`
or `Category`/`Office` tables directly.

ISA Hierarchical Mapping

The `ISA` relationship in an Entity-Relationship (ER) model represents an inheritance or
hierarchical relationship where a subclass entity inherits attributes and relationships from a
parent entity. In relational databases, this can be mapped in a few different ways.

Here are two common approaches to mapping `ISA` hierarchical relationships in SQL, with
examples and explanations:

Example 1: Single Table Inheritance

We have a `Person` entity and two subclasses, `Student` and `Teacher`. Both `Student` and
`Teacher` inherit attributes from `Person`.

- `Person` (Base Entity)

- Attributes: `person_id`, `name`, `dob`
- `Student` (Sub-Class)
- Additional Attribute: `major`
- `Teacher` (Sub-Class)
- Additional Attribute: `department`

All entities are stored in a single table with a discriminator column to differentiate between the
subclasses.

CREATE TABLE Person (

person_id INT PRIMARY KEY,
name VARCHAR(100),
dob DATE,
person_type VARCHAR(10), -- Discriminator column
major VARCHAR(100), -- Nullable, for Students
department VARCHAR(100) -- Nullable, for Teachers
);

-- Insert data into the table

INSERT INTO Person (person_id, name, dob, person_type, major, department)
VALUES (1, 'Alice', '2000-01-01', 'Student', 'Computer Science', NULL),
(2, 'Bob', '1985-05-23', 'Teacher', NULL, 'Mathematics');

- The `Person` table includes columns for both common attributes and those specific to
`Student` or `Teacher`.
- The `person_type` column indicates the subclass, helping distinguish between `Student` and
`Teacher`.
- This approach is simple but can result in many `NULL` values and may become inefficient if
there are many attributes or subclasses.

Example 2: Class Table Inheritance

The same scenario as above, but using separate tables for each subclass.

- `Person` (Base Entity)

- Attributes: `person_id`, `name`, `dob`
- `Student` (Sub-Class)
- Attributes: `person_id`, `major`
- `Teacher` (Sub-Class)
- Attributes: `person_id`, `department`

1. Base Table:

CREATE TABLE Person (

person_id INT PRIMARY KEY,
name VARCHAR(100),
dob DATE
);

2. Subclass Tables:

CREATE TABLE Student (

person_id INT PRIMARY KEY,
major VARCHAR(100),
FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

CREATE TABLE Teacher (

person_id INT PRIMARY KEY,
department VARCHAR(100),
FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

3. Insert Data:

-- Insert into Person table

INSERT INTO Person (person_id, name, dob) VALUES (1, 'Alice', '2000-01-01');
INSERT INTO Person (person_id, name, dob) VALUES (2, 'Bob', '1985-05-23');

-- Insert into Student table

INSERT INTO Student (person_id, major) VALUES (1, 'Computer Science');

-- Insert into Teacher table

INSERT INTO Teacher (person_id, department) VALUES (2, 'Mathematics');

Explanation:
- The `Person` table contains only the common attributes.
- `Student` and `Teacher` tables each have their specific attributes and a foreign key reference
to the `Person` table.
- This approach avoids `NULL` values and normalizes the database structure by keeping
subclass-specific attributes in separate tables.

Summary
- Single Table Inheritance: All attributes are in one table, using a discriminator column to identify
the subclass. Simple but can lead to inefficiencies and many `NULL` values.
- Class Table Inheritance: Common attributes are stored in a base table, while subclass-specific
attributes are stored in separate tables. This is more normalized and avoids `NULL` values but
requires joining tables to retrieve complete data.
These methods provide different trade-offs between simplicity and normalization, and the choice
often depends on the specific requirements and complexity of the database design.