1 / 56

Introduction to Management Information Systems Chapter 4 Database Processing

Introduction to Management Information Systems Chapter 4 Database Processing. HTM 304 Fall 07. Learning Objectives. Know purpose of database processing List components of a database system Understand important database terms Know elements of the entity-relationship model

deandre
Download Presentation

Introduction to Management Information Systems Chapter 4 Database Processing

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. Introduction to Management Information SystemsChapter 4 Database Processing HTM 304 Fall 07

  2. Learning Objectives • Know purpose of database processing • List components of a database system • Understand important database terms • Know elements of the entity-relationship model • Understand general nature of database design

  3. Purpose of a Database Keep track of things with more than one theme

  4. One Theme Spread Sheet

  5. Multi-theme Database All three records for one student (student is the link of different themes) Student Score Email records office visits

  6. Database Hierarchy • Bytes:the basic elements which compose of database • Fields (Columns). Name, number or characters that describe an aspect of a business object or activity. • e.g. Student Numbers, Student Names, Midterm Scores, etc. • Record (Rows). Collection of related data fields. • e.g. 000054323 Jenny White 89. • File (or Table).Collection of related records. • e.g. Student Score Table, Student Office Visit Table, Student Email Record Table • Database. Collection of integrated and related files + Relations between (among) tables + structure of data. • The relations and structures are called metadata

  7. Database Components of Database Tables or Files + Relationships among Rows in Table + Metadata = How to effectively organize and process raw data is the key to a successful database design and an Information System design.

  8. Student Table Records/Rows Columns/Fields

  9. Poorly Designed Table Common Practice in Excel In Database Design, the record will be split into 2 records: Jones Feb 1, 2002 Jones@ourcompany.com 100 Finance Jones Feb 1, 2002 Jones@ourcompany.com 100 Accounting Pros and cons?

  10. Relationships • The records in different tables are connected by certain relationships – relational databases • Key – column or group of columns that identifies a unique row in a table • For example: Student Number is key of Student table • The key insures no repeated records • Every table MUST have a key • Foreign key –non-key column or field in one table that links to a primary key in another table • Student Number in Email and Office_Visit tables • Foreign keys link records from different tables together as one compound record

  11. Examples of Relationships Foreign Key Key Foreign Key

  12. Database Review Components Tables or Files + Relationships among Rows + Metadata =

  13. Metadata • Metadata are data that describe data (Format, Field Properties, etc.) • Makes databases much more useful • No one needs to guess, remember, or record what is in database • Make databases easy to use for authorized and unauthorized purposes

  14. Exercise: Use Access to setup a data table • Create a database file (*.mdb) • Create table in design view • All tables are in one database file • First create the table in design view • Specify the fields (column headers) • Specify the types of the fields, and other properties • Specify which field is the key! • Finish design view -- Close the design view • Fill in records in the table • Double click the table to enter editing view • Enter records • Try enter repeated keys, it won’t let you! Why?

  15. DB Application Systems Components • Pure database data correct, but raw form not pertinent or useful • DB applications make DB data accessible and useful • Users employ DB applications • Forms • Formatted reports • Queries (SQL language) • Application programs • Each calls on DBMS to process DB tables

  16. DB Application System Components DBMS (Database Management System) A program used to create, process, and administer a database. Database developers use the DBMS to create tables, relationships, and other structures in the database.

  17. Enterprise DBMS • Process large organizational and workgroup databases • Support many, perhaps thousands, of users and many different database applications • Support 24/7 operations and can manage dozens of different magnetic disks with hundreds of gigabytes or more data • IBM’s DB2, Microsoft’s SQL Server, and Oracle are examples of enterprise DBMS products

  18. Personal DBMS • Designed for smaller, simpler database applications. • Used for personal or small workgroup applications that involve <100 users, normally fewer than 15 • Bulk of personal databases have only a single user • Microsoft Access only available personal DBMS

  19. Language used in DBMS • SQL: Structured Query Language • An international standard language for processing a database: Data Manipulation/Definition and Data Queries • Data Manipulation • Data Queries: See later. INSERT INTO StudentTable ([Student Number], [Student Name], Hw1, Hw2, MidTerm) VALUES (1000, ‘Joe Black’, 90, 85, 88) UPDATE StudentTable SET MidTerm = ’90’ WHERE Student Number = ‘1000’ DELETE FROM StudentTable WHERE MidTerm = NULL

  20. Exercise: • Below shows the original table. • Write down the resulting table after executing the following SQL command: UPDATE StudentTable SET LetterScore = ’A’ WHERE Score >= ‘90’. Original Table: StudentTable Exercise: write down all the other letter scores based on certain criterion.

  21. DB Application System Components D B M S Database Applications: collection of forms, reports, queries, and application programs that process a database. A database may have one or more applications, and each app. may have one ore more users…(Page 84) DBMS (Database Management System) A program used to create, process, and administer a database. Database developers use the DBMS to create tables, relationships, and other structures in the database.

  22. Multiple Database Applications • Think how the school uses student information?

  23. Form – A structured way to edit and view data

  24. Example Student Report Read Only. No Editing

  25. Query • Use SQL to search through the database and produce certain results. SELECT (certain attributes) FROM (certain database tables) WHERE (conditions) SELECT STUDENT Name, MIDTERM FROM STUDENTTABLE WHERE MIDTERM>90

  26. Exercise: • Write down the results when using the following query command on the following original data table: SELECT MIDTERM FROM STUDENTTABLE WHERE MIDTERM>90 SELECT Student Name FROM STUDENTTABLE WHERE MIDTERM<70 SELECT Student Name, Student Number, HW1, HW2, MidTerm FROM STUDENTTABLE WHERE MidTerm > (HW1+HW2)/2

  27. DB Application System Components

  28. Database Development Process E-R Modeling: High level blueprint defining how data are stored structurally in the system Define users’ need through interview

  29. E-R Modeling • E-R Model: a detailed, logical representation of the data for a business. To document rules and policies of an organization that govern data.

  30. Components in E-R Model • Entity – something users want to track • Examples Order, Customer, Salesperson, Item • Some entities represent physical object, such as an Item or Salesperson • Other entities represent a logical construct of transaction, such as Order or Contact • Entities have attributes that describe characteristics of the entity • Example attributes of Salesperson are SalespersonName, Email, Phone, etc.

  31. Entities (cont) • Entities have identifier, an attribute (or group of attributes) whose value is associated with 1 & only 1 entity instance • OrderNumber is the identifier of Order,because only 1 Order instance has given value of OrderNumber • CustomerNumber identifier of Customer • If each member of sales staff has unique name, then SalespersonName is an identifier of Salesperson

  32. Student Data Model Entities

  33. Relationships • Connects identities, allow cross entity search • A line is used to represent a relationship between two entities

  34. Parking permit Course Course Student instructor Student Student ID Name DoB Address … Instructor ID Name Department Office … Student ID Name DoB Address … Parking Permit # Date of Purchase Type of Permit Expiration Date … CRN Course Title Classroom Credit … CRN Course Title Classroom Credit … Three basic types of relationships • One-to-one: • One student can buy only one permit • One permit can only purchased by one student • One-to-Many: • One instructor can teach many courses • A course can only be taught by one instructor • Crow’s foot = Many • Many-to-Many: • One student can take more than one course • A course can be registered by multiple students

  35. Exercise Who defines the relationships?

  36. Exercise: • Exercise 22 a) (Page 105)

  37. Opposing Forces Guide: No, Thanks, I’ll Use a Spreadsheet • I’m not buying all this stuff about databases. • “I’ve tried them and they’re a pain-way too complicated to set up, and most of the time, a spreadsheet works just as well.” • “No, unless you are a General Motors or Toyota, I wouldn’t mess with a database.” • “You have to have professional IS people to create it and keep it running.” • “Besides, I don’t really want to share my data with anyone.” • “I work pretty hard to develop my client list.” • “Why would I want to give it away?” • “When I want something, I use Excel’s Data Filter.” • “I can usually get what I need.” • “Of course, I can’t still send form letters, but it really doesn’t matter.” • “I get most of my sales using the phone, anyway.”

  38. Database Security • The firewall, a computing device located between a firm’s internal network and external networks, prevents unauthorized access to the internal network. • For the best security, the DBMS computer should be protected by a firewall, and then all other security measures should be designed as if the firewall has been breached.

  39. Database Security (cont) • All operating systems and DBMS patches should be installed as soon as they become available. • No one other than authorized operations personnel should be able to directly access the computer that runs the DBMS. • The computer running the DBMS should be secured behind locked doors, and visits to that room should be recorded in a log. • Most DBMS products log failed attempts to sign on and produce other usage reports as well. • The database administrator (DBA) should periodically monitor such logs and reports for suspicious activity.

  40. Database Security (cont) • All major DBMS products have extensive, built-in security features. • These features allow for the definition of user accounts and user roles. • Each user account belongs to a specific person. • A role is a generic employee function, such as payroll clerk or field salesperson. • Once an account is defined, it can be assigned specific permissions, and it can also be assigned particular roles. • It is important to have a plan of action for security emergencies. • Disaster Recovery Plan (85%) • Business Continuity Plan (<15%) • The steps to be taken vary from database to database.

  41. Ethic Guide: Nobody Said I Shouldn’t • Kelly make a backup copy of his company’s database on CD and took it home and installed it on his home computer. • While performing queries on the database, he found customer order information and focused in on his friend Jason’s customer order entries. • Kelly noticed that Jason gave huge discounts to Valley Appliances but not to his other customers. • At an after work gathering, Kelly asked Jason about the Valley Appliances discount telling him what he had been doing. • When Kelly returned to work, he was fired

  42. Class 2: E-R Modeling and Database Lab • E-R Data Model • The most popular technique for creating a data model. • E: entity (things) • R: relationship (among the entities) • + business rules restricting the flow of the data • Data Model: • Read Problem Solving Guide: (p89a) Immanuel Kant, Data Modeler • Attempts to model the “real world” • Simply a model of what the data modeler perceives

  43. 5 minutes free write Social Implications of DB: • Relate any of Kant’s ideas to what you learned from reading “For Some, Online Persona Undermines Resume” • PRINT your name on Free Write and hand it in

  44. More exercise • Book 23 a) Create a E-R Diagram to relate all the information in the form

  45. Translating E-R diagram into Tables (1:N) Key Step 1: Create one table for each entity Key

  46. Translating E-R diagram into Tables (1:N) • Step 2: Add Foreign Key Column to the “many entity” to represent relationship

  47. Step 3: edit relationship • Go to Tools  Relationships • Add two entities • Drag the “advisor name” of advisor table to the “advisor name” of student table. • Close relationships

  48. Many-to-Many Relationship • Now the rule changed • One advisor can supervise many students • One student can have many adviser • One student must have at least one advisor • One advisor can have no student

  49. Foreign Key doesn’t work for N:M relationship

  50. Solution

More Related