1 / 39

Lecture 4 Term 2

Lecture 4 Term 2. IS6172 27/1/14. Role of Systems Analyst Some suggestions. Identify the stakeholder in the system Identify the fact finding tasks that could be completed to specifically address the problem/opportunity presented

lorne
Download Presentation

Lecture 4 Term 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 4 Term 2 IS6172 27/1/14

  2. Role of Systems AnalystSome suggestions • Identify the stakeholder in the system • Identify the fact finding tasks that could be completed to specifically address the problem/opportunity presented • How is this data collected and analysed – could refer to use diagramming to illustrate this • Refer to the skills of a systems analyst, how are these be used in relation to the health insurance company scenario • Refer to what other companies are doing • What is happening in analogous markets • Use appropriate references to support your statements • Use tables/illustrations to help you explain or support your argument

  3. Use Case – Some hints • Lack of detail • Ensure to break use case activity into individual activity where appropriate • Use include and extend where appropriate • Be careful with ‘Log In’ use case activity • Must a customer be logged in to provide their details and generate a quote??? • Be careful with colour • Arrows omitted in places • Remember - • help development teams visualise the functional requirements of a system

  4. <<INCLUDES>> and <<EXTENDS>>

  5. <<INCLUDE>> • Both the Manage Customer Details and Enter Order use cases include the Find Customer Record use case. • They both have the procedures from Find Customer Record in common.

  6. INCLUDE and EXTEND • Both add extra behaviour to use cases

  7. The <<include>> arrow • Arrow points from the base use case to the included use case. • Think of “this base use case > INCLUDES > this use case”

  8. Example

  9. Like a conditional <<include>>. • Only takes place under certain conditions. • An <<extend>> is a use case that extends another use case (the ‘base’ use case) when circumstances require it. <<extend>>

  10. Shown as a dotted line with “<<extend>>” label • The condition under which the <<extend>> occurs is shown in { } beside the line • E.g. {if paid by credit card} <<extend>>

  11. <<extend>> Note how some activities only take place if wine has been ordered. Note: Solid lines or arrows between use cases are not allowed. The only way to link use cases is with includes or extends.

  12. ERD • USE OF FOREIGN KEYS • To create a relationship line between one entity and another you must include a foreign key where appropriate • It is through the foreign key that the relationship exists • NO TWO ENTITIES SHOULD HAVE THE SAME PRIMARY KEY • ALL ENTITIES SHOULD HAVE AT LEAST ONE NON-KEY ATTRIBUTE

  13. Foreign Keys Wikipedia • In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In other words, a foreign key is a column or a combination of columns that is used to establish and enforce a link between two tables.

  14. Example Engineer and Project Table/Entities Foreign Key Primary Key Relationship Primary Key

  15. Problem • A software company employs a number of programmers who are assigned to work on specific projects. • Each project is controlled by one manager. • Each programmer works on only one project but contributes to the writing of several programs. • Each program may be written by one or more programmers. • Construct an ERD to represent this scenario.

  16. Possible Entities • Manager • Project • Programmer • Program • Is this it?

  17. Possible Entities Project Manager Programmer Program

  18. Possible Entities Project Manager Programmer Program

  19. Possible Entities Project Manager NEED TO RESOLVE M:N Programmer Program

  20. Possible Entities Project Manager Programmer Assignment Program

  21. PKs, FKs and Attributes • Manager – Emp_No (PK), Name • Project – Project_ID (PK), Project_Name, Project_StartDate, Mgr_No (FK) • Programmer – Emp_No (PK), Name, Language, Project_ID(FK) • Assignment – Emp_No, Progam_No (PK) • Program – Program_No (PK), Title, Language

  22. ERDs • If I deleted a record of this table (or entity instance of an entity), would I loose important data that is not captured anywhere else in my database? • Date is an important attribute….

  23. Group By • A GROUP BY clause results into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. • You typically use a GROUP BY clause in conjunction with an aggregate expression. i.e. Sum, Avg, Count

  24. Group By Example • -- find the average flying_times of flights grouped by -- airport SELECT AVG (flying_time), orig_airport FROM Flights GROUP BY orig_airport;

  25. Group By • The Group By Column does not have to be in the SELECT statement • E.g. Select avg(sal) From employee Group by dept_no; 680 2150 462.5

  26. Revisit – Group By More than one Column • When you need to see results for groups within groups • You can determine the default sort order of the results by the order of the columns in the GROUP By clause Select dept_no, job, sum(sal) From employee GROUP BY dept_id 3 MANAGER 900 4 ANALYST 800 1 CLERK 360 3 ACCOUNTANT 700 1 MANAGER 1000 3 CLERK 250 4 MANAGER 3500, job;

  27. Rationale • Select specifies the column to be retrieved • Dept_id (department number in the employee table) • Job (employee role in the employee table) • Sum(sal) (sum of all salaries in the employee table) • From the employee database table • Group By (specifies how I want to group the row/records) • First the rows are group by department number • Then they are grouped by job or role NB The sum function is applied to the salary column for all jobs in each department number

  28. Equi-joins • We need a method of joining tables where can specify how we want them to join. • We do this by using the where clause. • For example Select emp_name, dept_name From employee, dept Where employee.dept_no=dept.dept_no; • This will return all the employees with the name of their department. • Why does it work? SAFETY BYRNE SAFETY HARTE HR CASEY HR MURRAY SAFETY DOHERTY SAFETY MARTIN

  29. Example SELECT * FROM employee, dept WHERE employee.dept_no = dept.dept_no AND job = 'CLERK'; • 12 MURRAY CLERK 1 360 80 27-JAN-12 1 HR 3rdFloor • 9 DOHERTY CLERK 3 150 28-JUN-11 3 SAFETY 3rdFloor • 4 BYRNE CLERK 3 100 20-FEB-09 3 SAFETY 3rdFloor • An equi join is an inner join statement that uses an equivalence operation to match rows from different tables. The converse of an equi join is a nonequi join operation.

  30. Non equi join • An nonequi (or theta) join is an inner join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables.

  31. Some background create table salgrade( grade number, losal number, hisal number ); insert into salgrade values (1, 700, 1200); insert into salgrade values (2, 1201, 1400); insert into salgrade values (3, 1401, 2000); insert into salgrade values (4, 2001, 3000); insert into salgrade values (5, 3001, 9999);

  32. Non-equi Joins Have a look at the salgrade table. GRADE LOSAL HISAL ---------- ---------- ---------- 5 3001 9999 4 2001 3000 3 1401 2000 2 1201 1400 • 7001200 • This is a table which lists the grades in the company and the salary range for that grade. If we wanted to find out what grade someone was in we couldn’t use an equi-join. The two tables (emp and salgrade) won’t have values equal to each other.

  33. Non-equi Joins (cont) • If an employees salary is between 700 and 1200 then they are grade 1, etc. • There is no equal to statement here. We are looking at a range. • To find everyone’s grade, we would run select e.emp_name, s.grade from employee e, salgrade s where e.sal between s.losal and s.hisal;

  34. Some Results 3 HEARNE ANALYST 4 800 100 07-JAN-11 4 BYRNE CLERK 3 100 20-FEB-09 6 WALSH MANAGER 4 3500 11-OCT-07 8 HARTE ACCOUNTANT 3 700 300 20-JAN-12 11 CASEY MANAGER 1 1000 150 17-OCT-08 12 MURRAY CLERK 1 360 80 27-JAN-12 9 DOHERTY CLERK 3 150 28-JUN-11 10 MARTIN MANAGER 3 900 190 07-SEP-13 • Criteria all sals between 700 (losal and 9999 hisal) • HARTE 1 • HEARNE 1 • MARTIN 1 • CASEY 1 • WALSH 5

  35. Table aliases • You can see from the examples above that can specify which table a column is in when joining. Where emp.deptno=dept.deptno • For example Select scott.thebigtable.column1 , scott,theotherbigtable.column2 From scott.thebigtable, scott.theotherbigtable Where scott.thebigtable.column3 = scott.theotherbigtable.column3 And scott.thebigtable.column1=’FEBRUARY’ Order by scott.thebigtable.column1; • • This is very cumbersome!

  36. Example Syntax SELECT * FROM table_name1, table_name2 WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

  37. Order and Customer Table http://www.w3resource.com/sql/joins/perform-a-non-equi-join.php

  38. SQL Example SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area FROM orders a,customer b     WHERE a.ord_amount BETWEEN b.opening_amt AND b.opening_amt;  

  39. Joining multiple tables If we wish to use the data from 3 tables, then we need 2 joins. In other words we would need two where clauses. 4 tables would need 3 joins, and so on. The rule is : Number of tables – 1 = minimum number of joins. • An example of this is to display employee name, employee’s department name, and employees salary grade. This info comes from 3 tables so we need 2 joins (where clauses). • Write the code to do this

More Related