# working on a databases discussion question and need an explanation and answer to help me learn.

Learning Goal: I’m working on a databases discussion question and need an explanation and answer to help me learn.I need help with this one Sql Question single line query The MySQL DB environment does not have a DB space estimator tool for calculating the total amount of DB storage needed for x rows of data in a table or y index entries in an index.The calculations of space estimation COULD be done by hand …, but space estimation should be automated. It would be part of a suite of physical DB design tools (as exists in more advanced DB systems)Note: Material beyond the scope of 134 would implement DB block space calculations as a “DB stored procedure” callable with appropriate arguments.You must implement an automated Bfr calculation for InnoDB row storage. (Specifically, you must calculate a Bfre value). Your code is limited to only one SQL query (refer to as “Q4”) that uses some MySQL session user-defined (session-temporary) variables in subexpressions in that query.All calculations have unit size bytes.A session-temporary user-defined variable and an initial value are defined using:set @variableName = value; < == Semicolon required since this is interpreted as an MySQL statementAfter its definition, a variable’s value is referenced by: @variableName Session variable values to useIn this problem “overhead” means storage space occupied by DB internals, thus not available for row storage.blockSize: 16384; — Default InnoDB (both index and DB) block sizerowSize: 256; — Assume fixed row sizeblockMetaData: 128; — Block metadata total = header + tail = 120+8rowLinkage: 2*(@blockSize/@rowSize); — Total row separator overhead (= 2 bytes per row)You define value of totalBlockOverhead (means total space unavailable for row storage)ff: (15/16) — multiply the (actual bytes available for row storage) by ff < == this is the Bfre value Required Bfre calculation steps to implement in script codeStep 1 – Define the constants (i.e., user-defined variables) discussed above using MySQL “user-defined” variables. A user-defined variable’s value can appear in an MySQL query as part of a < == Per SQL standard doc. for SELECT target itemsStep 2 – Code a single SQL query Q4 that calculates Bfre using the Step 1 values.Q4 will be a very simple query of the form: select ; ( will involve sub-expressions using session variables and nested parentheses)Some of the calculations for final the Bfre result must discard numeric fractions.The MySQL built-in function for discarding a fraction is truncate(expression,n), where expression is a numeric expression value with possibly a fraction part, and n specifies the number of digits to the right of the decimal after expression has digits truncated (if any). For example, truncate(207.54, 0) is 207.In your MySQL calculations, both the Bfr value and the Bfre value must be integers.Code Q4 based on module P3’s discussion on Bfr re-definition, slide#20. This provides a more accurate Bfr calculation vs. slide#14 using the MySQL user-defined variables. = > your final SQL one-row result must have the following row schema:Note the directions below under x, y, and z, describing relevant user-defined variables.+———————+———————–+———-+| totalBlockOverhead | availRowSpace | bfre |+———————+———————–+———-+| x | y | n | <== All numeric values must be type int+———————+———————–+———-+ x defined usingy defined using ff is a factorblockMetaData & blockSize and the in the expressionrowLinkage expression for x for n

Requirements: 1-2