IS605/606: Information Systems Instructor: Dr. Boris Jukic

1. IS605/606: Information SystemsInstructor: Dr. Boris Jukic Technology Focus: Database 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. 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

7. Relational Schema

8. Reduced Data Redundancy Products Customers

9. 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.

10. 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

11. 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.

12. Entities and Attributes • Entities and Attributes • An entity (or entity instance) is a person, place, event, or thing for which we intend to collect data. • University -- Students, Faculty Members, Courses • Airlines -- Pilots, Aircraft, Routes, Suppliers • Each entity has certain characteristics known as attributes. • Student -- Student Number, Name, GPA, Date of Enrollment, Date of Birth, Home Address, Phone Number, Major • Aircraft -- Aircraft Number, Date of Last Maintenance, Total Hours Flown, Hours Flown since Last Maintenance

13. E-R Diagrams • E-R Diagram (ERD) elements: • Rectangles are used to represent entities. • Diamonds are used to represent the relationship(s) between the entities. • The number 1 is used to represent the “1” side of the relationship. • The letter M is used to represent the “many” sides of the relationship.

14. 1 to Many Relationship

16. 1 to Many Relationship: another example

17. Many to Many Relationship

19. E-R Diagram: An example