Qualab AbbasServerName:ABBAS710330.DATABASE.WINDOWS.NET Login: qualab710330Password: Abbas710330

By

Cloud Design & Development

Module Code :B7IS110

Module Leader: Clive Gargan

1

SYSTEM DEVELOPMENT PROCESS AND METHODOLOGIES FOR BANK CORP.LTD

1. REQUIREMENTS/SYSTEM ANALYSIS

In this phase of the development cycle our IT team is going to do the define of the problems, identifying the its causes, specifying the solution, and identifying the information requirements that must be met be the by a system solution.

We will also examine the documents, work papers, procedures and interviewing the key users of the system. This phase is also includes the feasibility study to determine whether that solution is feasible or not.

2. SYSTEM DESIGN

In this phase our team is going to think what is the solution for Bank Corp.Ltd.We will start

prototyping and might write the codes to show them how it will actually look like and get the

feedback from the key users. System design phase will show how the system will fulfill its

objective.

3. PROGRAMMING

During programming stage, system specifications that were prepared during the design stage are

translated into software programming codes for the Bank Corp.Ltd

2

4. TESTING

Exhaustive and thorough testing will be conducted to make sure the whether the system produces the right results. System Testing will test the functioning of the information system as a whole. It will also determine the whether the different parts of the system working together as planned. Acceptance testing will provide the final certification that system is ready to use in the production setting.

5. CONVERSION

In this process our team will change the old Bank Corp system into new system. There are four main conversion strategies could be employed but we decide to apply parallel strategy. Parallel strategy will be applied in Bank Corp until everyone assured that the new database working correctly.

6. PRODUCTION AND MAINTENANCE

After the installation of new system in Bank Corp and conversion is completed, we can say that system is in production. During this stage system will be reviewed by both users and technical specialists to determine how well it has met Bank Corp objectives and to decide whether any revision or modification are in order. After the system has fine tuned it will maintain while it is in production to correct errors, meet requirements or improve processing efficiency.

3

BANK CORP LTD DATABASE TABLES DETAILS

Bank Corp has a total seven tables to manipulate and access the data for their clients. Customers table has a relationship with Accounts and Association Table to access the Accounts and Association details. Account table also has a relationship with Transaction table and Security table to see what kind of transaction has made against each Account and what kind of security has been provided to the account.

To see the two way relationship between the customers and the type of association they are associated with CustomersId1 and CustomersId2 has been added to the association table as foreign key along with associationtypeId.

Six different types of JOINS has been added to see different columns between the tables.

Five VIEW TABLES has been added to see limited data between tables and for the safe guard of sensitive information.

Five STORE PROCEDURES has been added to increase the efficiency of the data and quick access for the most important information.

TRIGGERS are also added to the tables for the notification when database will be updated

4

5

BANK CORP.LTD PROJECT PLAN

6

1. DDL STATEMENTS TO CREATE TABLES AND INSERT DATA INTO DATABASE FOR BANK

CORP.LTD1 Customers Table

CREATE TABLE Customers

( CustomersId int NOT NULL,FirstName varchar(20)

NULL,LastName varchar(20) NULL,Address1 varchar(50)

Null,Address2 varchar(50) Null,PostalCode varchar(30)

Null,County varchar(20) Null,City varchar(255)

NULL,ContactNumber int NULL,DateOfBirth date

NULL,AccountId int NOT NULL,

)

INSERT INTO dbo.Customers

(CustomersId, FirstName, LastName, Address1, Address2,

PostalCode, County, City, ContactNumber, DateOfBirth,

AccountId)

VALUES

(3, 'Audrey', 'Dwyer', 'Blackrock ', 'Terrace', 'Blackrock',

'Dublin', 'Dublin','0827764343', '1998-12-07' , 13 )

2 Accounts Table

CREATE TABLE Accounts

(

AccountId int NOT NULL,AccountType varchar(20)

NULL,AccountOpenDate datetime NULL,BranchName

varchar(50) Null,BranchAddress varchar(50)

Null,BranchCounty varchar(30) Null,LastUpdatedOn

varchar(20) Null,AccountBalance int NULL,CustomersId

int NOT NULL,

)

INSERT INTO dbo.Accounts

(AccountId, AccountType, AccountOpenDate,

BranchName, BranchAddress, BranchCounty,

LastUpdatedOn, AccountBalance, CustomersId)

VALUES

(4, 'Savings', '1973-10-28', 'Cork', '12 Hillcrest Road',

'Cork', '2016-02-01 16:15:12', '1000', 12 )

3 Transection Table

CREATE TABLE Transections

(TransectionsId int NOT NULL TransectionDate date

NULL, TransectionType varchar(30)NULL, AccountsId int

Null)

INSERT INTO dbo.Transections

(TransectionsId,TransectionDate,TransectionType,

AccountsId)

VALUES

(1 ,'2016-02-07', 'Bank Cheque', 15)

7

4 Security Type Table

CREATE TABLE SecurityType

(

SecuritytypeId int NOT NULL,SecurityName varchar(30)

NULL,DatedCreated datetime NULL,LastUpDated

datetime NULL,StillValid varchar(10) NULL,DeletedOn

datetime NULL,SecuritysId int NOT NULL)

INSERT INTO dbo.SecurityType

(SecuritytypeId, SecurityName, DatedCreated,

LastUpDated, StillValid, SecuritysId)

VALUES

(15, 'Land' , '1998-12-23','2016-03-05', 'Yes', 2)

5 Security Table

CREATE TABLE Securitys

((SecurityId int NOT NULL, , LastUpdated datetime

NULL, AccountId int NOT NULL, SecurityTypeId int

NOT NULL)

INSERT INTO dbo.Securitys

(SecurityId, LastUpdated, AccountId, SecurityTypeId)

VALUES

(8, '2016-02-23 12:16:35', 9, 13 )

6 Association Table

CREATE TABLE Association

(AssociationId int NOT NULL,CustomersId1 int NOT

NULL,CustomersId2 int NOT NULL,AssociationTypeId

int NOT NULL)

INSERT INTO dbo.Association

(AssociationId, CustomersId1, CustomersId2,

AssociationTypeId)

VALUES

(4, 10, 13,1)

7 Association Type Table

CREATE TABLE AssociationType

(

AssociationTypeId int NOT NULL,AssociationName

varchar(50) NULL,AssociationAddress varchar(40)

NULL,AssociationCounty varchar(20)

NULL,AssociationCity varchar(30)

NULL,AssociationContactNumber int NULL,JoiningDate

date NULL,StillMember

)

INSERT INTO dbo.AssociationType

(AssociationTypeId, AssociationName,

AssociationAddress, AssociationCounty, AssociationCity,

AssociationContactNumber,JoiningDate, StillMember)

VALUES

(1, 'Accountant','3 Blackrock

Park','Blackrock','Dublin','014454668','2014-02-26','Yes)

8

2. PRIMARY AND FOREIGN KEY CONSTRAINTS TO THE BANK CORP DATABASE TABLES

1 ALTER TABLE Customers

ADD CONSTRAINT pkCustomers PRIMARY KEY (CustomersId)

2 ALTER TABLE Accounts

ADD CONSTRAINT pkAccounts PRIMARY KEY (AccountId)

3 ALTER TABLE Transections

ADD CONSTRAINT pkTransections PRIMARY KEY (TransectionsId)

4 ALTER TABLE Security

ADD CONSTRAINT pkSecuritys PRIMARY KEY (SecurityId)

4 ALTER TABLE SecurityType

ADD CONSTRAINT pkSecurityType PRIMARY KEY (SecurityTypeId)

5 ALTER TABLE AssociationType

ADD CONSTRAINT pkAssociationType PRIMARY KEY (AssociationTypeId)

6 ALTER TABLE Association

ADD CONSTRAINT pkAssociationId PRIMARY KEY (AssociationId)

1 ALTER TABLE Accounts

ADD FOREIGN KEY (CustomersId)

REFERENCES Customers(CustomersId)

2 ALTER TABLE Association

ADD FOREIGN KEY (CustomersId)

REFERENCES Customers(CustomersId)

Foreign keys has been added to the Securitys AndTransections Tables as above

9

3. DIFFERENT JOINS STATEMENTS TO SEE RECORD BETWEEN DIFFERENT TABLES

1 TO SEE THAT TWO CUSTOMERS ARE CONNECT WITH SAME ASSOCIATION TYPE.

SELECT

C.CustomersId, C.FirstName, C.LastName,C.Address1, C.Address2, C.County, C.ContactNumber, A.AssociationTypeId

FROM

Customers C

INNER JOIN

Association A

ON

C.customersId=A.AssociationId

2 T0 SEE NULL VALUES IN TABLES

SELECT

C.customersId1,C.customersId2, A.AssociationName, A.AssociationAddress, A.AssociationContactNumber, A.JoiningDate,StillMember

FROM

Association C

FULL OUTER JOIN

AssociationType A

ON

C.AssociationId=A.AssociationTypeId

3 JOIN TO SEE THE TYPE OF CUSTOMERS (Personal or Corporate)

SELECT

C.customersId, C.FirstName, C.LastName, C.County,C.DateOfBirth,

A.AccountType, A.AccountOpenDate, A.BranchName, A.AccountBalance

FROM

Customers C

INNER Join

Accounts A

ON

C.CustomersId=A.AccountId

4 TO SEE THE TYPE OF TANSECTION MADE WITH TIME, DATE AND BRANCH NAME.(Personal or Corporate)

SELECT

C.AccountId, C.AccountType, C.AccountOpenDate, C.BranchName, C.BranchCounty, C.LastUpDatedOn, A.TransectionType, A.TransectionDate

FROM

Accounts C

INNER Join

Transections A

ON

C.AccountId=A.TransectionsId

10

5 TO SEE The Type Of Security Provided to the BANK SECURITY NAME TIME, DATE AND VALIDATION.

SELECT

C.AccountId, C.LastUpDated, A.SecurityName,A.DatedCreated, A.StillValid

FROM

Securitys C

INNER JOIN

SecurityType A

ON

C.SecurityId=A.SecuritytypeId

6 TO SEE The Account type and security ID provided to the account along with branch ,balance and date created that security.

SELECT

A.AccountId, A.SecurityTypeId, C.AccountType, C.BranchName, C.BranchAddress,

C.LastUpDatedOn,C.AccountBalance

FROM

Accounts C

NNER JOIN

Securitys A

ON

C.AccountId = A.SecurityId

11

4. VIEW TABLES TO SELECT LIMITED COLUMNS AND HIDE SENSITIVE INFORMATION FROM ORDINARY USERS

1 CREATE VIEW vwSecureDataCustomers

As

SELECT

CustomersId, FirstName, LastName, County, ContactNumber

From dbo.Customers

SELECT * FROM vwSecureDataCustomers

WHERE CustomersId = 1

2 CREATE VIEW vwSecureAccount

as

SELECT

AccountId,CustomersId,AccountType, AccountOpenDate, BranchName, BranchAddress, LastUpDatedOn

From dbo.Accounts

SELECT * FROM vwSecureAccount

WHERE AccountId = 5

3 CREATE VIEW vwSecureSecurityType

as

SELECT

SecurityTypeId,DatedCreated, LastUpdated, StillValid, SecuritysId

From dbo.SecurityType

4 CREATE VIEW vwSecureAssociationType

as

SELECT

AssociationTypeId,AssociationAddress, AssociationCounty, JoiningDate, StillMember

From AssociationType

12

5 CREATE VIEW vwSCustomersandAssociationView

As

SELECT

CustomersId, FirstName, LastName,Address1, Address2, County, ContactNumber, AssociationTypeId

FROM dbo.Customers

INNER JOIN

dbo.Association

on

CustomersId = AssociationId

5. DIFFERENT STORE PROCEDURES HAS BEEN CREATED TO SAVE TIME TO RETRIEVE IMPORTANT COLUMNS FROM BANK CORP

DATABASE

1 CREATE PROCEDURE uspGetNameAndContact

AS

SELECT FirstName, LastName, ContactNumber

FROM Customers

GO

2 CREATE PROCEDURE uspGetAccountInfo

AS

SELECT AccountId, AccountType, AccountOpenDate, BranchName, AccountBalance

FROM Accounts

GO

3 CREATE PROCEDURE uspGetTransectionInfo

AS

SELECT TransectionsId, TransectionDate, TransectionType

FROM Transections

GO

4 CREATE PROCEDURE uspGetSecurityTypeInfo

13

AS

SELECT SecurityTypeId, SecurityName, StillValid

FROM SecurityType

GO

5 CREATE PROCEDURE uspGetAssociationTypeInfo

AS

SELECT AssociationTypeId, AssociationName, JoiningDate, StillMember

FROM AssociationType

GO

6. DIFFERENT TRIGGERS HAS BEEN CREATED TO KEEP THE RECORD OF ALL UPDATES IN TABLES

Create Trigger tr trUpdateOrDrop

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

PRINT 'A NEWTABLE HAS BEEN CREATED , ALTERED OR DROPED'

END

Create Trigger trCustomersTable

ON dbo.Customers

AFTER UPDATE, Insert

AS

BEGIN

PRINT 'Customers Table Has A New Record'

END