Menu
I have my table with these 3 entities: student, lecturer and person. Person will be the parent that holds common attributes of student and lecturer. This is my database design, is this a correct way of implementing specialization?
personID int Primary key,
Role varchar(10) ,
Name varchar(50),
email varchar(255)
studentID int,
intake varchar(50),
major varchar( 50) ,
personID int reference person(personID)
LecturerID int ,
Skill varchar(50),
Qualification varchar(50) ,
personID int reference person(personID)
The above of the table, is this the correct way to implement generalization/specialization ? If it is, How do I insert a new value to the database when a new student enrolls?
527
Generalization is the process of extracting common properties from a set of entities and create a generalized entity from it. It is a bottom-up approach in which two or more entities can be generalized to a higher level entity if they have some attributes in common.
Number: Generalization involves multiple entities and combines them into a generalized entity. Specialization involves a single entity broken down into multiple sub-entities. Size: Generalization reduces the schema of the data by unifying components. Specialization expands the schema by multiplying the components.
A diamond notation is a common representation of specialization/generalization relationships in ER diagrams.
Hence, we have four types of specialization/generalization: Disjoint, total. Disjoint, partial. Overlapping, total.
You generally have the right idea (or, at least, one of the common idioms to implement such a database schema). If your rdbms supports it, I'd use an enum for the role column (MySQL, e.g., does. MS SQL Server does not). If it doesn't I'd add a check constraint to achieve a similar effect and make sure you don't get any junk inserted there.
Insertion should be done to the base table (person) first and only then to the specialization tables (student or lecturer). E.g.:
INSERT INTO person
VALUES (1, 'student', 'hiboss', '[email protected]');
INSERT INTO student
VALUES (100, 'intake', 'computer science', 1);
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
