CS403: Introduction to Modern Database Systems

2a. assess the three-schema database architecture, comparing levels relative to roles, data independence, and abstraction

Two important principles that we encounter throughout computer science and software engineering are abstraction and hierarchy. Here we see them in the form of the ANSI-SPARC (American National Standards Institute/Standards Planning and Requirements Committee) three-level architecture for database systems.

• Using abstraction and hierarchy, explain the three views of the ANSI-SPARC standard architecture for database systems?
• What benefits does that architecture provide? 

The table in the study guide for Unit 1 presents a picture of a three-level hierarchy of software engineering development phases, views, and data models. The middle and right columns of that table show the 3 levels of the ANSI-SPARC database architecture. What is the software engineering name for each of the 3 levels? What is the ANSI-SPARC view name for each level? What is the model name for each level?

A slightly different (but compatible) hierarchy diagram is shown in this article. How does this diagram differ from the hierarchy table in the study guide for Unit 1? What is the relationship between adjacent levels of each hierarchy (software engineering, view, and model)? For example, what is the relationship of requirements to designs, 1:1 or 1:n? What is the relationship of designs to implementations, 1:1 or 1:n? The same question can be asked of the view hierarchy and the model hierarchy; the answers are the same for each hierarchy.

Think about this relative to both software engineering and to the view and model hierarchies. An application has numerous requirements. There is no unique design that satisfies a given requirement, and there is no unique implementation for a given design. If we define a design for each requirement and then integrate them, we have one overall design. If we define an implementation for each design and then integrate them, we have one implementation. 

As we move down the hierarchy, from level to level, detail is added while maintaining the semantics of the higher level (its data types, data relationships, and constraints). A higher level is more abstract than lower levels. Abstractions provide a guided framework that helps us move from the application or problem domain to a desired technology solution domain. Each abstraction level has common types of models that are applicable to each level. For example, an Entity-Relationship model is applicable to application domains. What are some specific models for Detailed Design / Logical Database Design / Logical Model? To review, see the table from the study guide for Unit 1, this diagram, and pages 15-17 of Database Design.

Another benefit of abstraction pertains to maintenance. What is that benefit? To review, read page 18 of Database Design and page 1-11 of Database Systems for Management. Note that 'architecture' as used here means hierarchy, and embodies several additional concepts.

Roles are also included in this unit. In the real world, we frequently use goals to help us focus on results. In addition, we utilize processes to help us determine what we need to achieve the results. Further, we utilize procedures to help us determine how we could achieve the results. In the real world, this general approach is called problem-solving; in business, it has been called business process; in software engineering, it is called software process.

The definition of a process includes: 

1. purpose of the process
2. roles, responsibilities, and resources
3. a list of the work products used and produced by the process
4. ways to monitor, review, measure, and check the performance of the process

The software engineering terminology for these parts of a process definition are:

1. requirements
2. project planning
3. software work products (also called artifacts or elements)
4. verification and validation

Roles are included in step 2. What are some roles in the development and use of a database? To review, read page 1-5 of Database Systems for Management. Database plans are discussed on page 2-3, and roles on pages 2-11 to 2-15. The other aspects of a process are discussed throughout Chapter 2. As you read, also think about the levels at which the user, management (top and systems), database administrator, information analyst, database analyst, and programmer roles occur.

 

2b. use examples to describe and differentiate between tables, views, and indexes

• What are the elements of a database?
• What are some database specific models?
• What are: tables, views, and indexes?

A database is composed of data elements: fields, records, and tables. A data item of an application domain is represented using a data type, which is stored in digital memory, called a field. A combination of related fields is called a record. A collection of records having a defined relationship is called a table. A collection of tables is called a database. In the relational model, a table is also called a 'relation'. To review, read pages 13-14 of Database Design.

A very thorough discussion of three specific database models is presented in "Database Systems for Management". In the early history of databases in the early 1970s, there were competing implementations and designs for database systems. The American National Standards Institute 

Formed the software planning and requirements committee (SPARC) to specify a set of requirements that expressed the needs of organizations. To get started, SPARC formed a study group on database management systems to examine features of competing systems. The study group identified goals for database management systems, such as ease of use, data security, data integrity, control of redundancy, data independence. Motivated by the realization that the evolution of database technology was progressing, the study group proposed a database design framework that supported the database goals and provided a high degree of independence to facilitate continuous improvement from evolutionary changes in the technology. The ANSI/SPARC framework has 3 levels, called views, where each view is independent of changes in the other views. 

The ANSI/SPARC views are summarized in Database Systems for Management, pages 4-1 to 4-3. Three early database models for the conceptual view are described on pages 4-4 to 4-7.

A view is a perspective that focuses on a subset of the functions or records of a database. A view is sometimes called a schema when used in the context of a data or database model. A schema is a data model (such as the E-R model) or a database model (such as hierarchical, network, or relational model) description for a level (user/requirements, design, or physical/implementation level). A subschema is a model description for a subset of the data of a database. A subschema definition is contained in the definition of a schema. You can review schemas and subschemas for each level of the ANSI/SPARC 3 level architecture and the roles that use them, as well as specific database model schema/subschema descriptions and examples of user view schemas, on pages 4-2 to 4-11 of Database Systems for Management.

Pages 10-1 to 10-13 of Database Systems for Management present a thorough discussion of topics relevant to the physical or internal view. Data structure implementation decisions directly affect the efficiency of database performance. Does database design affect efficiency? How? An index (also called a key) is a field (a column or an attribute in the relational model) that is used to efficiently locate records having a specific value for that field. You can review indices in physical models on pages 15-18 of Database Design. Data Definition and Data Manipulation commands, covered in later units of this course, define keys and utilize them to find information.

 

Unit 2 Vocabulary

This vocabulary list includes terms that might help you with the review items above and some terms you should be familiar with to be successful in completing the final exam for the course. 

Try to think of the reason why each term is included.

• Schema
• Database architecture
• Roles
• Data independence
• Abstraction
• Table
• View
• Index
• Database administrator
• Database analyst
• Top-level management
• Project management
• Database plan
• Users
• Subschema
• Effectiveness
• Efficiency