Unit 2: Database Architecture and Date Languages
In order to properly create and then manage a database, we need to have a thorough understanding of the data it holds. Because data can be seen from different levels, we will introduce different data models and learn how to apply them in order to describe the structure of the database, thereby providing a "view" of the database for the different types of users introduced in the previous section. This unit explains database architecture and design using the ANSI/SPARC three-schema architecture.
Completing this unit should take you approximately 4 hours.
Upon successful completion of this unit, you will be able to:
- assess the three-schema database architecture, comparing levels relative to roles, data independence, and abstraction; and
- use examples to describe and differentiate between tables, views, and indexes.
2.1: Databases and the Three-Schema Architecture
This article presents the three-schema architecture, which aims to provide data independence. The three schemas are external, conceptual, and internal. Each provides a level of independence: application independence from the external schema, external schema independence from the conceptual schema, conceptual schema independence from the internal schema, and internal schema independence and thus application independence from the physical data itself.
Read chapters 3 and 4. In chapter 3, the three ANSI/SPARC schemas are presented as phases of database development: the requirements phase, the high-level design phase, the detailed design phase, and the implementation phase. These correspond to the external model, the internal model, and the physical model, respectively. This chapter also describes entity-relationship modeling as a means of representing a conceptual model. Chapter 4 discusses conceptual modeling for hierarchical, network, and relational databases.
2.2: Tables, Views, and Indexes
Read chapter 7, which introduces the relational database model. Relational databases are one of the most commonly-used database models. The chapter includes a mathematical foundation for the model in relational algebra and relational calculus. These are also the basis of the standard database language SQL. Most current database systems support the relational model.
You have probably used spreadsheet software like Microsoft Excel or OpenOffice. If so, you have likely used tables, views, and indexes while manipulating your spreadsheet data. These are all concepts used in relational databases. This video explains the concepts of relational databases using Microsoft Excel. A "primary key" is a special type of index that is unique, and the main (or primary) value used to locate an entry in a database.
This lecture explains the basic concepts of relational databases, including tables, columns, primary keys, foreign keys, and referential integrity. It then illustrates them using a customer order database. The latter part of the lecture uses Microsoft Access to implement a relational database.
Unit 2 Assessment
- Receive a grade
Take this assessment to see how well you understood this unit.
- This assessment does not count towards your grade. It is just for practice!
- You will see the correct answers when you submit your answers. Use this to help you study for the final exam!
- You can take this assessment as many times as you want, whenever you want.