Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Management System Lecture 1 (Subject Information (Subject…
Database Management System Lecture 1
Subject Information
Subject Objectives
To
extend skills
of sound database development practices
To gain
knowledge of principles and techniques
for implementation of database management systems
To understand
index structures, query optimisation
and
transaction management
for design of database management system
Subject Organisation
Lectures
Tutorials/lab
Assignment
Exam
Class materials
Textbooks
LMS
Subject Leaning Guide
News & Announcements
Lecture notes
Lab instructions
Oracle official documents
Assessment & Requirements for passing CSE3DMS
Weekly tutorials/labs
Assignment
Exam
Laboratory attendance
Lab Allocation
Staff
Lecturer
Tutor
SQL Programming
Client/Server Databases & the Oracle11g Relational Database
Types of Database Systems
Client/server database
DBMS server process runs on one workstation, and the database applications run on separate client workstations across the network
Clients computer runs user interface programs and database applications that retrieve and manipulate small amounts of data from database through server
Database system at Server side stores large numbers of records and management that serves to clients and maintain the consistency of databases.
There are 2-tier or 3-tier client/server architecture of DBMS
Organisations generally use a client/server database if the database will have more than 10 simultaneous users
Personal databases (DBMS)
DBMS and database applications run on the same workstation
Utilised primarily for creating single-user database applications
Support small multi-user database applications by storing the database application files on a file server instead of on a single user’s workstation
The Oracle11g Client/Server Database
Oracle11g is the latest release of Oracle Corporation’s relational database
All Oracle server-side and client-side programs use Oracle Net, a utility that enables the network communication between the client and the server
Client-Side Utilities
SQL*Plus
for creating and testing command-line SQL queries and executing PL/SQL procedural programs
Oracle SQL Developer
free integrated development environment that simplifies the development and management of Oracle Database. It offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modelling solution, and a migration platform for moving your 3rd party databases to Oracle
Oracle10g Developer Suite: for developing database applications including the following Developer tools
Forms Builder
for creating custom user applications
Reports Builder
for creating reports for displaying, printing, and distributing summary data
Enterprise Manager
for performing database administration tasks such as creating new user accounts and configuring how the DBMS stores and manages data
Review SQL Programming
What we have learned for SQL
Structured Query Language (SQL)
The standard query language for relational databases
Data definition language (DDL)
Create new database objects
Create database tables using SQL*Plus
Create Synonyms & Copy tables
Creating New Sequences
No need to issue COMMIT command
Sequences are used to contain automated counter values, most commonly used to retain counter values for integer-valued primary keys
E.g. CREATE SEQUENCE <sequence_name> START WITH 1
CURRVAL
1 more item...
NEXTVAL
3 more items...
Pseudo column: DUAL
3 more items...
Grant Sequences
1 more item...
Modify or delete existing objects
Data manipulation language (DML)
Insert, update, delete, and view database data
Define Oracle11g database schemas
Modify and delete database tables using SQL*Plus
Deleting Table Rows
Cannot delete row if it has
child row
– other table use this record
Child row – foreign key is used in another table
Row’s value is foreign key
Cannot delete row if it has child row, Unless first delete the row in which foreign key value exists – delete the enrolment student
TRUNCATE syntax
TRUNCATE TABLE <table_name>
Cannot truncate table with foreign key constraints
Must disable constraints first
DELETE syntax
DROP TABLE <table_name>
Cannot delete table with foreign key constraints
Must disable constraints first
Alter Constraints
ALTER TABLE <table_name>
DISABLE CONSTRAINT <constraint name>
Debug Oracle11g SQL commands and use Oracle Corporation online help resources
View information about your database tables using Oracle11g data dictionary views
Data dictionary
tables that contain information about the structure of the database.
USER: shows the objects in the current user’s schema
ALL: shows both objects in the current user’s schema and objects that the user has privileges to manipulate
E.g. User_tables, all_tables, user_constraints, etc
Data Dictionary Information
ALL_VIEWS
ALL_SEQUENCES
USER_OBJECTS
ALL_SYNONYMS
USER_SOURCE
USER_ERRORS
USER_CONSTRAINTS
Viewing Sequence Information
SELECT sequence_name
FROM user_sequences;
Creating Transactions and Committing New Data
User can
commit
(save) changes
User can
rollback
(discard) changes
Pending transaction: a transaction waiting to be committed or rolled back
Oracle DBMS locks records associated with pending transactions
Other users cannot view or modify locked records
Savepoints
A bookmark that designates the beginning of an individual section of a transaction so that changes can be rolled back to savepoint
Disk Storage, Basic File Structures, and Hashing
Storage of databases
The collection of data must be stored physically on some computer storage medium
Computer storage media forms a storage hierarchy that includes two main categories:
Primary storage. can be operated directly by the computer’s central processing unit (CPU), such as the computer’s main memory and smaller but faster cache memories
Secondary and tertiary storage. Magnetic disks, optical disks (CD-ROMs, DVDs, and other similar storage media), and tapes. Hard-disk drives are classified as secondary storage, whereas removable media such as optical disks and tapes are considered tertiary storage
Data in secondary or tertiary storage cannot be processed directly by the CPU; first it must be copied into primary storage and then processed by the CPU
Records, Blocks, Files, Files of Records
Data stored as magnetised areas on magnetic disk surfaces
A
disk pack
contains several magnetic disks connected to a rotating spindle
Disks are divided into concentric circular, Each circle is called a track
A track is divided into blocks
The block size
B
is fixed for each system during initialization
Typical block sizes range from B=512 bytes to B=4096 bytes
Whole blocks are transferred between disk and main memory for processing
A read-write head moves to the track that contains the block to be transferred
Disk rotation moves the block under the read-write head for reading or writing
A physical disk block (hardware) address consists of
a cylinder number (imaginary collection of tracks of same radius from all recorded surfaces)
the track number or surface number (within the cylinder)
and block number (within track)
Reading or writing a disk block is time consuming because of the seek time
s
and rotational delay
rd
Double buffering can be used to speed up the transfer of contiguous disk blocks
various surfaces in a disk pack are called a cylinder
Records
Fixed and variable length records