Database Management Systems

1
Database Management Systems Programming
LIS 558 - Week 4 Entity Relationship Modeling II

• Faculty of Information Media Studies
• Summer 2000

2
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

3
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

contains
COURSE
SECTION
CourseID, ...
CourseID, SectionID, ...
DEPENDENT
EMPLOYEE
has a
EmployeeID, ...
EmployeeID, DependentID, ...
4
Illustration of the Weak Relationship
Between DEPENDENT and EMPLOYEE
5
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
CONTRACT
CLIENT
1
m
subtype
INDIVIDUAL
CORPORATE
PRODUCTS
SERVICES
The same identifier (e.g., ClientID) is used for
the supertype as well as subtype.
6
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)

7
Developing an E-R Diagram
Entities for the Tiny College Database

• COURSE
• CLASS
• ENROLL (Bridge between STUDENT and CLASS)
• STUDENT

• SCHOOL
• DEPARMENT
• EMPLOYEE
• PROFESSOR

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

9
Developing an E-R Diagram

• Tiny College Database
• Each department offers several courses.

10
Developing an E-R Diagram

• Tiny College Database
• A department may offer several sections (classes)
  of the same course.
• A 1M relationship exists between COURSE and
  CLASS.
• CLASS is optional to COURSE

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

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

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

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

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

16
Components of the E-R Model
17
(No Transcript)
18
Exercise Problem Rob 2.1-7
19
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.

20
Exercise Problem - Rob 2.1-7
21
E-R Modeling I Exercises (distributed last
week) Complete solutions on course website
22
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.

23
Solution to Q1
24
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.
25
Solution to Q2
26
E-R Modeling II Exercises (distributed last
week) Complete solutions on course website
27
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.

28
Solution to Q1
29
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.
30
Solution to Q4
UserID,...
USER
(0,N)
1
ProbID...
reports
HARDWARE
M
(1,1)
1
PROBLEM
ProbID,
is a
(0,1)
(1,1)
M
assigned to
SOFTWARE
(0,N)
1
RESOLVER
ProbID,...
ResolverID,...
31
E-R Modeling II - 5
5. Design a database that tracks employees
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
employees supervisor, dependents, work history
and employee benefits.
32
Solution to Q5
supervises
employee
1
1
parent of
M
dependents
1
M
has
M
M
M
work history
acquires
assigned
M
given
N
N
N
project
skill
N
M
benefits
requires
33
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)

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

35
(No Transcript)
36
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)

37
A Data Dictionary for the ARTIST Database
38
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.
• 1M Relationship
• Weak Entity
• MN Relationship
• 11 Relationship

39
Developing an E-R Diagram

• 1M Relationships
• Create the foreign key by putting the primary key
  of the one (parent) in the table of the many
  (dependent).
• Foreign Key Rules

40
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 1M relationship, except foreign key
  restrictions
• NOT NULL
• ON DELETE CASCADE
• ON UPDATE CASCADE
• MN Relationship
• Convert the MN relationship to a composite
  (bridge) entity consisting of (at least) the
  parent tables primary keys.

41
Developing an E-R Diagram

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

42
Developing an E-R Diagram

• Case 1 MN, Both Sides MANDATORY

43
Developing an E-R Diagram

• Case 2 MN, Both Sides OPTIONAL

44
Developing an E-R Diagram

• Case 3 MN, One Side OPTIONAL

45
Developing an E-R Diagram

• Cases 1-3 MN

PATIENT
DRUG
prescribed
M
N
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.
46
Example of decomposing entitieswith a binary MN
relationship

• StudentsClasses have an MN relationship,
  therefore, decompose to three tables.

bridge table
47
Developing an E-R Diagram

• Case 4 1M, Both Sides MANDATORY

1
M
EMPLOYEE
PRODUCT
checks
EMPLOYEE (EMP_ID, EMP_DEPT, ) PRODUCT (PROD_ID,
PROD_NAME, PROD_FIBRE, EMP_ID... )
48
Developing an E-R Diagram

• Case 5 1M, Both Sides OPTIONAL

M
1
has
PHYSIOTHERAPIST
CLIENTS
PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...) CLIENT
(CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP, PT_ID)
49
Developing an E-R Diagram

• Case 6 1M, Many Side OPTIONAL, One Side
  MANDATORY

1
M
MACHINE
PARTS
contains
MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...) PART
(PART_ID, PART_NAME, PART_CATEGORY, , MACH_ID)
50
Developing an E-R Diagram

• Case 7 1M, One Side OPTIONAL, One Side MANDATORY

1
M
BAND
MUSICIAN
accepts
BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN
(MUSICIAN_ID, MUSICIAN_INSTRUMENT, BAND_ID)
51
Developing an E-R Diagram

• Case 8 11, Both Sides MANDATORY

52
Developing an E-R Diagram

• Case 8 11, Both Sides MANDATORY

PLUMBER
BUILDING
1
1
assigned
EMPLOYEE
JOB-DESCRIPTION
1
1
has a
EMPLOYEE (EMP_NUM, EMP_LNAME,, JOB_DESC)
53
Developing an E-R Diagram

• Case 9 11, Both Sides OPTIONAL

TRAINER
1
1
has
EXERCISER
EXERCISER (EXERCISER_ID, EXERCISER_LNAME,
TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME,
...)
54
Developing an E-R Diagram

• Case 10 11, One Side OPTIONAL, One Side
  MANDATORY

1
1
EMPLOYEE
AUTO
has
EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,) AUTO
(LIC_NUM, SERIAL_NUM, MAKE, MODEL,, , EMP_ID)
55
Developing an E-R Diagram

• Case 11 Weak Entity (Foreign key located in weak
  entity)

56
Case 11. Decomposing Weak Entities

• When the relationship type of a binary
  relationship is 1M 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.

1
M
contains
UNIT
HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS,
...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)
57
Developing an E-R Diagram

• Case 12 Multivalued Attributes

58
Decomposing an IS-A Relationship
Entity CLIENT contains ClientNumber ClientName Add
ress AmountDue SocialInsuranceNumber TaxIdentifica
tionNumber ContactPerson Phone
CLIENT
1
INDIVIDUAL
CORPORATE
CLIENT
CLIENT
Problem Too many NULL values Solution
Separate into CLIENT entity plus several
subtypes
59
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
CLIENT (CLIENT_ID, AMOUNT_DUE, ) INDIVIDUAL_CLIEN
T (CLIENT_ID, SIN, ) CORPORATE_CLIENT(CLIENT_ID,
GST, )
1
INDIVIDUAL
CORPORATE
CLIENT
CLIENT
60
Transforming Recursive Relationships

• 11 - create a foreign key field (duplicate
  values not allowed) that contains the domain of
  primary key

1M - create a foreign key field (duplicate
values allowed) that contains the domain of
primary key
61
Transforming MN Recursive Relationships

• MN - create a second relation that contains two
  foreign keys one for each side of the
  relationship

62
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, N1
  preliminary tables are required.

63
Library Database Example
AUTHOR
BOOK
N
M
M
1
64
University Example
M
N
M
M
advises
N
1
65
E-R Modeling Transformation Exercise
66
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.

67
E-R Modeling Transformation Exercise
68
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 existence-dependent on INVOICE
• The design depends on the exact nature of the
  business rules.

69
E-R Modeling Transformation Exercise
70
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,)

71
Further E-R Transformation Exercises
72
ER Modeling I handout - Q1

• DIVISION (DivisionID,ManagerID)
• DEPARTMENT (DeptID,DivisionID)
• EMPLOYEE (EmpID, DeptID)
• PROJECT (ProjectID,)
• EMPLOYEE_PROJECT (EmpID, ProjectID,)

not null
null allowed
73
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.
74
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
75
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.
76
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.
77
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
78
ER Modeling II - Q2

• MEMBER (MemberID, )
• WORKOUT (WorkoutID, MemberID, Date)
• EXERCISE (ExerciseID)
• WORKOUT_EXERCISE (WorkoutID, ExerciseID,
  NumberSets, NumberReps,)

79
ER Modeling II - Q3

• EMPLOYEE (EmployeeID, NamePositionID)
• PART_TIME_EMPLOYEE (EmployeeID, HourlyRate)
• FULL_TIME_EMPLOYEE (EmployeeID, Salary,
  OfficeRoom, )
• POSITION (PositionID, Title, Job_Description)

All foreign keys not null.
80
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.
81
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,)

Optionally, create a ProjectSkill_ID attribute
to use as primary key.
All foreign keys are not null.
82
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,)

Optionally, use the combination of SpeciesID,
DiseaseID and Date as primary key and remove
SpeciesDiseaseID entirely.
All foreign keys not null.
83
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 computers internal
  calendar/clock)
• graphic (picture)

84
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

85
Common Errors in Database Design

• Flat file database
• Too much data
• Compound fields
• Missing keys
• Bad keys
• Missing relationships
• Unnecessary relationships
• Incorrect relationships

• Duplicate field names
• Cryptic field or table names
• Referential integrity
• Database Security
• Missing or incorrect business rules
• Missing or incorrect constraints

John Paul Ashenfelter, Common Database
Mistakes, May 26, 1999, lthttp//webreview.com/wr/
pub/1999/03/26/feature/index3.htmlgt (Oct 10,
1999).
86
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 well-structured
  database

87
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