DBMS with SQL

profiledesg
lecture_02_s12014.ppt
Home>Computer Science homework help>DBMS with SQL

Welcome to
CIS 2002: DATABASE DESIGN AND IMPLEMENTATION
Semester 1, 2014 – LECTURE 2

*

*

*

10.wmf

COMMONWEALTH OF AUSTRALIA

Copyright Regulations 1969

WARNING

This material has been copied and communicated to you

by or on behalf of The University of Southern

Queensland pursuant to Part VA of the

Copyright Act

1968 (

the Act

).

The material in thi

s communication may be subject to

copyright under the Act. Any further copying or

communication of this material by you may be the subject

of copyright protection under the Act.

Do not remove this notice.

USAGE OF SLIDES

Use of these lecture slides is restricted to teaching staff and students enrolled in this course. All students who use these slides should have acquired the prescribed texts.

These slides are for the personal use of students on the Study Desk only.

Students should not copy slides, allow third parties access to the slides or distribute the slides.

Copyright of these slides vests in the university and, where applicable, Cengage Education

*

*

VOLUME OF WORK

  • In this course, the first few weeks are relatively intense in regards to readings, size of lecture, etc.
  • THIS ALLOWS you to build knowledge and skills quickly giving you many weeks to work on practical problems
  • As the semester progresses, the amount of new material introduced each week declines, HOWEVER…
  • Tutorial / practical work becomes progressively more intense

*

*

WEEKLY ONLINE QUIZ

  • A weekly online quiz will be made available on the Study Desk
  • This will also familiarize you with the process of taking the online test
  • Click on the link
  • Have a practice run after you studied the lecture and tutorials

*

*

ONLINE TEST

  • Released to the USQStudyDesk next week
  • Available until 23h55 on 24 March 2014
  • See Introductory Book for more detail
  • The following material will be examined:

Casteel, chapter 2 & 8

  • This test is worth FIVE PERCENT. Read the materials attentively so you know where to look. It is not necessary to rote learn for this test. Practice is key!

*

*

READINGS – WEEK 2

  • Study Book, Module 2
  • Study Book, modules 9.1(optional)
  • Selected Readings 2.1 and 2.2 (Will be used for next 5/6 weeks)
  • Casteel, chapter 2
  • Casteel, chapter 8

*

*

Lecture 2 Objectives

  • Working with Oracle ISQLPLUS environment
  • Data Analysis and Modelling
  • Data Models
  • Data Model Concepts
  • Entities
  • Attributes
  • Data Associations (Relationships)
  • Cardinality
  • Business rules
  • SQL WHERE Clause
  • ORDER BY Clause

*

*

WORKING WITH ORACLE

  • Two interfaces – Oracle SQL Client Interface and Web (HTML) interface
  • In the labs we will use the Web interface ISQLPLUS
  • Web interface is much more user friendly but has a few disadvantages, e.g. some functionality is lost in formatting output

*

iSQLPLUS (WEB INTERFACE) 1

  • We will use this utility in labs and you can use it from home or anywhere as long as there is a good Internet connection

  • Primary server:

http://orastud-is1.usq.edu.au:5560/isqlplus/workspace.uix

  • Reserve server:

http://oradb-stud2.usq.edu.au:5560/isqlplus

Only use the reserve server if you have problems accessing the primary server. You will need to repeat any work already done in primary server, e.g. running table create scripts.

*

iSQLPLUS 2

  • You will get the ISQLPLUS login screen. Enter your username and password and the following host string if it is not already entered for you:

Primary server: student

Reserve server: student.usq.edu.au

Note: Details about your username and password are contained in Tutorial 1

*

iSQLPLUS 3 (LOGIN SCREEN – RESERVE SERVER)

*

iSQLPLUS 4

  • You will now be taken into the Oracle Web (HTML) interface (see next slide).
  • Be careful while having more than one SQL statement active at one time. When you have completed a statement, remove it (or click clear button) before starting with the next.

*

iSQLPLUS 5

*

iSQLPLUS 6

In the workspace tab, you can:

  • Enter SQL command and click on execute
  • Use <save script> to save OR cut and paste to Notepad ( not Word)
  • To load a script, click <load script> and then select the script file and click <load> in the right hand side.

*

iSQLPLUS 7

  • The <History> tab allows you to retrieve previous SQL statements that were executed.
  • The <Preferences> button at the top allows you to set local (client) preferences – be careful not to change the default formatting options.
  • You can CHANGE PASSWORD from the <Preferences> section.

*

ISQLPLUS 8

  • When you write programs (especially longer programs towards the end of semester) keep a running copy of your work in Notepad. Sometimes, the connection goes down
  • Copying from Word to ISQLPLUS sometimes causes problems, especially with the quotes.

*

ISQLPLUS 9

  • Always click on <logout> icon at the top to finish your session. REMEMBER THIS!! Don’t just close the browser.

*

Data Analysis and Modelling

  • We use the key elements of the Finkelstein approach for data analysis and modelling

  • This approach (also called Information Engineering) is the standard at USQ - with minor adaptations based on industry standards

*

*

Analysis & Modelling - A Tool

  • Analysis – goal is to break a complex requirement into smaller parts to better understand the requirement.
  • Modelling - goal is to try and make a model of the requirement to better understand the requirement.
  • Models are:
  • inexpensive to produce
  • relatively easy/cheap to change
  • excellent communication for structured ideas exchange

*

*

*

Logical Model

  • basis of existing system
  • Focuses on WHAT, not HOW
  • interpret in plain English

*

*

*

5

Data Model in SDLC

  • Used during the data analysis phase of the SDLC BUT can be used as standalone tool or outside the SDLC paradigm
  • Database design (logical and physical)
  • Used as a means of communication with users
  • contains business rules relevant to the data

*

*

*

6

Data Modelling: art or science?

  • Is Data Modelling an art or science ? Can it be done by following a specified set of rules/procedures.

  • You decide as you do it.

*

*

What about Process ?

  • The theory says that good Data Modelling is independent of process.
  • Is this really the case ? Can we create models, even databases without considering how they will be used ?
  • If you are working on a system which already exists, do you think that you will work independent of process ?

*

*

Modelling Methods

  • Many Modelling methods
  • Entity relationship (ER) modelling
  • Natural language Information Analysis Method (NIAM)
  • Unified Modelling Language (UML)

*

*

Our focus

*

7

What is the data model?

  • A graphical representation that is an
  • abstraction of the data (or ‘things’)
  • under investigation

*

*

*

8

Data Model Three level architecture

  • Conceptual Schema – to organise the data requirements
  • Logical Schema – to describe the structure of data, e.g. table and columns
  • Physical Schema – to describe the physical means in storing data, e.g. storage, partitions, CPU

*

*

*

Data Model Concepts

  • Data Entities (Entities)
  • Entity type - classification (e.g. UNIVERSITY)
  • Entity occurrence - individual (e.g. USQ)
  • Data Attributes
  • Data Associations (Relationships)
  • Cardinality
  • Business rules

*

*

*

10

Data Entity

  • Represents some 'thing' that is to be stored for later reference.
  • Always named in the singular e.g. CUSTOMER, ORDER, STUDENT
  • Is any object of interest to the organisation under investigation, any part of the system, or any object about which data can be collected and stored

*

*

*

11

Data Entity (2)

  • Often shortened to ‘entity’
  • Entity refers to the logical representation of data
  • compared with the physical representation on disk or on paper (record)

*

*

*

12

Data Attribute

  • A 'piece' of information that describes a data entity. Referred to as an attribute or a data item.
  • Always uniquely named in the singular.

*

*

Entity: PLAYER

Attributes: player name, position, nationality

*

13

Data Relationship or Association

  • Indicates that a relationship exists between two entities.
  • May be called association or relationship
  • Two entity occurrences are related if a change in one makes a significance difference to the other,

*

*

eg. The relationship between jobs and workers

a job has a worker/s associated with it

a worker/s have jobs associated with them

the removal of one effects the other

*

14

The Data Model

  • Is built from:
  • Entities
  • Attributes
  • Associations

*

*

*

The Data Model - Illustrated

*

*

ENTITY

A

ENTITY

B

*

Cardinality - One to One

A department has one manager and a manager

manages one department

*

*

DEPT

MANAGER

*

Cardinality - One to Many

*

*

ORDER

INVOICE

An order generates (one or) many invoices and an invoice is generated

against one order

*

Cardinality - Many to Many

*

*

PRODUCT

PART

A product contains (one or) many parts and a part is used in (one or) many

products

*

Cardinality

  • Tells how many occurrences of one entity type are linked to occurrences of the other entity type
  • Ask: "What is the maximum number of instances in the second entity that this first entity can be related to?" Then repeat in the reverse order.

*

*

*

Cardinality

  • One to one

  • One to Many

  • Many to many

*

*

*

Business Rules or Optionality

  • Optional

  • Mandatory

  • Optional becoming

mandatory
(not used extensively in this course)

*

*

*

Relationships

*

*

A relationship is a logical connection between two entities. It defines

and qualifies the relationship between the entities.

Relationship Degree (or cardinality)

SKILL

EMPLOYEE

SKILL

EMPLOYEE

One or many

One

Relationship Nature

Optional

Mandatory

An employee MAY have (one or) many skills. A skill MUST be assigned to

one (and only one) employee.

*

Relationships

*

*

SKILL

JOB

EMPLOYEE

Entities

Relationships

An employee MAY be allocated to many jobs. A job MUST be allocated to (one or)

many employees. A job requires (one or) many skills. A skill MAY relate to (one or)

Many jobs. An employee MUST have (one or) many skills. A skill may be assigned

to (one or) many employees

*

Relational Database Design steps

*

*

Data

Requirements

Data

Model

Candidate

Relations

(or just relations)

Normalized

relations

Tables

in a relational

database

Supporting

Physical

Implementation

e.g. indexes

1. DATA REQUIREMENTS

  • An employee MUST be assigned to one (and only one) department. For employees, we store empid and employye name

  • A department MAY have (one or) many employees. We store deptno, department name and location

*

*

2. DATA MODEL

  • CONSIDER THE FOLLOWING ERD

*

*

EMPLOYEE

An employee MUST be assigned to one (and only one) department

A department MAY have (one or) many employees

Notes: MAY implies that a department need not have any employees

If the department only has one employee, the rule is satisfied. It has the

potential to have many employees

DEPARTMENT

3. CREATE A RELATION

  • Now, convert the entity and (where applicable) it’s relationships, into a relation

  • If an entity is on the MANY side of a relationship, you will need to add a foreign key to the attributes
  • Another name for a set of relations is an ENTITY LIST

*

*

3 and 4. RELATIONS

  • Consider the relation:

EMPLOYEE(empid#,empname,deptid#)

The relation is named EMPLOYEE and has THREE attributes.

empid# is the primary key

deptid# is a foreign key pointing to DEPARTMENT

NOTE how keys are represented. These relations are already normalized.

*

*

RELATIONS 2

  • The relation DEPARTMENT becomes:

DEPARTMENT(deptno#, department name, location)

*

*

SQL – CASTEEL CHAPTER 8

*

*

WHERE Clause Syntax

  • A WHERE clause is used to retrieve rows based on a stated condition
  • Requires:
  • Column name
  • Comparison operator
  • Value or column for comparison
  • Values are case sensitive

*

WHERE Clause Example

  • List WHERE clause after FROM clause
  • Enclose nonnumeric data in single quotes

*

Comparison Operators

Indicate how the data should relate to the given search value

*

Arithmetic Comparison Operators

*

Other Comparison Operators

*

BETWEEN…AND Operator

Finds values in a specified range

*

IN Operator

  • Returns records that match a value in a specified list
  • List must be in parentheses
  • Values are separated by commas

*

IN Operator Example

*

LIKE Operator

  • Performs pattern searches
  • Used with wildcard characters:
  • Underscore (_) for exactly one character in the indicated position
  • Percent sign (%) represents any number of characters

*

LIKE Operator Example

*

Logical Operators

  • Used to combine conditions
  • Evaluated in order of NOT, AND, OR:
  • NOT – reverses meaning
  • AND – both conditions must be TRUE
  • OR – at least one condition must be TRUE

*

AND Logical Operator Example

*

OR Logical Operator Example

*

Multiple Logical Operators

Resolved in order of NOT, AND, OR

*

Multiple Logical Operators

Use parentheses to override the order of evaluation

*

Resolving Multiple Types of Operators

Arithmetic operators

Comparison operators

Logical operators

*

Treatment of NULL Values

  • Absence of data
  • Requires use of IS NULL operator

*

Treatment of NULL Values (continued)

  • A common error is using = NULL which does not raise an Oracle error but it also does not return any rows

*

ORDER BY Clause Syntax

  • The ORDER BY clause presents data in sorted order
  • Ascending order is default
  • Use DESC keyword to override column default
  • 255 columns maximum

*

ORDER BY Clause Syntax
Sort Sequence

  • In ascending order, values will be listed in the following sequence:
  • Numeric values
  • Character values
  • NULL values
  • In descending order, sequence is reversed

*

ORDER BY Example

*

ORDER BY Can Reference
Column Position

*

COMMONWEALTH OF AUSTRALIA

Copyright Regulations 1969

WARNING

This material has been copied and communicated to you

by or on behalf of The University of Southern

Queensland pursuant to Part VA of the

Copyright Act

1968 (

the Act

).

The material in thi

s communication may be subject to

copyright under the Act. Any further copying or

communication of this material by you may be the subject

of copyright protection under the Act.

Do not remove this notice.