Transcript Problem Solving

DAY 12:
DATABASES
RAHUL KAVI
[email protected]
October 3, 2013
1
LAST CLASS
•
•
•
•
•
•
•
Two Variable What-If Analysis
Goal Seek
Scenario Manager
Solver
3D 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, YouTube.com, etc.
• Store content of popular services like
Netflix, Instagram,Pandora, iTunes,
Spotify, Game Center (iOS), 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 are 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, 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
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’ (‘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’
17
COMMON EXCEL ISSUES
• Switch Row/Column
• Merge (but not center)
• Relative/Absolute/Mixed References
18