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 role of the logical schema, which is also referred to as the conceptual schema, might be viewed as an essential intermediary layer that bridges the gap between the physical storage of data and the user-specific views. It could be considered as a representation of the entire database structure and organization at a level that is abstracted from the physical details, yet detailed enough to capture the complexities and relationships inherent in the data. The logical schema arguably plays a crucial role in maintaining the integrity and consistency of the database. It is likely where the definitions of data types, constraints, relations, and entities are established. This level might be where the rules governing the relationships between different data elements are set, which could be essential for ensuring that the data is logically correct and meaningful.
One of the potential key functions of the logical schema could be to provide a stable and consistent database structure that is somewhat immune to changes in the physical schema or the user views. For example, changes in the physical storage mechanisms or optimizations in how data is stored and accessed, might not necessitate alterations to the logical structure of the database. Similarly, modifications in user views, such as adding new views or altering existing ones, might be done without impacting the overall logical structure of the database. Furthermore, the logical schema might be instrumental in database management tasks such as data migration, where its stability and consistency could be critical in ensuring a smooth transition of data across different systems or storage formats. Additionally, in database design and redesign processes, the logical schema could serve as a key reference point, guiding the structuring and restructuring of data in response to evolving business requirements and technological advancements.
Overall, the logical schema in the Three Schema Architecture could be seen as a pivotal layer that not only facilitates the efficient and effective use of the database but also contributes significantly to the flexibility and scalability of the database system as a whole.

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