How Do We Implement an Is-A Relationship

How do we implement an IS-A Relationship?

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:

ERD

In this model there are no Employees of Generic Type. Here, an Employee can only be Administrative or Technical.

How to implement the IS-A relationship in Hibernate Spring Boot?

@Entity
public class Account {

@Id
private String id;

@OneToOne
private User user;
}

create the super class

@MappedSuperclass
public abstract class User {

@Id
private String id;

}

And extend it with your entities.

@Entity
public class Doctor extends User {

private String name;

private String email;

}

you need to create an (Absctract) class User and extend with
Patient and Doctor as well.You only need to define the id field on the base entity.
You also have to provide getters and setters to all classes. I suggest using the lombok annotations @Getter and @Setter

Or you can map them one by one to the Account entity.

Hit me up if something isn't clear!

Entity Relationship Diagram. How does the IS A relationship translate into tables?

Assuming the relationship is mandatory (as you said, a person has to be a student or a teacher) and disjoint (a person is either a student or a teacher, but not both), the best solution is with 2 tables, one for students and one for teachers.

If the participation is instead optional (which is not your case, but let's put it for completeness), then the 3 tables option is the way to go, with a Person(PersonID, Name) table and then the two other tables which will reference the Person table, e.g.
Student(PersonID, GPA), with PersonID being PK and FK referencing Person(PersonID).

The 1 table option is probably not the best way here, and it will produce several records with null values (if a person is a student, the teacher-only attributes will be null and vice-versa).

If the disjointness is different, then it's a different story.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
# "link back" if you need

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes: class_id, class_name, teacher_id # the "many" side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id # the junction table

Example queries:

 -- Getting all students for a class:

SELECT s.student_id, last_name
FROM student_classes sc
INNER JOIN students s ON s.student_id = sc.student_id
WHERE sc.class_id = X

-- Getting all classes for a student:

SELECT c.class_id, name
FROM student_classes sc
INNER JOIN classes c ON c.class_id = sc.class_id
WHERE sc.student_id = Y



Related Topics



Leave a reply



Submit