Distributed Databases - PowerPoint PPT Presentation

Loading...

PPT – Distributed Databases PowerPoint presentation | free to download - id: 56f1d4-ODViN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Distributed Databases

Description:

Title: Distributed Databases Author: Peeter J. Kirs Last modified by: Peeter J. Kirs Created Date: 11/11/2000 1:41:50 PM Document presentation format – PowerPoint PPT presentation

Number of Views:3
Avg rating:3.0/5.0
Date added: 4 November 2019
Slides: 35
Provided by: Peeter6
Learn more at: http://pkirs.utep.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Distributed Databases


1
More SQL
(With a little more on Database Design)
2
  • Lets Start with some simple single table queries
  • Assume the following Table
  • With the following data

3
  • Group Functions are those which perform an
    operation on a column from a single table
  • One of the simplest is the count() function
  • Count() can also be applied to specific columns

?? How ???
(I have changed the data in the tables)
4
  • We can also find the minimum column values
  • Or the Maximum column values

5
  • We can also sum or average across tables

What if we want each students average grade ???
  • We could try and include student name

6
  • We need to rewrite our command so that we get
    multiple lines of output
  • Group Function Summary

Group Function
Usage
SUM (column)
Find column sum (NULL Values ignored)
AVG (column)
Find column Average (NULL Values ignored)
MAX (column)
Find MAX column value (NULL Values ignored)
MIN (column)
Find MIN column value (NULL Values ignored)
COUNT (column)
Count the number values in a column (NULL Values
ignored)
7
  • Another useful function is SYSDATE, which returns
    the present date or time

(DUAL is a table owned by user SYS and available
to all users)
?? How is that useful ???
8
  • We can use it in calculations. For example, to
    calculate each of our students age

That makes no sense !!!
  • Actually, it does
  • Those are our student ages in DAYS

9
  • To find out our student ages in years
  • Notice we have cleaned-up our output a little

(More on that in a little while)
10
  • There are a number of Date Arithmetic procedures
    that can be applied

(Remember I made these slides on 03/07/2003)
11
  • There are also a number of Date functions

(The output has been formatted to make it fit the
slide)
SQLgt select months_between(sysdate,(to_date('10/12
/2002','MM-DD-YYYY'))) from dual MONTHS_BETWEEN(
SYSDATE,(TO_DATE('10/12/2002','MM-DD-YYYY')))
4.85797006 SQLgt select
add_months(sysdate,8) from dual ADD_MONTH


07-NOV-03

SQLgt select next_day(sysdate,'MON') from
dual NEXT DAY(SYSDATE,MON)

10-MAR-03
SQLgt select
last_day(sysdate) from dual LAST_DAY(

31-MAR-03
SQLgt select
round(to_date('10/12/2002','MM-DD-YYYY'),'Year')
from dual ROUND(TO_
01-JAN-03

SQLgt select trunc(sysdate,'month')
from dual TRUNC(SYS 01-MAR-03


12
  • Lets go over a little more on formatting output.
    Consider
  • There are a number of commands we need to
    consider individually

13
upper(trim(firstname))
upper(trim(lastname))
(as well as)
  • Put whatever string is passed into upper case
  • Remove all leading and trailing spaces from the
    string passed

upper(trim(Dashiell ))
(Stored on a field of 20 characters)
(Returned as)
upper(Dashiell)
(Returned as)
DASHIELL
14
  • The notation

Is a concatenation operator
(it will join two strings together as one)
upper(trim(firstname))
upper(trim(lastname))

HAMMETT
DASHIELL HAMMETT
DASHIELL
round((sysdate DOB)/365.25,2)
(to two decimal pts. of precision)
319113.1930
- 311600.015 7513.1915/365.25 20.56997
20.57
15
to_char(DOB, fmMonth DD, YYYY)
  • Our old function

(Remember?)
August 12, 1982
  • A date format mask

(the fm is used to remove unnecessary spaces or
zeros)
  • There are a large number of numeric and date/time
    formats available

(Which we are NOT going to go over here)
16
  • Grouping Data
  • Rows in a table can be divided into different
    groups and treated separately
  • A HAVING clause, similar to a WHERE clause, can
    be used in the GROUP BY clause

17
  • Grouping Data
  • Grouping works only on segregated groups of data
  • We need to explicitly state how we wish to group

18
  • Subqueries
  • Suppose we wished to get a list of all students
    who toke DATABASE in Spring 2003 and received an
    A
  • As we know, the result is the product of all of
    the tables

(Remember our discussion on Query Optimization
???)
19
  • Subqueries
  • Subqueries
  • Each of the tables contain the following data
  • The product of the tables is

17 44 7 6 3 15,708 Rows
and 3 3 8 3 4 21 Columns
For a total of 15708 21 (43 48 74 28
27)
15708 21 220 72,570,960 Bytes
(And this is a simple example)
20
  • Subqueries
  • If we used the subquery
  • We would save a lot of time and space

21
  • Subqueries
  • Before going further, however, lets review some
    subquery operators

Operator
Use
IN
Equal to any of the values in the list
ALL
Compare the given values to EVERY value in
returned by the subquery
ANY
Compare the given values to EACH value in
returned by the subquery
  • There are also a number of meanings for subquery
    operators when used in conjunction with the
    standard relational operators

Operator
Use
lt ANY
Less than the Maximum Value
ANY
Similar to IN
gt ANY
More than the minimum value
gt ALL
More than the Maximum value
lt ALL
Less than the Minimum value
22
  • Subqueries
  • Now lets analyze the results of our subquery,
    starting with the innermost queries

( Select courseID from course where coursename
Database)
Returns a 4-byte Integer Value
(CourseID 100)
( Select SemID from Semester where Semname
Spring 2003)
Returns a 4-byte Integer Value
(SemID 102)
  • Remember, these subqueries are nested in the
    query

select classid from class where courseid IN (
select courseid from course where coursename
'Database ) and semester ( select semid from
semester where semname 'Spring 2003 ) )
23
  • Subqueries
  • Our select will now choose only those records
    meeting the restrictions

(There is only 1)
  • And only the classid (5004) is returned

24
  • Subqueries
  • The next inner subquery produces a list of
    sudents in class 5004 AND received an A in the
    class

(Only the StudentID list is returned)
25
  • Subqueries
  • Our outermost query produces the list of student
    names based on the list returned from the
    subquery on class

StudentName
Abdul-Jabbar, Kareem
Lopez, Jennifer
26
  • Subqueries

??? How Much of a RAM/Storage Savings is there ???
  • We only work with single tables
  • The largest table is Class
  • 44 Rows
  • 3 Columns/Attributes/Fields
  • 48 Bytes per record
  • 2112 total Bytes
  • Which is the greatest amount of RAM required at
    any time

27
  • Oracle Objects
  • We have already seen some Oracle Objects
  • Tables
  • Views
  • There are some others
  • Sequence
  • Synonym
  • Index

28
  • Sequence
  • Sequences can be created for autonumbering of
    records
  • In this case, just as with our views and
    constraints, we have added an object to our
    repository called class_classid_seq

29
  • Sequence
  • The next time that we add a class, we can have it
    autonumbered

30
  • Sequence
  • Some of the options available include

Option
Meaning
INCREMENT BY n
The increment value for number generation is n
START WITH s
Start incrementing with the number s
MAXVALUE x
The maximum value allowed
NOMAXVALUE
1027 1,000,000,000,000,000,000,000,000,000
MINVALUE m
The minimum value allowed
NOMINVALUE
1 if a ascending sequence and -1026 if descending
CYCLE
Sequence continues after reaching Max Value
NOCYCLE
No continuation after reaching Max Value
CACHE c
Oracle generates c numbers in advance and stores
them in advance for improved system performance
NOCACHE
The system does not generate numbers in advance
31
  • Synonym
  • Sometimes, object names can become very long
    (especially we follow standard naming conventions)
  • Synonyms are added to the repository to shorten
    and clarify names

32
  • Indices
  • In order to speed processing, an index can be
    created
  • When searching for a record, Oracle uses the
    index instead of scanning the entire database

(Implicit Indices are created when Primary Keys
or Unique constraints are established)
33
  • Locking Records
  • When a user enters a select command, the rows
    selected are not locked
  • If a user wants to view AND lock the rows
  • The NOWAIT clause tells any other users accessing
    the record that it is locked

34
(No Transcript)
About PowerShow.com