Oracle Assignment

Bank of Hong Kong has 20 branches located in different parts of the island with its main branch in Queensway. The main branch monitors the functioning of branches.

Each branch has a manager. Manager's detail includes the Manager-id, name, starting date and the branch to which attached.

Bank employees are identified by their employee-id values. Employee-id identifies the employee uniquely and its is also used to identify the branch in which he/she is working. Employee name, contact numbers of each employee, employee'ss dependents names, employee-id of the employee's manager are included. The bank also keeps track of the employee's strt date and , thus, lenght of employment.

The bank branch stores customer-id,name and address of the customer. Customers may have accounts and can take out loans. A customer may be associated with a particular bank branch and permitted to do transactions in other branches.

Each customer is given an account card, visa card and a ready credit card. Each card has its card number, which will give the account number and type of card.

The bank offers two types of accounts to each customer, saving and current accounts. An account can be held by more than one customer, and a customer can have more than one account. Each account is assigned a unique account number. The bank maintains a record of each account' balance, and the most recent date on which the account was accessed by each customer holding the account.

Bank branches provde load facility to customers to purchase private houses and cars. Bank branches also have personal loan facilities and personal loan is limited to twice the monthly salary of the customer. Each loan is identified by a unique loan number, loan amount, interest rate and minumum payment for each month. Loan repayment details are kept on monthly basis. Each repayment detail includes payment number, the date and amount for each payment.

*MUST* Use the following to develop the application:

-Oracle 8i database

-Oracle Forms 6i

-Oralce PL/SQL

-on windows 2000 platform

## Deliverables

1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.

2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.

3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).

4) The following:

-Draw an ER diagram for the above system

-Translate the above ER diagram into a Relational database

-Check for normalizations (up to 3NF) (show the workings for 1NF,2NF and 3NF)

-Explain and justify the types of intergrity constraints that will be imposed on the proposed relations.

1-create the above tables. Add extra attributes and justify them.

2-insert 10-15 rows of data in each table. The insert statement must be stored in a script file. Ensure that the your data supports the queries below.

3-Create a single row - list of Cusomters Form

4-Create a multi row - loan particulars

5-Create a master detail relationship between the 1) customer and saving account 2)Employee and Bank Branch

6-Illustrate how to do the follwoing queries:

-List the detail of customers whose balance is >$2000

-List the details of credit cards issued to the customer during the year 2002

-List the bank branches issued loans to customers and the total loan amount

-List the details fo customers whose balance is greateer than the average balance of all the customers.

-Determine the years of service fo the Employees in the bank

-List the number of employees working in each branch

7-Create a report showing the details of the loan repayment collected during the period January to June 2003

8-Create a report showing the details of customers who have taken housing and card loans in the year 2002.

-Produce a reoprt which documents the design an dimplementation process and includes the following:

a) A listing showing the data for each table (based on your stored script)

b) Screen shots of the forms in tasks 3,4,5

c)Screen shots of the query input and results for each of the queries in task 6.

d) Screen layout of the rports produced for task 7 and 8

## Platform

*MUST* Use the following to develop the application:

-Oracle 8i database

-Oracle Forms 6i

-Oralce PL/SQL

-on windows 2000 platform

Habilidades: Engenharia, MySQL, Oracle, PHP, Arquitetura de software, Teste de Software

Veja mais: diagram bank branches located, working for oracle, the private bank, the month of january, sql to oracle, sql oracle pl sql, sql in pl sql, sql code oracle, software engineering employment, pl sql statement, pl sql software, pl sql in oracle, pl sql documents, pl sql and oracle, pl sql and, package sql oracle, package pl sql oracle, package in oracle pl/sql, package design manager, oralce pl sql, oracle sql pl, oracle sql package, oracle sql documents, oracle forms source code, oracle create type in package

Acerca do Empregador:
( 1 comentário ) Hong Kong

ID do Projeto: #2992731