Atm Database Management 714c2t

ATM

DATABASE

MANAGEMENT

SYSTEM

1

CHAPTER 1

INTRODUCTION 1.1 INTRODUCTION TO DATA BASE MANAGEMENT SYSTEM Database management systems are computer software applications that interact with the , other applications, and the database itself to capture and analyze data. A generalpurpose DBMS is designed to allow the definition, creation, querying, update, and istration of databases. Well-known DBMSs include MySQL, PostgreSQL, Microsoft, Oracle, Sybase and IBM DB2. A database is not generally portable across different DBMSs, but different DBMS can interoperate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one DBMS. Database management systems are often classified according to the database modelthat they ; the most popular database systems since the 1980s have all ed the relational model as represented by the SQL language. Sometimes a DBMS is loosely referred to as a 'database'.A database is an organized collection of data. The data is typically organized to model aspects of reality in a way that s processes requiring information. For example, modelling the availability of rooms in hotels in a way that s finding a hotel with vacancies. Formally, a "database" refers to a set of related data and the way it is structured or organized. Access to this data is usually provided by a "database management system" (DBMS) consisting of an integrated set of computer software that allows s to interact with one or more databases and provides access to all of the data contained in the database (although restrictions may exist that limit access to particular data). The DBMS provides various functions that allow entry, storage and retrieval of large quantities of information as well as provide ways to manage how that information is organized. Because of the close relationship between them, the term "database" is often used casually to refer to both a database and the DBMS used to manipulate it. Outside the world of professional information technology, the term database is often used to refer to any collection of related data (such as a spreadsheet or a card index). This article is concerned only with databases where the size and usage requirements necessitate use of a database management system. Existing DBMSs provide various functions that allow management of a database and its data which can be classified into four main functional groups: K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

2

1. Data definition – Creation, modification and removal of definitions that define the organization of the data. 2. Update – Insertion, modification, and deletion of the actual data.[3] 3. Retrieval – Providing information in a form directly usable or for further processing by other applications. The retrieved data may be made available in a form basically the same as it is stored in the database or in a new form obtained by altering or combining existing data from the database. 4. istration – ing and monitoring s, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information that has been corrupted by some event such as an unexpected system failure. Both a database and its DBMS conform to the principles of a particular database model. "Database system" refers collectively to the database model, database management system, and database.

1.2 ENTITY RELATIONSHIP MODEL In software engineering, an entity–relationship model (ER model) is a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a database such as a relational database. The main components of ER models are entities (things) and the relationships that can exist among them.Entity–relationship modeling was developed by Peter Chen and published in a 1976 paper. However, variants of the idea existed previously, and have been devised subsequently such as supertype and subtype data entities and commonality relationships. An entity–relationship model is a systematic way of describing and defining a business process. The process is modeled as components (entities) that are linked with each other by relationships that express the dependencies and requirements between them, such as: one building may be divided into zero or more apartments, but one apartment can only be located in one building. Entities may have various properties (attributes) that characterize them. Diagrams created to represent these entities, attributes, and relationships graphically are called entity–relationship diagrams.The entity-relationship (E-R) data model is based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects. An entity is a “thing” or “object” in the real K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

3

world that is distinguishable from other objects. For example, each person is an entity, and bank s can be considered as entities. Entities are described in a database by a set of attributes. For example, the attributes -number and balance may describe one particular in a bank, and they form attributes of the entity set. Similarly, attributes customer-name, customer street address and customer-city may describe a customer entity. An extra attribute customer-id is used to uniquely identify customers (since it may be possible to have two customers with the same name, street address, and city). A unique customer identifier must be assigned to each customer. In the United States, many enterprises use the social-security number of a person (a unique number the U.S. government assigns to every person in the United States) as a customer identifier. A relationship is an association among several entities. For example, a depositor relationship associates a customer with each that she has. The set of all entities of 11 the same type and the set of all relationships of the same type are termed an entity set and relationship set, respectively. The overall logical structure (schema) of a database can be expressed graphically by an E-R diagram. Symbols used in E-R Diagram 1. Entity – rectangle 2. Attribute – oval 3. Relationship – diamond 4. Link – line 1.3 RELATIONAL MODEL The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F. Codd.[1][2] In the relational model of a database, all data is represented in of tuples, grouped into relations. A database organized in of the relational model is a relational database. In the relational model, related records are linked together with a "key".The purpose of the relational model is to provide a declarative method for specifying data and queries: s directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries.Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in an SQL database schema corresponds to a predicate variable; the K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

4

contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates. However, SQL databases deviate from the relational model in many details, and Codd fiercely argued against deviations that compromise the original principles.The relational model used the basic concept of a relation or table. The columns or fields in the table identify the attributes such as name, age, and so. A tuple or row contains all the data of a single instance of the table such as a person named Doug. In the relational model, every tuple must have a unique identification or key based on the data. In this figure, a social security number (SSAN) is the key that uniquely identifies each tuple in the relation. Often, keys are used to data from two or more relations based on matching identification. The relational model also includes concepts such as foreign keys, which are primary keys in one relation that re kept in another relation to allow for the ing of data. As an example of foreign keys is storing your mother's and father's SSAN in the tuple that represent you. Your parents' SSANs are keys for the tuples that represent them and they are foreign keys in the the tuple that represents you. 1.4 NORMALIZATION Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. Normalization involves refactoring a table into smaller (and less redundant) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys. Edgar F. Codd, the inventor of the relational model (RM), introduced the concept of normalization and what we now know as the First normal form (1NF) in 1970.Codd went on to define the Second normal form (2NF) and Third normal form (3NF) in 1971, and Codd and Raymond F. Boyce defined the BoyceCodd Normal Form (BCNF) in 1974. Informally, a relational database table is often described as "normalized" if it is in the Third Normal Form. Most 3NF tables are free of insertion, update, and deletion anomalies. The relational model separates the logical design from the physical design: DBMS performance is a matter of physical designer using indexes, view materialization, big buffers, etc. It is not a matter of changing the logical design. Update anomalies:

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

5

if data items are scattered and are not linked to each other properly, then there may be instances when we try to update one data item that has copies of it scattered at several places, few instances of it get updated properly while few are left with there old values. This leaves database in an inconsistent state. Deletion anomalies: we tried to delete a record, but parts of it left undeleted because ofunawareness, the data is also saved somewhere else. Insert anomalies: we tried to insert data in a record that does not exist at all. 1 NF: First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.First normal form is an essential property of a relation in a relational database. Database normalization is the process of representing a database in of relations in standard normal forms, where first normal is a minimal requirement. 2 NF: Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.Put simply, a table is in 2NF if and only if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key. 3 NF: The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971.Codd's definition states that a table is in 3NF if and only if both of the following conditions hold: The relation R (table) is in second normal form (2NF) Every non-prime attribute of R is non-transitively dependent on every superkey of R. A non-prime attribute of R is an attribute that does not belong to any other candidate key of R. A transitive dependency is a functional dependency in which X → Z (X determines Z) indirectly, by virtue of X → Y and Y → Z (where it is not the case that Y K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

6

→ X). A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds: X contains A (that is, X → A is trivial functional dependency), or X is a superkey, or Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)[5][6][need quotation to ] Zaniolo's definition gives a clear sense of the difference between 3NF and the more stringent Boyce–Codd normal form (BCNF). BCNF simply eliminates the third alternative ("Every element of A-X, the set difference between A and X, is a prime attribute"). BCNF: Boyce–Codd normal form (or BCNF or 3.5NF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF as originally defined. If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist. A relational schema R is in Boyce– Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold: X → Y is a trivial functional dependency (Y ⊆ X) X is a super key for schema R Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper by Ian Heath in 1971. Date writes: "Since that definition predated Boyce and Codd's own definition by some three years, it seems to me that BCNF ought by rights to be called Heath normal form. But it isn't." Edgar F. Codd released his original paper 'A Relational Model of Data for Large Shared Databanks' in June 1970. This was the first time the notion of a relational database was published. All work after this, including the Boyce-Codd normal form method was based on this relational model.Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF Depending on what its functional K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

7

dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

1.5Application areas of DBMS 1. Banking: For customer information, s, and loans, and banking transactions. 2. Airlines: For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner - terminals situated around the world accessed the central database system through phone lines and other data networks. 3. Universities: For student information, course registrations, and grades. 4. Credit card transactions: For purchases on credit cards and generation of monthly statements. 5. Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks. 6. Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds. 7. Sales: For customer, product, and purchase information. 8. Manufacturing: For management of supply chain and for tracking production of items in factories, inventories of items in warehouses / stores, and orders for items.

1.6 AIM To create a data base for Management Information Systems for ATM database management system. And write queries for retrieving the data.

1.7 OBJECTIVES 1. To understand the entity relational model. 2. To understand about the relational model. 3. To understand the modules and its functions. K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

8

4. To create a data base which contains tables or relations. 5. To write queries for each function to retrieve the data from the tables.

CHAPTER 2 PROJECT DESCRIPTION In olden days, customers used to go to the banks and check their balance amounts and draw the money, as the technology has been increasing banking institutions have been introduced the ATM. An ATM is an automated teller machine which is a computerized telecommunication device that provides the customers of a finical institution with the access to financial transactions in a public space.In the ATM’s of the every customer isprovided with unique PIN i.e., personal identification number. The money in ATM is drawn by entering the PIN number.The other names of the ATM are automated banking machine, money machine,cashmachine, hole-in-the- wall, cash point and any time money. On most modern ATMs, the customer is identified by inserting a plastic ATM card with a magnetic stripe or a plastic smart card with a chip that contains a unique card number and some security information such as an expiration date or CVV (CVV). Authentication is provided by the customer entering a personal identification number (PIN).Using an ATM, customers can access their bank deposit or credit s in order to make a variety of transactions such as cash withdrawals, check balances, or credit mobile phones. If the currency being withdrawn from the ATM is different from that in which the bank is denominated the money will be converted at an official exchange rate. Thus, ATMs often provide the best possible exchange rates for foreign travellers, and are widely used for this purpose. Most ATMs are connected to interbank networks, enabling people to withdraw and deposit money from machines not belonging to the bank where they have their s or in the countries where their s are held (enabling cash withdrawals in local currency). Some examples of interbank networks include NYCE, PULSE, PLUS, Cirrus, AFFN, Interac, Interswitch, STAR, LINK, MegaLink and BancNet. 2.1 CUSTOMER MODULE:

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

9

Customers to have an ATM first they should have their in the respective banks .After creating the in the bank at the age of eighteen one can apply for the ATM card .By inserting the ATM card into machine , enter the PIN number of our card. List of options are displayed out of which one option is selected and further process is continued according to it.A customer is the recipient of a good, service, product, or idea, obtained from a seller, vendor, or supplier for a monetary or other valuable consideration. A customer is a person through whom the bank gets an opportunity to make an earning in return to the service they can provide the customer with. an individual who has a checking with a bank or an individual who has a mortgage or a loan with the bank or an individual who has a fixed deposit with the bank are all customers of the bank.

2.1.1 ATTRIBUTES IN CUSTOMER TABLE The attributes of the customer module are card_id taken as a primary key, number as acc_no taken as foreign key referring from table,cust_id ,customer name as cust_name , to take the address of the customer street, city, zip, state are taken as attributes, for maintain the email_id and phone_no are taken as attributes. Attribute

Data type

Cust_id

Number

Cust_name

Varchar

Card_id

Number

Email_id

Varchar

State

Varchar

City

VarChar

Zip

Number

Ph_no

Number

Acc_no

Varchar

Aadhar_no

Number

Street

Varchar

Table 1 atrributes of the customer table

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

10

2.1.2 E R DIAGRAM OF CUSTOMER MODULE:

Figure 2.1 er digram of customer

2.2 INFO MODULE: An refers to assets, liabilities, income, expenses, and equity, as represented by individual ledger pages, to which changes in value are chronologically recorded with debit and credit entries. A bank is a financial between a bank customer and a financial institution. A bank can be a deposit , a credit card, or any other type of offered by a financial institution. The financial transactions which have occurred within a given period of time on a bank are reported to the customer on a bank statement and the balance of the at any point in time is the financial position of the customer with the institution. A fund that a customer has entrusted to a bank and from which the customer can make withdrawals. Examples of common financial s are cash, s receivable, mortgages, loans etc. K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

11

2.2.1 ATTRIBUTES IN INFO MODULE: In this module after conducting normalization, we have four tables like branch, , acc1, transactions. In branch table we have attributes like branch_id which is a primary key, branch_name, branch_loc, branch_ph. Attribute

Data type

Br_id

Number(5) primary key

Br_name

Varchar(10)

Br_loc

Varchar(10)

Br_ph

Number(11) Table 2 attributes of the branch

In table we have attributes like _no which is a primary key, holder name, gender, branch id which is a foreign key referencing to branch table.

Table 3 of the In

Attribute

Data type

Acc_no

Number(5) primary key

Acc_name

Varchar(10)

Gender

Char

Br_id

Number(5), foreign key

acc1

attributes table we

have attributes like acc_no which is a foreign key referencing to table, mode of operation, type of . Attribute

Data type

Acc_no

Number(5) foreign key

Mode_of_operation

Varchar(10)

Type_of_acc

Varchar(10)

Table 4 attributes of the acc1

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

12

In transaction table we have attributes like trasaction id which is a primary key, number which is a foreign key referencing to table, open_balance, current_balance, withdrawal_limit, transaction_date. Attribute

Data type

Trans_id

Number(5) primary key

Acc_no

Number(5) foreign key

Open_balance

Number(10)

Current_balance

Number(10)

Withdrawal_limit

Number(10)

Trans_date

Date Table 5 attributes of the transcation

2.2.2 E R DIAGRAM OF INFO MODULE

Br_phno

Br_id

Acc_no

Br_loc

acc_name gen

Br_name

branch

has mop

Br_id acctp Cur bal

transid withlimitli

Op bal

Figure 2.2 E R diagram of the

2.3 CARD INFO MODULE: Credit card-Pre-approved credit which can be used for the Purchase of items now and payment of them later. It is a plastic card having a magnetic strip, issued by a bank or business authorizing the holder to buy goods or services on credit . Also called charge cards. The concept of using a card for was first described in 1887 by Edward Bellamy in his utopian novel Looking Backward. The size of most credit cards is 85.60 X 53.98 mm. K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

13

Person should have a savings current in the bank. His assets and liabilities on a particular date are reported to bank. A statement of annual or monthly income. He is considered credit worthy up to certain limit depending upon his income , assets and expenditure. Mostly card_information contains cvv number, expdate, _number which can be taken as foreign key from table,cardtype and holder_name are the attributes in this table.

2.3.1 ATTRIBUTES OF THE CARD INFO The attributes of the card table are card_id is taken as a foreign key referring the customer. As every ATM card has pin number , expiry date ,issue date , CVV number and card type. So, pin ,cvv,exp_date,iss_date, card type are taken as the attributes. Attribute

Data

Card_id

type Number

Pin

Number

Cvv

Number

Card_type

VarChar

Exp_date

Date

Iss_date

Date

Table 6 attributes of the card 2.3.2 E R DIAGRAM OF THE CARD INFO MODULE:

card_id Iss_date

pin

Card

Exp_date

card_type K L University Computer Science & Engineering

cvv

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

14

Figure 2.3 E R diagram of card

2.4 EMPLOYEE MODULE: Convenient and simple, Standard Chartered’s Employee Banking is a worldclass banking facility that allows you to streamline your salary payments from home or at work. Every employee has an id and there are several departments to work in . If there are several branches for a bank in a place then we require the department of which they are working with. 2.4.1 ATTRIBUTES OF THE EMPLOYEE Relating employee there are two tables they are employee table and dept table. In emp table the emp id acts as a primary key,emp_name , age, date_of_ing, experience , acc_no. In the dept table dept_id ,dept_name and emp_id acts as a foreign key. Attribute

Data type

Emp_id

Number

Emp_name

Varchar

Age

Number

Experience

Number

date_of_ing Acc_no

Date Number

Table 7 attributes of the employee

Attribute

Data type

Dept_id

Number

Emp_id

Number

Dept_name

VarChar

Table 8 attributes of the department

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

15

2.4.2 ER DIAGRAM Empname

age

deptid

empid

empid

Acc_no Works in

employee sal

dept deptname

doj

exp

Figure 2.4 E R diagram of the employee

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

16

CHAPTER 3 ER DIAGRAM

cardid

cvv

pin

expdate

card

cardty

issdat has

has

Cur bal Br_phno

Br_id

Acc_no

Br_loc

acc_name gen

branch

Br_name

has

mop

Br_id acctp transid

manages withlimitli

Op bal

m

age

Empname empid K L University Computer Science & Engineering

deptid

empid Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

17

Acc_no Works in

employee

dept

sal

deptname doj

exp

Figure 3.1 E R diagram

CHAPTER 4 SQL QUERIES 4.1 CUSTOMER MODULE: Creating a table: Create table customer(cust_id number(5),card_id number(5),cust_name varchar(10), phone_no number(11),aadhar_no number(6),acc_no number(5) ,foreign key(acc_no) references (acc_no),email_id varchar(10),street varchar(10),city varchar(10), State varchar(10), zip number(7)); Inserting records into table: Insert into customer values(&cust_id,&card_id,’&cust_name’,&acc_no,&aadhar_no,’&email_id’,’&street’, ‘&city’,’&state’,&zip,&phone_no); Customer registration details: create view custreg as select * from customer; select * from custreg; Transaction scheduling: select * from transcation where transcation_date='10-nov-2014'; Transaction summary: select * from transcation; Transaction daily schedule list: select * from transcation; Customer visit history: Select cust_id,cust_name ,transcation_date from customer c , transcation t where c.acc_no=t.acc_no; Report on missing data: Select * from customer where acc_no IS NULL; K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

18

Initiate daily processes: select cust_id,c.card_id,cust_name from customer c,card d where issue_date='30-aug95'and c.card_id = d.card_id; Reprocessing facility: Update table customer phone_no=9000450536 where card_id=222;

4.2 info module: Creating a table: Create table ( acc_no number(5) primary key, acc_holder name varchar(10), gender char, br_id number(5), foreign key references branch(br_id); Create table acc1(acc_no number(5), foreign key(acc_no) references (acc_no), mode_of_operation varchar(10), type_of_acc varchar(10)); Create table transcation(acc_no number(5), foreign key(acc_no) references (acc_no), open_balance number(10),current_balance number(10), withdrawal_limit number(10), transcation_date date); Create table branch(br_id number(5) primary key, br_name varchar(10), br_locat varchar(10),br_phno number(11)); Inserting records into table: Insert into values (&acc_no,’&acc_holder name ‘,’ &gender’,&br_id); Insert into acc1 values(&acc_no,’&mode_of_operation’,’&type_of_’); Insert into transcation (&acc_no, &open_balance, &current_balance, &withdrawal_limit, ‘ & transcation_date’); Insert into branch(&br_id,’&br_name’, ‘&br_loc’, &br_phno); Customer registration details: create view custreg as select * from customer; select * from custreg; K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

19

Transaction scheduling: select * from transcation where transcation_date='26-jan-2015’'; Transaction summary: select * from transcation; Transaction daily schedule list: select * from transcation;

Customer visit history: Select cust_id,cust_name ,transcation_date from customer c , transcation t where c.acc_no=t.acc_no; Report on missing data: select * from transcation where open_balance IS NULL; Initiate daily processes: select cust_id,c.card_id,cust_name from customer c,card d where issue_date='30-aug95'and c.card_id = d.card_id; Reprocessing facility: update acc1 set type_of_acc='t' where acc_no=72345; 4.3 CARD INFO MODULE Creating a table: Create a table card(card_id number(5) ,foreign key(card_id) refernces customer(card_id) , acc_no number(5) , foreign key (acc_no) references (acc_no), exp_date date, issue_date date,cvv number(4), pin number(5) primary key); Create table card1(pin number(5), foreign key (pin) references cardinfo(pin), card_type varchar(20)); Inserting records into table: Insert into card (&card_id, & acc_no, ‘&exp_date’,’&issue_date’,&cvv,&pin); Insert into card1(&pin,’&card_type’); Customer registration details: create view custreg as select * from customer; select * from custreg; Transaction scheduling: K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

20

select * from transcation where transcation_date='26-jan-2015’'; Transaction summary: select * from transcation; Transaction daily schedule list: select * from transcation;

Customer visit history: Select cust_id,cust_name ,transcation_date from customer c , transcation t where c.acc_no=t.acc_no; Report on missing data: select * from card where cvv IS NULL; Initiate daily processes: select cust_id,c.card_id,cust_name from customer c,card d where issue_date='9-mar10'and c.card_id = d.card_id; Reprocessing facility: update card1 set card_type='credit' where pin=5024; 4.4 EMPLOYEE MODULE: Creating a table: Create table emp (emp_id number(5),emp_namevarchar(10),acc_no number(5),age number(5),sal number(6),experience number(3), date_of_ing date); Create table dept (dept_id number(5),dept_name varchar(10),emp_id number(5)); Inserting a record: Insert into emp values(&emp_id,’&emp_name’,&acc_no,&age,&sal,&experience, ‘&date_of_ing’); Insert into dept values(&dept_id,’&dept_name’,&emp_id); Customer registration details: create view custreg as select * from customer; select * from custreg; Transaction scheduling: K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

21

select * from transcation where transcation_date='26-jan-2015’'; Transaction summary: select * from transcation; Transaction daily schedule list: select * from transcation;

Customer visit history: Select cust_id,cust_name ,transcation_date from customer c , transcation t where c.acc_no=t.acc_no; Report on missing data: select * from dept where dept_id IS NULL; Initiate daily processes: select cust_id,c.card_id,cust_name from customer c,card d where issue_date='9-mar10'and c.card_id = d.card_id; Reprocessing facility: update emp set emp_name='honey' where emp_id=1000;

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

22

CHAPTER 5 OUTPUTS 5.1 CUSTOMER MODULE 5.1.1 Creating a table

Figure 5.1.1 creating a table 5.1.2 Inserting a record into the table

Figure 5.1.2 inserting a table 5.1.3 Customer registration details

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

23

Figure 5.1.3 customer registration details

5.1.4 Transaction scheduling

Figure 5.1.4 transaction scheduling 5.1.5 Transaction daily scheduling

Figure 5.1.5 transaction daily scheduling 5.1.6 Transaction summary

Figure 5.1.6 transcation summary K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

24

5.1.7 Customer visit history

Figure 5.1.7 customer visit history 5.1.8 Report on missing data

Figure 5.1.8 report on missing data 5.1.9 Initiate daily process

Figure 5.1.9 initiate daily process 5.1.10 Reprocessing facility

Figure 5.1.10 reprocessing facility 5.2 Info Module 5.2.1 creating a table

Figure 5.2.1 creating an table

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

25

Figure 5.2.1.1 creating an acc1 table

Figure 5.2.1.2 creating a branch table 5.2.2 Inserting a record into table

Figure 5.2.2 insering into

Figure 5.2.2.1 inserting into acc1

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

26

Figure 5.2.2.2 inserting into branch

5.2.3 Customer registration details

Figure 5.2.3 customer registration details 5.2.4 transaction scheduling:

Figure 5.2.4 transaction scheduling 5.2.5 Transaction daily scheduling

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

27

Figure 5.2.5 transaction daily scheduling 5.2.6 Transaction summary

Figure 5.2.6 transcation summary

5.2.7 Customer visit history

Figure 5.2.7 customer visit history 5.2.8. Report on missing data

Figure 5.2.8 report on missing data 5.2.9 Initiate daily process

Figure 5.2.9 initiate daily process 5.2.10 Reprocessing facility

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

28

Figure 5.2.10 reprocessing facility

5.3 CARD INFO MODULE: 5.3.1 Creating a table

Figure 5.3.1 creating a card table

Figure 5.3.1.1 creating a card1 table 5.3.2 inserting into table

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

29

Figure 5.3.2 insert into card

Figure 5.3.2.1 inserting a card1 5.3.3 Customer registration details

Figure 5.3.3 customer registration details 5.3.4 Transaction scheduling

Figure 5.3.4 transaction scheduling 5.3.5 Transaction daily scheduling K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

30

Figure 5.3.5 transaction daily scheduling 5.3.6 Transaction summary

Figure 5.3.6 transcation summary 5.3.7 Customer visit history

Figure 5.3.7 customer visit history 5.3.8 Report on missing data

Figure 5.3.8 report on missing data K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

31

5.3.9 Initiate daily process

Figure 5.3.9 initiate daily process

5.3.10 Reprocessing facility

Figure 5.3.10 reprocessing facility 5.4 EMPLOYEE MODULE 5.4.1 Creating a table:

Figure 5.4.1 creating employee table

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

32

Figure 5.4.1.1 creating dept table

5.4.2 Inserting into table

Figure 5.4.2 insertion in employee table

Figure 5.4.2.1 insertion in dept value K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

33

5.4.3 Customer registration details

Figure 5.4.3 customer registration details 5.4.4 Transaction scheduling

Figure 5.4.4 transaction scheduling 5.4.5 Transaction daily scheduling

Figure 5.4.5 transaction daily scheduling 5.4.6 Transaction summary

Figure 5.4.6 transcation summary K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

34

5.4.7 Customer visit history

Figure 5.4.7 customer visit history

5.4.8 Report on missing data

Figure 5.4.8 report on missing data 5.4.9 Initiate daily process

Figure 5.4.9 initiate daily process 5.4.10 Reprocessing facility

Figure 5.4.10 reprocessing facility

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

35

CHAPTER 6 CONCLUSION Our team believes that the design yields a simple, easy to use system. The cardholder is prompted through the system at every step of the way with sufficient error checking to prevent invalid input. This system allows and ties multiple s to one ATM card and PIN combination. It allows a cardholder to have as many s as desired (more than one checking, more than one credit card, more than one loan, etc.). Allows cardholders to make balance inquiries, deposits, withdrawals, payments, and transfer funds – all with the appropriate s displayed for selection depending upon the type of transaction Limits the amount of a withdrawal from to with no limits placed on fund transfers and Limits the number of transactions per day. ATM networks are the wave of new generation banking. Today, ATM networks have conquered normal banking procedures by rendering 'smarter' services. Our proposed distributed approach for ATM network scan be a way to properly utilize and different resources. This utilization is essentially required and its’ necessity will increase rapidly with time. It will not only make the life easier but also will make the tasks secure and resource efficient. We expect that the proposed distributed approach

for ATM

networks

will

become

feasible

and

pervasive

for the

next

generation banking. Established and implemented a my SQL database as our data source.

K L University Computer Science & Engineering

Department of

ATM

DATABASE

MANAGEMENT

SYSTEM

36

CHAPTER 7 REFERENCES 1. Silberschatz, Henry FKorth, S. Sudarshan, “Database System Concepts”, 2003, Fifth Edition, Tata MCGraw 2. Raghu Ramakrishnan, Johannes Gehkke, “Dtabase Management Systems:, 2004, Second Edition, Tata MCGraw Hill. 3. http://www.javaguicodexample.com/conceptualdatamodeling.pdf 4. http://www.aw-bc.com/info/riccardi/database/Riccardi_ch4.PDF 5. www.cs.ucdavis.edu/~green/courses/ecs165a-w11/9-transactions.pdf 6. http://homepages.cwi.nl/~manegold/teaching/DBtech/slides/ch12-2.pdf

K L University Computer Science & Engineering

Department of