CH 6- Transforming Data Models into Database Designs Flashcards
database design
-a set of database specifications that can actually be implemented as a database in a specific DB MS product.
systems analysis and design stages
– Conceptual design (conceptual schema)
– Logical design (logical schema)
– Physical design (physical schema)
Transforming a Data Model into a Database Design (steps)
- Create a table for each entity
- Create relationships
- Specify Minimum cardinality
- Create a table for each entity
– Specify the primary key (consider surrogate keys, as appropriate) – Specify alternate keys – Specify properties for each column: ▪ Null status ▪ Data type ▪ Default value (if any) ▪ Data constraints (if any) – Verify normalization
- Specify Minimum cardinality
– O-O relationships
– M-O relationships
– O-M relationships
– M-M relationships
Selecting the Primary Key
• The ideal primary key is short, numeric, and fixed.
• Surrogate keys meet the ideal, but have two
disadvantages to users:
– They have no meaning to users (an ID used as a
surrogate key may give no indication of the related
table)
– When data is shared with among different databases
(an ID used in one database may have no meaning to
a user in another database)
Specify Column Properties: Null Status
& Alternate Keys
• Null status indicates whether or not the value of the
column can be NULL.
———————————————————————
•The terms candidate key and alternate key are
synonymous.
•Candidate keys are alternate identifiers of unique
rows in a table
default value
-the value supplied by the DBMS when a new row is created.
Data constraints
1) Domain constraints
2) Range constraints
3) Interrelation constraints
4) Interrelation constraints
Domain constraints
-limit column values to a particular set of values.
Range constraints
-limit values to a particular interval of values (within a given range)
Interrelation
limit a column’s values in comparison to values in other columns in the same table.
Interrelation constraints
limit a column’s values in comparison to values in other columns in other tables (referential integrity constraints on foreign keys).
1:1 Strong Entity Relationship
Place the primary key of one entity in the other entity
as a foreign key.
1-N Strong Entity Relationships
• Place the primary key of the table on the one side of the relationship into the table on the many side of the
relationship as the foreign key.
• The one side is the parent table and the many side is the child table, so “place the key of the parent in the child.”
N:M Strong Entity Relationships
there is no place for the foreign key in either table.
– A COMPANY may supply many PARTs
– A PART may be supplied by many COMPANYs
- The solution is to create an intersection table that stores data about the corresponding rows from each entity.
- The intersection table consists only of the primary keys of each table which form a composite primary key.
• Each table’s primary key becomes a foreign key linking back to that
table.
intersection table
– Holds the relationships between two strong entities in an N:M relationship
– Contains only the primary keys of the two entities:
▪ As a composite primary key
▪ As foreign keys
association table
Has all the characteristics of an intersection table
– PLUS it has one or more columns of attributes specific to the associations of the other two entities
Representing Ternary and Higher-Order
Relationships
• Ternary and higher-order relationships may be constrained by the binary relationship that comprise them.
– MUST constraint—requires that one entity must be
combined with another entity in the ternary (or higher-order) relationship
– MUST NOT constraint—requires that certain combinations of two entities are not allowed to occur in the ternary (or higher-order) relationship
– MUST COVER constraint—a binary relationship specifies all combinations of two entities that must appear in the ternary (or higher-order) relationship
Design for Minimum Cardinality
Relationships can have the following types of minimum
cardinality:
– O-O: parent optional and child optional
– M-O: parent mandatory and child optional
– O-M: parent optional and child mandatory
– M-M: parent mandatory and child mandatory
• We will use the term action to mean a minimum
cardinality enforcement action.
• No action needs to be taken for O-O relationships.
cascading update
when a change to the parent’s primary key is applied to the child’s foreign key.
– Surrogate keys never change and there is no need for cascading updates when using them
cascading delete
when associated child rows are deleted along with the deletion of a parent row.
– For strong entities, generally do not cascade deletes
– For weak entities, generally do cascade deletes
trigger
-module of code that is invoked by the DBMS when specific events occur
Actions for M-O Relationships
• Make sure that:
– Every child has a parent
– Operations never create orphans
• The DBMS will enforce the action as long as:
– Referential integrity constraints are properly defined
– The foreign key column is NOT NULL
Actions for O-M Relationships
- The DBMS does not provide much help.
* Triggers or other application codes will need to be written
Actions for M-M Relationships
• The worst of all possible worlds:
– Especially in strong entity relationships
– In relationships between strong and weak entities the
problem is often easier when all transactions are
initiated from the strong entity side
• Complicated and careful application programming will be
needed.
