diagram account role and permission

diagram account role and permission.sql

CREATE DATABASE DB_MY_CONTACTS;
USE DB_MY_CONTACTS;

/*------ACCOUNTS------*/
CREATE TABLE ACCOUNTS (
	ACCOUNT_ID INT,
	FIRST_NAME VARCHAR(50) NOT NULL,
    LAST_NAME VARCHAR(50) NOT NULL,
    PROFILE_PICTURE VARCHAR(255) NULL,
    EMAIL VARCHAR(50) NULL,
    PHONE_NUMBER VARCHAR(25) NULL,
    ADRRESS_LINE_1 VARCHAR(100) NOT NULL,
	DATE_OF_BIRH DATE NOT NULL,
    GENDER ENUM('M','F'),
    ENABLED TINYINT NOT NULL,
    DATE_CREATED DATETIME NOT NULL,
    DATE_UPDATE DATETIME NOT NULL,
	IS_SUPER_ADMIN TINYINT NOT NULL
);

ALTER TABLE ACCOUNTS
	ADD CONSTRAINT PK_ACCOUNT
    PRIMARY KEY (ACCOUNT_ID);


ALTER TABLE ACCOUNTS
	MODIFY ACCOUNT_ID INT AUTO_INCREMENT;


/*--------ROLES--------*/

CREATE TABLE ROLES (
	ROLE_ID INT,
    NAME VARCHAR(50) NOT NULL
);

ALTER TABLE ROLES
	ADD CONSTRAINT PK_ROLES
    PRIMARY KEY (ROLE_ID);

ALTER TABLE ROLES
	MODIFY ROLE_ID INT AUTO_INCREMENT;

/*-------ACCOUNTS-ROLE-----*/

CREATE TABLE ACCOUNTS_ROLES (
	ACCOUNT_ROLE_ID INT,
    ACCOUNT_ID INT,
    ROLE_ID INT
);


ALTER TABLE ACCOUNTS_ROLES 
	MODIFY ACCOUNT_ROLE_ID INT AUTO_INCREMENT PRIMARY KEY;
    
ALTER TABLE ACCOUNTS_ROLES 
	ADD CONSTRAINT FK_ROLE_ID
    FOREIGN KEY (ROLE_ID)
    REFERENCES ROLES(ROLE_ID);
    
ALTER TABLE ACCOUNTS_ROLES 
	ADD CONSTRAINT FK_ACCOUNT_ID
    FOREIGN KEY (ACCOUNT_ID)
    REFERENCES ACCOUNTS(ACCOUNT_ID);
    	
/*--------PERMISSION--------*/

CREATE TABLE PERMISSION (
	PERMISSION_ID INT,
    NAME VARCHAR(50) NOT NULL
);

ALTER TABLE PERMISSION
	ADD CONSTRAINT PK_PERMISSION_ID
    PRIMARY KEY(PERMISSION_ID);
    
ALTER TABLE PERMISSION 
	MODIFY PERMISSION_ID INT AUTO_INCREMENT;


/*--------PERMISION--------*/

CREATE TABLE ROLE_PERMISSION (
	ROLE_PERMISSION_ID INT,
    ROLE_ID INT,
    PERMISSION_ID INT
);

ALTER TABLE ROLE_PERMISSION
	ADD CONSTRAINT PK_ROLE_PERMISSION
    PRIMARY KEY(ROLE_PERMISSION_ID);

ALTER TABLE ROLE_PERMISSION
	MODIFY ROLE_PERMISSION_ID  INT AUTO_INCREMENT;

ALTER TABLE ROLE_PERMISSION
	ADD CONSTRAINT FK_ROLE_PERMISSION_ROLE_ID
    FOREIGN KEY (ROLE_ID)
    REFERENCES ROLES(ROLE_ID);
    
ALTER TABLE ROLE_PERMISSION
	ADD CONSTRAINT FK__ROLE_PERMISSION_PERMISSION_ID
    FOREIGN KEY (PERMISSION_ID)
    REFERENCES PERMISSION(PERMISSION_ID);