CS计算机代考程序代写 database 2021/8/8 DBS – 211

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 1/9

DBS211

Week 8 – Conceptual Database Modelling and
ERDs

Table of Contents

Welcome

Conceptual Data Modelling

8 Steps of Data Modelling

Entity Relationship Diagrams

Symbology and Formatting

Summary

Reading Materials

Article on Crow’s Foot ERDs

Textbook Chapter 5

Alternative Agile Database Modelling Approach

RESOURCE: Online ERDs:

Welcome to Week 8

This week we dive into the conceptual components of designing a database using the data

modelling process and creating a UML Crow’s Foot Entity Relationship Diagram (ERD). By

following a detailed series of modelling steps, the student will be able to design a basic database

eliminating many to many relationships and establishing relationships and their types.

Conceptual Data Modelling

Data modelling is the process of following a series of pre-defined steps to conceptually design a

database schema and then developing a graphical representation of a proposed database design

http://dbs211.ca/index.html
https://www.codeproject.com/Articles/878359/Data-Modelling-using-ERD-with-Crow-Foot-Notation

Chapter 5 Data Modelling


http://www.agiledata.org/essays/dataModeling101.html
https://app.diagrams.net/

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 2/9

using entity relationship diagrams. This design will be based on the discovery of required entities,

business rules, and database design requirements.

8 Steps of Data Modelling

If searching on the Internet, you will find many versions of the data modelling process steps. The

following version is a simple and effective methodology that follows the principles of this course

and will server you well in designing database schemas.

Step 1 – Identify Entities

Identify the major objects of which we need to store. These will likely be people, places, events

about which the client needs to store data. It is easy to lose focus on this first step and start

thinking attributes (or characteristics) are entities. You must be clear in determining what is an

entity vs an attribute.

For Example: For a used car lot, some entities might be: Vehicles, Staff, Locations, Services

offered. Be careful not to chose something like type of car as that is an attribute and not

necessarily an entity. It is almost always more beneficial to start with less entities than more.

Step 2 – Identify Attributes

Identify the characteristics, or properties, of the entities that need to be stored.

Examples: Vehicle Colour, Staff First Name, Staff Last Name, Vehicle Make, Vehicle Model, etc

In addition to the attribute names, determine if the attribute is mandatory or optional data.

Mandatory data are included in bold format.

Step 3 – Specify Unique Identifiers (PKs)

Define attributes within each entity that could be used as a unique identifier for individual

instances of the entity. This will ultimately become the primary key, and therefore must meet the

criteria for a primary key. If a suitable one could not be identified, then a new surrogate key can be

added to auto-generate a key field. Primary Keys are indicated in the diagram by being

underlined. They will always be bold as well as the unique identifiers will always be required.

Example: Social Insurance Number (SIN), Vehicle Identification Number (VIN),

productSerialNumber, etc.

Step 4 – Define Attributes Which Form Relationships

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 3/9

Identify entities that have relationships with each other. At this point it does not have to be tied to

a specific attribute, although it ultimately will be.

Examples:

a sales rep may be related to customers

products may be related to product categories

vehicles related to manufacturers

vehicle models associated with manufacturers

Step 5 – Define Relationship Cardinality / Optionality

Determine the number of occurrences of one entity for a single occurrence of the related entity.

This typically will only have three options – “none”, “one” or “more than one”. More than one is

known as “many”. Each relationship cardinality is defined by a range.

i.e. none or one, one or more, none or many, one and only one.

These are the possible results and are determined in BOTH directions of the relationship. (i.e. #

entity 1 for each entity 2 and # entity 2 for each entity 1)

Be careful not to write these based on the existing data, but based on data that may occur in the

future as well.

Lastly in this step, the Key attributes are identified as Foreign Keys that relate to their related

entities Primary Key.

Examples:

student to courses – each student can take 0 – many courses and each course will have 0 –

many students in it.

cell phone to owner – each cell phone can have 1 and only 1 owner, and each person can own

0 or many cell phones

each car has 1 and only 1 manufacturer, but each manufacturer will make 0 or many cars.

each professor is 1 and only 1 employee of the college, but each employee may or may not

(0-1) be a professor.

Step 6 – Eliminate Many to Many Relationships

If the cardinalities for any one relationship contains a many in both directions, then it must be

eliminated. This is because it is physically impossible to create a many-to-many relationship in a

standard relational database.

To eliminate the many-to-many relationship, erase the relationship completely and create a new

entity between the two previous entities (called a junction or bridge entity). The recreate 2 new

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 4/9

relationships between the new entity and one each to the two original entities. In most, if not all,

cases, these new relationships will be opposite 1-to-many relationships.

Examples:

If players can play on more than one team and teams have more than one player, then this

relationship is deleted and a new rosters entity will be created between players and teams.

This new bridge entity will contain attributes to relate to each of the two original tables and

then cardinalities between players and rosters and teams and rosters can be determined and

defined.

The relationship between students and courses will be a many-to-many, so we must create a

new entity between, let’s called it, classlists, that relates the classlist to students and the

classlist to courses. Each of these will be 1-many relationships – or 0-1 to 0-many cardinality.

In each example above, then the new entity is created, the designer must go all the way back to

step 1 from the new entity and follow the steps forward to ensure complete design and

consistency.

Step 7 – Normalize the Database

The normalization process is the largest step, by far, and involves determining dependencies

between attributes, and their unique identifiers. The next 3 weeks of this course covers this large

topic and then you wil understand that the data modelling process via ERDs overlaps with

normalization here and you need to know both in order to perfect your database designs.

The main goals for normalization is to:

eliminate redundant data

eliminate repeating data

remove irrelevant associated data

ensure full and direct dependency between all attributes and their entities unique identifier.

Step 8 – Define Attribute Types and Sizes

This final step is simply to define the attribute data types and sizes. These values are not required

for any of the previous steps and therefore it is recommended to do this last to avoid having to

redo them several times. Some things to note:

chose data types to be optimal for the data type while still allowing future potential data to be

inserted.

for referential integrity, FKs and PKs

use numeric fields over string fields wherever possible

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 5/9

Entity Relationship Diagrams

ERDs are a graphical representation of the proposed database showing the entities, attributes or

characteristics of the entities, and the relationships between the entities (people, places,

concepts, and events).

There are 4 Goals for the ERDs:

capture all the required data

ensure no data repetition

do not include any data that is derived from other data

arrange the data in a logical manner

Symbology and Formatting

In this course, we will focus on the CROW’s Foot style of UML ERD. The standards for a UML

Crow’s Foot ERD are:

Entities are contained in boxes with the entity name in a top box and written in ALL CAPS

Entity and Attribute names must be singular in nature. Singular means written as a single

word, no spaces.

Attribute names are written in one of, lower case, pascal case, or camel case.

Unique identifiers are underlined or included in a separate section of the box.

required fields are included using bold format.

foreign keys are identified using FK

The cardinalities of the diagram are displayed using only 3 symbols.

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 6/9

a circle – representing 0

a line – representing 1

a crow’s foot – representing more than one. Sometimes called a fan.

EVERY relationship line will have TWO symbols on each side representing the range of values.

There are only 4 possible combinations that are used.

Circle and Line – 0 to 1 entities per each related entity

2 lines – 1 and only 1 entity per each related entity

a Circle and a Foot – 0 to Many entities per each related entity

a line and a foot – 1 or Many entities per each related entity

In the above example:

each department can have 0 or many employees

each employee must be in one department and only one department

a couple more examples to learn from:

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 7/9

Using Diagrams.net (Draw.io) to Create a Crow’s Foot ERD

The link to the referred to website is https://app.diagrams.net/

There are other online tools or you can use MicroSoft Visio to complete the ERDs. This site will

demo draw.io as it is fairly simple, free and produced clean ERDs.

Creating a New ERD

When you first visit the site, you are prompted to either create a new diagram or open a new one.

Your ERDs can be saved to your local harddrive and reopened later for further editing.

Click on Create New Diagram and then choose a crow’s foot diagram. This is the last option
under the software category. Name your diagram and click Create .

https://app.diagrams.net/

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 8/9

You will now have a new template diagram in front of you that you can break apart and uses the

pieces to create your ERD. Make sure you expand the “Entity Relationship” object window (as

shown below) to have access to additional objects you may need.

You will also want to open the format panel to edit the fonts, sizes, relationship symbols etc. Open

the panel by clicking the middle icon in the top right corner. This

panel is context sensitive, meaning it will changed depending on what

object is currently selected. To edit cardinality symbols, make sure

you have a relationship selected.

Note the difference between the entities provided in the sample diagram. There are samples of

both standard single field PK entities and bridge entities with a composite key identifier.

2021/8/8 DBS – 211

dbs211.ca/Weeks/Week09/index.html 9/9

Saving Your Work

Once you are ready to save your work, click the file menu (the one inside the browser window, not

the browser file menu) and chose Save. Indicate the location you want the file saved, make sure it

has a good name and click save.

The next time you need to work on the diagram, you can load the website homepage and choose

open and existing diagram and navigate to the file you just saved.

IMPORTANT: The save file from this website or other websites will NOT be appropriate for

submitting your work. You should first ensure your name, the date and your id is in a box on

the diagram and then take a PNG screen shot or save as a PDF document for submission.