CHAPTER 1: INTRODUCTION

Databases and Database Users

Types of Databases and Database Applications

 Traditional Applications:
o Numeric and Textual Databases:
 Stored in tables (rows & columns).
 Data types: numbers, text.
 Example: student records, banking systems.
 More Recent Applications:
o Multimedia Databases
o Geographic Information Systems (GIS)
o Data Warehouses
o Real-time & Active Databases
o Many other applications: social media, e-commerce, AI.
 This course focuses on traditional applications

Basic Definitions

Database: Collection of related data.

Data: Known facts that can be recorded and have meaning

- Example: “John, 20 years old, Math = 9” are data

- Can be numbers, text, images, …

Mini-world: a part of the real world represented in the database

- Example: in a university  students, courses, grades

- Not everything is stored (e.g., weather, traffic) <-> irrelevant

DBMS (Database Management System): Software to create, store, query, and manage databases

- Examples: MySQL, Oracle, SQL Server, PostgreSQL

DBS (Database System): combination of DBMS + database (data) + sometimes applications using DBMS.

- Example: Student Management System  DB(tables), DBMS (MySQL), app (web interface)
- Model: User <-> Website/ App <-> DBMS (SQL) <-> Tables (Data)

Figure 1.1 – Simplified Database System Environment

- Users/Programmers: interact with system via applications or SQL queries.

- Application Programs/Queries: send requests (e.g., insert, update, select).
- DBMS Software:
o Process Queries/Programs: receive request + check, optimize, execute queries.
 Example: SELECT * FROM Student
o Access Stored Data: read/write data in storage.
- Stored Database Definition (Meta-data): structure info (tables, datatypes, keys).
 o Example: MSSV INT PRIMARY KEY, Name VARCHAR(50).
- Stored Database: actual data (records).
o Example: (MSSV=001, Name="Nguyen Van A", Age=20)

Flow: Users  App/queries  DBMS  Meta-data + Database  Results back to users.

Typical DBMS Functionality

1. Define a database  specify data types (INT, VARCHAR), structures (tables, relations),
constraints (primary keys, foreign keys, uniqueness, rules)
2. Load  Create DB + load initial data on secondary storage
3. Manipulate the database:
 Retrieval: queries, reports (e.g., SELECT)
 Modification: insert, delete, update
4. Access through App/ Web applications  users interact indirectly
5. Processing & Sharing by concurrent users/ programs:
- Many users/ programs can use the DB at the same time
- DBMS ensures data stays valid & consistent (no conflicts, no corruption)
o Example: when 100 people booked the last seat, DBMS will make sure only 1 can book.

Others:

- Protection or Security: prevent unauthorized access

o Example: Only admin can remove the data, students can only see their grades
- Active processing: triggers, automatic internal actions
o Example: when the account < 100k  the system will automatically send the warning
 - Presentation and Visualization of data: help create reports, charts, dashboards, ...  easy to
analyze the data
- Maintain the database and associated programs over the lifetime of the database application
included backup, updates, bug fixes
o Called database, software, and system maintenance

Example of a Database (With a Conceptual Data Model)

Mini-world: University environment

Entities:

 STUDENTS: Doan Quoc Hung, 10423049

 COURSES: CSE
 SECTIONS (of courses): Math 101 can have 2 sections (morning and afternoon)
 DEPARTMENTS (Faculty): IT department, Math Department
 INSTRUCTORS: Christian

Relationships:

 SECTIONS belong to COURSES: Section 1 is for Database 101

 STUDENTS take SECTIONS: Student A enrolls in Part 1 of Database 101
 COURSES have prerequisite COURSES: Database 201 requires Database 101 to be taken
 INSTRUCTORS teach SECTIONS: Mr. Nam teaches Part 1 of Database 101
 COURSES offered by DEPARTMENTS: Database 101 is in the IT Department
 STUDENTS major in DEPARTMENTS: Student A majors in IT Science

Expressed in: Conceptual Data Model (e.g., ER – Entity Relationship model).

Main Characteristics of the Database Approach

Self-describing nature of a database system:

- DBMS catalog, like the “manual” or “map” of the entire databases, stores data description (e.g.,
structures, types, constraints, …), not store the value
o Example: when create table Student(id INT, name VARCHAR(100)), the catalog will write
down:
 There is one table named Student
 This table has one column ID type INT & one column NAME type VARCHAR(100)
 The column ID is the Primary key

- Description = meta-data (data about data) = schema

o Example:
 Data  Student: id = 1, name = “An”
 Meta data  Student Table has column id (INT, PK) and name(VARCHAR(100))
- Enables DBMS to work with different applications, not have to rewrite the system again
- Only cares about the logic, not how to store, DBMS will search meta-data in catalog to know
which tables are existed, their names, types, …
o Example: app A use web for students to track their GPA, app B use app mobile to sort
students’ GPA > 3.5  Both 2 apps do not need to know where the files are located, in
which blocks, index, …  DBMS will use meta-data to understand and send the result

Program-data independence:

- Separation between programs and data.

- Change in data structures/storage does not require changing all access programs.
 Make System easy to maintain, flexible and labor-saving
  1 relation = 1 table

Data Abstraction:

 DBMS provides data model to describe the model and hide storage details.
 Users/ Programmers refer to the data model constructs (table, columns, relationships, …) rather
than data storage details (file, block, index)
o Example: when write SELECT name FROM Student  only need to know the information
of Student table and the column name, not which file on which disk that stored values

Support of Multiple Views of the data:

 Each user can have a different view of the DB.

 Views show only relevant data → simplify usage + improve security.
o Example:
 Students only see their courses and grades
 Teachers know which classes they teach
Sharing & Multi-user Transaction Processing

 DBMS allows concurrent users (many users at the same time) to retrieve/update data.
 Concurrency control: ensures each transaction is executed correctly or aborted.
 Recovery subsystem:
o If a transaction is completed (commit) → all changes must be saved permanently in the
DB.
o If there is a problem (power failure, crash) → DBMS restores the correct state.
 OLTP (Online Transaction Processing): key DB application, supports hundreds of concurrent
transactions per second (e.g., banking, ticketing).

Database Users

Divided into two main groups:

Actors on the Scene (Mainly focus on this)

- Directly use/ control DB content & design, develop, maintain DB applications

- Roles:
o Database Administration (DBA): Authorize database access, coordinate and control its
usage, acquire software & hardware resource, and monitor performance  if the
system gets slow during registration week, they tune indexes or allocates more storage
Example: a DBA creates user accounts for professors, students and staffs
 Professors: can insert/ update student grades
 Students: can only read their own grades
 Staff: can insert new students into the system
 DBA = system manager who ensures secure and efficient operation
o Database Designers: define DB content, structures, constraints and functions/
transactions. They must communicate with end-users to understand their needs
Example: The Database Designers should:
 Design tables: a Student Table (ID, name, major), a course table (course
ID, title, credits) and a grades table (student ID, course ID, grade)
 Define constraints: each student must have a unique ID (primary key)
 Discuss with professor and staff to understand what data is needed
(transcript, GPA calculation)
 Database designer = architect who builds the logical structure of the database
o End-users: directly use database for queries, reports or updating the database content,
can be categorized based on their usage and expertise:
Casual End-users: occasional access, only use DB when needed
 Professors check students’ grades
Naïve/ Parametric: the largest group of end-users, use the predefined functions
called “canned transactions”, not need to understand complex structure
 Bank-tellers: deposit/withdraw money basing on available form
 Reservation clerk: book and cancel through the system with fixed
functions.
 less creative, but involves repetitive operations during the shift.
 Sophisticated: experts (business analysts, scientists, engineers); use advanced
tools/software to analyze DB directly.
Stand-alone: maintain personal DBs via ready-to-use apps (e.g., tax program,
address book).
 Example: a student maintains their budget using excel
- Illustration: in a university, DBA give permission for teachers to enter scores, Database Designers
designs the “Students” and “Course” tables, and students look up scores via the app.

Workers behind the Scene

- Not directly work with real data of users

- Build and maintain the DBMS software and supporting systems operators
- Roles:
o DBMS developers: MySQL, Oracle creators
o Tool developers: backup, query optimizers
o Systems operators: manage servers, OS, backups
- Example: MySQL team develops the DBMS software

Advantages of Using the Database Approach

1. Data Management

 Control redundancy → avoid duplicates.

 Persistent storage → long-term saving of data/objects. (in OODBMS)
 Represent complex relationships → model real-world links.

2. Security & Integrity

 Access control → restrict unauthorized users.

 Integrity constraints → enforce valid & consistent data.
  Backup & recovery → protect data from failures.

3. Efficiency & Performance

 Efficient queries → indexes & structures improve performance.

 Data sharing → multi-user concurrent access.

4. Intelligence & Flexibility

 Multiple interfaces → support different user types (end-users, administrations, designers, …)

 Rules & inferences → triggers, deductive/active rules.

Additional Implications of Database Approach

 Potential for enforcing standards

o Very crucial for large organizations
o DB helps maintain uniform standards: data names, display formats, report structures,
metadata (data description), web layouts.
 Ensures consistency, easier management, and scalability.
 Reduced development time:
o new apps can be developed faster
o Since the database and tools already exist, developers only add application logic instead
of starting from scratch  reuse existing DBMS.
 Flexibility to change data structures:
o DB structures can evolve with new requirements (adding tables, columns, relationships)
 DBMS supports system evolution without disrupting the entire application.
 Availability of current information
o Very important for online real-time systems (airline, hotel, car reservations).
o Users always see up-to-date information, avoiding errors from outdated data.
 Economies of scale
o Each department builds separate systems  wasteful overlap of resources and
personnel.
 A shared database across departments saves costs and increases efficiency.

Historical Development of Database Technology

1960s–1970s: Early Models

  Hierarchical & Network Models → dominated DB use.
 Still used today (esp. hierarchical).

1970s–1980s: Relational Model

 Introduced by Codd (1970), researched in IBM & universities.

 Commercial RDBMS products in early 1980s (Oracle, DB2, SQL Server).

1980s–1990s: Object-oriented & Emerging

 OODBMS introduced for complex data (CAD, multimedia).

 Limited adoption, very complex, lack of standardization→ led to ORDBMS (object-relational).
 Extended RDBMS add support (multimedia, XML, new types).

1990s–present: Web & E-commerce

 Web data in HTML; XML standards enabled data exchange.

o In HTML, the tags are predefined by the W3C (<h1>, <p>, <table>), and each tag has a
fixed meaning and rendering behavior in web browsers.
o In XML, however, the user defines the structure and tags (<student> ... </student>).
These tags do not have predefined meanings — they simply represent the data, and
their interpretation depends on the application that reads the XML.
 HTML is designed for displaying information to human on websites, while XML is
designed for computers to understand, store, and exchange data
 E-commerce apps widely use DB + XML.
 PHP, JavaScript → dynamic pages + database access/update through Web pages.

Extending Database Capabilities

In recent years, DBMS no longer store only simple tabular data. They have been extended to support
many different types of data and more complex applications.

 Scientific applications → large and complex scientific data (genes, climate, physics).
 XML (eXtensible Markup Language) → support for structured data exchange between systems.
 Image/Audio/Video management → handle multimedia data efficiently (YouTube, Spotify).
 Data Warehousing & Data Mining → store huge datasets and extract patterns/knowledge.
 Spatial data → manage maps, GIS, location-based services.
  Time series & historical data → manage stock prices, IoT sensors, logs.
 These requirements forced DBMS to research new ways of storing, indexing, and operations to handle
modern data.

When not to use a DBMS

Main inhibitor (Costs) of using a DBMS

 High initial investment (buy DBMS, license, extra hardware, …)

 Overhead (additional cost) from general features (security, concurrency, recovery, integrity).

Unnecessary cases

 Database & apps are simple, fixed, rarely change.

 Strict real-time requirements (DBMS overhead may too slow).
 Single-user access only (no need for sharing).

DBMS may not suffice

 DB cannot handle data complexity because of modeling limitations.

 Users require special operations that are not supported by DBMS.

 DBMS is very powerful, but if the data is simple, requires high real-time, or has special calculations
beyond the capabilities of DBMS → DBMS should not or cannot be used.

------------------------------------------------------------------------------------------------------------------------------------------

CHAPTER 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE

Data Models

Definition: Concepts that describe the structure of database, operations for manipulating these
structures, and constraints that the DB should obey

 Data models = structure + operations + constraints

Structure:

- Constructs = building blocks of DB structure. They include:

o Elements (attributes/ column, data types)
o Groups (entity, record, table/ relation)
o Relationships among groups
- Image: construct = bricks, structure = a house built from those bricks

Constraints:

- Rules restricting valid data (e.g., age ≥ 18, each Section must belong to a Course)
- Must always be enforced.
Operations

- Purpose: Define how to retrieve and update data using model constructs.
- Types:
o Basic operations → built-in, generic (insert, delete, update, retrieve).
o User-defined operations → specific to user’s application needs (e.g.,
compute_student_gpa, update_inventory).

Categories of Data Models

Conceptual (high-level, semantic)

- Close to user perception of data, even normal people can understand it

- Entity-based / Object-based (e.g., ER model)
- Represents entities, attributes, relationships
Physical (low-level, internal)

- Describes storage details inside computer, really close to hardware

- Includes files, indexes, pointers, pages
- Found in DBMS design/admin manuals

Implementation (representational) = logical data model

- Between conceptual & physical

- Used in commercial DBMS
- Example: Relational data model (tables, rows, columns), Network, Hierarchical, …

Schemas vs Instances

Database Schema

- Description of the database (structure, data types, constraints).

- Changes very infrequently  called intension (design intent)
- Schema diagram: illustrative display of the schema.
- Schema construct: individual component in the schema (e.g., STUDENT, COURSE).
o Example:
 Database Schema for student system:
 STUDENT (SID, Name, Major, GPA)
 COURSE (CID, Title, Credits)
 ENROLL (SID, CID, Grade)

 STUDENT(SID: int, Name: varchar, Major: varchar, GPA: float)  Schema

 Schema Constructs can be:

 Entities/ Tables: STUDENT, COURSE, ENROLL
 Attribute/ Column: SID, Name, Major, GPA, …
 Constraints: Primary key, foreign key, …

 Schema = the blueprint of the entire database.

Database State

- The actual data (content) stored at a specific moment in time.

- Also called instance / occurrence / snapshot.
- Changes every time when the DB is updated (insert, delete, update)

 also called extension (specific data extended from design)

- Initial database state: when the database is first loaded

- Valid state: data that satisfies the structure and all constraints of the DB
- The term instance can also apply to components (e.g., record instance, table instance).
o Database Instance: all the data in DB at a specific point in time
 Example: all tables (STUDENT, COURSE, ENROLL) and their data on today
o Table Instance: data of a single table at that moment
 Example: this whole STUDENT table with its 2 rows = table instance
 o Record (or Row) Instance: a single row in a table
 Example: (S1, Alice, CS, 3.5) in the STUDENT table

 Instance = the snapshot of data at a given moment.

Three-Schema Architecture

Purpose:

- Provide program–data independence.

 Data independence = ability to change how data is stored (physical) or structured
(logical) without changing application programs.
 Applications only need to work with the schema at the level they require (usually
external or conceptual), without needing to care about how the data is actually stored
underneath.
 Example:
 Physical: Moving a table to SSD instead of HDD → app code doesn’t change.
 Logical: Splitting FULL_NAME into FIRST_NAME + LAST_NAME → queries using
FULL_NAME view still work.
- Support multiple user views.
- Not directly used in commercial DBMS, but explains DB system organization.

Levels of Schema:

Internal Schema (Physical level):

- Describes how data is stored physically (storage structures, indexes, access paths)
- Uses a physical data model.
- Example:
o A STUDENT table is stored as a file with pages and records
o An index on ID (B+ tree) is used for fast lookup
Conceptual Schema (Logical level):

- Describes the overall logical structure of the database (entities, relationships, constraints)
- Uses a conceptual or implementation data model.
- Example:
o Entity: STUDENT(ID, Name, GPA)
o Relationship: ENROLLS (STUDENT_ID  STUDENT, COURSE_ID  COURSE)
o Constraint: GPA between 1 and 4.

External Schemas (View level):

- Describe different user views of the database. Each user/application sees only what they need.
- Usually uses the same model as conceptual schema.
- Example:
o Student’s view: Name, GPA only
o Professor’s view: Student Name, GPA, Courses
o Admin’s view: Full details including Address, Birthdate

Mapping

- The process of translating input from the external schema and retrieving results from the
internal schema.
- Needed to transfer requests and data across schema levels.
- Programs use the external schema  the DBMS maps it to the conceptual schema  then to
the internal schema for execution.
- Data retrieved from the internal schema is reformatted to match the external view (e.g., SQL
query results displayed on a web page).

- Example:
Data Independence

Ability to modify schema at one level without affecting higher levels.

 Logical Data Independence: Change conceptual schema (e.g., add a new field) without changing
external schemas or applications.
o Example: We have a STUDENT table, and view the name and grade

Then when we add a new column email into STUDENT

 still view name and grade because we don’t use the column email
 Physical Data Independence: Change internal schema (e.g., reorganize files, add indexes)
without changing conceptual schema.
o Example: The STUDENT table is located in student_file.dat. Now we move this file to SSD
 the tables still remain, and the programs can access to the database using SQL
statement
 Main idea: only mappings between levels need updates; higher-level schemas are unchanged 
applications remain unchanged too because they refer to the external schemas

DBMS Languages Overview:

Data Definition Language (DDL) = defines the structure.

- Used to define database schemas.

- Who uses?  DBA (Database Administrator) and database designer.
- Specifies conceptual schema; may also define internal and external schemas.
- Main functions: CREATE TABLE, DTOP TABLE, ALTER TABLE, adding constraints, data types,
relationships.
- In some DBMSs, there used to have a separate language for each type of schema:
o SDL (Storage Definition Language) → internal schema  specialize for DBA and designer
o DDL (Data Definition Language) → conceptual schema
o VDL (View Definition Language) → external schema

 Now many DBMSs combine all this three into DDL (e.g., SQL), no longer separate language

DML (Data Manipulation Language) = manipulates the data.

- Used to retrieve and modify data in the database (e.g., SELECT, INSERT, UPDATE, DELETE).
- Can be:
o Embedded in programming languages (e.g., C, Java).
o Standalone query language (e.g., SQL): Commands are run directly on SQL terminal or
query tool, not embedded in the program
o Accessed through API libraries (e.g., JDBC, ODBC)  help programmers connect and
send DML commands to DBMS
 - Types of DML
o High-level (Non-procedural / Declarative language)
 Specify what data to get, not how.
 Work with sets of records. (set-oriented)
 Example: SQL, relational algebra.

o Low-level (Procedural)
 Specify how to get data, one record at a time.
 Use loops/ pointers for multiple records.
 Example:

DBMS Interfaces

 users or programmers interact with the database management system (DBMS). 3 main types:

 Stand-alone query interface → independent interface, enter SQL commands directly

 Programmer interface → embed DML inside code
 User-friendly interface → for non-technical users (menus, forms, GUI)

DBMS Programming Language Interfaces

 how programmers manipulate databases. 3 main types:

 Embedded approach → SQL inside programming language (Java, C, …)

 Procedure call approach → connect via APIs.

 Database programming language → language built around SQL

User-Friendly DBMS Interfaces

 Menu-based → choose options from list

 Forms-based → fill out form fields to insert/query data
 Graphics-based → point & click / drag & drop
 Natural language → input requests in English
 Menus + forms + graphics = webs

Other DBMS Interfaces

 Speech I/O → use voice input/output

 Web browser interface → access DB via website
 Parametric interface → fixed functions for specific roles
 DBA interface → manage accounts, schemas, authorizations
Database System Utilities

 Data loading → import external files into DB

 Backup → save DB copies periodically
 Reorganize → optimize file structures
 Report generation → create formatted reports
 Performance monitoring → check speed & usage
 Other utilities → sorting, data compression, user tracking

Other Tools

 Data Dictionary / Repository → store metadata & schema info

o Active → accessed by DBMS (internal)
o Passive → accessed by DBA only.
 Application Development Environments & CASE tools → help build DB apps (PowerBuilder,
JBuilder, JDeveloper.)

Centralized and Client-Server DBMS Architectures

Centralized DBMS
  All components (DBMS software, hardware, applications, user interface) and processing are
done at the central site.
 Other computers act as terminals (display screen, keyboard) connecting via LAN or specific line;
no connection → no access.
 If the server is down → can't do anything.
 Pros: easy to manage and secure.
 Cons: heavy load on one system.

Basic Client–Server Architecture

 This is a more distributed model. The system is divided into:

o Clients: user machines (PCs, laptops, mobile devices).
o Servers: machines that specialize in handling certain functions.
 Examples of servers: Print server, File server, DBMS server, Web server, Email server …
 Each server has a specialized task, and Clients send specific requests to appropriate servers.

Clients

 Have client software to communicate with servers.

 May be diskless or have minimal installations, only DBMS
 Connected via LAN or wireless network.

DBMS Server

 Handles database queries and transactions.

 Also called SQL server, query server, or transaction server.
 Clients access data via APIs:
o ODBC (Open Database Connectivity): for general languages
o JDBC (Java Database Connectivity): for Java
 Both client and server need proper modules installed.
2-tier Architecture (Client–Server) = App Management (GUI + DB)

 Typical structure: a desktop application installed on a computer, connecting to a DB server in a

LAN.
 Called 2-tier because it has two layers:
o Tier 1 – Client: User Interface (GUI) + Application logic.
o Tier 2 – Server: DBMS + Data storage.
 Communication: Client sends SQL queries via TCP/IP → Server executes → Returns results.
 1 server ↔ many clients, 1 client ↔ 1 server

 A client can connect to multiple DBMSs or data sources (e.g., SQL Server, Oracle, or even files) if
it has proper drivers and connection info
o Example: In your computer can install:
 SQL Developer → access Oracle DB
 SQL Server Management Studio (SSMS) → access SQL Server
 MySQL Workbench → access MySQL
o All three are on the same machine (client), and each time you open any tool you are
connecting to the corresponding DBMS.
o  connection configuration
 In some systems (like Object DBMS), clients can handle extra functions (e.g., data dictionary,
query optimization, recovery), making them “smarter” instead of sending SQL command and
waiting for result.
o  improve performance or flexibility

3-tier Architecture = Web-based (Browser + App Server + DB)

Structure:
 Client tier: browser (HTML, JS).
 Adds an Application Server (middle tier) between client and DB server.
o Stores business logic and web connectivity.
o Processes data from DB and sends results to clients.
o Improves security — clients cannot access DB server directly
 Database tier: DBMS server (MySQL, PostgreSQL...).
 Flow: Browser → Web Server → Database → Web Server → Browser

DBMS Classification

 By data model:
o Traditional: Relational, Network, Hierarchical.
o Emerging: Object-Oriented, Object-Relational.
 By users: Single-user (e.g., MS Access) vs Multi-user (e.g., Oracle, MySQL).
 By architecture: Centralized (one server) vs Distributed (multiple servers).

Distributed DBMS Types

  Homogeneous: same DBMS software at all sites.
 Heterogeneous: different DBMSs at sites.
 Federated/Multi-database: independent DBs accessed through a common interface.
 Modern DDBMSs often work as client-server systems because they are not fully distributed
anymore — just many servers serving many clients over the network.

Cost Considerations

 Range: free (MySQL, PostgreSQL) → millions (Oracle, IBM DB2).

 Commercial DBMSs may offer extra modules (time-series, spatial, XML).
o Modules called cartridges or blades (Oracle).
 Licensing: site (organization), seat (maximum number of concurrent users), or single-user.

CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL

Overview of Database Design Process
Two key activities:
1️. Database Design → Create the conceptual schema (entities, attributes, relationships).
2️. Applications Design = part of software engineering → Focus on programs and user interfaces that
access the database.
Example: Designing a student management system:
→ Database design: define tables like Students, Courses, Enrollments.
→ Application design: build forms or webpages for adding/viewing student info.

ER Model: Entities & Attributes

 Entity type: represents a real-world object or concept represented in the database.

o EMPLOYEE(Name, SSN, Address, Sex, BirthDate)
 Attribute: Property that describes an entity (Name, SSN, Address, Sex, BirthDate)
 Entity (Instance): A specific entity has values for each attribute.
o Name = ‘John Smith’, SSN = ‘123456789’, Address = ‘731 Fondren, Houston, TX’, etc.
 Value Set / Data Type: Each attribute has a domain of valid values (int, string, date, enum, …)
 Types of Attributes
Type Definition Example
Simple Attribute Single atomic value per entity. SSN, Sex.
Composite Can be divided into smaller subparts. Address (Street, City, Zip)
Attribute Name (First, Last).
Multi-valued Entity can have multiple values for the same {Color} for CAR
Attribute attribute. {Previous_Degrees} for STUDENT.
Normally, we separate it into another table
(because 1-M).
Composite + Attribute has multiple sets of sub-attributes. {PreviousDegrees (College, Year, Degree, Field)}.
Multi-valued <-> Each sub-attribute has many values  John: {(MIT, 2019, BSc, Physics),
(Stanford, 2022, MSc, CS)}

Entity types and Key attributes

- Entity Type: A collection of entities with the same attributes. (EMPLOYEE, PROJECT)
- Key Attribute: An attribute whose value uniquely identifies each entity. (SSN of EMPLOYEE)
- Composite Key: A key made up of multiple attributes (VehicleTagNumber = (Number, State)).
Note: If one key is missing  not enough to distinguish
- Multiple Keys: An entity type may have more than one key.
VehicleIdentificationNumber (popularly called VIN)
VehicleTagNumber (Number, State), aka license plate number.
 Note: Both of them are key attributes, but normally choose one to be primary key
 Each key is underlined in the ER diagram.

Displaying an Entity type

 Entity type: Rectangle

 Attribute: Oval
 Composite attribute: Ovals connected to a
parent oval
 Key attribute: Underlined
 Multivalued attribute: Double oval
 Lines: Connect attributes to their entity type

Entity set

 Entity Type: defines a group of entities with the same attributes.

 Entity Instance: represents one specific object (one row in the table).
 Entity Set:
o A collection of all entity instances of a given entity type that exist in the database.
o Represents the current state of data for that entity type.
 The same name (e.g., CAR) is often used to refer to both the entity type and its entity set
 As data changes (insert, delete, update), the entity set also changes  reflect the current
contents of the database.
Refining the initial design by introducing relationships

o The initial design is incomplete  some requirements are shown as relationships.

o The ER model has three main concepts: Entities, Attributes, and Relationships.
o A relationship links two or more entities with a specific meaning. (instance)
o EMPLOYEE works on PROJECT
o EMPLOYEE manages DEPARTMENT.
o Relationship types group similar relationships (e.g., WORKS_ON, MANAGES).
o Meaning: a general pattern describing all connections (class) <> relationship (connection)
o The degree of a relationship = number of entity types involved.
o Binary relationships involve two entities.

Relationship type vs relationship set

 Relationship Type = Schema description - defines name, participating entity types, & constraints
 Relationship Set = The number of instances of relationship types stored in the database.
o Each instance connects one entity from each participating type (John Smith - ProductX)
 In diagram, a diamond shape represents the relationship type, linked to entities by straight lines.

 6 relationship types = relationship set

Recursive Relationship Type

 involve the same participating entity type in different roles twice

Why called recursive?

 Because it goes back to the same entity type: EMPLOYEE → SUPERVISION → EMPLOYEE

Example: the SUPERVISION relationship, EMPLOYEE participates twice in two distinct roles:

 supervisor (or boss) role (1)

 supervisee (or subordinate) role (N)

 Note: because one entity type appeared twice, we need to assign label to distinguish

Each relationship instance relates two distinct EMPLOYEE entities:

 One employee in supervisor role

 One employee in supervisee role

 EMPLOYEE can supervise other EMPLOYEE

 Cardinality: One supervisor → many supervisees; each supervisee → one supervisor.

Weak Entity Types

 A weak entity has no key attribute of its own.

 It must participate in an identifying relationship with an owner (identifying entity).
 Identified by:
o A partial key of the weak entity, and
o The owner entity it is related to.
 Example:
o DEPENDENT is weak; identified by (Name, EMPLOYEE).
o EMPLOYEE is the identifying entity via DEPENDENT_OF relationship

Constraints on Relationships

 define participation limits.

Cardinality Ratio (max participation):

Type Meaning Example

1:1 One to one 1A can only link to 1B and vice versa
1: N/ N:1 One to many/ Many to one 1A can link to many Bs, but 1B can only link to 1A
M:N Many to many Many As can link to many Bs and vice versa

Existence Dependency / Participation Constraint (min participation):

 0 → optional participation (not existence-dependent)

 1 or more → mandatory participation (existence-dependent)
Attribute of Relationship types

 A relationship type can have its own attributes.

o Example: HoursPerWeek in WORKS_ON shows how many hours an employee works on
a project, not In EMPLOYEE or PROJECT individually
 Such attributes depend on the combination of entities (e.g., employee + project).
 Usually appear in M:N relationships.
o In 1:N relationships, attributes can often be moved to the entity on the N-side.

(min, max) notation

 defines how many times an entity can participate in a relationship.

 min = minimum participation, max = maximum participation.
 Default: (0, n). Must satisfy min ≤ max.

One employee can manage at most one

department or none, whereas one
department must have exactly one manager

One employee must work for exactly

1 department, whereas one
department can have one or many
employees
Relationships of Higher Degree

 Binary relationship = degree 2

 Ternary relationship = degree 3
 n-ary relationship = degree n
 Note: An n-ary relationship is not equivalent to multiple binary ones  represent
different meanings.
 SUPPLY(Supplier, Part, Project) <> supplier – Part + Supplier – Project + Part - Project
 Constraints for higher-degree relationships are harder to specify.
 Sometimes, both binary and n-ary relationships can exist in the same schema if they convey
different meanings.
 A ternary relationship can sometimes be replaced by a weak entity with multiple identifying
relationships.

 If a binary relationship can

always be derived from a
higher-degree one, it is
redundant and can be
omitted.
o Suppose we have
relationship
OFFERS(Course,
Section, Semester)
o TAUGHT_DURING(
Course,
Semester) will be
deduced then
  TAUGHT_DURING is redundant because info already in OFFERS
 But in this case, we cannot remove it, as we want to know who actually teach
this course

Extended Entity-Relationship (EER) Model

Original ER model lacks specialization/generalization features

EER extends ER with:

 Type–subtype & set–subset relationships.

 Specialization/generalization hierarchies.
 New diagram notations to represent them.
 very complicated, and we don’t want to have this shjt in the exam :”)))

CHAPTER 5: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS

Informal Definitions

 A Relation = a table with rows and columns.

 Each row (tuple) = a real-world entity or relationship.
 Each column (attribute) has a name describing its meaning.

 The key is an attribute (or set of attributes) that uniquely identifies each row.
o SSN is a common example of a key in a STUDENT table.
 Artificial (surrogate) keys are system-generated identifiers for rows. (e.g., ID = 1, 2, 3, …)

Formal Definitions

 Relation Schema: Structure of a relation. (Description)

Written as R(A1, A2, ..., An) where R is the relation’s name and As are attributes.
Example: CUSTOMER(Cust-id, Cust-name, Address, Phone#)
o CUSTOMER is the relationship name.
o Attributes: Cust-id, Cust-name, Address, Phone#.
 # = number = no.
 Each attribute has a domain (valid value range), for example:
 Cust-id: 6-digit number
 Phone#: valid phone number
 Tuple: An ordered set of values representing a row (enclosed in angled brackets ‘< … >’)
o Example: <632895, "John Smith", "101 Main St", "(404) 894-2000">.
o This is called a 4-tuple as it has 4 values
 Domain: Set of valid values for an attribute, often with a defined format
o Example:
 USA_phone_numbers: 10-digit US phone numbers, format (ddd)ddd-dddd.
 Date: can be in yyyy-mm-dd or dd/mm/yyyy format.
o The same domain can be used for multiple attributes.
  the Date domain can be used for both Invoice-date and Payment-date,
although they have different meanings.
 Relation State: Current set of tuples in a relation; a subset of the Cartesian product of the
attribute domains.
o Each attribute has its own domain, and the values in the table are valid combinations of
those domains.
o For example:
 Cust-name has domain as varchar(25) string, representing “customer name”

Formal Definitions – Summary & Example

Relation Schema:

 Written as R(A1, A2, ..., An)

 R: name of the relation
 A1, A2, ..., An: attributes of the relation

Domain:

 dom(Ai) is the set of valid values for attribute Ai.

Relation State:

 r(R) is a subset of dom(A1) × dom(A2) × ... × dom(An)

 Each element of r(R) is a tuple (row), for example ti = <v1, v2, ..., vn>

Example:

 R(A1, A2)
o dom(A1) = {0,1}, dom(A2) = {a, b, c}
o All possible combinations (Cartesian Product): {<0,a>, <0,b>, <0,c>, <1,a>, <1,b>, <1,c>}
o A specific relation state: r(R) = {<0, a>, <0, b>, <1, c>}
Characteristics of Relations

 Tuples Ordering: Tuples in a relation are unordered – the row order doesn’t matter.
 Attributes Ordering: Attributes in a schema R(A1, A2, ..., An) and values in a tuple <v1, v2, ...,
vn> are considered ordered, since each value must match its attribute position.
 Alternative definition: Some models treat attribute order as irrelevant (identified by attribute
names instead).

 Atomic values: Each value in a tuple is indivisible (cannot contain multiple values).
 Domain rule: Each value vi in tuple <v1, v2, ..., vn> must belong to its attribute’s domain
dom(Ai).
o If age has a positive int domain, t[Age] cannot be “Twenty”
 Null value: Used when a value is unknown or inapplicable.
 Notation:
o t[Ai] or [Link] → value of attribute Ai in tuple t.
 Example: t[Name] = "John".
o t[Au, Av, ..., Aw] → sub-tuple with values of those attributes.
 Example: t[Name, Age] = <"John", 25>.

Relational Integrity Constraints

 Constraints = conditions that must hold all data valid and consistent.
 3 main types:
o Key constraint → No duplicate rows.
o Entity integrity → Primary key cannot be null.
o Referential integrity → Foreign key must match a primary key in another table.
 Domain constraint → Every value must match its attribute’s data type or be null (if allowed).

Key Constraints

Superkey: A set of attributes that uniquely identifies each tuple in a relation.

Key: A minimal superkey — removing any attribute causes loss of uniqueness.

Candidate key: Any attribute set that can serve as a key; there may be several keys in one relation.
Primary key:

 One candidate key chosen from several candidate keys to uniquely identify tuples (usually the
smallest or simplest one).
 The primary key attributes are underlined
 Reference the tuple from another tuple  foreign key
 The other candidate keys that are not chosen as a primary will be called alternate key

Rule:

 Any key is a superkey, but not every superkey is a key.

 Any set of attributes that includes a key is a superkey
 A minimal superkey is also a key

Example: consider CAR(State, Reg#, SerialNo, Make, Model, Year)

 CAR has two (candidate) keys: {State, Reg#}, {SerialNo}  Both are superkey of CAR
 {SerialNo, Make} is a superkey but not a key, removing attribute Make still unique
 Primary key: choose {SerialNo} because it is simple
Relational Database Schema
 A collection of relation schemas that belong to the same database.
- Notation: S = {R1, R2, ..., Rn}
o where S = name of the entire database schema,
o each Ri = name of an individual relation schema.
- Example: COMPANY database schema with six relations:
o EMPLOYEE, DEPARTMENT, PROJECT, WORKS_ON, DEPENDENT, DEPT_LOCATIONS.
 S = {EMPLOYEE, DEPARTMENT, PROJECT, WORKS_ON, DEPENDENT, DEPT_LOCATIONS}
 S is the COMPANY database schema

Entity Integrity

 Rule: The primary key attributes (PK) of every relation must not be NULL.
o Reason: Primary keys uniquely identify tuples; if NULL, identity is lost.
 If composite key: None of its attributes can be NULL.
 Other attributes may allow NULLs even if they’re not part of the PK.
 Example: In STUDENT(StudentID, Name, Age)  StudentID cannot be NULL.
Referential Integrity

 Definition: A constraint involving two relations:

o ensure consistency between referencing relation (R1) and referenced relation (R2).
o Tuples in R1 have attributes FK (foreign key) referencing PK (primary key) in R2.
 Referential integrity rule: FK in R1 must be either
o an existing PK value in R2, or
o NULL (if FK is not part of PK in R1).
 Representation: Shown as a directed arc from [Link] → [Link].
 Other Types of Constraints

Semantic Integrity Constraints

 Based on real-world business rules (application semantics), like we using condition “if”
 Cannot be expressed directly by the relational model.
 Example: “An employee cannot work more than 56 hours per week across all projects.”
 Expressed using:
o Triggers → automatic checks or actions
 If violate  report the error or cancel action
o Assertions → global conditions that must always hold
 If violates  all the databases will get error and cannot be updated

Populated Database State

 The current content of the database.

 Each table (relation) has many tuples (rows).
 The database state = the union of all relation states.
 Changes when operations occur:
o INSERT – add new tuple
o DELETE – remove existing tuple
o MODIFY (UPDATE) – change attribute values
 Each operation creates a new state of the database.

Update Operations on Relations

- Update operations: INSERT, DELETE, MODIFY (UPDATE).

 Integrity constraints must be preserved (primary/ foreign key, check, NOT NULL, UNIQUE)
- Sometimes multiple updates must happen together.
 Example: when delete an employee, the related salary or project lines should be deleted.
- Updates can trigger other updates automatically (propagation)  Ensure Integrity constrains
 Example: When deleting a customer, his orders may also need to be deleted (CASCADE).
- If integrity violation occurs, there are several possible actions:

Handling Method Description Example

RESTRICT / REJECT Cancel the operation that causes the Do not allow deleting a customer if they still have
violation existing orders.
INFORM USER Perform the operation but warn the user Allow the update, but display a logic warning to
about the violation the user.
CASCADE Automatically update or delete related When deleting a customer → automatically
records to correct the violation delete all their orders.
SET NULL Assign NULL to the affected foreign key When deleting a customer → the CustomerID
values field in the Orders table becomes NULL.
USER-DEFINED Execute a user-defined routine to handle When deleting a customer → automatically
ACTION the violation reassign their orders to a default “Unknown
Customer” account.
 Possible violations for each operation

Operation Violations Explanation / Example

INSERT - Domain constraint - Wrong data type (e.g., text instead of number)
- Key constraint - Duplicate primary key
- Referential integrity - Foreign key references a non-existing primary key -
- Entity integrity Primary key value is NULL
DELETE - Referential integrity If a deleted tuple is referenced by another tuple.
Options: RESTRICT, CASCADE, SET NULL
UPDATE - Domain constraint Updating data might break data type rules or
- NOT NULL constraint relationships between tables. Updating PK behaves like
- Referential integrity (if FK updated) DELETE + INSERT.
- Key constraint (if PK updated)

------------------------------------------------------------------------------------------------------------------------------------------

CHAPTER 7: DATABASE DESIGN BY ER- AND EER TO RELATIONAL MAPPING

Step 1: Mapping of Regular Entity Types

- Regular (Strong) entity type E  create a relation R including all simple attributes of E
- Choose one of the key attributes of E  primary key for R
 If the chosen are composite  all its attribute form the primary key

primary

Strong E

Weak E
 Step 2: Mapping of Weak Entity Types

A weak entity has no key of its own; it exists only when its owner (strong) entity exists.

Steps:

1. Create a relation R for Weak Entity

2. Include in R:
o all simple attributes of Weak entity (and partial key, if any)
o Primary key of the Strong entity (owner) as FK in R
3. Define the primary key of R:
o If there is a partial key: PK(R) = PK(owner) + partial key of weak entity.
o If there is no partial key: PK(R) = PK(owner)
o If weak entity depends on multiple owner: PK(R) = all owners’ primary keys + partial key
4. Note:
 The owner’s primary key becomes a foreign key in R.
 Rename it if needed to avoid duplication

primary

EMPLOYEE (Owner) Partial key

Rename Ssn  Essn

Step 3: Mapping of Binary 1:1 Relation Types

1. Foreign key approach: Choose one of tables between S and T (better to choose total participation)

- Assume S is the total participation, then add the PK of T in S and become FK

- Include all the simple attributes of the 1:1 relationship type R as attributes of S
2. Merge relation approach:

3. Cross-reference or relationship relation approach:

 We use this approach when both entities are optional to participate in the relationship
Step 4: Mapping of Binary 1:N Relationship Types
Step 5: Mapping of Binary M:N Relationship Types.

Step 6: Mapping of Multivalued attributes

Step 7: Mapping of N-ary Relationship Types. (n > 2)

 Step 1: Create a new table S to represent the relationship R.

o The table name is usually the name of the relationship
 Step 2: Add foreign keys to this table.
o The foreign key is the primary key of each participating entity.
 Step 3: Add the relationship's own attributes (if any)
 Step 4: Define the primary key of the new table.
o Normally, it is a combination of all foreign keys.
Result
Notations

------------------------------------------------------------------------------------------------------------------------------------------

CHAPTER 8: SCHEMA DEFINITION, CONSTRAINTS, QUERIES AND VIEWS

Data Definition Language (DDL)

 defines the structure of the database. It specifies:

 Schema of each table

 Domain of attributes
 Integrity constraints
 Indexes to maintain
 Physical storage structure on disk

Common Types used in Oracle

CHAR(n) → Fixed-length character string of n. If the string is shorter, adds spaces to make it long enough

VARCHAR2(n) → Variable-length text of n (preferred for strings). If the string is shorter, it does nothing.

NUMBER(p, s) → Numeric with precision p (the total of number), and scale s (the number after comma)

o NUMBER(7,2) allows numbers from -99999.99 to 99999.99.

INTEGER → Alias for NUMBER(38,0)

DATE → Stores date & time with 4 digits of year, month, day, hour, minute, second. Default format is
specified by parameter

o TO_DATE('2025-10-15 08:30:00', 'YYYY-MM-DD HH24:MI:SS')

TIMESTAMP → Date & time with fractional seconds

o SYSTIMESTAMP can return the value like 15-OCT-25 08.31.42.123456 AM.

BLOB → Variable length binary data of up to 128 terabytes, use for storing file .jpg, .mp4, .pdf

CREATE TABLE

 Create a new relation with name + attributes + data types

 Also use a constraint NOT NULL on an attribute

Including Constraints

- Constraints enforce rules at the table level

- Constraints might prevent deletion, update and insert operations, if there are constraint violations.

 This helps keeping the data consistent.

Constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (to give condition)

Constraint Guidelines

 name a constraint or let the database generate a name.

Define a constraint as a Column-level or Table-level constraint.

  Column-level constraint: named after column

 Table-level constraint: named after the column list, often used when binding multiple columns.

Why have to name the constraint?  To update easier. If not, we have to call the name by the system

Without name:

 System creates a name like SYS_C001234

When we want to remove:

 Hard to remember to work

With name:

 Easier
DROP TABLE

 remove completely a relation: its data and definition

 When drop, the relation can no longer be used since its description no longer exists

ALTER TABLE

 add attribute or change the structure of the existing relation

Note: When add new attribute, all the tuples of the relation in that column will have NULLs  cannot
have constraint NOT NULL right away

Example:

Then enter a value for the new attribute JOB for each EMPLOYEE tuple

RENAME

 Only the owner who can change the name of the table, view, sequence or synonym

Naming Convention

Names must:

 Start with a letter.

 Be up to 30 characters long.
 Use only letters, numbers, _, $, #.
 Be unique per user.
 Not use reserved words.

CREATE SCHEMA

 Allows to group together tables and other constructs that belong to the same database
application.
 A SQL schema is identified by a schema name, and includes a authorization identifier to identify
the user or account who owns the schema

 Note: In Oracle, this not actually creates a schema. Instead, it creates when you create a user
REFERENTIAL INTEGRITY OPTIONS

We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT for foreign keys

When supervisor is deleted from

emp, the employees that they
supervise will have superssn = NULL

Important Note:

There are two different ways to set primary key, one for simple attribute, one for composite attributes.

 This way, only 1 column (dnumber) will be set unique

 This way, 2 columns (dnumber, branch_id) are unique

Retrieval Queries in SQL

 Important:

(1): if you forget WHERE:

 retrieve all tuples in the relation


incorrect &
very large
relations

 very dangerous

(2): SELECT * = SELECT all tuples

(3): DISTINCT  no duplicates

Note:

SELECT in SQL <> in Relational Algebra:

 In Relational Algebra  filter rows (select rows that meet the conditions).
 In SQL  select the column to display.

SQL is a multi-set (bag), not a set

 In set theory  elements cannot be duplicates.

 In SQL  a table can contain rows (tuples) that are exactly the same, and the order not matter

 SQL treats a table as a “multi-set” (or “bag”) — meaning it can contain duplicate elements.

Example: {A, B, C, A}  this is a bag because A appears twice

{A, B, C}  this is a bag as well as a set (no duplicates)

If we have a relation:

Then we choose:

We will have the result


 May be duplicated (as SQL allows).

 Bag also same as list, allowing duplicates, but the order is matter

Example:

- In bag: {A, B, A} and {B, A, A} is the same (order not matter)

- In list: [A, B, A] <> [B, A, A] (the order is matter)

What if we don’t allow duplicated value?  use DISTINCT

or PRIMARY KEY or UNIQUE

Simple SQL Queries

Basic SQL Queries based on the operations of relational algebra:

- SELECT (σ): filters rows ~ WHERE in SQL

- PROJECT (π): selects columns ~ SELECT in SQL
- JOIN (⨝): Combine two tables based on common conditions ~ JOIN … ON in SQL

 These three together form the foundation of all SQL queries.

Example of a simple query on one relation

Query 0: Retrieve the birthdate and address of the employee whose name is 'John B. Smith’.

 PROJECT (π)

 SELECT (σ)

 The result can be duplicated

Query 1: Retrieve the name and address of all employees who work for the 'Research' department.

 PROJECT (π)

 JOIN (⨝)

Condition for JOIN (⨝)

 SELECT (σ)
Query 2: For every project located in 'Stafford', list the project number, the controlling department
number, and the department manager's last name, address, and birthdate.

Two join conditions:

 project  department
 department  employee

Aliases

 In SQL, attributes can share the same name if they belong to different tables.
 When this happens  prefix the attribute with its table name to specify which one you mean.
 Example: [Link], [Link]

Query 8: For each employee, retrieve the employee's name, and the name of his or her immediate
supervisor.

 E and S are called

aliases

 Convenience

SUBSTRING COMPARISON
R(A, B, C, D, E, F, G, H, I, J)
CK = {A, B}  đây là candidate key AB (composite đàng hoàng)
KA (key attribute): A, B
NKA (non-key attribute): C,D,E,F,G,H,I,J
A -> D,E  not 2NF

R(O#, Odate, Cust#, Total_amount, I#, Qty_ordered, Total_price, Discount%)

CK1 = {O#, I#}
Key attribute: O#, I#
Non key: Odate, Cust, Total_amount, Qty_ordered, Total_price, Discount%

FD

O# → Odate, Cust#, Total_amount

(O#, I#) → Qty_ordered, Total_price, Discount%

Check 2NF  no because Odate, Cust#, Total_amount only depend on O#, not I#  partial dependency

Decompose R

R(O#, Odate, Cust#, Total_amount, I#, Qty_ordered, Total_price, Discount%)

/ \

ORDER(O#, Odate, Cust#, Total_amount) ORDER-ITEM (O#, I#, Qty_ordered, Total_price, Discount%)

Check 3NF  yes

If 2NF is not true -> 3NF is not tree