Relational Model

• Database represented as a collection of relations

• Each row in the table represents a collection of related data values

• Each row is called a tuple• Each column header is an attribute• Each table is a relation

The relation 'student'

STUDENTName SSN Phone Address Age

305-61-2435 3731616 19 3.21381-62-1245 3754409 125 Kirby Road 18 2.89

Darren Davidson 422-11-2320 Null 3452 Elgin Road 25 3.53Charles Cooper 489-22-1100 3769821 265 Lark Lane 29 3.93Barbara Benson 533-69-1238 8398461 7384 Fontana Lane 19 3.25

GradeAverageBenjamin Bayer 2918 Blluebonnet LaneKatherine Ashly

Relational Model cont.

EMPLOYEE

FNAME LNAME SSN BDATE ADDRESS SALARY

PROJECTPNAME PNUMBER PLOCATION

WORKS_ONESSN PNO HOURS

A Relational Database schema

Network Model

●Two basic data structures: records and sets●A record type describes the structure of a group of

records that store the same type of information

STUDENTNAME SSN ADDRESS DEPT BIRTHDATE

Data item name FormatNAME CHAR 30SSN CHAR 9ADDRESS CHAR 40DEPT CHAR 10BIRTHDATE CHAR 9

Record Type Student

Network Model cont.

●Complex data items may be defined●eg. repeating groups

STUDENTNAME ... TRANSCRIPT

YEAR COURSE SEMESTER GRADE

Smith ... 1984 COSC3320 1 A1984 COSC3340 1 A1984 MATH312 1 B1985 COSC4310 2 C1985 COSC4330 2 C

Network Model cont.

• A set type is a description of a 1:N relationship between two record types

• A set type definition consists of– A name for the set type– An owner record type– A member record type

Network Model cont.

DEPARTMENT

DNAME ....

STUDENT

....SNAME

MAJOR_DEPT

The set type MAJOR_DEPT

Network Model cont.

• A record type can participate as owner or member in any number of set types

EMPLOYEEFNAM

ELNAM

EBIRTHDATESSN ADDRESS SALARY DEPT

SUPERVISORSUPERVISOR_SSN

IS_A_SUPERVISOR SUPERVISES

DEPARTMENTNAME NUMBER LOCATION

MANAGES

Hierarchical Model

• Two basic data structures: records and parent-child relationships

• A record is a collection of field values that give information on an entity

• A record type is a group of records

Hierarchical Model cont.

• A parent-child relationship type is a description of a 1:N relationship between two record types

DEPARTMENTDNAME DNUMBER MGRNAME MGRSTDATE

EMPLOYEENAM

ESSN BDATE ADD

PROJECTPNAM

EPNU

MPLOCATION

A hierarchical schema

Hierarchical Model cont.

• Every record type except for the root participates as a child record type in exactly one PCR type

• This limits relationships in a hierarchical schema since M:N relationships cannot be directly represented.

• An M:N relationships is handled by allowing duplication of child record instances

Hierarchical Model cont.

DEPARTMENTDNAME DNUM MGRNAM

EMGRSTRTDATE

EMPLOYEENAME SSN BDATE ADD

PROJECTPNAM

EPNUM PLOCATION

WORKERNAM

ESSN HOURS

A hierarchical schema

Object Oriented Model

• Enables storage of persistent objects• A unique system generated object identifier

OID is assigned to each object• Objects may have an object structure of

arbitrary complexity• Relationships between objects may be

represented via references – that is, by placing the OID of related objects within an object itself

Deciding on a DSS Model

There are many different types of model which may be either descriptive or predictive. Each type of model will utilise appropriate modelling techniques. Each technique appears in a form of static or dynamic model, using either quantitative or qualitative data. The model may be constructed in an environment domain of assumed certainty, uncertainty or risk.

Categories of ModelsCategory Process and Objective Representative Techniques

Optimisation of problems with a few alternatives

Find the best solution from a small number of alternatives

Decision tables, decision trees

Optimisation via algorithm Find the best solution from a large number of alternatives, using a step by step improvement process

Linear and other mathematical programming models, network models

Optimisation via an analytic formula

Find the best solution in one step, using a formula

Inventory models

Simulation Finding a good enough solution or the best among the alternatives checked, using experimentation

Simulation

Heuristics Find a good enough solution using rules

Heuristic programming

Categories of Models cont.Category Process and Objective Representative Techniques

Predictive Models Predict the future for a given scenario

Forecasting models, Markov Analysis

Other Models Solve a what-if case, using a formula

Financial modeling, waiting lines

Major Capabilities of the UIMS

• Provides Graphical User Interface• Accomodates the user with a variety of input devices• Presents data in a variety of formats through a variety of

output devices• Gives users “help” capabilities, prompting, diagnostic and

suggestion routines or any other flexible support• Provides interaction with the database and the model base• Stores input and output data• Provides color graphics, three dimensional graphics and data

plotting

Major Capabilities of the UIMS cont.

• Can support communication among and between users and builders of the MSS

• Provides flexibility and adaptiveness so that the MSS will be able to accommodate different problems and technologies

• May provide interaction through various dialog styles• May capture, store and analyze dialog usage to improve the

dialog system

ES Development Life Cycle

• Phase 1: Project initialisation– Problem definition– Needs assessment– Evaluation of alternative solutions– Verification that an ES approach is

appropriate– Consideration of management issues

ES Development Life Cycle

• Phase 2: System analysis & design– Produce conceptual design – Decide development strategy– Decide sources of knowledge, and ensure

co-operation– Select computer resources– Perform a feasibility study– Perform a cost-benefit analysis

ES Development Life Cycle

• Phase 3: Prototyping– Build a small prototype– Test, improve and expand it– Demonstrate and analyse feasibility– Complete the design

ES Development Life Cycle

• Phase 4: System development– Build the knowledge base– Test, evaluate and improve the knowledge

base– Plan for integration

ES Development Life Cycle

• Phase 5: Implementation– Ensure acceptance by users– Install, demonstrate and deploy the system– Arrange orientation and training for the

users– Ensure security– Provide documentation– Arrange for integration and field testing

ES Development Life Cycle

• Phase 6: Post-implementation– Operation– Maintenance– Upgrading– Periodic evaluation

DSS Conclusions

DS systems can be separated into seven broad categories, each aiding decision making by different methods.

Communications Driven DSSData Driven DSSDocument Driven DSSKnowledge Driven DSSModel Driven DSSSpreadsheet based DSSWeb Based DSS

Glossary of Terms

• MIS • DSS• MSS• BI• EIS• ES• AI

• MIS – Management Information Systems– A system of collecting, storing and grouping data

into a form of information needed to carry out management functions. They provide a structured flow of info to middle management to help in day to day operations.

– An MIS is normally organised around a database with inquiry and report generating features

• DSS– Decision Support System– A software tool aimed at aiding an individual in

the decision making process. Normally geared towards managerial decision making at any level within the organisation.

– A DSS will facilitate the manipulation and understanding of large amounts of data to aid in typically “unstructured” decision making.

• MSS– Management Support System– Any computer based system aimed at aiding and

supporting managers in their decision making.– Couples information retrieval facilities with

techniques to model problems and generate “what if scenarios”

– A DSS is a type of MSS

• BI– Business Intelligence– the tools and systems that play a key role in the

strategic planning process of the corporation.– refers to technologies, applications and practices

for the collection, integration, analysis, and presentation of business information and sometimes to the information itself. (wikipedia)

– A framework for decision support. It combines architecture, databases or data warehouses, analytical tools and applications. (Decision Support and Business Intelligence Systems, Turban, Aronson, Liang, Sharda)

• EIS– Executive Information Systems– EIS are a type of MIS which is geared towards

helping management in their strategic decision making processes.

– They provide access to both internal and external data and place emphasis on the user interface and graphical display of that data.

– EIS applications allow a lot of flexibility in the manipulation of data.

• ES– Expert Systems– A computer system that applies reasoning

methodolgies to knowledge in a specific domain to render advice much like a human expert. (Decision Support and Business Intelligence Systems, Turban, Aronson, Liang, Sharda)

– Main components are : Knowledge Base, Inference Engine, Blackboard and Justifier

• AI– Artificial Intelligence– A field of computer science that studies systems

which make it possible for a machine to carry out functions that, done by humans, would require intelligence.

– Main characteristics are symbolic reasoning, machine learning, heuristics and inferencing

Decision Tables Exercise

This is an extract of rules which are applied by a medical insurance company:

• No charges are reimbursed to the patient until the excess amount has been met.

• Hospital visits are reimbursed at 80% and Lab visits are reimbursed at 70%.

• Doctor's office visits are reimbursed at 90% for "Participating Physicians" or 50% for others.

• The question of whether the Doctor is a Participating Physician is only applicable for Doctor's office visits; it is not applicable for Hospital visits or Lab work.

Set up an appropriate Decision Table for these rules.

Linear Programming Exercise A company is involved in the production of two items (X and Y). The resources needed

to produce X and Y are twofold, namely machine time for automatic processing and craftsman time for hand finishing. The table below gives the number of minutes required for each item:

Machine time Craftsman time Item X 13 20 Y 19 29

The company has 40 hours of machine time available in the next working week but only 35 hours of craftsman time. Machine time is costed at £10 per hour worked and craftsman time is costed at £2 per hour worked. Both machine and craftsman idle times incur no costs. The revenue received for each item produced (all production is sold) is £20 for X and £30 for Y. The company has a specific contract to produce 10 items of X per week for a particular customer.

Formulate the problem of deciding how much to produce per week as a linear program. Solve this linear program graphically.