You are here: Home Data Modeling & Database Database: Sample Data

---

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
Melinda	J	Schipper	765-43-21	Female	20-APR-1973	5000
Kevin	A	Schulte	123-45-67	Male	13-OCT-1969	5000	10	IT

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.
• 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.

Please proceed to next page for continuation.

---

Oracle Database Objects       Sample Data Analysis: Continued...

---