Transcript lec 13 IT
INFORMATION AND DATABASES Part 1 LEARNING OBJECTIVES • The importance of high-quality information and issues involved in managing business data. • Advantages of the Database approach. • Data Modeling and Database Design with Entity Relationship Diagram (ERD) • How organizations can maximize their strategic potential with databases : Data Warehouse, Data Marts, Data Mining, Business Intelligence Levels, Formats & Granularities of Information Information Type: Transactional And Analytical Information Quality • Business decisions are only as good as the quality of the information used to make the decisions • Characteristics of High-quality Information • Accurate • Complete • Consistent • Unique • Timely Information Quality Information Quality Low Quality Information Example The Costs Of Using Low-quality Information • The four primary sources of low quality information include 1. Customers intentionally enter inaccurate information to protect their privacy 2. Different entry standards and formats 3. Operators enter abbreviated or erroneous information by accident or to save time 4. Third party and external information contains inconsistencies, inaccuracies, and errors The Costs Of Using Low-quality Information . . . • Potential business effects resulting from low quality information include • Inability to accurately track customers • Difficulty identifying valuable customers • Inability to identify selling opportunities • Marketing to nonexistent customers • Difficulty tracking revenue • Inability to build strong customer relationships The Benefits Of Good Information • High quality information can significantly improve the chances of making a good decision • Good decisions can directly impact an organization's bottom line Information Timeliness • Real-time information – Immediate, up-to-date information • Real-time system – Provides real-time information in response to requests Difficulties in Managing Business Data • Amount of data increases exponentially. • Data are scattered and collected by many individuals using various methods and devices. • Data come from many sources. • Data security, quality and integrity are critical. • An ever-increasing amount of data needs to be considered in making organizational decisions. Data Life Cycle ( in modern businesses ) Data Management • File Processing Systems • Stand-alone applications with their own data files. • Data are NOT shared across applications • Redundancy , Inaccurate • DBMS – Database Management Systems • Use a DBMS software to create, store, organize, and retrieve data from a single database or several databases • Examples: Microsoft Access, SQL Server, Oracle Conventional Files vs. the Database File – a collection of similar records. • Files are unrelated to each other except in the code of an application program. • Data storage is built around the applications that use the files. Database – a collection of interrelated files • Records in one file (or table) are physically related to records in another file (or table). • Applications are built around the integrated database Difficulties of Non-Relational Data Files • Update Anomaly: not changing all occurrence of a data item (in many places) • Insert Anomaly: add an invalid (null record) to the database • Delete Anomaly: not remove all info (in many places) about a deleted record A File Processing System Database Management System Application1 Application2 Application3 DBMS DB Files vs. Database Database Management System (DBMS) Field Individual characteristics/attributes about an ENTITY. (Also called columns) Record A group of fields or attributes to describe a single instance/member of an ENTITY. (Also called rows) File A collection of records or instances for a given ENTITY. (Also called tables) A collection of files or entities containing Database information to support a given system or a particular topic area Main Database Activities/Functions • Recording Data (Input) • Entry Form • Processing Data (Query) • Structured Query Language (SQL) • Query by example (QBE) • Reporting Data (Output) • Report – a compilation of data that is organized and produced in printed format • Report Generators The Database Approach • DBMS provides all users with access to all the data. • DBMS minimize the following problems: • Data redundancy • Data isolation • Data inconsistency • DBMS maximizes the following issues: • Data security • Data integrity • Data independence Using A Relational Database For Business Advantages • Database advantages from a business perspective include • Increased flexibility • Increased scalability and performance • Reduced information redundancy • Increased information integrity (quality) • Increased information security Increased Flexibility • A well-designed database should • Handle changes quickly and easily • Provide users with different views • Have only one physical view • Physical view – Deals with the physical storage of information on a storage device • Have multiple logical views • Logical view – Focuses on how individual users logically access information to meet their own particular business needs Increased Scalability & Performance • A database must scale to meet increased demand, while maintaining acceptable performance levels • Scalability – Refers to how well a system can adapt to increased demands • Performance – Measures how quickly a system performs a certain process or transaction Database Approach • Advantages of the Database Approach • Program-data independence • Minimal data redundancy • Improved data consistency • Improved data sharing • Increased productivity of application development • Enforcement of standards • Improved data quality • Improved data accessibility • Reduced program maintenance Database Approach . . . • Costs/Risks of the Database Approach • New, specialized personnel • Installation/management cost and complexity • Conversion cost: what to do with legacy systems (old data) • Need for explicit backup and recovery • Data ownership: organizational conflict Data Hierarchy Database Field / Column File / Table Record / Row Student ID 2144 Last Name Arnold First Name Betty 3122 Taylor John 3843 Simmons Lisa 9844 Macy Bill 2837 Leath Heather 2293 Wrench Tim Database Management Systems Database Components Form Builder Report Writer Interactive Query Tool Application Program Database Front-end Database Engine To other computer systems Database Database Gateway To other DBMS brands Data Modeling • ERD (Entity Relationship Diagram) Blue print of Relational Database • Entity (object / info of interest) • Attributes (characteristics / fields) • Relationship (business rules) Entities • Entity is a group of attributes corresponding to the same conceptual thing about which we need to capture and store data (in a file/table) • Entity is a set of instances / members of the object that it represents (records) • Entity must have a unique name, unique identifier, and at least one attribute (the identifier itself is sufficient) Entities: Examples • • • • • Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier. Places: sales region, building, room, branch office, campus. Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle. Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip. Concepts: account, block of time, bond, course, fund, qualification, stock. Entity Instance: Example Entity instance – a single member of an entity. Attributes Entity Student ID Last Name First Name Instances 2144 Arnold Betty 3122 Taylor John 3843 Simmons Lisa 9844 Macy Bill 2837 Lea Heather 2293 Wrench Tim Attributes • An attribute is a descriptive property or characteristic of interest of an entity. Also called field. • The data type for an attribute defines what type of data can be stored in that attribute. • The domain of an attribute defines what values an attribute can legitimately take on. • The default value for an attribute is the value that will be recorded if not specified by the user. Entities & Attributes Entities & Attributes . . . ENTITY NAME CUSTOMER - entity id - attribute 1 - attribute 2 - ………….. - attribute n - Customer_ID - Cust_Name - Cust_Address - Cust_Phone Attributes • Simple vs. composite • Single-valued vs. multi-valued • Stored vs. derived • Null-valued Simple Vs. Composite • Composite attributes can be divided into smaller subparts, which represent more basic attributes that have their own meanings • Address can be broken down into a number of subparts, such as Street, City, State, Zip Code • Street may be further broken down by Number, Street Name, and Apartment/Unit Number • Attributes that are not divisible into subparts are called simple attributes Single-valued Vs. Multi-valued • Single-valued attribute means having only a single value of each attribute of an entity at any given time • A CUSTOMER entity allows only one Telephone Number for each CUSTOMER • If a CUSTOMER has more than one Phone Number and wants them all included in the database, then CUSTOMER entity cannot handle them Multi-valued • Multi-valued attribute means having the potential to contain more than one value for an attribute at any given time • An entity in a relational database cannot have multivalued attributes, must create another entity to hold them • Relational databases do not allow multi-valued attributes because they can cause problems: • Confuses the meaning of data in the database • Significantly slow down searching • Place unnecessary restrictions on the amount of data that can be stored Stored Vs. Derived • If an attribute can be calculated using the value of another attribute, it is called a derived attribute • The attribute that is used to derive the attribute is called a stored attribute • Derived attributes are not stored in the file, but can be derived when needed from the stored attributes Null-valued • Null-valued attribute – Assigned to an attribute when no other value applies or when a value is unknown THANKYOU (PART 2 IN NEXT LECTURE)