DROP TABLE EMPLOYEE;

--base table with fixed columns
CREATE TABLE EMPLOYEE
(EMPLOYEE_ID NUMBER,
 NAME        VARCHAR2(100),
 DEPTNO      NUMBER,
 SAL         NUMBER
 );
 
 insert into EMPLOYEE VALUES(10,'BLAKE',500,1000);
 insert into EMPLOYEE VALUES(20,'KING',500,2000);
 insert into EMPLOYEE VALUES(30,'ROGER',300,3000);
 
 commit;
 
 select * from EMPLOYEE;

DROP TABLE TABS_METADATA;

--metadata table which as definition to additional columns
CREATE TABLE TABS_METADATA
 (
  TABS_METADATA_ID            NUMBER(18,0) CONSTRAINT PK_TABS_METADATA PRIMARY KEY,
  OWNER                       NVARCHAR2(30),
  TABLE_NAME                  NVARCHAR2(30),
  COLUMN_NAME                 NVARCHAR2(30),
  COLUMN_LABEL                NVARCHAR2(30),
  DATA_TYPE                   NVARCHAR2(106),
  DATA_LENGTH                 NUMBER(18,0),
  DATA_PRECISION              NUMBER(18,0),
  NULLABLE                    NVARCHAR2(1),
  DATA_DEFAULT                NVARCHAR2(100),
  COLUMN_ORDER                NUMBER(18,0)
 );
 
insert into TABS_METADATA VALUES(123,'SCOTT','EMPLOYEE','ADDRESS1','Address1','VARCHAR2',1000,NULL,'Y',NULL,1);
insert into TABS_METADATA VALUES(456,'SCOTT','EMPLOYEE','ADDRESS2','Address2','VARCHAR2',1000,NULL,'Y',NULL,2);

commit;

SELECT * FROM TABS_METADATA;

DROP TABLE TABS_ATTR_DATA;

-- this table has key valaue pair
-- TABS_ATTR_DATA_ID => primarky key
-- UUID => in this example EMPLOYEE_ID from EMPLOYEE table
-- ATTRIBUTE_VALUE => actual value for the attribute
CREATE TABLE TABS_ATTR_DATA
(TABS_ATTR_DATA_ID  NUMBER CONSTRAINT PK_TABS_ATTR_DATA PRIMARY KEY,
 TABS_METADATA_ID   NUMBER,
 UUID               NUMBER,
 DATA_TYPE          VARCHAR2(10),
 ATTRIBUTE_VALUE    VARCHAR(255),
 CONSTRAINT FK_TABS_ATTR_DATA FOREIGN KEY (TABS_METADATA_ID)REFERENCES TABS_METADATA(TABS_METADATA_ID)
 );
 
insert into TABS_ATTR_DATA  values (1,123,20,'VARCHAR2','999 Abc Ave');
insert into TABS_ATTR_DATA  values (2,456,20,'VARCHAR2','CA, 12345');

commit;

select * from TABS_ATTR_DATA;

-- expected output from view
EMPLOYEE_ID NAME  DEPTNO  SAL ADDRESS1  ADDRESS2

10  BLAKE 500 1000  NULL  NULL
20  KING  500 2000  999 Abc Ave  CA, 12345
30  ROGER 300 3000  NULL  NULL