Sample Data Analysis

Database: Sample Data Analysis

For the sample data provided in the previous page table, as a Data Modeler you have to design logical data model, physical data model and generate DDL scripts. In order to do the above tasks, you need to create the following:

  • Create 3 tables GENDER_LKP, DEPARTMENT_LKP, EMPLOYEE_DTL.
  • Assign correct datatypes to the columns.
  • Create constraints like primary key, unique key, null, not null, check to the columns.
  • Assign correct PHYSICAL names for tables and columns.
  • Select GENDER_LKP, DEPARTMENT_LKP as parent tables and EMPLOYEE_DTL as the child table and connect them.
  • Even though column “DTTM_STMP” has not been present in the sample data, you have to add “DTTM_STMP” to all tables to know the date and time on which a record is inserted or updated.

GENDER_LKP Analysis

  • Since ‘male’ and ‘female’ values will be repeated for several records in EMPLOYEE_DTL table, you have to design this lookup for saving disk space. Sometimes the detail table may not show the exact business requirements also.
    E.g. Since the example data contains both genders; you know that there are two genders. What would have happened if all records were male? By seeing the sample data, you might have come to a conclusion that there are only ‘males’ allowed as employees. By discussing with Business Analyst, you would come to know that both genders are allowed. For this purpose, you design a lookup table, which identifies the business requirements also.
  • You have to assign NOT NULL constraint for all columns since lookup tables in general should not have null value.
  • You have to create a column Gender Code, which is not present in sample data and this column should be assigned Primary Key to validate the detailed data in EMPLOYEE_DTL table.

Sample Source Data

Entity NameTable NameAttribute NameColumn NameDatatypeNull OptionPrimary KeyForeign Key
Gender LookupGENDER_LKPGender CodeGNDR_CDVARCHAR2(10)NOT NULLYesNo
Gender LookupGENDER_LKPGender DescriptionGNDR_DESCVARCHAR2(50)NOT NULLNoNo
Gender LookupGENDER_LKPDateTime StampDTTM_STMPDATENOT NULLNoNo

Analysis of DEPARTMENT_LKP:

  • Since ‘Dept No’ and ‘Dept Desc’ values will be repeated for several records in EMPLOYEE_DTL table, you have to design this lookup for saving the disk space.
  • You have to assign NOT NULL constraint for all columns since in general, lookups should not have null value.
  • You have to assign ‘Dept No’ as the primary key to validate the detailed data in EMPLOYEE_DTL table.

Sample Source Data

Entity NameTable NameAttribute NameColumn NameData TypeNull OptionP KeyF Key
Department LookupDEPARTMENT_LKPDepartment NumberDPTMT_NUMNUMBER(2)NOT NULLYesNo
Department LookupDEPARTMENT_LKPDepartment DescriptionDPTMT_DESCVARCHAR2(50)NOT NULLNoNo
Department LookupDEPARTMENT_LKPDateTime StampDTTM_STMPDATENOT NULLNoNo

Analysis of EMPLOYEE_DTL:

  • As of now, this example entity contains only sample of 2 records. In real time, this entity would store millions of records. In order to retrieve the data in a faster way, you have to create an additional column EMP_DTL_ID in EMPLOYEE_DTL and assign it as primary key.
  • In our example, column EMP_DTL_ID will be populated through sequence initiated by a trigger and you can see the sequence code and trigger code in later sections.
  • Even though SSN can be added as a primary key in EMPLOYEE_DTL table, you have to add a new column EMP_DTL_ID as the primary key to ensure the fastest retrieval of data.
    Tip:Wherever it is required, you have to create new columns.

Sample Source Data:

Entity NameTable NameAttribute NameColumn NameData TypeNull OptionPrimary KeyForeign Key
Employee DetailEMPLOYEE_DTLEmployee Detail IdentifierEMP_DTL_IDNUMBERNOT NULLYesNo
Employee DetailEMPLOYEE_DTLDepartment NumberDPTMT_NUMNUMBER(2)NULLNoYes
Employee DetailEMPLOYEE_DTLGender CodeGNDR_CDVARCHAR2(10)NOT NULLNoYes
Employee DetailEMPLOYEE_DTLFirst NameFRST_NMVARCHAR2(30)NOT NULLNoNo
Employee DetailEMPLOYEE_DTLLast NameLST_NMVARCHAR2(30)NOT NULLNoNo
Employee DetailEMPLOYEE_DTLMiddle NameMDLE_NMVARCHAR2(30)NULLNoNo
Employee DetailEMPLOYEE_DTLSSNSSNVARCHAR2(11)NOT NULLNoNo
Employee DetailEMPLOYEE_DTLBirth DateBRTH_DTDATENOT NULLNoNo
Employee DetailEMPLOYEE_DTLSalary AmountSLRY_AMTNUMBER(7,2)NOT NULLNoNo
Employee DetailEMPLOYEE_DTLDateTime StampDTTM_STMPDATENOT NULLNoNo

Leave a Reply

Your email address will not be published. Required fields are marked *