Database Management Systems (Comp 3753 X1) 1 Who I am - - PowerPoint PPT Presentation

Database Management Systems

(Comp 3753 X1)

1

Who I am…

• Instructor: Darcy Benoit
• Office: Carnegie 316
• Phone: 585-1390
• Email: darcy.benoit@acadiau.ca (text only

please, include [3753] in subject)

• Office Hours: Check the webpage

3753 2

Course Information

• Webpage: http://cs.acadiau.ca/~dbenoit/

3753/

3753 3

Textbook

Recommended for Course Material:

• Elmasri/Navathe Fundamentals of Database Systems

(Fifth Edition), Addison-Wesley Publishing Company.

Note: Some previous students didn’t think that the book was overly useful. Others thought that it was the best book they ever

• bought. You may be able to get by without

the book, or with the fourth edition.

3753 4

Proposed Marking Scheme

• If you fail the exam you will fail the course

(regardless of the marking scheme). Group Project

• Parts 1-3 worth 6%, 2% & 4% = 12%)
• Part 4 (programming) worth 10%

22% Individual Assignments (2 x 4%) 8% Midterm Exam 20% 20% Final Exam 50%

3753 5

Assignment Info

• Due dates listed on the web site are final.
• Late Assignments -- 15% per day penalty.

– Late assignments must be dated and signed, not shoved under my door. – Assignments more than 3 days late may not receive a mark. – Arrangements may be made for those with legitimate reasons. Documentation is

• essential. As much warning as possible is

expected.

3753 6

Assignment Hand In

• Instructions for each assignment will be

located on the web only.

– You are responsible for finding out when assignments are due.

• Always keep a backup copy of your

assignments.

3753 7

Group Work

• You need to choose group members soon.
• Make sure that they are people you can

work with – the groups will stay the same all term.

• Groups can be 1-3 people
• Groups of 1 will be marked the same as

groups of 3.

3753 8

Academic Dishonesty

• Penalties are harsh:

– See the webpage.

• Group work means that all members of the

group should work on each part of the project, not individual members doing individual parts.

• Individual assignments are not to be done

as groups.

3753 9

Slides

• May be available at class time.
• Corrections made in class may not

propagate to the online slides, so take notes.

• Slides and podcasts are not meant to

replace lectures – everything covered in class is fair game.

3753 10

Course Objectives

Upon course completion, you should

• understand the basic concepts of DBMSs
• be able to proceed from a set of requirements

to the development of application programs to access a relational database

• be able to formulate queries in SQL
• be able to write application programs which

access a database

• be familiar enough with IBM’s DB2 to take the

DB2 Certified User exam (with some extra work on your part)

3753 11

Course Material

• 1. Basic Concepts
• 2. Conceptual Modeling

– Entity-Relationship Model

• 3. Relational Model
• 4. Schema Design

– Mapping from E-R to Relational – Normalization

3753 12

Course Material (cont’d)

• 5. Relational Algebra
• 6. SQL
• 7. Application Programming

– Embedded SQL – JDBC – JSPs (hopefully)

• 8. Transaction and concurrency control
• 9. Performance Issues (maybe)

3753 13

Alternate Objectives

• In this class, I will be podcasting by

recording the class and putting it online.

• I will try and put the material online in

formats that will be useful for you (mp3, flash movie, quicktime movie and pdf).

• I will adjust how this is done based on

feedback from the class.

Why study databases?

• Data (information) is a key resource.
• Databases help us

– Store it (file structures, disk management) – Understand it (data models) – Keep it secure (security, recovery) – Find it and use it (query languages, concurrency control and data analysis tools)

3753 15

Why study databases?

• Information is growing
• DBMSs are a key part of most systems

– banking, making reservations, purchasing, borrowing, marks, schedules, browsing online catalogues, video on demand, interactive maps and images, employee information, web servers

3753 16

IBM’s DB2

• IBM Database software
• DB2 Scholars Program – free software!
• Windows, NT, Linux, OS/2 …

– RPMs are available for RedHat and CentOS

• You can download a copy of the software

from the course webpage.

3753 17

Database Management Systems: Basic Concepts

Elmasri/Navathe: Chapters 1 & 2

3753 18

Definitions

• Data: known facts that can be recorded
• Database: a collection of data
• represents some aspect of the real world
• logically coherent collection (not a random collection)
• designed, built & populated for a specific purpose
• Database Management System: the software

that manages the data

3753 19

DBMSs provide...

Facilities to:

Define – specify data types, structures & constraints for the data to be stored in the database Construct – store the data Manipulate – pose queries to retrieve specific data, update data or generate reports based

• n the data

3753 20

Popular Examples

• Company Databases

– employees, departments, projects …

• Airline Reservation Systems

– flights, fares, customers, reservations ..

• Library Databases

– authors, titles, publishers, videos …

• Bank Databases

– accounts, customers ...

3753 21

Database System Environment

Application Programs/Queries Software: Query Processing & Programs Software: Data Access

DBMS Software Database System

Users/Programmers Database Definition Database

22

DBMSs VS File Processing

Why do we need a DBMS? Why not just use files to store data?

3753 23

File Processing

Human Resources Science Office

Application Programs Application Programs

3753 24

File Processing VS DBMS

File Processing

• data definition is part of

application programs

• programs & data are

interdependent

DBMS

• self-describing
• program-data independence
• support of multiple views of

data

• provides concurrency control &

transaction processing capabilities

• provides mechanisms for

backup & recovery

• support for query languages
• provides access control

3753 25

Desirable Capabilities

• control redundancy
• restrict access
• provide persistent storage for program objects &

data structures

• permit inferencing & actions by using rules
• provide multiple user interfaces
• represent complex relationships among data
• enforce integrity constraints
• provide back-up & recovery

3753 26

Additional Advantages of using a DBMS

• Potential for enforcing standards
• Reduced application development time
• Flexibility
• Availability of up-to-date info to all users
• Economical
• Multi-user to a high degree

3753 27

Historically….

1960’s

• Hierarchical model (IBM’s IMS)
• Network Model (CODASYL)

1970’s

• Network DBMS’s introduced
• Codd introduces Relational Model (1971)
• Chen develops ER model (1976)
• Relational DB’s appear in late ‘70’s

3753 28

Historically…(con’t)

1980’s

• Relational DBMS’s dominate
• Preliminary SQL standard published
• Object-oriented concepts
• Distributed DBMS’s become an important area of

research

1990’s

• Client-server takes over
• Legacy DBs become a major problem
• New areas such as data warehousing, multimedia

emerge

3753 29

Historically (con’t)

2000

• Databases survive Y2K!

2001-2006

• IBM buys Informix
• Industry depending heavily on DBMSs for everything
• Move back to mainframes and DBMSs for everything

from data storage to web servers

• IBM drops Derby/Cloudscape to Apache as an Open

Source project

3753 30

Database Players

• DBA ($$$$$$)

– access authorization, coordination & monitoring database usage, problem determination, performance tuning etc

• Designers

– identify the requirements & chose the appropriate structures to represent & store the data

• Users
• System analysts & application programmers
• DBMS system designers & implementers
• Tool developers
• Operators & maintenance personnel

3753 31

Areas of Database Study

• DBMS software
• Database Design
• Query Languages
• Application Programming
• Database Administration
• Data Warehousing & Data Mining
• Performance

3753 32

Data Model

• a collection of concepts that can be used to

define the structure (data, data types, relations and constraints) of a database.

• Examples:

– Entity Relationship model – Relational Model – hierarchical & network models – object-data models

3753 33

Conceptual Models Entities, attributes & relationships Implementation Models Record Structures

Record formats, record orderings, access paths

Physical Models

Categories of Data Models

High Level Low Level

34

Schemas & Instances

• Important to distinguish between

– database schema: the description of the database – database: the stored data

3753 35

Company Type Name Date Amt NumShares Broker

Trimark Mutual Fund Trimark Fund 01/01/84 49.75 100

• C. Harris

AGF

Mutual Fund Foreign Equity 01/01/94 62.25 1000

• C. Harris

Financial Records (Company, Type, Name, Date, Amt, NumShares, Broker)

Characteristics of the Database Approach

• insulation of programs & data
• support of multiple user views
• use of a catalog to support database

descriptions

3753 37

Database States

• Empty State -- database is empty when we

first define the database schema

• Initial State -- database is first populated or

loaded with data

• Current State -- snapshot in time

3753 38

Three-Schema Architecture

External View #1 External View #2 External View #3

Conceptual Schema Internal Schema

External Level

Conceptual Level

Internal Level

39

Data Independence

• Logical data independence - ability to

change the conceptual schema without having to change the external schemas or application programs

• Physical data independence - ability to

change the internal schema without having to change the conceptual (or external) schemas.

3753 40

DBMS Languages

• DDL: Data Definition Language

– used to define/change the structure of the database

• DML: Data Manipulation Language

– used to query the database, insert data, change data or delete data

3753 41

DBMS Interfaces

• Menu-based Interfaces for browsing
• Forms-based Interfaces
• GUIs
• Natural Language Interfaces
• Specialized Interfaces
• Interfaces for the DBA
• Programming Interfaces (APIs)

3753 42

Database System Utilities

• Loading
• Backup
• File Reorganization
• Performance Monitoring

3753 43

Classification of DBMSs

• Data Model Classification

– relational, network, hierarchical, object-

• riented …
• Number of users

– single user or multi-user

• Number of Sites

– centralized vs distributed

• Cost of the DBMS

3753 44