Introduction to Databases and DBMS

Databases

• Data is a collection of facts and figures used to create information.
• Database (DB): An organized collection of structured information (data) stored electronically in a computer system.
• Database Management System (DBMS): Organizes and manages data, making retrieval, manipulation, and information production easier.
• DBMS Characteristics:
  • Real-world entity: Modern DBMSs design architecture around real-world entities and their attributes. (e.g., a school database might use students as an entity and age as an attribute.)
  • Relation-based tables: DBMSs use tables to represent entities and their relationships. Database structure is easily understood by table names.
  • Isolation of data and application: DBMS is distinct from the data. Data is passive; DBMS is active. DBMS also handles metadata (data about the data).
  • Less redundancy: DBMS uses normalization to reduce redundancy in data by splitting relations (tables) when attributes have redundant values. Normalization is a complex mathematical process.
  • Consistency: Every relation in a database must remain consistent. DBMS methods and techniques ensure consistency.
  • Query Language: DBMSs include query languages for efficient retrieval and manipulation of data. Users can use many filtering options to get specific sets of data. (This is an improvement over traditional file processing.)
  • ACID Properties: Atomicity, Consistency, Isolation, and Durability - critical for transaction management. ACID properties ensure that the database remains consistent even during multi-transactional environments and failures.
  • Multiuser and Concurrent Access: DBMS allows multiple users to access and manipulate data simultaneously. While there are restrictions (on concurrent access to the same data item) users are usually unaware of them.
  • Multiple views: DBMS presents data differently to different users (e.g., sales vs. production). The user views are tailored to their specific needs.
  • Security: Features like multiple views and constraints help control who can access specific data
• DBMS Users
  • Administrators: Manage the DBMS
  • Designers: Design the logical structure
  • End Users: Use the data
• Entity-Relationship (ER) Model:
  • Represents real-world entities and relationships.
  • ER model defines
    • Entity sets: Tables
    • Relationship sets: Associations between entities
    • Attributes: Characteristics defining an entity
    • Constraints: Rules to ensure data integrity.
• Types of Relationships (Mapping Cardinalities):
  • One-to-one: Only one instance of an entity is associated with the relationship.
  • One-to-many: One instance of an entity can be associated with multiple instances of another entity.
  • Many-to-one: Multiple instances of one entity can be associated with a single instance of another entity.
  • Many-to-many: Multiple instances of one entity can be associated with multiple instances of another entity.
• Participation Constraints
  • Total participation: All entities are involved in the relationship.
  • Partial participation: Not all entities are involved in the relationship.
• Database Schema
  • Physical Schema: Defines how data is physically stored (e.g., files, indices).
  • Logical Schema: Defines logical structure, constraints, relationships, views and integrity rules.
• MS Access vs. MS SQL Server
  • Business Size: MS Access suitable for smaller operations; MS SQL Server for larger enterprises.
  • Interface: MS Access has a user-friendly interface; MS SQL Server is more technical, requiring training.
  • Queries Complexity: MS SQL Server supports more complex queries and objects.
  • Concurrent Sessions: MSSQL Server can handle more concurrent users and transactions.
  • Integrations: MS SQL Server offers more robust integration options.
  • Security: MS SQL Server is more secure.
  • Price: MS Access is less expensive than MS SQL Server.
• Structured Query Language (SQL)
  • SQL is a programming language used for all relational databases to query, manipulate and define data and provide access control.
  • Developed at IBM in 1970’s with Oracle as a major contributor.
  • Standard(ANSI) SQL is used, along with extensions (like Transact-SQL(T-SQL).

Most popular database management systems

(Note: data in a chart format. A summary of the ranking is provided here instead.)

• Oracle, MySQL, Microsoft SQL Server, PostgreSQL, MongoDB, Redis, IBM Db2, Elasticsearch, SQLite, Microsoft Access, and Amazon DynamoDB are ranked based on popularity. Oracle and MySQL are top-ranked; others follow.