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:
  1. Access: most popular 'personal' DB
  2. Oracle: Most popular commercial DBMS
  3. MS SQL server: Likely second most popular commercial DBMS
  4. MySQL: most popular open source DBMS
  5. MariaDB: highly compatible to MySQL
  6. Postgres: popular open source DBMS known for innovation and functionality.
  7. SQLite: most popular portable DB engine.
• 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. MongoDB (document store)
    • Wide Column DB: optimized over large data-set; store columns together, not rows. E.g. Cassandra and HBase.
• One DB ranking: http://db-engines.com/en/ranking. What do you notice?
• We focus on RDBMS.

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. Three major kinds:
  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, such as:
       • conceptual and physical database design and implementation
       • security
       • user account management
       • backup and recovery
       • performance tuning
• The likely role of most of you currently: DB Developers.
• You are likely not the end users of the DB system you built.
• 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: modeling

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 (Consult Figure 2.4 of Ricardo.)
• 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 layer pattern is an important concept in Computer Science and software architecture.
• The conceptual level is the key.