Fundamental of Databases — part 2
This is a summary of Databases System Concepts and Designs taught by Leo Mark from Georgia Tech
5. Outline major topics
Based on the definition of database and database management systems (DMS) from Fundamental of Databases — part 1 we will look at three major topics:
- Data Modeling
- Process modeling
- Database efficiency
6. Data modeling
The model represents a perception of structures of reality. The data modeling process has two steps. First, to fix the perception of structures of reality. Second, to represent that perception. Database people use different languages for this. One language is the extended entity-relationship model, which is good for fixing a perception of structures of reality. And the other one is the relational model which is good for representing this perception inside a database.
In the data modeling process, we select aspects of reality that are important to us while ignoring others and we use abstraction to organize these aspects.
7. Process Modeling
In process modeling, the aim is to fix and represent the perception of the process of reality. As opposed to the structured process which is not represented inside the database, they are reflected in the way we use the database through the database management systems.
There are two ways in which we can use databases through database management systems. One is to embed data manipulation language code inside a program. The other one is to use the data manipulation language directly to issue ad-hoc (special for a certain purpose) queries on the database.
Let’s look at the figure above. If we have a program that builds, for example, a user interface for an application, then inside of that program it is possible to have a data manipulation link (DML) with statements that access databases, either to update the data or to retrieve the data and to display it back through the interface.
Alternatively, it is possible to use the data manipulation language directly which directly does ad hoc queries on the database or ad hoc updates and maintenance of the database.
8. Data Models-Architecture DBMS Architecture
- Data models
In order to do data modeling, we need to talk about a tool called a data model. Data models contain formulations to allow us to express data structures, constraints, and operations. Two major data models we’re going to talk about, namely, the extended entity-relationship model and the relational model. - Database architecture
And another important aspect I want to briefly talk about is database architecture. So, it turns out that a database and mode of the structure of reality act has several layers to it. We want to look at what are those layers. We also want to take a look at what the architecture of a database management system, to allow us to create such a database, would be. Specifically, we will look at ANSI/SPARC 3-Level database architecture and the corresponding database management system architecture. - Metadata
Finally, I want to mention metadata. What it is. Why it is so important to make everything else happen in a database. And now on a data structures.
9. Examples of data models
A data model is not the same as a model of data. We define the database to be a model of structures of reality. A data model is a tool or the formulas that we use to create such a model. We should always be talking about the following three elements when talking about a data model: data structures, integrity constraints, and operations. Let me mention three examples of data models.
- The entity-relationship model. We will use this model to fix the perception of reality.
- The relational model. We will implement this model in a database management system.
- Hierarchical model. It was implemented in the first database system IBMIMS database system back in 1967. Interestingly the hierarchical model is also the fundamental model on XML databases today.
10. Relational Model — Data Structures
A relational model is an example of a data model. With this, we can illustrate what data structures and constraints, and operations look like in that model. In the relational model, data is represented in tables.
Look at the table above! The table name is RegularUser. Each column has the name: Email, BirthDate, Hometown, and Salary. Each of the columns has a data type. In this case, Email are represented by varchar(50), variable length character strings maximum length 50. BirthDate is represented over data time. Hometown, again in varchar(50). Salary is represented over the data type integer.
A table is set to have columns. In this case, there are four columns. The number of columns is also called the degree of the table. A relation also has a number of rows, in this case, seven rows in the table. The table name, column names, and data types together constitute the scheme of this table.
The schema represents aspects of the data that are stable over time. In other words, the schema is not expected to change. The state of the database represented by the rows in the table reflects aspect that is dynamic and change over time. So the idea is that whereas the scheme, in general, represents the structure of data, the rows will represent what is the current state of reality that’s modeled by this table.
11. Relational Models — Constraints
Constraints represent rules that cannot be expressed by the data structures we just looked at. For example:
- We might want emails to be unique, as they are in this example. That would allow us to think about emails as representing unique regular users. So for each user out in the real world, there’s a corresponding row in this table here. It wouldn’t make sense if the email were null because that really doesn’t represent anything.
- We also need to constrain the birth data to be after 1/1/1900.
- Finally, we might have a constraint that says that Hometown must be a city in the United States.
Notice that none of these constraints are expressed by the table structure or the data types that the column values are pulled from. That’s why constraints express rules that are not expressed by the data structures alone.
