We implement an One-to-Many relationship by adding one Table's PK, as FK to the other Table. We implement a Many-to-Many relationship by adding 2 Table's PKs to a third Table.
How do we implement an IS-A Relationship ?
The Entities are TECHNICIAN and ADMINISTRATIVE which both are EMPLOYEE. I could just use an extra field in the Table EMPLOYEE(id, name, surname, role, ...AdminFields..., ...TechFields...)
but i would like to explore the IS-A option.
EDIT: I did as Donnie suggested, but without the role field.
Most ORMs implement the IS-A relationship using a single column discriminator, choosing which subclass to instantiate based on the value in a particular column. With respect to your example, you probably don't really mean role, since typically a person can fill many different types of roles.
A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items.
In database design, object-oriented programming and design (see object oriented program architecture), has-a (has_a or has a) is a composition relationship where one object (often called the constituted object, or part/constituent/member object) "belongs to" (is part or member of) another object (called the composite ...
To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. We have developed a relationship between the Teachers and the Courses table using a foreign key.
I did as Donnie suggested, but without the role field, because it complicates things. This is the final implementation:
DDL:
CREATE TABLE Employee ( ast VARCHAR(20) not null, firstname VARCHAR(200) not null, surname VARCHAR(200) not null, ... PRIMARY KEY(ast) ); CREATE TABLE Administrative ( employee_ast VARCHAR(20) not null REFERENCES Employee(ast), PRIMARY KEY(employee_ast) ); CREATE TABLE Technical ( employee_ast VARCHAR(20) not null REFERENCES Employee(ast), ... PRIMARY KEY(employee_ast) );
ER Diagram:
In this model there are no Employees of Generic Type. Here, an Employee can only be Administrative or Technical.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With