ACS-1803 Introduction to Information Systems

1. ACS-1803Introduction to Information Systems Data Management Lecture Outline 4, Part 2 Instructor: Kerry Augustine Fundamentals of Information Systems, Sixth Edition Ralph Stair, George Reynolds ACS-1803 Introduction to Information Systems

2. Data Modeling and Database Characteristics • When building a database, an organization must consider: • Content:What data should be collected and at what cost? • Access:What data should be provided to which users and when? • Logical structure:How should data be arranged so that it makes sense to a given user? • Physical organization:Where should data be physically located?

3. Data Modeling • Building a database requires two types of designs: • Logical design: • Abstract model of how data should be structured and arranged to meet an organization’s information needs • Physical design: • Starts from the logical database design and fine-tunes it for performance and cost considerations • Planned data redundancy: • Done to improve system performance so that user reports or queries can be created more quickly

4. Data Modeling (continued) • Data model: • Diagram of data entities and their relationships • Enterprise data modeling: • Starts by investigating the general data and information needs of the organization at the strategic level • Entity-relationship (ER) diagrams: • Data models that use basic graphical symbols to show the organization of and relationships between data

5. Data Modeling IIlustration (See handout) • StNumber • StName • Street • City • StPhone • StMajor • StNumber • Cno • Cname • Grade STUDENT GRADE gets Next, identify the relationships and design the tables First, list data fields See the following had out for a more detail illustration of a data modeling example. • Student Number • Student Name • Street • City • Student Phone • Student Major • Course Number • Course Name • Grade Student Grade • StNumber • StName • Street • City • StPhone • StMajor • StNumber • Cno • Cname • Grade

6. REA Model • an ERD where the entities model specific business RESOURCES, EVENTS and AGENTS is called a Resource Event Agent model • This model is useful in designing databases for various business information systems (e.g. modern accounting systems) • REA treats the accounting system as a virtual representation of the actual business. In other words, it creates computer objects that directly represent real-world-business objects. The real objects included in the REA model are: • goods, services or money, i.e., RESOURCES • business transactions or agreements that affect resources, i.e., EVENTS • people or other human agencies (other companies, etc.), i.e., AGENTS • These objects contrast with conventional accounting terms such as asset or liability, which are less directly tied to real-world objects. For example, a conventional accounting asset such as goodwill is not an REA resource.

7. REA Example

8. The Database System and MIS • The combination of the database, the DBMS, and the application programs that access the database is referred to as a database system. • The database system coupled with a set of hardware, software, telecommunications, people, and procedures makes up a management information system (MIS).

9. Database Management Systems (DBMS) • Creating and implementing the right database system: • Ensures that the database will support both business activities and goals • Capabilities and types of database systems vary considerably

10. Levels of a DBMS Level Term Term Definitions Lowest Individual characteristics about an ENTITY. Fields are also called attributes or columns depending on the type of DBMS Field A group of fields or attributes to describe a single instance of an ENTITY. These are also called rows depending on the DBMS Record A collection of records or instances for a given ENTITY. These are also called tables, depending on the DBMS File A collection of files or entities containing information to support a given system or a particular topic area Database Highest

11. Overview of Database Types • Flat file: • Simple database program whose records have no relationship to one another • Single user: • Only one person can use the database at a time • Examples: Access, FileMaker Pro, and InfoPath • Multiple users: • Allow dozens or hundreds of people to access the same database system at the same time • Examples: Oracle, Sybase, and IBM

12. Providing a User View • Schema: • Used to describe the entire database • Serves as the “blue print” to the design of the dbms and focuses on the relationships between entities. • Can be part of the database or a separate schema file. • Three examples of schemas are shown on the next slide: • External Level Subschema (end user view) is focused at the end user level. It serves as a map to the Conceptual View to the design of the DBMS. • Conceptual Schema (design view) and the Internal Level Schema (data dictionary view) is used by database analysts and design specialists to map the DBMS.

13. Subschema--User A Subschema--User B Subschema--User C Smith . . . A Jones . . . B Arnold . . .D Mapping external-level views to conceptual-level schema Classes Enroll Student Cash Receipt Mapping conceptual-level items to internal-level descriptions

14. Subschema--User A Subschema--User B Subschema--User C Smith . . . A Jones . . . B Arnold . . .D Mapping external-level views to conceptual-level schema • An employee’s access to data should be limited to the subschema of data that is relevant to the performance of his/her job. Classes Enroll Student Cash Receipt Mapping conceptual-level items to internal-level descriptions

15. Subschema--User A Subschema--User B Subschema--User C Smith . . . A Jones . . . B Arnold . . .D • Database analysts and design specialists are frequently involved in developing the conceptual- and external-level schemas. Mapping external-level views to conceptual-level schema Classes Enroll Student Cash Receipt Mapping conceptual-level items to internal-level descriptions

16. The Database Management System • System software that sets up the database structure (‘skeleton’ on disk according to a certain model, fills the structure with data and retrieves the data to provide meaningful information (P. 118)) • Parts of dbms: • data dictionary: defines each field and record, explains what each field means and who is authorized to update it • query language: used to extract data that satisfy certain criteria from a database. Used to develop query retrieval commands and reports – which are the two main forms of output from a dbms.

17. The Database Management System • Data Dictionary • Is a document that database designers prepare to help individuals enter data • Provides several pieces of information about each attribute in the database including: • Name • Key (is it a key or part of a key?) • Data Type (date, alphanumeric, numeric, etc.) • Valid Value (the format or numbers allowed) • Can be used to enforce Business Rules which are captured by the database designer to prevent illegal or illogical values from entering the database. (e.g. who has authority to enter certain kinds of data)

18. The Database Management System e.g., SQL: Structured Query Language: - Popular language for making requests to a relational dbms (p. 132 bot) SELECT e.g.,LAST NAME, DEPARTMENT, SALARY FROM EMPLOYEE WHERE DEPARTMENT = ‘4530’ AND SALARY > 25000 Popular dbms: Access, FoxPro - individual Oracle - Client / Server network Using Access (hand out)

19. Database Systems Activities – Data Entry Employment Applications • Example • Data is entered from paper employment applications into a form entry screen • The entry forms are designed to match the paper forms for ease of entry • The form data is processed by the entry program and then stored in the employment database Enter Forms (Form Entry Screen) (Form Entry Program) (Employment DB)

20. The Database Management System Query – A database function that extracts and displays information from a database given selection parameters. • SQL (Structured Query Language) • A language to select and extract data from a database • The industry standard language for relational databases • QBE (Query by Example) • A technique that allows a user to design a query on a screen by dragging and placing the query field in their desired locations • Example – Display applicants entered in the last 30 days • Query parameters are selected in the query request screen • The database program uses SQL to query and present the result (Query Request) (Query Program) (Employment Query)

21. Database Systems Activities – Query Report – A database function that extracts and formats information from a database for printing and presentation • Report Generator • A specialized program that uses SQL to retrieve and manipulate data (aggregate, transform, or group) • Reports are designed using standard templates or can be custom generated to meet informational needs • Example – Report on applicants entered in the last 30 days • Report parameters are selected in the report request screen • The database program uses SQL to query and present the result (Query Request) (Query Program) (Employment Report)

22. Data Warehouses, Data Marts, and Data Mining • P. 138-142 • Data warehouse: • Database that holds business information from many sources in the enterprise • Data mart: • Subset of a data warehouse • Data mining: • Information-analysis tool that involves the automated discovery of patterns and relationships in a data warehouse

23. Data Warehouses, Data Marts, and Data Mining (continued) Operational Informational Extract Data Extract Data Data Mart Department Databases Data Warehouse • Day-to-day department transactions • Used primarily by departments • Extracted department transactions • Used for business analysis • Extracted subset of a data warehouse • Used for highly specific business analysis

24. Data Warehouses, Data Marts, and Data Mining (continued) • Predictive analysis: • Form of data mining that combines historical data with assumptions about future conditions to predict outcomes of events • Used by retailers to upgrade occasional customers into frequent purchasers • Software can be used to analyze a company’s customer list and a year’s worth of sales data to find new market segments

25. Data Warehouses, Data Marts, and Data Mining (continued)

26. Databases and Computer Networks - can have centralized database (at one location) -  or, fragmented, distributed database: different parts of the database are stored in the locations where they are accessed most often, but continue to be fully accessible to others; Database Administrator (DBA): person in an IS department responsible for the database (p. 135) Databases on the Web: - the only way for organizations to conduct business on the Internet is to people outside the organizations access to their databases (e.g., items for auction) (p. 138)

27. Visual, Audio, and Other Database Systems • Visual databases: • Can be stored in some object-relational databases or special-purpose database systems • Virtual database systems: • Allow different databases to work together as a unified database system • Spatial data technology: • Using database to store and access data according to the locations it describes

28. Text Databases • Databases are thought of as consisting of structured records of fields; but can also have ‘databases’ of text • these can be on the web and can use search engines to search for keywords • - such ‘databases’ can come on CD-ROMs

29. Using Databases with Other Software • DBMSs can act as front-end or back-end applications: • Front-end applications interact directly with people • Back-end applications interact with other programs or applications

30. Summary • DBMS: • Group of programs used as an interface between a database and its users and other application programs • Basic functions: • Providing user views • Creating and modifying the database • Storing and retrieving data • Manipulating data and generating reports

31. Summary (continued) • Data warehouses: • Relational database management systems specifically designed to support management decision making • Data mining: • Automated discovery of patterns and relationships in a data warehouse