CH 6- Transforming Data Models into Database Designs

Question 1

database design

Answer 1

-a set of database specifications that can actually be implemented as a database in a specific DB MS product.

Question 2

systems analysis and design stages

Answer 2

– Conceptual design (conceptual schema)
– Logical design (logical schema)
– Physical design (physical schema)

Question 3

Transforming a Data Model into a Database Design (steps)

Answer 3

1. Create a table for each entity
2. Create relationships
3. Specify Minimum cardinality

Question 4

1. Create a table for each entity

Answer 4

– 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

Question 5

3. Specify Minimum cardinality

Answer 5

– O-O relationships
– M-O relationships
– O-M relationships
– M-M relationships

Question 6

Selecting the Primary Key

Answer 6

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

Question 7

Specify Column Properties: Null Status

& Alternate Keys

Answer 7

• 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

Question 8

default value

Answer 8

-the value supplied by the DBMS when a new row is created.

Question 9

Data constraints

Answer 9

1) Domain constraints
2) Range constraints
3) Interrelation constraints
4) Interrelation constraints

Question 10

Domain constraints

Answer 10

-limit column values to a particular set of values.

Question 11

Range constraints

Answer 11

-limit values to a particular interval of values (within a given range)

Question 12

Interrelation

Answer 12

limit a column’s values in comparison to values in other columns in the same table.

Question 13

Interrelation constraints

Answer 13

limit a column’s values in comparison to values in other columns in other tables (referential integrity constraints on foreign keys).

Question 14

1:1 Strong Entity Relationship

Answer 14

Place the primary key of one entity in the other entity

as a foreign key.

Question 15

1-N Strong Entity Relationships

Answer 15

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

Question 16

N:M Strong Entity Relationships

Answer 16

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.

Question 17

intersection table

Answer 17

– 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

Question 18

association table

Answer 18

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

Question 19

Representing Ternary and Higher-Order

Relationships

Answer 19

• 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

Question 20

Design for Minimum Cardinality

Answer 20

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.

Question 21

cascading update

Answer 21

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

Question 22

cascading delete

Answer 22

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

Question 23

trigger

Answer 23

-module of code that is invoked by the DBMS
when specific events occur

Question 24

Actions for M-O Relationships

Answer 24

• 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

Question 25

Actions for O-M Relationships

Answer 25

• The DBMS does not provide much help.

* Triggers or other application codes will need to be written

Question 26

Actions for M-M Relationships

Answer 26

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