For each strong entity set in the ER diagram, create a separate relation (table) in the relational schema.
Include all simple attributes of the entity set as columns in the table.
For composite attributes, include only their simple component attributes as separate columns (e.g., Address composite attribute with Street, City, State components would become Street, City, State columns).
The key attribute(s) of the strong entity set become the primary key of the new table. Choose one candidate key as the primary key.
Example: STUDENT(StudentID, FirstName, LastName, DateOfBirth, Street, City, State, ZipCode).

For each weak entity set, create a separate relation (table).
Include all simple attributes of the weak entity set as columns.
Include the primary key of its identifying (owner) strong entity set as a foreign key in the weak entity's table. This foreign key will typically also be part of the weak entity's primary key.
The partial key (discriminator) of the weak entity set, combined with the primary key of the identifying strong entity, forms the primary key of the weak entity's table.
Example: DEPENDENT(EmployeeID, DependentName, Relationship, DateOfBirth) where EmployeeID is a foreign key referencing the EMPLOYEE table.

The approach depends on the cardinality ratio:
1:1 (One-to-One) Relationships:
Option A (Merging): The most common approach. Choose one of the participating entity sets' tables (often the one with total participation, or the one that's less frequently queried alone) and include the primary key of the other entity set as a foreign key in it. Any attributes of the relationship itself also become columns in this table.
Example: EMPLOYEE manages DEPARTMENT (1:1). If each department has exactly one manager and each manager manages at most one department, you could add EmployeeID (FK) to DEPARTMENT table or DepartmentID (FK) to EMPLOYEE table.
Option B (Separate Table): Create a new table for the relationship. Its primary key would be a composite of the primary keys of both participating entity sets (which also serve as foreign keys). This is less common for 1:1 but applicable if the relationship itself has many attributes or participation is partial on both sides.

1:N (One-to-Many) Relationships:
This is typically straightforward. The primary key of the entity set on the 'one' side is included as a foreign key in the table corresponding to the entity set on the 'many' side.
Any attributes of the relationship itself also become columns in the table on the 'many' side.
Example: DEPARTMENT employs EMPLOYEE (1:N from DEPARTMENT to EMPLOYEE). Add DepartmentID (FK) to the EMPLOYEE table.
EMPLOYEE(EmployeeID, Name, Salary, DepartmentID)
DEPARTMENT(DepartmentID, Name, Location)

M:N (Many-to-Many) Relationships:
Always create a new relation (table) for the relationship. This new table is often called a 'bridge table' or 'junction table.'
The primary key of this new table is typically a composite key formed by the primary keys of all participating entity sets (these also become foreign keys in the new table).
Any attributes of the relationship itself become additional columns in this new table.
Example: STUDENT enrolls in COURSE (M:N). Create ENROLLS_IN table.
ENROLLS_IN(StudentID, CourseID, Grade, EnrollmentDate)
StudentID is a foreign key referencing STUDENT table.
CourseID is a foreign key referencing COURSE table.

For each multi-valued attribute, create a new separate relation (table).
This new table will have two columns:
The primary key of the entity set to which the multi-valued attribute belongs (this column will also be a foreign key).
The multi-valued attribute itself.
The primary key of this new table will be the composite of these two columns.
Example: EMPLOYEE has multi-valued attribute PhoneNumber.
EMPLOYEE_PHONE(EmployeeID, PhoneNumber)
EmployeeID is a foreign key referencing EMPLOYEE table.

For any relationship involving three or more entity sets, always create a new relation (table) for the relationship.
The new table's primary key is typically a composite key formed by the primary keys of all participating entity sets (these also serve as foreign keys).
Any attributes of the relationship itself become additional columns in this new table.
Example: SUPPLIES relationship among SUPPLIER, PART, and PROJECT (ternary).
SUPPLIES(SupplierID, PartID, ProjectID, Quantity, Date)
SupplierID, PartID, ProjectID are foreign keys referencing their respective tables.

There are several common strategies for mapping superclass/subclass hierarchies, each with pros and cons regarding storage efficiency, query complexity, and handling of null values:
Option 1: Multiple Tables (Superclass and Subclasses):
Create a table for the superclass (e.g., EMPLOYEE) containing all common attributes and its primary key.
Create a separate table for each subclass (e.g., SECRETARY, ENGINEER). Each subclass table will contain:
Its specific (non-inherited) attributes.
The primary key of the superclass. This superclass primary key will serve as both the primary key and a foreign key in the subclass table, referencing the superclass table.
Pros: No redundant data from specific subclass attributes; handles total/partial and disjoint/overlapping naturally.
Cons: Retrieving data for a complete entity (superclass + specific subclass attributes) requires joins, which can be slower.
Example:
EMPLOYEE(EmployeeID, Name, Salary, HireDate)
SECRETARY(EmployeeID, TypingSpeed, FK referencing EMPLOYEE)
ENGINEER(EmployeeID, EngineeringDiscipline, FK referencing EMPLOYEE)

Option 2: Single Table (All Classes):
Create a single table for the entire hierarchy. This table includes all attributes from the superclass and all attributes from all subclasses.
Include a "type" or "discriminator" column to indicate which subclass an entity belongs to (e.g., EmployeeType could be 'Secretary', 'Engineer', 'Manager').
Pros: No joins required to retrieve full entity data, simpler queries.
Cons: Significant NULL value waste for attributes that don't apply to a particular subclass; can be hard to enforce constraints specific to a subclass.
Example:
PERSON(PersonID, Name, DOB, SocialSecurityNum, IsStudent, StudentMajor, IsEmployee, EmployeeSalary, EmployeeType) (where StudentMajor is NULL if IsStudent is false, EmployeeSalary is NULL if IsEmployee is false, etc.)

Option 3: Multiple Tables (Subclasses Only - No Superclass Table):
Create a separate table for each subclass. Each subclass table includes all its specific attributes and all the inherited attributes from the superclass (including the superclass's primary key, which becomes the primary key of the subclass table).
Pros: No joins needed for subclass-specific queries.
Cons: Significant data redundancy if common superclass attributes are repeated in many subclass tables; challenging to query all entities in the superclass (e.g., all EMPLOYEEs) without UNION operations across all subclass tables. Less suitable for partial completeness constraints.
Example:
SECRETARY(EmployeeID, Name, Salary, HireDate, TypingSpeed)
ENGINEER(EmployeeID, Name, Salary, HireDate, EngineeringDiscipline)

The choice among these options depends on the specific characteristics of the hierarchy (disjoint/overlapping, total/partial), query patterns, and trade-offs between storage, performance, and complexity.

When an aggregation is present, the aggregated relationship (which is treated as an abstract entity) is mapped similar to an N-ary relationship.
Create a new table for the relationship that participates in the aggregation.
This table will include the primary keys of all entities involved in the aggregated relationship, plus any attributes of that relationship.
Then, treat this new table's primary key (or a subset of it if appropriate) as a single composite identifier when relating it to the table of the entity that participates in the aggregated relationship.
This is often implemented by adding the primary key of the aggregated relation's table as a foreign key to the table representing the entity participating in the aggregation.

Generally, derived attributes are not stored as separate columns in the relational schema. Instead, they are calculated (derived) on-the-fly through queries (e.g., calculating Age from DateOfBirth using a SQL function).
Exception: Derived attributes might be stored if their computation is very complex or resource-intensive, or if historical values are needed that cannot be easily re-derived. In such cases, they become regular columns, but their values must be explicitly maintained (e.g., updated periodically).