Transcript Mgt 240 Lecture

Mgt 240 Lecture
Database Management Systems:
Access and Corporate Databases
September 30, 2004
Miscellaneous

Homework 4 due Friday, October 1st by 5pm



If you save your text document as a word document, not an rtf file, it
will be a lot smaller
Must use Excel 2003 – list range features not available on earlier
versions
Near term schedule


This week – database management systems
Next week – data warehouses and exam review


Two weeks from now – exam


Look over reading questions and come prepared to ask about any you aren’t
sure of
In regular class session
Reading questions

A lot assigned today – really cover last week, this week, and next
week’s topics
Database Management
Systems

Manage creation, implementation, use, and
updates of databases



Provide an interface to databases
MS Access is a DBMS
Facilitate








Importing and exporting data
Defining logical and physical structure of data
Documentation of logical and physical structure of data
Queries to database
Managerial reporting
Human interface to database
Security management of databases
Backup of databases
Database Management
Systems

Import and export data manually

Importing from another Access database


Importing from an Excel file


Import Rafting.xls into Trips.mdb
Copying and pasting


Import PledgeRecords table from Pledge database into
Redwood database
Copy and paste records from Groups.mdb into
Trips.mdb booking table
Allow other applications to request data or
input data

Important for corporate databases where database
updating and querying is automated
Storing and Retrieving Data
Database Management
Systems

Define the logical and physical structure of the
data


Data definition language (DDL) - a collection of instructions and
commands used to define and describe data and data relationships in a
specific database
Data definition in Access (Trips.mdb)





Defining fields
Primary and foreign keys
Data types
Field properties
Table relationships


Analyzer (Gourmet1.mdb)
Relationships window (Redwood.mdb in Access tutorial 2, case 3 folder)
Database Management
Systems

Documentation of logical and physical
structure of data

Data dictionary – detailed description of data in a database








Name of data items
Aliases or other names that may be used to describe a
data item
Acceptable range of values for an item
Data type
Data item length
Modification and access permissions
List of reports in which data item is included
Documenter in Access (Redwood.mdb in Access tutorial 2,
case 3 folder)
Typical Uses of a Data Dictionary







Provide a standard definition of terms and data elements
Assist programmers in designing and writing programs
Simplify database modification
Reduce data redundancy
Increase data reliability
Speed program development
Ease modification of data and information
Database Management Systems


Facilitate queries to the database
Access has provides two ways to construct queries
 Query window or query by example
 Graphical user interface (GUI)
 Allows drag and drop of fields into table
 Structure Query Language
 Standard query language for databases
 Queries created in query window are translated into SQL in
order for Access to execute them
 Can create queries directly in SQL
 Can you think of any advantages of SQL?
 Example: Redwood.mdb in Access tutorial 3, case 3 folder
Database Management Systems

Managerial reporting




One of the key capabilities of DBMS’s
Generate periodic or ad hoc reports
In Access can create reports by wizard or in design view
Example: Redwood.mdb in Access tutorial 4, case 3 folder
Database Management Systems

Human interface to database




Access provides a graphical user interface to do all the database
tasks we have discussed so far
Can go further by constructing a form-based interface for nontechnical users
Example: ZooFunds.mdb in Access tutorial 10, case 3 folder
Example: Grades database for this course
Database Management Systems

Security management for databases




Encrypt database
Password protect database
Assign user permissions
Limit user to subset of objects in the database
Database Management Systems

Backup of databases

In Access


Back up command
Replicate command
Corporate Data Management:
Database Evolution at ND
NonRelational
HP databases
Relational
Databases
Data
Warehouse
Corporate Data Management: Current
Databases at ND

Database Management Services


Currently a number of databases serving
different functions at the University
HP-3000 Databases

Undergraduate admissions archive database




Database description
Database listing
Database diagram
Relational databases



Database diagrams
Undergraduate admissions star schema
Online photo
Corporate Data Management:
Current Databases at ND

Data Dictionary Report Page
Database Information Requests:
Current Databases at ND


Decision support and ad hoc reporting

Extracts of data that are not available thru application menus for analysis purposes

Formatted reports in Excel, and/or Microsoft Access.

Ad-hoc analytic reports.
Information that must be provided when making a report request

This request is for (one of the following):
New Adhoc report
Modification to an existing report on your HP menu
New regular report
Not sure
How often will this be run (one of the following):
One time only
Once a year
Once a month
More frequently
Not sure
Description: Describe what you need or what problem you are trying to solve.
Current method: Describe how you are getting the information now.
Who will have access to this report or extract?
Target Date: What is the desired target date for delivery/implementation?
Corporate Data Management: The
Future of Computing at ND

Corporate data at ND
 Renovare project
 The mission of the Renovare project is to improve access to data
for students, faculty, and staff, streamline and automate the
University's business processes, and increase integration between
systems
 Improve Access to Data
 Implement a convenient, intuitive, Web-based facility for
student and faculty self-service
 Provide timely and accurate information for management
decisions
 Provide flexible, ad-hoc reporting capabilities
 Provide a one-stop-shop to deliver information from
administrative systems to the desktop
 Increase Integration Between Systems:
 Reduce redundancy of data
 Provide more timely and accurate interfaces between
systems
 Promote greater cooperation among University offices
and better interdepartmental planning
 Benefits for ND community at large
Renovare Project: The Future
of Data Management at ND

Benefits for students



Faculty/Student System
Launch date: July 1, 2005
Information from admissions through
academic history is available in a secure Web
environment. Students can apply for
admission, register for classes, retrieve
financial aid data, and access other processes
and information through the Student SelfService application, which can be used from
any computer that is connected to the Web.
Renovare Project: The Future of
Data Management at ND

Benefits for faculty
 Faculty/Student System
 Launch date: July 1, 2005
 Faculty can review call lists, report grades,
submit advising reports, and access course
management tools online through the Faculty
and Advisor Self-Service application, which
provides an interactive interface to the
administrative database for advisors, teaching
faculty, and other academic administrators in
the colleges, schools, and institutes without
compromising system security or institutional
policies.
Data Warehouses, Data Marts,
and Data Mining



Data Warehouse - a database that collects business
information from many sources in the enterprise,
covering all aspects of the company’s processes,
products, and customers.
Data Mart – a subset of a data warehouse.
Data Mining - an information analysis tool that involves
the automated discovery of patterns and relationships in
a data warehouse.
Data Warehousing at ND – Why
Construct a Data Warehouse?






Operational databases are built for applications not for easy
querying and analysis
Queries and reports add overhead to operational applications-slow systems down.
Warehouse/Mart structured for ease of query and analysis;
isolated from operational data so that those systems not affected
by lengthy queries.
Historical data adds overhead as databases get larger and larger -this requires purging databases on a periodic basis.
Data warehousing moves legacy databases to newer database
structures -- e.g., move Turboimage HP data to relational
database.
The warehouse allows data from disparate sources to be combined
into one integrated view of data. A good example would be
information from the campus ID card system combined with
information from the campus student system
Elements of a Data Warehouse
Data Warehousing at ND
Data
Dictionary Report Page
Data Warehousing at ND – Key Steps







Extraction first to operational data store into a relational model (in
our current environment, Oracle)
Gives immediate benefit of data in relational database but in same
basic structure as on legacy HP
Data stewards and their areas can access this data with new tools
-- Business Objects, Excel, WEBdb (Oracle Web Database Portal)
Data cleansing and analysis can be done without affecting
operational systems, yet feedback is given to data stewards and
applications group to correct sources of bas data.
Transformation to customized views of data in Warehouse/Mart
expedited by use of Oracle Warehouse Builder
Data stewards determine access privileges
Building of metadata repository to document data sources,
meaning, transformations that were necessary as defined, for
example, by accepted Institutional Research standards or by the
Data Stewards.
Business Intelligence
Gathering enough of the right information in a
timely manner and usable form.




Competitive intelligence
Counterintelligence
Knowledge management
What kinds of new business intelligence could be
derived from ND’s new data warehouse?

Data Dictionary Report Page
Assigned Reading


Read chapter 3 (pp. 98-127) in text
Following are questions that you should be sure you can answer based on your
understanding of the text:














What is a DBMS?
What is a database environment?
Why are databases important to a business’s success?
What is the traditional approach to data management and why is it problematic?
What is the database approach and how does it improve on the traditional approach?
How can databases inform marketing and product mix decisions?
What kind of decisions must be made when building a database?
What is the difference between the logical and physical design of a database?
What is a data model?
What is an entity-relationship (ER) diagram?
How does the relational database model improve upon the hierarchical and network models?
What is the key to linking tables in a relational database?
Why is it important to be able to link tables in a relational database?
What is the role of a data definition language?
Assigned Reading Questions
(continued)











What is the purpose of a data dictionary?
What are the responsibilities of a database administrator?
What are some of the factors to consider when selecting a database
management system?
What is a data warehouse?
What is a data warehouses primary advantage?
What must be done to data from other databases before it can be
incorporated into a data warehouse?
What is a data mart and what is its purpose?
What is data mining and what is its objective?
What kind of decisions can be based on business intelligence?
What is knowledge management?
What is on-line analytical processing and how does it relate to data
warehouses?