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

database management systems
SMART_READER_LITE
LIVE PREVIEW

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


slide-1
SLIDE 1

Database Management Systems

(Comp 3753 X1)

1

slide-2
SLIDE 2

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

slide-3
SLIDE 3

Course Information

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

3753/

3753 3

slide-4
SLIDE 4

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

slide-5
SLIDE 5

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

slide-6
SLIDE 6

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

slide-7
SLIDE 7

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

slide-8
SLIDE 8

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

slide-9
SLIDE 9

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

slide-10
SLIDE 10

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

slide-11
SLIDE 11

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

slide-12
SLIDE 12

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

slide-13
SLIDE 13

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

slide-14
SLIDE 14

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.

slide-15
SLIDE 15

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

slide-16
SLIDE 16

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

slide-17
SLIDE 17

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

slide-18
SLIDE 18

Database Management Systems: Basic Concepts

Elmasri/Navathe: Chapters 1 & 2

3753 18

slide-19
SLIDE 19

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

slide-20
SLIDE 20

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

slide-21
SLIDE 21

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

slide-22
SLIDE 22

Database System Environment

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

DBMS Software Database System

Users/Programmers Database Definition Database

22

slide-23
SLIDE 23

DBMSs VS File Processing

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

3753 23

slide-24
SLIDE 24

File Processing

Human Resources Science Office

Application Programs Application Programs

3753 24

slide-25
SLIDE 25

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

slide-26
SLIDE 26

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

slide-27
SLIDE 27

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

slide-28
SLIDE 28

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

slide-29
SLIDE 29

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

slide-30
SLIDE 30

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

slide-31
SLIDE 31

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

slide-32
SLIDE 32

Areas of Database Study

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

3753 32

slide-33
SLIDE 33

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

slide-34
SLIDE 34

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

slide-35
SLIDE 35

Schemas & Instances

  • Important to distinguish between

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

3753 35

slide-36
SLIDE 36

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)

slide-37
SLIDE 37

Characteristics of the Database Approach

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

descriptions

3753 37

slide-38
SLIDE 38

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

slide-39
SLIDE 39

Three-Schema Architecture

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

Conceptual Schema Internal Schema

External Level

Conceptual Level

Internal Level

39

slide-40
SLIDE 40

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

slide-41
SLIDE 41

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

slide-42
SLIDE 42

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

slide-43
SLIDE 43

Database System Utilities

  • Loading
  • Backup
  • File Reorganization
  • Performance Monitoring

3753 43

slide-44
SLIDE 44

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