COMP 2005 Personal Database Tools

1
COMP 2005Personal Database Tools
2
Administrivia

• Lecturer Professor Jon Patrick
• jonpat_at_cs.usyd.edu.au
• Format 26 x 1 hour lectures
• 12 x 1 hour workshops
• Tutors to be announced
• Web www.ug.cs.usyd.edu.au/cs2/dbs

3
Textbook

• Data Modelling Essentials, Graeme Simsion.
• available now in the Co-Op bookshop
• Sybase SQL Anywhere, CD-ROM

4
References

• Fundamentals of database systems
• Ramez Elmasri Shamkant B. Navathe
• Database systems principles, design, and
  implementation
• Catherine M. Ricardo.
• An introduction to database systems
• C. J. Date.
• JoeCelkos SQL for Smarties, Joe Celko

5
Assessment

• Workshop participation 10
• Exercises using Sybase - SQL AnywhereTutorials
  are on-linewritten exercisesgroup work
• Assignments 30
• Exam 60

6
Database Technology Week 1.1
7
Learning Objectives

• To understand the nature of a DBMS
• To understand the elements of an operational
  database
• To understand the basic manipulation of records
• To learn the some basic SQL statements
• To learn enough to get started in the Lab

8
What is a DBMS?

• A database is a system for storing data
• A Database Management System is a system for
  managing the storage of data(database)

9
SQL

• An ANSI standard
• Determined by big manufacturers and some
  academics
• Now we have SQL-3

10
SQL Anywhere - An Introduction

• A Sybase proprietary version of SQL
• Structured Query Language - SQL

11
SQL Anywhere - Getting Started

• Three Working Components
• Database - get it opened/running
• ISQL - Interactive SQL/ data retrieval process
• SQL Central - logon as an administrator

12
SQL Anywhere - A database

• The Workshops will use a demonstration database
• SADEMO is a business sales application
• It has to be started/opened

13
SQL Anywhere - ISQL

• Interactive retrieval system
• 4 window panes for operation
• Interactive SQL - pop-up menus
• Data - answers to queries are shown
• ISQL - logon as a user to make retrievals
• Statistics - general information
• Command - commands are typed in and batch
  processed, that is click on execute.

14
SQL Anywhere - SQL Central

• Supports DBA functions
• Create database tables
• streamline processing
• create user accounts
• all modules require a login procedure
• USERID - dba
• PASSWORD - sql

15
SQL Anywhere Manual

• Manual is on-line - select from the pop-up menu
• read the section in the manual and work through
  the examples on the demonstration database
• try the examples in the workshop tasks
• build up confidence at using the software -
  become expert in using it

16
Some primitive SQL
17
Genealogical Tree Tables

• Person(ID, fname, lname, sex, DOB)
• Partner(ID1, ID2, DOP)
• Child-Parent(IDP1, IDP2,IDChild)

18
Some primitive SQL

• SELECT id FROM person
• SELECT id2 FROM partner where id1jon
• SELECT id1 FROM partner where id2jan
• SELECT FROM child-parent where id1jon AND
  id2jan
• SELECT ID from person where DOBgt1-1-77

19
DATA MANIPULATION

• INSERT a record
• DELETE a record
• UPDATE a record

20
INSERT A RECORD

• INSERT places a record in the database in a
  position that is correctly sequenced on the
  primary key
• the positioning may be physical or logical
  depending on the database management system
  software

21
DELETE A RECORD

• Delete removes the nominated record from the
  database
• it may perform a logical or physical delete
• the delete may fail if the record has some
  dependency constraint

22
UPDATE A RECORD

• Update changes the nominated contents of the
  attribute of a record
• it may fail if it does not conform to domain
  constraints
• it overwrites the previous contents

23
Data Modelling Techniques Week 1.2
24
Learning Objectives

• To understand the tabular storage of data
• To understand keys and their usage

25
TABULAR STORAGE
26
KEYS

• Primary Key - Unique identifier
• Candidate Key
• Alternate Key
• Foreign Key
• Concatenated Key
• Attribute