REAL WORLD MODEL Entity-Relationship (ER) Flashcards
(19 cards)
database (DB)
Data
Mini-world
A collection of related data.
Known facts that can be recorded and have an implicit meaning
Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university.
Database Management System (DBMS)
Database System
A software package/ system to facilitate the creation and maintenance of a computerized database.
The DBMS software together with the data itself. Sometimes, the applications are also included.
Typical DBMS Functionality
- Define* a particular database in terms of its data types, structures, and constraints
- Construct* or Load the initial database contents on a secondary storage medium
Manipulating the database:
- Retrieval: Querying, generating reports
- Modification: Insertions, deletions and updates to its
content
- Accessing the database through Web applications
Processing and Sharing by a set of concurrent users and application programs - yet, keeping all data valid and consistent
Typical DBMS Functionality
Other features:
- Protection/Security measures to prevent unauthorized access.
- “Active” processing to take internal actions on data
- Presentation and Visualization of data
- Maintaining the database and associated programs over the lifetime of the database application
Overview of Database Design Process
Two main activities :
- Database design
- To design the conceptual schema for a database - Applications design
- Focuses on the programs and interfaces that access the database
- Generally considered part of software engineering
Database Schema
A Database Schema is:
The description description of a database.
Includes descriptions of the:
- database structure
- data types
- constraints on the database.
!!It is common to give an illustrative display of (most aspects of) a database schema using a DATABASE SCHEMA DIAGRAM!!
ER Model Concepts
Entities and Atributes
- Entities are specific objects or things in the mini-world that are represented in the database.
- Attributes are properties used to describe an entity.
- A specific entity will have a value for each of its attributes.
- Each attribute has a value set (or data type) associated with it
Types of Attributes (1)
•Simple
Each entity has a single atomic value for the attribute. For example, SSN or Sex.
•Composite
The attribute may be composed of several components. For ex:
- Address(Apt#, House#, Street, City, State, ZipCode, Country), OR
- Name(FirstName, MiddleName, LastName).
- Composition may form a hierarchy where some components are themselves composite.
Types of Attributes (2)
•Multi-valued
An entity may have multiple values for that attribute. For example:
- Color of a CAR
- PreviousDegrees of a STUDENT
- Denoted as {Color} or {PreviousDegrees}.
!!!In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels, although this is rare!!!!!!
Entity Types and Key Attributes (1)
Entities with the same basic attributes are grouped or typed into an entity type
-> For example, the entity type EMPLOYEE and PROJECT
An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type.
-> For example, SSN of EMPLOYEE.
Entity Types and Key Attributes (2)
A key attribute may be composite.
- VehicleTagNumber is a key of the CAR entity type with components (Number, State).
An entity type may have more than one key.
- > The CAR entity type may have two keys:
- VehicleIdentificationNumber (popularly called VIN)
- VehicleTagNumber (Number, State), aka license plate number
Each key is underlined
Displaying an Entity type
In ER diagrams, an entity type is displayed in a rectangular box
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 displayed in double ovals
Entity set
Each entity type will have a collection of entities stored in the database
•Called the entity set
•Previous slide shows three CAR entity instances in the entity set for CAR
•Same name (CAR) used to refer to both the entity type and the entity set
!!!Entity set is the current state of the entities of that type that are stored in the database!!!
Refining the initial design by introducing relationships
Some aspects in the requirements will be represented as relationships
ER model has three main concepts:
•Entities (and their entity types and entity sets)
•Attributes (simple, composite, multivalued)
•Relationships (and their relationship types and relationship sets)
Relationships and Relationship Types
A relationship relates two or more distinct entities with a specific meaning.
- For example, EMPLOYEE John Smith works on the ProductX PROJECT, or EMPLOYEE Franklin Wong manages the Research DEPARTMENT.
Relationships of the same type are grouped or typed into a relationship type.
- For example, the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate.
The degree of a relationship type is the number of participating entity types.
- Both MANAGES and WORKS_ON are binary relationships.
Relationship type vs. relationship set (1)
Relationship Type:
•Is the schema description of a relationship
•Identifies the relationship name and the participating entity types
•Also identifies certain relationship constraints
Relationship Set:
•The current set of relationship instances represented in the database
•The current state of a relationship type
Relationship type vs. relationship set (2)
Each instance in the set relates individual participating
entities – one from each participating entity type
In ER diagrams, we represent the relationship type as
follows:
- Diamond-shaped box is used to display a relationship type
- Connected to the participating entity types via straight lines
Recursive Relationship Type
Same entity type participates more than once in a relationship type
-Both participations are same entity type in different roles
Example: the SUPERVISION relationship
EMPLOYEE participates twice in two distinct roles:
- supervisor (or boss) role
- supervisee (or subordinate) role
Notation for Constraints on Relationships
Cardinality ratio (of a binary relationship):
-Shown by placing appropriate numbers on the relationship
edges(1:1, 1:N, N:1, or M:N)
- Participation constraint* (on each participating entity
type) : - Total (called existence dependency) shown by double line
- Partial shown by single line
