Database, a data collection stored as an organized set of files on a computer
Database Transaction, a sequence of operations to read (query) data from or write (insert, modify) data into the database
Database Management System (DBMS) – a software system that supports creation and population of the databases as well as database transactions
Data Redundancy & Inconsistency
Multiple file formats, duplicate info in different files
Difficulty in accessing data
Need to write a new program to carry out each new task
Data isolation — the files are not connected with each other
Integrity problems
Integrity constraints (e.g., GPA >= 0) become “buried” in program code rather than being stated explicitly
Hard to add new constraints or change existing ones
What is a Relational Database?
Relational Databaseis a database that stores data as a set of Tables (Relations) with:
Set of Columns
One column is called a Primary Key
All values in a Column have the same data type
Set of Rows
Rows have entries that correspond to Columns
Each Row is Unique based on the Primary Key
Primary Key must have a unique value (not NULL or empty)
Row Entries not in the Primary Key do not have to be unique
Non-Primary Keys entries for a Row can be NULL or empty
Rows of data in their raw form are stored in Tuples
Relational Database Management Systems (RDBMS), a set of programs for managing and facilitating all the operations on a database while maintaining the integrity and security of the data, e.g. Oracle RDBMS, IBM DB2, PostgreSQL
Data Types for Relational Databases
char(n) Fixed length character string, with user-specified length n
varchar(n) Variable length character strings, with user-specified maximum length n.
int. Integer (a finite subset of the integers that is machine-dependent).
smallint. Small integer (a machine-dependent subset of the integer domain type).
numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point.
real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.
float(n). Floating point number, with user-specified precision of at least n digits.
Most systems also have other types such as Date, Time, URL, etc.
Relations :: Logical View
Relation (Table) is a set of Tuples
Recall a set cannot have duplicates and the order of elements is not significant
Tuple (Row) is a sequence of elements
Tuples can have duplicates
Order of elements in a tuple is significant
<> = denotes the structure of a Tuple
Examples:
•A Tuple:
<1, “Jane”, 25, “female”>
•A set of Tuples:
{ <1, “Jane”, 25, “female”>,
<2, “John”, 18, “male”> }
•Note that the values that occur in the corresponding positions in the two tuples are of the same type
RDBMS Characteristics
Schemas & Relational Model
Concurrency
ACID Transactions
“Join” operations
Table Schema(s)
Structure of the DB tables
Relational Database Model
Mapping of the DB table connections
Concurrency
Multiple concurrent transactions from multiple users at the same time without any unintended and undesirable effects
Atomic Data Structures
Only single data types, e.g. Integers, Characters, Boolean, String
No lists, arrays, dictionaries, JSON objects or XML documents
ACID Transactions
Atomicity: Every transaction is either entered into the DB in its entirety or not at all
Consistency: Only those transactions are executed which will not make a consistent DB inconsistent
Isolation: Each transaction is executed as if it is the only transaction being executed at that time
Durability: Once a transaction is committed, it remains in the DB even in the face of system failures
Relational Database Model
How are these tables connected?
Relational Database Model
From a High Level, shows how the Database Tables are connected to one another to form a Database.
Database Table Schemas
Note: Database Table Schemas rarely change. The Data in the Database Tables can change frequently.
The Structure is often referred to as the ‘Schema’ of a table in textbooks.
1) A Table is its Schema plus + data stored in it (data with Metadata)
Let’s Drill down into the Relational DB Model
Database Schema Example
Friends(name of the schema, not part of the schema) – Table Name
Name: Varchar (String)
Age: Integer
Gender: Varchar (String)
Also represented as:
Friends(Name: Varchar (String), Age: Integer, Gender: Varchar (String))
Or as:
Friends(Name, Age Gender)