Learning Objectives

 Relate the idea of data as a corporate resource that can be used to gain a
competitive advantage to the development of the database management
systems environment.
 Differentiate the types of databases and database applications
 Understand the principals of typical DBMS functionality
 Explain the main characteristics of the database approach
 Know the types of database users
 Appraise the advantages of using the database approach
 Summarize the historical development of database technology
 Know how to extend database capabilities
 Estimate when not to use databases
 Outline

 Types of Databases and Database Applications

 Basic Definitions
 Typical DBMS Functionality
 Example of a Database (UNIVERSITY)
 Main Characteristics of the Database Approach
 Types of Database Users
 Advantages of Using the Database Approach
 Historical Development of Database Technology
 Extending Database Capabilities
 When Not to Use Databases
 Data
 Database:
 A collection of related data, that represent some real world entity. Use to
solve some problems.
 Data:
 Known facts that can be recorded and have an implicit meaning. Stored
in the form of text, image, numbers, video, audio, speech,
 Mini-world:
 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):
 A software package/ system to facilitate the creation and maintenance of
a computerized database.
 Database System:
 The DBMS software together with the data itself. Sometimes, the
applications are also included.
 Information: processed/ meaningful data.
 What is Data?

 A single piece of data is a single fact about something that interests us.

 A fact can be any characteristic of an object.

Database Management System (DBMS)
 DBMS contains information about a particular enterprise
 Collection of interrelated data
 Set of programs to access the data
 An environment that is both convenient and efficient to use
 Database Applications:
 Banking: transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Online retailers: order tracking, customized recommendations
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Databases can be very large.
 Databases touch all aspects of our lives
University Database Example

 Application program examples

 Add new students, instructors, and courses
 Register students for courses, and generate class rosters
 Assign grades to students, compute grade point averages (GPA) and
generate transcripts
 In the early days, database applications were built directly on top of file
systems
Drawbacks of using file systems to
store data
 Data redundancy and inconsistency
 Multiple file formats, duplication of information in different
files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation
 Multiple files and formats
 Integrity problems
 Integrity constraints (e.g., account balance > 0) become
“buried” in program code rather than being stated
explicitly
 Hard to add new constraints or change existing ones
Drawbacks of using file systems to store data (Cont.)
 Atomicity of updates
 Failures may leave database in an inconsistent state with partial updates carried out
 Example: Transfer of funds from one account to another should either complete or not
happen at all
 Concurrent access by multiple users
 Concurrent access needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
 Example: Two people reading a balance (say 100) and updating it by withdrawing
money (say 50 each) at the same time
 Security problems
 Hard to provide user access to some, but not all, data

Database systems offer solutions to all the above problems

Main Characteristics of the
Database Approach
 Self-describing nature of a database system:
 A DBMS catalog stores the description of a particular database (e.g. data
structures, types, and constraints)
 The description is called meta-data*.
 This allows the DBMS software to work with different database applications.
 Insulation between programs and data:
 Called program-data independence.
 Allows changing data structures and storage organization without having to change
the DBMS access programs.
-----------------------------------------------------------------------------
* Some newer systems such as a few NOSQL systems need no meta-data: they store
the data definition within its structure making it self describing
Main Characteristics of the Database
Approach(Cont…)
 Data Abstraction:
 A data model is used to hide storage details and present the users with a
conceptual view of the database.
 Programs refer to the data model constructs rather than data storage details
 Support of multiple views of the data:
 Each user may see a different view of the database, which describes only
the data of interest to that user.
Main Characteristics of the
Database Approach(Cont…)
 Sharing of data and multi-user transaction
processing:
 Allowing a set of concurrent users to retrieve from and to
update the database.
 Concurrency control within the DBMS guarantees that each
transaction is correctly executed or aborted
 Recovery subsystem ensures each completed transaction
has its effect permanently recorded in the database
 OLTP (Online Transaction Processing) is a major part of
database applications. This allows hundreds of concurrent
transactions to execute per second.
 Database Users

 Users may be divided into

 Those who actually use and control the database content,
and those who design, develop and maintain database
applications (called “Actors on the Scene”), and
 Those who design and develop the DBMS software and
related tools, and the computer systems operators (called
“Workers Behind the Scene”).
Database Users – Actors on the
Scene
 Database administrators:
 Responsiblefor authorizing access to the database, for
coordinating and monitoring its use, acquiring software
and hardware resources, controlling its use and
monitoring efficiency of operations.
 Database Designers:
 Responsible to define the content, the structure, the
constraints, and functions or transactions against the
database. They must communicate with the end-users
and understand their needs.
Actors on the scene (continued)

 End-users: They use the data for queries, reports and some of them
update the database content. End-users can be categorized into:
 Casual: access database occasionally when needed
 Naïve or Parametric: they make up a large section of the end-user
population.
 They use previously well-defined functions in the form of
“canned transactions” against the database.
 Users of Mobile Apps mostly fall in this category
 Bank-tellers or reservation clerks are parametric users who do this
activity for an entire shift of operations.
 Social Media Users post and read information from websites
Database End Users (cont.)
 Sophisticated:

 A sophisticated end user is someone who can perform complex

queries or updates, and can access the database in their own way
 These include business analysts, scientists, engineers, others
thoroughly familiar with the system capabilities.
 Many use their own tools in the form of software packages that
work closely with the stored database.
 Stand-alone:

 Mostly maintain personal databases using ready-to-use packaged

applications.
 An example is the user of a tax program that creates its own
internal database.
 Another example is a user that maintains a database of personal
photos and videos.
Database Users – Actors on the
Scene (cont.)
 System Analysts and Application Developers
This category currently accounts for a very large proportion of the IT
work force.
 System Analysts: They understand the user requirements of naïve
and sophisticated users and design applications including canned
transactions to meet those requirements.
 Application Programmers: Implement the specifications
developed by analysts and test and debug them before
deployment.
 Business Analysts: There is an increasing need for such people
who can analyze vast amounts of business data and real-time data
(“Big Data”) for better decision making related to planning,
advertising, marketing etc.
Database Users – Actors behind the
Scene
 SystemDesigners and Implementors: Design and implement DBMS
packages in the form of modules and interfaces and test and debug
them. The DBMS must interface with applications, language
compilers, operating system components, etc.
 Tool Developers: Design and implement software systems called
tools for modeling and designing databases, performance monitoring,
prototyping, test data generation, user interface creation, simulation
etc. that facilitate building of applications and allow using database
effectively.
 Operators and Maintenance Personnel: They manage the actual
running and maintenance of the database system hardware and
software environment.
Advantages of Using the Database
Approach
 Controlling redundancy in data storage and in development and
maintenance efforts.
 Sharing of data among multiple users.
 Restricting unauthorized access to data. Only the DBA staff uses
privileged commands and facilities.
 Providing persistent storage for program Objects-that can retain data
even when the device's power is off
 E.g., Object-oriented DBMSs make program objects persistent
 Providing Storage Structures for efficient Query Processing-how data
is organized and stored on devices like disks. It uses techniques like
indexing, clustering, and partitioning to improve data access and
retrieval
Advantages of Using the Database
Approach(Cont.)
 Providing optimization of queries for efficient processing.
 Providing backup and recovery services.
 Providing multiple interfaces to different classes of users.
 Representing complex relationships among data.
 Enforcing integrity constraints on the database.
 Drawing inferences and actions from the stored data using deductive
and active rules and triggers.
Additional Implications of Using the
Database Approach
 Potential for enforcing standards:
 This is very crucial for the success of database applications
in large organizations. Standards refer to data item
names, display formats, screens, report structures, meta-
data (description of data), Web page layouts, etc.
 Reduced application development time:
DBMS provides an interface for the application
program to interact with the database, reducing
the time and effort required to develop and
maintain the application.
Additional Implications of Using the
Database Approach(Cont.)
 Flexibility to change data structures:
 Database structure may evolve as new requirements are
defined.
 Availability of current information:
 Extremely important for on-line transaction systems such
as shopping, airline, hotel, car reservations.
 Economies of scale:
 Wasteful overlap of resources and personnel can be
avoided by consolidating data and applications across
departments.
Historical Development of Database
Technology
 Early Database Applications:
 The Hierarchical and Network Models were introduced in mid
1960s and dominated during the seventies.
 A bulk of the worldwide database processing still occurs
using these models, particularly, the hierarchical model
using IBM’s IMS system.
 Relational Model based Systems:
 Relational model was originally introduced in 1970, was
heavily researched and experimented within IBM Research
and several universities.
 Relational DBMS Products emerged in the early 1980s.
Historical Development of Database
Technology (cont.)
 Object-oriented and emerging applications:
 Object-Oriented Database Management Systems
(OODBMSs) were introduced in late 1980s and early 1990s
to cater to the need of complex data processing in CAD and
other applications.
 Their use has not taken off much.
 Many relational DBMSs have incorporated object database
concepts, leading to a new category called object-relational
DBMSs (ORDBMSs)
 Extended relational systems add further capabilities (e.g.
for multimedia data, text, XML, and other data types)
Extending Database Capabilities
 New functionality is being added to DBMSs in the following areas:
 Scientific Applications – Physics, Chemistry, Biology - Genetics
 Earth and Atmospheric Sciences and Astronomy
 XML (eXtensible Markup Language)
 Image Storage and Management
 Audio and Video Data Management
 Data Warehousing and Data Mining – a very major area for future
development using new technologies
 Spatial Data Management and Location Based Services
 Time Series and Historical Data Management
 The above gives rise to new research and development in incorporating
new data types, complex data structures, new operations and storage and
indexing schemes in database systems.
Extending Database Capabilities
(cont.)
 Background since the advent of the 21 Century:
st

 First decade of the 21st century has seen tremendous growth in user
generated data and automatically collected data from applications and
search engines.

 Social Media platforms such as Facebook and Twitter are generating

millions of transactions a day and businesses are interested to tap into this
data to “understand” the users

 Cloud Storage and Backup is making unlimited amount of storage

available to users and applications
Extending Database Capabilities
(cont.)

Emergence of Big Data Technologies and NOSQL databases
 New data storage, management and analysis technology was necessary to deal with
the onslaught of data in petabytes a day (10**15 bytes or 1000 terabytes) in some
applications – this started being commonly called as “Big Data”.
 Hadoop (which originated from Yahoo) and Mapreduce Programming approach to
distributed data processing (which originated from Google) as well as the Google
file system have given rise to Big Data technologies Further enhancements are
taking place in the form of Spark based technology.
 NOSQL (Not Only SQL- where SQL is the de facto standard language for
relational DBMSs) systems have been designed for rapid search and retrieval from
documents, processing of huge graphs occurring on social networks, and other
forms of unstructured data with flexible models of transaction processing
When not to use a DBMS
 Main inhibitors (costs) of using a DBMS:
 High initial investment and possible need for additional
hardware.
 Overhead for providing generality, security, concurrency
control, recovery, and integrity functions.
 When a DBMS may be unnecessary:
 If the database and applications are simple, well defined,
and not expected to change.
 If access to data by multiple users is not required.
 When a DBMS may be infeasible:
 In embedded systems where a general purpose DBMS may
not fit in available storage
When not to use a DBMS
 When no DBMS may suffice:

 If there are stringent real-time requirements that may not be met because
of DBMS overhead (e.g., telephone switching systems)

 If the database system is not able to handle the complexity of data because
of modeling limitations (e.g., in complex genome and protein databases)

 If the database users need special operations not supported by the DBMS
(e.g., GIS and location based services).
Data Models

 Data Model:
 A set of concepts to describe the structure of a database, the operations for
manipulating these structures, and certain constraints that the database should
obey.
 Data Model Structure and Constraints:
 Constructs-A statement that lets users run a query by example. The user
specifies a range of values for the screen fields that correspond to the
database columns. The CONSTRUCT statement converts these values into a
Boolean expression that can be used in the WHERE clause of a SELECT
statement.
 Constructs are used to define the database structure
 Constructs typically include elements (and their data types) as well as groups of
elements (e.g. entity, record, table), and relationships among such groups
 Constraints specify some restrictions on valid data; these constraints must be
enforced at all times
Data Models

 Data Model Operations:

 These operations are used for specifying database retrievals and updates
by referring to the constructs of the data model.
 Operations on the data model may include basic model operations (e.g.
generic insert, delete, update) and user-defined operations (e.g.
compute_student_gpa, update_inventory)
Categories of Data Models

 Conceptual (high-level, semantic) data models:

 The conceptual data model describes the database at a very high level and is
useful to understand the needs or requirements of the database. It is this model,
that is used in the requirement-gathering process i.e. before the Database
Designers start making a particular database
 Physical (low-level, internal) data models:
 The physical Data Model is used to practically implement Relational Data Model.
Ultimately, all data in a database is stored physically on a secondary storage device
such as discs and tapes. This is stored in the form of files, records, and certain other
data structures.
 Implementation (representational) data models:
 This type of data model is used to represent only the logical part of the database and
does not represent the physical structure of the database. The representational data
model allows us to focus primarily, on the design part of the database.
Schemas versus Instances

 Database Schema:
 The description of a database.
 Includes descriptions of the database structure, data types, and the
constraints on the database.
 Schema Diagram:
 An illustrative display of (most aspects of) a database schema.
 Schema Construct:
 A component of the schema or an object within the schema, e.g.,
STUDENT, COURSE.
Schemas versus Instances

 Database State:
 The actual data stored in a database at a particular moment in time.
This includes the collection of all the data in the database.
 Also called database instance (or occurrence or snapshot).
 The term instance is also applied to individual database components, e.g.
record instance, table instance, entity instance
Database Schema vs. Database
State
 Database State:
 Refers to the content of a database at a moment in time.
 Initial Database State:
 Refers to the database state when it is initially loaded into the system.
 Valid State:
 A state that satisfies the structure and constraints of the database.
Database Schema
vs. Database State (continued)
 Distinction
 The database schema changes very infrequently.
 The database state changes every time the database is updated.

 Schema is also called intension.

 State is also called extension.
Example of a Database Schema
Example of a database state
Three-Schema Architecture

 Proposed to support DBMS characteristics of:

 Program-data independence.
 Support of multiple views of the data.
 Not explicitly used in commercial DBMS products, but has been useful
in explaining database system organization
Three-Schema Architecture

 Defines DBMS schemas at three levels:

 Internal schema at the internal level to describe physical
storage structures and access paths (e.g indexes).
 Typically uses a physical data model.
 Conceptual schema at the conceptual level to describe the
structure and constraints for the whole database for a
community of users.
 Uses a conceptual or an implementation data model.
 External schemas at the external level to describe the
various user views.
 Usually uses the same data model as the conceptual schema
The three-schema architecture
Three-Schema Architecture

 Mappings among schema levels are needed to transform

requests and data.
 Programs refer to an external schema, and are mapped by
the DBMS to the internal schema for execution.
 Data extracted from the internal DBMS level is reformatted
to match the user’s external view (e.g. formatting the
results of an SQL query for display in a Web page)
Data Independence

Data independence is a property of a database management system by

which we can change the database schema at one level of the database
system without changing the database schema at the next higher level
 Logical Data Independence:
 The capacity to change the conceptual schema without having to change
the external schemas and their associated application programs.
 Physical Data Independence:
 The capacity to change the internal schema without having to change the
conceptual schema.
 For example, the internal schema may be changed when certain file
structures are reorganized or new indexes are created to improve
database performance
Data Independence (continued)

 When a schema at a lower level is changed, only the mappings

between this schema and higher-level schemas need to be changed
in a DBMS that fully supports data independence.
 The higher-level schemas themselves are unchanged.
DBMS Languages

 Data Definition Language (DDL)

 Data Manipulation Language (DML)
 Storage Definition Language (SDL)
 If distinct languages are used, DDL is used to specify conceptual schema
and SDL is used to specify internal schema.
 The mapping between the two schemas may be specified in either of the
language.
 View Definition Language (VDL)
 For a true three-schema architecture, VDL is required for external schema
and its mapping with conceptual schema.
 In relational DBMSs, SQL is used in the role of VDL to define views as
results of predefined queries.
DBMS Languages

 Data Definition Language (DDL):

 Used by the DBA and database designers to specify the conceptual
schema of a database.
 In many DBMSs, the DDL is also used to define internal and external
schemas (views).
DBMS Languages

 Data Manipulation Language (DML):

 Used to specify database retrievals and updates
 DML commands (data sublanguage) can be embedded in a general-
purpose programming language (host language), such as C, C++, or Java.
 A library of functions can also be provided to access the DBMS from a
programming language
 Alternatively, stand-alone DML commands can be applied directly (called a
query language).
Types of DML
 High Level or Non-procedural Language:
 These include the relational language SQL
 Used in a standalone way
 Are “set”-oriented and specify what data to retrieve rather than how to
retrieve it. Also called declarative languages.
 Low Level or Procedural Language:
 These must be embedded in a programming language
 Retrieve data one record-at-a-time;
 Constructs such as looping are needed to retrieve multiple records, along
with positioning pointers.
DBMS Interfaces

 Stand-alone query language interfaces

 Example: Entering SQL queries at the DBMS interactive SQL interface (e.g.
SQL*Plus in ORACLE)
 Programmer interfaces for embedding DML in programming
languages
 User-friendly interfaces
 Menu-based, forms-based, graphics-based, etc.
DBMS Programming Language Interfaces

 Programmer interfaces for embedding DML in a programming

languages:
 Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for
Java)
 Procedure Call Approach: e.g. JDBC for Java, ODBC for other
programming languages
 Database Programming Language Approach: e.g. ORACLE has
PL/SQL, a programming language based on SQL;
User-Friendly DBMS Interfaces

 Menu-based, popular for browsing on the web

 Forms-based, designed for naïve users
 Graphics-based
 (Point and Click, Drag and Drop, etc.)
 Natural language: requests in written English
 Combinations of the above:
 For example, both menus and forms used extensively in Web database
interfaces
Other DBMS Interfaces

 Speech as Input and Output

 Web Browser as an interface
 Parametric interfaces, e.g., bank tellers using function keys.
 Interfaces for the DBA:
 Creating user accounts, granting authorizations
 Setting system parameters
 Changing schemas or access paths
Overview of Database Design Process

 Database Design
 To design the conceptual schema for a database application
 Conceptual modeling is a very important phase in designing a
successful database application.
 Entity-Relationship (ER) model is a popular high-level conceptual
data model.
 This model and its variations are frequently used for the conceptual
design of database applications, and many database design tools
employ its concepts.
Overview of Database Design Process

Slide
3- 53
The first step shown is requirements collection and analysis.
During this step, the database designers interview prospective database
users to understand and document their data requirements.
The result of this step is a concisely written set of users requirements.
These requirements should be specified in as a detailed and complete
form as possible.
In parallel with specifying the data requirements, it is useful to specify
the known functional requirements of the application.
These consist of the user defined operations (or transactions) that will
be applied to the database, including both retrievals and updates.
 Once the requirements have been collected and analyzed, the next
step is to create a conceptual schema for the database, using a high-
level conceptual data model.
 This step is called conceptual design. The conceptual schema is a
concise description of the data requirements of the users and
includes detailed descriptions of the entity types, relationships,
and constraints; these are expressed using the concepts provided
by the high-level data model.
The next step in database design is the actual implementation of the
database, using a commercial DBMS.
Most current commercial DBMSs use an implementation data model
such as the relational or the object-relational database model so the
conceptual schema is transformed from the high-level data model
into the implementation data model.
This step is called logical design or data model mapping; its result is a
database schema in the implementation data model of the DBMS.
 The last step is the physical design phase, during which the
internal storage structures, file organizations, indexes,
access paths, and physical design parameters for the
database files are specified.
 In parallel with these activities, application programs are designed
and implemented as database transactions corresponding to the high
level transaction specifications.
Example COMPANY Database

 We need to create a database schema design based on the following

(simplified) requirements of the COMPANY Database:
 The company is organized into DEPARTMENTs. Each department has a
name, number and an employee who manages the department. We keep
track of the start date of the department manager. A department may
have several locations.
 Each department controls a number of PROJECTs. Each project has a
unique name, unique number and is located at a single location.
Example COMPANY Database
(Contd.)
 We store each EMPLOYEE’s social security number, address, salary, sex,
and birthdate.
 Each employee works for one department but may work on several projects.
 We keep track of the number of hours per week that an employee currently
works on each project.
 We also keep track of the direct supervisor of each employee.
 Each employee may have a number of DEPENDENTs.
 For each dependent, we keep track of their name, sex, birthdate, and
relationship to the employee.
ER Model Concepts
 Entities and Attributes
 Entities are specific objects or things in the mini-world that are represented
in the database.
 For example the EMPLOYEE John Smith, the Research DEPARTMENT, the
ProductX PROJECT
 Attributes are properties used to describe an entity.
 For example an EMPLOYEE entity may have the attributes Name, SSN,
Address, Sex, BirthDate
 A specific entity will have a value for each of its attributes.
 For example a specific employee entity may have Name='John Smith',
SSN='123456789', Address ='731, Fondren, Houston, TX', Sex='M',
BirthDate='09-JAN-55‘
 Each attribute has a value set (or data type) associated with it – e.g.
integer, string, subrange, enumerated type, …
Types of Attributes

 Simple Attribute
 Each entity has a single atomic value for the attribute. For example,
SSN or Sex.
 Composite Attribute
 The attribute may be composed of several components. For example:
 Address(Apt#, House#, Street, City, State, ZipCode, Country), or
 Name(FirstName, MiddleName, LastName).
 Composition may form a hierarchy where some components are
themselves composite.
 Multi-valued Attribute
 An entity may have multiple values for that attribute. For example,
Color of a CAR or PreviousDegrees of a STUDENT.
 Denoted as {Color} or {PreviousDegrees}.
Types of Attributes (2)

 In general, composite and multi-valued attributes may be nested

arbitrarily to any number of levels, although this is rare.
 For example, PreviousDegrees of a STUDENT is a composite multi-valued
attribute denoted by {PreviousDegrees (College, Year, Degree, Field)}
 Multiple PreviousDegrees values can exist
 Each has four subcomponent attributes:
 College, Year, Degree, Field
Example of a composite attribute
Entity Types and Key Attributes (1)

 Entities
with the same basic attributes are
grouped or typed into an entity type.
 Forexample, 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
 See CAR example on next slide
Entity Type CAR with two keys and a
corresponding Entity Set
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
Initial Design of Entity Types for the
COMPANY Database Schema

 Based on the requirements, we can identify four initial entity types in

the COMPANY database:
 DEPARTMENT
 PROJECT
 EMPLOYEE
 DEPENDENT
 Their initial design is shown on the following slide
 The initial attributes shown are derived from the requirements
description
Initial Design of Entity Types:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Slide
3- 70
Refining the initial design by introducing
relationships

 The initial design is typically not complete

 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)
 We introduce relationship concepts next
Relationships and Relationship Types (1)

 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 instances of the WORKS_FOR N:1 relationship
between EMPLOYEE and DEPARTMENT
Relationship instances of the M:N WORKS_ON
relationship between EMPLOYEE and PROJECT

Slide
3- 74
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)

 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
Refining the COMPANY database schema
by introducing relationships

 By examining the requirements, six relationship types are

identified
 All are binary relationships( degree 2)
 Listed below with their participating entity types:
 WORKS_FOR (between EMPLOYEE, DEPARTMENT)
 MANAGES (also 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 are:
WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Slide
3- 78
Recursive Relationship Type

 An relationship type whose with the same participating

entity type in distinct roles
 Example: the SUPERVISION relationship
 EMPLOYEE participates twice in two distinct roles:
 supervisor (or boss) role
 supervisee (or subordinate) role
 Each relationship instance relates two distinct EMPLOYEE
entities:
 One employee in supervisor role
 One employee in supervisee role
Weak Entity Types

 An entity that does not have a key attribute

 A weak entity must participate in a identifying relationship type with an
owner or identifying entity type
 Entities are identified by the combination of:
 A partial key of the weak entity type
 The particular entity they are related to in the identifying entity type
 Example:
 A DEPENDENT entity is identified by the dependent’s first name, and the
specific EMPLOYEE with whom the dependent is related
 Name of DEPENDENT is the partial key
 DEPENDENT is a weak entity type
 EMPLOYEE is its identifying entity type via the identifying relationship
type DEPENDENT_OF
Constraints on Relationships

 Constraints on Relationship Types

 (Also known as ratio constraints)
 Cardinality Ratio (specifies maximum participation)
 One-to-one (1:1)
 One-to-many (1:N) or Many-to-one (N:1)
 Many-to-many (M:N)
 Existence Dependency Constraint (specifies minimum
participation) (also called participation constraint)
 zero (optional participation, not existence-dependent)
 one or more (mandatory participation, existence-dependent)
Many-to-one (N:1) Relationship
Many-to-many (M:N)
Relationship
Displaying a recursive
relationship
 In a recursive relationship type.
 Both participations are same entity type in
different roles.
 For example, SUPERVISION relationships
between EMPLOYEE (in role of supervisor
or boss) and (another) EMPLOYEE (in role
of subordinate or worker).
 In following figure, first role participation labeled with 1 and second
role participation labeled with 2.
 In ER diagram, need to display role names to distinguish
participations.
A Recursive Relationship
Supervision`
Recursive Relationship Type is: SUPERVISION
(participation role names are shown)

Slide
3- 86
Attributes of Relationship types

 A relationship type can have attributes:

 For example, HoursPerWeek of WORKS_ON
 Its value for each relationship instance describes the number of hours per
week that an EMPLOYEE works on a PROJECT.
 A value of HoursPerWeek depends on a particular (employee, project)
combination
 Most relationship attributes are used with M:N relationships
Example Attribute of a Relationship Type:
Hours of WORKS_ON

Slide
3- 88
Summary of notation for ER diagrams

Slide
3- 89