MIT 5314 Database Applications Slide 1 Visible Analyst

MIT 5314: Database Applications Slide # 1 Visible Analyst An Introduction To Using Visual Analyst to Create an ERD Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 2 Visible Analyst n Visible Analyst is a Computer Aided Software Engineering (CASE) Tool • A quick Note: In January 2003, The Visible Developer Professional Edition was selling for $1, 995 (Cheap!) n Among the models included are: n Functional Decomposition (Business Model) • Shows the business functions and processes they support in a hierarchical structure n Entity Relationship Diagram (Data Model) • Shows the data entities of an application and the relationship between the entities n Object Model (Object Class Model) • Dr. Peeter Kirs Shows classes of Objects, Subclasses, Aggregations, and Inheritance Fall 2003

MIT 5314: Database Applications Slide # 3 Visible Analyst n Visible Analyst Models (Continued) n State Transition Model (Real Time Model) • Shows object transition to and from various states and conditions and the events or triggers cause them to change between different states n Process Model (Data Flow Diagrams) • • Shows how things occur in the organization via a sequence of processes, actions, stores, inputs and outputs. Processes are decomposed into more detail, producing a layered hierarchical structure n Product Model (Structure Chart) • Dr. Peeter Kirs Shows a hierarchical, top-down design map of how the application will be programmed, built, integrated, and tested Fall 2003

MIT 5314: Database Applications Slide # 4 Visible Analyst n Visible Analyst Models (Continued) n Use-Case Model • Shows the relationship between a user and the information system n Activity Model • A special form of the state diagram representing the performance of actions or subactivities n Sequence Model • Shows how objects collaborate in some behavior n Collaboration Model • Shows an interaction organized around the objects in the interaction and their links to each other n Repository Model (Database Project_ • Dr. Peeter Kirs The Bible of Systems Analysis and Design Fall 2003

MIT 5314: Database Applications Slide # 5 Visible Analyst n Open the Visible Analyst Program Ø The First Thing You will see is the start-up menu Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 6 Visible Analyst n The Next thing you will see is the main menu: To start a new project, select New Project from the File Menu Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 7 Visible Analyst n When you do, another pop-up menu will appear: Click the OK Button You have now created a Project called Medical which will be stored (in this case) at C: VISABLE AnalystData Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 8 Visible Analyst n Once you have created a project, Visible Analyst, by default, assumes you will creating a new diagram: It just happens to be an ERD (Which is what we want) Click the OK Button Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 9 Visible Analyst n You will now see the ERD Drawing Screen: Let’s First add the Entity PHYSICIAN Click on the Entity Button When you do, your cursor will change: Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 10 Visible Analyst • Move the cursor to some position on the screen (Or keep it where it is) • Then Right Click PHYSICIAN • Another Pop-up menu will then appear asking you to name the entity (Object) • Label it PHYSICIAN • Then Press OK Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 11 Visible Analyst n You Have now created the Entity Physician • Next, we need to change to the select mode • Next we right-click on our entity PHYSICIAN (A Pop-up Menu Will appear) Define … Change Item … Stylize … Text Settings … Colors … • Choose Define (We are going to add attributes) Cut Copy Delete Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 12 Visible Analyst MEDICAL DATABASE • Before we can go any further, we need to save our diagram • Let’s label our diagram as MEDICAL DATABASE • And then Press OK Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 13 Visible Analyst • A New Pop-up Menu Will appear Contains Information about the Physician employed by the hospital • This menu will first ask us for a description of the entity • Describe what the database is for (This will become part of the Repository) • Put in an appropriate description Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 14 Visible Analyst • Alias ? ? Contains Information about the Physician employed by the hospital (We will talk about that later --- Maybe ---) • To add an attribute, Simply move your cursor to the add field, and start typing in the field name PHYSID Char No • As soon as you do, some default values come up • These values refer to the data type, the length, and whether or not a Null value can be entered Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 15 Visible Analyst Contains Information about the Physician employed by the hospital • We will first have to change the null (or missing) values, as necessary • In our case, we know that PHYSID should be of length 9 • Notes ? ? PHYSID Char 9 No This will be the primary key for the PHYSICIAN table. It will also be the foreign key in table PATIENT • A GREAT idea – Put in thoughts about what this means – if it turns out to be wrong, you can change it later • Now ADD the attribute to the entity Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 16 Visible Analyst • And the attribute has been added • For the time being, let’s assume that this is the only attribute • Click on the EXIT button A Dialogue Box will remind to save our entry • Click ‘YES’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 17 Visible Analyst • It may not look like we did anything, but we did • There are three ways of viewing the screen: • The ENTITY LEVEL (which we are in) • The PRIMARY KEY LEVEL • The ATTRIBUTE LEVEL • To see the attribute added, Click on the ATTRIBUTE Level View Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 18 Visible Analyst • Notice that the Entity (PHYSICIAN) and the one attribute we entered (Phys. ID) are displayed • While we are at it, let’s look at the PRIMARY KEY LEVEL screen • Click on the PRIMARY KEY LEVEL Button Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 19 Visible Analyst Where is all the information? Define … Change Item … Stylize … Text Settings … Colors … Cut Copy Delete Dr. Peeter Kirs • The PRIMARY KEY LEVEL Screen Shows ONLY the Entity Name and the Primary Key • We didn’t designate a Primary Key • Let’s make Phys. ID the primary key • Once again, RIGHT-CLICK on the entity • And again, Choose DEFINE Fall 2003

MIT 5314: Database Applications Slide # 20 Visible Analyst • Adding a Primary Key is extremely simple • Click on the primary key button • A new dialogue Box will appear Phys. ID • Click on the attribute you wish to make a Primary Key • And it will appear in the “Columns in Key” Box • Now just press the OK Button Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 21 Visible Analyst • Notice that Phys. ID is now the Primary Key • Now let’s go back and see our Diagram Screen • Click on the EXIT button (This time there will be no reminder to save the object) Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 22 Visible Analyst • Remember, we are still in the PRIMARY KEY LEVEL screen • Let’s Add another Attribute (You know the drill) • RIGHT-CLICK on the entity Define … Change Item … Stylize … Text Settings … Colors … • And again, Choose DEFINE Cut Copy Delete Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 23 Visible Analyst • Let’s Add the attribute ‘Phys. Name’ • Once again, the default values will appear in the ‘Type’ and ‘Null’ Fields Phys. Name Char No What if we don’t Want the data type ‘Char’ or we wished to allow ‘Null’ Values? • Change them Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 24 Visible Analyst • Let’s change the attribute ‘Phys. Name’ to an integer (Yes -- I know that’s dumb, but bear with me) • Click on the Scroll Bar button Phys. Name Dr. Peeter Kirs Char No Fall 2003

MIT 5314: Database Applications Slide # 25 Visible Analyst • The list of all available data types will appear • Choose the one you want • Integer ? ? ? • Small Integer ? ? ? Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 26 Visible Analyst • Clicking on the ‘Null’ Scroll Bar will cause the possibilities to be displayed • At any rate, we want the attribute ‘Phys. Name’ to be of data type ‘Char’ and of length 30 30 Dr. Peeter Kirs (Which we would signify by entering ’ 30’ in the length field) Fall 2003

MIT 5314: Database Applications Slide # 27 Visible Analyst • Once again, after we have added our attribute, we need to add it to the list Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 28 Visible Analyst • As soon as we hit the add button, it will be added to the list • Let’s go back to the Diagram Screen • Click Exit (Again, there will be no reminder to save) Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 29 Visible Analyst (I have added a few more Attributes) • Notice that we are in ATTRIBUTE LEVEL Mode Define … Change Item … Stylize … Text Settings … Colors … What if we want to add a constraint to an attribute ? ? • Not a problem all we need to do is go back to the ‘Define’ menu Cut Copy Delete Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 30 Visible Analyst • Click on the ‘Check Constraints’ Tab Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 31 Visible Analyst • Click on the ‘Column Check Constraints’ Scroll Bar (The complete list of attributes will appear) • Highlight the attribute you wish to put conditions on and click Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 32 Visible Analyst • Click the ‘Add/Remove’ Button • Add the condition Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 33 Visible Analyst • Now let’s add the entity ‘PATIENT’ • Click on the ‘Entity’ Button • Put the entity wherever you want PATIENT • Once again, the label entity dialogue box will ask you to label the object • After that, Click on the ‘OK’ Button Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 34 Visible Analyst • Add ‘PATIENT’ Attributes as we did before (Make sure you first choose the ‘Select Mode’) • Notice also that I have expanded the ENTITY folder Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 35 Visible Analyst • We’re now ready to link the tables together • Choose the appropriate relationship from the menu bar (In our case, this is a one (mandatory) to many (optional) relationship) Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 36 Visible Analyst • After you click on the relationship button, simply connect the tables with your cursor Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 37 Visible Analyst Treats • Another Dialogue box will appear asking you questions about the relationship Treated By • May a Physician have Zero patients? • Enter the name of the relationship from PHYSICIAN to PATIENT • Enter the name of the relationship from PATIENT to PHYSICIAN • Click the ‘OK’ Button Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 38 Visible Analyst • And we have set up a relationship between the tables Have We ? ? • One nice feature about Visible Analyst is that it will Analyze our diagram for us Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 39 Visible Analyst Symbols Lines Text Construct Change Item Stylize Connect Snap Symbols Snap Lines • Click on the ‘DIAGRAM’ Tool bar option • Now click on the ‘Analyze’ Option Analyze Split Dataflow Modify View Delete • Another Menu will pop-up with available options • Click the ‘OK’ Button • And, we Appear to be OK Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 40 Visible Analyst • Keep in mind that Visible Analyst is MORE that just a diagramming tool • It also maintains our REPOSITORY Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 41 Visible Analyst n Before going on, let’s review what a Repository does: • A repository is a database for a project that contains information both captured from your diagrams and entered directly by you. • It contains metadata that describes the organization’s data and data processing resources • Provides information about: • • What users must know what What automated CASE tools that are used to specify and develop information systems All Applications that access and manipulate data The DBMS that maintain the repository and update system privileges, passwords, and other information Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 42 Visible Analyst • Now, getting back to our Example: • To check our repository, click on the ‘REPOSITORY’ Tool bar option Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 43 Visible Analyst • Let’s do a Key Analysis • We forgot to add our foreign key Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 44 Visible Analyst • The procedure for adding a foreign key is straightforward Define … Change Item … Stylize … Text Settings … Colors … Dr. Peeter Kirs • RIGHT-CLICK on the entity Cut Copy • And again, Choose DEFINE Delete Fall 2003

MIT 5314: Database Applications Slide # 45 Visible Analyst • One of the options on the ‘Define Item’ Menu is ‘Foreign Keys’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 46 Visible Analyst Physician • First, Click on the attribute you wish as a foreign key • Then click on the field you want to relate the foreign key with (The attribute will be added) • Save it • And Exit Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 47 Visible Analyst Define Divisions Key Analysis Key Synchronization Model Balancing Syntax Check • Notice that our ATTRIBUTE LEVEL Screen reflects the addition of the foreign key Generate Database Schema Generate DDS Generate Code Reports Report Query • Let’s check our repository to make sure everything is OK • Pick Key Analysis Dr. Peeter Kirs What is going on ? ? Fall 2003

MIT 5314: Database Applications Slide # 48 Visible Analyst n There actually a few problems: • • Remember how we defined our PHYSICIAN Table And our PATIENT Table • We have a mismatch between PHYSICIAN. Phys. ID and PATIENT. Physician Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 49 Visible Analyst Define Divisions Key Analysis Key Synchronization Model Balancing Syntax Check • Another benefit of Visible Analyst is that it will correct these types of problems automatically Generate Database Schema Generate DDS Generate Code Reports Report Query • Once again, Click on ‘Repository’ • Now Click on ‘Key Synchronization’ • The Pop-up Menu will give you some options • Click ‘OK’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 50 Visible Analyst • You will be asked to save your synchronization • So, Save it • Save this file too Name. It. What. You. Wish Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 51 Visible Analyst Define Divisions Key Analysis Key Synchronization • Model Balancing Syntax Check Are we OK now ? ? Let’s Check --- Generate Database Schema Generate DDS Generate Code Reports Report Query • Click on ‘Key Analysis’ • We appear to be OK Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 52 Visible Analyst n Let’s once again take a look at our tables: • • First, our PHYSICIAN Table Next, our PATIENT Table • The Issue has been resolved Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 53 Visible Analyst • I have added two more entities (You know how to do this) • Let’s add the Associative entity TREATMENT • Aside from clicking on the ‘Associative Entity’ Button (instead of the ‘Entity’ Button, the procedure is the same Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 54 Visible Analyst • Next use the mouse to connect the entities • Then fill in the necessary information • First, let’s relate PATIENT and TREATMENT (This is a (mandatory) 1 to (optional) Many relationship) Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 55 Visible Analyst Symbols Lines Text Construct Change Item • Let’s Stylize Connect Snap Symbols Snap Lines Are we OK now ? ? Check Analyze Split Dataflow Modify View Delete • Note that this is a WARNING not an ERROR Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 56 Visible Analyst Define Divisions Key Analysis • Let’s Key Synchronization Model Balancing Syntax Check But what about the Repository ? ? Check that Generate Database Schema Generate DDS Generate Code Reports Report Query • Once again, we forgot our Foreign Keys Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 57 Visible Analyst • Open the ‘DEFINE’ Menu (Right-Click on TREATMENT) • Go to the ‘Foreign Keys’ Option • First link PATIENT and TREATMENT • Choose the relationship • Then add the attribute • Move it to the Link Patient • This time, Click on ‘Migrate’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 58 Visible Analyst Migrate ? ? n Key Synchronization ‘migrates’ primary keys across normalized relationships to make Foreign Keys in other Entities n It also adds descriptive information about the relationship and the related entities to the Repository n When you created the entity, the Repository created an ‘Associator’ Element Name for it n Visible Analyst uses this information for Key Analysis and Synchronization Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 59 Visible Analyst • Do the same thing for the other relationships • Select the relationship between TREATMENT and PRESCRIPTION • Add the attribute • Move it to the Link • Migrate Prescription • Do it again for the relationship between TREATMENT and ILLNESS Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 60 Visible Analyst • Notice that a strange thing has happened: • The attribute names have been changed to correspond with their labels in the tables they refer to: (Due to Synchronization) • We could change them, or just leave them as is Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 61 Visible Analyst Define Divisions Key Analysis Key Synchronization Model Balancing Syntax Check Are we OK now ? ? • Let’s Check Generate Database Schema Generate DDS Generate Code Reports Report Query Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 62 Visible Analyst • The Only thing we haven’t done is set-up our Unary Relationship (But we know how to) • Draw the relationship • Right-Click on PHYSICIAN • Choose ‘Define’ • Add the field ‘Supervisor’ • Set ‘Supervisor’ as a Foreign Key -- And We’re Done --Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 63 Visible Analyst How do we Open a Different Project ? ? • First, Go to the ‘File’ Option Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 64 Visible Analyst • Now Select the project • Let’s Choose Project TEST • Then Click ‘OK’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 65 Visible Analyst • Choose (highlight) Entity • Now open up the ‘Diagram’ Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 66 Visible Analyst • Let’s Open the Driver’s License ERD Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 67 Visible Analyst • Let’s Review the ERD: The Department of Motor Vehicles (DMV) Many Individuals Apply for Licenses An Applicant May take may Road Tests Has Many Evaluators The DMV May (Must? ) Issue Many Drivers Licenses Each Evaluator Gives many Road Tests Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 68 Visible Analyst n Let’s Look at some of Visible Analyst’s other (ERD) options: • We can Establish Database Security • We can Modify The Balancing Rules • We can Modify The SQL Dialect Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 69 Visible Analyst • We can Even: • Generate the DDS (Data Descriptor Specification) • Generate the SQL Code Needed • Create Reports • Create Report Queries -- BUT NOT WITH THE STUDENT VERSION --- Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 70 Visible Analyst ? ? Any Questions ? ? ? Dr. Peeter Kirs Fall 2003

MIT 5314: Database Applications Slide # 71 Visible Analyst Dr. Peeter Kirs Fall 2003