Question 197896 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Transforming a class diagram to a SQL database

I have this part of a class diagram that I want to translate into SQL:

Piece of class diagram:

My internship supervisor sent me this SQL code:


CREATE TABLE `users` (
  `Matricule` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `Role` enum('ResponsableProduction','ResponsableChaineProduction','ResponsableMaintenance','AgentMaintenance','Administrateur','Magasinier') NOT NULL
);

CREATE TABLE `responsable_production` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL
);

CREATE TABLE `responsable_maintenance` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `RefChaine` varchar(50) NOT NULL
);

CREATE TABLE `responsable_chaine_production` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `RefChaine` varchar(50) NOT NULL
);

CREATE TABLE `magasinier` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL
);

CREATE TABLE `agent_maintenance` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `Specialite` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  `Sexe` enum('Homme','Femme','','') NOT NULL,
  `NiveauEducation` varchar(50) NOT NULL,
  `ExperienceProfessionnelle` int(11) NOT NULL
);

-- Triggers `users`
--
DELIMITER $$
CREATE TRIGGER `User_Insert` AFTER INSERT ON `users` FOR EACH ROW BEGIN
    IF (NEW.Role = "ResponsableMaintenance") THEN
        insert into responsable_maintenance(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "ResponsableChaineProduction") THEN
        insert into responsable_chaine_production(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "AgentMaintenance") THEN
        insert into agent_maintenance(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "ResponsableProduction") THEN
        insert into responsable_production(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "Magasinier") THEN
        insert into magasinier(matricule) VALUES (NEW.matricule);
    End IF;
END
$$
DELIMITER ;

But it didn’t seem quite right to me. Because, for example if I’m adding a new user, first I have to add it to the user table, then add his remaining details in one of the other table depending on his role. That seemed a lot of trouble to me, and inconsistency problems might arise later on.
Is this SQL script consistent with the Class Diagram? (after adding the needed PK and FK constraints into it ofc).
And if yes, is there a simpler/safer database design than the one my supervisor sent me?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Apparently the SQL code I provided in my post is not right as I suspected.

    The problem is that it doesn't manifest the ability of one user to have one or multiple roles (relationship described by the class diagram).

    The solution should be creating:

    • User table that hold all the attributes from the different roles.
    • Role table that contains all possible roles("responsable production", "magasinier", "administrateur", ...)
    • Associative entity/table between User table and Role table.

  2. 0

    The challenge in implementing your UML diagram into SQL tables, is the inheritance. Inheritance is not a concept in SQL, and several techniques exist to map such an UML model to a relational model:

    • single table inheritance: you’d have one table for the role that would would contain (nullable) columns for all possible attribute in any roles, as well as a code that helps to know which kind of role the row is about:

      CREATE TABLE `role` (
  `IdRole` varchar(10) NOT NULL,
  `NomRole` varchar(50) NOT NULL,
  `TypeRole` enum ('ResponsableProduction','ResponsableChaineProduction', 'ResponsableMaintenance','AgentMaintenance','Administrateur','Magasinier') NOT NULL
  `Specialite` varchar(50)     -- Used only if maintenance agent, NULL otherwise
);

    • class table inheritance: you’d have a table for each class in the inheritance hierarchy, and relationships of each class with its parent/child classes. The can be very cumbersome if many classes are empty or have not many differences.

    • concerete table inheritance: you’d have a table for each concrete class but none for abstract parents. Each table has all the necessary rows: the ones inherited, and its own.

    Your supervisor seems to play with you: he/she used concrete table inheritance, because you have all the role specialisations and not the abstract role. Unfortunately, it uses rows which are related to user, which is dramatically false. Moreover with the multiple roles possible, it would mean to duplicate information if a user has several roles.

    Once you have sorted out, what really belongs to the role tables, and what belongs to the user table, you ara ready to create the n to n mapping table, between users and roles.

    Login or Signup to reply.
  3. 0

    It is a bit difficult to understand the complete environment of your questioning, however, I can tell you that the trigger that is being created, which is responsible for creating a record in the corresponding table, according to the role of the new user, is not entirely correct because It only inserts the "Matricule" field but there are more "not null" fields that, by not assigning data to it, would most likely generate an error.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search