07-08-08, 10:16 #1Registered User
- Join Date
- Jul 2008
Unanswered: creating a view for balance in sql
my project is about creating a database for a bank.i attached the er schema to this post so that it would be more helpful. here is also the description of the project:
The CIB bank issues different types of Credit Cards to its customers. These types include:
1- CIB Master
2- PCC Personal Credit Card for a normal account
§ The CIB Mastert card allows the customer to withdraw cash and/or pay his/her shopping with a debit balance to a certain defined limit. 5% of the debit balance must be paid by the customer through a deposit transaction on the 25th of the month, and the remaining debit amount is subject to an interest rate of 12%. The CIB Master card is the only one which allows the customer to pay his/her transactions with any foreign currency; the amount of transaction is converted into its equivalent local currency using the current exchange rates.
§ The PCC (Personal Credit Card) for a normal account allows the customer to withdraw and deposit cash transactions on the customer’s account through the ATM machines. This credit card allows only withdraw transactions as long as there is enough balance in the account. The card allows the customer to pay his/her shopping and restaurants charges as well.
§ A customer is a named person and is allowed to have one or more accounts in the bank. A person is described by an ID (unique), name, and address, phone numbers, and an Email. Users – of that application – usually refer to name of a person by Family name and First name.
§ Each Credit Card is identified by a unique number, and has a type, a holder, a limit, an expiry date, payment terms, and a set of conditions. It is required to know how many Credit Cards are issued for each Card type. It is required to keep track of all the transactions for each Credit Card for a specific period. Each transaction is identified by a unique number, and has a type (debit or credit), a date, an amount, and the recipient (if any). Monthly, the bank issues an Account Statement for each Credit Card containing all transactions during the period and the final balance at end of that period.
§ For each Account, there may well be many Cards authorized. An owner of an account may hold a Credit Card on his/her account and may as well issue other Credit Cards for members of his/her family on the same account (a person may have an account, with a Card for himself, one for his wife, and others for his adult children).
The CIB bank needs to have a robust database application to manage and control all the information of the Credit Cards activities. The required application must support – at least - the following functions:
§ Managing Customers’ data (Add, modify, delete, retrieve, ... etc.).
§ Managing Accounts’ data.
§ Managing Credit Credits’ data.
§ Managing Credit Cards’ transactions.
§ Producing the Credit Card Account Statement
Complete the given data if necessary and then use Oracle development tools to design and implement an database application covering the previously mentioned functions.
Hand in a report including:
1- Brief description of the system analysis and design:
§ Required functions.
§ Basic entities to be represented to support each function.
§ Required data items to be represented for each basic entity.
§ Basic relationships linking the different entities.
2- The Conceptual Database Design using the Entity-Relationship (ER) Model.
3- Database design description:
§ Required database relations, relational constraints, ... etc.
4- Application description:
§ Tables’ structure.
§ Forms (screens).
5- Sample runs on the implemented functions.
6- Your comments on the assignment and on the used package.
8- A CD containing all database files and other objects with short instructions to run the application.
i need to create 2 views to get the balance and the late payments, respectively, using sql plus. plz reply asap. thanks for your times.
07-08-08, 10:35 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
Do you expect us to do the heavy lifting so you can be paid for our efforts?
You're On Your Own (YOYO)!You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
07-08-08, 14:44 #3Registered User
- Join Date
- Jul 2008
reply to yoyo
this is a part of a project on a database course in the curriculum of my university sir and not a task on a job. do u c that this schema is really a bank's schema? that's really odd.
07-08-08, 15:51 #4Lost Boy
Provided Answers: 4
- Join Date
- Jan 2004
- Croatia, Europe
You have attached an upside-down scanned copy of your notebook. I thought I have seen everything, but some people manage to dazzle me over and over again.
As of your question: the problem seems to be too wide; I believe you'll hardly find someone who is willing to decipher your ER-model, spend fair amount of his/her free time in order to provide a CREATE VIEW statement you are looking for.
Basically, that's not the way a forum works. It is YOU who should do that and ask a question about a specific problem (for example, "my CREATE VIEW statement returns ORA-xxxxx error; here are CREATE TABLE and INSERT INTO sample data statements along with CREATE VIEW I have used. Where did I go wrong?")
I'm sorry, but that's about everything I have to say about your problem at the moment. However, you might get the answer you are looking for, just if you wait long enough.
07-09-08, 05:25 #5Registered User
Originally Posted by dbaman11
- Join Date
- Dec 2007