1 / 21

MIS 301 Information Systems in Organizations

MIS 301 Information Systems in Organizations. Dave Salisbury salisbury@udayton.edu (email) http://www.davesalisbury.com/ (web site). Things to know about Database. Using Access QBE to Query Relational Databases (done) Querying Multi-table Databases (done)

erasmus-copeland
Download Presentation

MIS 301 Information Systems in Organizations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Things to know about Database • Using Access QBE to Query Relational Databases (done) • Querying Multi-table Databases (done) • Using Logical Modeling to Create a Relational Database • Translating a business situation into a data model, and then into a relational database

  3. Databases & Data Modeling • Data as a resource • Samples of how databases are set up in organizations • Basic logical modeling of data • Basic physical modeling of data • Data Integrity • Normalization

  4. 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?

  5. Disadvantages of File Processing • 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 • 80% of of information systems budget

  6. Duplicate (Redundant) Data

  7. Problems with Data Redundancy • 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

  8. 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)

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

  10. Modeling Reality • A database must mirror the real world if it is to answer questions about the real world • Data Modeling is a design technique for capturing reality STUDENT Social_Security_No Name Major

  11. Using Logical Modeling to Create a Relational Database • Most relational databases include many tables, not just one, to avoid redundancy. • This redundancy can result in the database table taking up unneeded storage space as well as causing problems when trying to insert new records, delete existing records, or update records. • These problems, typically referred to as anomalies, can harm the integrity of the database records. • To create relational databases that avoid these problems with redundancy, logical modeling is used. • The first step in logical modeling is to create an Entity-Relationship Diagram (ERD) that allow us to focus on the “big picture,” that is, the entities and the relationships.

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

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

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

  15. Entity-Relationship Diagramming The symbols for an ERD are (where 1:1 means a one-to-one relationship and 1:M means a one-to-many relationship):

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

  17. Relational Databases • Relations (a.k.a. tables) • Each row is unique (entity instance) • Order is unimportant • Each column represents one thing (attribute) • Entries are from the same domain (e.g. student)

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

  19. 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)

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

  21. How this looks in Access CUSTOMER 1 PRODUCT 1 CustomerIDFirstNameLastNameAddressCityStateZip Telephone ProductIDDescriptionPriceWeightSupplier ORDER 1 OrderIDOrderDateCustomerID ORDER LINE OrderIDProductIDQuantity 8 8 8

More Related