Oracle10G Developer Suite Oracle 10G Designer By Anna Sidorova

1. Oracle10G Developer SuiteOracle 10G DesignerBy Anna Sidorova

2. Start Oracle Designer Starting Oracle Designer

3. Log into Oracle (Connect) • Enter your username and password • Type ‘oracle10g’ in the Connect String box Starting Oracle Designer

4. Tools • Data Flow Diagrammer • Entity Relationship Diagrammer • Process Diagrammer • Functional Hierarchy Diagrammer • Matrix Designer • Database Design Transformer • Application Design Transformer • Design Editor • Repository Object Navigator • Repository Reports Starting Oracle Designer

5. Oracle Designer Screen To launch a specific click once on a respective icon. Double clicking will cause the application to shut up. Starting Oracle Designer

6. Types of objects • Business unit • used in process modeling • Business function • Used in process modeling, data flow modeling and functional hierarchy modeling • Will have data flows as subordinate objects • Data store • Used in data flow modeling • Will have data flows as subordinate objects • Entity • Has attributes, relationships and primary keys as subordinate objects • Diagram – contains all the diagrams and matrices • External Entities (sources and sinks) • Table definitions, etc Starting Oracle Designer

7. You can view your objects through the Repository object navigator Starting Oracle Designer

8. Using Data Flow Diagrammer • Deals with functions, data stores, external entities and data flows • Each diagram decomposes a specific global function • An organization in general • A specific system • A function within the system • You can include objects that you have already created in other tools or as a part of a different diagram Starting Oracle Designer

9. Oracle Designer Screen Select Dataflow Diagrammer Starting Oracle Designer

10. Dataflow Diagrammer Select New or Open depending on your task Starting Oracle Designer

11. Choose Container Click to select your container Starting Oracle Designer

12. Select or Create a Global Function Starting Oracle Designer

13. Working on a Diagram Starting Oracle Designer

14. Adding elements to the DFD • Using respective icons at the top of the screen, add a new function inside the created frame function • Name your function • Add a data store • Add data flows between external entities, data stores and functions Starting Oracle Designer

15. Starting Oracle Designer

16. You can modify information about a function, data store or data flow by double-clicking on it Starting Oracle Designer

17. Decomposition • To decompose your diagram • Select a function you want to decompose • Right-click on it and select Open Down • A new diagram will open with the selected function as a frame function, elements that interact with this function and global data flows • During the decomposition you can specify you can resolve the global data flows by attaching them to sub-functions • Global data flow will get automatically removed from the higher level diagram Starting Oracle Designer

18. Resolving data flows • You will need to modify the higher level diagram to include resolved data flows • right click on the higher level diagram, select Include/resolved data flows • select necessary data flows • The resolved data flows will appear as dashed lines Starting Oracle Designer

19. Viewing functional hierarchy • Functional Hierarchy Diagrammer automatically creates a functional hierarchy based on the functional decomposition specified in the Data Flow Diagram • Open the Functional Hierarchy Diagrammer, select a container the same way as for DFD • Create a new diagram, select a higher level function. • All lower level functions will show as its subordinates Starting Oracle Designer

20. Starting Oracle Designer

21. Creating a business process map • Deals with functions, business units and flows, including data flows and temporal flows. • Each diagram decomposes a specific global function • An organization in general • A specific system/process • A function within the system/process • You can include objects that you have already created in other tools or as a part of a different diagram Starting Oracle Designer

22. Creating business process map • Open Process Modeler • Add organizational units (swim lanes) • Add functions and assign them to organizational units • Add data and temporal flows. • Add triggers Starting Oracle Designer

23. Process Model Example Starting Oracle Designer

24. Creating a Entity Relationship Diagram • Open the Entity Relationship Diagramer • Click New Diagram, select a container the same way as for DFD • Add entities and relationships using the icons at the top of the screen • Name your elements using the dialog boxes that pop up • To modify information about each of the elements, double click on the element and use the dialog window Starting Oracle Designer

25. Your ERD should look something like this … • In order to specify subtype-supertype relationship, you place the subtype inside of the supertype Starting Oracle Designer

26. Modify information about entities, add attributes Starting Oracle Designer

27. Modify information about relationships, including Min and Max cardinalities Starting Oracle Designer

28. Formatting diagrams • You can change the font and color of your elements using the following icons. • You can drag elements on the diagram in order to better reposition them • You can add summary information to the diagram by selecting File/Summary Information Starting Oracle Designer

29. Relating data stores and flows to entities • Entities and their attributes can be assigned as content of data stores and data flows in a DFD • In order to assign an entity to a data store or a data flow right click on the store, select content tab in the properties window Starting Oracle Designer

30. Relating functions to entities • A function can manipulate data • Create • Retrieve • Update • Delete • In order to assign an entity usages to a function right click on the function and select entity usage tab in the properties window Starting Oracle Designer

31. Creating a Matrix • Open the Matrix Diagrammer • Click New Diagram, select a container the same way as for the other diagrams • Select element that you would like to appear as rows and as columns Starting Oracle Designer

32. Creating a CRUD Matrix • For a Crud Matrix, select functions as rows, and entities as rows • Select information to appear as column and row labels and on the intersection or columns and rows Starting Oracle Designer

33. Your final CRUD Matrix will look like this Starting Oracle Designer

34. Viewing and printing reports • Open the Repository report tool • Select the appropriate report type and double click on the report name Starting Oracle Designer

35. Starting Oracle Designer

36. Oracle Tutorial Problem • The order management system allows to load and store product information from the product database (external entity). For each product, the following information is stored: Product ID, short description, long description, price (for simplicity reasons, we assume constant availability of all the products). • The order management system also allows customers to register with the system. On registration, the following customer information is stored: login (e-mail), first name, last name, address, city, state, zip, day phone, and a password. A registered customer can log into the system by submitting a login and a password. During the login, the system will query the customer file. • After logging in a customer can view product information and place an order. Order information includes order number, date, shipment info, products included and quantities for each product, and the total order amount. An order may include multiple products, and a product can be included in multiple orders. • Order information is stored in an order database. Order information is provided to the order fulfillment system. • Based on this description, we will build an ER diagram, a data flow diagram, a functional hierarchy diagram and a crud matrix Starting Oracle Designer

37. Next Class • Oracle DB design transformer • Oracle Design Editor Starting Oracle Designer

38. Oracle 9i Developer SuiteOracle 9i DesignerNow we will see how it can work for a simple example

39. Database Design Transformer • Allows to transform an ERD into a database (server model) • Produces Server model and Table definitions • In order to run transformer • Open Database Design Transformer • Select the container Starting Oracle Designer

40. Transforming the Database design • On the database design transformer dialog box view and modify settings and then click Run Starting Oracle Designer

41. Editing the Database design • Open the database design editor • Select Server model • Select container and click Create/Edit DB objects • Select Tables and Columns Starting Oracle Designer

42. Editing the Database design (cont’d) • Select all tables and click Diagram • Now you can view and edit the database design Starting Oracle Designer

43. Generating tables • Once you are satisfied with your DB design you can use the Design Editor tool to generate table for you r oracle Database • Go to Menu/Generate/Generate Database from Server model • You will need to provide login information for the DB once again • Click Start Starting Oracle Designer

44. Generating tables • Somewhere along the way a message will pop up … • Click View DDL (Data Definition Language) • A number of text files will open. • Make sure you save all of them!!! • You can now click Execute DDL Starting Oracle Designer

45. Generating tables (cont’d) • A progress report will tell you if tables were created successfully • You can now check if the tables have been created by logging into the SQL*Plus, typing • DESCRIBE [TableName]; Starting Oracle Designer

46. Oracle 9i Developer SuiteOracle 9i DesignerQuestions???