Chapter 9 of the textbook delved into database design, which has two steps — (1) creating a logical database model and (2) creating a physical database model — which are often done parallel with other system design steps.
Logical database modeling has four key steps:
- Develop a logical data model for each known user interface (form and report) for the application using normalization principles.
- Combine normalized data requirements from all user interfaces into one consolidated logical database model; this step is called view integration.
- Translate the conceptual E-R data model for the application or enterprise, developed without explicit consideration of specific user interfaces, into normalized data requirements.
- Compare the consolidated logical database design with the translated E-R model and produce, through view integration, one final logical database model for the application.
In the physical design, you choose things like datatype, file organization, storage media, and much more. It’s where you really start to worry about storage space and speed.
One of the most important aspects of database design is picking primary keys. Primary keys are a unique field (or fields) that allow an individual record to be identified. Records from one table can then be associated with those from another, by way of foreign key, which is the primary key from another table. Normalization and the different normal forms are also based around the primary keys. In first normal form, there are unique rows and no attributes with multiple values in them. In second normal form, each nonprimary key attribute is identified by the whole primary key. In third normal form, nonprimary key attributes do not depend on each other.
Normalization is a database design technique that reduces data redundancy and has properties of simplicity, and minimal maintenance. The result of normalization is that every nonprimary key attribute depends on the whole primary key. The two purposed that normalization is used for are: eliminating redundant data and ensuring data dependencies make sense.
There are certain rules under normalization and book mentioned two frequently used rules:
1. Second normal form (2NF) – each nonprimary key attribute is identified by the whole key.
2. Third normal form (3NF) – nonprimary key attributes do not depend on each other.
In this week’s reading, an important takeaway that I think is important to mention is about a system development methodology. According to the CISA textbook, a systems development methodology is a structure that an organization uses to plan and control the development of information systems and software and new business applications. The textbook also mentions three different types of models that can be used for a system development methodology. These three models are known as
– Traditional waterfall
– V-shaped
– Iterative
According to the book, the traditional SDLC (waterfall) model and variants of the model normally involve a life cycle verification approach that ensures that potential mistakes are corrected early and not solely during final acceptance testing. V-shaped models focus on the relationship between development phases and testing levels. V-Shape models are the most vague testing, the unit test and occurs immediately after programs have been written in code. And the iterative model is a cyclical process in which business requirements are developed and tested until the entire application is designed and tested to be launched. During each iteration, the development process goes through each phase, from requirements through testing, and each subsequent cycle incrementally improves the processes and efficiencies of the system.
This chapter to me was very important. I have a project at work, that requires merging data from 30 different spreadsheets. The problem with this is that Primary Key is not consistent (terminology) for every spreadsheet, even though it is related data. This makes the process of merging the files very cumbersome as the primary key nuances must be known to make the project work.
Having a better understanding of the Primary Key in my opinion, would lead to a more relational aspect of the project. This could cause efficiency and effectiveness as the understanding is better.
I am in a situation similar to Michael in that I also have a project at work that is based around data and a data model. The project requires a data model to be thought through and tables designed to support an application. The lead architect is doing this in a somewhat adhoc manner and the data model and tables are constantly changing (the architect says evolving). The issue is that there are teams trying to write code that uses the tables and they are changing.
Listening to the lectures and reading the chapter I am able to relate to the benefit of having this thought through and mapped out – vs creating it on the fly!