Unit 3: Data Models
In Unit 2, you learned that database design is a process to facilitate the construction, development, implementation, and maintenance of database management systems. Unit 3 will add to your knowledge of database design. This unit introduces data models. Your ability to understand and explain data models is the first step to designing a database. Data models are abstract models that organize data elements and define the logical inter-relationships between different data elements. The purpose of data models is to represent "what data are required" and "what format to use" in business practices. Data models facilitate organizational communication and development. Therefore, data models are used to accurately represent requirements by designing responses needed to answer those requirements.
Completing this unit should take you approximately 8 hours.
Upon successful completion of this unit, you will be able to:
- describe the various data models, inlcuding logical models like the ERD model and physical models like the relational model;
- distinguish between the disadvantages vs advantages of different data models;
- describe why each data model, ERD and Relational, are used, and the benefits and drawbacks of each;
- apply E-R modeling to several practical examples;
- analyze a database system for security flaws; and
- design mitigating systems and procedures to address security flaws.
3.1: Types of Data Models
The primary role of data models is to determine how data is revealed to the end user. Data professionals create and structure database tables to answer specific business questions. Data models ensure the best possible data analysis by revealing the most relevant data requested by the end user. Remember, data modeling is NOT data analysis. Data modeling optimizes the process in order to deliver clean and usable data for analysis.
Watch this video. Data modeling makes analysis easier. For example, if you needed to compare data from the customer perspective and organizational objectives. It would be exceedingly difficult to conduct this type of analysis with a single query. However, this identifies the importance of data models before conducting data analysis.
- Read this chapter, taking notes on each type of data model. You'll want to be able to explain the similarities and differences between each data model. Then, complete the exercise at the end of the lesson.
Conceptual, logical, and physical data models are methods used to model data in a domain. While each of these is considered a data model, they are created for different purposes. Now, you will learn more about these data models and how to use them.
Watch this webinar on data modeling and metadata management. Pay attention to the purpose of data models, metadata, and relational databases. Do data models provide metadata management? Why or why not?
3.1.1: Conceptual Models
A conceptual model focuses on identifying data used within a business. It is used to support business development, events, and track performance measures. However, it does not focus on process flow or other physical characteristics.
Read this chapter and learn the models listed in order of decreasing level of abstraction. Pay attention to the summary given on conceptual models. Complete the exercises at the end of the chapter.
3.1.2: Logical Models
Logical data models are a way to translate business concepts for data purposes/uses into Entity-Relationship diagrams. Logical data models describe data in as much detail as possible. This model is more complex than conceptual models. Logical data models establish column (data) types to include all entities and relationships among them. Remember, logical data models have nothing to do with creating a database. Identifying the data elements we want to translate and identifying how they are related through key fields.
Watch this video on the first steps of logical data modeling. Logical data modeling involves taking some construct, or data, from the physical world and representing it in a logical data model. The first step in logical data modeling involves identifying the data elements we want to represent and how they are related to each other via key fields.
3.1.3: Physical Models
Physical data models define all required logical database components and services to build a database. Physical data models can also be a layout of an existing database. Normally, physical data models contain the table structure, column names, primary keys, and other relationships among tables. The most commonly used physical model is the Relational Model. The relational model was originally developed by Edgar F. Codd in 1969 and has become the international standard. Today most Database Management Systems are Relational Database Management Systems (RDBMS). Gaining an understanding of the relational model and relational databases is thus a key skill for anyone seeking to work in the data management field.
Watch this video on Entity-Relationship (E-R or sometimes E/R) modeling. E-R models represent the first step in converting a conceptual data model into a physical data model.
3.2: Data Model Advantages and Disadvantages
Data models are critical in producing higher-quality data, faster performance, and few data errors. However, as a data professional, it is important to be able to explain the advantages and disadvantages of data models. We can construct data models using a number of techniques. The Entity-Relationship (E-R) model is in wide use. In addition to being an effective modeling technique in and of itself, E-R modeling also facilitates the easy construction of a relational model for implementation in a relational database management system (RDBMS). Note the role of E-R modeling as you proceed through this lesson.
Watch this video on data modeling that compares E-R modeling to UML modeling. Pay attention to each presenter as they discuss the pros and cons of each of these database modeling techniques.
Watch this video on the advantages of a relational model. Did you understand the advantages of logical and physical data models?
After completing this section, you should be able to explain the advantages and disadvantages of data models. Next, we will explain the E-R model and business rules.
3.3: The Enhanced E-R Model and Business Rules
An entity-relationship (E-R) model is also known as an entity-relationship (E-R) diagram. An E-R model is a graphical representation of entities and their relationship with each other. E-R models are usually applied with organizing data within databases and information systems.
Business rules describe organizational policies that apply to the data stored in a database. Business rules contain two constraints. (1) Field constraints within tables and (2) relationship constraints between two or more tables. Therefore, business rules reflect how an organization understands the use of its data.
Read this chapter and pay attention to the main concepts, kinds of entities, types of attributes, and types of keys. Take notes on the three relationship types and complete the exercise at the end of the chapter.
Watch this video on business rules. What is the relationship between E-R models and business rules? Are business rules important to E-R modeling? Why or why not?
3.3.1: Supertypes and Subtypes
Supertypes and subtypes happen frequently in the business world. An entity is known as a supertype. Each group established within the entity is known as a subtype. An entity subtype contains unique characteristics of the subtype. For example, an employee is a "supertype" and a professor is a "subtype" of an employee.
Watch this lecture on the Enhanced E-R model. Take notes on the definition of supertype and subtype. Pay attention to the unique relationship between supertypes and subtypes.
3.3.2: How Business Rules are Used
Business rules are intended for organizations that use data to explain policy, procedure, or principles. Business rules define entities, attributes, relationships, and constraints.
Read this chapter and pay attention to the section on business rules and how they are obtained from users. Take note of the business rule examples and answer the exercises at the end of the chapter.
3.4: Database Security
Data represents a critical asset for organizations. Any valuable asset can attract the interest of people who wish to steal the asset or harm the organization controlling the asset. For this reason, database security is a critical function of any database system. We need to ensure that only those people with a legitimate need to access the data are granted permission to access the data.
Watch this lecture and take notes on the security concepts discussed. Note the many different security threats that database management systems may be subjected to and the actions that the database administrator can take to mitigate against some of these threats. In particular, make note of the use of SQL injection attacks as you study SQL later in the course.
Remember, data models not only provide a basis for the design of a computerized database system, they are also very useful for communicating with non-technical business users. Business process owners work with data modelers in order to ensure that the database system being developed will meet the needs of the organization. The data model provides a common point of reference in the development process. As a data professional, you will have to create and structure database tables to support business operations and processes. In conclusion, you learned about various data models, their advantages, and their disadvantages. Now, you can explain data models and apply E-R models using the practical exercises given in this section.
Study Guide: Unit 3
We recommend reviewing this Study Guide before taking the Unit 3 Assessment.
Unit 3 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.