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

1
IS605/606 Information Systems Instructor 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
Product Id Product Name Product Description Product Price
001 PlainSounder AM/FM Radio, 2 speakers 51.00
002 BetterBox Radio, CD, 6 Speakers 123.00
003 UltraX CD, MP3, 8 Speakers 270.00
Customer ID Customer Name Customer Location Product ID
C1 Chrysler Germany 001
C2 Circuit City California 001
H1 Hyundai Korea 001
G1 GM Michigan 002
F1 Ford Motors Michigan 002
H2 Honda Japan 003
B1 BMW Germany 003
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
15
(No Transcript)
16
1 to Many Relationship another example
17
Many to Many Relationship
18
(No Transcript)
19
E-R Diagram An example
20
(No Transcript)