PCIT 02: Introduction to DBMS

• • Definitions Database: • A very large, integrated collection of data. • Models real-world enterprise Entities (e.g., students, courses) • Relationships (e.g., Madonna is taking CS564) Database Management System (DBMS)
  • a software package designed to store and manage databases Examples of Database Applications: Banking all transactions Airlines: reservations, schedules Universities: registration, grades
• • Concurrent access by multiple users • Concurrent accessed needed for performance • Uncontrolled concurrent accesses can lead to inconsistencies E.g. two people reading a balance and updating it at the same time • Security problems • 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: application programs hide details of data types. Views can also hide information (such as an employee's salary) for security purposes
• Instances and Schemas • 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) • Analogous to type information of a variable in a program • Physical schema: database design at the physical level • Logical schema: database design at the 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

  
  

  • Procedura l-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) • Specification notation for defining the database schema Example: create table account ( account_number char(10), branch_name char(10), balance integer) • DDL compiler generates a set of tables stored in a data dictionary
• Data dictionary contains metadata (i.e., data about data) • Database schema • Data storage and definition language • Specifies the storage structure and access methods used • Integrity constraints Domain constraints • Referential integrity (e.g. branch_name must correspond to a valid branch in the branch table) • Authorization
• Database Design

  The process of designing the general structure of the database

  
  

  Logical Design - Deciding on the database schema. Database design requires that we find a "good" collection of relation schemas.

  
  

  -Business decision - What attributes should we record in the database?

  
  

  -Computer Science -decision What relation schemas should we have and how should the attributes be distributed among the various relation schemas?

  
  

  • Physical Design -Deciding on the physical layout of the database
• Other Data Models • Object-oriented data model • Object-relational data model
• Database Management System Internals • Storage management • Query processing • Transaction processing
• Concurrency Control • Concurrent execution of user programs is essential for good DBMS performance. • Because disk accesses are frequent, and relatively slow, it is important to keep the cpu humming by working on several user programs concurrently. • Interleaving actions of different user programs can lead to inconsistency, e.g., check is cleared while account balance is being computed DBMS ensures such problems don't arise: users can pretend they are using a single-user system.
• Summary • DBMS used to maintain, query large datasets. • Benefits include recovery from system crashes, concurrent access, quick application development, data integrity and security. • Levels of abstraction give data independence • A DBMS typically has a layered architecture, • DBAs hold responsible jobs and are well-paid! • DBMS R&D is one of the broadest, most exciting areas in CS. • Advanced databases course at the graduate level
• Levels of Abstraction

  • Many views, single conceptual (logical) schema and physical schema

  • Views describe how users see the data.

  Conceptual schema defines logical structure. Sometime we separate between conceptual level and logical level

  • Physical schema describes the files and indexes used.

  *Schemas are defined using DDL (Data Definition Language)

  *data is modified/queried using DML (Data Manipulation Language)

  
• The Entity-Relationship Model

  • Models an enterprise as a collection of entities and relationships

  • Entity: a "thing" or "object" in the enterprise that is distinguishable from other objects

  -Described by a set of attributes

  • Relationship: an association among several entities represented diagrammatically by an entity-relationship diagram: