Create the Tables for the following descriptions using the constraints as specified.
COLUMN NAME |
CONSTRAINT DESCRIPTION |
CUST_NO |
-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START
WITH LETTER 'C' |
NAME |
NOT NULL |
PHONE_NO |
|
CITY |
NOT NULL |
TABLE NAME-- BRANCH
COLUMN NAME | CONSTRAINT DESCRIPTION |
BRANCH_CODE | -PRIMARY KEY |
BRANCH_NAME | NOT NULL |
BRANCH_CITY | -ANY METRO CITY OF INDIA – DELHI, MUMBAI, KOLKATA, CHENNAI. |
CREATE TABLE BRANCH
(BRANCH_CODE VARCHAR(10) CONSTRAINT PKBC PRIMARY KEY,
BRANCH_NAME VARCHAR(20) NOT NULL,
BRANCH_CITY VARCHAR(10),
CONSTRAINT CHBC CHECK(BRANCH_CITY IN('DELHI','MUMBAI','KOLKATA','CHENNAI')));
TABLE NAME-- ACCOUNT
COLUMN NAME |
CONSTRAINT DESCRIPTION |
ACCOUNT_NO |
-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START
WITH LETTER 'A' |
TYPE |
-TYPES- 'SB', 'FD',
'CA' |
BALANCE |
-MUST BE <
10000000 |
BRANCH_CODE |
-FOREIGN KEY, REFERS TABLE
BRANCH |
CREATE TABLE ACCOUNT
(ACCOUNT_NO VARCHAR(5) CONSTRAINT PKAN PRIMARY KEY,
TYPE VARCHAR(2),
CONSTRAINT CHTY CHECK(TYPE IN('SB','FD','CA')),
BALANCE NUMBER(20) CONSTRAINT CHBA CHECK(BALANCE<100000000),
BRANCH_CODE VARCHAR(10),
CONSTRAINT CHAN CHECK(ACCOUNT_NO LIKE 'A____'),
CONSTRAINT FKBC FOREIGN KEY(BRANCH_CODE) REFERENCES BRANCH(BRANCH_CODE));
TABLE NAME-- DEPOSTOR
COLUMN NAME |
CONSTRAINT DESCRIPTION |
CUST_NO |
-FOREIGN KEY, REFERS TABLE CUSTOMER -PRIMARY KEY |
ACCOUNT_NO |
-FOREIGN KEY, REFERS TABLE
ACCOUNT -PRIMARY KEY |
CREATE TABLE DEPOSTOR
(CUST_NO VARCHAR(5) CONSTRAINT FKCN REFERENCES CUSTOMER(CUST_NO),
ACCOUNT_NO VARCHAR(5) CONSTRAINT FKAN REFERENCES ACCOUNT(ACCOUNT_NO),
CONSTRAINT PKCA PRIMARY KEY (CUST_NO,ACCOUNT_NO));
TABLE NAME-- LOAN
COLUMN NAME |
CONSTRAINT DESCRIPTION |
LOAN-NO |
-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START
WITH LETTER 'L' |
CUST_NO |
-FOREIGN KEY, REFERS TABLE CUSTOMER |
AMOUNT |
-MUST BE > 1000 |
BRANCH_CODE |
-FOREIGN KEY, REFERS TABLE
BRANCH |
CREATE TABLE LOAN
(LOAN_NO VARCHAR(5) CONSTRAINT PKLN PRIMARY KEY,
CUST_NO VARCHAR(5) CONSTRAINT FKCN REFERENCES CUSTOMER(CUST_NO),
AMOUNT NUMBER(20) CONSTRAINT CKAM CHECK(AMOUNT>1000),
BRANCH_CODE VARCHAR(10) CONSTRAINT FKBC REFERENCES BRANCH(BRANCH_CODE),
CONSTRAINT CKLN CHECK(LOAN_NO LIKE 'L____'));
TABLE INSTALLMENT
COLUMN NAME |
CONSTRAINT DESCRIPTION |
INST_NO |
-PRIMARY KEY -MUST BE<=10 |
LOAN_NO |
-PRIMARY KEY -FOREIGN KEY, REFERS TABLE LOAN |
INST_AMOUNT |
-NOT NULL |
CREATE TABLE INSTALLMENT
(INST_NO NUMBER(10) CONSTRAINT CHIN CHECK(INST_NO<=10),
LOAN_NO VARCHAR(10) CONSTRAINT FKLN REFERENCES LOAN(LOAN_NO),
INST_AMOUNT NUMBER(20) NOT NULL;
CONSTRAINT PKIL PRIMARY KEY(INST_NO,LOAN_NO));
0 Comments
Please do not Enter any spam link in the comment box