DB Life Cycle   «Prev  Next»

Lesson 8 Database design tools
Objective Explain the Use of CASE tools in Database Design

Database Design Tools

CASE (computer-aided-software-engineering) tools are software packages used to design and implement an information system. There are many CASE tools available on the market, ranging from easy-to-use (intuitive) to extremely sophisticated (and not at all user-friendly). Database designers find the drawing capabilities of CASE tools especially useful for creating database diagrams.

What is a Case Tool?

A case tool is a class of software that automates the activities involved in various life cycle phases. For example, when establishing the functional requirements of a proposed application, prototyping tools can be used to develop graphic models of application screens to assist end users to visualize how applications will look after development. Subsequently, system designers can use automated design tools to transform the prototyped functional requirements into detailed design documents. Programmers can then use automated code generators to convert the design documents into code. Automated tools can be used collectively, as mentioned, or individually. For example, prototyping tools could be used to define application requirements that get passed to design technicians who convert the requirements into detailed designs in a traditional manner using flowcharts and narrative documents .
As you do more with the UML and the programming gets increasingly mechanical, it becomes obvious that the programming should be automated. Indeed, many CASE tools do some form of code generation, which automates building a significant part of a system. Eventually, however, you reach the point at which all the system can be specified in the UML, and you reach UML as programming language. In this environment, developers draw UML diagrams that are compiled directly to executable code, and the UML becomes the source code. Obviously, this usage of UML demands particularly sophisticated tooling.

CASE Tools available for RDBMS

CASE (Computer-Aided Software Engineering) tools are software applications that support various stages of the software development life cycle. In the context of relational database management systems (RDBMS), there are several CASE tools available that can aid in database design, implementation, and maintenance. Here are some examples:
  1. ERwin Data Modeler: ERwin is a popular CASE tool used for database design and modeling. It allows users to create and modify entity-relationship diagrams (ERDs), and generates SQL scripts for database implementation. ERwin also includes features for database version control, data dictionary management, and database documentation.
  2. SQL Power Architect: SQL Power Architect is an open-source CASE tool that supports database design and modeling for various RDBMS platforms, including Oracle, MySQL, and SQL Server. It provides a graphical interface for designing ERDs, and generates SQL scripts for database implementation. SQL Power Architect also includes features for data profiling, data lineage analysis, and database documentation.
  3. Oracle SQL Developer Data Modeler: Oracle SQL Developer Data Modeler is a CASE tool specifically designed for Oracle databases. It provides a graphical interface for designing ERDs, and generates SQL scripts for database implementation. SQL Developer Data Modeler also includes features for database version control, data dictionary management, and database documentation.
  4. Toad Data Modeler: Toad Data Modeler is a CASE tool that supports database design and modeling for various RDBMS platforms, including Oracle, SQL Server, and MySQL. It provides a graphical interface for designing ERDs, and generates SQL scripts for database implementation. Toad Data Modeler also includes features for database version control, data dictionary management, and database documentation.
  5. DbWrench: DbWrench is a multi-platform CASE tool that supports database design and modeling for various RDBMS platforms, including MySQL, Oracle, and SQL Server. It provides a graphical interface for designing ERDs, and generates SQL scripts for database implementation. DbWrench also includes features for database version control, data dictionary management, and database documentation.
These are just a few examples of the CASE tools available for RDBMS. Each tool has its own features, strengths, and limitations, and the choice of tool depends on the specific needs and requirements of the database project.

Common Diagrams

The most common diagrams are:
  1. Data flow diagrams, which display how data travel through an organization.
  2. Entity-relationship (ER) diagrams, which illustrate business objects[1], their characteristics, and how these objects relate to each other.

Some advanced CASE tools are capable of translating ER diagrams directly into SQL statements. Others are used to help design the user applications that interact with databases. A CASE tool can not design a database. Only a database designer can examine a database environment and decide which business objects and business rules ought to be represented in a database.
A set of rules or conditions describing the business polices that apply to the data stored on a company databases.
The next lesson concludes this module.

Computer-Aided Software Engineering

CASE (Computer-Aided Software Engineering) packages are software packages which include tools to help with database design. The main goal of these packages is to give database designers a way of representing systems that are too complex to understand in their source code or schema[1] based forms. They help automate software development and maintenance tasks and usually contain tools for system analysis, project management, and design.
  1. Uses of CASE Tools in Databases: CASE tools can serve many functions in database design, including:
    • Collecting and analyzing data
    • Designing a data model
    • Feasibility analysis
    • Requirements definition
    • Implementing the database
    • Prototyping
    • Data conversion
    • Generating application code
    • Generating reports
    • Programming and testing
    • Maintenance
  2. Advantages of Case Tools: CASE tools can provide many advantages when used in database design, including:
    • Improved productivity in development
    • Improved quality through automated checking
    • Automatic preparation and update of documentation
    • Encouragement of prototyping and incremental development
    • Automatic preparation of program code from requirements definition
    • Reduced maintenance systems
  3. Disadvantages of Case Tools: However, there are also some disadvantages to using the tools in database design, including:
    • Cost increase
    • Need for specialized training
    • Limitations in flexibility of documentation
    • Inadequate standardization
    • Slow implementation
    • Unrealistic expectations

[1]Business objects: Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.)
[2]schema: A data view which is specified in a language which the DBMS software is designed to understand is called a schema.

SEMrush Software