DBMS-REVIEWER.docx

**DBMS REVIEWER** **DATABASE MANAGEMENT SYSTEM (DBMS)** - a collection of programs that enables user to create and maintain a database. - DBMS is a general-purpose software system that facilitates the process of defining, consisting, manipulating and sharing database among various...

**DBMS REVIEWER** **DATABASE MANAGEMENT SYSTEM (DBMS)** - a collection of programs that enables user to create and maintain a database. - DBMS is a general-purpose software system that facilitates the process of defining, consisting, manipulating and sharing database among various users and applications. **HOW DBMS WORKS** - AN application program accesses the DB by sending queries to the DBMS. - Query causes some data to be retrieved over which a transaction will be performed. A transaction may cause some data to be read and some data to be written into the DB. **DATABASE** - stores a lot of critical information to access data quickly and securely. **DBMS ARCHITECTURE** - help users to get their request done while connecting to the database. We choose database architecture depending on several factors like the size of the database, number of users, and relationships between the users. **TWO TYPES OF DATABASE MODEL** 1. Logical Model 2. Physical Model **TYPES OF DBMS ARCHITECTURE** 1. **1-Tier Architecture** - In 1-Tier Architecture the database is directly available to the user, the user can directly sit on the DBMS and use it that is, the client, server and database are all present on the same machine. 2. **2-Tier Architecture** - The 2-Tier Architecture is similar to a basic client-server model. The application at the client and directly communicates with the database on the server side. - APIs like ODBC and JDBC are used for this interaction. - The server side is responsible for providing query processing and transaction management functionalities. On the client side, the user interfaces and application programs are run. The application on the client side establishes a connection with the server side to communicate with the DBMS. - An advantage of this is the maintenance and understanding are easier, and compatible with existing system. However, this model gives poor performance when there are a large number of users. 3. **3-Tier Architecture** - There is another layer between the client and the server. - The client does not directly communicate with the server. Instead it interacts with an application server which further communicates with the database system and then the query processing and transaction management takes place. - This intermediate layer acts as a medium for exchange of partially processed data between the server and the client. - This type of architecture used in the case of large web applications. **DATA MODELS** 1. **Hierarchical Data Model** - Hierarchical Data Model is the oldest type of the data model. - It was developed by IBM in 1968. - It organizes data in the tree-like structure. - Hierarchical model consists of the ff: - It contains nodes which are connected by branches - The topmost node is called the root node. - If there are multiple nodes appear at the top level, then these can be called as root segments. - Each node has exactly one parent - One parent may have many children. 2. **Network Data Model** - It is the advance version of the hierarchical data model. - This model organize data, it uses directed graphs instead of the tree structure. - In this model the child can have more than one parent. It uses the concept of the two data structures i.e Records and Sets.  3. **Relational Data Model** - The relational data model was developed by E.F Codd in 1970. - There are no physical links as they are in the hierarchical data model. - Following are the properties of the relational data model: - Data is presented in the form of the table only - It deals only with the data not with the physical structure. - Provides information regarding metadata. - At the intersection of row and columns there will be only one value for the tuple it provides a way to handle the queries at ease. **SCHEMAS AND INSTANCES** **INSTANCES** - Is the state of an operational database with data at any given time. It contains a snapshot of the database. The instances can be changed by certain CRUD operations, such as like addition, and deletion of data. - It may be noted that any search query will not make any kind of changes in the instances. **SCHEMA** - Schema is the overall description of the database. - The basic structure of how the data will be stored in the database is called schema. **3 Types of Schema** 1.**Logical Schema** -- It describes the database designed at a logical level. 2.**Physical Schema** -- It describes the database designed at the physical level. 3.**View Schema** -- It defines the design of the database at the view level. **Primary Key** - Definition: A primary key is a unique identifier for a record in a table. Each table in a database should have one primary key. - Characteristics: - **Uniqueness**: Each value in the primary key column must be unique. - **Non-null**: Primary key columns cannot contain NULL values. - **Stability:** The values in the primary key columns should rarely, if ever, change. **Foreign Key** - Definition: A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables. - Characteristics: - **Referential Integrity:** Foreign keys help maintain the referential integrity between tables by ensuring that the value in the foreign key column(s) matches a value in the referenced primary key column(s). - Can contain NULL values, unless explicitly restricted. **Candidate Key** - **Definition:** A candidate key is a column, or set of columns, that can uniquely identify any database record without referring to any other data. - **Characteristics:** - Uniqueness: Each value must be unique. - Non-null: Candidate key columns cannot contain NULL values. **DATABASE SCHEMA** - Database Schema Figure shows the suggested system\'s database architecture. It explains how the data are arranged and the links that link them. All of the required data restrictions are listed. Database designers created the schema to facilitate programmers\' understanding and usage of the database. 2. **CATEGORIES OF DATABASE SCHEMA** a. **Physical Database Schema:** This schema addresses the actual methods of storing data, including files, indices, and other structures. It describes the steps involved in storing data in secondary storage. b. **Logical Database Schema:** The logical restrictions that must be applied to the data being stored are outlined in the Logical Database Schema. Definitions are given to tables, views, and integrity constraints. **Relationships:** - The ballots table is linked to the candidates table through the IDNUMBER column, indicating which candidate is associated with each ballot. - The ballots table is also linked to the voters table through the IDNUMBER column, indicating which voter cast the ballot. - The voted table is linked to the voters table through the IDNUMBER column, indicating which voters have voted. - The candidates and candidatesarchive tables are likely related, with the candidatesarchive table storing historical data about candidates.