Data Modeling with ER Model

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the primary purpose of an Entity-Relationship model?

To visualize data relationships

To design a database schema (correct)

To analyze data queries

To implement a database

What are the components that an ER model is based on?

Entities and relationships

Attributes and constraints

Entities, relationships, and dependencies

Entities, attributes, and constraints (correct)

What is an entity in the context of an ER model?

A constraint on a database design

A specific object or thing in the mini-world (correct)

A relationship between objects

A property of an object

What is an attribute in the context of an ER model?

A property used to describe an entity Signup and view all the answers

In the COMPANY database, what is a department composed of?

Projects and locations Signup and view all the answers

What is a Relationship Type?

A schema description of a relationship Signup and view all the answers

What is tracked for each employee in the COMPANY database?

Social security number, address, salary, sex, and birthdate Signup and view all the answers

What is the purpose of an Entity-Relationship Diagram (ER Diagram)?

To describe the structure of a database Signup and view all the answers

What is represented by a diamond-shaped box in an ER diagram?

Relationship type Signup and view all the answers

How many relationship types are identified in the refined COMPANY database schema?

6 Signup and view all the answers

What is a project in the COMPANY database?

A unique task with a name and number Signup and view all the answers

What is the degree of the relationships identified in the refined COMPANY database schema?

2 Signup and view all the answers

What is the popular name for the VehicleIdentificationNumber attribute?

VIN Signup and view all the answers

What is the relationship type between an EMPLOYEE and a DEPARTMENT in the refined COMPANY database schema?

WORKS_FOR Signup and view all the answers

What happens to the attribute 'Manager of DEPARTMENT' in the refined design?

It is refined into a relationship Signup and view all the answers

How are attributes typically displayed in an ER diagram?

In ovals Signup and view all the answers

What is the purpose of underlining a key attribute in an ER diagram?

To indicate it is a key attribute Signup and view all the answers

What is possible between the same participating entity types?

More than one relationship type Signup and view all the answers

What is the relationship type between a PROJECT and a DEPARTMENT in the refined COMPANY database schema?

CONTROLS Signup and view all the answers

What is an entity set?

A collection of entities stored in the database Signup and view all the answers

How many initial entity types are identified for the COMPANY database?

4 Signup and view all the answers

What is the name of the employee who manages a department?

Department Manager Signup and view all the answers

What does a department control in the COMPANY database?

Projects Signup and view all the answers

What is the purpose of an ER diagram?

To design a database schema Signup and view all the answers

Study Notes

ER Model

An Entity-Relationship (ER) model describes the structure of a database with the help of a diagram, known as an Entity-Relationship Diagram (ER Diagram).
An ER model is a design or blueprint of a database that can later be implemented as a database.
ER Model is based on the notion of real-world entities and relationships among them.
ER Model is best used for the conceptual design of a database.

ER Model Components

Entities and their attributes
Constraints
Relationships among entities

ER Diagram

Entity types are displayed in rectangular boxes
Attributes are displayed in ovals
Each attribute is connected to its entity type
Components of a composite attribute are connected to the oval representing the composite attribute
Each key attribute is underlined
Multivalued attributes are displayed in double ovals

Entity Set

Each entity type will have a collection of entities stored in the database
Called the entity set
The entity set is the current state of the entities of that type that are stored in the database

Entity Types in the COMPANY Database

DEPARTMENT
PROJECT
EMPLOYEE
DEPENDENT

Attributes for Each Entity Type

DEPARTMENT: name, number, employee who manages the department, start date of the department manager, locations
PROJECT: unique name, unique number, location
EMPLOYEE: social security number, address, salary, sex, birthdate, department, projects, number of hours per week worked on each project, direct supervisor
DEPENDENT: name, sex, birthdate, relationship to the employee

Relationship Types

Is the schema description of a relationship
Identifies the relationship name and the participating entity types
Identifies certain relationship constraints

Relationship Sets

The current set of relationship instances represented in the database
The current state of a relationship type

Relationship Types in the COMPANY Database

WORKS_FOR (between EMPLOYEE, DEPARTMENT)
MANAGES (between EMPLOYEE, DEPARTMENT)
CONTROLS (between DEPARTMENT, PROJECT)
WORKS_ON (between EMPLOYEE, PROJECT)
SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor))
DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

ER Diagram – Relationship Types

WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Learn about the Entity-Relationship model, a design blueprint for databases that represents real-world entities and their relationships. Understand how to create an ER diagram and implement a database.