Chapter 1 - The Fundamentals Flashcards
What do the first five chapters of the book cover?
They are devoted to the fundamental tasks of relational database design.
What is modeling?
Is the process of capturing the essence of a system in a known language that is common to the user. Is often used as a generic term for the overall process of creating a database.
What is data modeling?
Is a specific type of model that focuses entirely on the storage and management of the data storage medium.
Who was the “founder” of relational database design?
Edgar F. Codd
What year did the “founder” write his first paper?
1979
What was the “founders” original paper called?
“A Relational Model of Data for Large Shared Data Banks”
Who wrote the other two seminal works on relational database design?
Edgar F. Codd. In 1985.
What is the name of the “founders” second work?
“Is Your DBMS Really Relational?”
What is the name of the “founders” third work?
“Does your DBMS Run By the Rules
What is the name of Rule 1?
The Information Principle
What is the formal definition of Rule 1?
All information in the relational database is represented in exactly one and only one way - by values in tables
What is the name of Rule 2?
Guaranteed Access
What is the formal definition of Rule 2?
Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.
What is the point of Rule 1?
When Codd wrote the rules, there were several different ways that data was being stored. Codd wanted to “abstract” the way the data was presented to the user. So, the user only sees a table with rows and columns but the database engine developer is free to save the data using any underlying format they want.
What is the point of Rule 2?
The rule is about how data is accessed in a table. The primary key will uniquely identify any row in the table. The column will hold a single attribute of that row. It is basically the “addressing system” for the data.
What does “atomic” mean?
It means a value that cannot be broken down any further without losing its fundamental value.
What is the name Rule 3?
Systemic Treatment of NULL Values
What is the formal definition of Rule 3?
NULL values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational RDBMS for representing missing information in a systemic way, independent of data type.
What is the point of Rule 3?
NULL indicates that you don’t have a value to use for a particular attribute. Codd did not want every RDBMS or programmer to come up with their own way to indicating “no data”. NULL means “unknown”. NULL indicates an unknown value of a string and propagates through a math formula. Anything added to “unknown” is still “unknown”.
What is the name of Rule 4?
Dynamic Online Catalog Based on the Relational Model
What is the formal definition of Rule 4?
The database description is represented at the logical level in the same way as ordinary data, so authorized users can apply the same relational language to its interrogation as they apply to regular data.
What is the point of Rule 4?
It means that RDBMS must use tables to describe the database structure itself. Basically, you use the same language (SQL) to look at the “metadata” about the database as you would the data itself.
What is the name of Rule 5?
Comprehensive Data Sublanguage Rule
What is the formal definition of Rule 5?
A relational system may support several languages and various modes of terminal use. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and whose ability to support all of the following is comprehensible:
a. data definition
b. view definition
c. data manipulation (interactive and by program)
d. integrity constraints
e. authorization
f. transaction boundaries (begin, commit, and rollback).
What is the point of Rule 5?
The rule basically mandates that a relational database language must exist to manipulate data. SQL is that language. SQL is just the language that everyone has standardized on but there are other languages that can be used as long as they meet Codd’s definition.
What makes a relational language different than a procedural language?
A procedural language allows you to specify exactly what will happen and in what order. A relational language allows you to express the end result you want and the database engine is free to calculate the results any way it can.
What is the name of Rule 6?
View Updating Rule
What is the formal definition of rule 6?
All views that are theoretically update-able are also update-able by the system.
What is the point of Rule 6?
The rule basically states that views should be update-able and when updating them the underlying tables should be updated, also.
What is a view?
It is a stored representation of data (that is technically a table itself). It is commonly referred to as a “virtual table”.
What does CRUD stand for?
Create
Read
Update
Delete
What is another name for a “regular” table (not a virtual table)?
A “materialized” table
What is the name of Rule 7?
High-Level Insert, Update, and Delete
What is the formal definition of Rule 7?
The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update, and deletion of data.
What is the point of Rule 7?
Inserts, updates, and deletions need to be done using simple operations and commands. All the complexity of multiple users interacting with the database at the same time must be hidden from the user.
What is the name of Rule 8?
Physical Data Independence
What is the formal definition of Rule 8?
Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.
What is the point of Rule 8?
States that the way the database stores and accesses data should be abstracted away from the end user. The user ignores data implementation details. That is the job of the database engine. This is about how the database engine relates to the file system.
What is the name of Rule 9?
Logical Data Independence
What is the formal definition of Rule 9?
Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
What is the point of Rule 9?
States that applications should not care if changes are made to the database tables. Basically, you should be able to add and remove columns without impacting previously existing columns. That is the reason why you should never use “select * …” because that locks your code to the entire table instead of just the columns that you need data from.
What is the name of Rule 10?
Integrity Independence
What is the formal definition of Rule 10?
Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
What is the point of Rule 10?
Basically states that the database must support data integrity constraints and not rely on the application to do that. The database engine is the tool that should be primarily used to protect the integrity of the data.
What constraints does Rule 10 mandate must exist?
- Primary keys for entity integrity (ensuring each row is unique)
- Foreign keys for referential integrity
- Unique constraints for non-PK keys
- NULL constraints – whether or not a column can hold NULL values
- Check constraints for rows and tables
What is the name of Rule 11?
Distribution Independence
What is the format definition of Rule 11?
The data manipulation sublanguage of a relational DBMS must enable application programs and terminal activities to remain logically unimpaired whether and whenever data are physically centralized or distributed.
What is the point of Rule 11?
The point is that the database engine should abstract away the actual location of the data. The user should not care where the data is actually stored (centrally or distributed across multiple servers). This is still being implemented by modern DBMS.
What does RDBMS stand for?
Relational Database Management System
What does DBMS stand for?
Database Management System
What is the name of Rule 12?
Nonsubversion Rule
What is the formal definition of Rule 12?
If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language
What is the point of Rule 12?
States that there should be no way access and change the data that bypasses the integrity constraints. Only when loading large amounts of bulk data should this rule be ignored.
