JIMS2 Discussion Group

1
JIMS-2 Discussion Group

• RDBMS Fundamentals
• September 18th 2003

2
The Relational Database Model

• A database can be understood as a collection of
  related files.
• How those files are related depends on the model
  used.
• Early models included the hierarchical model
  (where files are related in a parent/child
  manner, with each child file having at most one
  parent file), and the network model (where files
  are related as owners and members, similar to the
  network model except that each member file can
  have more than one owner).
• The relational database model was a huge step
  forward, as it allowed files to be related by
  means of a common field.
• In order to relate any two files, they simply
  need to have a common field, which makes the
  model extremely flexible.

3
Basic Concepts

• Data are the values stored in the database. On
  its own, data means very little. "43156" is an
  example.
• Information is data that is processed to have a
  meaning. For example, "43156" is the population
  of the town of Littlewood.
• A database is a collection of tables.
• Each table contains records, which are the
  horizontal rows in the table. These are also
  called tuples.
• Each record contains fields, which are the
  vertical columns of the table. These are also
  called attributes. An example would be a product
  record.
• Fields can be of many different types. There are
  many standard types, and each DBMS (database
  management system, such as Oracle) can also have
  their own specific types, but generally they fall
  into at least three kinds - character, numeric
  and date. For example, a product description
  would be a character field, a product release
  date would be a date field, and a product
  quantity in stock would be a numeric field.

4
Basic Concepts

• The domain refers to the possible values each
  field can contain (it's sometimes called a field
  specification). For example, a field entitled
  "marital_status" may be limited to the values
  "Married" and "Unmarried".
• A field is said to contain a null value when it
  contains nothing at all. Fields can create
  complexities in calculations and have
  consequences for data accuracy. For this reason,
  many fields are specifically set not to contain
  NULL values.
• A key is a logical way to access a record in a
  table. For example, in the product table, the
  product_id field could allow us to uniquely
  identify a record. A key that uniquely identifies
  a record is called a primary key.
• An index is a physical mechanism that improves
  the performance of a database. Indexes are often
  confused with keys. However, strictly speaking
  they are part of the physical structure, while
  keys are part of the logical structure.
• A view is a virtual table made up of a subset of
  the actual tables.

5
Basic Concepts

• A one-to-one (11) relationship occurs where, for
  each instance of table A, only one instance of
  table B exists, and vice-versa. For example, each
  vehicle registration is associated with only one
  engine number, and vice-versa
• A one-to-many (1m) relationship is where, for
  each instance of table A, many instances of the
  table B exist, but for each instance of table B,
  only once instance of table A exists. For
  example, for each artist, there are many
  paintings. Since it is a one-to-many
  relationship, and not many-to-many, in this case
  each painting can only have been painted by one
  artist.
• A many to many (mn) relationship occurs where,
  for each instance of table A, there are many
  instances of table B, and for each instance of
  table B, there are many instances of the table A.
  For example, a poetry anthology can have many
  authors, and each author can appear in many
  poetry anthologies.

6
Basic Concepts

• A mandatory relationship exists where, for each
  instance of table A, one or more instances of
  table B must exist. For example, for a poetry
  anthology to exist, there must exist at least one
  poem in the anthology. The reverse is not
  necessarily true though, as for a poem to exist,
  there is no need for it to appear in a poetry
  anthology.
• An optional relationship is where, for each
  instance of table A, there may exist instances of
  table B. For example, a poet does not necessarily
  have to appear in a poetry anthology. The reverse
  isn't necessarily true though, for example for
  the anthology to be listed, it must have some
  poets.
• Data integrity describes the accuracy, validity
  and consistency of data. An example of poor
  integrity would be where a poet's name is stored
  differently in two different places.
• Database normalization is a technique that helps
  us to reduce the occurrence of data anomalies and
  poor data integrity.

7
Table Keys

• A key is the tool to unlock access to database
  tables. By knowing the key, we know how to locate
  specific records, and traverse the relationships
  between tables.
• A candidate key is any field, or combination of
  fields, that uniquely identifies a record. The
  field/s of the candidate key must contain unique
  values (if the values were duplicated, they would
  be no longer identify unique records), and cannot
  contain a null value.
• A primary key is the candidate key that has been
  chosen to identify unique records in a particular
  table.
• It is always better to choose the candidate key
  with the least number of fields for the actual
  primary key.

8
Foreign Keys

• A relation between two tables is created by
  creating a common field to the two tables. The
  common field must be a primary key to the one
  table (the table that would be the one component
  of the one-to-many relationship).
• Foreign keys allow us to ensure what is called
  referential integrity. This means that if a
  foreign key contains a value, the value must
  refer to an existing record in the related table.
  
• Foreign keys also allow what are called cascading
  deletes and updates. The delete "cascades"
  through the relevant tables, removing all
  relevant records.
• Foreign keys can contain null values if the
  relationship is optional, which indicates that no
  relationship exists. If the relationship is
  mandatory, the foreign key cannot contain nulls.

9
Views

• Views are virtual tables. They do not contain any
  data themselves - rather they're a structure to
  allow us to access data, or a subset of the data.
• A view can consist of a subset of one table.
• A view could be a combination of a number of
  tables.
• Views are often used for security purposes.
  Junior developers may need access to certain
  portions of a table, but they do not need access
  to all the data. What they don't need, even if it
  is from the same table, is hidden and safe from
  manipulation or viewing. Also, views allow SQL
  queries to be much simpler.

10
Database Normalization

• The definition of 1st normal form
• there are no repeating groups
• all the key attributes are defined
• all attributes are dependent on the primary key
• The definition of 2nd normal form
• it's in 1st normal form
• it includes no partial dependencies (where an
  attribute is dependent on only a part of a
  primary key).
• The definition of 3rd normal form
• it's in 2nd normal form
• it contains no transitive dependencies (where a
  non-key attribute is dependent on another non-key
  attribute).

11
What is SQL ?

• SQL, at its simplest, is a basic language that
  allows you to "talk" to a database and extract
  useful information.
• With SQL, you may read, write, and remove
  information from a database. SQL's non-procedural
  nature makes it easier to work with than other
  languages.
• SQL, contrary to popular belief, is not an
  acronym for "Structured Query Language". Rather,
  it's one of those odd acronyms that actually mean
  nothing at all.
• SQL is standardized, and the current version is
  referred to as SQL-92. Any SQL-compliant database
  should conform to the standards of SQL at the
  time.
• the standard SQL commands such as "Select",
  "Insert", "Update", "Delete", "Create", and
  "Drop" can be used to accomplish almost
  everything that one needs to do with a database.

12
The SELECT Statement

• The select statement is used to query the
  database and retrieve selected data that match
  the criteria that you specify.
• Here is the format of a simple select statement
• select "column1" ,"column2",etc from
  "tablename" where "condition"
• optional
• The column names that follow the select keyword
  determine which columns will be returned in the
  results.
• You can select as many column names that you'd
  like, or you can use a "" to select all columns.
  

13
The SELECT Statement

• The select statement is used to query the
  database and retrieve selected data that match
  the criteria that you specify.
• Here is the format of a simple select statement
• select "column1" ,"column2",etc from
  "tablename" where "condition"
• optional
• The column names that follow the select keyword
  determine which columns will be returned in the
  results.
• You can select as many column names that you'd
  like, or you can use a "" to select all columns.
  

14
The WHERE clause

• The where clause (optional) specifies which data
  values or rows will be returned or displayed,
  based on the criteria described after the keyword
  where.
• Conditional selections used in the where clause
• Equal
• gt Greater than
• lt Less than
• gt Greater than or equal
• lt Less than or equal
• ltgt Not equal to
• The WHERE clause can be strengthened by the use
  of AND, OR, and NOT operators.
• SELECT FIRST_NAME, LAST_NAME,VACATION TAKEN,
  VACATION_ACCRUED FROM EMPLOYEES WHERE LAST_NAME
  LIKE "J" AND (VACATION_ACCRUED - VACATION_TAKEN)
  gt 0

15
The SELECT Statement (contd)

• An excellent keyword to get acquainted with is
  "DISTINCT". If you have data that is duplicated,
  but you only want to see one instance of it,
  DISTINCT is just the ticket.
• SELECT DISTINCT COMPANY FROM CUSTOMERS
• SELECT statements are also adept at handling
  comparisons such as greater than and less than.
• SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES WHERE
  VACATION_TAKEN gt VACATION_ACCRUED
• Mathematical functions can be performed in SQL as
  they are in most languages.
• SELECT FIRST_NAME,LAST_NAME, VACATION_TAKEN,VACATI
  ON_ACCRUED, (VACATION_ACCRUED-VACATION_TAKEN)
  FROM EMPLOYEES

16
The LIKE Operator

• The LIKE pattern matching operator can also be
  used in the conditional selection of the where
  clause.
• Like is a very powerful operator that allows you
  to select only rows that are "like" what you
  specify. The percent sign "" can be used as a
  wild card to match any possible character that
  might appear before or after the characters
  specified. For example
• select first, last, city from empinfo where first
  LIKE 'Er'
• This SQL statement will match any first names
  that start with 'Er'. Strings must be in single
  quotes.
• Or you can specify,
• Select first, last from empinfo where last LIKE
  's'
• This statement will match any last names that end
  in a 's'.

17
The CREATE Statement

• The create table statement is used to create a
  new table.
• Here is the format of a simple create table
  statement
• create table "tablename" ("column1" "data type",
  "column2" "data type", "column3" "data type")
• To create a new table, enter the keywords create
  table followed by the table name, followed by an
  open parenthesis, followed by the first column
  name, followed by the data type for that column,
  followed by any optional constraints, and
  followed by a closing parenthesis.
• It is important to make sure you use an open
  parenthesis before the beginning table, and a
  closing parenthesis after the end of the last
  column definition.
• Make sure you seperate each column definition
  with a comma.
• All SQL statements should end with a "".

18
Constraints

• create table "tablename" ("column1" "data type"
  constraint, "column2" "data type" constraint,
  "column3" "data type" constraint)
  optional
• When tables are created, it is common for one or
  more columns to have constraints associated with
  them.
• A constraint is basically a rule associated with
  a column that the data entered into that column
  must follow.
• For example, a "unique" constraint specifies that
  no two records can have the same value in a
  particular column. They must all be unique.
• The other two most popular constraints are "not
  null" which specifies that a column can't be left
  blank, and "primary key". A "primary key"
  constraint defines a unique identification of
  each record (or row) in a table.

19
INSERTing into a Table

• The insert statement is used to insert or add a
  row of data into the table.
• To insert records into a table, enter the key
  words insert into followed by the table name,
  followed by an open parenthesis, followed by a
  list of column names separated by commas,
  followed by a closing parenthesis, followed by
  the keyword values, followed by the list of
  values enclosed in parenthesis.
• The values that you enter will be held in the
  rows and they will match up with the column names
  that you specify. Strings should be enclosed in
  single quotes, and numbers should not.
• insert into "tablename" (first_column,...last_colu
  mn) values (first_value,...last_value)

20
Updating Records

• The update statement is used to update or change
  records that match a specified criteria. This is
  accomplished by carefully constructing a where
  clause.
• update "tablename" set "columnname" "newvalue"
  ,"nextcolumn" "newvalue2"... where
  "columnname" OPERATOR "value" andor "column"
  OPERATOR "value" optional

21
Deleting Records

• The delete statement is used to delete records or
  rows from the table.
• delete from "tablename"where "columnname"
  OPERATOR "value" andor "column" OPERATOR
  "value" optional
• To delete an entire record/row from a table,
  enter "delete from" followed by the table name,
  followed by the where clause which contains the
  conditions to delete. If you leave off the where
  clause, all records will be deleted.

22
Drop a Table

• The drop table command is used to delete a table
  and all rows in the table.
• To delete an entire table including all of its
  rows, issue the drop table command followed by
  the tablename.
• drop table is different from deleting all of the
  records in the table. Deleting all of the records
  in the table leaves the table including column
  and constraint information. Dropping the table
  removes the table definition as well as all of
  its rows.
• drop table "tablename"