Database Design

Detailed Process on Designing Arc Database

Data Byte Design

Database Design is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a Data Definition Language, which can then be used to create a database. A fully attributed data model contains detailed attributes for each entity. The term database design can be used to describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views. In an object database the entities and relationships map directly to object classes and named relationships. However, the term database design could also be used to apply to the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the database management system (DBMS).

E

S I

G N Designed By Steve Gershen

Data Btye Design

System Analysis & Design Subsystem Data Dictionary (DD) Domain Generator (DOMGEN)

SQL Generator (SQLGEN) Data Layer Generator (DLGEN)

System Analysis & Design Subsystem

Data Btye Design

System Analysis & Design Subsystem



Helps in system analysis and design process from building ERD to database schema generation



Based on System Architect CASE tool



Resides on PC/Windows platform



Use is optional

Data Btye Design

Main

EAST REGION DATABASE Service Orders

Data Byte Interface

Domain definitions

Data Btye (DATA ENTRY)

Valid values and groups Database objects Tables, views Referential integrity rules Key Check, nullity and unique constraints

Service Orders

WEST REGION DATABASE

Main

Generates appropriate definitions for each supported programming environment Basic domain manipulation methods are: Single field declaring Field array declaring

Set valid value Check field validity Move from “C” to host variable Move from host variable to “C”

Data Dictionary

Check against valid value

Main

Input “Create Table” statements Use Domains to define field type Output Structure declaration Macro to declare host variables Macro to copy host variables to structure Macro to copy structure to host variables Macros that define different parts of SQL statement related to table structure Column list for SELECT statement Update list of UPDATE statement Insert list for INSERT statement

Main

Create Table Customer ( CUST_NAME copy “NAME.DOM” CUST_ID copy “ID.DOM” CUST_ADDR copy “ADDR.DOM” );

STRUCT{ Name_D (CustName) ID_D (CustID) Addr_D (CustAddr) } Customer;

# define SELECT_CUSTOMER SELECT Cust Name Cust ID Cust Addr FROM customer INTO CustName_H CustID_H CustAddr_H

Main

  

Defines entity attributes Provides mapping to physical segment/table Specifies data access methods

DATA DICTIONARY

Billing Account Service Customer Entity Definition

ORACLE Templates DB2 Templates Informix Templates

Data Layer Services Generated Code

Main

Generated Data Layer Service • DLEntity CUSTOMER table CUSTOMER ( • GROUP KEY ( • CUST_ID • ) • GROUP DATA ( • FIRST_NAME • LAST_NAME • ) • ) • DLMethod GET-CUSTOMER source DB2 ( • • • •

STATEMENT GetByKey ( SELECT ALL FROM CUSTOMER WHERE CUST_ID= :CUST_ID )

• GET-CUSTOMER USING • AR02-I-KEY, • AR03-O-REC, • AR04-O-STATUS.

AR01-I-CONTEXT,

• MOVE CUST_ID IN AR02-I-KEY TO • CUST_ID IN SQL-REC • EXEC SQL • SELECT • CUST_ID, • FIRST_NAME, • LAST_NAME • INTO • :SQL-REC.DATA.FIRST-NAME • :SQL-REC.DATA.LAST-NAME • FROM CUSTOMER • WHERE CUST_ID = :SQL-REC.PK. CUST_ID • END-EXEC

• <<<METHOD>>> USING • AR02-I-KEY, • AR03-O-REC, • AR04-O-STATUS.

• • • •

AR01-I-CONTEXT,

<<>> IN AR02-I-KEY TO <<>> IN SQL-REC >>>

• <<<STATEMENT GetByKey >>> • PERFORM ERROR-HANDLER • <<>> IN SQL-REC TO • <<>> IN AR03-O-REC • EXIT

• PERFORM ERROR-HANDLER

Input Data

• • • • • •

MOVE CUST-ID IN SQL-REC TO CUST-ID IN AR03-O-REC MOVE FIRST-NAME IN SQL-REC TO FIRST-NAME IN AR03-O-REC MOVE LAST-NAME IN SQL-REC TO LAST-NAME IN AR03-O-REC

• EXIT.

Template