Database Principles What s a database Computerized record keeping system Automated rolodex filing cabinet etc Organized collection of logically related data Text numeric dates audio video spreadsheets etc Basic features Insert new data in the middle Add new data at end Retrieve data Update modify existing data Delete data BMGT301 2 Database Approach A software system that is used to create maintain and provide controlled access to user databases Order Filing System Invoicing System Payroll System resources BMGT301 DBMS manages data resources like an operating system manages hardware DBMS Central database Contains employee order inventory pricing and customer data 3 Benefits of a DBMS Benefits Data independence program data separation Reduced data redundancy Inconsistency can be avoided Data sharing Standards can be enforced Security can be applied Integrity can be maintained Conflicting requirements can be balanced enterprise wide optimization Business Unit Business Unit DBMS Data BMGT301 4 Reduced Information Redundancy Databases reduce information redundancy Redundancy the duplication of information or storing the same information in multiple places Inconsistency is one of the primary problems with redundant information Important BMGT301 5 Terminology Database terminology Table A grid of rows columns that is a collection of occurrences of one type of record cannot mix record types Record A row in a table A collection of related stored fields can have multiple occurrences of same type field within a record e g 6 car model fields car1 car2 etc in a car club database Field or Attribute A column in a table or the smallest unit of stored data Defines instance of information name part no format integer size 10 BMGT301 6 Table Example Emp ID Name Dept Name Salary 100 140 110 190 150 Simpson Marketing 48 000 Beeton Accounting 52 000 Lucero IS 43 000 Davis Finance 55 000 Martin Marketing 42 000 BMGT301 7 Data Types All DBMS support various data types Each product Oracle DB2 Access is sadly unique Do this for proprietary reasons This costs you money time effort if you want to switch from one DBMS to another A data type is a characteristic you designate for a field when you create it It determines what kind of data can be entered into the field BMGT301 8 MS Access Data Types Text Number Currency Auto Number Date Time Memo Yes No OLE Object Hyperlink Most common a string of alphanumeric characters Store numeric data general Special kind of number avoids rounding errors Special kind of number automatically incremented integer Stores dates and or times in many formats Special kind of text field up to 65 536 char Binary field either yes no or true false Link to a complex object like an audio file or picture Link to a file or website Opens it when clicked BMGT301 9 Relational Model There are many ways models to build a database system Relational hierarchical network object oriented etc Similarly there are many ways to build motor vehicles Gas diesel hybrid electric front wheel rear wheel all wheel Same fundamentals different implementations different pros cons BMGT301 10 Relational Model The definition is very simple A database is a collection of data organized in a particular way A relational database stores its data in tables MS Access for example is a relational database so its data is stored in tables BMGT301 11 ER Model of 3 Table DBMS Relational model because tables are related to each other linked Student ID STUDENT Semeste r Grade Registers for Course ID Title Location COURSE Name Address Major Instructor Name INSTRUCTOR Teaches BMGT301 12 Relational Model basic principles 1 2 3 Prime Relational properties based on set theory No duplicate rows Result of the mathematical definition of a relation Sets in mathematics do not contain duplicates As stated before we can have databases tables with duplicate records Rows are unordered Again this is a mathematical set and sets have no ordering In a table or database there is ordering At minimum there is the physical ordering or entry of records Fields Attributes are unordered Same argument This normally can be followed in a table or database as attributes or fields are usually referenced by name 4 Fields Attributes are atomic Each field or attribute has only one value not a list of values This also implies that relations do not contain repeating groups i e they are normalized 5 Tables have names 6 Fields columns have names BMGT301 13 Example Employee1 Table tblEmployee1 Emp ID Name Dept Name Salary Simpson Marketing 48 000 100 140 110 190 150 Beeton Accounting 52 000 Lucero IS 43 000 Davis Finance 55 000 Martin Marketing 42 000 BMGT301 14 Primary Keys PK Primary Keys Relational Model Property 1 says all rows must be unique no duplicates This implies that each row is uniquely identifiable A Primary Key is the attribute or combination of attributes that uniquely identifies each row BMGT301 15 The One Table Solution It is tempting to keep things simple and put all your data relating to a problem in one all encompassing table Program Name Network Name Popular Name Notes Andy Griffith Columbia Broadcasting CBS One of the largest Bob Newhart Columbia Broadcasting CBS One of the largest I Love Lucy Columbia Broadcasting CBS One of the largest BMGT301 16 What s Wrong Redundancy Same data entered over and over Any time you need to make a change you may have to change that attribute in many places Implies you need a program to make this change Ex search for old phone number and when you find it change it to xxx xxxx But what happens when someone makes an error and enters an o instead of a 0 in one entry Your program never finds it error stays Worse You don t know it Relational rule one fact in ONE place BMGT301 17 Relational Tables In the relational model we break up a business problem into subject tables Remember one fact in one place Thus we have customer tables order tables shipping tables inventory tables student tables course tables etc We link these tables together via the Primary Keys PKs and something called Foreign Keys FKs place Customers Orders drawn from Inventory BMGT301 18 Relational System Design Customer Order places contains Order Line Item Shipped via Drawn from Distributor Product Inventory entities relationships BMGT301 19 Keys and Relationships BMGT301 20 Good Practice When you use a database you want to be able to Feel absolutely confident that the data is correct trust reports queries Be able to operate and maintain the system
View Full Document
Unlocking...