Transcript CSCI N311 - Department of Computer and Information Science

Department of Computer and Information Science,
School of Science, IUPUI
Introduction to Relational
Databases
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
1
Sharing Knowledge and Success
Oracle is a relational database management system
(RDBMS).
A DBMS allows users to...
put data in
keep/manage the data
get data out and work with it
Dale Roberts
2
Database Management
Disadvantages of traditional file processing systems…
uncontrolled redundancy
inconsistent data
inflexibility
limited data sharing
poor enforcement of standards
low programmer productivity
excessive program maintenance
An DBMS deals with these problem my introduction…
Concurrency control and locking
Transaction processing
Read-consistency
Dale Roberts
3
Database Management: Data Models
Hierarchical Data Model (IMS)
nested sets of 1:1 or 1:M relationships
Network Data Model (IDMS, TOTAL)
multiple sets of 1:1, 1:M, M:1, or M:N relationships
Relational Data Model (Oracle, SQL Server, DB2, mySQL)
A Relational DBMS expresses data in terms of a “relation” or a
table consistent of named columns with data organized into rows.
relationships are NOT physically implemented
uses primary keys to represent associations
terminology: relations (tables), columns, tuples (rows), domain,
degree, cardinality, primary keys, concatenated keys, alternate
keys, foreign keys, Referential Integrity or R.I.
relational algebra, three main operators: select, project, join
normalization theory:1NF, 2NF, 3NF
Dale Roberts
4
Sharing Knowledge and Success
• The Language of Oracle: SQL
– Structured Query Language, English like query language
– keywords: select, from, where, and order by
– Example query:
select city, temperature
from weather
where temperature > 80
order by temperature;
• Examples of relational databases
– stock tables in newspaper
– sports scores
– 100 year example in book
Dale Roberts
5
History of SQL
History of SQL
•
•
•
•
The paper, “A Relational Model of Data for large Shared Data Banks,” by Dr.
E. F. Codd, was published in June 1970 in … Communications of the ACM.
Codd’s model is now accepted as the definitive model for relational database
management systems (RDBMS).
The language, Structured English Query Language (SEQUEL) was developed
by IBM to use Codd’s model.
SEQUEL later became SQL.
In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the
first commercially available implementation of SQL. Today, SQL is accepted
as the standard RDBMS language.
Dale Roberts
6
Client-Server Architecture
In the Oracle database system
environment, the database
application and the database are
separated into two parts: a front-end
or client portion, and a back-end or
server portion—hence the term
client/server architecture.
Client and server are located on
different computers, and these
computers are connected through a
network. The server and clients of
an Oracle database system
communicate through Oracle Net
Services, Oracle's network interface.
Dale Roberts
SQL
7
The Dangers in a Relational Database
• It looks very easy to use a RDBMS
– learning about normalization, SQL, etc. make for instant “experts.”
– lack of experience with major production systems can create
catastrophic project failures.
• Testing cycles are getting shorter
– newer development tools make software development quicker,
usually systems testing gets shortened.
• Recent college grads...
– least experienced developers usually have more training with
relational database technology.
– veteran developers are busy with older projects.
Dale Roberts
8
The Dangers in a Relational Database
Bad Examples of Table and Column Names
• Tables
DEPT
PE
EMP
PROJ
EMPS
TITLES
MYEMPS
PERSONNEL
AU_ORD
DNAME
ENUMBER
LORANGE
PNAME
SLSTAXPCT
BLOC
DISCOUNTTYPE
ESAL
LOWQTY
PROJNO
WORKHRS
• Columns
AD1
CDLEXP
EMPNO
HIGHQTY
NOTE
PUBDATE
AU_LNAME
DEPTNO
ENAME
HIRANGE
ORD_NUM
QTYOH
• Reasons
– abbreviation used without good reason
– inconsistent abbreviations, underlines, and
use of plurals
– purpose not apparent from name
– name rules have limitations
Dale Roberts
9
The Dangers in a Relational Database
Use English Name for Data:
• Poor Example:
Better Example:
Dale Roberts
10
The Basic Parts of Speech in SQL
•
SQL is a language. Oracle SQL is a superset of the American National
Standards Institute (ANSI) and the International Standards Organization (ISO)
SQL92 standard at entry level conformance.
•
PL/SQL is Oracle’s procedural language extension to SQL. It allows you to
link several SQL commands through procedural language.
•
SQL*Plus (SQLPLUS from command line) is a tool that allows users to
interact with Oracle. SQL*Plus enables you to manipulate SQL commands and
PL/SQL blocks, and to perform many additional tasks as well. Through
SQL*Plus, you can:
–
–
–
–
–
enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
format, perform calculations on, store, and print query results in the form of reports
list column definitions for any table
access and copy data between SQL databases
send messages to and accept responses from an end user
Dale Roberts
11
Ten Commandments (suggestions?)
Include Users. Put them on the project team, teach them
SQL.
2. Name tables, columns, data with users. Develop
application thesaurus to ensure consistency.
3. Use English words.
4. Don’t mix levels in naming.
5. Avoid codes and abbreviations.
6. Use meaningful keys.
7. Decompose overloaded keys.
8. Analyze and design from the tasks, not just the data.
Normalization is not design.
9. Move tasks from users to the machine.
10. Don’t be seduced by development speed. Take time in
analysis, design, testing, tuning.
1.
12
Dale Roberts
Acknowledgements
McFadden and Hoffer, Database Management, pp. 814
Loney, Oracle Database 10g The Complete Reference
Oracle® Database Concepts 10g Release 2 (10.2)
Dale Roberts
13