• Collection of related data
• Represents some aspect of the real world
• Built for a specific purpose

• also called universe of discourse (UoD)
• the part of the real world we need to represent in our database

Database management system (DBMS) 

• Software you need to do stuff with your data
• Collection of programs 
• Enables users to create and maintain a database

Defining a database 

Specify the data types, structures, and constraints of the data to be stored

Meta-data

• Database definition or descriptive information 
• Stored by the DBMS in the form of a database catalog or dictionary

• Query and update the database miniworld 
• Generate reports

Sharing a database 

Allow multiple users and programs to access the database simultaneously

Application program 

Accesses database by sending queries to DBMS

Query 

Causes some data to be retrieved

Transaction 

• When a user views a database
• May cause some data to be read and some data to be written into the database

• System - protect where the data is stored
• Security - user authorization

Maintain the database system 

Allow the system to evolve as requirements change over time

• Requirements specification and analysis
• Conceptual design
• Logical design 
• Physical design

Conceptual design

• Independent of the database system
• Graphical and user friendly
• How do the pieces of data fit together

Logical design 

Convert the conceptual design to a format that can be directly implemented in the database system

Physical design

• How do I want to organize this on the disc
• What will be in separate files (some tables may be split into separate files)

• Each user must define and implement the files needed for a specific software application 
• Only used by one user at a time
• No meta-data
• Limited security/authorization

Program-data independence

Structure of data files is stored in DBMS catalog separately from access programs

Program-operation independence

• Interface includes operation name and data types of its arguments 
• Implementation can be changed without affecting the interface

Data abstraction

Allows program-data independence and program-operation independence

Conceptual representation of data

Data model 

Type of data abstraction used to provide conceptual representation

• Subset of the database 
• Contains virtual data derived (i.e. computed) from the database files but is not explicitly stored

Multiuser DBMS 

• Users have a variety of distinct applications
• Must provide facilities for defining multiple views

Concurrency control software 

Online transaction processing (OLTP) application

• Very fast
• Involves a small number of records

• Authorizing access to the database
• Coordinating and monitoring its use
• Acquiring software and hardware resources

• Identifying the data to be stored 
• Choosing appropriate structures to represent and store this data

System analysts

Determine requirements of end users

Application programmers 

Implement the specifications as programs

Denormalization

Sometimes necessary to use controlled redundancy to improve the performance of queries

Data normalization 

Restricting unauthorized access

• Security and authorization subsystem
• Privileged software

Referential integrity constraint 

Entry must be related to at least one other entry

Key or uniqueness constraint

Business rules

Data abstraction 

Data model

• Collection of concepts that describe the structure of a database
• Provides means to achieve data abstraction
• Does not contain actual data
• Includes constraints

Basic operations 

Specify retrievals and updates on the database

Dynamic aspect or behavior of a database application

Allows the database designer to specify a set of valid operations allowed on database objects

High-level or conceptual data models 

• Close to the way many users perceive data
• Abstract – good to explain to low-tech people
• Independent of DBMS
• Important for designing a database

Low-level or physical data models 

Representational data models

• Easily understood by end users 
• Also similar to how data organized in computer storage
• Defined by DBMS
• Has tables and columns
• Important for writing queries

Represents a real-world object or concept 

Attribute

• Represents some property of interest 
• Further describes an entity

Entity-Relationship model

High level, Graphically describes data

Relational data model

Used most frequently in traditional commercial DBMSs

Object data model 

New family of higher-level implementation data models 

Physical data models 

Describe how data is stored as files in the computer 

Access path 

Structure that makes the search for particular database records efficient

Index 

• Example of an access path 
• Allows direct access to data using an index term or a keyword

Database schema

Description of a database

Schema diagram

Displays selected aspects of schema

Schema construct

Each object in the schema

Data in database at a particular moment in time

Internal level 

Describes physical storage structure of the database

Conceptual level

Describes structure of the whole database for a community of users

Describes part of the database that a particular user group is interested in

Centralized DBMSs Architecture

All DBMS functionality, application program execution, and user interface processing carried out on one machine

• Servers with specific functionalities
• Client machines 
• • Appropriate interfaces to utilize these servers
  • Local processing power to run local application

Server handles

Query and transaction functionality related to SQL processing 

Client handles

User interface programs and application programs

Open Database Connectivity (ODBC) 

• Provides application programming interface (API)
• Allows client-side programs to call the DBMS
• • Both client and server machines must have the necessary software installed

JDBC

Allows Java client programs to access one or more DBMSs through a standard interface

• Data model
• Relational
• Object
• Hierarchical and network (legacy)
• Native XML DBMS
• Number of users
• Single-user
• Multiuser – most databases are multiuser
• Number of sites
• • Centralized
  • Distributed
  • Homogeneous
  • Heterogeneous
• Cost
• • Open source
  • Different types of licensing

Requirements collection and analysis

• Database designers interview prospective database users to understand and document data requirements
• Result: data requirements
• • Functional requirements of the application

Value sets (or domain of values)

Specifies set of values that may be assigned to that attribute for each individual entity

Degree of a relationship type 

Number of participating entity types EX: Binary, ternary

Role name signifies role that a participating entity plays in each relationship instance

Recursive relationships

• Same entity type participates more than once in a relationship type in different roles
• Must specify role name

Identifying relationship

Relates a weak entity type to its owner 

Do not have key attributes of their own

• Choose names that convey meanings attached to different constructs in schema
• Nouns give rise to entity type names
• Verbs indicate names of relationship types
• Choose binary relationship names to make ER diagram readable from left to right and from top to bottom

• Ternary relationship must be represented as a weak entity type
• • No partial key and three identifying relationships
• Or introduce a surrogate Key