what's a database a database primer let’s discuss databases n why they are hard n why we need...
TRANSCRIPT
What's a Database
A Database Primer
Let’s discuss databases Why they are hard Why we need them
Advantages of Database Processing
More information from the same amount of data
Share Data Balance Conflicting Requirements Control Redundancy Facilitate Consistency Improve Integrity Expanding Security Increasing Productivity Provide Data Independence
More information from the same amount of data Data + Processing =
Information
Share Data
Islands of data are difficult to share
Conflicts with file structures, naming conventions
Balance Conflicting Requirements Different parts
of the business have different uses for the data
Control Redundancy
Single authoritative source
Facilitate Consistency
One source – no conflicting data
Improve Integrity
Refer to single authoritative source
Ensure that single source is correct
Expanding Security
Put all your eggs in one basket
Watch the basket
Increasing Productivity
Only one place to store
Only one place to look
Prepare data once
Data Independence
Changes in the physical structure do not affect the logical structure= scalability
Topics Manual database example
Structure Fields, Records, Files Columns, Rows, Tables Attribute, Tuple, Relations Rules: Types of data you can
enter Field Column Attribute
Field Name Column Name Attribute Name
Record Row Tuple
File Table Relation
More Complex Model What if….
A Database is…
Tables Relationships Queries Forms Reports Programs
A Student Record
A Few Students
Table View
A Few Students
Kelly moves
Redundant Data leads to Inconsistent Data
Bud moved too but now the addresses don’t match!His record still has the old address.
A Solution?
A Solution?Move the data that is common to several records to a new table
A Solution!
A Solution
Relationships
Primary Keys
Foreign Key
Even More Relationships
Lots of Tables
Normalize? 1st Normal Form
At each row-column intersection, there must be one, and only one, value.
For example, a database would violate the rule for the first normal form if it stores, in a single row-column intersection, all of the scores for a bowler
2nd Normal Form Every non-key column must depend
upon the entire primary key. No non-key column is dependent on
only a portion of the primary key If the primary key is composite-made up of
more than one component - no non-key column can be a fact about a subset of the primary key.
e.g. compound key firstname+lastname+province code would violate this rule, if another column was functionally dependent on the province code (John+Smith+BC) if “British Columbia” is functionally dependent on the code BC
Only an issue if you have compound primary keys
e.g. name + product code
3rd Normal Form No non-key column can depend
on another non-key field Each column must be a fact about the
entity identified by the primary key All determinants are candidate keys e.g. in a table about people that
contains a column with province codes, and another column with the full name of the province, the province code is a determinant of the full name, but is not a candidate key for the table of people
Defn: Determinant
A column that determines another column is a determinant
4th Normal Form There must not be any
independent one-to-many relationships between primary key columns and non-key columnsi.e. There are no multivalued dependencies
ID Answer1 Answer2 Answer3
223 A B C
224 B C A
5th Normal Form Tables are broken into the
smallest possible pieces in order to eliminate all redundancy within a table.
In extreme cases, tables in fifth normal form may consist of a primary key and a single non-key column.
High integrity Slow performance Redundancy due to extensive use of
foreign keys
Summary – Normal Forms
Normal Form
Meaning/Conditions Notes
First No repeating groups exist
Second 1NF + no nonkey column is dependent on a portion of the primary key
If primary key is a single column then automatically 2NF
Third 2NF + the only determinants are candidate keys
Boyce-Codd Normal Form
Fourth 3NF + no multivalued dependencies
A Historical Perspective•Physical collections
•Flat Files
•Lists
•Indexes and pointers
Other Perspectives
•Hierarchical Structures
•Folders and Files
•Inverted Tree
•Organization Charts
•Single parent – many child relationships
•Network
•Multi – parent – many child relationships
What do you normalize? Office
Supplies Inventory
Office Kitchen Bathroom
Relational Fields, Records, Files Columns, Rows, Tables Attribute, Tuple, Relations Structure and Relationships