MSCI 2130 Introduction to MIS Lecture Notes PDF

MSCI 2130: Introduction to MIS Lecture 2- Week 2 1  6 Wireless Tech and Cloud Computing Data & Knowledge Mgt Introduction to IS Hardware, Software, & Telecommunicat...

MSCI 2130: Introduction to MIS Lecture 2- Week 2 1  6 Wireless Tech and Cloud Computing Data & Knowledge Mgt Introduction to IS Hardware, Software, & Telecommunications 2  3 Introduction to Management Information Systems Chapter 5 Data and Knowledge Management ©2022 John Wiley & Sons, Inc. All rights reserved. How Important Data is How data transformed the NBA – YouTube 4 Difficulties of Managing Data Data increases exponentially with time Multiple sources of data (e.g., internal, personal, external) New sources of data (e.g., AI generated content) Data rot (e.g., customers move to new address, change their phone numbers, etc.) Data degradation: ▪ Refers to problems with the media on which the data are stored, such as temperature, humidity, and exposure to light can cause physical problems with storage media and thus make it difficult to access the data Data security, quality, and integrity 5 Difficulties of Managing Data (cont’d) We need a reliable place to store the data We want to be able to use the data at anytime We want to make sure it is safe and secure 6 What To Do? How about spreadsheets? Why do not businesses keep things simple and just store things in spreadsheets? 7 The Spreadsheet Approach 8  The Spreadsheet Approach (cont’d) Hooray! Your startup is doing pretty well! Hiring more employees To handle hundreds of orders! 9 The Spreadsheet Approach (cont’d) Huge numbers of orders coming in every day Multiple employees are trying to enter those orders into the spreadsheet you made in the first week Employees keep track of and modify the orders as customers call to ask questions. But that doesn't always happen because you now have over 1 million orders. Excel crashes every time it is loaded and used! So what? 10 The Spreadsheet Approach (cont’d) 11 The Spreadsheet Approach (cont’d) Break-up the spreadsheet into six separate spreadsheets so that it doesn't crash every time you try to enter an order. Benefits: It helped prevent Excel from crashing Concerns: 1. Each spreadsheet is still very large and it takes a while to open each one 2. Every time a customer calls to ask a question about an order or an invoice, It takes time before the employee can find the correct spreadsheet that has the order 3. The more annoying part is that multiple people are trying to use the spreadsheet at the same time. So when a customer calls, an employee has to wait until somebody else finishes before he/she can open the spreadsheet 12 The Spreadsheet Approach (cont’d) Let’s move the excel sheet online (i.e., to the cloud)! 13 The Spreadsheet Approach (cont’d) Benefits: multiple people edit the spreadsheet at the same time. Concerns: 1. The spreadsheets are still pretty big. So they still take a while to open and search 2. Sometimes multiple people end up interacting with a spreadsheet at the same time, and they end up writing over each other's entries 3. A lot of inconsistencies arise in the data which are hard to catch if you don't have multiple spreadsheets open at once 14 Example of some inconsistencies.. More Concerns: 1. If a customer calls again, will we type in all that information about a returning customer again? 2. Will we change multiple fields in the spreadsheet to correct a mistake? How can we make multiple updates? 3. How to make our data secure and safe? 15  Is not there a better approach? 16 The Database Approach Whether you know it or not, you’re using a database every day 17 What is a Database? A database is a shared and organized collection of interrelated data, designed to meet the needs of multiple types of end-users An efficient, reliable, and safe, multi-user storage that gives access to massive amounts of persistent data Stored in a standardized, convenient form 18 Database Table Example.. 19  Why Databases? Minimize: Data redundancy: the same data are no longer stored in multiple locations. Data inconsistency: Single version of the truth. 20  Why Databases? (cont’d) Maximize: Data security: Having high-security measures in place that ban malicious access to it. Data integrity: Data meet certain constraints (e.g., ensuring no alphabetic characters in a Social Security Number field) Data independence: Applications and data are independent of one another; that is, applications and data are not linked to each other, so all applications are able to access the same data. 21 How to Design and Build Databases? 22 Database Table Example.. Physical Design of a Database 23 Database Management Systems (DBMS) Database Management System (DBMS): a set of programs that provides users with tools to create and manage the physical design of a database. 24 Database Management Systems (DBMS) Example of DBMSs: SQL Server, MySQL, PostgresSQL…etc. Such DBMSs rely on a fundamental concept called “relational databases” 25  Relational Databases Relational databases break up data sets into individual pieces or subsets of data Such subsets of data is in the form of tables (relations) representing entities with primary/foreign keys representing relationships 26 Relational Databases (cont’d) 27 Relational Databases (cont’d) A relational data model organizes data as a set of relations, or a set of two-dimensional tables. In other words, instead of having one big table with all the columns to be used, we break it down into multiple tables (relations) each holding the columns that represent the data within that table (relation). Relation: -is a two-dimensional table, with the following properties: Each column has a distinct name (attribute name) The order of columns is immaterial Each column contains values about the same attribute Each row is unique/distinct, duplicate rows are not allowed The order of rows is immaterial Primary keys are not null But… Is it as easy as just choosing the right DBMS and go ahead with building our tables (relations)? Database Development Process 30 Need for Entity-Relationship Diagrams Figuring out exactly what information you want to collect and exactly how you can organize information in tables and link those tables, can be tricky Database architects draw conceptual diagrams called entity- relationship diagrams (ERDs) to communicate the structure of the database they intend to implement to the rest of their team before they make the physical design of the database This step is called the “Conceptual Design” 31 Conceptual Design Entity-Relationship model—a popular high-level conceptual data model used to create an abstract database structure that represents real-world objects Minimal data rule: All what is needed is there, and all what is there is needed Even if you never designed a database in your life, being able to read these diagrams can be very helpful for writing tricky queries 32 ERD Example.. Course Title Description Classes Courses Credits Course_ID Teacher_I Class Course_ID D Name 33 Entity Relationship Diagram Elements Entity A thing, event, or person in the organization's environment about which someone wants to collect data (e.g., Student) Attribute Characteristic or property of an entity (e.g., Student ID) Relationship Association between entities Composite entity (Associative entity) Represents both an entity and relationship Has its own properties 34 Converting ERDs into Tables (Relations) 36 Entity Relationship Data Model Entity → Table Each entity drawn in an ERD is translated into a table in the database A table in ERD is referred to as “relation” “Relation” in databases is very different from “relationship” So a “Student” entity will turn into a table named “Student” Name GPA Student Stud_ID 37 Relational Data Model Attribute → Column Each attribute in the ERD becomes a column in the table Each entity (or table) in an ERD must have a unique attribute that identifies it Unique attribute of an entity becomes its primary key (i.e., the column that allows you to link tables together) Primary keys are represented in an ERD by underlining the attribute Name GPA Student Stud_ID 38 Converting ERD into Tables Student Age Name GPA SSN Home_Phone Office_Phone Address 39 Keys (Identifiers) Keys: one or more columns that can be used to identify a row: Primary key (unique ID) a unique attribute used by DBMS to uniquely identify a row (e.g., Student ID, SSN) This is how we can guarantee that all rows are unique Primary keys are represented in an ERD by underlining its attribute Foreign key (cross reference key) a non-key attribute in one relation that also appears as a primary key in another relation (e.g. department number is a key in department table but a foreign key in employee table) Concatenated key (Composite key) - use two or more attributes to identify a record (e.g.. Student ID & Course ID to identify a Grade record)  Keys (Identifiers) cont’d Key Characteristics: Keys can be simple (a single field) or composite (more than one field) Will not change in value Will not be null Keys usually are used as indexes to speed up the response to user queries 42 Exercise..1 Is this a relation?  Special Types of Attributes Multivalued: Instead of having a single-value attribute, we may have a multivalued attribute Multivalued attributes can hold multi values for a single entity occurrence Example: Employee skills (e.g., computer skills, language skills…etc), or student mobile number, or email 44  Special Types of Attributes Derived: A derived attribute which is not actually available in the database but can only be derived from the available attributes Example: “Employee_Age” can be derived from his/her birth date and the current date, or “Years_Employed” can be derived from date employed and the current date. Represented as a dashed line Age 45  Order Processing ERD: Customer Name Description Unit price S Name S ID Address Customer Product Products Salesman Entity Number Relationship Associative Prepared Placed Entity Attribute Orders OrderLine S ID Product Order Order Number Customer Date Number Number Name Quantity Primary Key Composite Key NOTE: no cardinality relationships are specified yet in this diagram46  Relational Schema for the Four Entities Primary Key Foreign Key (implements 1:N relationship between customer and order) Composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product) 47 AI Today! Did you know! Nvidia just announced several partnerships with major healthcare institutions to accelerate medical innovation through AI, with applications spanning genomics, drug discovery, and clinical research 48 49