Transcript LIS 558 - Week 4 Entity Relationship Modeling II

Database Management Systems & Programming
LIS 558 - Week 4
Entity Relationship Modeling II
Faculty of Information & Media Studies
Summer 2000
Class Outline

Entity-Relationship Modeling Continued
E-R Modeling Exercises
E-R Model Transformation

BREAK






E-R Model Transformation Exercises
Common database data types
Guidelines for designing tables
Common database design flaws
Weak Entities



An entity that is existence dependent on another entity
(mandatory participation) and
Has a primary key that is partially or totally derived from
the parent entity of the relationship
In ERD depict weak entity using double lines
COURSE
contains
CourseID, ...
EMPLOYEE
EmployeeID, ...
SECTION
CourseID, SectionID, ...
has a
DEPENDENT
EmployeeID, DependentID, ...
Illustration of the Weak Relationship Between
DEPENDENT and EMPLOYEE
IS-A Supertypes-Subtypes


A subtype entity is an entity that contains a set of optional
attributes of the supertype entity and inherits its attributes
and its relationships from the supertype entity
If the supertype entity is related to mutually exclusive subtype
entities (can belong to only one subtype), indicate with 1; if
subtypes are overlapping (can belong to more than one), use m
supertype
CLIENT
1
INDIVIDUAL
CORPORATE
CONTRACT
subtype
PRODUCTS
The same identifier (e.g., ClientID) is
used for the supertype as well as subtype.
m
SERVICES
Steps to E-R Modeling
1. Identify entities
2. Identify relationships
3. Determine relationship type
4. Determine level of participation
5. Assign an identifier for each entity
6. Draw completed E-R diagram
7. Deduce a set of preliminary skeleton tables along
with a proposed primary key for each table (using
cases provided)
8. Develop a list of all attributes of interest (not
already listed and systematically assign each to a
table in such a way to achieve a 3NF design (i.e., no
repeating groups, no partial dependencies, and no
transitive dependencies)
Developing an E-R Diagram
Entities for the Tiny College Database

SCHOOL

COURSE

DEPARMENT

CLASS

EMPLOYEE


PROFESSOR

ENROLL (Bridge between
STUDENT and CLASS)
STUDENT
Developing an E-R Diagram

Tiny College Database
• Each school is composed of several departments.
• The smallest number of departments operated by a
school is one, and the largest number of departments is
indeterminate (N).
• Each department belongs to only a single school.
Developing an E-R Diagram

Tiny College Database
• Each department offers several courses.
Developing an E-R Diagram

Tiny College Database
• A department may offer several sections (classes) of
the same course.
• A 1:M relationship exists between COURSE and
CLASS.
• CLASS is optional to COURSE
Developing an E-R Diagram

Tiny College Database
• Each department has many professors assigned to it.
• One of those professors chairs the department. Only
one of the professors can chair the department.
• DEPARTMENT is optional to PROFESSOR in the
“chairs” relationship.
Developing an E-R Diagram

Tiny College Database
• Each professor may teach up to four classes, each one
a section of a course.
• A professor may also be on a research contract and
teach no classes.
Developing an E-R Diagram

Tiny College Database
• A student may enroll in several classes, but (s)he takes
each class only once during any given enrollment
period.
• Each student may enroll in up to six classes and each
class may have up to 35 students in it.
• STUDENT is optional to CLASS.
Developing an E-R Diagram

Tiny College Database
• Each department has several students whose major is
offered by that department.
• Each student has only a single major and associated
with a single department.
Developing an E-R Diagram

Tiny College Database
• Each student has an advisor in his or her department;
each advisor counsels several students.
• An advisor is also a professor, but not all professors
advise students.
Components of the E-R Model
Exercise Problem
Rob 2.1-7
Exercise Problem - Rob 2.1-7


An EMPLOYEE has only one JOB_CODE, but a
JOB_CODE can be held by many EMPLOYEEs.
An EMPLOYEE can have many BENEFITs, and any
BENEFIT can be assigned to many EMPLOYEEs.
Exercise Problem - Rob 2.1-7
1
JOB
M
is assigned to
EMPLOYEE
1
own s
1
PLAN
M
d esc ribe s
M
BE NE FIT
E-R Modeling I Exercises
(distributed last week)
Complete solutions on
course website
E-R Modeling I - #1
1. Use the following business rules to write all appropriate
connectivities and cardinalities in an E-R diagram:
 A department employs many employees, but each
employee is employed by one department, although some
employees, known as "rovers", are not assigned to any
department;
 A division operates many departments, but each
department is operated by one division;
 An employee may be assigned to many projects and a
project may have many employees assigned to it;
 A project must have at least one employee assigned to it;
 One of the employees manages each department;
 One of the employees runs each division.
Solution to Q1
E-R Modeling I - #2
2. The Hudson Engineering Group (HEG) has contacted you to create a
conceptual model whose application will meet the expected database
requirements for its training program. The HEG administrator gives
you the following description of the training group's operating
environment:
The HEG has twelve instructors and can handle up to thirty trainees
per class. HEG offers five "advanced technology" courses, each of
which may generate several classes. If a class has fewer than ten
trainees in it, it will be canceled. It is, therefore, possible for a course
not to generate any classes during a session. Each class is taught by
one instructor. Each instructor may teach up to two classes or may be
assigned to do research. Each trainee may take up to two classes per
session.
Given this information, do the following:
- Describe the relationships between the entities in terms of
connectivity, cardinality, and existence dependence.
- Draw the E-R diagram for HEG.
Solution to Q2
E-R Modeling II Exercises
(distributed last week)
Complete solutions on
course website
E-R Modeling II - #1
1. Given the following business rules, create the E-R diagram,
incorporating each of the specified relationships:
- A company operates four departments;
- Each department in part (a) employs employees;
- Each of the employees in part b may or may not have one or more
dependents;
- Each employee in part (c) may or may not have an employment
history.
Solution to Q1
E-R Modeling II - #4
4. A large hospital has just hired you to design a technical help
desk database application. The hospital has just standardized their
complement of network PCs by acquiring 500 Pentium III 400 MHz
workstations. Each workstation has an appropriate range of
applications software already loaded.
The purpose of the helpdesk database is to track the problems
that arise (hardware and software), the users who report them,
and the person assigned to resolve the problem. The development
of this helpdesk and the associated database was proposed to
reduce response time, reduce paperwork, centralize problem
resolution, and ensure appropriate follow-up.
Provide a conceptual design for this database application by
creating an entity-relationship diagram.
UserID,...
Solution to Q4
USER
(0,N)
1
ProbID...
reports
(1,1)
ProbID,…
M
PROBLEM
(1,1)
M
HARDWARE
1
(0,1)
is a
assigned to
(0,N)
1
ResolverID,... RESOLVER
SOFTWARE
ProbID,...
E-R Modeling II - #5
5. Design a database that tracks employee’s skills.
Additionally, track various projects and the skills that are required
to perform each project.
Finally, the project must be appropriately assigned.
Include also the employee’s supervisor, dependents, work history
and employee benefits.
Solution to Q5
employee
supervises
1
1
parent of *
M
1
M
M
has *
M
dependents
M
work history
acquires
assigned
N
N
M
given
N
project
skill
M
requires
N
benefits
Steps to E-R ModelTransformation
1. Identify entities
2. Identify relationships
3. Determine relationship type
4. Determine level of participation
5. Assign an identifier for each entity
6. Draw completed E-R diagram
7. Deduce a set of preliminary skeleton tables
along with a proposed primary key for each table
(using cases provided)
8. Develop a list of all attributes of interest (not
already listed and systematically assign each to a
table in such a way to achieve a 3NF design (i.e., no
repeating groups, no partial dependencies, and no
transitive dependencies)
Developing an E-R Diagram

Converting an E-R Model into a Database
Structure
• A painter might paint many paintings. The cardinality is
(1,N) in the relationship between PAINTER and
PAINTING.
• Each painting is painted by one (and only one) painter.
• A painting might (or might not) be exhibited in a
gallery; i.e., the GALLERY is optional to PAINTING.
Developing an E-R Diagram

Summary of Table Structures and Special
Requirements for the ARTIST database
PAINTER(PRT_NUM, PRT_LASTNAME,
PRT_FIRSTNAME, PRT_INITIAL,
PTR_AREACODE, PRT_PHONE)
GALLERY(GAL_NUM, GAL_OWNER,
GAL_AREACODE, GAL_PHONE, GAL_RATE)
PAINTING(PNTG_NUM, PNTG_TITLE,
PNTG_PRICE, PTR_NUM, GAL_NUM)
A Data Dictionary for the ARTIST Database
Developing an E-R Diagram

General Rules Governing Relationships
among Tables
1. All primary keys must be defined as NOT NULL.
2. Define all foreign keys to conform to the following
requirements for binary relationships.
– 1:M Relationship
– Weak Entity
– M:N Relationship
– 1:1 Relationship
Developing an E-R Diagram

1:M Relationships
• Create the foreign key by putting the primary key of
the “one” (parent) in the table of the “many”
(dependent).
• Foreign Key Rules:
Null
On Delete
On Update
If both sides are
MANDATORY
NOT NULL
RESTRICT
CASCADE
If both sides are
OPTIONAL
NULL
ALLOWED
SET NULL
CASCADE
If one side is
OPTIONAL and
the other
MANDATORY
NULL
ALLOWED
SET NULL
or
RESTRICT
CASCADE
Developing an E-R Diagram

Weak Entity
• Put the key of the parent table (strong entity) in the
weak entity.
• The weak entity relationship conforms to the same
rules as the 1:M relationship, except foreign key
restrictions:
NOT NULL
ON DELETE CASCADE
ON UPDATE CASCADE

M:N Relationship
• Convert the M:N relationship to a composite (bridge)
entity consisting of (at least) the parent tables’
primary keys.
Developing an E-R Diagram

1:1 Relationships
• If both entities are in mandatory participation in the
relationship and they do not participate in other
relationships, it is most likely that the two entities
should be part of the same entity.
Developing an E-R Diagram

Case 1: M:N, Both Sides MANDATORY
Developing an E-R Diagram

Case 2: M:N, Both Sides OPTIONAL
Developing an E-R Diagram

Case 3: M:N, One Side OPTIONAL
Developing an E-R Diagram

Cases 1-3: M:N
PATIENT
M
prescribed
N
DRUG
PATIENT (PATIENT_ID, PATIENT_LNAME, PATIENT_PHYSICIAN,...)
DRUG (DRUG_ID, DRUG_NAME, DRUG_MANUFACTURER, ...)
PRESCRIBE(PATIENT_ID, DRUG_ID, DOSAGE, DATE…)
NOTE: The relationship may have its own attributes.
Example of decomposing entities
with a binary M:N relationship
Students:Classes have an M:N relationship,
therefore, decompose to three tables.
bridge table
Developing an E-R Diagram

Case 4: 1:M, Both Sides MANDATORY
EMPLOYEE
1
checks
M
PRODUCT
EMPLOYEE (EMP_ID, EMP_DEPT, …)
PRODUCT (PROD_ID, PROD_NAME, PROD_%FIBRE, EMP_ID... )
Developing an E-R Diagram

Case 5: 1:M, Both Sides OPTIONAL
PHYSIOTHERAPIST
1
has
M
CLIENTS
PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...)
CLIENT (CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP#, …PT_ID)
Developing an E-R Diagram

Case 6: 1:M, Many Side OPTIONAL, One Side
MANDATORY
MACHINE
1
contains
M
PARTS
MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...)
PART (PART_ID, PART_NAME, PART_CATEGORY, …, MACH_ID)
Developing an E-R Diagram

Case 7: 1:M, One Side OPTIONAL, One Side
MANDATORY
BAND
1
accepts
M
MUSICIAN
BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...)
MUSICIAN (MUSICIAN_ID, MUSICIAN_INSTRUMENT, … BAND_ID)
Developing an E-R Diagram

Case 8: 1:1, Both Sides MANDATORY
Developing an E-R Diagram

Case 8: 1:1, Both Sides MANDATORY
PLUMBER
1
1
assigned
BUILDING
PLUMBER (PLUMBER_ID, PLUMBER_LNAME,…BUILDING_ID)
BUILDING (BUILDING_ID, BUILDING_ADDRESS,...)
EMPLOYEE
1
has a
1
JOB-DESCRIPTION
EMPLOYEE (EMP_NUM, EMP_LNAME,…, JOB_DESC)
Developing an E-R Diagram

Case 9: 1:1, Both Sides OPTIONAL
EXERCISER
1
has
1
TRAINER
EXERCISER (EXERCISER_ID, EXERCISER_LNAME, …TRAINER_ID)
TRAINER (TRAINER_ID, TRAINER_LNAME, ...)
Developing an E-R Diagram

Case 10: 1:1, One Side OPTIONAL, One Side
MANDATORY
EMPLOYEE
1
has
1
AUTO
EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,…)
AUTO (LIC_NUM, SERIAL_NUM, MAKE, MODEL,, …, EMP_ID)
Developing an E-R Diagram

Case 11: Weak Entity (Foreign key located in
weak entity)
Case 11. Decomposing Weak Entities



When the relationship type of a binary relationship is 1:M
between an entity and its weak entity, two tables are required:
one for each entity, with the entity key from each entity serving
as the primary key for the corresponding table.
Additionally, the entity that has a dependency on the existence
of another entity has a primary key that is partially or totally
derived from the parent entity of the relationship.
Weak entities must be deleted when the strong entity is
deleted.
HOSPITAL
1
contains
M
UNIT
HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS, ...)
UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)
Developing an E-R Diagram

Case 12: Multivalued Attributes
Decomposing an IS-A Relationship
CLIENT
1
INDIVIDUAL
CLIENT
CORPORATE
CLIENT
Entity CLIENT contains
ClientNumber
ClientName
Address
AmountDue
SocialInsuranceNumber
TaxIdentificationNumber
ContactPerson
Phone
Problem: Too many NULL values
Solution: Separate into CLIENT entity plus several
subtypes
Decomposing an IS-A Relationship




Create a table for the parent entity and for each of the child entities or
subtypes
Move the associated attributes from the parent entity into the child table
to which they correspond
From the parent entity take the entity key and add it as the primary key to
the corresponding table for each child entity
In the event a table corresponding to a child entity already has a primary
key then simply add the entity key from the parent entity as an attribute
of the table corresponding to the child entity
CLIENT
1
INDIVIDUAL
CLIENT
CORPORATE
CLIENT
CLIENT (CLIENT_ID, AMOUNT_DUE, …)
INDIVIDUAL_CLIENT (CLIENT_ID, SIN#, …)
CORPORATE_CLIENT(CLIENT_ID, GST#, …)
Transforming Recursive Relationships
1:1 - create a foreign key field (duplicate values not allowed) that
contains the domain of primary key
Stud_ID Stud_FName Stud_LName
1
2
3
4
Rodney
Joki
Francine
Anne
Jones
Singh
Moire
Abel
Locker
Partner
4
3
2
1
1:M - create a foreign key field (duplicate values allowed) that
contains the domain of primary key
Prod_ID
Prod_Name
Base_Prod
1
2
3
4
5
6
Chicken burger
Raw Chicken
Weiner Schnitzel
Fried Chicken
Ground pork
Pork dumplings
2
5
2
5
Transforming M:N Recursive Relationships
M:N - create a second relation that contains two foreign keys: one
for each side of the relationship
BookID
1
2
3
4
BookTitle
Author
Open Secrets
Wilderness Tips
Stones from the River
Cat's Eye
Alice Munro
Margaret Atwood
Ursula Hegi
Margaret Atwood
BookID
RecommendedBook
1
1
2
4
4
2
4
4
2
3
Decomposing Ternary relationships



When a relationship is three-way (ternary) four
preliminary tables are required: one for each
entity, with the entity key from each entity
serving as the primary key for the corresponding
table, and one for the relationship.
The table corresponding to the relationship will
have among its attributes the entity keys from
each entity
Similarly, when a relationship is N-way, N+1
preliminary tables are required.
Library Database Example
writes
AUTHOR
M
BOOK
N
M
publishes
1
PUBLISHER
PUBLISHER (Pub_ID, ___, ___, ___, ___, …)
BOOK (ISBN, Pub_ID, ___, ___, ___, ___, …)
AUTHOR (Author_ID, ___, ___, ___, ___, …)
WRITES(ISBN, Author_ID, ___, ___, ___, ___, …)
Case 6
Case 2
University Example
M
STUDENT
takes
N
COURSE
M
M
taught
by
Case 6
ENROLL (StudID, CourseID, ___, ...)
STUDENT (StudID, ___, ___, FacID, …)
Case 2
COURSE (CourseID, ___, ___, ___, …)
FACULTY (FacID, ___, ___, ___, ___, …)
TEACH (FacID, CourseID,…)
Case 2
N
1
FACULTY
E-R Modeling &
Transformation Exercise
E-R Modeling & Transformation Exercise
Create an E-R model and define its table structures for the
following requirements.
- An INVOICE is written by a SALESREP. Each sales
representative can write many invoices, but each invoice is
written by a single sales representative.
- The INVOICE is written for a single CUSTOMER. However,
each customer may have many invoices.
- An INVOICE may include many detail lines (LINE) which
describe the products bought by the customer.
- The product information is stored in a PRODUCT entity.
- The product's vendor information is found in a VENDOR
entity.
E-R Modeling & Transformation Exercise
1
1
CUSTOMER
VENDOR
(1,1)
(1,N)
(1,N)
gene rate s
M
(1,1)
SHIPMENT
1
M
INVOICE
M
(1,1)
writes
1
(1,N)
SALES RE P
M
M
1
PRO DUCT
INV_LINE
(1,N)
(1,1)
(1,1)
(1,N)
(1,1)
(1,N)
M
1
E-R Modeling & Transformation Exercise
• Keep in mind that the preceding E-R diagram reflects a set of
business rules that may easily be modified
• For example, if customers are supplied via a commercial customer
list, many of the customers on that list will not (yet!) have bought
anything, so INVOICE would be optional to CUSTOMER
• We are assuming here that a product can be supplied by many
vendors and that each vendor can supply many products. The
PRODUCT may be optional to VENDOR if the vendor list includes
potential vendors from which you have not (yet) ordered anything.
• Some products may never sell, so LINE is optional to PRODUCT...
because an unsold product will never appear in an invoice line.
• LINE may be shown as weak to INVOICE, because it borrows the
invoice number as part of its primary key and it is existencedependent on INVOICE
• The design depends on the exact nature of the business rules.
E-R Modeling & Transformation Exercise
1
VENDOR
(0,N)
sh ips
(1,1)
1
CUSTOMER
ORDER
(1,N)
(1,1)
gene rate s
M
(1,1)
(1,1)
writes
1
(1,N)
SALES RE P
M
sh ows in
M
1
INVOICE
M
M
contains
(1,N)
M
INV_LINE
(1,1)
1
is in
(1,1)
(1,N)
PRO DUCT
(0,N)
1
E-R Modeling & Transformation Exercise
CUSTOMER (CustomerID, …)
INVOICE (InvoiceID, CustomerID, SalesRepID,…)
LINE (InvoiceID, LineID, ProdID,…)
PRODUCT (ProductID, …)
SALESREP (SalesRepID, …)
VENDOR (VendorID,…)
SHIP (ShipID, ProductID, VendorID,…)
Further E-R Transformation
Exercises
ER Modeling I handout - Q1
DIVISION (DivisionID,…ManagerID)
DEPARTMENT (DeptID,…DivisionID)
not null
EMPLOYEE (EmpID, …DeptID)
PROJECT (ProjectID,…)
EMPLOYEE_PROJECT (EmpID, ProjectID,…)
null allowed
ER Modeling I - Q2
INSTRUCTOR (InstructorID, HighestDegree, …)
COURSE (CourseID, ClassTitle, …)
CLASS (ClassID, CourseID, InstructorID,
Term…)
TRAINEE (TraineeID, …)
ENROLL (TraineeID, ClassID, Term…)*
All foreign keys not null.
* Optionally, create an EnrollmentID
attribute to use as primary key.
ER Modeling I - Q3
CUSTOMER (CustomerID, …)
INVOICE (InvoiceID, CustomerID, SalesRepID,…)
LINE (InvoiceID, LineID, ProdID,…)
PRODUCT (ProductID, …)
SALESREP (SalesRepID, …)
VENDOR (VendorID,…)
SHIP (ShipID, ProductID, VendorID,…)
All foreign keys not null
ER Modeling I - Q4
AGENT (AgentID, LName, Region…)
CLIENT (ClientID, LName,…)
MUSICIAN (MusicianID, AgentID, Name,
DaysAvailable,…)
EVENT (EventID, ClientID, MusicianID, Date,
Time, Location…)
INSTRUMENT (InsturmentID, …)
MUSICIAN_INSTRUMENT (MusicianID,
InstrumentID, YearsExperience…)
All foreign keys not null.
ER Modeling I - Q5
CITY (CityID, …)
TEAM (TeamID, CoachID, CityID, …)
PLAYER (PlayerID, TeamID,…)
COACH (CoachID, TeamID,…)
GAME (GameID, HomeTeamID, VisitorTeamID,…)
All foreign keys not null.
ER Modeling II - Q1
COMPANY (CompanyID, …)
DEPARTMENT (DepartmentID, CompanyID…)
EMPLOYEE (EmployeeID, DepartmentID, …)
DEPENDENT (EmployeeID, DependentID, …)
EMPLOYEE_HISTORY (EmployeeID, HistoryID, …)
All foreign keys are not null
ER Modeling II - Q2
MEMBER (MemberID, …)
WORKOUT (WorkoutID, MemberID, Date…)
EXERCISE (ExerciseID…)
WORKOUT_EXERCISE (WorkoutID, ExerciseID,
NumberSets, NumberReps,…)
ER Modeling II - Q3
EMPLOYEE (EmployeeID, Name…PositionID)
PART_TIME_EMPLOYEE (EmployeeID,
HourlyRate…)
FULL_TIME_EMPLOYEE (EmployeeID, Salary,
OfficeRoom, …)
POSITION (PositionID, Title, Job_Description…)
All foreign keys not null.
ER Modeling II - Q4
USER (UserID, Name, Department,…)
PROBLEM (ProblemID, TimeSpent, UserID,
ResolverID,…)
HARDWARE (ProblemID, Description, Solution…)
SOFTWARE (ProblemID, SoftwareVersion, …)
RESOLVER (ResolverID, Name, Phone, Level, …)
All foreign keys not null.
ER Modeling II - Q5
EMPLOYEES (EmployeeID, SupervisorID, …)
SKILLS (SkillID, SkillName, …)
EMPLOYEE_SKILL (EmployeeID, SkillID, DateAcquired,
Certification,…)
PROJECTS (ProjectID, ProjectName, ManagerID, StartDate…)
EMPLOYEE_PROJECT (EmployeeID, ProjectID, Role…)
PROJECT_SKILL (ProjectID, SkillID, SkillLevelRequired,
NumberStaff,…)*
DEPENDENTS (EmployeeID, DependentID, DateOfBirth…)
WORK_HISTORY(EmployeeID, HistoryID,…)
BENEFITS (BenefitID, BenefitType, Company, Contact,…)
EMPLOYEE_BENEFIT (EmployeeID, BenefitID,…)
All foreign keys are not null.
* Optionally, create a ProjectSkill_ID
attribute to use as primary key.
ER Modeling II - Q6
ORCHARD (OrchardID, Location, …)
SPECIES (SpeciesID, Name, OrchardID…)
DISEASE (DiseaseID, Symptoms, Treatment,…)
SPECIES_DISEASE (SpeciesDiseaseID, SpeciesID,
DiseaseID, Date,…)*
CUSTOMER (CustomerID, …)
ORDER (OrderID, CustomerID, …)
ORDERDETAILS (OrderID, DetailID, SpeciesID,…)
All foreign keys not null.
* Optionally, use the combination of
SpeciesID, DiseaseID and Date as primary
key and remove SpeciesDiseaseID entirely.
Characteristics of Fields


Each field within a table must have a unique
name (avoid spaces and special characters).
Data within a field must be of the same data
type. The following are common data types:
•
•
•
•
•
•
•
•
character (text or string)
memo (large character field)
integer (whole numbers for calculations)
number (values with decimals for calculations)
currency (formatted number)
logical or Boolean (true/false; 0,-1; yes/no)
date/ time (use computer’s internal calendar/clock)
graphic (picture)
Guidelines for Ideal Table Design








Each table should represents a single theme or subject or entity
or transaction
Tables should include primary keys that uniquely identify each
record of each table
Avoid the use of smart keys that attempt to embed meaning into
primary keys (keys should be meaningless)
A primary key should be a unique, random or sequential collection
of alphabetic, numeric or alphanumeric characters
The domain of primary keys should be large enough to
accommodate the identification of unique rows for the entire
potential universe of records
Use the suffix ID in constructing primary keys to ensure they are
readily identifiable
Tables should not contain any of the following: multipart fields,
multivalued fields, calculated or derived fields or unnecessary
duplicate fields
There should be a minimum amount of redundant data
Common Errors in Database Design

Flat file database

Duplicate field names

Too much data

Cryptic field or table names

Compound fields

Referential integrity

Missing keys

Database Security

Bad keys


Missing relationships


Unnecessary
relationships

Missing or incorrect business
rules
Missing or incorrect
constraints
Incorrect
relationships
John Paul Ashenfelter, “Common Database Mistakes”, May 26, 1999,
<http://webreview.com/wr/pub/1999/03/26/feature/index3.html> (Oct 10, 1999).
The Well-Structured Database





E-R modeling is top-down method of designing
Transforming an E-R model does not guarantee
the best design (e.g., E-R model could be way
off)
Best to transform E-R model and then check
the design according to the Cases of
normalization
Normalization is bottom-up method of designing
a database
Use both approaches to develop a wellstructured database
The Challenge of Database Design:
Conflicting Goals

Conflicting Goals
• Design standards (design elegance)
• Processing speed
• Information requirements

Design Considerations
• Logical requirements and design conventions
• End user requirements; e.g., performance,
security, shared access, data integrity
• Processing requirements
• Operational requirements
• Documentation