Chapter 17 - Methodology-Logical Database Design for the Relational Model
mandatory participation on one side of 1:1 relationship
"Optional" is parent, primary key should be posted to child (mandatory)
Mapping strong entity types
"Regular" entities: - Simple - Composite - Multi-valued
One-to-one (1:1) binary relationship types
- mandatory participation on both sides of 1:1 relationship - mandatory participation on one side of 1:1 relationship - optional participation on both sides of 1:1 relationship.
Types of integrity constraints
- required data (when an entity must contain valid data) - attribute domain constraints (legal values) - multiplicity (a branch has many staff and a member of staff works at a single branch) -entity integrity (primary key can not be null) - referential integrity (if the foreign key contains a value, that value must refer to an existing tuple in the parent relation (candidate key)) - general constraints
Relation structure types
- strong entity types - weak entity types - one-to-many (1:*) binary relationship types - one-to-one (1:1) binary relationship types - one-to-one (1:1) recursive relationship types - superclass/subclass relationship types - many-to-many (*:*) binary relationship types - complex relationship types - multi-valued attributes.
Deriving relations for logical data model results in
A relational schema
Three phases of database design:
Conceptual Logical Physical
This phase of database design is primarily concerned with developing the ER diagram
Conceptual (ER = Entity Relationship)
The logical design is said to have this with the conceptual design....
Correspondence with E-R Model Relations (tables) correspond with entity types and with many-to-many relationship types Rows correspond with entity instances and with many-to-many relationship instances Columns correspond with attributes NOTE: The word relation (in relational database) is NOT the same as the word relationship (in E-R model)
logical data model includes
ER diagram(s), relational schema, and supporting documentation such as the data dictionary, which is produced throughout the development of the model.
functional dependencies
Indicate important relationships between the attributes of a relation. It is those functional dependencies and the primary key for each relation that are used in the process of normalization.
Relation anomalies
Insertion (adding new rows forces user to create duplicate date) Deletion (deleting rows may cause a loss of data that would be needed for other future rows) Modification (changing data in a row forces changes to other rows because of duplication) *Normalization is a method to remove anomalies
Requirements for a table to qualify as a relation:
It must have a unique name. Every attribute value must be atomic (not multivalued, not composite). Every row must be unique (can't have two rows with exactly the same values for all their fields). Attributes (columns) in tables must have unique names. The order of the columns must be irrelevant. The order of the rows must be irrelevant.
This phase of database design is primarily concerned transforming the ER diagram into relations
Logical
asterisk:asterisk is
Many to many
(1:*)
One-to-many "One" is parent, primary key should be posted to child
optional participation on both sides of 1:1 relationship
Parent/child assignment arbitrary
global logical data model
Represents the data requirements for all user views
local logical data model
Represents the data requirements of one or more but not all user views of a database (Step 2) Build a logical data model based on the conceptual data model for each user view of the enterprise, and then validate the model using the technique of normalization and against the required transactions.
Steps in Building Logical Data Model
Step 2.1 Derive relations for logical data model Step 2.2 Validate relations using normalization Step 2.3 Validate relations against user transactions Step 2.4 Check integrity constraints Step 2.5 Review logical data model with user Step 2.6 Merge logical data models into global data model (optional step) Step 2.7 Check for future growth
Integrity constraints are
The constraints that we wish to impose in order to protect the database from becoming incomplete, inaccurate, or inconsistent
Logical database design
The process of constructing a model of the data used in an enterprise based on a specific data model but independent of a particular DBMS and other physical considerations.
relational schema validation
The structure of the relational schema is validated: - using normalization - then checked to ensure that the relations are capable of supporting the transactions given in the users' requirements specification - next check whether all important integrity constraints are represented by the logical data model - then validated by the users to ensure that they consider the model to be a true representation of the data requirements of the enterprise
The purpose of normalization is
To ensure that the set of relations has a minimal yet sufficient number of attributes necessary to support the data requirements of the enterprise with minimal redundancy The objective of this step is to ensure that each relation derived from the logical data model is in at least Boyce-Codd Normal Form (BCNF). [Most just validate to 3rd normal form, 1 normal - remove repeating groups, 2nd normal - remove partial dependencies, 3rd normal - remove transitive dependencies]
Foreign Key
an attribute (possible composite) in a relation of a database that serves as the primary key of another relation in the same database
mandatory participation on both sides of 1:1 relationship
combine the entities involved into one relation and choose one of the primary keys of the original entities to be the primary key of the new relation, while the other (if one exists) is used as an alternate key
database design methodology main phases:
conceptual, logical, and physical database design
Relational modeling uses these to maintain relationships
primary keys and foreign keys Primary keys are typically the unique identifier noted on the conceptual model Foreign keys are the primary key of another entity to which an entity has a relationship Composite keys are primary keys that are made of more than one attribute Weak entities Associative entities
relational schema
set of relations
In deciding where to post (or place) the foreign key attribute(s), we must first identify
the "parent" and "child" entities involved in the relationship