CSCI 305 – F13, Lecture 2
[Slides originally assembled by Prof. King]

The DBMS

DBMS
• Database Management System
– DBMS
– Specialized software design for the sole purpose of providing services to store, manage, and query data in an efficient manner

Task #1 – Create new DB
• A DBMS allows a DB administrator to specify the DB schema
– Schema is a DB blueprint
– Uses some type of data definition language (DDL)
– Is usually a part of the query language (e.g., SQL)
– Example:

Task #2 – Allow querying data
• Query languages are used to request specific data from databases
– Sometimes called a Data-Manipulation Language
(DML)

• Examples:
– SQL – very common; especially for relational databases – XQuery – used for XML repositories

Task #3 – Large amounts of data
• A modern DBMS must handle an enormous amount of data efficiently
• It must be scalable
• It must be reliable and offer persistence
– (i.e. keep the data around for a LONG time)

Task #4 - Durability
• A DBMS must be resilient in the face of:
– Hardware failure
– Periods of heavy use
• Must still be responsive
• Think – Amazon in December!

– Attempts to breach security

Task #5 - concurrency
• Provide concurrent access with isolation and atomicity: – Ensure isolation among concurrent users
– Ensure individual DB actions to be atomic

Evolution of the database

The data model
• Every database system has an underlying model that determines
– Structure placed on the data
– Operations on the data
– Constraints on the data

• Let's explore the history of these models, specifically focusing on the structure for now…

Flat File
• aka - table model
• Entire database consists of a single table
– spreadsheet

• Often stored in a standard text file, CSV or tabdelimited
• Example...

• Simple user database for a system admin

Flat file
• Discuss advantages / disadvantages
– HINT – consider an order management system.
How would you have a single table containing everything you need with orders?

• Still occasionally used for simple designs
– Even here it is usually discouraged. Why?
– Always need to consider scalability!

Hierarchical Database Model
• 1960s – 1970s
• Data is organized into a tree-like structure
• Restriction – must maintain tree structure
– Parents have many children
– Children have only one parent

• Introduced by IBM with IMS – Information
Management System
– Also used by the Windows Registry!

• Image from oracle.com

Relational Model
•
•

Used by the majority of modern database management systems (DBMS)
Database = set of relations (or tables)
– Each relation in the database has a distinct name
– Example: Student, Campus

•

Each relation has a set of attributes
– Each attribute has a distinct name, used to describe the entities in the relation
– Example: Students have FirstName, LastName, ID, SS#, etc.

•

Tuple has a value for each attribute, representing a single entity
– Example: Each student in the class is a tuple
– An specific entity, Brian

•

Each attribute has a type (or domain)
– ID:char(9), name:char(25), GPA:float

•

One attribute is used to represent something unique about the record
– Called a key
– Other tables reference this key instead of containing duplication information
• This is what makes it relational

• Widespread use today!
• Yet, it has its challenges
– Type system
– Implementation of a table
• Are you going to keep the entire facebook database of users in memory at all times? (Probably not!)
• Rarely implemented as a 2D table…

Object-oriented model
• You'll also see object-relational model
• A relational model with the restriction that the underlying database system has the same type system as the application program
– What benefits does this offer the application developer? – JDBC

Example

Better example
Look familiar?
UML Class diagram! Semi-structured