SOLUTION: IT 310 Entity Relationship Diagramming Database Class Paper

Last Two Weeks
• Basics of Database
• Start with using MySQL
IT 310
Agenda






Steps in Database Development
Requirements Definition
Basics of Entity-Relationship data model
More about relationships: cardinality
Developing ER diagram using software tool
Enhanced (or extended) ERD:
– Strong vs. weak entities
– Supertypes and subtypes
• Case discussion
• Practice questions
IT 310
Software Requirement (for
ERD)
If you have Visio installed on your computer, then you’re all set!
If you don’t have Visio, no worry. Download the package ER
Assistant (for Windows) from Canvas. It’s a free software. Click
on the .exe file to begin the installation process. Take the
Defaults including an icon on the Desktop.
Note: If you’re a Mac user, you may need to work in one lab
computer or download StarUML from Canvas.
I will have a quick demo of using both software. They are very
light-weight and intuitive to use.
IT 310
4
Three Stages of
Database Development
Requirements Stage (Conceptual)



Conduct interviews
Examine existing records, forms etc
Model data
• Design Stage (Logical)
• Implementation Stage (Physical)
IT 310
Sources of Requirements for a Database
Applications
IT 310
Example of Business Rules
Academic database
Possible business rules identified during
requirements step:




Students must declare a program of study before
enrolling in any class
Graduate classes may be taken by seniors with a
GPA greater than 3.25
No advisor may have more than 50 advisees
Students may declare one or two majors but no
more
IT 310
7
Basics of ER Model
• When you create a database, data requirements must first
be documented in a data model
• A number of techniques can be used to create data models
– The most popular is the
entity-relationship
model created by
Basics
of ER Model
Peter Chen in 1976 (now interpreted as the extended entityrelationship model)
• The most important elements of the E-R model are entities,
attributes, identifiers, and relationships.
IT 310
Entities
• An entity is something that users want to track. Examples include
customers, purchases, products, etc.
• Entities of a given type are grouped into an entity class such as
EMPLOYEE (a collection of all EMPLOYEE entities)
• An entity instance of an entity class is the occurrence of a
particular entity, such as CUSTOMER 12345.
• Indicator: Nouns
– People, place, object, event, concept
• In one ER diagram, every entity type must have a UNIQUE
name.
IT 310
Entity Class and Entity Instance
IT 310
10
Attributes
Entities have attributes that describe the entity’s characteristics


ProjectName
StartDate
Attributes have a data type





Numeric
Character
Date
Currency
Etc…
Attributes have properties: Required, Default values, Limits,etc.
IT 310 7-11
Attributes (cont’d)
Entity instances have identifiers
An identifier will identify a particular entity instance in the entity
class. Examples include:


Social Security Number
Student ID
Identifier may include several attributes

Composite attribute
Identifier may be unique or nonunique



Unique identifiers identify one, and only one, entity instance
Nonunique identifiers identify a set in instances
Employee ID vs Employee Name
IT 310
12
Level of Entity Attribute Display
IT 310
13
Relationships
• Entities can be associated with one another in relationships
• Relationship degree defines the number of entity classes
participating in the relationship
– Unary: one participant
– Degree 2 is a binary relationship
– Degree 3 is a ternary relationship
IT 310
Degree of a Relationship Type (Class)
• Unary relationship
– Will not be discussed
Employees
Manages
• Binary relationship
Students
Enroll-in
Courses
• Ternary relationship
Suppliers
Supply
Parts
IT 310
Projects
Practice Question 1
• For the following cases, identify the entities (including
their attributes) and the relationships
– A retailer keeps track of customers, products, and the orders that
customers placed.
IT 310
Three Types of Binary Relationships
IT 310
Cardinality of Relationships
• One-to-One
– Each entity instance in the relationship will have exactly one
related entity instance
• One-to-Many
– An entity instance on one side of the relationship can have
many related entities instances, but an entity instance on the
other side will have a maximum of one related entity
instance
• Many-to-Many
– Entity instances on both sides of the relationship can have
many related entity instances on the other side
IT 310
Cardinality Constraints
• Relationships are named and classified by their cardinality, which is a word that
means count.
• Each of the three types of binary relationships shown in the previous slide have
different maximum cardinalities.
• Cardinality
– How many instances of one entity type can or must be associated with each instance of
another entity?
– Defines important constraints or business rules that hold in the real world
• Maximum Cardinality: the maximum number entity instances that may participate
in a relationship instance.
• Minimum Cardinality: the minimum number of entity instances that must
participate in a relationship instance.
– If zero, then optional
– If one or more, then mandatory IT 310
Crow’s Foot Symbols
One-to-Many Relationship
A patient history is
recorded for one and
only one patient
A patient must have recorded
at least one history, and can
have many
IT 310
20
Crow’s Foot Example:
Many-to-Many Relationship
An employee can be
assigned to any number of
projects, or may not be
assigned to any at all
A project must be
assigned to at least one
employee, and may be
assigned to many
IT 310
HAS-A Relationships
• The relationships in the previous slides are called HAS-A
relationships
• The term is used because each entity instance has a relationship
to a second entity instance
– An employee has a badge
– A badge has an employee
IT 310
Crow’s Foot Notation
IT 310
Practice Question 2: Cardinality
• A department must have at least one employee and cannot have
more than 10 employees
• An employee may or may not belong to a department and can at
most be employed by one and only one department
Draw ER diagram using Crow’s foot notation. (Mark cardinalities
based on the above business rules.)
IT 310
Strong vs. Weak Entities, and
Identifying Relationships
Strong entity
Weak entity
Also called ID-dependent weak entity
An entity that cannot exist in the database without the existence of another entity
Has a composite identifier (the identifier for the strong entity and the identifier for the
weak entity itself)
▪Identifying relationship (strong relationship)
• Links strong entities to weak entities
• Represented by a solid line
▪Nonidentifying relationship (weak relationship)


Used between strong entities
Represented by a dashed line
IT 310
25
ID-Dependent Weak Entity
Examples
IT 310
26
Associative Entities
• An associative entity (also called an association entity) is used whenever a pure
N:M relationship cannot properly hold attributes that are describing aspects of the
relationship between two entities.
• A new entity is then created to:
– link the two original entities
– hold the attributes
IT 310
The Associative Entity
IT 310
Developing an E-R Diagram
Heather Sweeney Designs will be used as an
ongoing example




Heather Sweeney is an interior designer who specializes in home
kitchen design
She offers a variety of free seminars at home shows, kitchen and
appliance stores, and other public locations
She earns revenue by selling books and videos that instruct people
on kitchen design
She also offers custom-design consulting services
IT 310
29
Heather Sweeney Designs:
The Seminar Customer List
IT 310
Heather Sweeney Designs:
The Customer Form Letter
IT 310
31
Heather Sweeney Designs:
Sales Invoice
IT 310
32
Practice Question 3: HS Designs ERD
• Create the ERD file for HS Designs based on the previous slides.
Note that database design (creating ERD) is an iterative process.
It’s similar as a software design.
IT 310
Exercise solution:
Final Data Model
IT 310
34
Enhanced ERD (EERD):
Supertypes and Subtypes
• Subtype: A subgroup of the entity instances in an entity type which
has attributes that are distinct from those in other subgroups
• Supertype: A generic entity type whose attributes are shared by all
its subtypes
• Subtypes can be exclusive or inclusive:
– if exclusive, the supertype relates to at most one subtype
– if inclusive, the supertype can relate to one or more subtypes
• The relationships that connect supertypes and subtypes are called
IS-A relationships because a subtype is the same entity as the
supertype
IT 310
Subtype Entity Examples
IT 310
Tips on ER Modeling
• There can be multiple ways to satisfy the requirements.
– The correct answer is not unique.
– Yet, some designs can be wrong.
• Some designs are correct only under certain
circumstances.
– Write down the assumptions to justify your particular
design. Without assumptions, your design should allow all
conceivable situations.
Must include Assumptions for Business Rules for a
Conceptual Design to be Complete!
IT 310
More Tips on ER Modeling
• Each entity class, with a unique name, must
have an identifier.
• For each relationship, don’t forget cardinalities in
all directions.
– When the business rules are unclear, think about all conceivable situations; if
assumptions are made, write them down.
IT 310

Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Our Basic features
  • Free title page and bibliography
  • Plagiarism-free guarantee
  • Unlimited revisions
  • Money-back guarantee
  • 24/7 support
Our Options
  • Writer’s samples
  • Expert Proofreading
  • Overnight delivery
  • Part-by-part delivery
  • Copies of used sources
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

AcademicWritingCompany guarantees

Our customer is the center of what we do and thus we offer 100% original essays..
By ordering our essays, you are guaranteed the best quality through our qualified experts.All your information and everything that you do on our website is kept completely confidential.

Money-back guarantee

Academicwritingcompany.com always strives to give you the best of its services. As a custom essay writing service, we are 100% sure of our services. That is why we ensure that our guarantee of money-back stands, always

Read more

Zero-plagiarism tolerance guarantee

The paper that you order at academicwritingcompany.com is 100% original. We ensure that regardless of the position you are, be it with urgent deadlines or hard essays, we give you a paper that is free of plagiarism. We even check our orders with the most advanced anti-plagiarism software in the industry.

Read more

Free-revision guarantee

The Academicwritingcompany.com thrives on excellence and thus we help ensure the Customer’s total satisfaction with the completed Order.To do so, we provide a Free Revision policy as a courtesy service. To receive free revision the Academic writing Company requires that the you provide the request within Fifteen (14) days since the completion date and within a period of thirty (30) days for dissertations and research papers.

Read more

Privacy and Security policy

With Academicwritingcompan.com, your privacy is the most important aspect. First, the academic writing company will never resell your personal information, which include credit cards, to any third party. Not even your lecturer on institution will know that you bought an essay from our academic writing company.

Read more

Adherence to requirements guarantee

The academic writing company writers know that following essay instructions is the most important part of academic writing. The expert writers will, therefore, work extra hard to ensure that they cooperate with all the requirements without fail. We also count on you to help us provide a better academic paper.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2020 at 10:52 AM
Total price:
$26
The price is based on these factors:
Customer Academic level
Number of pages required
Urgency of paper