Fill This Form To Receive Instant Help
Homework answers / question archive / Experiment 6: ODBC/JDBC?optional? Goal To practice how to access the database from applications with ODBC/JDBC or other methods
To practice how to access the database from applications with ODBC/JDBC or other methods.
Hint: when configuring ODBC data source, you need to pay special attention to whether it is 32-bit or 64 bit. The configuration information should be consistent with the 32 / 64 bit of the application.
Hint: the example program is implemented by MFC or CSharp language, and can run normally under vs2019. You can also use your favorite programming language or other IDE to re implement, but the function must be the same as the given example program.
The scenario description of specific banks is as follows:
Suppose that there are five basic entities for the business of Bank C: customers, bank cards, financial products, insurance and funds. For these entities, it is assumed that bank C has the following businesses:
A customer can apply for multiple bank cards.
A customer can purchase multiple financial products, and each type of financial product can be purchased by multiple customers.
A customer can purchase multiple funds, and the same type of fund can be purchased by multiple customers.
A customer can purchase multiple insurance, and the same type of insurance can be purchased by multiple customers
According to the business relationship of Bank C, the following ER diagram is obtained.
? 8-1 ER diagram of C Bbank System Finance
Customer: customer number, customer name, customer ID, customer phone number, customer login password
Bank card: bank card number, bank card type, customer number, balance
Financial products: product name, product number, product status, product price, time period, close start date, sale start date, product discription
Insurance: Insurance name, insurance number, insurance price, applicable population, insurance period, product status
Fund: fund name, fund number, fund type, fund price, risk level, fund manager, fund status
Customer table: customer (C_ ID?C_ NAME?C_ ID_ CARD?C_ PHONE?C_ PASSWORD?
Card table: Bank_ card?B_ NUMBER?B_ TYPE?B_ C_ ID, B_ BALANCE?
Financial products table:finances_ product?P_ NAME?P_ ID?P_ DESCRIPTION?P_ PRICE?P_CLOSE_DATE, P_SALE_DATE, P_STATUS, P_ YEAR?
Insurance table:insurance( I_ NAME?I_ ID?I_ PRICE?I_ PERSON?I_ YEAR?I_ STATUS?
Fund table: fund (F_ NAME?F_ ID?F_ TYPE?F_ PRICE?RISK_ LEVEL?F_ MANAGER, F_STATUS?
Financial products purchase table: customer number, financial product number, purchase time,purchase quantity, cumulative income, payment bank card number
Insurance purchase table: customer number, insurance number, insurance time, purchase quantity, cumulative income, payment bank card number
Fund purchase table: customer number, fund number, time of fund purchase, purchase quantity, cumulative income, payment bank card number
Financial products purchase table: C_finances(c_ ID, P_ ID, P_ TIME, P_ AMOUNT, P_ INCOME, B_ C_ ID)
Insurance purchase table: C_ insurance (C_ ID, I_ ID, I_ TIME, I_ AMOUNT, I_ INCOME, B_ C_ ID )
Fund purchase table: C_ fund (C_ ID, F_ ID, F_ TIME, F_ AMOUNT, F_ INCOME , B_ C_ ID)
Based on the DDL file of the database and the prepared data, complete the following functions:
C_ id ASC, f_ id ASC, f_ amount DESC
On December 1, 2018, the bank will sell one-year financial product, No. 4, starting closing time is 2019 / 1 / 6, price is 8.0 yuan per share, status of 0 (0 means normal).
Hint: it is required to insert the appropriate record into table finances_product.
Suppose there are three deals:
a. At 13:00:00:00 on January 5, 2019: customer 3 purchased 1000 shares of No. 4 financial product with bank card '62220213020000002':
b. At 14:00:00:00 on January 5, 2019: customer 5 purchased 1000 shares of No. 4 financial product with bank card '62222021302020200003'
c. 2015 / 1 / 5 15:00:00: customer 5 purchased 500 shares of financial product No.4 with bank card '62220213020000003'
Hint: you need to give insert some appropriate purchase records into the table C_finances, and the total purchase amount is calculated according to the purchase amount and price(unit price), and the corresponding amount is deducted from the purchased bank card. Pay attention to consider the occasion of insufficient balance of debit card, which may lead to purchase failure. The whole process involves multiple tables, so you can consider using procedures to encapsulate the whole process. In addition, the balance of the bank card should be updated? if the balance of the debit card is insufficien, the update would be failed.
Assuming that the financial product expired on January 6, 2020, the income is assumed to be 5%. On January 6, 2020, the bank will cash in the proceeds for all users who purchase No. 4 financial product: calculate the income of all customers who purchased the product, and add the total amount of principal + income to the bank card balance.
Hint: according to the specified rate of income, calculate the balance (income + principal) of each purchase transaction of No. 4 financial product. The balance of the transaction is then added to the balance of the payment card of the transaction.
It is assumed that the bank has finished processing the income cashing of financial product No.4. Now the bank wants to take the financial product off the shelf. Note that the foreign key constraints should be considered when deleting the financial product record.
Hint: you’d better backup the data before delete. For example, save the historical purchase records of No. 4 financial product to a separate history table, and then delete the relevant data of No.4 financial product from table C_ finances. When delete No.4 financial product from table finances_product, the foreign key constraint should be considered, and the purchase record of product 4 should be deleted first. We can also consider adding a column to finances_ product, to make the product property be invalid instead of deleting it completely.