Solution to DBMS Question – 4

This is a solution to the DBMS assignment Lab questions. You go step by step and run queries to get the solution to the questions, but we recommend first try to solve the problems by yourself and then check the solutions.

View DBMS Assignment Questions

Step: Create an E-R Model

ER Diagram – Bank Database

Step : Relational Model

Relational Model – Bank Database

Step : Implement Relations

1. Create the CUSTOMER table first and add constraints.

CREATE TABLE CUSTOMER ( CID NUMBER(5), CNAME VARCHAR2(30));
ALTER TABLE CUSTOMER ADD PRIMARY KEY(CID));

2. Create the BRANCH table and add constraints.

CREATE TABLE BRANCH (BCODE VARCHAR2(5), BNAME VARCHAR2(30));
ALTER TABLE BRANCH ADD PRIMARY KEY(BCODE));

ALTER TABLE BRANCH ADD CONSTRAINT CPK CHECK ( BCODE LIKE 'B%');

3. Create the ACCOUNT table and add constraints – primary key, foreign key and check constraints for account type.

S – Saving and C – current account

CREATE TABLE ACCOUNT ( ANO VARCHAR2(5), ATYPE CHAR(2), BALANCE NUMBER(7), CID NUMBER(5), BCODE VARCHAR2(5));
ALTER TABLE ACCOUNT ADD PRIMARY KEY(ANO) ;

ALTER TABLE ACCOUNT ADD FOREIGN KEY(CID) REFERENCES CUSTOMER (CID);

ALTER TABLE ACCOUNT ADD FOREIGN KEY(BCODE) REFERENCES BRANCH(BCODE);

ALTER TABLE ACCOUNT ADD CONSTRAINT CPK2 CHECK (ATYPE IN ('S','C'));

ALTER TABLE ACCOUNT ADD CONSTRAINT CK CHECK (ANO LIKE 'A%');

4. Create the final table – TRANSACTION and add constraints such as primary key, foreign key and check constraints for TTYPE.

W – WITHDRAWAL
D – DEPOSIT

CREATE TABLE TRANSACTION ( TID VARCHAR2(5), ANO VARCHAR2(5),  TTYPE CHAR(2), TDATE DATE, TAMOUNT NUMBER(7));
ALTER TABLE TRANSACTION ADD PRIMARY KEY(TID); ALTER TABLE TRANSACTION ADD FOREIGN KEY(ANO) REFERENCES ACCOUNT(ANO); ALTER TABLE TRANSACTION ADD CONSTRAINT CPK3 CHECK (TTYPE IN ('W','D'); ALTER TABLE TRANSACTION ADD CONSTRAINT CPK4 CHECK (TID LIKE 'T%');

Other Solutions

Solution (B)
You can insert data into the tables now.

1. Customer Relation look like below after inserting data.

Customer Relation – Bank Database

2. Insert data into the branch table as follows.

Branch Table – Bank Database

3. Insert the data into account table.

Account Table – Bank Database

4. Insert data into the last table – Transaction.

Transaction Table – Bank Database

Solution (C)

Solution (C) – Bank Database

Solution (D)

Solution (D) – Bank Database

Solution (E)

Solution (E) – Bank Database

Solution (F)

Solution (F) – Bank Database

Please support us by disabling your adblocker or whitelist this site from your adblocker. Thanks!