DBMS One Liner
# DBMS contains information about a particular enterprise
# Collection of interrelated data
# Set of programs to access the data
# An environment that is both convenient and efficient to use
# Database Applications:
>> Banking: all transactions
>> Airlines: reservations, schedules
>> Universities: registration, grades
>> Sales: customers, products, purchases
>> Online retailers: order tracking, customized recommendations
>> Manufacturing: production, inventory, orders, supply chain
>> Human resources: employee records, salaries, tax deductions
>> Databases touch all aspects of our lives
# In the early days, database applications were built directly on top of file systems
Drawbacks of using file systems to store data:
>> Data redundancy and inconsistency, Multiple file formats, duplication of information in different files
>> Difficulty in accessing dataNeed to write a new program to carry out each new task
>> Data isolation — multiple files and formats
>> Integrity problemsIntegrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly
>> Hard to add new constraints or change existing ones
>> Atomicity of updates: Failures may leave database in an inconsistent state with partial updates carried out
Example: Transfer of funds from one account to another should either complete or not happen at all Concurrent access by multiple usersConcurrent accessed needed for performance
>> Uncontrolled concurrent accesses can lead to inconsistencies–Example: Two people reading a balance and updating it at the same time
>> Security problems: Hard to provide user access to some, but not all, data
# Database systems offer solutions to all the above problems.
# Levels of Abstraction
>> Physical level: describes how a record (e.g., customer) is stored.
>> Logical level: describes data stored in database, and the relationships among the data.
# View level: A way to hide:
(a) details of data types and
(b) information (such as an employee’s salary) for security purposes.
# View of Data: An architecture for a database system
# Instances and Schemas: Similar to types and variables in programming languages
# Schema– the logical structure of the database
>> Example: The database consists of information about a set of customers and accounts and the relationship between them)
>> Physical schema: database design atthe physical level
>> Logical schema: database design atthe logical level
# Instance– the actual content of the database at a particular point in time, Analogous to the value of a variable.
# Physical Data Independence– the ability to modify the physical schema without changing the logical schema.
>> Applications depend on the logical schema
>> In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.
# Data Models: A collection of tools for describing
>> Data
>> Data relationships
>> Data semantics
>> Data constraints
# Relational model
>> Entity- Relationship data model (mainly for database design)
>> Object- based data models (Object-oriented and Object-relational)
>> Semistructured data model (XML)
# Other older models:
>> Network model
>> Hierarchical model
# Data Manipulation Language (DML)
Language for accessing and manipulating the data organized by the appropriate data model
>> DML also known as query language
# Two classes of languages
>> Procedural – user specifies what data is required and how to get those data
>> Declarative (nonprocedural) – user specifies what data is required without specifying how to get those data
>> SQL is the most widely used query language
# Data Definition Language (DDL)
>> DDL compiler generates a set of tables stored in a data dictionary
# Data dictionary contains metadata (i.e., data about data)
>> Database schema
>> Integrity constraints