MODELLING DATA AND ENTITY RELATIONSHIP DIAGRAMS – PART 1
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
Copyright By PowCoder代写 加微信 powcoder
• List 2 of the steps required to build the requirements for a large system
• Give the name of the model that we will use to visually represent our database for our mini-world
• Give the name of the person who invented the visual model used for representing database
• List at least 2 different pieces of software you could use to create the visual model
• QUESTION à What do you do before starting to build anything big…like a house or an airplane or car?
BEFORE CREATING THE DATABASE…
BUILD A MODEL!
WHERE AND HOW DO WE START?
• Forget what you learned in first year and second year about coding right away…ALWAYS START WITH THE DATA NOT THE CODE!
• Narrow your scope as much as possible and figure out what data you need to store/save by:
• Gather any/all reports your customers use/print/generate
• Look at any screens where the customers enter/modify data
• Find the requirements of your customers/stateholders
• Figure how they VIEW the data
• Integrate the views to get the minimal data that you need to store (narrowing your scope)
DRAW A MODEL BEFORE CREATING THE DATABASE!
• Entity Relationship Model (ER Diagram)
• Created by in 1976
• VISUAL representation of your data
• Map your ER Diagram/Model to a currently used model for implementing a database (we will map to the Relational Model)
EXAMPLES OF ER DIAGRAMS:
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
HOW CAN WE REPRESENT THE PREVIOUS SLIDE AS A MODEL?
• We must incorporate all the information of the mini-world we described!
• We will represent it visually
• Several programs you could use to create ER Diagrams, for example: • Microsoft Visioàhttps://support.office.com/en-us/article/video-what-is-visio-
421b0c94-7ecf-4e62-8072-d27e04d24fe6 • Draw IOàhttps://www.draw.io/
• smartdrawàhttps://cloud.smartdraw.com/ • Lots of other ones!
ENTITY RELATIONSHIP DIAGRAMS – PART 2
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Given a description of a mini-world, identify which nouns should be Entities and which nouns should be Attributes
• Define the following terms: Entity, Key, Attribute, Multi-valued Attribute, Composite Attribute and Derived Attribute and select the correct shape to represent these in an ER Diagram
• Determine if an attribute is allowed to have null values and if so, decide if the null value is not applicable OR not known
• Distinguish between an attribute and an attribute’s value (its domain)
WHEN DESIGNING A DATABASEà NOUNS ARE KING!
• Look at your requirements, your specs, your forms, your reports and identify the nouns.
• Usually the nouns turn into: • Entities
• Attributes
• Let’s look at our case study and identify some nouns
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
WHAT’S WHAT?
• What is different about the noun employee vs. the noun salary?
• If you can see the different between them, you understand what an ENTITY is compared to an ATTRIBUTE!
E-R MODEL CONCEPTS AND KEY TERMS
Entities and Attributes:
◦ Entity – A single “THING” that exists, has independent existence àEmployee
◦ Attribute – describes a “thing”àAge, SSN, Sex, Name
◦ Value – taken on by an attribute à 25, 456-876-788, Female,
◦ Composite Attributes vs. Atomic or Simple Attributes à vs. 45 ◦ Single-Valued Attributes vs. ̀Age vs. CollegeDegrees ◦ Derived Attributes vs. Stored AttributesàAge vs. Birthdate
ENTITYàany object in our mini-world that we want to model and store information about. E.g. Student, Professor, Classroom
ATTRIBUTEàdefines the information about the entity that needs to be stored. An entity will have 0 or more attributes. An attribute has a DOMAIN. E.g. student number
DOMAINàthe type of values that an attribute can take. E.g. String, integer, real, date
C53319 9/17/19
• Null Values – Why and when would we use this? • Apartment Number
• Phone Number
QUESTION: What is the difference between attributes with null values that are not applicable vs. not known?
MORE TERMINOLOGY
• Entity Types, Value Sets (DOMAIN) and Key Attributes
• Entity Type – defines a set of entities that have the same set of attributes • Entity – an instance of an entity type
• Entity Set, Collections – group of Entities
• Key, Uniqueness
• Combination to create key
• Value Sets (Domains)
QUESTION: What is Age’s domain, what is Name’s domain, what is Cost’s domain?
C53319 9/17/19
E-R DIAGRAM NOTATION SO FAR: Entity Type
Attribute
Key Attribute Multi-valued Attribute Composite Attribute Derived Attribute
QUESTION: WHAT ARE THE ENTITIES FROM OUR EXAMPLE?
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
QUESTION: WHAT IS OUR DIAGRAM SO FAR? (IT IS STARTED BELOW)
Let’s use draw.io to finish the diagram.
Department
Name Location Number 9/17/19
ENTITY RELATIONSHIP DIAGRAMS – REPRESENTING RELATIONSHIPS – PART 3
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Define the following terms: Relationship, Cardinality, Unary Relationship, Binary Relationship, Ternary Relationship, One to One Relationship, One to Many Relationship, and Many to Many Relationship and give an example of each
• Using lines, diamonds and text, indicate the above relationships on an ER diagram
• Determine if a relationship has any associated attribute(s)
• Represent relationship attributes on an ER diagram
WHAT ARE WE MISSING FROM OUR MODEL? WHAT HAVEN’T WE REPRESENTED YET?
• Look again at our case study, what are we missing from our ER diagram?
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
E-R MODEL CONCEPTS AND KEY TERMS
Relationship – a named grouping of entities
Relationship Set – an ordered list of entity sets
A Relationship Type R among n entity types E1, E2, … En defines a set of associations among entities. Thus R is a set of relationship instances ri, where each ri associates n entities (e1, e2, …, en) and each entity ej in ri is a member of entity type Ej, 1<=j<=n. Hence a relationship type is a mathematical relation on E1, E2, ... En.
Example: (“Reid”, “CS3319”) is a relationship set of (Prof, Course) C53319 9/17/19
E.G. THE RELATIONSHIP: EMPLOYEE WORKS FOR DEPARTMENT
Department
Math Computer Science
r1 e3 r3
QUESTION: In the above diagram, how many entity types 9/17/19 participate in the relationship?
BINARY RELATIONSHIP - EXAMPLE
CourseName
DEGREE OF A RELATIONSHIP: BINARY, TERNARY, UNARY
Unary Relationship
Ternary Relationship
MORE TERMINOLOGY
Recursive (Unary) Relationship: an entity of one entity type has a relationship with other entities of that same entity
Attributes on Relationships: Describes some piece of information about the relationship. E.g.àQuantity see above
Cardinality Ratio: number of relationships instances that an entity can participate in, there are 3 common ones:
◦ One-To-One: Employee Manages Department
◦ Many-To-One: Employee Works_For Department ◦ Many-To-Many: Employee Works_On Project
QUESTION: What is the cardinality of: Man BIOLOGICALLY FATHERS Child?
QUESTION: Give an example of a many to many relationship: 9/17/19
DRAW THE FOLLOWING RELATIONSHIPS AS THEY WOULD BE REPRESENTED IN AN ER DIAGRAM:
• An artist paints paintings
• A person owns pets
• A woman is currently married to a man (and show the date they got married) and a woman has been married to a man (show the date they got married and the date
the marriage ended). 1
Wedding Date
Man Painting
Artist Person
Currently Married to
Wedding M Date
Marriage End M Date
ownWs as Married
E-R DIAGRAM NOTATION SO FAR: Relationship Type
Relationship Attribute Cardinality
QUESTION: WHAT ARE THE RELATIONSHIPS FROM OUR EXAMPLE?
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
QUESTION: WHAT IS OUR DIAGRAM SO FAR? (IT IS STARTED BELOW)
Let’s use draw.io to finish the diagram.
ENTITY RELATIONSHIP DIAGRAMS – PARTICIPATION ON RELATIONSHIPS – PART 4
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Define the following terms: Total (Mandatory) Participation, Partial (Optional) Participation and give an example of each
• Using lines and (min, max) notation indicate the participation of each side of a relationships on an ER diagram
• Determine if a relationship has total or partial participation by reading the specs about a relationship.
CAN WE HAVE ENTITIES THAT DO NOT PARTICIPATE IN A RELATIONSHIP?
• For example: In the relationship: Artist PAINTS Picture, does every Picture have to be painted? Does every Artist have to paint a picture?
• In the relationship: Woman GIVES BIRTH TO Child, does every Woman have to give birth? Does every Child have to have been given birth to?
• Key word is EVERY. Every implies TOTAL participation. Anything less than EVERY implies PARTIAL participation.
Participation Constraint: specifies whether the existence of an entity depends on it being related to another entity via the relationship type.
◦ Total (Mandatory) - every entity in the entity set MUST BE related to the other entity set via the relationship. (For example, every employee must Work_For a department)
◦ SHOW ON ER DIAGRAM WITH A DOUBLE LINE
◦ Partial (Optional) - some or part of the entity set are related to the other entity set but not necessarily all. (For example, some employees manage a department but not all)
◦ SHOW ON ER DIAGRAM WITH A SINGLE LINE
◦ Other notation --> (min, max) where 0<=min<=max and max>=1. Each entity must participate in at least min and at most max relationships. Thus a min of 0 implies partial participation.
QUESTION: What is the participation of Child in: Man FATHERS Child?
QUESTION: What is the participation of Man in: Man FATHERS Child?
Start Date
Person Person
Books Books
Hotel Room Hotel Room
Artist Artist
Painting Painting
paints paints
Child Child
Woman Woman
Gives Birth
ToBirth To
Start Date M Books N
Hotel Room
Gives Birth To
E-R DIAGRAM NOTATION SO FAR: Relationship Participation
Partial – single line
Total – double line
(Min, Max) Notation
QUESTION: In the following ER diagram, what does the (min, max) notation imply?
Is allocated
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
QUESTION: WHAT IS OUR DIAGRAM SO FAR? (IT IS STARTED BELOW)
Let’s use draw.io to finish the diagram.
ENTITY RELATIONSHIP DIAGRAMS – WEAK ENTITIES – PART 5
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Identify a Weak Entity given a set of specifications.
• Using a double outlined rectangle and diamond indicate a weak entity on an ER Diagram • Identity the OWNER Entity of a WEAK Entity.
WHAT MAKES AN ENTITY WEAK?
• Look at the Entities: BUILDING and ROOM and at the Relationship HAS
SquareMetres
BuildingCode
RoomNumber
FloorNumber
WEAK ENTITY TYPES
• Have no key attribute of their own
• Cannot exist without its identifying owner
• Always has total participation with its identifying owner
• Always has a double line around the relationship with its identifying owner
• The identifying owner does not have to have 1 weak entity (partial participation)
• Can sometimes be represented at composite, multi-valued attributes
• Use a dashed underline to show the partial key of the weak entity
• Use a double outlined diamond to show the relationship with the owner entity
SquareMetres
BuildingCode
RoomNumber
FloorNumber
E-R DIAGRAM NOTATION SO FAR: Weak Entity
QUESTION: Can you see a weak entity in our case study?
CASE STUDY – CREATING AN ER DIAGRAM
Suppose we plan to model a company which is organized into departments.
Each department has a unique name, number and employee who manages it (we want to keep track of when the employee started managing the department)
A department may have several locations
A department controls a bunch of projects, each project has a unique number,
name and a single location
Each employee has a name, ssnumber, address, salary, sex and birthdate
An employee is assigned to only one department but may work on several projects which are not necessarily from the same department
Keep track of the number of hours each employee works on each project.
Keep track of the direct supervisor of each employee
Keep track of the dependents of each employee (name, sex, birthdate and
QUESTION: WHAT IS OUR DIAGRAM SO FAR?
Let’s use draw.io to finish the diagram.
ENTITY RELATIONSHIP DIAGRAMS – SEMANTIC GUIDELINES – PART 6
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to: • Follow the guidelines and properly label your ER Diagram
GENERAL SEMANTIC GUIDELINES
Use either all singular names for the entities or all plural names but not both (preferably singular)
Try to use meaningful names, try to make the relationships read from left to right and from top to bottom
QUESTION: What’s wrong with this example?
IS TREATED BY Treats
Patient Patient
QUESTION: Label the cardinality on the above diagram:
Is employee by
Doctor Doctors
Nurse Nurse
OUR CASE STUDY SO FAR: Let’s use draw.io to fix it up a little!
USING VISIO INSTEAD OF DRAW.IO
MSVisio à http://www.csd.uwo.ca/~lreid/cs3319/usingvisio/usin gvisio.htm
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com