Transcript Chapter 2 - Kinnis Gosha, PhD

Chapter 2
DATABASE DESIGN AND
DEVELOPMENT: A VISUAL
APPROACH
Raymond Frost – John Day – Craig Van Slyke
Chapter 2
Relational Theory
1
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
The Relational Model
• The relational model is perhaps the
simplest and most intuitive data model
ever developed.
• The entire model is based upon tables
with rows and columns.
• Tables are called relations, hence the term
relational model is used.
2
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Equivalent Terms
Exhibit 2-1: Relational Model-Equivalent Terms
Database Design and Development: A Visual Approach © 2006 Prentice Hall
3
Chapter 2
Entity Relationship Diagrams
One-to-Many Relationships: Parent vs. Child
The crows foot points to the child.
One customer places many orders;
Each order belongs to one customer.
One employee has many dependents;
Each dependent belongs to one employee.
Exhibit 2-2: One-to-Many Relationships
Database Design and Development: A Visual Approach © 2006 Prentice Hall
4
Chapter 2
Many-to-Many Relationships
A many-to-many relationship is represented
by creating an associative entity.
Exhibit 2-3: Many-to-Many Relationships
Database Design and Development: A Visual Approach © 2006 Prentice Hall
5
Chapter 2
Attributes
Exhibit 2-4: Attributes
Database Design and Development: A Visual Approach © 2006 Prentice Hall
6
Chapter 2
Primary Keys
A primary key uniquely identifies
each record in a table.
•
•
•
•
Unique
Minimal
Not Null
Nonupdateable
7
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Unique But NOT Minimal
Primary Keys
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-5: Unique But NOT Minimal
Primary Keys
8
Chapter 2
Unique and Minimal Primary Keys
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-6: Email Is a Unique AND Minimal
Primary Key
9
Chapter 2
Primary Keys Formed from Existing
Fields
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-7: Primary Keys Formed from
Existing Fields
10
Chapter 2
Computer-Generated Primary Keys
Computer-generated keys are often formed by adding one to the last
number assigned.
Primary keys might also be computer generated to establish a singlefield primary key, as opposed to one formed of multiple fields.
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-8: Computer-Generated Primary
Keys
11
Chapter 2
Foreign Keys Create Relationships
A parent table reproduces its primary key values in every child table to which it connects.
Because these reproduced values originate outside of the child table, they are called foreign keys.
Foreign keys link the related records between parent and child tables. A foreign key is placed in
the child table (following the crow’s foot).
Foreign keys are boldfaced and follow the naming convention of
PARENT_TABLENAME$parent_fieldname (Fleming and van Holle, 1998).
Exhibit 2-9: Foreign Keys Create Relationships
Database Design and Development: A Visual Approach © 2006 Prentice Hall
12
Chapter 2
Weak Entity
A dependent table (also called a weak entity) is a child table
that requires a parent table for identification.
Exhibit 2-10: Weak Entity
Database Design and Development: A Visual Approach © 2006 Prentice Hall
13
Chapter 2
Primary Key for an Associative
Entity
An associative table is a child of two parent tables that
are in a many-to-many relationship.
An order may list many products and a given product may
appear on multiple orders.
This many-to-many relationship between order
and product is represented by the associative table
LINEITEM.
To form its primary key, LINEITEM uses both ORDER$id
and PRODUCT$id.
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-11: Primary Key for an Associative
Entity
14
Chapter 2
Data Integrity
• Entity integrity: Every table must have a
valid primary key.
• Referential integrity: No unmatched
foreign key values. Referential integrity
requires that foreign key values match
existing primary key values in the table to
which they refer.
15
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Exercise: Find the Entity and
Referential Integrity Violations
Exhibit 2-12: Find Two Entity Integrity and Three
Referential Integrity Violations
Database Design and Development: A Visual Approach © 2006 Prentice Hall
16
Chapter 2
Answers:
= entity integrity violations
=referential integrity violations
17
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
First Normal Form (1NF)
A database is normalized when each field holds just one value, and each
table has the correct set of fields.
A table is said to be in first normal form (1NF) when each field in that
table contains single values only.
Exhibit 2-13: First Normal Form (1NF)
Database Design and Development: A Visual Approach © 2006 Prentice Hall
18
Chapter 2
Types of Databases
Hierarchical: Represents a database as a tree-structured hierarchy, similar to the
folder system on a computer.
Network: Represents a database as a network of connected tables. The major
difference between a network and a relational database is that the relational
database has foreign keys to make connections between tables, whereas the
network database uses physical pointers to connect tables.
Relational: A relational database is the easiest system in which to develop and
maintain a database.
Object oriented: The first type of database to allow programs (methods), and not
just data, to be stored in the database. They were also the first databases to allow
data objects to inherit structures from other data objects. Over time, relational
products have become more object oriented—incorporating both stored procedures
(programs) and inheritance—thereby eliminating the competitive advantage of
object-oriented systems.
19
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Advantages of Relational
Databases
• Reliance on logical, rather than physical,
links between related records
• Use of a fourth-generation language
(4GL)
• Allowance for a high degree of data
independence
20
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Views
When developers create tables in a database, they are called base tables.
A view, by contrast, is a subset derived from the base tables. Views enable
the DBA to restrict the portion of the database visible to each user.
The member_email_view contains only the address, first_name, and last_name
fields, whereas the member_phone_view contains only the first_name, last_name,
and telephone fields.
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-14: Views Used to Restrict Access
and Rename Columns
21
Chapter 2
Practice: Draw the Crow’s Foot
A consultant does many projects; each project is done by one consultant.
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-15: ER Diagram for Practice
Exercise 1
22
Chapter 2
Exercise: Draw a New ERD
Create an associative entity, ENROLLMENT, to represent the many-to-many
relationship.
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-16: ER Diagram for Practice
Exercise 2
23
Chapter 2
Exercise: Select a Primary Key
Exhibit 2-17: Table for Practice Exercise 3
Database Design and Development: A Visual Approach © 2006 Prentice Hall
24
Chapter 2
Exercise: Identify the Foreign Key
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Exhibit 2-18: ER Diagram for Practice
Exercise 4
25
Chapter 2
Exercise: Identify the Entity and
Referential Integrity Violations
There is one Entity Integrity and one Referential Integrity violation
Exhibit 2-19: Tables for Practice Exercise 5
Database Design and Development: A Visual Approach © 2006 Prentice Hall
26
Chapter 2
Printable Slide Versions
The following slides contain non-animated
versions of the previous animated slides
for handouts.
27
Database Design and Development: A Visual Approach © 2006 Prentice Hall
Chapter 2
Many-to-Many Relationships
A many-to-many relationship is represented
by creating an associative entity.
Exhibit 2-3: Many-to-Many Relationships
Database Design and Development: A Visual Approach © 2006 Prentice Hall
28
Chapter 2
Attributes
Exhibit 2-4: Attributes
Database Design and Development: A Visual Approach © 2006 Prentice Hall
29