Exam 1: Data management

Database

Organized collection of logically related data

Data

Stored representations of meaningful objects and events

Structured vs Unstructured Data

Structured: numbers, text, dates. Unstructured: images, video, sound, documents

Information

Data processed to increase the knowledge of those who use it

Metadata

Data that describes the properties and context of user data (type,size,allowed values, source)

Relational database

Data as a collection of tables; Relationships represented by common values in related tables

DBMS

Software used to create,maintain, and provide controlled access to user databases.

Data Model

Graphical diagram capturing the nature and relationships of data (entities, attributes, relationships)

Most common data modeling representation

Entity-relationship (E-R) model

Entity

Noun describing a person, place, object, event, or concept; composed of attributes.

Enterprise data model vs project data model

Enterprise: high level ‘birds eye’ view, less detail. project: more detailed, scoped to one project

Program-data dependence

Every program maintains it own metadata for each file it uses

Duplication of data

Different systems keep separate copies of the same data

Limited data sharing

No centralized control of data

Lengthy development times

programmers must design their own file formats

Excessive program maintenance

up to 80% of the information systems budget

10 advantages of the database approach

• program-data independence

• planned data redundancy

• improved data consistency

• improved data sharing

• increased application development productivity

• enforcement of standards

• improved data quality

• improved data accessibility and responsiveness

• reduced program maintenance

• improved decision support

5 costs/risks of the database approach

• New specialized personnel

• installation and management cost and complexity

• conversion costs

• need for explicit backup and recovery

• organizational conflict

operational databases

record transactions, keep the company running, constantly changing, usually relational

Analytical (informational) systems

Help analysts/managers understand and decide; relational and non relational (data warehouses, big data, NoSQL)

Data modeling and design tools

Automated tools to design databases and applications

Repository

Centralized knowledge base of data definitions, relationships, screen/report formats

DBMS

Creates, maintains, controls access to databases

User interface

Text, graphical displays, menus, etc.

Data/database administrators

Maintain the database

System developers

design databases and software

End users

use the applications and databases

SDLC

Systems Development Life Cycle: Traditional, detailed, well planned, comprehensive but slow (‘waterfall’, yet iterative as a cycle)

5 SDLC phases in order

Planning → Analysis → Design → Implementation → Maintenance

Planning

Preliminary understanding of the business situation; enterprise model and conceptual data modeling begin

Analysis

Thorough analysis leading to functional requirements; detailed conceptual data modeling

Design

Logical and physical database design

Implementation

Write programs, build databases, test, install, train, documennt

Maintenance

Monitor, repair, enhance (tune database, fix errors)

Prototyping/RAD

Rapid application development: cursory conceptual modeling, define the database during the initial prototype, repeat implementation/maintenance with new versions.

Agile flavors

Prototyping, agile, methodologies, extreme programming, scrum

Agile emphasizes

Individuals and interactions, working software, customer collaboration, response to change

External schema

User views (reports, screens, forms)

Conceptual schema

Combines external views into one coherent, comprehensive definition of the enterprises data

Internal schema

Two parts: logical schema and physical schema

Logical schema

Representation of data for a type of data management technology

Physical schema

How data are represented and stored in secondary storage using a particular DBMS

Project

planned undertaking with a beginning and an end; initiated in planning, executed in analysis/design/implementation, closed at the end of implementation.

Business analyst

analyzes the business situation, establishes requirements

Systems analyst

Technical expertise for the overall information system

Database analyst/data modeler

analysts who focus on the database

Database architech

Establishes data standards in business units

Data administrator

Responsible for existing databases; ensures integrity and consistency

project manager

oversees projects and personnel

Programmer

writes programs that maintain and access database data

4 drivers of database evolution

• program-data independence

• more complex data types

• easier/faster access for less technical people

• stronger decision support

Hierarchical and network models

earliest models; inflexible (many-to-many impossible in hierarchical, hard to modify in network)

Most common model for business

relational

personal database

1 user, megabytes

Multi-tiered client/server

2-1000 users, gigabytes

Enterprise resource planning (ERP)

>100 users, gigabytes-terabytes

Data warehouse

>100 users, terabytes-petabytes; integrates multiple sources, keeps historical data, finds patterns/trends

Data lake

Large repository for internal and external data with no predefined schema

3 tiers of client/server

client tier (browsers/UI) → Application/Web tier (app code) → Enterprise tier (DBMS and data)

Metadata is not data itself

it describes data

Entity type

Collection of entities sharing common properties, what the E-R box represents

Entity instance

A single occurrence of an entity type

Attribute

Property or characteristic of an entity or relationship type of interest to the organization

Relationship

Association among instances of one or more entity types

Relationship type vs instance

Type: line between entity types. Instance: link between specific entity instances

Inappropriate entities

System users and system outputs should be entities

Business rule

Statement that constrains an organization; derived from policies, procedures, events, functions

7 traits of a good business rule

• Business-Oriented

• Precise

• Atomic

• Consistent

• Declarative

• Distinct

Declarative

says what, not how

Atomic

One statement only

Distinct

Non-redundant

Good data name

Business-related (not technical), meaningful/self documenting, unique, readable, from an approved word list, repeatable, standard syntax.

Data definition guidelines

Concise, essential meaning, same source, accompanied by diagrams, stated in the singular.

Term vs Fact

Term: word/phrase with specific meaning. Fact: association between two or more terms

Entity naming

singular noun, specific to the organization; concise for events name the result not the activity, consistent across diagrams

How should an entity definition start?

“An X is…”; say what is and is not the entity; when instances are created/destroyed; what history is kept.

Attribute naming rules

singular noun/noun phrase; unique; standard format; same qualifiers for similar attributes across entities.

Attribute definition should include

what it is and why; what is included/excluded; aliases;source;changeable?; required/optional;min/max occurrences.