Created by Turbolearn AI

Introduction
A database is a collection of related data with an implicit meaning. It represents some aspect of the real world and is
designed, built, and populated with data for a specific purpose.

For example, a UNIVERSITY database might contain information concerning students, courses, and grades, with data
records for STUDENT, COURSE, SECTION, GRADE_REPORT, and LECTURER.

Databases can also be specialized:

Multimedia database: Stores images, audio clips, and video streams.

Spatial database: Stores and analyzes maps, weather data, and satellite images.
Temporal database: Stores historical data.

A Database Management System (DBMS) is a collection of programs used to create and maintain a database.

File-Based Approach
In a file-based approach, data is stored in separate computer files and processed by computer programs or applications.

Consider a property rental company:

Sales Department: Uses Sales Application Programs to manage Sales files.

Contract Department: Uses Contract Application Programs to manage Contract files.

Problems with the File-Based Approach

Data Redundancy: The same information is kept in multiple places, wasting storage space and duplicating effort.
Data Inconsistency: Various copies of the same data conflict, leading to inconsistency in data format.
Rigid Data Structure: File structures suitable for one application might not suit others when files are shared.
Physical Data Dependency: Changes to the data file structure require alterations in all application programs using
that file.
No Support of Concurrency Control: A file being processed by one application is unavailable for other applications
or ad hoc queries.

Shared File Approach

The shared file approach attempts to alleviate some problems of the file-based approach by sharing data (files)
between different applications. This alleviates data redundancy and solves the data inconsistency problem across
different versions of the same file. But other problems remain.

Database Approach
The database approach arose because the definition of data was embedded in application programs, and there was no
control over access and manipulation of data beyond that imposed by the application programs. This approach utilizes a
Database Management System (DBMS).

A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and
sharing databases among various users and applications.

Data vs. Information vs. Knowledge vs. Wisdom

Page 1
 Created by Turbolearn AI

Data: 308
Information: 308 km
Knowledge: "308 km is quite a far distance"
Wisdom: "It's very difficult to walk 308 km, but vehicle transport is OK"

Key Components of the Database Approach

System catalog (metadata): Provides a description of data to enable program-data independence.
Entities, attributes, and relationships: Logically related data comprises these, reflecting an organization's
information.
DBMS: Facilitates defining, constructing, manipulating, and sharing databases.

Capabilities of the Database Approach

Specify data types, structures, and constraints.
Query data: retrieve, update (insert, delete, modify).
Control access to the database using security, integrity, concurrency, and recovery control systems.

Roles in the Database Environment

Database Administrator (DBA): Authorizes access, coordinates, monitors use, and acquires resources.
Database Designers: Identify data to be stored and choose appropriate structures.
Application Programmers: Implement specifications as programs.
End Users: Require access to the database for their jobs.

Workers Behind the Scene

DBMS System Designers and Implementers: Design and implement DBMS modules and interfaces.
Tool Developers: Design and implement tools.
Operators and Maintenance Personnel: Responsible for running and maintaining the database system's hardware
and software.

Characteristics of the Database Approach

Self-describing nature: The database system contains its own description.
Insulation between programs and data: Achieved through program-data independence and data abstraction.
Support of multiple views of the data.
Sharing of data and multi-user transaction processing.

Three-Schema Architecture and Data Independence

The three-schema architecture aims to:

Enable all users to access the same data.

Hide physical database storage details from users.
Allow the DBA to change storage structures without affecting users' views.
Protect the internal structure from changes to physical storage aspects.
Allow the DBA to change the conceptual structure without affecting all users.

Levels of the Three-Schema Architecture

Page 2
 Created by Turbolearn AI

External Level: Users' view of the database, relevant to a particular user.

Conceptual Level: Community view of the database, describing what data is stored and the relationships among
the data.
Internal Level: Physical representation of the database on the computer, describing how the data is stored.

Database Languages
Data Definition Language (DDL): Allows the DBA or user to describe and name entities, attributes, and
relationships, including integrity and security constraints.
Data Manipulation Language (DML): Provides basic data manipulation operations (select, insert, update, delete).
Data Control Language (DCL): Defines activities such as granting privileges to users and defining when changes
should be made to the database.

Types of DML
Procedural DML: User specifies how to manipulate data.
Non-Procedural DML (Declarative Language): User states what data is needed rather than how to retrieve it (e.g.,
SQL, QBE).
Fourth Generation Languages (4GLs): Non-procedural languages, application generators, and report generators.

Data Models, Database Schema, and Database State

Data Model: An integrated collection of concepts for describing data, relationships, and constraints in an
organization.

Categories of Data Models

Object-based (Conceptual): ER, Object-Oriented.
Record-based (Representational): Relational, Network, Hierarchical.
Physical: Used to describe data at the internal level.

Other Definitions
Database Schema: The description of a database, specified during database design, not expected to change
frequently.
Schema Diagram: A displayed schema.
Database State (Snapshot): The data in the database at a particular moment in time.

Data Management Systems Framework

Extending database capabilities for new applications, such as storage and retrieval of images, videos, data mining,
spatial databases, and time-series applications. This involves:

More complex data structures.

New data types.
New operations and query languages.
New storage and retrieval methods.
New security mechanisms.

Overview of Database Design Process

Database design focuses on designing the conceptual schema for a database application. Applications design focuses
on the programs and interfaces that access the database.

The database design process generally involves:

Page 3
 Created by Turbolearn AI

1. Requirements collection and analysis

2. Conceptual design
3. Logical design (data model mapping)
4. Physical design

A Sample Database Application

Consider designing a database for a company that keeps track of employees, departments, and projects.

Requirements
The company is organized into DEPARTMENTs. Each department has a unique name, a unique number, and a
particular employee who manages the department. The start date when that employee began managing the
department is tracked. A department may have several locations.
A department controls a number of PROJECTs, each of which has a unique name, a unique number, and a single
location.
EMPLOYEE's name, Social Security number, address, salary, sex, and birth date are stored. An employee is
assigned to one department but may work on several projects. The current number of hours per week that an
employee works on each project is tracked. The direct supervisor of each employee is also tracked.
DEPENDENTs of each employee are tracked, including first name, sex, birth date, and relationship to the
employee.

What is the ER Model?

The Entity-Relationship (ER) model is a popular high-level conceptual data model used for the logical organization of
data within a database system.

ER Diagrams (ERD) are diagrammatic notations associated with the ER model, used in conceptual design.

Why use ER data modeling?

User requirements can be specified formally and unambiguously.
It can be easily understood by ordinary users.
It provides an effective bridge between user requirements, database design, and implementation.
The conceptual data model is independent of any particular DBMS.

ER Model Concepts
The ER model describes data as:

Entities
Attributes
Relationships

Entity
A thing in the real world with an independent existence.

An entity may be an object with a physical existence (a person, a car, a house, or an employee) or an object with a
conceptual existence (a company, a job, or a university course).

Attribute
Properties that describe an entity.

Page 4
 Created by Turbolearn AI

For example, an EMPLOYEE entity may have Name, SSN, Address, Sex, and BirthDate.

Each attribute has a value set (or data type) associated with it.

Types of Attributes

Attribute
Description Example
Type

Simple Has a single atomic value. SSN, Sex

Name (First name, Middle
Composite Composed of several components.
name, Last name)
Colors of a Car {Color},
Multi-valued Has multiple values.
Phones of a Person {Phone}
Derived Has a value that is derivable from values of related attributes. Number of students in a class
Complex
Combination of composite and multivalued attributes.
Attribute
Attributes whose values are distinct for each individual entity in an entity
Key SSN of EMPLOYEE
set. Uniqueness property must hold for every entity set of the entity type

Entity Types
Collection (or set) of entities that have the same attributes.

Entity Types and Attributes

Let's consider a scenario involving a company database. We need to store information about employees, including their
name, Social Security number, address, salary, sex, and birth date. Each employee is assigned to one department but
may work on several projects. The database should keep track of the number of hours per week an employee works on
each project and who their direct supervisor is. Additionally, it should store information about the employee's
dependents, including their first name, sex, birth date, and relationship to the employee.

Initial Conceptual Design of COMPANY Database

Here's an initial ERD for the COMPANY database:

Page 5
 Created by Turbolearn AI

The ERD illustrates the relationships between entities such as EMPLOYEE, PROJECT, DEPARTMENT, and DEPENDENT.
The EMPLOYEE entity is linked to the PROJECT entity through the WORKS_ON relationship and to the DEPENDENT
entity. The DEPARTMENT entity is related to both EMPLOYEE and PROJECT entities.

Here's another look at an ERD for a company database:

This ERD visually represents the database structure and the relationships between entities like EMPLOYEE,
DEPARTMENT, PROJECT, and DEPENDENT.

Relationships and Relationship Types

A relationship type R among n entity types E1, E2, ..., En defines a set of associations among entities from these
entity types. For example, the relationship type WORKS_FOR exists between EMPLOYEEs and DEPARTMENTs.

Relationship instance Each relationship instance ri associates n individual entities (e1, e2, ..., en), where
each entity ej in ri is a member of entity set Ej.

For instance, an employee John Smith works on the project ProductX.

The degree of a relationship type refers to the number of participating entity types. Relationships can be binary (degree
2), ternary (degree 3), or n-ary (degree n). It's possible to have multiple relationship types with the same participating
entity types.

Here's an example of employees working for departments:

EMPLOYEE – Works-for – DEPARTMENT

EMPLOYEE – Manages – DEPARTMENT

This diagram illustrates the relationships between employees, departments, and the "works_for" relationship. It shows a
many-to-one relationship, where multiple employees can work for one department.

Page 6
 Created by Turbolearn AI

Consider also recursive relationships. In these, the same entity type participates more than once in a relationship type,
but in different roles. For example, in SUPERVISION relationships between EMPLOYEEs, one employee is the boss and the
other is the subordinate. It's crucial to specify the role each participating entity plays.

The image above illustrates the relationships between employees and their supervisors.

Constraints on Binary Relationship Types

Structural Constraints

Page 7
 Created by Turbolearn AI

Structural constraints express the semantics of a relationship. These constraints include the cardinality ratio and
participation constraints.

Cardinality Ratio: Specifies the maximum number of relationship instances an entity can participate in a binary
relationship.
One-to-one (1:1)
One-to-many (1:M) or Many-to-one (M:1)
Many-to-many (M:N)

The image above illustrates a one-to-one relationship where one employee manages one department.

The image illustrates a many-to-many relationship between employees and the projects they work on.

Page 8
 Created by Turbolearn AI

Participation Constraint: Specifies whether the existence of an entity depends on its being related to another
entity.
Mandatory (Total Participation): Every instance of a participating entity type must participate in the
relationship (denoted by a double line).
Optional (Partial Participation): Not every instance of a participating entity type must participate in the
relationship (denoted by a single line).

Relationship Type Notations

Element Description

Entity Type EMPLOYEE, DEPARTMENT

Relationship Type Worksfor
Cardinality Ratio N(many), 1 (one)
Participation Constraint Double line (mandatory), Single line (optional)

For example, an EMPLOYEE works for one DEPARTMENT. A DEPARTMENT has many EMPLOYEEs. An EMPLOYEE must work for a
DEPARTMENT, while a DEPARTMENT may have no EMPLOYEEs.

Attributes of Relationship Types

Relationship types can have attributes.

Example: HoursPerWeek of WORKS_ON

In a 1:1 relationship type, relationship attributes can be migrated to either participating entity type.
In a 1:N relationship type, relationship attributes can be migrated only to the entity type on the N-side of the
relationship.
In M:N relationship types, relationship attributes cannot be migrated to any entity type.

Page 9
 Created by Turbolearn AI

This ERD illustrates the relationships between various entities such as EMPLOYEE, DEPARTMENT, PROJECT, and
DEPENDENT, including their attributes and relationship types.

Weak Entity Types

Weak entity types do not have key attributes of their own and are identified by their relationship to specific entities from
another entity type through an identifying relationship.

Identifying Relationship Relates a weak entity type to its owner. It always has a total participation
constraint.

Entities in a weak entity type are identified by:

1. A partial key of the weak entity type.

2. The particular entity they are related to in the identifying entity type.

Notations for Relationship Types

Element Description

Weak entity type DEPENDENT

Identifying relationship Of
Partial key Name
Identifying/Parent Entity EMPLOYEE
Attributes of Parent SSN

ER Diagram and Naming Conventions

An ER model is visually represented using an ER diagram. Proper naming of schema constructs is essential:

Choose names that convey the meaning of the constructs in the schema.
Use nouns for entity type names.
Use verbs for relationship type names.
Choose binary relationship names to make the ER diagram readable from left to right and top to bottom.

Here is a summary of notation for ER diagrams:

Page 10
 Created by Turbolearn AI

Case Study: Designing the Company Database

To design the company database:

The company is organized into departments. Each department has a unique name and number. A particular
employee manages the department, and the start date when that employee began managing the department is
recorded. A department may have several locations.
A department controls a number of projects. Each project has a unique name, a unique number, and a single
location.
Store the employee's name, Social Security number, address, salary, sex, and birth date. An employee is assigned
to one department but may work on several projects, not necessarily controlled by the same department. Track the
current number of hours per week that an employee works on each project and the direct supervisor of each
employee.
Keep track of the dependents of each employee, including their first name, sex, birth date, and relationship to the
employee.

Here is an ERD for the COMPANY Database:

Page 11
 Created by Turbolearn AI

Case Study: Designing a Library Database

Let's explore a case study about designing a database for a small library:

Alternative Diagrammatic Notations

Min-Max Notation
The (min, max) notation specifies structural constraints on relationships, replacing the cardinality ratio and
single/double line notation for participation constraints. It associates a pair of integer numbers (min, max) with each
participation of an entity type E in a relationship type R, where 0 ≤ min ≤ max and max ≥ 1.

Page 12
 Created by Turbolearn AI

The image shows relationships between EMPLOYEE and DEPARTMENT entities. In the top section, an employee works for one
department (1,1), while a department can have between four and many employees (4,N). In the bottom section, an
employee may manage zero or one department (0,1), and a department is managed by exactly one employee (1,1).

Here’s another ER diagram using (min, max) notation:

UML Methodology
The Unified Modeling Language (UML) is used extensively in software design. UML class diagrams correspond to
entities in the ER model.

Page 13
 Created by Turbolearn AI

UML Class Diagrams

A class in UML includes three sections:

1. The top section gives the class name.

2. The middle section includes the attributes.
3. The last section includes operations that can be applied to individual objects.

Associations in UML represent relationship types, while relationship instances are represented as links. Binary
associations are represented as a line connecting participating classes and may optionally have a name. Link attributes
are placed in a box connected to the association’s line by a dashed line.

Key Aspects of UML Class Diagrams

Multiplicities: Represented as min..max, where an asterisk (*) indicates no maximum limit on participation.
Types of Relationships: Include association and aggregation.
Distinguish between unidirectional and bidirectional associations.
Model weak entities using qualified associations.

Here's a comprehensive guide to ER diagram notation:

Page 14
 Created by Turbolearn AI

Problems with ER Models

Semantic Constraints
Some constraints cannot be directly expressed in the ER diagram and must be enforced through trigger mechanisms,
application programs, or other means.

Examples:
The age of an employee must be greater than 18 years.
The salary of a department manager must be higher than that of other employees in the department.
When increasing an employee's salary, the increase must not be more than 20% of their current salary.

Connection Traps
Connection traps often result from misinterpreting the meaning of certain relationships. The two main types of
connection traps are fan traps and chasm traps.

Fan Trap: Occurs when a model represents a relationship between entity types, but the pathway between certain
entity occurrences is ambiguous. Typically involves two or more 1:N relationships fanning out from the same entity.
Chasm Trap: Occurs when a model suggests the existence of a relationship between entity types, but the pathway
does not exist between certain entity occurrences. Usually involves optional participation.

Fan Trap Example

Page 15
 Created by Turbolearn AI

Exercises

University Database
Design a database for a university that maintains records of its departments, lecturers, course modules, and students.
The university consists of departments, each with a unique name and descriptive attributes. Each department has
several lecturers, one of whom is the head. Lecturers have different names and must teach one or more modules. A
lecturer belongs to only one department. Modules are offered by departments and taught by one lecturer, and they must
be attended by some students. Each module has a unique module number. Students must enroll for a number of
modules, and each student is given a unique student number.

Small Library Database

Design a database for a small library to store data about branches and books. Each branch has a unique ID and name,
and an address. For each book, the database should record a unique book ID, title, publisher, and publication year. A
book may have several authors, each represented by a name. A book typically has several copies, each with a copy
number. The availability and total number of copies of each book should be tracked.

Page 16