Solution to DBMS Question – 4

This is 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 1: The first step is to create an E-R Model of all the relation in the database.

ER Diagram - Bank Database
ER Diagram – Bank Database

Step 2: Next create a Relational Model for Banking Database Application.

Relational Model - Bank Database
Relational Model – Bank Database

Step 3: It is time you can create relations according to the relational model.

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%');

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

1. Customer Relation look like below after inserting data.

Customer Relation - Bank Database
Customer Relation – Bank Database

2. Insert data into the branch table as follows.

Branch Table - Bank Database
Branch Table – Bank Database

3. Insert the data into account table.
Account Table - Bank Database
Account Table – Bank Database

4. Insert data into the last table – Transaction.

Transaction Table - Bank Database
Transaction Table – Bank Database

Solution (C)
Solution (C) - Bank Database
Solution (C) – Bank Database

Solution (D)
Solution (D) - Bank Database
Solution (D) – Bank Database

Solution (E)
Solution (E) - Bank Database
Solution (E) – Bank Database

Solution (F)
Solution (F) - Bank Database
Solution (F) – Bank Database

Advertisements