Data Management with SQL

1
Data Managementwith SQL

• Information storage and Retrieval

2
Overview

• What is the difference between files and
  databases?
• Advantages of a database
• The basic structure of a database
• Oracle
• SQL

3
Comparison of File Database Systems

• Database oriented
• database contains data on multiple types of
  entities and relationships
• data can be shared
• DBMS provides service for sharing
• data integrated and multiple access paths

• File oriented
• file contains data about one type of entity
• each program owns its own file
• file-system services used to access data
• data duplication - redundancy

4
ADVANTAGES OF DATABASE SYS

• REDUCED REDUNDANCY - Controlling duplication
• INTEGRATION AND SHARING OF DATA - One set of
  data to maintain
• MAINTAINING CONSISTENCY - Data propagation
• DATA ACCESS - Multiple access paths High
  level language
• STANDARDIZATION - Enforcing local and global
  standards
• SECURITY - User access Protection of Data
• DATA INDEPENDENCE - Data descriptors separate
  from application program

5
APPLICATION PROGRAMS
FILE SYSTEM
6
(No Transcript)
7
RELATION(Table) FILE
Attributes (columns) field

• Last
• Name

First Name
SS
Dept
Salary
Tuples (rows) record
SMITH
JOHN
112113456
ACC
34000
ANDERSON
MARY
223445676
IS
44000
CHIN
LOU
222345678
IS
50000
8
RELATIONAL DATABASE MODEL TERMS
TERM
DEFINITION
Tuple
A row in a relation
Column of relation
Attribute
Domain
Value set from which attribute
chosen
Degree
Number of attributes in relation
Cardinality
Number of tuples in relation
Primary key
Particular candidate key
Foreign key
Attribute which references primary
key in another relation
9
Oracle at Bentley

• Multi-user relational database
• Logon to your Bentley account THEN access Oracle
• Centralized Oracle database
• allows sharing
• security
• Multiple tablespaces, multiple databases

10
Basic SQL Command

• SELECT ATTRIBUTES
• FROM TABLENAME
• SELECT
• FROM ANIMAL
• How to find out what tables exist in the
  database
• SELECT
• FROM TAB

11
Basic SQL Commands

• How to find out what attributes exist in a table
• DESCRIBE ANIMAL
• SELECT ATTRIBUTES
• FROM TABLENAME
• WHERE CONSTRAINT
• SELECT ANAME
• FROM ANIMAL
• WHERE ASPECIES LION