Please guide me to design the table for the following scenario.
The scenario is, the whole building can be one department, some of the floors in the building can be a department or room can be a department. For e.g. Department “SOFTWARE” can take the whole building or “SOFTWARE” department can be in 1st floor,2nd floor of a building or SOFTWARE can be in a room
BUILDING1>> SOFTWARE
BULDING2>> FLOOR 1>> SOFTWARE
BULDING2>> FLOOR 2>> SOFTWARE
BULDING2>> FLOOR 3>>ROOM1>> SOFTWARE
Please guide me in achieving these scenarios.
Script
CREATE TABLE “BUILDING"
( "BUILDING_ID" NUMBER,
"BUILDING_NAME" VARCHAR2(100 BYTE)
);
CREATE TABLE “FLOOR"
( "FLOOR_ID" NUMBER,
"FLOOR_NAME" VARCHAR2(100 BYTE),
"BUILDING_ID" NUMBER
);
CREATE TABLE "ROOM"
( "ROOM_ID" NUMBER,
"ROOM_NAME" VARCHAR2(100 BYTE),
"FLOOR_ID" NUMBER
);
INSERT INTO BUILDING VALUES(1,'First Building');
INSERT INTO BUILDING VALUES(2,'Second Building');
INSERT INTO FLOOR VALUES(1,'1st Floor',1);
INSERT INTO FLOOR VALUES(2,'2nd Floor',1);
INSERT INTO FLOOR VALUES(3,'3rd Floor',1);
INSERT INTO ROOM VALUES(1,'ROOM1',1);
INSERT INTO ROOM VALUES(2,'ROOM2',1);
INSERT INTO ROOM VALUES(3,'ROOM3',2);
COMMIT;