Database Systems Week I

Database

A collection of related data organized to model aspects of the real world, designed for a specific purpose.

Database Management System

Software that enables users to create, maintain, and query databases efficiently while ensuring data integrity, security, and concurrent access.

key characteristics of a database

structured organization, persistent storage, shared access, query capability, data integrity

File processing issues

data redundancy, data inconsistence, access difficulty, integrity problems, atomicity issues, concurrent access anomalies, security problems

DMBS solutions

centralized storage, consistency control, query language, constraint enforcement, transaction support, concurrency control, access control

key dbms disadvantages

high cost, complexity, system overhead, performance, single point of failure

when not to use dbms

simple data, small data volume, real time systems, embedded systems, no multi user access

three levels of abstraction

external level (user views), conceptual level (schema), internal level (physical devices)

data independance

logical independence (change conceptual schema), physical independence (change storage structure), protection layer (applications remain stable when db changes)

data model

collection of concepts for describing data, relationships, semantics, and constraints

primary key

unique identifier for each row

foreign key

references primary key in another table

composite key

multiple columns combined to uniquely identify a rows

super key

set of attributes that can uniquely identify touples

touple

row, a single record representing one instance

attribute

column, a property characteristic of the entity

schema vs instance

structured definition vs actual data stored

domain

set of allowed values for a column

DDL

data defenition language (defines DB structure)

programming types (data)

transient data, single process, basic constraints, procedural access, in memory only

DML

data manipulation language (syntax)

DCL

data control language (access control)

TCL

transaction control language (manage transactions)

DDL interpreter

processes schema definitions and stores metadata in the data dictionary

DML compiler

Translates DML statements into low level instructions for query evaulations

query optimizer

selects the most efficient execution strategy

execution engine

executes the optimized low level instructions and returns query results

DBMS System Architecture

Authorization and integrity, transaction manager, file manager, buffer manager

database admins

schema management, security & access control, backup & recovery, performance monitoring, system maintenance

database users

application programmers, sophisticated users, naive users, database designers

technical users

database designers, application programmerse

end users

sophisticated users, naive/end users

enterprise database setup process

requirement analysis → conceptual design → logical design → physical design → implementation → testing and deployment

database design principles

minimize redundancy, enforce integrity, plan for growth, optimize performance, consider users, document everything

ACID

Atomicity (completes or does not), Consistency (database stays valid), Isolation (concurrent transactions don’t interfere with each other), Durability (committed changes persist)

no sql

non relational database designed to store and manage flexible, often unstructured or semi structured data without relying on traditional tables and fixes schemas