9/11/24, 2:12 C170
PM Notes
Database Design
A Logical View of Data
Tables and Their Characteristics
• A table is a perceived two-dimensional structure of rows and columns
• A table is also called a relation
• A table is a persistent representation of a logical relation
• A table contains a group of related entity occurrences
• Table / entity
set Keys
• Key – consists of one or more attributes that determine other attributes
• Dependencies
o Determination is the state in which knowing the value of one attribute makes it possible to determine
the value of another.
o Determinant (key) – the attribute whose value determines another
o Dependent – the attribute whose value is determined by the other attribute
o Full functional dependence – used to refer to functional dependencies in which the entire collection
of attributes in the determinant is necessary for the relationship
• Types of Keys
o Composite key – composed of more than one attribute
o Key attribute – an attribute that is a part of a key
o Superkey – key that can uniquely identify any row in the table
o Candidate key – a minimal superkey , a superkey without any unnecessary attributes
o Entity integrity – the condition in which each row in the table has its own unique identity
▪ all values in the primary key are unique
▪ no key attribute in the primary key can contain a null
o Null – absence of any data value – never allowed in any part of the primary key
o Foreign key (FK) – the primary key of one table that has been placed into another table to create
a common attribute
o Referential integrity – the condition in which every reference to an entity instance by another
entity instance is valid.
o Secondary key – a key that is used strictly for data retrieval
purposes The Data Dictionary and the System Catalog
Data dictionary – provides a detailed description of all tables in the database created by the user and designer.
System Catalog – can be described as a detailed system data dictionary that describes all objects within the database,
including data about table names, table’s creator and creation data, number of columns in each table, data type
corresponding to each column, index filenames, index creators, authorized users and access priveleges.
Homonyms – are similar sounding words with different meaning (boar and bore)
1:1 relationship
1:M relationship
M:N relationship
Composite Entity – bridge entity – associative entity – used to facilitate M:N relationships
Linking table – the implementation of a composite entity
Cardinality – denotes how many instances of one object are related to instances of another object.
Modality – denotes an instance of a specific entity is optional or mandatory in a relationship
Data Redundancy Revisted
Index – an orderly arrangement used to logically access rows in a table (ordered arrangement of keys and pointers
• Index key – in effect the index’s reference point.
• Unique key – the index key can have only one pointer value (row) associated with it.
Codd’s Relational Database rules
about:blan 1/
k 5
, 9/11/24, 2:12 C170
PM Notes
• Information – all information in a relational database must be logically represented as column values in
rows within tables
• Guaranteed access – every value in a table is guaranteed to be accessible through a combination of table
name, primary key value, and column name
• Systematic treatment of nulls – nulls must be represented and treated in a systematic way, independent of
data type
• Dynamic online catalog based on the relational model – the metadata must be stored and managed as
ordinary data – tables within the database
• Comprehensive data sublanguage – the relational database may support many languages; however it must
support one well-defined, declarative language as well as data data definition, view definition, data
manipulation, integrity constraints, authorization, and transaction management
• View updating – any view that is theoretically updatable must be updatable through the system
• Highlevel insert, update, and delete – the database must support set-level inserts, updates and deletes
• Physical data idependence – application programs and ad hoc facilities are logically unaggected when
physical access methods or storage structures are changed.
• Logical data independence – application programs and ad hoc facilities are logically unaffected when
changes are made to the table structures that preserve the original table values
• Integrity independence – all relational integrity constraints must be definable in the relational language
and stored in the system catalog, not at the application level
• Distribution independence – the end users and application programs are unaware of and unaffected by the
data location
• Nonsubversion – if the system supports low-level access to the data, users must not be allowed to bypass
the integrity rules of the database
• Rule zero – all preceding rules are based on the notion that to be considered relational, a database must use
its relational facilities exclusively for management
ER Relationship Model (ERM)
Entities –an object of interest to the end user (usually refers to entity set)
Attributes – characteristics of entities
• Required Attribute – an attribute that must have a value
• Optional attribute – an attribute that does not require a value
• Domains – a domain is a set of possible values for a given attribute. (ex. (ff,4) for GPA ff – 4.ff
Identifiers (Primary Key) – one or more attributes that uniquely identify each entity instance.
Relational Schema – a frequently used shorthand notation for the table structure
Composite Identifiers – a primary key composed of more than one attribute
Composite and Simple attributes
• Composite Attribute – is an attribute that can be further subdivided to yield additional attributes
• Single-Valued Attributes – an attribute that can have only a single value
• Multivalued attributes – attributes that can have many values
• Derived attributes – an attribute whose value is calculated (derived) from other attributes
Relationships
• Participants – entities that participate in a relationship
Connectivity and Cardinality
• Connectivity – used to describe the relationship classification
• Cardinality – expresses the minimum and maximum number of entity occurrences associated with one
occurrence of the related entitity
Existence Dependece – if an entitiy can exist in the database only when it is associated with another related entity
occurrence
Existence-Independent – if an entity can exist apart from of its related entities
• Strong Entity
about:blan 2/
k 5
PM Notes
Database Design
A Logical View of Data
Tables and Their Characteristics
• A table is a perceived two-dimensional structure of rows and columns
• A table is also called a relation
• A table is a persistent representation of a logical relation
• A table contains a group of related entity occurrences
• Table / entity
set Keys
• Key – consists of one or more attributes that determine other attributes
• Dependencies
o Determination is the state in which knowing the value of one attribute makes it possible to determine
the value of another.
o Determinant (key) – the attribute whose value determines another
o Dependent – the attribute whose value is determined by the other attribute
o Full functional dependence – used to refer to functional dependencies in which the entire collection
of attributes in the determinant is necessary for the relationship
• Types of Keys
o Composite key – composed of more than one attribute
o Key attribute – an attribute that is a part of a key
o Superkey – key that can uniquely identify any row in the table
o Candidate key – a minimal superkey , a superkey without any unnecessary attributes
o Entity integrity – the condition in which each row in the table has its own unique identity
▪ all values in the primary key are unique
▪ no key attribute in the primary key can contain a null
o Null – absence of any data value – never allowed in any part of the primary key
o Foreign key (FK) – the primary key of one table that has been placed into another table to create
a common attribute
o Referential integrity – the condition in which every reference to an entity instance by another
entity instance is valid.
o Secondary key – a key that is used strictly for data retrieval
purposes The Data Dictionary and the System Catalog
Data dictionary – provides a detailed description of all tables in the database created by the user and designer.
System Catalog – can be described as a detailed system data dictionary that describes all objects within the database,
including data about table names, table’s creator and creation data, number of columns in each table, data type
corresponding to each column, index filenames, index creators, authorized users and access priveleges.
Homonyms – are similar sounding words with different meaning (boar and bore)
1:1 relationship
1:M relationship
M:N relationship
Composite Entity – bridge entity – associative entity – used to facilitate M:N relationships
Linking table – the implementation of a composite entity
Cardinality – denotes how many instances of one object are related to instances of another object.
Modality – denotes an instance of a specific entity is optional or mandatory in a relationship
Data Redundancy Revisted
Index – an orderly arrangement used to logically access rows in a table (ordered arrangement of keys and pointers
• Index key – in effect the index’s reference point.
• Unique key – the index key can have only one pointer value (row) associated with it.
Codd’s Relational Database rules
about:blan 1/
k 5
, 9/11/24, 2:12 C170
PM Notes
• Information – all information in a relational database must be logically represented as column values in
rows within tables
• Guaranteed access – every value in a table is guaranteed to be accessible through a combination of table
name, primary key value, and column name
• Systematic treatment of nulls – nulls must be represented and treated in a systematic way, independent of
data type
• Dynamic online catalog based on the relational model – the metadata must be stored and managed as
ordinary data – tables within the database
• Comprehensive data sublanguage – the relational database may support many languages; however it must
support one well-defined, declarative language as well as data data definition, view definition, data
manipulation, integrity constraints, authorization, and transaction management
• View updating – any view that is theoretically updatable must be updatable through the system
• Highlevel insert, update, and delete – the database must support set-level inserts, updates and deletes
• Physical data idependence – application programs and ad hoc facilities are logically unaggected when
physical access methods or storage structures are changed.
• Logical data independence – application programs and ad hoc facilities are logically unaffected when
changes are made to the table structures that preserve the original table values
• Integrity independence – all relational integrity constraints must be definable in the relational language
and stored in the system catalog, not at the application level
• Distribution independence – the end users and application programs are unaware of and unaffected by the
data location
• Nonsubversion – if the system supports low-level access to the data, users must not be allowed to bypass
the integrity rules of the database
• Rule zero – all preceding rules are based on the notion that to be considered relational, a database must use
its relational facilities exclusively for management
ER Relationship Model (ERM)
Entities –an object of interest to the end user (usually refers to entity set)
Attributes – characteristics of entities
• Required Attribute – an attribute that must have a value
• Optional attribute – an attribute that does not require a value
• Domains – a domain is a set of possible values for a given attribute. (ex. (ff,4) for GPA ff – 4.ff
Identifiers (Primary Key) – one or more attributes that uniquely identify each entity instance.
Relational Schema – a frequently used shorthand notation for the table structure
Composite Identifiers – a primary key composed of more than one attribute
Composite and Simple attributes
• Composite Attribute – is an attribute that can be further subdivided to yield additional attributes
• Single-Valued Attributes – an attribute that can have only a single value
• Multivalued attributes – attributes that can have many values
• Derived attributes – an attribute whose value is calculated (derived) from other attributes
Relationships
• Participants – entities that participate in a relationship
Connectivity and Cardinality
• Connectivity – used to describe the relationship classification
• Cardinality – expresses the minimum and maximum number of entity occurrences associated with one
occurrence of the related entitity
Existence Dependece – if an entitiy can exist in the database only when it is associated with another related entity
occurrence
Existence-Independent – if an entity can exist apart from of its related entities
• Strong Entity
about:blan 2/
k 5
