Let us understand the steps that need to be followed to come up with scripts as per ER diagram.
- Understand ER diagram - ER diagram might not have all the information required for generating scripts.
- Clarify the doubts - Brainstorming session with Data Architect, Business Analyst and Technical Architects of the project.
- Define the rules - We need to come up with all the rules that need to be enforced at Database level.
- Some common questions which will drive defining rules while generating scripts to create tables.
- What is the primary key column or columns in a table?
- Do we need to enforce unique constraints other than primary key column or columns?
- What all fields can be empty and what all fields cannot be empty?
- What is the scale and/or precision for columns in the table?
- Do we need to apply any rules on the values of a column?
- We need to do this for each and every table in the data model and come up with scripts based on the answers.
As we have already created regions table, let’s take the next table - jobs as example and come up with answers.
- job_id is primary key column and it has to be populated using sequence generated integer. We need to have primary key for this table because other tables such as employees and job_history have to be created as child tables.
- job_title has to be unique and should not be empty
- min_salary and max_salary should not be empty. Also they should not have more than 2 decimals.
- min_salary should not be less than $3000.
- max_salary should not be greater than 6 digit dollar amount ($999,999.99)
- As we are trying to define salary ranges for each job title, we need to ensure that min_salary is less than max_salary.