observe yourself turn off communication devices dress properly
TRANSCRIPT
Observe Yourself
Turn off communication devices
Dress properly
DATABASE
Key Issues
What is a database? Some important database concepts and
vocabulary. Key characteristics about the data in a
database.
What is a Database?
Database is a collection of related data that is stored and organized in a manner enabling information to be retrieved from the database as needed.
[Morley 3008 p575]]
Database Software
It is more formally called a database management system (DBMS), is used to create, maintain, and access databases.
A DBMS also controls the organization of the data and protects the integrity and security of the data, so it is accurate and protected against both intentional and accidental damage.
DBMS Product Category
Workgroup/ Personal level Enterprise level
Enterprise level can serve heavier load than personal level in terms of number of users accessing database or amount of data stored in a database.
DBMS Product
Examples of personal level are Microsoft Access, Corel Paradox, Lotus Approach.
Examples of enterprise level are Microsoft SQL Server, Oracle, IBM DB2, MySQL
Database Element
A database typically consists of interrelated tables consisting of fields and records.
A field (column) is a single type of data to be stored in a database, such as Person’s name: Thomas, Jimmy Person’s telephone number: 02-300-4543 ID code: 451277
A record (row) is a collection of related fields.
Key Field
The field which is chosen to be the uniquely identify a record so the record can be easily retrieved and process
Can either called “ ” Eg. Student ID
•David•Thailand•1st Year
David
•David•Australia•2nd Year
David
Student
?•David•Thailand•1st Year
4715233
•David•Australia•2nd Year
4612544
Student
Data Hierarchy
Characters A letter, number or special character Eg. A, B, C, 1, 2, 3, #
Field Unit of data consisting of one or more characters (bytes) Eg. Name, address
Record A collection of related fields Eg. Each student record
Table A collection of related records Eg. Student file
Database An organized collection of integrated files Eg. S&T database
•David•Thailand•1st Year
David
•Annie•Australia•2nd Year
Annie
Student
•Grace•Thailand•CS
Grace
•Peter•Australia•IT
Peter
Teacher
Flat File and Database
Flat File
A file management system is a program that allows the creation of individual database tables (flat files), each of which is stored in its own physical file and is not related to any other file.
Consequently, file management systems can work with only one table at a time, and each table has to contain all the data that may
need to be accessed or retrieved at one time. As a result, file management systems have a
much higher level of redundancy than database management systems.
An organization would have different files for different purposes.
Problem in Flat File Processing Data redundancy Data inaccuracy
Relational Database
In a relational database, data from several tables is tied together (related) using a field that the tables have in common so that information can be extracted from multiple tables, if needed, to fulfill a user request.
File
DB
Advantage of DBMS
Reduced Data Redundancy Data redundancy means the same data fields appear in
many different files and is in different format Save storage space
Improve Data Integrity Data integrity: data is accurate, consistent, and up-to-
date The change of data just make in only one place.
File sharing All authorized users can work on the same set of file.
Increased security Each level of user has different level of access.
Retrieving information from a relational database
Query language
Retrieving data from a database is called query.
Query Language An easy-to-use computer language for making
queries to a database and for retrieving selected records based on criteria.
Query Language
Each DBMS software provides its own tool for query. The most popular is Structured Query Language
Ex. SELECT product_nameFROM product
WHERE price = 100
Form of SQL
The fields used in the query are specified with
The tables to which they belong are specified with
Selection criteria are determined by The query results can be sorted in any
sequence with
Demo
Query/Requirement Retrieve address and e-mail of student ID
5001234
Demo
Query/Requirement Seek the students who get honor.
QBE
• Query By Example(QBE) is a feature of query language that use a sample record form to define the qualifications he wants for selected records. In other words, the user fills in a form
EX
You want to contact to studentID 5201111 So you have to search for E-mail and phone
no. of studentID 5201111.
More SQL Statements
Source : http://www.w3schools.com/sql/sql_quickref.asp
SQL Statement Syntax
AND / OR SELECT column_name(s)FROM table_nameWHERE conditionAND|OR condition
ALTER TABLE ALTER TABLE table_name ADD column_name datatypeorALTER TABLE table_name DROP COLUMN column_name
AS (alias) SELECT column_name AS column_aliasFROM table_nameorSELECT column_nameFROM table_name AS table_alias
BETWEEN SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2
CREATE DATABASE
CREATE DATABASE database_name
CREATE TABLE CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name2 data_type,...)
CREATE INDEX CREATE INDEX index_nameON table_name (column_name)orCREATE UNIQUE INDEX index_nameON table_name (column_name)
CREATE VIEW CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition
DELETE DELETE FROM table_nameWHERE some_column=some_valueorDELETE FROM table_name (Note: Deletes the entire table!!)DELETE * FROM table_name (Note: Deletes the entire table!!)
DROP DATABASE
DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name (SQL Server)DROP INDEX index_name ON table_name (MS Access)DROP INDEX index_name (DB2/Oracle)ALTER TABLE table_nameDROP INDEX index_name (MySQL)
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)FROM table_nameWHERE column_nameIN (value1,value2,..)
INSERT INTO INSERT INTO table_nameVALUES (value1, value2, value3,....)orINSERT INTO table_name(column1, column2, column3,...)VALUES (value1, value2, value3,....)
INNER JOIN SELECT column_name(s)FROM table_name1INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LEFT JOIN SELECT column_name(s)FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
RIGHT JOIN SELECT column_name(s)FROM table_name1RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)FROM table_name1FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)FROM table_nameORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s)FROM table_name
SELECT * SELECT *FROM table_name
SELECT DISTINCT
SELECT DISTINCT column_name(s)FROM table_name
SELECT INTO SELECT *INTO new_table_name [IN externaldatabase]FROM old_table_nameorSELECT column_name(s)INTO new_table_name [IN externaldatabase]FROM old_table_name
SELECT TOP SELECT TOP number|percent column_name(s)FROM table_name
TRUNCATE TABLE
TRUNCATE TABLE table_name
UNION SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2
UPDATE UPDATE table_nameSET column1=value, column2=value,...WHERE some_column=some_value
WHERE SELECT column_name(s)FROM table_nameWHERE column_name operator value
Master/Transaction fileBatch/Realtime processingIndividual/Distributed databaseDBAData recoveryData mining
Other concepts
Type of Data Files
Master File Data file which contains relatively
permanent records that are generally update periodically
Transaction File Temporary holding file that holds all
change to be made to the master file: addition, deletion, revision.
Each period, master file would be updated with the change called for in transaction file.
Sale Amount
2005Jan45,456 Fab75,558Mar21,794Apr34,129
JanFeb
MarApr
May
Unchanged
update
ProductID
ProductName
Quantity on Hand
001 TV 10
002 Radio 10
003 DVD Player 20
Inventory Table
OrderID CustomerName ProductID Time Quantity
1 Alice 001 11:00 1
OrderID CustomerName ProductID Time Quantity
2 Bob 001 12:00 1
OrderID CustomerName ProductID Time Quantity
3 Charlie 002 17:30 2
Transaction File
Update
Purchase TableMaster File
Batch & Real-time Processing The transactions can be handled via Batch processing (offline processing)
the data is gathered over a period of time and then processed in a set (batch) at periodic intervals, such as at the end of the day or once a week.
Real-time processing (online transaction processing, OLTP) Each transaction is processed immediately
as it is entered.
Database Classification
Single-user vs. Multiuser Database Systems
Centralized vs. Distributed Database Systems
Single-user & Multiuser DB
Single-user Multiuser
Centralized & Distributed DB
Centralized DB Distributed DB
Customer Address
Customer Credit History
Database Administrator (DBA) Person who coordinates all related
activities and needs for an organization’s database.
The DBA determines user access privileges, sets standards, guidelines, and control procedures, backup data.
Responsibilities of DBA Database design, implementation, and operation
How space will be used on secondary-storage devices How file or record may be added, deleted, changed
Coordinate with users User access privileges Control procedures Establish priority for requests Develop user documentation
System security Prevent unauthorized access
Backup and recovery Perform regularly backup Develop plan for recovering data
Performance monitoring Make sure that service for user is at the standard, not too slow
Data Warehouse
It is a comprehensive collection of data about a company and its customers.
In a data warehouse, data from transaction processing and other operations are reorganized and put into a form that is optimized for queries.
http://www.persysinc.com/images/persys_data_warehouse.gif
Data Mining
Data mining makes use of a data warehouse by applying intelligent software to scan its contents for subtle patterns that may not be evident to management.
It identifies patterns and relationships between data. It can be a useful sales and marketing tool to help
retail companies match customers with products that would be likely to purchase – a type of customer profiling.
Wal-Mart is one company that uses extensive data mining and targets specific types of merchandise in their local stores, based on the characteristics of the people that live in that geographical area.
http://www.mbaknol.com/management-information-systems/data-mining-and-its-business-applications/attachment/data-mining/