1 / 34

Management of Information Systems: 45-870

This course covers the concepts and techniques of designing and creating databases for effective information systems management. Topics include data and database definitions, database structures, and using Access2000 for relational databases.

berryg
Download Presentation

Management of Information Systems: 45-870

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Management of Information Systems: 45-870 Mini-3 Spring 2000

  2. Agenda • Announcements and Reminders • Technology Byte Presentations • In-Class Exercise • Designing and Creating Databases • Data/Database Definitions and Concepts • Database Structures • IT Exercise II Assigned • Using Access2000 to Create Relational Databases

  3. Announcements • Technology Byte Presentations • Today (1/27): • Section A: E-Team, Tigers • Section B: E-Sales, The Masters • Tuesday (2/1): • Section A: DeathSpiral.Com, E-Sixpack • Section B: Jeree’s Kids, Hot TaMaLes • IT Exercise I Due Today

  4. In-Class Exercise • Reflection on IT Exercise I: • Pros and cons of using an HTML generator (specifically FrontPage) • Reflections on use • What process did you use as a team to do this assignment? • What aspects of your process were effective? Problematic? • How do you feel about your end product? • Based on this experience, what managerial concerns or challenges do you have in relation to developing web pages for company intra/ inter/ extranets?

  5. The IT Architecture TELECOMMUNICATIONS SOFTWARE & DATA HARDWARE

  6. Key Definitions • Data: • Information: • File: • Database: • Metadata:

  7. Disadvantages of Traditional File Processing Systems • Data Dependence • Data Redundancy • Limited Data Sharing • Long Development Times • Excessive Maintenance

  8. Advantages of a Database Approach • Minimal Data Redundancy • Data Integration • Data Independence • Improved Data Sharing • Enforcement of Consistent Standards and Constraints • Better Access • Security, Backup/Recovery, Concurrency

  9. Database Concepts • Entities: • Examples:

  10. Database Concepts • Relationships: • Types: • 1:1 • 1:M or M:1 • M:M

  11. Database Concepts • Attributes: • Examples:

  12. Database Concepts • Records: • Example:

  13. Database Concepts • Keys: • Types and Examples:

  14. Database Structures • Physical Structure: how data is stored on and accessed from tape or disk. • Logical Structure: how the data is perceived by the user. • communication tool • design aid

  15. Database Structures • 3 common logical DB structures • Hierarchical • Network • Relational (most popular) • newest logical DB structure • Object Oriented

  16. 7345 | Professor | Ludwigsohn | Comp.Sci. Winter 1995 | 2 |Promotions Fall 1996 | 3 |None Intro | 7001 | 3 | 45 VBasic | 7232 | 4 | 25 VBasic2 | 7233 | 4 | 25 Hierarchical Data Structure

  17. Hierarchical Data Structure • Requires well-defined, prespecified access path; supports 1:M • Good for applications that involve very few types of queries but lots of data • Very efficient but inflexible • Not appropriate for business users

  18. 7345 | Professor | Ludwigsohn | Comp.Sci. Winter 1995 | 2 |Promotions Fall 1996 | 3 |None Intro | 7001 | 3 | 45 VBasic | 7232 | 4 | 25 VBasic2 | 7233 | 4 | 25 Network Data Structure

  19. Network Data Structure • More flexible (supports M:M) but still requires pre-specified path • Not as efficient; must limit links among files or volume of data • Difficult to modify and add links between files • Not appropriate for business users

  20. Relational Data Structure Faculty Table Dept Table Course Table

  21. Relational Data Structure • Most flexible; if two tables share a field, they can be linked • May not be efficient for high transaction volumes • Easy to modify • Most appropriate for business users

  22. Object Oriented structure • not yet widely used in practice • passive: separates object attributes from methods • active: stores object attributes and methods together

  23. Normalizing Database Structures • Why does anyone need to do this: • redundancy • update efficiency • possible inconsistencies

  24. Steps in “normalizing” data • 1. break repeating groups of fields into separate entities, each with its own unique key (first normal form) • 2. Identify fields that are facts about only a portion of a key. Split into separate entities. (second normal form) • 3. Identify fields that are facts about some other non-key field. Split into separate entities. (third normal form)

  25. EXAMPLE: Sales Report Data • Data includes: • Sales Person # • Sales Person Name • Sales Area • Customer Name • Customer Number • Warehouse Number • Warehouse Location • Sales Amount

  26. Sales Report Data: Additional Information • Only one number is assigned to each sales person • Only one number is assigned to each customer • Only one number is assigned to each warehouse • A salesperson serves many customers • A customer generates many sales • Customers receive their items from one warehouse

  27. Step#1: Pull out repeating groups (First Normal Form) FROM: SalesReport (SalesPerson#, SalesPerson-Name, Sales-Area, Customer#, Customer-Name, Warehouse#, Warehouse-Location, Sales-Amount) TO: SalesPerson (SalesPerson#, SalesPerson-Name, Sales-Area) SalesPerson-Customer (SalesPerson#, Customer#, Customer-Name, Warehouse#, Warehouse-Location, Sales-Amount)

  28. Step#2: Pull out facts about a portion of the key (Second Normal Form) FROM: SalesPerson-Customer (SalesPerson#, Customer#, Customer-Name, Warehouse#, Warehouse-Location, Sales-Amount) TO: Sales (SalesPerson#, Customer#, Sales-Amount) Customer-Warehouse (Customer#, Customer-Name, Warehouse#, Warehouse-Location)

  29. Step#3: Pull out facts about a non-key attribute (Third Normal Form) FROM: Customer-Warehouse (Customer#, Customer-Name, Warehouse#, Warehouse-Location) TO: Customer (Customer#, Customer-Name, Warehouse# ) Warehouse (Warehouse#, Warehouse-Location)

  30. Normalized Relations for this Data Sales (SalesPerson#, Customer#, Sales-Amount) SalesPerson (SalesPerson#, SalesPerson-Name, Sales-Area) Customer (Customer#, Customer-Name, Warehouse#) Warehouse (Warehouse#, Warehouse-Location)

  31. From Normalized Relations to an Entity Relationship Diagram • each normalized relation becomes an entity • identifier of each normalized relation becomes the primary key • secondary identifier of each normalized relation becomes a foreign key • Characteristics of each normalized relation determine relationship between entities

  32. Entity Relationship Diagram Example (SalesPerson#, SalesPerson-Name, Sales-Area) SalesPerson (Customer#, Customer-Name, Warehouse#) Sales Customer (SalesPerson#, Customer#, Sales-Amount) Warehouse (Warehouse#, Warehouse-Location)

  33. IT Exercise II • Creating, updating, and querying a database for CD On-Line, a new “virtual” store that sells CDs over the Internet • Due Thursday, Feb. 3 • Done with your Team • Submit exercise answers as a report (hardcopy) – one for each team

  34. From an ERD to a Database in Access2000 • Short Demo • Designing tables • Designing relationships • Adding data to tables • More “tips” relating to the use of Access 2000 for IT Exercise #2 will be linked to the exercise web page on the course web site

More Related