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.
Step 1: The first step is to create an E-R Model of all the relation in the database.
Step 2: Next create a Relational Model for Banking Database Application.
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%');
You can insert data into the tables now.
1. Customer Relation look like below after inserting data.
2. Insert data into the branch table as follows.
3. Insert the data into account table.
4. Insert data into the last table - Transaction.