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

Experiment 6: ODBC/JDBC?optional?  Goal To practice how to access the database from applications with ODBC/JDBC or other methods

Computer Science

Share With

---

1. Experiment 6: ODBC/JDBC?optional?
   1.  Goal

To practice how to access the database from applications with ODBC/JDBC or other methods.

 

1. 2. Content
1. ODBC data source configuration and program debugging. (20 points)

1. To configure an ODBC data source, the data source name is: student, which contains the S table (student information table).

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.

2. To understand ODBC programming, read and run the program example  (MFC or CSharp code) given in the experiment, it is required to write your own understanding of the program, or draw a flow chart of the program, and give the screenshot of the program running results.

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.

2. Refer to the above ODBC example program, use ODBC programming technology, write a simple program, including the database SPJ_ MNG connection, query, insert, modify and delete. (30 points)
3. Comprehensive application experiment of bank finance. (50 points)

1. According to the requirements, write SQL statements of different scenarios for the bank database.
2. Referring to the programming examples of JDBC / ODBC / third-party library, use one of the ways to access the bank database from the application program, and execute the SQL statement of question (1).

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

 

 

 

50. A complete description of the five entities in Bank C is as follows:

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

50. The tables of all entities in the database finance are as follows:

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?

50. The purchase relationships between different entities in Bank C are described as follows:

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

 

50. The corresponding table of purchase relationships in database finance are as follows:

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:

1. A new customer, whose ID number is "610103123456781234", registers at bank C, and applys for a new debit card. Please insert the record of the customer in the customer table and the card table.
2. Add table constraints according to the business needs, and verify them after the constraints are added successfully.

• In the bank card table, financial product purchase table, insurance purchase table and fund purchase table, add the correct foreign key constraints: that is, the customer number is set as a foreign key, and references the customer number in the customer table, and the financial product number, insurance number and fund number refer to the corresponding table respectively.
• In the above basic tables, there are six attributes related to amount or price. In the real life, the amount or price will not be negative. Therefore, for these properties, add constraints whose value is greater than 0.

3. Simulate the following business to write SQL statements for query:

• Query the card number and type information of all bank cards of bank C.
• Query the number of customers owned by bank C.
• query the customer number, name and ID number information of the bank cards owners.
• Statistics the amount of debit cards and credit cards respectively in all the bank cards.
• Query the average insurance price in the insurance table.
• Query the insurance type and price corresponding to the maximum and minimum of insurance price in the insurance table.
• Query the customer number and name whose bank card number is' 62220213020200006 '.
• Query the insurance name and applicable population whose insurance price is greater than the average value in the insurance product.
• Query the total number of financial products released by Bank C, by using the P_ YEAR to group.
• Query the insurance number, insurance name and insurance period applicable to the elderly people.

4. Create a business view based on the following queries:

• Create a view containing customer number, name and ID number of all the customers who have at least one bank card.
• Modify view: on the basis of the original view, only the customers with credit cards are included.

5. Simulate the business changes, people's demand for fund query has increased significantly. Create a composite index on the fund purchase table:

C_ id ASC, f_ id ASC, f_ amount DESC

6. Simulate the business demands, add, delete and modify data:

• A customer with customer number 2, applys to update his phone numbers with '13312345678'.
• Simulate the simplified process of the sale, purchase, settlement and stop saling of No. 4 financial product.

 

• Step 1: The bank sales new financial product.

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.

 

• Step 2: Customers purchase No. 4 financial products (purchase and deduct money from corresponding bank card).

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.

 

• Step 3: The bank cashes the income

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.

 

• Step 4:  The bank stops the financial product No.4

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.

 

7. Use JDBC, ODBC or other third-party library programming to implement the connection to the finance database, and implement the function of step 2 and step 3 in question (6) above.