Database: Sample Data
With a simple example of designing a data model with “employee related information”, most of the data structures can be easily understood. So we will try to design a data model using the sample data given below and will explain the data modeler's involvement in the database environment.
Sample Source Data
| First Name | Middle Name | Last Name | SSN | Gender | Birth Date | Salary | Dept No | Dept Desc |
| Kevin | A | Schulte | 123-45-67 | Male | 13-OCT-1969 | 5000 | 10 | IT |
| Valencia | D | Schipper | 765-43-21 | Female | 20-APR-1973 | 5000 |
Note: If you go through our topics provided under the Data Modelling Section, you will have a brief idea about how to design a data model.
Database: Sample Data Analysis
For the sample data provided in the above 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.
- Eventhough 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.
Please proceed to next page for continuation.
Sample Data Analysis: Continued...