Databases and DBMS. What is a Database?

Databases and DBMS

Eric Lew

(MSc, BSc)

SeconSys Inc.

Nov 2003

What is a Database ?

Data (singular: datum)

„

Factual Information

Database

„

Organized body of related information

„

Repository / storage of information

Examples:

„

Grocery list

Data Organization

Grocery list:

„ Apples (2) „ Bread „ Yoghurt „ Paper plates (20) „ Lettuce (2) „ Chicken drumsticks „ Mushrooms „ Cheese „ Spaghetti „ Pork ribs „ Cilantro „ Tomato Sauce „ Plastic forks (20) „ Oranges „ Ice cream „ Plastic knives (20) „ Parsley „ Sour cream „ Tomatoes „ Bacon „ Paper napkins „ Spinach

Data Organization (II)

Grocery list:

Fresh Produce: „ Oranges (1 lb) „ Apples (2 lbs) „ Lettuce (2) „ Spinach „ Mushrooms (white) „ Parsley „ Cilantro „ Tomatoes (plum) Meats: „ Pork ribs (3 lbs) „ Bacon (1 lb) „ Chicken drumsticks (20) Dairy:

„ Yoghurt (low fat) „ Sour cream „ Ice cream „ Cheese (mozarella)

Other:

„ Bread (white sliced) „ Spaghetti „ Tomato Sauce „ Plastic forks (20) „ Plastic knives (20) „ Paper napkins (20) „ Paper plates (20)

Data Models

Conceptual Model

„ What is the data about „ How will the data be used

„ Entity-Relationship (ER) diagram

Logical Model

„ Implementation dependent

ŠNetwork, Hierchical - obsolete ŠRelational - the current standard

ŠObject-oriented - the future?

Physical Model

„ How is the data physically stored and retrieved „ Hardware and software dependent

Relational Data Model

Data organized as a collection of tables Each table represents an entity type

„ Consists of rows and columns

„ Each row contains the data values of one entity

„ Each column represents an attribute (property) of the entity

Technical terms:

„ Table = relation „ Row = tuple, record „ Column = attribute, field „ Domain = cell value

Relational Concepts

Attribute values

„ Data types: character, number, logical, date „ Range constraint; e.g. 1.0 to 9.9

„ Enumeration contraint; e.g. ‘RED’, ‘BLUE’, ‘GREEN’ „ Null value means unknown data

No two rows can be identical

Primary key: attribute(s) which uniquely

identify a row

Relational Database Design

A cell should only have one value

Minimize data redundancy

(reduce human error)

Replace one big table by several small tables

„ Technical term: normalization

Use ‘small’ attributes for primary keys

„ More efficient use of space

Relational Database –

Bad Design

James Cook book1, book2 Software 101 11 Bright Crescent Chet Davis Jennifer Yates Book6 History 202 7 Hilly Lane Jill Aries Bill Gomez book3 Psychology 201 92 Sunlight Rd. Mia Rowe James Cook book1, book2 Software 101 89 Lilac Dr. John Bell Jennifer Yates book6 History 201 117 Rose Drive Alan Gold Jenny Yates book6 History 202 92 Sunlight Rd. Mia Rowe James Cook book4, book5 Database 101 11 Bright Cres. Chet Davis Bill Gomez book3 Psychology 201 675 West Lane Laura Holm Professor Books Course Address Student

Relational Database -

Good Design

Student 117 Rose Drive Alan Gold 16 7 Hilly Lane Jill Aries 15 92 Sunlight Rd. Mia Rowe 14 11 Bright Cres. Chet Davis 13 675 West Lane Laura Holm 12 89 Lilac Dr. John Bell 11 Address Name ID Professor Jennifer Yates 3 Bill Gomez 2 James Cook 1 Prof_Name ID Course 3 History 201 35 3 History 202 34 Database 101 Psychology 201 Software 101 Name 1 33 2 32 1 31 Prof_ID ID StudentCourse 32 14 31 13 34 15 35 16 34 14 33 32 31 Cour_ID 13 12 11 Stu_ID

Relational Concept: Join

Relationship between tables identified by primary keys and foreign keys

„ Primary key (PK) in Professor table is ‘ID’ „ Foreign key (FK) in Course table is ‘Prof_ID’ „ Primary and foreign key must be same data type

Joining tables: compare FK value and PK value

„ Course.Prof_ID = Professor.ID

Relationship types:

„ One-to-one „ One-to-many „ Many-to-many

Relational Database Management System

Separate application from data

„ Several applications can use same data

„ Tables can be added to database incrementally

Multi-user concurrent access to data

Maintenance of data integrity

„ Enforce validation of data „ Back-up and recovery

Transaction management

Structured Query Language

INSERT command

„ One row at a time

„ INSERT INTO Student (ID, Name) VALUES (20, ‘Adam Wright’)

DELETE command

„ DELETE FROM StudentCourse WHERE Stu_ID = 20

UPDATE command

„ UPDATE Course SET Prof_ID = 3 WHERE Name LIKE ‘History%’

SELECT command

„ SELECT * FROM Student WHERE ID > 10

„ SELECT Course.Name, Prof_Name FROM Course, Professor

WHERE Course.Prof_ID = Professor.ID

Stored Procedures

Blocks of SQL commands

„ Implement business rules

„ Reusable – used by multiple applications

Stored in the DBMS (hence the name)

Compiled at time of creation

„ Faster runtime execution „ No syntax errors at run-time

Language varies from one DBMS to another

„ Oracle: PL/SQL

Stored Procedure - Example

CREATE PROCEDURE TransferMoney @acctNum1 CHAR(15), @acctNum2 CHAR(15), @amount FLOAT AS

DECLARE @balance FLOAT BEGIN TRANSACTION

SELECT @balance = Balance FROM Savings WHERE Account = acctNum1 IF (@balance < @amount) THEN

ROLLBACK TRANSACTION RETURN (-1)

END IF

UPDATE Savings SET Balance = Balance - @amount WHERE Account = acctNum1 UPDATE Savings SET Balance = Balance + @amount WHERE Account = acctNum2 COMMIT TRANSACTION

RETURN (0)

Georelational Data Model

Hybrid data model (logical model)

„ Topological data mode (represents spatial data) „ Relational DBMS (represents attribute data)

Geographic data represented by Layers

„ Roads, streams, land cover…

„ Each layer is stored in a separate table

Spatial objects classified by graphical form

„ Points, Lines and Polygons are stored in separate

Georelational Example

Geodatabase

Storage of geo information within DBMS

Versioning

„

Multi-user editing

„

Multiple representations of data

„

Long transactions

Behaviours

„

Validation rules: domains, sub types

Object-Oriented Data Model

Entities represented as objects

„ Object classes (types)

„ Objects have properties (attribues) „ Objects have methods (operations)

Classes and Inheritance

„ Class hierarchy – super and sub classes „ Creation of new classes: Point, Line, Polygon

Operations and Encapsulation

„ Data and operations bundled together

„ Polymorphism: classes Rectangle and Circle can

have same operation CalculateArea

Object-Oriented DBMS

Not widely accepted currently

„

Lack of standard query language

„

Portability issues

„

Not as efficient as Relational DBMS

„

Big name RDBMS have too much

DBMS in an Enterprise

GeoDatabase Sales Database Application Server ArcObjects Sales Report MapInfo Inventory Web Server