Database Basics

by K. Yue

1. Introduction

• It is important to be familiar with basic terms and concepts of databases in this course.
• A database system is usually built by using a Database Management System (DBMS).
• Examples of Relational DBMS: Access, MS SQL server, Oracle, MySQL, Postgres, etc.
• Relational DBMS basically use the relational model (with extensions).
• There are many other models. Examples:
  • Object-Oriented Database (OODB): e.g. db4o, Gemstone, etc.
  • Big Data:
    • NoSQL DB: e.g. Mongo (document store).
• One DB ranking: http://db-engines.com/en/ranking. What do you notice?
• We focus on RDBMS.

Some Disadvantages of File Systems

Hoffer:

• Program-Data dependency
• Data duplication
• Limited Data Sharing
• Lengthy Development Time
• Excessive Program Maintenance

Some other disadvantages:

• Tend to be low level.
• Lack of a high order conceptual model.
• Lack of tools and supports.
• Difficulties in interoperability.

2. Users

• Users drive requirements. It is always important to find out the types of users.
• There are many types of users in a RDB.
  1. End Users: usually do not use SQL to access the database directly
     • Front-end users
     • managers
     • domain experts
  2. DB Developers
     • Develop DB solutions.
     • With various levels of access privileges.
  3. DB Administrators
     • Manage the entire DB.
       • conceptual and physical database design and implementation
       • security
       • user account management
       • backup and recovery
       • performance
• The likely role of most of you currently: DB Developers or administrators.
• You are likely not the end users.
• Do not build the database for yourself.

3. DB Development Phases

• The classical waterfall software development life cycle:
  1. Requirement
     1. Planning
     2. Analytics
  2. Design:
     1. Logical Design
     2. Physical Design
  3. Implementation
  4. Testing
  5. Maintenance
• There are many other software lifecycle models.
• DB development is a kind of software development.
• Identify and capture user requirements:
  • Likely the most tedious and difficult part for many traditional applications.
  • Collect documents of existing systems, if exist
  • Study documents of existing systems, if exist
  • Talk with domain experts and end users.
  • Model the problem using a modeling language, such as UML, ER, data flow, etc.
  • Document the captured requirements: e.g. requirement specifications, data dictionary, etc.
  • Iteratively refine and correct the model until enough details are captured.
• Design the database solution
  • Select the appropriate data model
  • Select the appropriate DBMS
  • Design the conceptual model
  • Design the architecture of the DB System
  • Design the physical database
  • Design external views
  • Design individual components
• Implementation and testing
  • Implement and test design
  • Optimize performance

4. The DB Architecture

• The three layered database architecture is well known and you can get a lot of information about it from the Web. Examples: http://jcsites.juniata.edu/faculty/rhodes/dbms/dbarch.htm. (Figure 1-9 Three Schema Architecture of Hoffer).
• Use the layer pattern to manage complexity.
• Three levels:
  • Internal/Physical level: Describes physical storage structure of the database
  • Conceptual/Logical level: Describes logical structure of the entire database
  • External or view level: Describes a part of the database for a particular user group; provide the right level of abstraction and security control.
    
• Provide data independence
  • Logical data independence: between conceptual database and external views.
  • Physical data independence: between conceptual database and physical database.
• The conceptual level is the key.