MIS 301 Information Systems in Organizations

MIS 301Information Systems in Organizations Dave Salisbury salisbury@udayton.edu (email) http://www.davesalisbury.com/ (web site)

Things to know about Database • Translating a business situation into a logical data model • Translating a logical data model into a relational database • Using access QBE to query relational databases (in Access) • Querying multi-table databases (in Access)

Databases & Data Modeling • Data as a resource • Basic logical modeling of data • Basic physical modeling of data • Data Integrity • Normalization

Student ROI (Return on Investment) • Your investment of time and effort in this course will result in your being able to answer these questions: • How do knowledge workers use Access QBE to query a single-table database? • How is Access QBE used to query multiple-table databases? • How do knowledge workers use logical modeling to create effective relational database systems? • How do you as a user participate in helping to build a database for your organization?

The Basics(this’ll come up again in technology) • Binary Digits (Bit) • 0 or 1 • Byte • 8 bits (usually), combined to mean something • Coding Schemes • ASCII – American Standard Code for Information Interchange • EBCDIC – Extended Binary Coded Decimal Interchange Code

The Data Hierarchy • The data hierarchy is a way of organizing stored data in progressively larger levels of complexity • Bit • Character • Field • Record • File • Database

File Processing Systems • Before the development of database systems, most organizations used file processing systems. • With file processing, each application uses it own set of files. • Even though files may be related by an application, they are not necessarily stored or managed together.

Traditional file processing problems • Program-Data Dependence • All programs maintain metadata for each file they use • Data Redundancy (Duplication of data) • Different systems/programs have separate copies of the same data • Limited Data Sharing • No centralized control of data • Excessive Program Maintenance • As much as 80% of of information systems budget

Duplicate (Redundant) Data

Data redundancy is bad because… • Waste of space to have duplicate data • Causes more maintenance headaches • The biggest Problem: • When data changes in one file, could cause inconsistencies • Compromises data integrity • Lack of coordination and central control • Non-standard file formats

Database • Central repository of shared data • Data is managed by a controlling agent • Stored in a standardized, convenient form • Requires a database management system (DBMS)

Advantages of Database Approach • Program-Data Independence • Metadata stored in DBMS, so applications don’t need to worry about data formats • Minimal Data Redundancy • Leads to increased data integrity/consistency • Improved Data Sharing • Different users get different views of the data • Enforcement of Standards • All data access is done in the same way • Improved Data Quality • Constraints, data validation rules

Relational Data Model • The relational data model was created by E. F. Codd in the early 1970’s. • It is a method of logically storing data in a database that is independent of the method used to physically store the data. • Relational database management systems (RDBMS) are the most popular way of storing large amounts of data.

The Relational Data Model • The relational data model stores data in one or more tables corresponding to entities. • Tables consist of records which correspond to rows in the tables. • Records store data on a single instance of an entity. • A record consists of one or more fields. • Fields are often referred to as attributes and correspond to columns of the tables.

Related Tables • Each table has a unique field called the primary key. • Tables are related via matching fields. • If the primary key in one table is the reference key to another table, it is referred to as a foreign key.

Using a Database • Designing a database and storing data in it is just the first step. • The power of a database comes from the capability to work with the data stored there. • The primary method of using a database is to query it. • The standard way of doing this for a RDBMS is to use the Structured Query Language (SQL), or for Access, a Query Language known as Query by Example (QBE).

Modeling Reality • A database must mirror the real world if it is to answer questions about the real world • Hence, we must first model reality using data modeling STUDENT Social_Security_No Name Major

Building a logical data model • Initial step to building a relational database – don’t focus on technology at this point • Identify the things an organization needs to track and how they are related • Focus on the “big picture” – things one cares about and how they are related

Entity-Relationship Modeling • One type of data modeling • Entities – things you want to keep track of • Relationships – how those things are related to one another

Notation in an ER diagram RELATIONSHIP ENTITY Something about whichyou want to keep data. How things you want to keep data about are tied together. How to depict how many of one thing is related to how many of another. = zero or = one or = many or or

Entity-Relationship Modeling • Relationships imply constraints on how many entities may occur on one side (or the other) of a given relationship. • Types of Relationships • one-to-one 1:1 • one-to-many 1:N • many-to-many N:M

Sample E-R Model Places/Placed by Contains/ isContained in Order Customer Product

The goal is a well-designed relational database • Tables • Each row is unique (entity instance – one example of a given entity) • Order is unimportant (can be sorted using queries later) • Each column represents one thing about the entity instance - attribute • Entries are from the same domain (e.g. student)

Well-designed relational databases • Multiple tables • One for each class of things one cares about • Avoid redundancy • Each class of things gets its own table • Only information about the relevant class goes in a table • Design to limit redundancy • Redundancy leads to waste of storage space • Redundancy causes modification anomalies on inserts, deletes or updates

Data Integrity • Rules to make sure your data is valid • Entity integrity constraint • Primary key cannot be null • Referential integrity • Ensures valid relationships between data • Cannot add a row with no parent • Cannot delete a parent without deleting child (cascading)

Relational database keys • Primary key (uniquely identifies a record) • Composite key, a.k.a. Concatenated key (two elements combined are unique) • Foreign key (links tables/relations)

Many-to-many relationships • Can’t exist in a relational database • They do, however, exist in real life • How to translate them into something that CAN be modeled in a relational database? • Make an intersection table between the two tables in a many-to-many relationship • Use one element of two tables to tie them together put in the intersection table • The resulting table will either have a defined key, or will use a combination of the keys of the two tables being tied together

M:N Relationships in Relational Model Places/Placed by Contains/ isContained in Order Order Line Customer Contains/ isContained in Product

How this looks in Access CUSTOMER 1 PRODUCT 1 CustomerIDFirstNameLastNameAddressCityStateZip Telephone ProductIDDescriptionPriceWeightSupplier ORDER 1 OrderIDOrderDateCustomerID ORDERED_PRODUCT 8 OrderIDProductIDQuantity 8 8

MIS 301 Information Systems in Organizations