Transcript Week 13 - Pravin Shetty > Resume

It’s Week 13 !
UNIT REVIEW
CSE9999 Semester 1 2004 Review / 1
Week 13 Lecture
There are a couple of items to address prior to commencing
the countdown.
1. Your assignment database.
The contents of the tables could reflect a current and a
history condition. This is similar to the concept of a
data warehouse.
One of the significant advantages of any database is its
capability of providing data for analyses - such as the
variations in different models or makes of buses
CSE9999 Semester 1 2004 Review / 2
Week 13 Lecture
This indicates that if database data is to be analysed in
‘dimension’- meaning time, models, makes, perhaps routes,
there must be some design component which addresses
the holding of ‘history’ data.
We could have created additional tables for data which is
replaced by ‘new’ data - or we could have copied the
contents of existing tables to a ‘secondary state’ - or would
that be satisfactory ?
CSE9999 Semester 1 2004 Review / 3
Week 13 Lecture
2. The outcomes of this unit
* you should have some appreciation of the ‘complex’
nature of data especially when it is being processed á la
database and computer-based processes
* you should have renewed or initiated your interest in a
DBMS (or perhaps 2 DBMS’s)
* you should be aware of the communication and interpersonal relationships necessary for working with other
people - particularly in groups.
CSE9999 Semester 1 2004 Review / 4
Week 13 Lecture
You should have well based concepts of the amount of
time, effort and skills resources required in the logical
planning, development , quality control, implementation and
final translation to an operational database
And you should have some awareness of the ‘background’
activity and planning which is necessary for performance.
And the final thought - there is ALWAYS room for
improvement and change - but there is always a cost
involved AND changes must be managed, tested and then
applied.
CSE9999 Semester 1 2004 Review / 5
Unit Review
CSE9999 Semester 1 2004 Review / 6
CSE9999 Review
• Data, Information, Need for Information
• Survivors, Potential Success, Success Organisations
• Database, Database Management Systems
• Commercial Models
• Business Activities, Functions, Processes
• Need for Information to Manage Resources
CSE9999 Semester 1 2004 Review / 7
CSE9999 Review
• Need for Information to be
– Accurate, Representative, Timely, Adequately Based
– Protected against Loss, Corruption, Unauthorised
Access
• Communication capabilities
CSE9999 Semester 1 2004 Review / 8
CSE9999 Review
• Commercial Models - Hierarchical, Network, Relational
• Designing a database
–
–
–
–
User requirements - current and future
Information outlines
Sources of data
Processes to be applied
CSE9999 Semester 1 2004 Review / 9
CSE9999 Review
Designing a database
– Restrictions on data occurrences, either singly or
when associated with other data
– User Rules for existence and operations on data
– Dependencies of data
– Life of data
– Value or worth of data
CSE9999 Semester 1 2004 Review / 10
CSE9999 Review
• Determining what ‘data sets’ exist (entities)
• Determining the relationships between these sets
– Optional
– Mandatory
– 1 to 1 (1:1) , 1 to Many (1:M)
or Many to Many (M:N)
– Unary, binary or ternary relationships
– Special conditions for existence of data
CSE9999 Semester 1 2004 Review / 11
CSE9999 Review
• Expanding the Entities to Attributes
– Conditions of existence for attributes
– Dependencies of attributes on a determinant
– Primary Key (and its characteristics)
CSE9999 Semester 1 2004 Review / 12
CSE9999 Review
• Primary - Foreign Key occurrences (and referred to the
Entity Relationships)
• Performance of the relationships on Insert, Update,
Modification of the Primary and Foreign keys
• Normalisation - dependencies of attributes on the Primary
Key. The objective is to avoid update, insert and delete
anomalies.
CSE9999 Semester 1 2004 Review / 13
CSE9999 Review
• Special conditions which could restrict some data
occurrences
• Long term storage requirements
• Self checking features ?
• Triggers and procedures - PL/SQL
CSE9999 Semester 1 2004 Review / 14
CSE9999 Review
• Access considerations - File Organisation and Access
Methods
• Indexing - Changing Structure - Secondary Indexing
• Performance Requirements
• Logical Path Access evaluation - Data Base and
Equipment Loading
CSE9999 Semester 1 2004 Review / 15
CSE9999 Review
• Growth, Volatility of Database - effect on Indexes
• Transaction Processing - Transaction Manager, Scheduler,
Recovery Manager
• Stages of access to the physical database
• Effect of power, software and hardware interruptions
CSE9999 Semester 1 2004 Review / 16
CSE9999 Review
• Checkpoint, Logging facilities
• Back Up processes
• Restart / Recovery outline
• Concurrency Aspects - multi user (transaction) control
• Time stamping, Locks
CSE9999 Semester 1 2004 Review / 17
CSE9999 Review
• Adverse effects of no or poor concurrency - lost updates,
inconsistent analysis
• Integrity Considerations - Entity, Referential, Domain,
Data, Cardinality, Processing
• Security of
- hardware, communications, data
access limitations, authorisations
CSE9999 Semester 1 2004 Review / 18
CSE9999 Review
• Use of Data Dictionary - intelligence about data
• Optimisation
-
query execution plan
• Data base administration
• Features of a database management system
CSE9999 Semester 1 2004 Review / 19
CSE9999 Review
• The increasing use of the World Wide Web for data access
and Information production
• The growing use of complex (or intelligent) software
• Non-structured data - Management of storage and access
• Portals
CSE9999 Semester 1 2004 Review / 20
Applications
Integrated Systems:
Customer Resource Management
Supply Chain Management
Unstructured Data Management
CSE9999 Semester 1 2004 Review / 21
CSE9999 Review
Associated Topics
Distributed Database
Data Warehousing
Data Mining
CSE9999 Semester 1 2004 Review / 22
Introduction to SQL
SQL - what is it ? What is its purpose ?
The commands : Create (object), delete, drop,
insert, update,
Querying with SQL
– Select (list of attributes)
– From (named tables)
– Where (conditions for selection, including nested
queries)
– Group By (selection of aggregations
– Having (condition for Grouping)
– Order by (viewing sequence)
CSE9999 Semester 1 2004 Review / 23
Introduction to SQL
Operators :
> < = !=
in, between
exists, not exists
any, not any
AND OR
NOT
Avg, max, min, std, sum - set functions
Correlation - multiple table query
CSE9999 Semester 1 2004 Review / 24
More on Tables
Attributes which make up a row in a table are always scalar
or atomic data types
They are complete in themselves and cannot be broken
into smaller parts
Attributes can have constraints placed on them when
– the table is created
– or when the table is altered
These constraints are part of the database
Unlike spreadsheet cells, an attribute set of values can
only hold data.
CSE9999 Semester 1 2004 Review / 25
Schemas and Tables
A Schema : is the data, the operators and the rules of the
defined database
Tables can be permanent (base tables) or virtual (views)
Any operation in SQL will return a result table
CSE9999 Semester 1 2004 Review / 26
More Terms
Scalar Values : This is a value which is not an array - one
dimension. 1 is a scalar value, 1,4 is not -it’s an array.
Literals : This is a constant which is referred to by its value.
Literal 3 is ‘the integer data type having the value 3’
Column Expression : This is a calculation which will return
a value for any data item in that attribute - also known as a
‘derived value’.
CSE9999 Semester 1 2004 Review / 27
A Transaction
A transaction is a sequence of SQL statements which Oracle
treats as a single unit
The set of changes is made permanent with the Commit
statement
Part or all of a transaction can be undone with the Rollback
statement
A transaction starts with the execution of the first SQL
statement in the transaction and ends with either the Commit
or Rollback statement
CSE9999 Semester 1 2004 Review / 28
A Transaction
Oracle guarantees that a transaction has statement-level
read consistency (the data stays the same while Oracle is
gathering and returning it)
If a transaction has multiple queries, then each query is
consistent, but not with each other
Transaction-level read consistency can be achieved with
the Set Transaction Read Only - (queries only)
CSE9999 Semester 1 2004 Review / 29
Some Guidelines
1. The name allocated to a table, known as the table name,
must be unique in the schema.
2. The attribute names used in a table must be unique in that
table
3. It is good practice NOT to have a table name repeated as
an attribute name in the same table (e.g. a table named
Personnel could have an attribute ‘employee’ - not an
attribute named ‘personnel’.)
CSE9999 Semester 1 2004 Review / 30
Some Guidelines
4. It is good practice to use the same name for an attribute
which occurs in other tables.
5. It is good practice to have related attributes in natural order
- e.g. Person’s name, title, address, salary rather than
address, person’s name, title, salary.
CSE9999 Semester 1 2004 Review / 31
Some Thoughts about Keys
A KEY is an attribute (or a set of attributes) in a table
which uniquely identifies each row in that table
There are several types of keys :
1. A candidate key - this is one of the ‘possible’ keys in a
table. A an example, a motor car has a Registered Number,
and also a Vehicle identification Number (VIN).
2. A surrogate key is a key with no meaning in itself. They
are normally generated by the database system using
some form of number generator. Users rarely see these
values - except in Microsoft Access
CSE9999 Semester 1 2004 Review / 32
Some Thoughts about Keys
Intelligent Keys : have a meaning. Longitude and Latitude
of an object (say a building) uniquely identifies a building
and also nominates where it is.
Super Key : This is normally a key with too many attributes
in it. Some of the columns could be removed and a key
would still exist. A good example is a key which contains
‘old value/new value’ such as ‘old telephone number/new
telephone number’ when numbers are upgrades or altered.
A Primary Key : The unique, not null expression of the
access to each row in a database table - normally
constrained by a domain set.
CSE9999 Semester 1 2004 Review / 33
Embedded SQL
In commercial processing, SQL normally is between the
database and a host programming language, passing data
in either a forward or backward direction ( accepting new
data, or returning results of queries.)
The host program communicates with the ‘outside world’
There are also PC tools which are designed to use SQL PowerBuilder, Designer2000, Developer2000, Delphi are
examples.
CSE9999 Semester 1 2004 Review / 34
New SQL Commands and Features
The OLAP functions proposed for SQL-99 are
ceiling
percentile_cont regr_slope
corr
percent_rabk
regr_sxx
covar_pop
power
regr_sxy
covar_samp
range
regr_syy
cume_dist
rank
row_number
dense_rank
regr_avg
sqrt
exp
regr-avgx
stddev_pop
floor
regr_agvy
stddev_samp
ln
regr_count
car_pop
moving_avg
regr_intercept var_samp
moving_sum
regr_r2
CSE9999 Semester 1 2004 Review / 35
New SQL Commands and Features
The principal statistical functions are ;
correlation
cumulative distribution
percentile
standard deviation
covariance
ranking
linear regression
variance
CSE9999 Semester 1 2004 Review / 36
Some Commands You Have Met
• Create : table, view, index, sequence (also create as select
..)
• Drop : table, view, index, sequence
• Insert : Adds a single row to an existing table
• Delete : Deletes a row or rows or all contents from an
existing table
• Update : Updates a row or rows
• SqlLoad : Loads bulk data from an external file to the
current database table (sqlldr80 for Personal Oracle)
CSE9999 Semester 1 2004 Review / 37
Some Commands You Have Met
• Alter : With care, provides alteration to some of the table
attributes and properties
• Copy : Copies data from 1 database to a named database
• Export : Copies data from a named table in a database to
the Operating system level
• Import : Copies data from the Operating system level to a
named database
• Union : Combines 2 or more tables into 1 table
CSE9999 Semester 1 2004 Review / 38
Operators and Predicates
• Arithmetic :
+ - / * **
• Logical
And
:
Or
• Comparison Predicates :
Not
= > < <=
>= <> != ^=
• Like Predicate : Pattern matching or Not matching
• Between Predicate : Numeric valued constraints
• In Predicate : String or numeric lists
CSE9999 Semester 1 2004 Review / 39
More Predicates
• Any
• All
• Exists
CSE9999 Semester 1 2004 Review / 40
Constraints
• Null
(and Not null)
• Unique
• Check (attribute property limit - In, Between )
‘Set’ Functions
Count, Sum, Average, Minimum, Maximum
and don’t forget the ‘additional’ functions in SQL99
CSE9999 Semester 1 2004 Review / 41
Data Types
• Varchar2(number) e.g. varchar2(15)
• Number(p,s) e.g. number (10,2) 8 whole number values,
2 decimal values)
• Date (including hrs, Min, Sec and decimals)
• Blob - Binary large object
• Clob - Compressed binary large object
• Other datatype supported by Microsoft - currency, long and
short integer, text, various temporal options
CSE9999 Semester 1 2004 Review / 42
An Application Server
Business
Intelligence
Content
Management
Application
Integration
Application
Server
Web Services
Wireless
Portal
Java J2EE
An interconnection of all necessary Middleware
CSE9999 Semester 1 2004 Review / 43
The Examination
CSE9999 Semester 1 2004 Review / 44
Examination Hints
• Make sure of the TIME , DATE and LOCATION
• Misreading IS NOT accepted for Supplementary or
Deferred Examination application.
CSE9999 Semester 1 2004 Review / 45
Examination Hints
• Make sure you have your I.D. Card
• Make sure you have the printout from the Examinations
Centre Web Page - and sit at the nominated desk
• In this unit, NO BOOKS, NOTES, CALCULATOR
• MOBILE PHONES must be turned OFF
CSE9999 Semester 1 2004 Review / 46
Examination Hints
• Use the 10 minutes ‘reading time’ to familiarise yourself
with the instructions, layout and questions.
• Start writing when advised by the Supervisor - don’t lose
marks unnecessarily
• Manage your time carefully
CSE9999 Semester 1 2004 Review / 47
Examination Hints
• If you are stuck on a question, MOVE ONTO ANOTHER
ONE
• In answering questions, use
• diagrams
• text
• notes
• point form - whichever you feel best suits
(Not Part A)
CSE9999 Semester 1 2004 Review / 48
Examination Hints
Exam paper presented as
Part A
Part B
Part C
Only answer the number of questions required.
CSE9999 Semester 1 2004 Review / 49
Some Examination Paper Details
• There are 3 sections:
– Section A - Contains 30 multiple choice questions
Total Marks 30
CSE9999 Semester 1 2004 Review / 50
Some Examination Paper Details
– Section B - Contains 12 short answer questions
8 only are to be answered
Contains a mixture of
multipart questions, (generally 3)
and some short answer questions.
Each question is worth 5 marks. Total Marks 40
CSE9999 Semester 1 2004 Review / 51
Some Examination Paper Details
- Section C contains 5 questions. Answer 2 only
The questions cover aspects of Databases such as
Business Rules
An Interpretation of an Entity Relationship Diagram
Constraints
Terms : Domains, Foreign Keys, Integrity rules
And there is a question of your impressions of Large,
Real Time Systems which operate in the Business
Environment
Each question is worth 15 marks
Total for Section C
30 Marks
CSE9999 Semester 1 2004 Review / 52
Examination Hints
• DO NOT WRITE after the Supervisor has given the
termination notice - you could lose marks
• Make sure your I.D. and Name are on all materials handed
up
• Hand up all required materials
• Leave the examination centre QUIETLY
CSE9999 Semester 1 2004 Review / 53
Examination Hints
If you cannot attend the examination, notify your Faculty
Office and make an application for Special Consideration
WITH SOME DOCUMENTATION -- e.g. medical certificate,
statutory declaration .....
CSE9999 Semester 1 2004 Review / 54
And that’s it for Semester 1, 2003
Best wishes for all your examinations
and for the successful completion of
your degree.
If we do meet again, why we shall smile
If not, why then this parting was well made.
CSE9999 Semester 1 2004 Review / 55