DAY 12 DATABASES RAHUL KAVI Rahul Kavimail wvu

DAY 12: DATABASES RAHUL KAVI Rahul. Kavi@mail. wvu. edu October 3, 2013 1

LAST CLASS • • Two Variable What-If Analysis Goal Seek Scenario Manager Solver 3 D Formulas Templates Cell Styles 2

DATABASES • A database is an organized collection of data • There are many types of databases – Flat File – XML – Workbooks – Relational – Key-Value Store 3

POPULAR DROP-OUT BILLIONAIRES (IMPORTANCE OF COMPUTER SKILLS) 4

WHY LEARN THESE SKILLS? • Database skills are an essential part of basic computer skills like Programming. • Limitless possibilities with a 800$ computer from the comfort of home (consume or produce). • Create own website/blog (commercial, personal). • Computer skills like Programming, Database Management, Blogging are like culinary skills. • Business Plan competition. 5

WHERE ARE DATABASES USED? • Storing bank records, corporate data, etc. • Storing login credentials for websites/blogs. • Store content of popular websites like Flickr. com, You. Tube. com, etc. • Store content of popular services like Netflix, Instagram, Pandora, i. Tunes, Spotify, Game Center (i. OS), App Store, Play Store (Android App Store). 6

WHERE ARE DATABASES USED? Source: oracle. com 7

RELATIONAL DATABASES • Access is a relational database – A relational database is a collection of data items organized as a set of formally described tables from which data can be accessed easily – In addition to table definitions, there also relationships between tables 8

RECORDS AND FIELDS REVIEW • A single entry in a table is called a “record” – A record (row) is one or more pieces of data about a single entity • Each piece of data in a record is a “field” (column). • A table definition lists all the fields the records in that table have – The definition can define default values for fields – Some fields are required for each record, others are optional 9

KEYS • Each table has to have a “primary key”. This is a field, or a combination of fields that will be unique to each record • Keys allow you to identify a particular record • You can use the key in other tables to reference the record 10

INDEXES • Indexes are an ordering of a key or other field that is computed on creation and kept up to date as the database is updated • By using the index, the database software is able to quickly retrieve the record given the field value 11

RELATIONSHIPS • By including a the key from one table as a field in a different table, we create a relationship between the two tables • This allows us to link the data between two tables • In the second table, the field is known as a “foreign key” 12

EXAMPLE • Two tables: People, Phone. Numbers – People: id, first_name, last_name, birthdate – Phone. Numbers: id, country_code, area_code, number, person_id • Phone. Numbers. person_id would hold the same value as People. id for phone numbers that belong to the given person 13

CASCADING • If the records with the foreign key are only used as an extension of the original table, you may want to cascade updates and/or deletes – Deletes: If the original record is deleted, the foreign key record is deleted – Updates: If the key of the original record is changed, the foreign key is updated to match 14

PLANNING DATABASES • Define what tables you need • Define what fields belong in each table • Define what data types each field should be • Define default values for each field • Choose between required and optional 15

NORMALIZATION • Normalization is a process of organizing fields and tables to minimize redundancy of data – DRY (don’t repeat yourself) – If you repeat yourself, when you need to make a change you have to change it everywhere or you will have problems 16

SQL • SQL is structured query language • SQL is how Access interacts with data under the hood • INSERT INTO ‘table’ (‘field 1’, ‘field 2’) VALUES (‘value 1’, ‘value 2’) • UPDATE ‘table’ SET ‘field 1’ = ‘value 1’ WHERE ‘field 2’ = ‘value 2’ • DELETE FROM ‘table’ WHERE ‘field’ = ‘value’ • SELECT ‘field 1’ FROM ‘table’ WHERE ‘field 2’ = ‘value’ 17

COMMON EXCEL ISSUES • Switch Row/Column • Merge (but not center) • Relative/Absolute/Mixed References 18