Database Management System Essentials
Length: Approximately eight hours.
Database management systems play an essential role in most modern transportation management, administrative, and control systems.
Therefore, transportation professionals are often responsible for making design, development, operations, and maintenance decisions concerning database management systems. In addition, many transportation professionals are users of database management systems to support their responsibilities. Clearly, in order to effectively accomplish their mission, transportation professionals do not need to be database experts. However, a basic understanding of database management systems has become important.
This course emphasizes the importance of database design, use, and management to ITS. The module begins with a general description of databases and database management systems, including the relationships that exist among a database, a DBMS, and an application. Relational database tables, Entity-Relationship Models, and the role of metadata are discussed in detail. Finally, the module describes SQL, and explores the role of databases in data warehousing and Geographic Information Systems.
Upon completion of the course, participants will be able to:
- Explain how databases work
- Describe how databases are designed
- Recall how to work with databases to support decision making
- Relate how databases are used for advanced applications, namely data warehousing and geographic information systems (GIS)
Lesson 1. DATABASES AND DBMSs
- The Database Management System (DBMS)
- DBMS Roles in ITS
- Big Data
- What Do You Need To Know
Lesson 2. HOW DATABASES WORK
- Why Use a Database?
- What Is a Relational Database?
- Relational Database Structure
- Relational Database Table Relating Tables with Keys
- Normalizing Databases
- Relational Database Tutorial
- Workshop #1
Lesson 3. DATABASE DESIGN
- Entity-Relationship Models
- Entity-Relationship (ER) Diagram
- Entity and Attributes
- Sample ER Diagram
- Workshop #2
- Relationships and Diagramming
- Relationship Examples
- Designing Databases with an ER Diagram
- Database Documentation/Metadata
- Workshop #3
Lesson 4. STRUCTURED QUERY LANGUAGE (SQL)
- Structured Query Language
- SQL Functions
- More about SQL
Lesson 5. DATA WAREHOUSE
- Data Warehouse and Components
- ITS Data Warehouse
- Key Issues Related to ITS Data Warehouse
- Strengths of Data Warehousing in ITS
- Weaknesses of Data Warehousing in ITS
Lesson 6. GEOGRAPHIC INFORMATION SYSTEM (GIS)
- What Is GIS?
- Representing Spatial Location in a Database
- Spatial Coordinate Systems
University of Virginia