DOC PREVIEW
UB MGS 351 - Test 2 REVIEW

This preview shows page 1-2-20-21 out of 21 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

MGS351 Review SheetI. Relational Database DesignRelational Databases- Store data in the form of related tables- Relational design is based on database normalization and can be accomplished with ERD’s- Benefitso Ensures data integrity and referential integrity Adding, deleting, and updating recordso Powerful to use - Design Principleso You must become a business expert o Poor relational database design leads to system failureo Proper design accurately reflects the organization’s business ruleso 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 expertTransforming 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 customerso 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 relationshipso Avoid common database problemso Make the data as tightly bound as possibleo 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 thekey **- Database normalization steps:o 1NF – remove repeating groupso 2NF – remove fields dependent only on part of the key fieldo 3NF – remove fields dependent only on other fields in that table o Example: see page 12 II. Databases and Data WarehousesData Hierarchy- Database- Table (File, Relation)- Records (Rows)- Fields (Columns, Attributes)- Bytes- BitsTraditional File Environment (1950’s)- Issueso Data Redundancyo Data Inconsistencyo Data Isolation (some departments have certain info while others do not)o Data Integrity (is the information valid?)o Securityo 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 builto 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 ServerHierarchal Database – used to break one component into subgroupsNetwork 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 storedo Example: C-drive, flash drive, etc… Business Intelligence - Knowledge about:o Customerso Competitorso Partnerso Competitive Environmento 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 informationo Operational database for day-to-day operations o Example: who is in a certain class?- Online analytical processing (OLAP) – the manipulation of information to supportdecision making o Takes data from OLTP, summarizes it, and stores it in a data warehouseo 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


View Full Document

UB MGS 351 - Test 2 REVIEW

Download Test 2 REVIEW
Our administrator received your request to download this document. We will send you the file to your email shortly.
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 2 2 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?