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:

1. Program-Data dependency
2. Data duplication
3. Limited Data Sharing
4. Lengthy Development Time
5. Excessive Program Maintenance

Some other disadvantages:

1. Tend to be low level.
2. Lack of a high order conceptual model.
3. Lack of tools and supports.
4. 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:
  1. Likely the most tedious and difficult part for many traditional applications.
  2. Collect documents of existing systems, if exist
  3. Study documents of existing systems, if exist
  4. Talk with domain experts and end users.
  5. Model the problem using a modeling language, such as UML, ER, data flow, etc.
  6. Document the captured requirements: e.g. requirement specifications, data dictionary, etc.
  7. Iteratively refine and correct the model until enough details are captured.
• Design the database solution
  1. Select the appropriate data model
  2. Select the appropriate DBMS
  3. Design the conceptual model
  4. Design the architecture of the DB System
  5. Design the physical database
  6. Design external views
  7. Design individual components
• Implementation and testing
  1. Implement and test design
  2. 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:
  1. Internal/Physical level: Describes physical storage structure of the database
  2. Conceptual/Logical level: Describes logical structure of the entire database
  3. 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.