UB MGS 351 - Test 2 REVIEW (21 pages)

Previewing pages 1, 2, 20, 21 of 21 page document View the full content.
View Full Document

Test 2 REVIEW



Previewing pages 1, 2, 20, 21 of actual document.

View the full content.
View Full Document
View Full Document

Test 2 REVIEW

131 views


Pages:
21
School:
University at Buffalo, The State University of New York
Course:
Mgs 351 - Intro to Mgmt Info Systms
Unformatted text preview:

MGS351 Review Sheet I Relational Database Design Relational Databases Store data in the form of related tables Relational design is based on database normalization and can be accomplished with ERD s Benefits o Ensures data integrity and referential integrity Adding deleting and updating records o Powerful to use Design Principles o You must become a business expert o Poor relational database design leads to system failure o Proper design accurately reflects the organization s business rules o Relational database design is both an art and science cost benefit Entity Relationship Diagrams diagramming tool used to design a relational database blueprint If designed properly an ERD will accurately reflect the business rules of the organization It will become a visual illustration of the organization s business rules Corrections are harder than adding on 1 1 1 N N M Entities rectangles Lines relationships Entity tables something about which data is kept Attribute fields descriptive value associated with an entity Key attribute primary keys attributes that uniquely identify an entity Example a customer entity has name address fax number and phone number attributes Relationships have 2 parts and an action verb to describe the process The concept of time must be factored into relationships Clearly define entities Identify all exceptions to the rule often difficult to define even if you are the business expert Transforming ERD s to Tables Entities tables Attributes fields Key attributes primary key Ensure all relationships simplify to a 1 N 1 1 1 relationships merge into one table One entity becomes an attribute of the other 2 1 N add a primary key from the one entity as a foreign key in the many entity 3 N M add an intersection table which includes a primary key from both tables Add intersection table Flip relationship lines around so the many side faces the intersection table Add primary keys from the original tables as fields in the intersection table use autonumber to create a new field or concatenate primary keys Assign a name to the intersection table Add other relevant fields to the intersection table if necessary See pages 7 8 for example Determining Relationships Every relationship is made of 2 halves To determine a relationship the correct questions must be asked o Can a single customer have one or many loans o Can a single loan be related to one or many customers o Can a single loan have one or many payments o Can a single payment be applied to one or many loans turn any M M or 1 1 into a 1 M relationships Database Normalization the way database design is often taught in classrooms The learning process of how to use relationships Application of a number of rules to the relational model which will simplify relationships o Avoid common database problems o Make the data as tightly bound as possible o Store the minimum amount of data First second and third normal form Un normalized vs Normalized Goal the fields in a table depend on the key the whole key and nothing but the key Database normalization steps o 1NF remove repeating groups o 2NF remove fields dependent only on part of the key field o 3NF remove fields dependent only on other fields in that table o Example see page 12 II Databases and Data Warehouses Data Hierarchy Database Table File Relation Records Rows Fields Columns Attributes Bytes Bits Traditional File Environment 1950 s Issues o Data Redundancy o o o o o Data Inconsistency Data Isolation some departments have certain info while others do not Data Integrity is the information valid Security Application Data Dependence Programs written on top of data files were very finicky because they had to follow very specific organizational structures and modification would result in error Database Approach Minimal data redundancy Data consistency Integration of data Sharing of data Uniform security privacy and integrity Data independence can add fields tables etc Centralized environment DBMS Components Data definition language DDL specifies content and structure of database and defines each data element creates structure Data manipulation language DML manipulates data in a database Data dictionary stores definitions of data elements and characteristics o Keeps track of what the user has built o Backing up a dictionary creates an empty skeleton Evolution of Databases Hierarchal 1970 s Object Oriented new Relational what we use now Relational Database Represents data as two dimensional tables called relations Relates data across tables based on common data elements primary key links to foreign key Examples DB2 Oracle MS SQL Server Hierarchal Database used to break one component into subgroups Network Database depicts data logically as many to many relationships no flexibility Database Design Conceptual Logical design abstract model of database from a business perspective Physical design shows how the database is arranged on storage devices o Database administrator can modify how it is stored o Example C drive flash drive etc Business Intelligence Knowledge about o Customers o Competitors o Partners o Competitive Environment o Internal Operations Database Administrator responsible for technical operations Data Administrator responsible for making sure data is stored correctly and consistently o Defining and tacking Online transaction processing OLTP the gathering of input information processing that information and updating existing information to reflect the gathered and processed information o Operational database for day to day operations o Example who is in a certain class Online analytical processing OLAP the manipulation of information to support decision making o Takes data from OLTP summarizes it and stores it in a data warehouse o Example how many people in total are in the class Data Warehouse a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis and decision making tasks Multidimensional data models OLAP online analytical processing Data Marts subset of data warehouse in which only a focused portion of the data warehouse information is kept Data Mining finding hidden patterns and trends in data Gets data from many places summarizes it uses it for decision making Data is represented by a large cube made of individual cubes o Each cube has a number o Data is summarized into its smallest form o Slicing and dicing the cube in order to find info Done with special tools


View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

Loading Unlocking...
Login

Join to view Test 2 REVIEW and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Test 2 REVIEW and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?