Department of Computer and Information Science, School of Science, IUPUI Fall 2003

1
Department of Computer and Information
Science,School of Science, IUPUIFall 2003
Introduction to Relational Databases

• Dale Roberts, Lecturer
• Computer Science, IUPUI
• E-mail droberts_at_cs.iupui.edu

2
Sharing Knowledge and Success

• Oracle is a relational database management system
  (RDBMS).
• RDBMSs allow users to...
• put data in
• keep/manage the data
• get data out and work with it

3
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 gt 80 order by
  temperature
• Examples of relational databases
• stock tables in newspaper
• sports scores
• 100 year example in book

4
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 maintencancefrom Database
  Management, McFadden and Hoffer, pp. 8-14

5
Database Management Data Models

• Hierarchical Data Model (IMS)
• nested sets of 11 or 1M relationships
• Network Data Model (IDMS, TOTAL)
• multiple sets of 11, 1M, M1, or MN
  relationships
• Relational Data Model (Oracle, SQL Server, DB2)
• 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 theory1NF, 2NF, 3NF

6
Database Management Normalization

• First Normal Form (1NF)
• Each column contains values about the same
  attribute, and each table cell value must be a
  single value.
• Each column has a distince name, order of columns
  is immaterial.
• Each row is distinct, rows cannot be duplicate
  for the same key
• The sequence of rows is immaterial.
• Second Normal Form (2NF)
• All non-key attributes must be fully dependent on
  the whole key.
• Third Normal Form (3NF)
• Each nonkey attribute should be dependent only on
  the relations key, not on any other nonkey.

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.

8
The Dangers in a Relational Database

• How to reduce the confusion
• Normalization1NF2NF3NF English
  names for tables and columns, English code names

9
The Dangers in a Relational Database
10
The Dangers in a Relational Database

• Bad Examples of Table and Column Names
• TablesDEPT EMP EMPS MYEMPSPE PROJ TITLES PERSONN
  EL
• ColumnsAD1 AU_LNAME AU_ORD BLOC CDLEXP DEPTNO DN
  AME DISCOUNTTYPEEMPNO ENAME ENUMBER ESALHIGHQTY
  HIRANGE LORANGE LOWQTYNOTE ORD_NUM PNAME PROJNOP
  UBDATE QTYOH SLSTAXPCT WORKHRS
• Reasons
• abbreviation used without good reason
• inconsistent abbreviations, underlines, and use
  of plurals
• purpose not apparent from name
• name rules have limitations

11
Chapter 2 The Dangers in a Relational Database

• Use English Name for Data
• Poor ExampleBetter Example

12
The Basic Parts of Speech in SQL

• SQL is a language. Oracle7 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 Oracles procedural language extension
  to SQL. It allows you to link several SQL
  commands through procedural language.
• SQLPlus (SQLPLUS from command line) is a tool
  that allows users to interact with Oracle.
  SQLPlus enables you to manipulate SQL commands
  and PL/SQL blocks, and to perform many additional
  tasks as well. Through SQLPlus, 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

13
The Basic Parts of Speech in SQL

• SQLPlus, very quick overview
• first, create/run a script to start an Oracle
  database instance
• from the command linesqlplusSQLgt
• Common commands in SQLPlus
• get ltfilenamegt
• _at_ltfilenamegt
• save ltfilenamegt
• list or l
• change or c
• edit
• save
• ! (shell to OS)
• Capitalization generally doent matter

14
The Basic Parts of Speech in SQL

• Relational Algebra
• manipulates 1 or 2 relations (tables) and returns
  1 new relation as a result.
• basic relational algebra operators are SELECT,
  PROJECT and JOIN.
• SELECT returns a horizontal subset of a relation.
• PROJECT returns a vertical subset of a relation
• JOIN returns the combination of 2 relations based
  on common attributes. from Database
  Management, McFadden and Hoffer, pp. 214-216

15
The Basic Parts of Speech in SQL

• Select, Project, Select and Project
• Join

16
The Basic Parts of Speech in SQL

• Simple Select Examples
• Select statement, single table query, all rows
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• ORDER BY CUSTOMER_NUMBER
• ... specific rows (where clause)
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER gt 500
• ... with a calculation
• SELECT CUSTOMER_NUMBER, LAST, (CREDIT_LIM -
  BALANCE)
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER gt 500
• ... multiple where conditions (and)
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• WHERE BALANCE gt 500
• AND BALANCE lt 1000

17
The Basic Parts of Speech in SQL

• The SELECT clause
• can be a list of columns,
• an asterik for all columns,
• a calculation (or other expressions),
• group functions (chapter 9),
• distinct keyword -- removes duplicates.
• SELECT DISTINCT SUPPLIER
• FROM PRODUCTS
• column names can be renamed in the output with
  an alias. SELECT CUSTOMER_NUMBER, LAST,
  FIRST, (CREDIT_LIM - BALANCE) AS
  REMAINING_CREDIT
• FROM CUSTOMER
• The FROM clause
• in single tables queries, just provide your
  tables name . more on this in multi-table
  queries.

18
Chapter 3 The Basic Parts of Speech in SQL

• The WHERE Clause
• this is a logical, boolean expression which must
  evalute to true for each row in the querys
  output.
• where clause comparison operators
• equal to
• lt, gt less than, greater than
• lt, gt less than or equal, greater than or equal
• ltgt, ! , not equal
• compound conditions are built with the boolean
  operators AND, OR.
• AND is evaluated first by default. Use
  parenthesis ( ) to force the order of OR/AND.
• a compliment of a condition can be evaluated with
  NOT. Examples below are equivalent
• SELECT PART_DESCRIPTION FROM PART WHERE
  WAREHOUSE_NUMBER ! 3
• SELECT PART_DESCRIPTION FROM PART WHERE NOT
  (WAREHOUSE_NUMBER3)

19
The Basic Parts of Speech in SQL

• The WHERE Clause (continued)
• BETWEEN keyword is the same as gt and lt. For
  example, these 2 statements are the
  same SELECT CUSTOMER_NUMBER, LAST, FIRST,
  BALANCE
• FROM CUSTOMER
• WHERE BALANCE gt 500 AND BALANCE lt 1000
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• WHERE BALANCE BETWEEN 500 AND 1000
• LIKE keyword is used for pattern matching.
  is for any number of characters._ (underscore)
  is for one character.
• SELECT CUSTOMER_NUMBER, LAST, FIRST
• FROM CUSTOMER WHERE LAST LIKE JONE
• SELECT CUSTOMER_NUMBER, LAST, FIRST
• FROM CUSTOMER WHERE LAST LIKE JONE_

20
The Basic Parts of Speech in SQL

• The WHERE Clause (continued)
• IN keyword provides a list of numbers or strings
  to compare to. This is similar to using OR with
  . For example, these 2 statements are the
  same SELECT CUSTOMER_NUMBER, LAST, FIRST,
  BALANCE
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER IN (10, 11, 12)
• SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• FROM CUSTOMER
• WHERE (CUSTOMER_NUMBER 10)
• OR (CUSTOMER_NUMBER 11)
• OR (CUSTOMER_NUMBER 12)
• NULL and NOT NULL keywordsNULL does not mean 0,
  null means no value!Example- this statement only
  gets customers whom we have a complete name
  for SELECT CUSTOMER_NUMBER, LAST, FIRST FROM
  CUSTOMER WHERE (FIRST IS NOT NULL) AND
  (LAST IS NOT NULL)

21
The Basic Parts of Speech in SQL

• The WHERE Clause (continued)
• Values can also be compared to another query in
  the where clause. This is called a subquery. For
  example SELECT CUSTOMER_NUMBER, LAST, FIRST,
  BALANCE
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER IN (SELECT
  CUSTOMER_NUMBER FROM CUSTOMER WHERE
  BALANCE gt 500)
• This however may not work sometimes with a
  subquery SELECT CUSTOMER_NUMBER, LAST, FIRST,
  BALANCE
• FROM CUSTOMER
• WHERE CUSTOMER_NUMBER (SELECT
  CUSTOMER_NUMBER FROM CUSTOMER WHERE
  BALANCE gt 500) because subqueries return sets
  of values, not a single value.

22
The Basic Parts of Speech in SQL

• The WHERE Clause (continued)
• Tables can be joined by common attributes. This
  is done in the where clause. SELECT
  WEATHER.CITY, CONDITION, TEMPERATURE,
  LATITUDE,. . . FROM WEATHER, LOCATION WHERE
  WEATHER.CITY LOCATION.CITY
• A shortcut (not in book). Tablenames can be
  aliased to save typing, reduce wordiness.
  SELECT W.CITY, CONDITION, TEMPERATURE,
  LATITUDE,. . . FROM WEATHER W, LOCATION L WHERE
  W.CITY L.CITY
• Joining data from different tables is one of the
  more powerful parts of SQL. It is not without its
  dangers.
• Joining tables the wrong way (not on common
  attributes), the wrong data comes back.
• Forgetting to specify common keys in the where
  clause returns a cartesian product (each row of
  both tables joined to each other, m times n rows)

23
The Basic Parts of Speech in SQL

• The ORDER BY Clause
• Specifies the ordering of the returned data.
• Multiple columns can be selected to sort on.
• ASC is ascending order order, default.
• DESC is descending sort order. SELECT FEATURE,
  SECTION, PAGE
• FROM NEWSPAPER
• WHERE SECTION F ORDER BY PAGE DESC,
  FEATURE ASC
• Views
• Makes a query act similar to a table.
• Views are describe-able, select-able
• The views select statement is ran everytime rows
  are selected from the view--ie data is not saved
  somewhere (like a table)
• CREATE VIEW INVASION AS
• SELECT W.CITY, CONDITION, TEMPERATURE,
  LATITUDE FROM WEATHER W, LOCATION L WHERE
  W.CITY L.CITY

24
Acknowledgements

• These slides were originally prepared by Tony
  Teal as part of the IUPUIs N311 Advanced
  Database Programming course.