Solution to DBMS Question

Table of Contents

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.

Step: Create an E-R Model

ER Diagram - Bank Database — ER Diagram – Bank Database

Step : Relational Model

Relational Model - Bank Database — 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%');