Mgt 240 Lecture

1. Mgt 240 Lecture Database Management Systems: Access and Corporate Databases February 22, 2005

2. Miscellaneous • Homework 4 due Friday, February 25th by 5pm • Must use Excel 2003 – list range features not available on earlier versions • Near term schedule • This week – database management systems • Next week – decision support systems • Week after spring break – Lecture Exam Two

3. 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

4. Database Management Systems • Import and export data manually • Importing from another Access database • Import PledgeRecords table from Pledge database into Redwood database • Importing from an Excel file • Import Rafting.xls into Trips.mdb • Copying and pasting • 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

5. Storing and Retrieving Data

6. 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)

7. 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)

8. 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

9. 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

10. 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

11. 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 non-technical users • Example: ZooFunds.mdb in Access tutorial 10, case 3 folder • Example: Grades database for this course

12. Database Management Systems • Security management for databases • Encrypt database • Password protect database • Assign user permissions • Limit user to subset of objects in the database

13. Database Management Systems • Backup of databases • In Access • Back up command • Replicate command

14. Corporate Data Management: Database Evolution at ND Non- Relational HP databases Relational Databases Data Warehouse

15. 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

16. Corporate Data Management: Current Databases at ND • Data Dictionary Report Page

17. 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?

18. 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

19. 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 Self-Service application, which can be used from any computer that is connected to the Web.

20. 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.

21. 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.

22. 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

23. Elements of a Data Warehouse

24. Data Warehousing at ND • Data Dictionary Report Page

25. 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.

26. 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