w2ch3

Database Management Systems (DBMS)

• A DBMS is a mathematically rich and scientific process that reduces data redundancy.
• Consistency is a state where every relation in a database remains consistent, and DBMS provides greater consistency than earlier forms of data storing applications.

Characteristics of DBMS

• Query Language: DBMS is equipped with a query language that makes it efficient to retrieve and manipulate data.
• ACID Properties: DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (ACID) to ensure the database stays healthy in multi-transactional environments.
• Multiuser and Concurrent Access: DBMS supports a multi-user environment and allows them to access and manipulate data in parallel.
• Multiple Views: DBMS offers multiple views for different users, enabling them to have a concentrated view of the database according to their requirements.

2-Tier Architecture

• The 2-Tier architecture consists of a client-side and a server-side.
• The client-side application interacts with the server-side, which provides functionalities like query processing and transaction management.
• The client-side establishes a connection with the server-side to communicate with the DBMS.

3-Tier Architecture

• The 3-Tier architecture contains an additional layer between the client and server.
• The client-side application interacts with an application server, which communicates with the database system.
• The end-user has no idea about the existence of the database beyond the application server.
• The 3-Tier architecture is used in large web applications.

DBMS Data Models

• Data models define how the logical structure of a database is modeled.
• Data models define how data is connected to each other and how they are processed and stored inside the system.

Entity-Relationship Model

• The Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them.
• The ER Model creates entity sets, relationship sets, general attributes, and constraints.
• The ER Model is best used for the conceptual design of a database.

Database Schema

• A database schema defines the entities and relationships among them.
• A database schema contains a descriptive detail of the database, which can be depicted by means of schema diagrams.
• The database schema can be divided into two categories: Physical Database Schema and Logical Database Schema.

Database Instance

• A database instance is a state of an operational database with data at any given time.
• A database instance contains a snapshot of the database and tends to change with time.
• A DBMS ensures that every instance (state) is in a valid state, following all the validations, constraints, and conditions imposed by the database designers.

Data Independence

• A database system normally contains a lot of data in addition to users' data.
• The Relational Data Base Management System (RDBMS) is based on the relational model introduced by E.F.Codd.
• Codd's 12 rules define the requirements for a true relational database.

Codd's 12 Rules

• Rule 1: Information Rule - All data is stored in table format.
• Rule 2: Guaranteed Access Rule - Every data element is accessible logically with a combination of table-name, primary-key, and attribute-name.
• Rule 3: Systematic Treatment of NULL Values - NULL values are given a systematic and uniform treatment.
• Rule 4: Active Online Catalog - The structure description of the entire database is stored in an online catalog.
• Rule 5: Comprehensive Data Sub-Language Rule - A database can only be accessed using a language having linear syntax that supports data definition, data manipulation, and transaction management operations.
• Rule 12: Non-Subversion Rule - The interface must not be able to subvert the system and bypass security and integrity constraints.

Relational Database Management System (RDBMS)

• RDBMS is most commonly used database.
• It contains a number of tables, and each table has its own primary key.
• Data is represented in terms of tuples (rows) in RDBMS.
• A table is a collection of related data entries and contains rows and columns to store data.

Database Table

• A table is the simplest example of data storage in RDBMS.
• A table consists of fields, rows, and columns to store data.

Field

• A field is a smaller entity of the table that contains specific information about every record in the table.

Row/Record

• A row of a table is also called a record.
• It contains specific information of each individual entry in the table.

Column

• A column is a vertical entity in the table that contains all information associated with a specific field in a table.

NULL Values

• The NULL value of the table specifies that the field has been left blank during record creation.
• It is different from the value filled with zero or a field that contains space.

Database Management System (DBMS)

• DBMS is a mathematically rich and scientific process that reduces data redundancy
• Consistency: a state where every relation in a database remains consistent
• DBMS provides greater consistency compared to earlier forms of data storing applications like file-processing systems
• Query Language: DBMS is equipped with a query language, making it efficient to retrieve and manipulate data
• ACID Properties: DBMS follows Atomicity, Consistency, Isolation, and Durability concepts, ensuring database health in multi-transactional environments and in case of failure

Multiuser and Concurrent Access

• DBMS supports a multi-user environment, allowing users to access and manipulate data in parallel
• Multiple views: DBMS offers different views for different users, enabling them to have a concentrated view of the database according to their requirements

Entity-Relationship (ER) Model

• ER Model is based on real-world entities and relationships among them
• Entities: real-world entities having properties called attributes, with attributes defined by their set of values called domain
• Relationships: logical associations among entities, mapped with entities in various ways, including one-to-one, one-to-many, many-to-one, and many-to-many

Relational Model

• The most popular data model in DBMS, based on first-order predicate logic
• Data is stored in tables called relations
• Relations can be normalized, with atomic values saved
• Each row in a relation contains a unique value
• Each column in a relation contains values from the same domain

Database Schema

• A database schema is the skeleton structure that represents the logical view of the entire database
• Data Integrity: categories include entity integrity, domain integrity, referential integrity, and user-defined integrity

DBMS vs RDBMS

• DBMS applications store data as files, while RDBMS applications store data in a tabular form
• In DBMS, data is generally stored in a hierarchical or navigational form, whereas in RDBMS, tables have an identifier called a primary key
• Normalization is present in RDBMS, but not in DBMS
• RDBMS defines integrity constraints, whereas DBMS does not apply security with regards to data manipulation

Relational Database Management System (RDBMS)

• Data is represented in terms of tuples (rows) in RDBMS
• Relational database is most commonly used and contains a collection of organized tables
• Each table has its own primary key, enabling easy data access

Database Table

• A table is a collection of related data entries and contains rows and columns to store data
• Field: a smaller entity of the table, containing specific information about every record
• Row/Record: a horizontal entity in the table, containing specific information of each individual entry
• Column: a vertical entity in the table, containing all information associated with a specific field
• NULL Values: specify that the field has been left blank during record creation

2-Tier and 3-Tier Architecture

• 2-Tier Architecture: client-side application establishes a connection with the server-side DBMS
• 3-Tier Architecture: contains an application server between the client and server, enabling the client to interact with the application server, which communicates with the database system

Database Management System (DBMS)

• DBMS is a mathematically rich and scientific process that reduces data redundancy.

Characteristics of DBMS

• Consistency: ensures every relation in a database remains consistent, detecting attempts to leave the database in an inconsistent state.
• Query Language: allows efficient data retrieval and manipulation through the use of filtering options.
• ACID Properties:
• Atomicity: ensures transactions are treated as a single, indivisible unit.
• Consistency: ensures the database remains in a consistent state after a transaction.
• Isolation: ensures transactions do not interfere with each other.
• Durability: ensures transactions are permanently stored in the database.
• Multiuser and Concurrent Access: supports multiple users accessing and manipulating data simultaneously.
• Multiple Views: offers different views of the database for different users, allowing them to focus on specific data according to their requirements.
• Security: imposes constraints on data entry and retrieval, offering multiple levels of security features.

DBMS Users

• Administrators: maintain the DBMS, create access profiles for users, and apply limitations to maintain isolation and force security.
• Designers: design the database, determining what data should be stored and in what format.

2-Tier and 3-Tier Architecture

• 2-Tier Architecture:
• Client-side application interacts directly with the server-side DBMS.
• Server-side provides query processing and transaction management functionalities.
• 3-Tier Architecture:
• Client-side application interacts with an application server, which communicates with the DBMS.
• End users have no knowledge of the database beyond the application server.
• Database has no knowledge of users beyond the application server.

DBMS Data Models

• Data models define the logical structure of a database.
• Entity-Relationship (ER) Model:
• Based on real-world entities and relationships among them.
• Creates entity sets, relationship sets, general attributes, and constraints.
• Best used for conceptual database design.
• ER Model is based on entities, their attributes, and relationships among entities.

Data Independence

• Logical Data Independence: ensures changes to table formats do not affect data residing on the disk.
• Physical Data Independence: allows changing the physical data storage system without impacting the schema or logical data.

RDBMS

• RDBMS (Relational Database Management Systems) is based on the relational model.
• Examples of RDBMS include SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access.
• Data Integrity in RDBMS:
• Entity integrity: no duplicate rows in a table.
• Domain integrity: enforces valid entries for a given column.
• Referential integrity: rows cannot be deleted that are used by other records.
• User-defined integrity: enforces specific business rules defined by users.

DBMS vs RDBMS

• DBMS stores data in files, while RDBMS stores data in a tabular form.
• DBMS does not use normalization, while RDBMS uses normalization.
• DBMS does not apply security constraints, while RDBMS defines integrity constraints for the purpose of ACID properties.

DBMS Characteristics

• Consistency: a state where every relation in a database remains consistent, with methods to detect and prevent inconsistent states
• Query Language: DBMS provides a query language for efficient data retrieval and manipulation, with filtering options to retrieve specific data sets
• ACID Properties:
  • Atomicity: ensures that database transactions are treated as a single, indivisible unit
  • Consistency: ensures that the database remains in a consistent state, even in the event of a failure
  • Isolation: ensures that concurrent transactions do not interfere with each other
  • Durability: ensures that once a transaction is committed, its effects are permanent
• Multiuser and Concurrent Access: DBMS supports multiple users accessing and manipulating data in parallel, with restrictions on transactions when users attempt to handle the same data item
• Multiple Views: DBMS offers multiple views for different users, allowing them to have a concentrated view of the database according to their requirements
• End Users: individuals who reap the benefits of having a DBMS, ranging from simple viewers to sophisticated users such as business analysts

DBMS Architecture

• Client/Server Architecture: a basic architecture used to deal with a large number of PCs, web servers, and database servers connected via networks
• 1-Tier Architecture:
  • Directly accessible by the user
  • Changes are directly made on the database
  • Not suitable for end-users, but useful for development of local applications
• 2-Tier Architecture:
  • Basic client-server architecture
  • Applications on the client end communicate directly with the database at the server side using APIs like ODBC and JDBC

Data Models

• ER Model:
  • Entities: real-world objects with properties (attributes) and domains
  • Relationships: logical associations among entities, with mapping cardinalities (one-to-one, one-to-many, many-to-one, many-to-many)
• Relational Model:
  • Data is stored in tables (relations)
  • Relations can be normalized
  • Values saved are atomic values
  • Each row in a relation contains a unique value
  • Each column in a relation contains values from a same domain

Database Schema

• Definition: the skeletal structure that represents the logical view of the entire database
• Components:
  • Entities and relationships among them
  • Constraints that are to be applied on the data
• Types:
  • Physical Database Schema: pertains to actual storage of data and its form of storage
  • Logical Database Schema: defines logical constraints that need to be applied on the data stored

Database Instance

• Definition: a state of an operational database with data at any given time
• Characteristics:
  • Contains a snapshot of the database
  • Changes with time
  • DBMS ensures that every instance is in a valid state, following all validations, constraints, and conditions imposed by the database designers

Data Independence

• Rule 6: View Updating Rule - all views of a database that can theoretically be updated must also be updatable by the system
• Rule 7: High-Level Insert, Update, and Delete Rule - database must support high-level insertion, updation, and deletion
• Rule 8: Physical Data Independence - data stored in a database must be independent of the applications that access the database
• Rule 9: Logical Data Independence - logical data in a database must be independent of its user's view (application)
• Rule 10: Integrity Independence - database must be independent of the application that uses it, with integrity constraints that can be independently modified
• Rule 11: Distribution Independence - end-user must not be able to see that the data is distributed over various locations