Information Resources Information Resources ManagementManagement

January 16, 2001January 16, 2001

AgendaAgenda

AdministriviaAdministrivia Course OverviewCourse Overview Database Management Systems Database Management Systems

(DBMSs)(DBMSs) Homework #1Homework #1

AdministriviaAdministrivia

SyllabusSyllabus BookBook HomeworkHomework Web PageWeb Page

Course CalendarCourse Calendar

Eleven HomeworksEleven Homeworks Approximately one per weekApproximately one per week

ExamsExams Midterm 1 - 2/20Midterm 1 - 2/20 Midterm 2 - 3/20Midterm 2 - 3/20 Final - TBAFinal - TBA

QuizzesQuizzes Up to fiveUp to five

Overview – PerspectiveOverview – Perspective

Database Design and ImplementationDatabase Design and Implementation ““Business” PerspectiveBusiness” Perspective IT professional in an organizationIT professional in an organization

Database DesignDatabase Design

Relational ModelRelational Model Database ModelingDatabase Modeling Evaluation ToolsEvaluation Tools NormalizationNormalization

Database Database ImplementationImplementation SQL (Structured Query Language)SQL (Structured Query Language) Dependability, ReliabilityDependability, Reliability ArchitectureArchitecture Distributed DatabasesDistributed Databases

Database Management Database Management SystemsSystems Traditional File Processing SystemsTraditional File Processing Systems DatabaseDatabase ComparisonComparison Database LifecyclesDatabase Lifecycles Database Management System Database Management System

FunctionsFunctions

Traditional File Traditional File Processing SystemProcessing System Separate FilesSeparate Files

DepartmentDepartment Business FunctionBusiness Function

Independent of Each OtherIndependent of Each Other High Level of Program DependenceHigh Level of Program Dependence

Program contains file structure and Program contains file structure and locationlocation

ExampleExampleTime Cards

Data Entry

Trans

Trans Master

New Master

Checks

Report

Sort by Employe

e #

Sorted by

Employee #

Update Master

Data Abstraction Data Abstraction (Views)(Views) Physical/InternalPhysical/Internal

Logical/ConceptualLogical/Conceptual

View/ExternalView/External

How data is storedHow data is stored

What is storedWhat is stored

What is used What is used (subset)(subset)

Traditional file processing prevents this abstraction. Why?

Three Views of DataThree Views of Data

External (View)

Client Customer

Programmer

Conceptual (Logical)

Designer

Database Administrat

or (DBA)

Internal (Physical)

Database Administrat

or (DBA)

Tech Support

What is NeededWhat is Needed

Physical IndependencePhysical Independence Logical IndependenceLogical Independence

Physical IndependencePhysical Independence

Physical layout and organization of data can be Physical layout and organization of data can be changed without changing either the overall changed without changing either the overall logical structure of the data or the application logical structure of the data or the application programsprograms

ExamplesExamples Move data locationMove data location Move data to faster storageMove data to faster storage Change indexingChange indexing Add a secondary keyAdd a secondary key

Physical IndependencePhysical Independence

External (View)

Conceptual (Logical)

Internal (Physical)

No Resulting Change Here

Change Here

Logical IndependenceLogical Independence

Non-loss changes to the logical structure can Non-loss changes to the logical structure can be made without changing application be made without changing application programs or end-user viewsprograms or end-user views

ExamplesExamples Change format of a field (zip from 5 to 9)Change format of a field (zip from 5 to 9) Add a new data fieldAdd a new data field Add a new tableAdd a new table Divide a table into twoDivide a table into two

Logical IndependenceLogical Independence

External (View)

Conceptual (Logical)

Internal (Physical)

No Resulting Change Here

Change Here Coordinating Change Here

DatabaseDatabase

organized collection of logically related dataorganized collection of logically related data shared collection of interrelated data shared collection of interrelated data

designed to meet the informational needs of designed to meet the informational needs of multiple usersmultiple users

data is independent of program and user data is independent of program and user viewsviews

data is stored with physical and logical data is stored with physical and logical independenceindependence

Database Management Database Management System (DBMS)System (DBMS) Software that facilitates the Software that facilitates the

implementation of the database conceptimplementation of the database concept

ComparisonComparison(Traditional vs. (Traditional vs. Database)Database)

Database Advantages/Traditional Database Advantages/Traditional DisadvantagesDisadvantages program-data dependenceprogram-data dependence duplicationduplication data sharingdata sharing development timesdevelopment times program maintenance – ripple effectsprogram maintenance – ripple effects flexibilityflexibility

ComparisonComparison(Traditional vs. (Traditional vs. Database)Database) Database Advantages/Traditional Database Advantages/Traditional

DisadvantagesDisadvantages securitysecurity data integritydata integrity data as corporate resourcedata as corporate resource

ComparisonComparison(Traditional vs. (Traditional vs. Database)Database) Traditional Advantages/Database Traditional Advantages/Database

DisadvantagesDisadvantages sizesize complexitycomplexity costcost special hardwarespecial hardware impact of failureimpact of failure recoveryrecovery

ComparisonComparison(Traditional vs. (Traditional vs. Database)Database) Traditional Advantages/Database Traditional Advantages/Database

DisadvantagesDisadvantages additional personneladditional personnel conversion costsconversion costs organizational conflictorganizational conflict

Database LifecycleDatabase Lifecycle

1. Enterprise Modeling1. Enterprise Modeling

2. Conceptual Data Modeling2. Conceptual Data Modeling

3. Logical Database Design3. Logical Database Design

4. Physical Database Design and 4. Physical Database Design and CreationCreation

5. Database Implementation5. Database Implementation

6. Database Maintenance6. Database Maintenance

People InvolvedPeople Involved

Systems analysts & designersSystems analysts & designers Database analysts & designersDatabase analysts & designers UsersUsers ProgrammersProgrammers Database Administrators (DBAs)Database Administrators (DBAs) Networking expertsNetworking experts Other technical expertsOther technical experts

Database Management Database Management System FunctionsSystem Functions data storage, retrieval and updatedata storage, retrieval and update user-accessible cataloguser-accessible catalog transaction supporttransaction support concurrency controlconcurrency control recovery servicesrecovery services authorization servicesauthorization services support for data communicationsupport for data communication

Database Management Database Management System FunctionsSystem Functions integrity servicesintegrity services

types - character, number, etc.types - character, number, etc. internal validityinternal validity

services to promote data independence services to promote data independence (logical and physical)(logical and physical)

utility servicesutility services

Database ApplicationsDatabase Applications

PersonalPersonal one userone user

WorkgroupWorkgroup small team - LAN connectedsmall team - LAN connected

Department/DivisionDepartment/Division multiple teams and functionsmultiple teams and functions

EnterpriseEnterprise entire organizationentire organization

In-Class ExerciseIn-Class Exercise

Groups of 4Groups of 4 Introduce yourself Introduce yourself Identify a possible database of each Identify a possible database of each

typetype Pick a speakerPick a speaker

In-Class ExerciseIn-Class Exercise

Introduce yourself and group membersIntroduce yourself and group members Give group’s example databasesGive group’s example databases

personalpersonal workgroupworkgroup department/divisiondepartment/division enterpriseenterprise

Personal DatabasesPersonal Databases

BenefitsBenefits individualizedindividualized meet specific meet specific

needsneeds purchased purchased

packagepackage

DrawbacksDrawbacks limited to no data limited to no data

sharingsharing replicated datareplicated data consolidation - consolidation -

standardizationstandardization supportsupport

Workgroup DatabasesWorkgroup Databases

BenefitsBenefits meet specific meet specific

needsneeds data shared data shared

(across group)(across group) customized customized

“views”“views”

DrawbacksDrawbacks data sharing data sharing

across groupsacross groups replicated datareplicated data securitysecurity not optimal for not optimal for

individualindividual DBMS cost & DBMS cost &

supportsupport

Department DatabasesDepartment Databases

BenefitsBenefits meet (specific) meet (specific)

needsneeds data shareddata shared

DrawbacksDrawbacks data sharingdata sharing replicated datareplicated data securitysecurity not optimal for not optimal for

individual/groupindividual/group performanceperformance DBMS cost & DBMS cost &

developmentdevelopment

Enterprise DatabasesEnterprise Databases

BenefitsBenefits meet needsmeet needs data shareddata shared ““mineable”mineable” consistent view to consistent view to

customerscustomers

DrawbacksDrawbacks size & complexitysize & complexity securitysecurity cost & supportcost & support developmentdevelopment standards & standards &

bureaucracybureaucracy distribution & distribution &

ownershipownership

Best Database?Best Database?

Personal?Personal? Workgroup?Workgroup? Department?Department? Enterprise?Enterprise?

Best Database?Best Database?

Personal?Personal? Workgroup?Workgroup? Department?Department? Enterprise?Enterprise?

Combination of databases that meets the Combination of databases that meets the needs of individuals, teams, needs of individuals, teams,

departments, and the companydepartments, and the company

Best Database?Best Database?

To the individual, it’s a personal To the individual, it’s a personal databasedatabase

To the team, it’s a workgroup databaseTo the team, it’s a workgroup database To the department, it’s a department To the department, it’s a department

databasedatabase To the CEO, it’s an enterprise databaseTo the CEO, it’s an enterprise database (To the DBA, it’s a headache)(To the DBA, it’s a headache)

Homework #1Homework #1

Data management recommendationData management recommendation High-level, experience basedHigh-level, experience based

““glossy” vendor materialglossy” vendor material ShowShow

alternativesalternatives strengths/weaknesses of eachstrengths/weaknesses of each well-reasoned explanationwell-reasoned explanation