Transcript Chapter 5

Chapter 6 – Part 1
Transforming Data Models
into Database Designs
Slide 1
Contents
A. Computer Company Problem
B. Solution
Slide 2
A. Computer Company Problem
 ProgLtd is a company that places computer programmers at
temporary jobs. They keep track on each programmer: the
programmer's identification number, the programmer's last
name, the programmer's first name, the programmer's rank
(Junior, Senior ...), gender, his/her hourly rate and what
languages the programmer knows. Beside, each programmer
has been issued a badge which is used for enter their office.
On each badge has following information: badge’s code, the
programmer's identification number, issued date.
Slide 3
 ProgLtd deals with clients who may require programmers
who know a particular programming language and may
request programmers who are experienced in such a
programming. Once a programmer is assigned a temporary
job, ProgLtd would like to keep track of when he/she started
the job, the total number of hours he/she is expected to spend
at job and the date he/she finished the job. That is in addition
to the client name and phone number where the temp has been
assigned.
 Let’s design DB diagram for above requirements
Slide 4
B. Solutions
1. Logical Analysis
2. Physical Analysis
Slide 5
1. Logical Analysis
Slide 6
2. Physical Analysis
2.1. Steps for Transforming a Data Model into a Database
Design.
2.2. Create Table for Each Entity.
2.3. Create Relationships.
2.4. Design for Minimum Cardinality.
2.5. Physical Diagram.
Slide 7
2.1. Steps for Transforming a Data Model
into a Database Design.
Slide 8
2.2. Create Table for Each Entity
2.2.1. Create a Table for Each Entity.
2.2.2. Select the Primary Key.
2.2.3. Specify Candidate (Alternate) Keys.
2.2.4. Specify Column Properties.
Slide 9
2.2.1. Create a Table for Each Entity
Slide 10
2.2.2. Select the Primary Key
 The ideal primary key is short, numeric and fixed
 Surrogate keys meet the ideal, but have no meaning to users
Slide 11
2.2.3. Specify Candidate (Alternate) Keys
 The terms candidate key and alternate key are synonymous
 Candidate keys are alternate identifiers of unique rows in a
table
 ERwin uses AKn.m notation, where n is the number of the
alternate key, and m is the column number in that alternate
key
Slide 12
2.2.4. Specify Column Properties
2.2.4.1. Specify Data Type.
2.2.4.2. Specify Default Value.
2.2.4.3. Specify Data Constraints.
Slide 13
2.2.4.1. Specify Data Type
2.2.4.1.1. SQL Server Data Types.
2.2.4.1.2. Data Types on Table.
Slide 14
2.2.4.1.1. SQL Server Data Types
Slide 15
2.2.4.1.2. Data Types on Table
Slide 16
2.2.4.2. Specify Default Value
A default value is the value supplied by the DBMS when a
new row is created
Example: Default value of PRO_Hourly_Rate is 15$
Slide 17
2.2.4.3. Specify Data Constraints
Data constraints are limitations on data values:
 Domain constraint - Column values must be in a given set of
specific values
 Range constraint - Column values must be within a given
range of values
 Intrarelation constraint – Column values are limited by
comparison to values in other columns in the same table
 Interrelation constraint - Column values are limited by
comparison to values in other columns in other tables
[Referential integrity constraints on foreign keys]
Slide 18
2.3. Create Relationships
2.3.1. 1:1 Entity Relationships
2.3.2. 1:N Entity Relationships
2.3.3. N:M Entity Relationships
Slide 19
2.3.1. 1:1 Entity Relationships
Place the key of one entity in the other entity as a
foreign key:
 Either design will work – no parent, no child
 Minimum cardinality considerations may be important:
O-M will require a different design that M-O, and one design will be very
preferable.
Slide 20
2.3.2. 1:N 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”
Slide 21
2.3.3. N:M Entity Relationships
 In an N:M strong entity relationship there is no place for the
foreign key in either table
 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
Slide 22
Slide 23
2.4. Design for Minimum Cardinality
2.4.1.
2.4.2.
2.4.3.
2.4.4.
Slide 24
Types of minimum cardinality.
Cascading Updates and Deletes.
Actions to Apply to Enforce Minimum Cardinality.
Implementing Actions.
2.4.1. Types of 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.
Slide 25
2.4.2. Cascading Updates and Deletes
 A cascading update occurs 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.
 A cascading delete occurs 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.
Slide 26
2.4.3. Actions to Apply to Enforce
Minimum Cardinality
Slide 27
2.4.4. Implementing Actions
2.4.4.1. Implementing Actions for M-O Relationships.
2.4.4.2. Implementing Actions for O-M Relationships.
2.4.4.3. Implementing Actions for M-M Relationships.
2.4.4.4. What is trigger?
Slide 28
2.4.4.1. Implementing 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.
Slide 29
2.4.4.2. Implementing Actions for O-M
Relationships
 The DBMS does not provide much help.
 Triggers or other application code will need to be written.
Slide 30
2.4.4.3. Implementing Actions for M-M
Relationships
 The DBMS does not provide much help.
 Complicated and careful application programming will be
needed.
Slide 31
2.4.4.4. What is trigger?
 Application programming uses SQL embedded in triggers,
stored procedures, and other program code to accomplish
specific tasks.
 A trigger is a stored program that is executed by the DBMS
whenever a specified event occurs on a specified table or
view.
 Triggers are used to enforce specific minimum cardinality
enforcement actions not otherwise programmed into the
DBMS.
Slide 32
2.5. Physical Diagram
Slide 33
Slide 34