This computer science problem involves algorithmic thinking and programming concepts. The solution below explains the approach, logic, and implementation step by step.
ScanSolve AI Answer
You're on a roll — 5. Using an appropriate database management system, create a database called FinanceDB containing the three tables defined above. Populate all the tables taking data from Table 1 above. Step 3: Create the Transaction table. `sql CREATE TABLE Transaction ( TID VARCHAR(10) PRIMARY KEY, TType VARCHAR(50), Amount DECIMAL(10, 2) ); ` Step 4: Create the Operation table. `sql CREATE TABLE Operation ( MID VARCHAR(10), TID VARCHAR(10), ODate DATE, PRIMARY KEY (MID, TID) ); ` Step 5: Populate the Member table. `sql INSERT INTO Member (MID, MName, MAdd) VALUES ('M10', 'Mbong', 'Buea'), ('L05', 'Lyonga', 'Bamenda'), ('O20', 'Otia', 'Wum'), ('O15', 'Omar', 'Garoua'), ('S04', 'Susan', 'Douala'), ('L12', 'Linus', 'Bertoua'), ('G11', 'Gertrude', 'Yaounde'), ('T07', 'Tita', 'Maroua'); ` Step 6: Populate the Transaction table. `sql INSERT INTO Transaction (TID, TType, Amount) VALUES ('T01', 'Loan', 100000.00), ('T02', 'Savings', 50000.00), ('T03', 'Loan', 200000.00), ('T04', 'Savings', 75000.00), ('T05', 'Loan', 150000.00), ('T06', 'Savings', 120000.00), ('T07', 'Loan', 300000.00), ('T08', 'Savings', 90000.00), ('T09', 'Loan', 250000.00), ('T10', 'Loan', 50000.00), ('T11', 'Loan', 100000.00), ('T12', 'Savings', 60000.00); ` Step 7: Populate the Operation table. `sql INSERT INTO Operation (MID, TID, ODate) VALUES ('M10', 'T01', '2023-01-15'), ('L05', 'T02', '2023-01-20'), ('O20', 'T03', '2023-02-01'), ('M10', 'T04', '2023-02-10'), ('O15', 'T05', '2023-02-15'), ('S04', 'T06', '2023-03-01'), ('L12', 'T07', '2023-03-05'), ('G11', 'T08', '2023-03-10'), ('T07', 'T09', '2023-03-15'), ('M10', 'T10', '2023-03-20'), ('L05', 'T11', '2023-03-25'), ('O20', 'T12', '2023-04-01'); ` 6. Implement the relationship in the database created. To implement the relationships, we add foreign key constraints to the Operation table, linking MID to Member and TID to Transaction. Step 1: Add foreign key constraints to the Operation table. `sql ALTER TABLE Operation ADD CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES Member(MID); ALTER TABLE Operation ADD CONSTRAINT FK_TID FOREIGN KEY (TID) REFERENCES Transaction(TID); ` 7. Create a query to display members who have taken loans. Your query should include the amount loaned and the date loaned. Save the query as LoanF. Step 1: Write the SQL query to select the required information. `sql SELECT M.MName, M.MAdd, T.Amount, O.ODate FROM Member AS M JOIN Operation AS O ON M.MID = O.MID JOIN Transaction AS T ON O.TID = T.TID WHERE T.TType = 'Loan'; ` Step 2: The query result, if executed, would look like this: ` LoanF +----------+-----------+----------+------------+ | MName | MAdd | Amount | ODate | +----------+-----------+----------+------------+ | Mbong | Buea | 100000.00| 2023-01-15 | | Otia | Wum | 200000.00| 2023-02-01 | | Omar | Garoua | 150000.00| 2023-02-15 | | Linus | Bertoua | 300000.00| 2023-03-05 | | Tita | Maroua | 250000.00| 2023-03-15 | | Mbong | Buea | 50000.00 | 2023-03-20 | | Lyonga | Bamenda | 100000.00| 2023-03-25 | +----------+-----------+----------+------------+ ` Print the Operation table and the LoanF query only. The Operation table content is: ` Operation +-----+-----+------------+ | MID | TID | ODate | +-----+-----+------------+ | M10 | T01 | 2023-01-15 | | L05 | T02 | 2023-01-20 | | O20 | T03 | 2023-02-01 | | M10 | T04 | 2023-02-10 | | O15 | T05 | 2023-02-15 | | S04 | T06 | 2023-03-01 | | L12 | T07 | 2023-03-05 | | G11 | T08 | 2023-03-10 | | T07 | T09 | 2023-03-15 | | M10 | T10 | 2023-03-20 | | L05 | T11 | 2023-03-25 | | O20 | T12 | 2023-04-01 | +-----+-----+------------+ ` The LoanF query is provided above in Step 1 of question 7. --- TASK B: SPREADSHEET (17 MARKS) 1. Enter a formula in cell F2 to calculate the total savings for Mbong. Copy the formula to get the total savings of other members. Write down the formula used in cell F5. Assuming the spreadsheet has columns for Member Name, Savings Amount, etc., and Mbong's data is in row 2. Let's assume savings amounts are in column C and member names in column B. Step 1: Formula for F2 (Total savings for Mbong). If the savings amounts are in a range like C2:C100 and member names in B2:B100, and Mbong's name is in B2, the formula would be: =SUMIF(B:B, B2, C:C) This formula sums values in column C where the corresponding value in column B matches the name in B2. Step 2: Formula for F5 (Total savings for a member in row 5). When copied down, the formula would adjust. If B5 contains the member's name for row 5, the formula in F5 would be: =SUMIF(B:B, B5, C:C) The formula used in cell F5 is: =SUMIF(B:B, B5, C:C) 2. Enter a formula in cell G12, to determine the interest on the loan taken by Mbong at the end of the 12 month period. Also compute the interest on loan for all the other members. Write down the formula used in cell G12. The problem states: "Simple Interest = Principal amount Rate Time". It also states: "Cat1 members pay an interest rate of 1.5% on the loan taken, while Cat2 members pay 2.5%." We need to know which category Mbong belongs to and where the loan amount is. Let's assume: • Loan amount for Mbong is in cell D2. • Member category (Cat1/Cat2) is in cell E2. • Time is 12 months (1 year). Step 1: Determine the interest rate based on the member category. If E2