Schema is a description of how the data stored and how it is organised within the database. It also describes a user friendly description and attribute of the data element. This is useful for non-technical business user can select the data point and generate analytics.
Let us understand this with an simplistic example. Let us say there is a invoice data available in a oracle database. Each invoice records are available in INVOICE table, customer information available in a CUSTOMER table and product information available in PRODUCT table
Schema will contain meta data about
-Tables
-How the tables are joined or related
-Columns in table (description, data type, length)
INVOICE – joins with- CUSTOMER – on CUSTOMER_CODE key
INVOICE – joins with – PRODUCT – on PRODUCT_CODE key
In this case CUSTOMER_CODE is a common column between INVOCE and CUSTOMER table; PRODUCT_CODE is a common column between INVOCE and PRODUCT table.
The columns in each of the table which needs to be available for analysis has to be described in schema, for example
-INVOICE_ID
Description: Invoice ID, Data type: String, Length: 10 characters
-INVOICE_DATE
Description: Invoice Date, Data type: Date, Length: NA
-INVOICE_AMT
Description: Invoice Amount, Data type: Number, Length: 14,3
And so on…