How to Create a Database Schema

How to Create a Database Schema

A schema is a text-based representation of the database objects that are created in a database from a script. As a Data architect, you generate a database schema from a physical model, using the Forward Engineer option. You can use the Forward Engineer option to create the database schema, regardless of how you created the physical model. You can generate the database schema for a specific subject area or for the main subject area.

For each target database, there is a default forward engineering template. The data modeling tool uses this default template to generate the schema. However, you can customize the default template and use it.

This section describes how Data Architects can create a database schema from an existing model. The process of generating the schema is explained with an example.

Consider the eMovies model, which addresses the following business requirements:

• Manage employee details
• Manage store details
• Manage movie details
• Manage copies of movies
• Manage customer details
• Maintain movie rental details for customers
• Record payment details for rented movies
• Display the credit status of customers
• Generate invoices for customers
• Display overdue details for customers
• Search for customers
• Search for movies that are rented based on rented date, customer phone number, employee number, or movie number

This example describes how to generate a schema for this model in SQL Server 2008 and include the following objects:

• Tables
• Columns
• Relationships
• Indexes

The following diagram illustrates how Data architects can generate a database schema:

Complete the following tasks:

1. Open a model and validate it.
2. Select the schema generation options.
3. (Optional) Use a custom template file.
4. Review the schema generation options.
5. Preview the script.
6. (Optional) Change the schema generation options.
7. Generate the schema.