ITEC 3220A Using and Designing Database Systems - PowerPoint PPT Presentation

Loading...

PPT – ITEC 3220A Using and Designing Database Systems PowerPoint presentation | free to download - id: 146f5d-ODM2N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

ITEC 3220A Using and Designing Database Systems

Description:

Tentative Schedule. Week 1 Database concepts and the relational database model (Chapter 1, 2 & 3) ... Tables are a series of row/column intersections ... – PowerPoint PPT presentation

Number of Views:93
Avg rating:3.0/5.0
Slides: 50
Provided by: cseY
Learn more at: http://www.cse.yorku.ca
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: ITEC 3220A Using and Designing Database Systems


1
ITEC 3220AUsing and Designing Database Systems
  • Instructor Gordon Turpin
  • Course Website http//cse.yorku.ca/gordon/itec32
    20S07
  • Office CSEB3020

2
Course Objective
  • Examine databases, trends in database management
    systems and their application in a wide range of
    organizational areas
  • Provide an overview of database processing, both
    historical and discussion of recent trends in
    database management
  • Provide the student with exposure to a range of
    tools, including a relational DBMS as well as an
    object-oriented DBMS

3
Textbooks
  • A bundle consisting of
  • Database Systems Design, Implementation,
    Management, Seventh Edition - Peter Rob Carlos
    Coronel
  • A Guide to SQL, Seventh Edition - Philip J. Pratt

4
Marking Scheme
  • Final exam (closed book) - 50Midterm (closed
    book) - 35Assignments (3 assignments) - 15
  • Lecture notes will be made available at through
    the course website.

5
Tentative Schedule
  • Week 1 Database concepts and the relational
    database model (Chapter 1, 2 3)
  • Week 2 Entity relationship model (Chapter 4)
  • Week 3 Normalization (Chapter 5)
  • Week 4 SQL (Chapter 7 A guide to SQL)
  • Week 5 SQL lab (Chapter 7 A guide to SQL)
  • Week 6 Advanced SQL lab (Chapter 8 A guide
    to SQL)

6
Schedule (Contd)
  • Week 7 Midterm
  • Week 8 Database design case study (Chapter 9)
  • Week 9 Transaction management and concurrent
    control (Chapter 10)
  • Week 10 Transaction management and concurrent
    control (Contd) and data warehousing (chapter 10
    13)
  • Week 11 Objected-Oriented database (Appendix G)
  • Week 12 TBA and review for final exam

7
Introduction
  • Database Systems and Data Models

8
Basic Definition
  • Data raw facts
  • Constitute building blocks of information
  • Information is produced by processing data and
    reveals meaning of data
  • Good, timely, relevant information key to
    decision making
  • Good decision making key to organizational
    survival
  • Database shared, integrated computer structure
    housing
  • End user data
  • Metadata

9
An Example
  • Converting data to information

10
An Example (Contd)
  • Metadata

11
What is a Database Management System (DBMS)
  • A collection of programs that manages the
    database structure and controls access to the
    data stored in the database
  • Possible to share data among multiple
    applications or users
  • Example bank and its ATM machines
  • Makes data management more efficient and
    effective
  • End users have better access to more and
    better-managed data

12
DBMS Manages Interaction
13
File and File System
  • Terminology
  • Data
  • Raw Facts
  • Field
  • Group of characters with specific meaning
  • Record
  • Logically connected fields that describe a
    person, place, or thing
  • File
  • Collection of related records

14
Example
15
Disadvantages of File Processing
  • Data Dependence
  • Change in files data characteristics requires
    modification of data access programs
  • Lengthy development time
  • Excessive program maintenance
  • Structural Dependence
  • Change in file structure requires modification of
    related programs

16
Example
17
Disadvantages of File Processing (Contd)
  • Data Redundancy
  • Different and conflicting versions of same data
  • Results of uncontrolled data redundancy
  • Data anomalies
  • Modification
  • Insertion
  • Deletion
  • Data inconsistency
  • Lack of data integrity

18
Solution Database Approach
  • Database consists of logically related data
    stored in a single repository
  • Advantages of database approach
  • Structural and data independence
  • Minimal data redundancy
  • Reduces inconsistency, data anomalies
  • Improves data sharing and data quality
  • Stores data structures, relationships, and access
    paths

19
Database vs. File Systems
20
Database System Environment
  • Hardware all the system's physical devices
  • Software
  • Operating system software
  • DBMS software
  • Application programs and utility software
  • People
  • Procedures
  • Data

21
Database Models
  • Collection of logical constructs used to
    represent data structure and relationships within
    the database
  • Conceptual models logical nature of data
    representation
  • Implementation models emphasis on how the data
    are represented in the database

22
Database Models Historic Overview
  • Flat files - 1960s - 1980s
  • Hierarchical 1970s - 1990s
  • Network 1970s - 1990s
  • Relational 1980s - present
  • Object-oriented 1990s - present
  • Object-relational 1990s - present
  • Data warehousing 1980s - present
  • Web-enabled 1990s - present

23
Hierarchical Database Model
  • Logically represented by an upside down tree
  • Each parent can have many children
  • Each child has only one parent

24
Hierarchical Database Model (Contd)
  • Advantages
  • Conceptual simplicity
  • Database security and integrity
  • Data independence
  • Efficiency
  • Disadvantages
  • Complex implementation
  • Difficult to manage and lack of standards
  • Lacks structural independence
  • Application programming and use complexity
  • Implementation limitations

25
Network Database Model
  • Each record can have multiple parents
  • Composed of sets
  • Each set has owner record and member record
  • Member may have several owners

26
Network Database Model (Contd)
  • Advantages
  • Conceptual simplicity
  • Handles more relationship types
  • Data access flexibility
  • Promotes database integrity
  • Data independence
  • Conformance to standards
  • Disadvantages
  • System complexity
  • Lack of structural independence

27
Relational Database Model
  • Perceived by user as a collection of tables for
    data storage
  • Tables are a series of row/column intersections
  • Tables related by sharing common entity
    characteristic(s)

28
Relational Database Model (Contd)
29
Relational Database Model (Contd)
  • Schema for the table
  • Graphical representation
  • Text description
  • AGENT(AGENT_CODE, AGENT_LNAME, AGENT_FNAME,
    AGENT_INITIAL, AGENT_AREACODE, AGETN_PHONE)

AGENT
30
Relational Database Model (Contd)
  • Advantages
  • Structural independence
  • Improved conceptual simplicity
  • Easier database design, implementation,
    management, and use
  • Ad hoc query capability with SQL
  • Powerful database management system
  • Disadvantages
  • Substantial hardware and system software overhead
  • Poor design and implementation is made easy
  • May promote islands of information problems

31
Object-Oriented Database Model
  • Objects or abstractions of real-world entities
    are stored
  • Attributes describe properties
  • Collection of similar objects is a class
  • Methods represent real world actions of classes
  • Classes are organized in a class hierarchy
  • Inheritance is ability of object to inherit
    attributes and methods of classes above it

32
OO Data Model
  • Advantages
  • Adds semantic content
  • Visual presentation includes semantic content
  • Database integrity
  • Both structural and data independence
  • Disadvantages
  • Lack of OODM
  • Complex navigational data access
  • Steep learning curve
  • High system overhead slows transactions

33
Costs and Risks of the Database Approach
  • Up-front costs
  • Installation Management Cost and Complexity
  • Conversion Costs
  • Ongoing Costs
  • Requires New, Specialized Personnel
  • Need for Explicit Backup and Recovery
  • Organizational Conflict

34
Review
  • Basic concepts data, information, database,
    DBMS, file, conceptual model, implementation
    model, etc
  • Why database and its importance, cost and risk
  • Different database models
  • definition
  • advantage
  • disadvantage

35
Chapter 3
  • The Relational Database Model

36
In this chapter, you will learn
  • Basic components of the relational database model
  • Entities and their attributes
  • Relationships among entities
  • Relational algebra
  • Relationship in relational database
  • Data redundancy

37
Basic Definition
  • Entities and Attributes
  • Entity is a person, place, event, or thing about
    which data is collected
  • Attributes are characteristics of the entity
  • Tables
  • Holds related entities or entity set
  • Also called relations
  • Comprised of rows and columns

38
Table Characteristics
  • Two-dimensional structure with rows and columns
  • Rows (tuples) represent single entity
  • Columns represent attributes
  • Row/column intersection represents single value
  • Tables must have an attribute to uniquely
    identify each row
  • Column values all have same data format
  • Each column has range of values called attribute
    domain
  • Order of the rows and columns is immaterial to
    the DBMS

39
Example Tables
40
Terminology for Relational Database
41
Key
  • Consists of one or more attributes that determine
    other attributes
  • Primary key (PK) is an attribute (or a
    combination of attributes) that uniquely
    identifies any given entity (row)
  • Keys role is based on determination
  • If you know the value of attribute A, you can
    look up (determine) the value of attribute B

42
Keys (Contd)
  • Composite key
  • Composed of more than one attribute
  • Key attribute
  • Any attribute that is part of a key
  • Superkey
  • Any key that uniquely identifies each entity
  • Candidate key
  • A superkey without redundancies

43
Keys (Contd)
  • Foreign key (FK)
  • An attribute whose values match primary key
    values in the related table
  • Referential integrity
  • FK contains a value that refers to an existing
    valid tuple (row) in another relation
  • Secondary key
  • Key used strictly for data retrieval purposes

44
Simple Relational Database
45
Controlled Redundancy
  • Makes the relational database work
  • Tables within the database share common
    attributes that enable us to link tables together
  • Multiple occurrences of values in a table are not
    redundant when they are required to make the
    relationship work
  • Redundancy is unnecessary duplication of data

46
Integrity Rules
47
Integrity Rules (contd)
48
Exercises
Table name TRUCK
Table name BASE
Table name TYPE
49
Exercises (Contd)
  • For each table, identify the primary key and the
    foreign keys.
  • Do the tables exhibit entity integrity? Explain
  • So the tables exhibit referential integrity?
    Explain
  • Identify the TRUCK tables candidate key (s).
  • For each table, identify a super key and a
    secondary key
About PowerShow.com