Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Pro SQL Server 2012 Relational Database Design and Implementation > Chapter 1 - The Fundamentals > Flashcards

Chapter 1 - The Fundamentals Flashcards

1
Q

What do the first five chapters of the book cover?

A

They are devoted to the fundamental tasks of relational database design.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is modeling?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is data modeling?

A

Is a specific type of model that focuses entirely on the storage and management of the data storage medium.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Who was the “founder” of relational database design?

A

Edgar F. Codd

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What year did the “founder” write his first paper?

A

1979

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What was the “founders” original paper called?

A

“A Relational Model of Data for Large Shared Data Banks”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Who wrote the other two seminal works on relational database design?

A

Edgar F. Codd. In 1985.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the name of the “founders” second work?

A

“Is Your DBMS Really Relational?”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the name of the “founders” third work?

A

“Does your DBMS Run By the Rules

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the name of Rule 1?

A

The Information Principle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is the formal definition of Rule 1?

A

All information in the relational database is represented in exactly one and only one way - by values in tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the name of Rule 2?

A

Guaranteed Access

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the formal definition of Rule 2?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the point of Rule 1?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the point of Rule 2?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What does “atomic” mean?

A

It means a value that cannot be broken down any further without losing its fundamental value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the name Rule 3?

A

Systemic Treatment of NULL Values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the formal definition of Rule 3?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the point of Rule 3?

A

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”.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the name of Rule 4?

A

Dynamic Online Catalog Based on the Relational Model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the formal definition of Rule 4?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is the point of Rule 4?

A

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.

23
Q

What is the name of Rule 5?

A

Comprehensive Data Sublanguage Rule

24
Q

What is the formal definition of Rule 5?

A

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).

25
Q

What is the point of Rule 5?

A

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.

26
Q

What makes a relational language different than a procedural language?

A

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.

27
Q

What is the name of Rule 6?

A

View Updating Rule

28
Q

What is the formal definition of rule 6?

A

All views that are theoretically update-able are also update-able by the system.

29
Q

What is the point of Rule 6?

A

The rule basically states that views should be update-able and when updating them the underlying tables should be updated, also.

30
Q

What is a view?

A

It is a stored representation of data (that is technically a table itself). It is commonly referred to as a “virtual table”.

31
Q

What does CRUD stand for?

A

Create
Read
Update
Delete

32
Q

What is another name for a “regular” table (not a virtual table)?

A

A “materialized” table

33
Q

What is the name of Rule 7?

A

High-Level Insert, Update, and Delete

34
Q

What is the formal definition of Rule 7?

A

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.

35
Q

What is the point of Rule 7?

A

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.

36
Q

What is the name of Rule 8?

A

Physical Data Independence

37
Q

What is the formal definition of Rule 8?

A

Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.

38
Q

What is the point of Rule 8?

A

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.

39
Q

What is the name of Rule 9?

A

Logical Data Independence

40
Q

What is the formal definition of Rule 9?

A

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.

41
Q

What is the point of Rule 9?

A

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.

42
Q

What is the name of Rule 10?

A

Integrity Independence

43
Q

What is the formal definition of Rule 10?

A

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.

44
Q

What is the point of Rule 10?

A

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.

45
Q

What constraints does Rule 10 mandate must exist?

A
  • 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
46
Q

What is the name of Rule 11?

A

Distribution Independence

47
Q

What is the format definition of Rule 11?

A

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.

48
Q

What is the point of Rule 11?

A

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.

49
Q

What does RDBMS stand for?

A

Relational Database Management System

50
Q

What does DBMS stand for?

A

Database Management System

51
Q

What is the name of Rule 12?

A

Nonsubversion Rule

52
Q

What is the formal definition of Rule 12?

A

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

53
Q

What is the point of Rule 12?

A

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.

Pro SQL Server 2012 Relational Database Design and Implementation (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions