Tables.sql

CREATE TABLE USERS(
AADHAAR_ID NUMBER(12) PRIMARY KEY,
NAME CHAR(10) NOT NULL,
AGE NUMBER(3),
DOOR_NO NUMBER,
STREET CHAR(25),
CITY CHAR(15),
STATE CHAR(5),
PINCODE NUMBER,
O_FLAG NUMBER(1) CHECK(O_FLAG = 0 OR O_FLAG = 1),
T_FLAG NUMBER(1) CHECK(T_FLAG = 0 OR T_FLAG = 1),
M_FLAG NUMBER(1) CHECK(M_FLAG = 0 OR M_FLAG = 1),
SU_FLAG NUMBER(1) CHECK(SU_FLAG = 0 OR SU_FLAG = 1));


CREATE TABLE USER_PHONE(
AID NUMBER(12),
PHONE_NO NUMBER(10),
PRIMARY KEY(AID, PHONE_NO));


ALTER TABLE USER_PHONE ADD CONSTRAINT FK1 FOREIGN KEY(AID) REFERENCES USERS(AADHAAR_ID);


CREATE TABLE LOGIN(
AID NUMBER(12),
USER_NAME CHAR(10),
PASSWORD CHAR(6) NOT NULL);


ALTER TABLE LOGIN ADD CONSTRAINT FK2 FOREIGN KEY(AID) REFERENCES USERS(AADHAAR_ID);


CREATE TABLE PROPERTY(
PROPERTY_ID NUMBER(10) PRIMARY KEY,
TOTAL_FLOORS NUMBER(3),
TOTAL_AREA NUMBER(4) NOT NULL,
PLINTH_AREA NUMBER(4) NOT NULL,
CONST_YEAR NUMBER(4) NOT NULL,
DOOR_NO NUMBER,
STREET CHAR(25),
CITY CHAR(15),
STATE CHAR(5),
PINCODE NUMBER,
HIKE NUMBER(5),
RENT NUMBER,
PROP_TYPE CHAR(3),
TOTAL_BED NUMBER);


CREATE TABLE PROP_OWNED(
AID NUMBER(12),
PID NUMBER(10),
PRIMARY KEY(AID, PID));


ALTER TABLE PROP_OWNED ADD CONSTRAINT FK3 FOREIGN KEY(AID) REFERENCES USERS(AADHAAR_ID);
ALTER TABLE PROP_OWNED ADD CONSTRAINT FK4 FOREIGN KEY(PID) REFERENCES PROPERTY(PROPERTY_ID);


CREATE TABLE PROP_RENTED(
AID NUMBER(12),
PID NUMBER(10),
START_DATE DATE,
END_DATE DATE,
COMM NUMBER(2),
PRIMARY KEY(AID, PID, START_DATE));


ALTER TABLE PROP_RENTED ADD CONSTRAINT FK5 FOREIGN KEY(AID) REFERENCES USERS(AADHAAR_ID);
ALTER TABLE PROP_RENTED ADD CONSTRAINT FK6 FOREIGN KEY(PID) REFERENCES PROPERTY(PROPERTY_ID);