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