9/11/24, 2:25 C170 Data Management
PM Applications
C170 Data Management Applications
Notes
about:blan 1/39
k
,9/11/24, 2:25 C170 Data Management
PM Applications
Lesson 1 - Conceptual Models
A Logical View of Data
- Placing the DBMS between the application and the database eliminates
most of the file system’s inherent limitations.
- The relational model enables you to view data logically rather than physically.
Tables and Their Characteristics
- The logical view of the relational database is facilitated by the
creation of data relationships based on a logical construct known
as a relation.
- A table is perceived as a two-dimensional structure composed of rows and
columns, and is called a relation.
- As far as the table’s user is concerned, a table contains a group of
related entity occurrences, or an entity set.
- The characteristics of a relational table are:
1. A table is perceived as a two dimensional structure composed of
rows and columns.
2. Each table row (tuple) represents a single entity occurrence within the
entity set.
3. Each table column represents an attribute, and each column has a distinct
name.
4. Each intersection of a row and column represents a single data value.
5. All values in a column must conform to the same data format.
6. Each column has a specific range of values known as the attribute domain.
7. The order of the rows and columns is immaterial to the DBMS.
8. Each table must have an attribute or combination of attributes
that uniquely identifies each row.
Keys
- A key consists of one or more attributes that determine other attributes.
- Determination is that state in which knowing the value of one attribute
makes it possible to determine the value of another.
- Functional dependence means that the value of one or more attributes
determines the value of one or more other attributes.
- The attribute whose value determines another is called the determinant.
- The attribute whose value is determined by the other attribute is called the
dependent.
- Full functional dependence is used to refer to functional dependencies in
which the entire collection of attributes in the determinant is necessary
for the relationship.
about:blan 2/39
k
,9/11/24, 2:25 C170 Data Management
PM Applications
Types of Keys
- A composite key is a key composed of more than one attribute.
- An attribute that is part of a key is called a key attribute.
- A superkey is a key that can uniquely identify any row in the table — in
other words, it functionally determines every attribute in the row.
- A candidate key is a minimal superkey without any unnecessary attributes,
based on a full functional dependency.
- Entity integrity is the condition in which each row in the table has its
own unique identity.
- All of the values in the primary key must be unique
- No key attribute in the primary key can contain a null.
- A foreign key (FK) is the primary key of one table that has been placed
into another table to create a common attribute.
- Referential integrity is the condition in which every reference to an entity
instance by another entity instance is valid.
- A secondary key is defined as a key that is used strictly for data retrieval
purposes.
Relational Algebra
- Relational algebra defines the theoretical way of manipulating table
contents using relational operators.
Formal Definitions and Terminology
- A relvar is a variable that holds a relation. The table structure is properly
called a relvar, and the data in the structure would be a relation. The relvar
is a container (variable) for holding relation data, not the relation itself.
- A relvar has two parts:
- the heading contains the names of the attributes
- the body contains the relation
about:blan 3/39
k
, 9/11/24, 2:25 C170 Data Management
PM Applications
Relational Set Operators
- The relational operators have the property of closure; the use of
relational algebra operators on existing relations produces new
relations.
- SELECT (Restrict):
- SELECT, also known as RESTRICT, is referred to as a unary operator
because it only uses one table as input.
-
- PROJECT
- PROJECT yields all values for selected attributes. It is also a unary
operator, accepting only one table as input.
-
- UNION
- UNION combines all rows from two tables, excluding duplicate rows.
To be used in the UNION, the tables must have the same attribute
characteristics. The
about:blan 4/39
k
PM Applications
C170 Data Management Applications
Notes
about:blan 1/39
k
,9/11/24, 2:25 C170 Data Management
PM Applications
Lesson 1 - Conceptual Models
A Logical View of Data
- Placing the DBMS between the application and the database eliminates
most of the file system’s inherent limitations.
- The relational model enables you to view data logically rather than physically.
Tables and Their Characteristics
- The logical view of the relational database is facilitated by the
creation of data relationships based on a logical construct known
as a relation.
- A table is perceived as a two-dimensional structure composed of rows and
columns, and is called a relation.
- As far as the table’s user is concerned, a table contains a group of
related entity occurrences, or an entity set.
- The characteristics of a relational table are:
1. A table is perceived as a two dimensional structure composed of
rows and columns.
2. Each table row (tuple) represents a single entity occurrence within the
entity set.
3. Each table column represents an attribute, and each column has a distinct
name.
4. Each intersection of a row and column represents a single data value.
5. All values in a column must conform to the same data format.
6. Each column has a specific range of values known as the attribute domain.
7. The order of the rows and columns is immaterial to the DBMS.
8. Each table must have an attribute or combination of attributes
that uniquely identifies each row.
Keys
- A key consists of one or more attributes that determine other attributes.
- Determination is that state in which knowing the value of one attribute
makes it possible to determine the value of another.
- Functional dependence means that the value of one or more attributes
determines the value of one or more other attributes.
- The attribute whose value determines another is called the determinant.
- The attribute whose value is determined by the other attribute is called the
dependent.
- Full functional dependence is used to refer to functional dependencies in
which the entire collection of attributes in the determinant is necessary
for the relationship.
about:blan 2/39
k
,9/11/24, 2:25 C170 Data Management
PM Applications
Types of Keys
- A composite key is a key composed of more than one attribute.
- An attribute that is part of a key is called a key attribute.
- A superkey is a key that can uniquely identify any row in the table — in
other words, it functionally determines every attribute in the row.
- A candidate key is a minimal superkey without any unnecessary attributes,
based on a full functional dependency.
- Entity integrity is the condition in which each row in the table has its
own unique identity.
- All of the values in the primary key must be unique
- No key attribute in the primary key can contain a null.
- A foreign key (FK) is the primary key of one table that has been placed
into another table to create a common attribute.
- Referential integrity is the condition in which every reference to an entity
instance by another entity instance is valid.
- A secondary key is defined as a key that is used strictly for data retrieval
purposes.
Relational Algebra
- Relational algebra defines the theoretical way of manipulating table
contents using relational operators.
Formal Definitions and Terminology
- A relvar is a variable that holds a relation. The table structure is properly
called a relvar, and the data in the structure would be a relation. The relvar
is a container (variable) for holding relation data, not the relation itself.
- A relvar has two parts:
- the heading contains the names of the attributes
- the body contains the relation
about:blan 3/39
k
, 9/11/24, 2:25 C170 Data Management
PM Applications
Relational Set Operators
- The relational operators have the property of closure; the use of
relational algebra operators on existing relations produces new
relations.
- SELECT (Restrict):
- SELECT, also known as RESTRICT, is referred to as a unary operator
because it only uses one table as input.
-
- PROJECT
- PROJECT yields all values for selected attributes. It is also a unary
operator, accepting only one table as input.
-
- UNION
- UNION combines all rows from two tables, excluding duplicate rows.
To be used in the UNION, the tables must have the same attribute
characteristics. The
about:blan 4/39
k
