Transcript Day 4: Critical Thinking and Data Analysis

DAY 12:
DATABASE CONCEPT
Tazin Afrin
[email protected]
September 26, 2013
1
DATABASE
• An organized collection of data.
• Database supports processes requiring
information about that data.
• Example :
– Address book
– Record of all employees of CNN international
on their payroll
2
DBMS
• Database Management System contains
information about a particular enterprise
– Collection of interrelated data
– Set of programs to access the data
– An environment that is both convenient and
efficient to use
• Examples of DBMS :
–
–
–
–
MySQL
SQLite
Microsoft SQL Server
Microsoft Access
3
DBMS
• Database Applications:
– Banking: all transactions
– Airlines: reservations, schedules
– Universities: registration, grades
– Sales: customers, products, purchases
– Manufacturing: production, inventory, orders,
supply chain
– Human resources: employee records,
salaries, tax deductions
4
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.
• There are relationships between tables.
• Example :
– Microsoft Access
5
SAMPLE RELATIONAL DATABASE
6
TERMINOLOGY
• Record
– In the context of a relational database, a row—also
called a record or tuple—represents a single,
implicitly structured data item in a table.
– Each record represents a set of related data
– Every record has the same structure.
• Field
– Each piece of data in a record is a field
– Some fields are required for each record, others are
optional
7
RECORDS AND FIELDS
Record
Field
8
PRIMARY KEY
• A table typically has a column or combination
of columns that contain values that uniquely
identify each row in the table. This column, or
columns, is called the primary key of the
table.
– A table can contain only one primary key
constraint.
9
FOREIGN KEY
• A foreign key is a column or combination of
columns that is used to establish and enforce
a link between the data in two tables.
• It controls the data that can be stored in the
another table.
• A link is created between two tables when the
column that hold the primary key value for
one table are referenced by the column in
another table.
• This column becomes a foreign key in the
second table.
10
FOREIGN KEY
11
FOREIGN KEY
12
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
13
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
• Relationships enable you to prevent
redundant data.
• 4 kinds of relationships
14
RELATIONSHIPS
One to one
One to many
15
RELATIONSHIPS
Many to one
Many to many
16
REFERENTIAL INTEGRITY
• Make sure that relationships between records
in related tables are valid.
• Ensures that you do not accidentally delete or
change related data.
• You cannot enter a value in the foreign key
field of the related table that does not exist in
the primary key of the primary table.
– Cannot enter new account in the account table for
a customer who does not exists.
– But can enter new account with NULL value in
customer ID
17
CASCADING
• You can specify whether you want to
automatically cascade update or cascade
delete related records from different
tables.
– 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
18
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
19
SQL
• SQL is structured query language
• SQL is how Access interacts with data under
the hood
• Queries:
– INSERT INTO ‘table’ VALUES (‘value1’, ‘value2’)
– UPDATE ‘table’ SET ‘field1’ = ‘value1’ WHERE
‘field2’ = ‘value2’
– DELETE FROM ‘table’ WHERE ‘field’ = ‘value’
– SELECT ‘field1’ FROM ‘table’ WHERE ‘field2’ =
‘value’
20
EXAM 1
• Student ID is required
• If you need accommodations, please make
your request today.
• Arrive 15 minutes early
• 10 minutes before scheduled class time, test
instructions will be distributed
• You may begin at your scheduled class time
• You will have 60 minutes
21
EXAM 1
• 1st October
• Section 58 : at 1:00 pm
• Section 60 : at 2:30 pm
22
THANK YOU
LOG OFF