Day 12: Databases

1. Day 12:Databases RAHUL KAVIRahul.Kavi@mail.wvu.edu October 3, 2013

2. Last class • Two Variable What-If Analysis • Goal Seek • Scenario Manager • Solver • 3D Formulas • Templates • Cell Styles

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

4. Popular drop-out Billionaires (importance of computer skills)

5. 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.

6. Where are databases used? • Storing bank records, corporate data, etc. • Storing login credentials for websites/blogs. • Store content of popular websites like Flickr.com, YouTube.com, etc. • Store content of popular services like Netflix, Instagram,Pandora, iTunes, Spotify, Game Center (iOS), App Store, Play Store (Android App Store).

7. Where are databases used? Source: oracle.com

8. 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 are also relationships between tables

9. 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

10. 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

11. 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

12. 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”

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

14. 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

15. 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

16. 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

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

18. Common Excel ISSUES • Switch Row/Column • Merge (but not center) • Relative/Absolute/Mixed References