Organizing Data and Information for Use in Decision Making

1. Organizing Data and Information for Use in Decision Making (MIS)

2. Views of Data • The Physical View • Concerned with • The Logical View • Concerned with how data is represented so that it will be meaningful to users.

3. FILE in Access TABLE in Access Bit: a single zero or one

4. E (usually called simply Entity) • It is a concept that relates to the items about which you wish to store information--these “items” can include conceptual ideas such as “philosophers’ thoughts,” people such as “student,” things such as “refrigerators” or places such as “city.” • G • EMPLOYEES Table

5. Instance • An of an Entity Class is called an of that class. Manhattan is an instance of the entity class city. • A More • A record in Access

6. Attribute • C • Employee #, Last Name, First Name, Hire Date, Dept. Number • The columns in Access • A PRIMARY KEY is a field in a database file that • ID Number Social Security Number

7. Four main database models • Hierarchical • Network • Relational (the ) • Object-Oriented

8. Relational Database Model • Uses a series of two-dimensional called to store information relating to a • Tables = Relation: A specific entity class • Rows = or individual file folders. • Columns = Attributes or • The relational database model is the widely used database model at the present time.

9. Spreadsheet Data Used to create a database

10. Steps to Create a Relational Database • Define entity classes and primary keys • Define relationships among entity classes • Define attributes (fields) for each relation • Use a data definition language to create the database.

11. Step One: Need a database that keeps track of the following information about your health club: The first name, last name, and id number for each of your members The address (street, city, state) where your members live The gender of each member and what class(es) they are taking The course name, number, and time that each of course meets The first name, last name, and id number for each of your employees. The home phone number for each employee, as well as their salary amount and the date that they were hired.

12. Data Assumptions/Traits • Member Number is unique. Each member will have only one Member Number. There is oneMember for each Member Number. • At this time, each member can enroll in only one course (at a time) that is offered by your health club. Therefore, eachmemberwillhaveonlyoneclass number recorded at any time. (At some point in your assignment, you will make it possible for your members to take more than one course at a time) • Class Number is the unique identifier for each class that your health club offers. There is only one course for each class number. • Employee Number is the unique identifier for each employee that works at your health club. There is only one employee for each employee number.

13. Data Assumptions/Traits • A member can take only one course at a time. • Only one instructor can teach a particular course. • Only one section of a course is taught at any one time. • A course may be taken by many different students. • An instructor can teach more than one course at any one time. • THEREFORE WE HAVE THE FOLLOWING ENTITY CLASSES: • MEMBERSHIP - primary key is Member Number • COURSES - primary key is Class Number • EMPLOYEES – primary key is Employee Number

14. Step 2: Define between entity classes. Using an Entity Relationship (E-R) Model Diagram, we can represent the entity classes and their relationships: COURSES EMPLOYEES 1:M 1:M MEMBERSHIP COURSES Key: Member Number Key: Class Number Key: Class Number Key: Employee Number STEP3: Define the attributes in each Entity which has been converted into a

15. Normalization • A technique used to make complex databases more efficient • Break one large • Eliminate all repeating groups in records • Eliminate • Assure that each field in the relation depends only on the primary key of that relation

17. Normalized Data: are together by a found in

18. A of this current example is that each member can . That is highly inefficient and it is not a good way to run your business.

19. Creating a allows you to store information in that table related to all of the different courses that your members are enrolling in. • Class Number from the Membership table and placed in the Enrollment table. • Neither Member Number nor Class Number is unique by itself in the Enrollment table. However, you can create a key that together, and that can be your unique identifier.

20. Step #4: Use a Data Definition Language to create the database You need a database management system (DBMS) to do this

21. Microsoft is a software (database management system) that a that follows the principles found in the Model

22. What Is a Database Management System An that provides all the necessary capabilities for b files, ex required for making decisions, and formatting the information into structured reports. DIFFERENTFROM ADATABASE

23. DATABASE a collection of information that you A database is actually composed of two parts: 1. The information itself / the files that are logically associated 2. The logical structure of the information which is called the data dictionary. The data dictionary contains the logical properties that describe information in a database.

24. DATABASE MANAGEMENT SYSTEM (DBMS) the software you use to specify the logical organization for a database and access it. A DBMS contains 5 software components:

25. Database Management System Engine accepts logical requests from the various other DBMS subsystems, converts them to their physical equivalent, and actually accesses the database and data dictionary as they exist on a storage device. You can work with the information

26. DATA DEFINITION SUBSYSTEM helps you create and maintain the data dictionary and define the structure of the files in a database. • A data dictionary serves the as an . • You use this subsystem to define the information logical when you first create a database. • Once you’ve created a database, you use this subsystem to fields, fields, or field properties.

27. What might be found in a data dictionary in Access? • F and their , as well as the • I are rules that help assure the quality of the information in a database. (Validation Rules in Access) • Setting a for a field • A birth date can be now or in the past: not in the future. • A registration database at your school includes integrity constraints concerning for certain classes. • Validation Text: seen in Access.

28. What might be found in a data dictionary in Access? • Input Masks and Field Sizes • Formats, Default Values, and Captions • With Referential Integrity, you create a feature that will • When two different tables are linked/joined by a relationship where referential integrity is enforced, you cannot type data into a linked field if that same data does not already exist in the original table. • You

30. DATA MANIPULATION SUBSYSTEM helps you add, change, and delete information in a database and mine it for valuable information. • This subsystem is most often the between you as a user and the information contained in a database. • Tools in this subsystem include views, report generators, query-by-example tools, and structured query language.

31. DATA MANIPULATION TOOLS • - allows you to see the content of a database file, make whatever changes you want, perform simple sorting, and search to find the location of specific information. • Add records. • Delete records • Sort records. • Search for records.

33. DATA MANIPULATION TOOLS • REPORT GENERATOR- helps you quickly define formats of reports and what information you want to see in a report. You can specify exactly what you want to see and where you want to see it. • Report Wizard • Report Design Screen

35. DATA MANIPULATION TOOLS • QUERY-BY-EXAMPLE (QBE) TOOL- helps you graphically design the answer to a question. You create this query • You specify that you want the database searched for, and the query tool will return records that match the conditions or criteria that you specified. • Queries are used to useful

36. Query Design screen (above) and Query Result (below)

37. DATA MANIPULATION TOOLS • STRUCTURED QUERY LANGUAGE (SQL)- a standardized fourth-generation language found in most database environments. SQL is the same as QBE, except that you perform a query by creating a statement instead of pointing, clicking, dragging. • SQL is a • Uses • SELECT • FROM • WHERE

38. APPLICATION GENERATION SUBSYSTEM • Tools for creating • Application buttons that are used to execute a certain task. • Uses a programming language specific to the database management system that you are using. contains facilities to help you develop transaction-intensive applications. This subsystem includes:

40. DATA ADMINISTRATION SUBSYSTEM helps you manage the overall database environment by providing facilities for: • B • Security management • W • W • Concurrency control ensures the validity of database updates. • Change management allows you to assess the impact of proposed structural changes.

41. One of the goals of a database management system is to provide easy , while at the same time . If you have multiple files, each containing the same information, then you will most likely find redundant elements and erroneous data.

42. Relational Database Model • Uses a series of two-dimensional • Connects or relates data in different files through the use of a • Based on mathematical principles which allow for more logical manipulation of data. • Most flexible type of organization.

43. THE CONCEPT OF KEYS • A KEY is a field or combination of fields used to identify records so they can be easily retrieved and processed. • A PRIMARY KEY is a field in a database file that. • I Social • You can create relationships between tables/files through /file.

44. Student and Department Student Entity Student Number Address . . Department ID Department Entity Department ID Address . . This way, if we wanted to know all the students that belong to a particular department, we could get the listing by joining the two entities on the Department ID value.

45. Driver’s license file/table Name Street address City State Zip Driver’s license no. Expiration date Car owner file/table Car License No. Model year Car make Street address City St. Zip Name Moving violation citation file/table Citation number Moving violation type Date cited Driver’s License No. Fines paid/ not paid Parking violation citation file/table Citation number Parking violation type Date cited Car License Number Fines paid/ not paid (MIS) Relational Database Model State DMV Database 1XYZ234 J.Doe A405261 J. Doe 1XYZ234 A405261

46. Creating between database files. • C /file. • You might have to physically draw the connection between two tables/files. This is usually achieved by drawing a connecting line between two fields.

47. INFORMATION STORED SEPARATELY BUT CAN BE RELATED THROUGH KEY JOINS

48. Another example: which movies are provided by which distributors?

49. Recent Database Developments • The Data Warehouse and Data Mining • Object-Oriented Database Management Systems

50. Organizational Databases • Organizational databases are constantly changing as the organizational systems are constantly updating and changing the contents of the organizational database to reflect current business activities. • Organizational databases support . Constantly being updated and changed. • Not very useful for decision making involving the analysis of • Organizational databases rarely maintain historical data, just information related to current operations.