01 DBMS

DBMS

Database

• organized collection of structured information, or data, typically stored electronically in a computer system.

Types of database

• Flat File
  • Stores data in a single table
  • Generally in plain text form
  • Each line holds one record
  • Fields in records separated by delimiters such as tabs or commas
• Hierarchical
  • Stores data in parent-children relationship nodes
  • One node can have one parent
  • Records contain information about their groups of parent/child relationships
• Network
  • Uses network structure to create relationship between entities
  • Differs from hierarchical database
    • Network node can have relationship with multiple entities
• Relational
  • Organized as a set of tables with columns and rows
  • Most efficient and flexible way to access structured information
• Object-oriented
  • Subscribes to a model with information represented by objects
• NoSQL
  • nonrelational database
  • Allows unstructured and semistructured data to be stored and manipulated

DBMS

• Interface between database and end users or programs
• Allows users to retrieve, update, and manage how information is organized and optimized
• Facilitates additional administrative operations
  • E.g. performance monitoring and backup

Database System

• Database is an implementation according to rules of a theoretical model
  • External level
    • User’s view of database
    • Part of the database relevant to a particular user
    • Excludes irrelevant data and data the user is not authorized to access
  • Conceptual level
    • What data is stored within the whole database
    • How the data is inter-related
    • Does not specify how data is physically stored
    • Controlled by database administrators
  • Internal level
    • How the database is physically represented on the computer system
    • How data is stored in database and on computer hardware
    • Controlled by dbms software

Objectives of the 3 levels is to separate the user’s view

• Allows independent customized user views
  • Each user should be able to access same data but different customized view
  • Views should be independent and changes should not affect others
• Hides physical storage details from users
• Database administrators should be able to change database storage structures without affecting the users’ views
• Internal structure of the database should be unaffected by changes to the physical aspects of the storage

Database vs File-Based

• Self-describing nature of a database system
  • Database system contains database and complete definition or description of of database structure and constraints
    • Definition is stored in dbms catalog
    • Information stored in catalog is called meta-data which describes the structure of database
  • In file processing, data definition is part of the application programs
    • Application programs are constrained to work with one specific database whose structure is declared in application programs
• Insulation between programs and data, and data abstraction
  • Database has program-data independence
    • Data definitions separate from programs; change storage without rewriting apps
  • In file processing file structures embedded in code; changes require program updates
• Support of multiple views of the data
  • Database
    • Different users see tailored subsets (views)
  • File processing
    • Single, fixed view per program
• Sharing of data and multiuser transaction processing
  • Database ensures concurrent transactions operate correctly and efficiently.
    • Updating same data is done in controlled manner
    • Multiple users can access at the same time
    • A transaction is a logical unit that is independently executed for data retrieval or updates (must be ACID)
      • Atomicity
        • all changes to data are performed as if they are a single operation
      • Consistency
        • Data is in a consistent state when a transaction starts and when it ends
      • Isolation
        • The intermediate state of a transaction is invisible to other transactions. As a result, transactions that are run concurrently appear to be serialized.
      • Durability
        • After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.

Additional Advantages of DBMS

• Controlling redundancy in data storage and in development and
• maintenance efforts.
• Restricting unauthorized access to data.
• Providing persistent storage for program objects and data
• structures
• Efficient query processing
• Providing backup and recovery systems
• Providing multiple interfaces to different classes of users.
• Representing complex relationships among data.
• Enforcing integrity constraints on the database.
• Permitting Inferencing and actions using rules. Typical actions are
• performed by triggers and stored procedures.
• Potential for enforcing standards.
• Reduced application development time.
• Flexibility to change data structures.
• Availability of up-to-date information.
• Economies of scale and sharing of data among multiple users.

When not to use DBMS

Costs/Inhibitors

• High initial investment (hardware, licensing, personnel)
• Overhead for generality, security, recovery, integrity, concurrency

Scenarios DBMS May Be Unnecessary

• Simple, static data & applications
• Stringent real-time requirements (DBMS latency too high)
• Single-user data access only

Scenarios DBMS May Be Inadequate

• Data too complex for supported models
• Specialized operations unsupported by the chosen DBMS

Relational Database

• collection of data organized into a table structure
• Tables can be modified, or rows and columns can be added or removed without affecting the rest of the database.
• RDBMS is the software that gives users the ability to update, query and administer a relational database.
• relational model is the conceptual basis of relational databases

• Tables are made up of records and fields
  • Record: a complete set of data about a single item
  • Field: one piece of data about a single item

Properties of relation

• Relation name is distinct from all other relation names in the relational schema
• Each attribute has a distinct name
• Each cell of relation contains exactly one atomic (single) value
• Values of an attribute are from the same domain
• Each tuple is distinct, there are no duplicate tuples
• Order of the attributes has no significance
• Order of tuples has no significance

Relational Keys

• Candidate key
  • minimal set of attributes that can uniquely identify each tuple in a relation.
• Primary key
  • candidate key used to identify tuples uniquely in a relation. Should not change overtime.
• Secondary/Alternate key
  • candidate key not selected to be primary key.
• **Composite/Compound key **
  • combination of two or more attributes in a relation that can be used to uniquely identify each tuple in the relation.
• Foreign key
  • attribute which provides a logical link between tables.

Criteria for choosing primary key

• Minimum set of attributes
• Less likely to have its values changed
• Less likely to lose uniqueness in the future
• With fewest characters
• Easier to use from the user’s point of view