Chapter 1 Identifying the Applications and the Database Schema

1. Chapter 1 Identifying the Applications and the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

2. In this chapter you will: • Learn how Oracle Forms Builder is integrated into the system development life cycle • Use data flow diagrams to identify applications, processes, and the data needed for the processes • Use entity relationship diagrams to initiate the design of the database schema • Use the data schema diagram to identify the database properties • Learn how Forms Builder uses the Oracle9i database Oracle9i Developer: Developing Web Applications with Forms Builder

3. Integration of Oracle Forms Builder into the System Development Life Cycle • The system development life cycle (SDLC) consists of a series of phases that describe the life of an information system. • Phases are: • Project identification and selection • Project initiation and planning • Analysis • Logical design • Physical design • Implementation • Maintenance Oracle9i Developer: Developing Web Applications with Forms Builder

4. Integration of Oracle Forms Builder into the System Development Life Cycle • Rapid application development (RAD) is a subset of the SDLC phases. • RAD enables a developer to implement a system in a shorter time period than traditional methods. • Forms Builder 9i is a development tool that supports both the traditional and RAD approaches. Oracle9i Developer: Developing Web Applications with Forms Builder

5. Integration of Oracle Forms Builder into the System Development Life Cycle • Systems have three types of applications: • Forms or screens • Reports • Processes that run without being seen by a user • Forms Builder 9i is used to develop forms. • Forms Builder 9i allows the developer to prototype forms early in the SDLC. Oracle9i Developer: Developing Web Applications with Forms Builder

6. Integration of Oracle Forms Builder into the System Development Life Cycle • Prototyping is a RAD approach that helps the designer identify system requirements. • Designer 9i is another Oracle tool used in the analysis and design phases. Oracle9i Developer: Developing Web Applications with Forms Builder

7. Integration of Oracle Forms Builder into the System Development Life Cycle • Designer 9i is a computer-aided software engineering (CASE) tool. It is used to: • Develop data flow diagrams • Create entity relationship diagrams • Generate data schema diagrams • Generate data definition language (DDL) commands needed to create the database objects • Generate form modules containing the data items needed by the form Oracle9i Developer: Developing Web Applications with Forms Builder

8. Integration of Oracle Forms Builder into the System Development Life Cycle Oracle9i Developer: Developing Web Applications with Forms Builder

9. Using Data Flow Diagrams to Identify Application, Processes, and Data • Before a developer can begin a form, the developer must first identify the following: • The needed forms • The data elements needed on the form • The data elements that are output from the form • Business rules that affect the data • Tables that support the data structure Oracle9i Developer: Developing Web Applications with Forms Builder

10. Using Data Flow Diagrams to Identify Application, Processes, and Data • A data flow diagram is a good place to start. • The purpose of a data flow diagram is to identify the processes in a system and the data that flows in and out of the process. • Processes that require interaction by an operator will require a form. Oracle9i Developer: Developing Web Applications with Forms Builder

11. Using Data Flow Diagrams to Identify Application, Processes, and Data • Data flow diagrams consist of four symbols: • External entity (source or sink) – Something outside the system that sends in or receives data • Process – An action performed in the system • Data flow -- Data moving into or out of a process • Data store -- Data at rest Oracle9i Developer: Developing Web Applications with Forms Builder

12. Using Data Flow Diagrams to Identify Application, Processes, and Data • A form’s data needs can be identified by reviewing the process’ incoming and outgoing data flows. • Data flow documentation consists of the data elements contained in the flow as well as the entity associated with the data elements. Oracle9i Developer: Developing Web Applications with Forms Builder

13. Using Data Flow Diagrams to Identify Application, Processes, and Data Oracle9i Developer: Developing Web Applications with Forms Builder

14. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Before a form can be built the database must be designed. • An entity relationship diagram (ERD) is the tool used to design and depict the conceptual design of the database. • Database elements are associated with an entity. • The set of entity attributes describes or models the entity. Oracle9i Developer: Developing Web Applications with Forms Builder

15. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Entities are then associated with other entities. This shows how they are related. • Entities will eventually be turned into a table. • An entity instance relates to a record in a table. Oracle9i Developer: Developing Web Applications with Forms Builder

16. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • There are three type of entities: • Regular entity • Weak entity • Associative entity Oracle9i Developer: Developing Web Applications with Forms Builder

17. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Regular entities exist on their own. (Student) • Weak entities must have a parent. (Courses offered, parent is Courses) • Associative entities relate two entities in a many to many relationship. (Courses Taken relates Students with the Courses Offered) Oracle9i Developer: Developing Web Applications with Forms Builder

18. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

19. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Regular and weak entities are often shown on a form in a master-detail layout. • The master form section contains unique entity instances. (Student) • The detail form section contains repeating weak entity instances. (Courses Taken) • Records in the master-detail layouts are related through primary and foreign keys. Oracle9i Developer: Developing Web Applications with Forms Builder

20. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • An entity must have an identifier. The identifier must have these features: • Value must be unique • Value must not be changeable • Identifier must always contain a value • Regular entities should have an identifier consisting of one data element. Oracle9i Developer: Developing Web Applications with Forms Builder

21. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Weak entities generally have an identifier consisting of multiple data elements. One of which is the foreign key. • A foreign key is a value that is the primary key in another entity. • Entities represent the actual databases tables. Oracle9i Developer: Developing Web Applications with Forms Builder

22. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Entities (and tables) are related to each other. • ERDs depict three types of relationships. • Unary - an entity is related to itself (i.e. an Employee is managed by another employee) • Binary - an entity is related to one other entity (i.e. a Student has Courses Taken) • U-nary - an entity is related to several different entities Oracle9i Developer: Developing Web Applications with Forms Builder

23. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

24. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Entity or table relationships have two important properties. These are: • Cardinality • Ordinality Oracle9i Developer: Developing Web Applications with Forms Builder

25. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Cardinality indicates the number of related instances. A Student can have many Courses Taken instances. However, a Courses Taken instance can only have one Student. • Ordinality indicates whether an instance of the related entity must occur. A Student instance can occur with a Courses Taken instance. However, a Courses Taken instance cannot exist without a Student instance. Oracle9i Developer: Developing Web Applications with Forms Builder

26. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

27. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Designer 9i has the ability to create a data schema diagram from the entity relationship diagram. • Designer 9i has forms used to document the data elements in each entity. • Designer 9i has forms used to document the entities that support the data flow. • Designer 9i takes the entity information and creates the data schema diagram, the tables, and the table constraints. Oracle9i Developer: Developing Web Applications with Forms Builder

28. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

29. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

30. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

31. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

32. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema Oracle9i Developer: Developing Web Applications with Forms Builder

33. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Forms Builder has wizards that grab information from the database when creating forms. • Forms Builder gives form items their names based upon the corresponding database table column. • Forms Builder sets the data type and length of all data items based on the database table columns. Oracle9i Developer: Developing Web Applications with Forms Builder

34. Using Entity Relationship Diagrams to Initiate the Design of the Database Schema • Forms Builder ensures NOT NULL constrained form items have the Required property set to YES. • Forms Builder identifies the primary and foreign key columns when creating master-detail forms. Oracle9i Developer: Developing Web Applications with Forms Builder

35. Where You Are and Where You’re Going • You have briefly reviewed the system development life cycle. • You have seen how Oracle Forms Builder fits within this life cycle. • You have seen how data flow diagrams are used to identify applications processes and data. • You were introduced to entity relationship diagrams and data schema diagrams. • You saw how Forms Builder uses the Oracle9i database. Oracle9i Developer: Developing Web Applications with Forms Builder

36. Where You Are and Where You’re Going • In the next chapter you will be introduced to Forms Builder. • You will learn how to build and execute a form. • You will learn how to use wizards to create a simple form. Oracle9i Developer: Developing Web Applications with Forms Builder