Lesson 9

This module discussed the objectives of database design strategy and explored two distinct approaches to database design.

1. Subject Approach to Database Design
2. Application approach to Database Design.

You discovered that a database has a three-tier architecture, and that one tier, namely the logical schema, is the domain of the designer. You looked at a brief overview of the stages in the database life cycle (DBLC).
Finally, you learned about CASE tools that assist in designing databases.

In the Three Schema Architecture, the logical schema is the middle layer that defines the logical view of the database. The logical schema represents the way that users perceive the data and defines the relationships between the different entities in the database. The logical schema is responsible for mapping the conceptual schema, which defines the overall structure and relationships of the data, to the physical schema, which describes how the data is actually stored on disk. The logical schema provides a conceptual representation of the database that is independent of the physical storage details.
The logical schema is also responsible for defining the access paths and data retrieval methods for the database. This includes defining the primary and foreign keys, indexes, and other data structures that are used to efficiently retrieve data from the database. The logical schema plays a critical role in ensuring the data integrity, consistency, and security of the database. It ensures that users can access the data they need in a way that is consistent and reliable, and that the data remains protected from unauthorized access or modification.
The logical schema in the Three Schema Architecture acts as a bridge between the conceptual and physical levels of the database, providing a logical view of the data that is optimized for efficient retrieval and use by end-users.

Having completed the lessons in this module, you should be able to:

1. Describe the overall strategy of database design
2. Describe the subject approach to database design
3. Describe the application approach to database design
4. Define user view, logical schema, and physical schema
5. Describe the design stages in the database life cycle
6. Describe the post-design stages in the database life cycle
7. Explain the use of CASE tools in database design

This module introduced you to the following terms:

1. business objects: Items in a business environment that are related, and about which data need to be stored (i.e. , customers, products, orders, etc.).
2. Business rules: A set of rules or conditions describing the business polices that apply to the data stored on a company databases.
3. conceptual model: A description of the structure of a database.
4. data flow diagram: A diagram illustrating the flow of data in an organization, including data sources, data storage, and data transformation processes.
5. data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist.Problems with data integrity occur when a value in one table that is supposed to relate to a value in another can not because the second value either has been deleted or was never entered.
6. data redundancy: Duplication of data in a database.
7. entity-relationship (ER) diagram: A diagram used during the design phase of database development to illustrate the organization of and relationships between data during database design.
8. information system: Interrelated components (e.g., people, hardware, software, databases, telecommunications, policies, and procedures) that input, process, output, and store data to provide an organization with useful information.
9. logical design: The second stage in the DBLC: creating a logical schema, followed by data normalization.
10. logical schema: The overall logical plan of a database; typically a completed ER diagram.
11. normalization: The process of applying increasingly stringent rules to a relational database to correct any problems associated with poor design.
12. physical design: The third stage in the DBLC: tweaking data design elements to optimize database performance.
13. physical schema: The underlying physical storage of data in a database, managed by the RDBMS.
14. system administrator: The person responsible for administering a multi-user computer system; duties range from setting up and configuring system components (e.g., an RDBMS) to performing maintenance procedures (e.g., database backups) on the system.
15. user view: Specifies which users are permitted access to what data in a database.

The next module explores the stages in the database life cycle.

Before moving on to the next module, click the Quiz link below to check your knowledge of database design strategy and tools.
Database Design Strategy - Quiz