access agenda 1.select lessons i.the day of the presentation is the primary criterion for selection...
TRANSCRIPT
ACCESS AGENDAACCESS AGENDA
1.1. SELECT LESSONSSELECT LESSONS
i.i. The day of the presentation is the primary criterion for The day of the presentation is the primary criterion for selection projectsselection projects
ii.ii. Start choices with Rx because went first for CIO.1Start choices with Rx because went first for CIO.1
2.2. HANDOUT THE FOLDERS HANDOUT THE FOLDERS WITH THE LESSONSWITH THE LESSONS
3.3. COPY THE ACCESS DATA FILES COPY THE ACCESS DATA FILES i.i. TO: student travel drivesTO: student travel drivesii.ii. FROMFROM: : !!!TEAM PROJECTS-COPYING TO TEAM TRAVEL DRIVE!!!TEAM PROJECTS-COPYING TO TEAM TRAVEL DRIVE
Copy to !!!ACCESS 2010 on I: on classroom PC Copy to !!!ACCESS 2010 on I: on classroom PC
4.4. SHOW POWERPOINTSHOW POWERPOINT1.1. Sample ScreensSample Screens2.2. Great PresentationsGreat Presentations
DatabasesDatabases DatabasesDatabases
DATABASESDATABASES & &
DATA WAREHOUSESDATA WAREHOUSES
Access to a Gold Mine of Information
““TheThe real valuereal value will come from getting thewill come from getting the right informationright information to theto the right peopleright people, at the, at the right timeright time, and giving those people the, and giving those people the toolstools to find to find the the meaningmeaning in it.”in it.” Declaration of IntegrationDeclaration of Integration
Abbie LundbergAbbie LundbergCIO 1Dec2002CIO 1Dec2002
ACCESSACCESS PROJECTS PROJECTSDatabaseDatabase
“What’s really greatreally great about databases and DBMSs is that the conceptsconcepts are the samesame whether you’re using a personal DBMSs package or a large mainframe DBMS package.”
“...once you learn database and DBMS conceptsconcepts while using a personal DBMS package (like MS ACCESS)(like MS ACCESS), you can applyapply your knowledge directlydirectly to business environments where other DBMS packages may be used.” Haag.2004.94Haag.2004.94
Team ProjectsTeam Projects use Microsoft’s DBMS ACCESSACCESS to demonstrate: Data Definition: tables: fields, properties, keys Project AProject A Application Generators: forms: controls Projects B, C, DProjects B, C, D Data Manipulation: sorting , , filters & queries Projects E, FProjects E, F
A-2aA-2a
Use Access as an Alternative….Use Access as an Alternative….DatabaseDatabase
“Access gives them a robustrobust database engine that does not lock into a specific technology.” 1.31.3
““Your job is to Your job is to getget workwork donedone, , notnot buildbuild computercomputer programsprograms. . Access provides an easyeasy way to get started …with a user interface that will be familiarfamiliar to anyone who has used Microsoft Office.” 2.32.3
Access is inexpensiveinexpensive, and most people catchcatch onon quicklyquickly….Your company probably owns several copies of Access. 2.4-52.4-5
Access is a workableworkable solutionsolution. 3.33.3
Stonyfield Farms Stonyfield Farms & CSS CSS
A-2bA-2b
TechRepublic TechRepublic 5Dec035Dec03
Figure 3.6Figure 3.6
AccessAccessProjectProject AA
tables, propertiestables, properties
AccessAccessProjectsProjects BB, , CC, , DDforms & controlsforms & controls
AccessAccessProjectsProjects EE, , FF
filter, sort, queryfilter, sort, query
A-3A-3
shared shared informationinformation
&&decentralized decentralized computing computing
capturingcapturingreliabilityreliability
conveyingconveyingaccessaccess
creatingcreating
analysisanalysis
cradlingcradling
relevancerelevance
cradlingcradling
communicatingcommunicating
accessaccess
DBMS processDBMS process data (reliability) data (reliability) info info (relevance) (relevance) knowledge knowledge
(access & analysis) (access & analysis)
DATA DATA forforProject is in Chapter folderProject is in Chapter folder
Synthesis & EvaluationSynthesis & Evaluationof DBMS procedures: outline of of DBMS procedures: outline of
text integrating concepts & screenstext integrating concepts & screens
PROCEDURES PROCEDURES fromfromMS step-by-step MS step-by-step handouthandout
CONCEPTS CONCEPTS && TEMPLATES TEMPLATES from from
PowerPoint & handoutsPowerPoint & handouts
Application Application & &
AnalysisAnalysis((ALLALL 33))
CIO caseCIO case
Presentation focusing on Presentation focusing on Analysis, Synthesis & EvaluationAnalysis, Synthesis & Evaluation
contributes to contributes to ComprehensionComprehension
Application Application & &
AnalysisAnalysis
HardHard l limitimit: : no more than no more than 20 minutes. 20 minutes.
# slides depends # slides depends on content & use.on content & use.
Text in MS Text in MS chapters illustrates chapters illustrates
how to apply, how to apply, synthesize & synthesize & evaluate data evaluate data procedures & procedures &
conceptsconcepts
A-4aA-4a
See samples See samples of prior of prior
presentationspresentations
copy to copy to travel drive after travel drive after
select projectsselect projects
ACCESS Projects: Structure & Process ACCESS Projects: Structure & Process
ASSIGNMENT: ASSIGNMENT: Work with Work with teamteam on on ACCESS ACCESS project. project. EmphasizeEmphasize course concepts to course concepts to
explain explain whywhy the procedures are the procedures are important in: important in:
meeting the meeting the MIS challengeMIS challenge
performing the performing the 5Cs5Cs, ,
processing processing datadata into into informationinformation then then knowledgeknowledge, ,
data sharing data sharing and and decentralized decentralized computingcomputing that support decision that support decision making and innovation making and innovation
building building CISCIS (TPS), (TPS), MISMIS (dashboards), (dashboards), DSSDSS, , WSSWSS
creating creating informationinformation partnershipspartnerships, , virtualvirtual – – learninglearning organizations that are organizations that are timelesstimeless & & locationlesslocationless
These These exercisesexercises are essential are essential to the to the synthesissynthesis of concepts, of concepts, frameworks and ideas into a frameworks and ideas into a new whole that new whole that changes the changes the brain brain realreal learninglearning ((BloomBloom Hierarchy of Learning Hierarchy of Learning Objectives; Objectives; WSJWSJ 19Jan07 19Jan07 “Thinking Can Change the “Thinking Can Change the Brain”)Brain”)
This requires reviewing This requires reviewing Information AgeInformation Age, , IT SystemsIT Systems, , IT StrategiesIT Strategies PowerPoints PowerPoints and using those concepts, and using those concepts, frameworks and ideas to frameworks and ideas to analyzeanalyze and and evaluateevaluate the the ACCESS procedures.ACCESS procedures.
Seinfeld car rental:Seinfeld car rental:databases support all 5Csdatabases support all 5Cs
take reservation take reservation capture capture formsforms
hold reservation hold reservation cradle cradle tablestables
availability of cars availability of cars create create query query
presentation presentation communicate & convey communicate & convey report report
10
Cluster diagram: Cluster diagram: access performs 5Cs & access performs 5Cs &
supports IT systemssupports IT systems
TENTATIVETENTATIVESCHEDULESCHEDULE TUESDAY THURSDAY
MARCHMARCH20122012
2020DSSDSS
2222DatabasesDatabases
2727DatabasesDatabases
2929DatabasesDatabases
APRILAPRIL20122012
33ACCESSACCESS
55ACCESSACCESS
1010SDLCSDLC
1212SDLCSDLC
1717SDLCSDLC
1919SDLCSDLC
2626TEAM TIMETEAM TIME
2828TEAM TIMETEAM TIME
MayMay20122012
11CIO.2CIO.2
33CIO.2CIO.2
Select Access ProjectsSelect Access ProjectsChoice reverse order CIO.1 presentations (Rx Choice reverse order CIO.1 presentations (Rx Build) Build)
TentativeTentativedatesdates
Access ProjectAccess ProjectCIO TeamCIO Team
800800 930930 200200
TT
APR 3APR 3
A: Data DefinitionA: Data Definition (cradling : relevance)(cradling : relevance)B: Application GeneratorsB: Application Generators (capturing : reliably)(capturing : reliably)C: Application GeneratorsC: Application Generators (capturing : reliably)(capturing : reliably)
THTH
APR 5APR 5
D: Application GeneratorsD: Application Generators (capturing : reliably)(capturing : reliably)E: Data ManipulationE: Data Manipulation (creating : analysis)(creating : analysis)F: Data ManipulationF: Data Manipulation (creating : analysis)(creating : analysis)
A-12A-12
HandoutHandout Access LessonsAccess Lessons
CopyCopy Projects to Travel DriveProjects to Travel Drive
ReviewReview Project NotesProject Notes
ACCESS 2010 ProjectsACCESS 2010 Projects
Information - RelevanceInformation - Relevance
Project AProject A Creating Databases & Tables
Data - Reliability Data - Reliability
Project B Project B Creating Forms
Project C Project C Restricting Data Format & Validation Rules
Project D Project D Restricting Data to Values in Lists and Other Tables
Knowledge – Access & AnalysisKnowledge – Access & Analysis
Project E Project E Sorting & Filtering Data in Table
Project E Project E Creating & Using Queries
Project D: Data - ReliabilityProject D: Data - Reliability
*** *** Uncheck “read only box”Uncheck “read only box” in properties (right click on file for properties), then Apply, OK
*** *** Security Warning: Security Warning: Enable content
*** Handout*** Handout
1.1.Access 2010: Access 2010: Chapter 6Chapter 6 “Maintain Data Integrity” ( “Maintain Data Integrity” (with noteswith notes))
2.2.GardenCompany06GardenCompany06 GardenCompany06 - AfterRestrictingDataUsingValidationRules
*** Context : *** Context : pp. 143
6.5 Restricting Data to Values in Lists6.5 Restricting Data to Values in Lists pp.167 - 171
6.6 Restricting Data to Values in Other Tables 6.6 Restricting Data to Values in Other Tables pp.172 - 176
At step 17, refer to procedures at step 4 on page 173.
2. Uncheck “Read Only”
4. click “OK”
3. click “Apply”
1. Right click the file, then click
properties
select: select: enable contentenable content
DATABASESDATABASES & &
DATA WAREHOUSESDATA WAREHOUSES
Access to a Gold Mine of Information
““TheThe real valuereal value will come from getting thewill come from getting the right informationright information to theto the right peopleright people, at the, at the right timeright time, and giving those people the, and giving those people the toolstools to find to find the the meaningmeaning in it.”in it.” Declaration of IntegrationDeclaration of Integration
Abbie LundbergAbbie LundbergCIO 1Dec2002CIO 1Dec2002
performperforminformationinformation
processing processing ((5Cs5Cs)) for forMIS challengeMIS challenge
IA-15IA-15
DDAATTAABBAASSEESS
&&
DDAATTA A WWAARREEHHOOUUSSEESS
DB-2.aDB-2.a
DBMSDBMS(ACCESS)(ACCESS)
software that allows software that allows KW to perform these KW to perform these
tasks: tasks: 5C5Css
Victoria SecretVictoria SecretWSJ 9Dec02WSJ 9Dec02
create create informationinformation
ledgerledger
DATA MART(Excel)(Excel)
TTools to capture/cradle ools to capture/cradle datadata; ; communicate / convey communicate / convey informationinformation & &
create create knowledgeknowledge so meet the MIS challenge so meet the MIS challenge
queriesqueries & & reportsreports used to used to createcreate & & communicate communicate informationinformation from from operational database to a special operational database to a special form of database that supports form of database that supports
decision making with decision making with MIS MIS && EISEIS derived from a data derived from a data warehouse by KW using warehouse by KW using queriesqueries & & reportsreports to to conveyconvey information & information & createcreate knowledgeknowledge with with DSS (Excel)DSS (Excel)
journaljournal
capturecapture & & cradlecradle datadata about basic about basic
objects & events with objects & events with TPSTPS (CIS) using (CIS) using formsforms & & tablestables
capture capture cradle cradle datadata
What What ISIS Victoria’s Secret? Victoria’s Secret?
DATABASESDATABASES
CAPTURE & CRADLECAPTURE & CRADLE BASIC DATABASIC DATA
from store TPS & online CISfrom store TPS & online CIS
PROVIDE ACCESS BY PROVIDE ACCESS BY COMMUNICATING & COMMUNICATING &
CONVEYINGCONVEYING INFORMATION TO INFORMATION TO
KNOWLEDGE WORKERSKNOWLEDGE WORKERS
ENABLE ANALYSISENABLE ANALYSIS THAT CREATES THAT CREATES
KNOWLEDGE FOR KNOWLEDGE FOR DECISION MAKING & DECISION MAKING &
INNOVATIONINNOVATION
What What ISIS Victoria’s Secret? Victoria’s Secret?
DATABASESDATABASES
““Databases enable e-tailers “ not only to track individuals’ purchases Databases enable e-tailers “ not only to track individuals’ purchases closely but also to closely but also to analyzeanalyze broadbroad buyingbuying patternspatterns - - by gender, - - by gender,
location and credit-card type, at least, and even internet-service location and credit-card type, at least, and even internet-service provider, if an e-mail address is given.” provider, if an e-mail address is given.” WSJ 1.5WSJ 1.5
““This wealth of data can be used to This wealth of data can be used to targettarget marketingmarketing effortsefforts….Victoria Secret mails several catalogues a year, featuring ….Victoria Secret mails several catalogues a year, featuring largely the same merchandise but with different covers and product largely the same merchandise but with different covers and product
photos. Then it gauges which generates the most sales.” photos. Then it gauges which generates the most sales.” WSJ 1.5, 3.1-4WSJ 1.5, 3.1-4
Why Why MathMathWill RockWill RockYour World!Your World!Bus Week Bus Week 23 Jan0623 Jan06
“A generation ago, quants turned financefinance upside down. Now they’re mapping out ad campaignsmapping out ad campaigns (Victoria’s Secret) (Victoria’s Secret) and building new businesses form mountains of personal data.” 54.1
“They’re turning personal datapersonal data, trendstrends and onlineonline contentcontent into math, crunching the numbers and discovering new new efficiencies efficiencies and ways to marketways to market. Before long they’ll have a mathematical model of you.” 6.1
DB-12.bDB-12.b
What are Databases & Data Warehouses?What are Databases & Data Warehouses?financefinance - - accountingaccounting exampleexample
Extractive Extractive Petroleum Petroleum IndustryIndustry
SIC 1311SIC 1311
SEC EDGARSEC EDGAR accounting accounting
DATABASEDATABASE
S&P Compustat S&P Compustat market market
DATABASEDATABASE
Figure 3-16Figure 3-16
Data Mart RRARRA
TacticalTactical, aimed at , aimed at meeting an meeting an
immediateimmediate needneed
create create && convey convey knowledgeknowledge
efficienciesefficiencies in inresource allocationresource allocation in capital markets in capital markets MathMath WillWill RockRock YouYou
OLAPOLAP selectively extracts / selectively extracts / organizes organizes relevantrelevant datadata for for
analysis like analysis like ledgerledger,, strategicstrategic but but unfinishedunfinished concept concept
createcreate & &
cradlecradleinformationinformation
OLTPOLTP: : JOURNALIZINGJOURNALIZING BASIC EVENTS BASIC EVENTS capturecapture
&& cradlecradledatadata
whatis.comwhatis.com
DB-3.aDB-3.a
DBMSDBMS
Physical StructurePhysical StructureHow is a database organized?How is a database organized?
ITITMISMIS
AccessAccess
Why MathWhy Math Will RockWill Rock
Your WorldYour World
Logical StructureLogical Structure(d e c i s i o n s)(d e c i s i o n s)
DBMS (DBMS (AccessAccess) is ) is software bridgesoftware bridge between between
user’s logical requests and user’s logical requests and physical structure so physical structure so more more
easily get to theireasily get to their work work performing the performing the 5Cs5Cs
Bus. WeekBus. Week23Jan0623Jan06
p.62p.62
TableTable
RowsRowsObservationObservation
ColumnsColumnsCharacteristicsCharacteristics
DB-4.aDB-4.a Figure 3.4Figure 3.4
FacilityFacility file file ((tabletable))
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
Part file Part file ((tabletable))
is composed ofis composed of files files ((tablestables))
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
DB-4.aDB-4.a
FieldsFields in in columnscolumnsrepresentrepresent characteristicscharacteristics
Part Part filefile (table) (table)
Figure 3.4Figure 3.4
Facility Facility filefile (table) (table)
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
DataData DictionaryDictionarydefines defines properties /properties / characteristicscharacteristics in in fields of a table, fields of a table, includingincluding keyskeys
is composed of is composed of filesfiles
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
DB-4.aDB-4.a
RecordsRecords in in rows rows entered entered byby formsforms
Fields in Fields in columnscolumnsrepresent represent characteristicscharacteristics
Part Part filefile (table) (table)
Figure 3.4Figure 3.4
Facility Facility filefile (table) (table)
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
DataData DictionaryDictionarydefines defines properties / properties / characteristicscharacteristics in fields in fields including including keyskeys
PrimaryPrimary keyskeys define define recordsrecords & & relationshipsrelationships withinwithin a filea file
Foreign
Foreign key
skey
s iden
tify re
lationsh
ips
identif
y relatio
nships b
etween
between
filesfiles
is composed of is composed of filesfiles
LogicalLogical StructureStructure ofof aa RelationalRelational DatabaseDatabase
Figure 3.6Figure 3.6
AccessAccessProjectProject AA
tables, propertiestables, properties
AccessAccessProjectsProjects BB, , CC, , DDforms & controlsforms & controls
AccessAccessProjectsProjects EE, , FF
filter, sort, queryfilter, sort, query
shared shared informationinformation
&&decentralized decentralized computing computing
capturingcapturingreliabilityreliability
conveyingconveyingaccessaccess
creatingcreating
analysisanalysis
cradlingcradling
relevancerelevance
cradlingcradling
communicatingcommunicating
accessaccess
DBMS processDBMS process data (reliability) data (reliability) info info (relevance) (relevance) knowledge knowledge
(access & analysis) (access & analysis)
AccessAccess ProjectsProjects demonstrate how demonstrate how DBMSDBMS subsystems subsystems provide the tools provide the tools to to createcreate and and useuse the the logicallogical structurestructure of a of a relational relational databasedatabase
ACCESSACCESS PROJECTS PROJECTSDatabaseDatabase
“What’s really greatreally great about databases and DBMSs is that the conceptsconcepts are the samesame whether you’re using a personal DBMSs package or a large mainframe DBMS package.”
“...once you learn database and DBMS conceptsconcepts while using a personal DBMS package (like MS ACCESS)(like MS ACCESS), you can applyapply your knowledge directlydirectly to business environments where other DBMS packages may be used.” Haag.2004.94Haag.2004.94
Team ProjectsTeam Projects use Microsoft’s DBMS ACCESSACCESS to demonstrate: Data Definition: tables: fields, properties, keys Project AProject A Application Generators: forms: controls Projects B, C, DProjects B, C, D Data Manipulation: sorting , , filters & queries Projects E, FProjects E, F
A-2aA-2a
What is a Data Definition Subsystem?What is a Data Definition Subsystem?
creates and maintains the data dictionary that defines the properties and logical structure of data in a file (table)(table)
H.89.8-90.1H.89.8-90.1
properties include: field name, data type, format, default value, validation rule, keys
contributes to maintaining data integrity
Team Project ATeam Project A
DB-7DB-7 DBMSDBMS
What is Application Generation Subsystem?What is Application Generation Subsystem?
contains facilities to develop transaction-intensive applications that require a detailed series of tasks to process a transaction. This subsystem includes: Tools for creating data entry screens and controls in formsforms Programming languages specific to the DBMS: SQLSQL
(standardized query language) in Access Interfaces to commonly used programming languages that
are independent of any DBMS: C, C++, Cobol
DB-8DB-8 DBMSDBMS
Team Projects B, C, DTeam Projects B, C, D
What is a Data Manipulation Subsystem?What is a Data Manipulation Subsystem?
most often the primary interface between a user and the data in a database
tools in the data manipulation subsystem include: views: filtering & sorting Project EProject E report generatorsqueries Project Project
FF
DB-9.aDB-9.a DBMSDBMS
helps you change data in a database and mine it for valuable information.
What What benefits / costsbenefits / costs are related to are related to databases & data warehouses?databases & data warehouses?
DatabaseDatabase
POTENTIAL BENEFITSPOTENTIAL BENEFITS improved data integrity data integrity (reliability: forms, controls,
validation rules) increased user productivityproductivity (input: forms;
output: queries/reports) increased securitysecurity reduced data redundancyredundancy (capture oncecapture once, use manyuse many)
POTENTIAL COSTSPOTENTIAL COSTS development training hardware maintenance software
DB-13DB-13
What Are the Critical Success Factors What Are the Critical Success Factors for Databases & Data Warehouses? for Databases & Data Warehouses?
Expectations are communicated to the users
User involvement is ensured throughout the project
Project has a good sponsor, from the business side not IT
Project team has the right skill set dedicated to the team
A realistic schedule including all necessary tasks
Tools have been chosen to match the needs of the users
Users are trained in the way they plan to use the warehouse
DataWarehousing.com (7 Feb 2001)DataWarehousing.com (7 Feb 2001)
Data WarehouseData WarehouseDB-14DB-14
Issues addressed within SDLCIssues addressed within SDLC
Who will be the usersusers? H.104.6-H.104.6-88
What datadata will they need? H.104.9-105.2H.104.9-105.2
What data mining toolsmining tools will the users need ? How often should the data be updatedupdated? H.105.3-4H.105.3-4
What types of databasesdatabases, DSSDSS & networksnetworks are most appropriate?
Who should oversee the information resource? CIOCIO should manage information which is as important as the
financial resources managed by CFO.
DB-12.aDB-12.a
Issues addressed within SDLCIssues addressed within SDLC
Who has ownershipownership of (responsibility for) the quality of specific information?
What ethicalethical, securitysecurity and privacyprivacy issues are involved in using information and IT?
How should technology changestechnology changes be assessed? Chasing technology is very expensive The real issues is whether the new technology manages
information better and if the benefitbenefit exceeds the costcost
DB-12.bDB-12.b
SDLC SDLC
Next topic Next topic
SystemsSystemsDevelopmentDevelopment
LifeLifeCycle Cycle
ASSIGNMENT: ASSIGNMENT: Work with Work with teamteam on on ACCESS ACCESS project. project. EmphasizeEmphasize course concepts to course concepts to
explain explain whywhy the procedures are the procedures are important in: important in:
meeting the meeting the MIS challengeMIS challenge
performing the performing the 5Cs5Cs, ,
processing processing datadata into into informationinformation then then knowledgeknowledge, ,
data sharing data sharing and and decentralized decentralized computingcomputing that support decision that support decision making and innovation making and innovation
building building CISCIS (TPS), (TPS), MISMIS (dashboards), (dashboards), DSSDSS, , WSSWSS
creating creating informationinformation partnershipspartnerships, , virtualvirtual – – learninglearning organizations that are organizations that are timelesstimeless & & locationlesslocationless
These These exercisesexercises are essential are essential to the to the synthesissynthesis of concepts, of concepts, frameworks and ideas into a frameworks and ideas into a new whole that new whole that changes the changes the brain brain realreal learninglearning ((BloomBloom Hierarchy of Learning Hierarchy of Learning Objectives; Objectives; WSJWSJ 19Jan07 19Jan07 “Thinking Can Change the “Thinking Can Change the Brain”)Brain”)
This requires reviewing This requires reviewing Information AgeInformation Age, , IT SystemsIT Systems, , IT StrategiesIT Strategies PowerPoints PowerPoints and using those concepts, and using those concepts, frameworks and ideas to frameworks and ideas to analyzeanalyze and and evaluateevaluate the the ACCESS procedures.ACCESS procedures.