1. IS605/606: Information SystemsInstructor: Dr. Boris Jukic Relational Database Systems in Enterprise Information Systems

2. File systems • File systems rely on coded file management programs to access, insert and modify their content • As such, file systems are plagued by two main problems • Structural dependence refers to the fact that if a file structure is changed (such as deletion or addition of a field), the related file management programs have to be modified accordingly • Data dependence refers to the fact that the changes in data characteristics, such as changing a field from integer to decimal (or even just changing the length of the field), will cause the related file management programs to be changed • Finally, there is a problem of data redundancy

3. Data Redundancy • When the same data is stored in more than one location (in multiple files or multiple fields within one file) It may lead to: • Data integrity (inconsistency) problems • may be caused by either data entry errors or failure to update all multiple copies of the same data • Data anomalies: modification, insertion and deletion

4. Data Redundancy in File Systems and Resulting Anomalies • Modification anomaly: if PlainSounder model description changes • Insertion Anomaly: if a new customer (Toyota for example) is added to the list of those who buy BetterBox product

5. Database Systems • Database Systems achieve data independence and structural independence • If data type of as filed is changed or a field is eliminated or a new one added, the existing management programs (queries) do NOT have to be modified • If properly designed, databases have a low level of redundancy, eliminating most of the insertion, deletion and modification anomalies • Logically related data instead of physically separated and unrelated files

6. Four (Logical) Data Models • Hierarchical Model (Legacy) • Standard tree-like structure • Network Model (Legacy) • More than one parent allowed • Relational Model • First truly data and structurally independent model • No predetermined navigational maps as in two older models • The Database technology of choice • Object Model • Tables become objects

7. RELATIONAL DATABASE ADVANTAGES • Database advantages from a business perspective include • Increased flexibility • Increased scalability and performance • Reduced information redundancy • Increased information integrity (quality) • Increased information security

8. Relational Database Management System • In RMDBS, all data appears to be stored in a collection of tables (or relations), which are independent of one another, but can be linked through common entries in one of the tables' columns or fields (controlled redundancy) • Relational Schema: The graph depicting relationship types between tables

9. Relational Schema

10. Reduced Data Redundancy Products Customers

11. Tables in RDBMS • Tables: Logical constructs containing individual entity sets. • Tables are always two-dimensional: rows and columns • each row represents a single entity (or entity instance) from the entity set • each (uniquely named) column represents one attribute • each row-column intersection results in a single data value • Each table must have a primary key : An attribute uniquely identifying each row (entity), satisfying the entity integrity conditions. Null value (no entry) is not permitted for a primary key. • The order of rows and columns within the table is irrelevant • Foreign Key is an attribute in one table whose values must either match the value of a primary key in another table or be set to null (no value). These conditions are known as referential integrity constraint.

12. Relationships within the relational database: • One-to-one relationships: • One-to many relationships: • Examples: • professor - class • department - employee • Many to-many relationships • Examples: parts – product, student – class, … • it is recommended to break it into a set (usually two) of one-to-may relationships through a so called composite (bridge) entity

13. Enterprise data planning • A large component of the business informational needs can be captured by the mapping of all entities the organizations need to keep track of and the relationships among them • E-R (Entity-Relationship) modeling is a standard technique that provides a simplified picture of the relationship among entities.

14. Keys and Relationships: transaction processing system example

15. RELATIONAL DATABASE ADVANTAGES • Database advantages from a business perspective include • Increased flexibility • Increased scalability and performance • Reduced information redundancy • Increased information integrity (quality) • Increased information security

16. Increased Flexibility • A well-designed database should: • Handle changes quickly and easily • Provide users with different views • A database has only one physical view • Physical view – deals with the physical storage of information on a storage device such as a hard disk • A database can have multiple logical views • Logical view – focuses on how users logically access information to meet particular business needs

17. Increased Scalability and 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

18. Reduced Information Redundancy • Databases reduce information redundancy • Redundancy – the duplication of information or storing the same information in multiple places • Inconsistency is one of the primary problems with redundant information

19. Increase Information Integrity (Quality) • Information integrity – a measure of the quality of information • Integrity constraints – rules that help ensure the quality of information • Operational integrity constraints – rules that enforce basic and fundamental information-based constraints • Business-critical integrity constraints – rules that enforce business rules vital to an organization’s success and often require more insight and knowledge than operational integrity constraints

20. Increased Information Security • Information is an organizational asset and must be protected • Databases offer several security features including: • Passwords – provide authentication of the user • Access levels – determine who has access to the different types of information • Access controls – Determine types of user access, such as read-only access

21. DATABASE MANAGEMENT SYSTEMS • Database management systems (DBMS) – software through which users and application programs interact with a database

22. INTEGRATING INFORMATION AMONG MULTIPLE DATABASES • Organizations typically maintain multiple systems, each with its own database • Integration – allows separate systems to communicate directly with each other

23. INTEGRATING INFORMATION AMONG MULTIPLE DATABASES • Forward integration – takes information entered into a given system and sends it automatically to all downstream systems and processes

24. INTEGRATING INFORMATION AMONG MULTIPLE DATABASES • Backward integration – takes information entered into a given system and sends it automatically to all upstream systems and processes

25. INTEGRATING INFORMATION AMONG MULTIPLE DATABASES • Building a central repository specifically for integrated information