Database Management Systems Quiz

Database Management Systems

• Database management systems (DBMS) are used to create and maintain databases.
• Data is raw, unprocessed facts. Examples include age, name, and location.
• Information is processed data. An example is "Suresh is 25 years old".
• A database is a collection of related data. Examples include online banking or library management systems.
• Meta-data describes the database definition.

Database Management System Functionalities

• Defining data: specifying the data type, structure, and constraints for data storage.
• Constructing data: the process of storing data on a storage medium.
• Manipulating data: querying the database to retrieve specific data, updating the database, and generating reports.
• Sharing data: allowing multiple users and programs to access the database concurrently.

Database Properties

• A database represents aspects of the real world in a simplified way.
• A logical collection of data with intrinsic meaning.
• A purpose-built structure for storing specific information.

Database System Environment

• A database system consists of the database system, DBMS software, and users/programmers, application programs/queries, software to process queries/programs, and software to access stored data.
• Stored DB definition contains metadata (the database definition).
• Stored database (database itself)

Example of a University Database

• A database that stores student and course information. Examples include name, roll number, major, class.

Data Models

• Underlying structure of a database.
• A conceptual tool, including data, relationships, semantics, and constraints.
• Design at physical, logical, and view levels.

Categories of Data Models

• Relational model: a collection of tables, representing both data and relationships. Multiple columns within tables have unique names, and tables are equivalent to relations. A record-based model with fixed-format records. Each record specifies a fixed number of fields or attributes.
• Entity–relationship model: a model of data, composed of entities and their relationships. An entity is a real-world object and is distinguishable from other objects.
• Object-based model: OOP languages like C++, Java, C#, object-oriented data model; Object-based Model = E-R Model + OO features. Object-Relational Data Model = Object-Based Model + Relational Model.
• Semi-structured model: data specification, individual data items of the same type may have different sets of attributes; Contrast to previous data models; Extensible Markup Language (XML).
• Network and Hierarchical models: tied closely to the underlying implementation, complicated the task of modeling the data. Not widely used except in older databases.

Instances and Schemas

• Schemas: the overall design of a database, equivalent to variable declarations and type definitions. Schema changes are infrequent. Example of a student record schema type:

type Student = record Rollno : numeric (5); Name : char (25); Class : char (10); end;

- **Instances**: the collection of information stored at a particular moment. Instances change frequently with the value of the variable.

### Three-Schema Architecture

- Three levels: Internal Level, Conceptual Level, External Level.
- Goal: separating user applications from the physical database.
  - Internal level: describes the physical storage structure; complete details of data storage and access paths.
  - Conceptual level: hides physical storage structure; concentrates on describing entities, data types, relationships, constraints.
  - External level: describes the part of the database users are interested in, hides other database parts.

### Data Independence

- Definition: The capacity to change one schema level of a database system without modifying higher levels.
  - Logical Data Independence: ability to modify conceptual schema without changing external schemas or application programs.
  - Physical Data Independence: ability to modify internal schema without changing conceptual schema.


### DBMS Languages


- In DBMS where the separation of levels is not strict,  Data Definition Language (DDL) is used to define conceptual and internal schemas.
- Data definition language (DDL): used to specify conceptual schema only.
- Storage definition language (SDL): used to specify the internal schema.
- View definition language (VDL): used to specify a user's views and mapping them to the conceptual schema.
- Data manipulation language (DML): used to manipulate data in the database. Types of DMLs: high-level (non-procedural) DML: used to specify complex database operations concisely; low-level (procedural) DML: embedded in general-purpose programming language.

### DBMS Interfaces

- Menu-based Interfaces: present users with a list of options (menus), most popular are pull-down menus.
- Forms based interfaces: displays each user a form, designed for naive users.
- Graphical user interfaces: displays a schema to the user in diagrammatic form; utilizes both menus and forms; uses pointing device.
- Natural language interfaces: has its own schema and dictionary; refers to them when interpreting requests; generates high-level queries; dialogues with user for clarification if interpretation not successful.
- Parametric user interfaces: specialized interfaces for users with small sets of operations that need to be repeated, often designed by systems analysts or programmers.
- DBA interfaces: privileged commands, used by DBA staff only for tasks like creating accounts or granting access.


### DBMS Component Modules

- DDL compiler: processes schema definition and stores it in the catalog
- Query compiler: handles high-level queries
- Pre-compiler: extracts DML commands from application programs
- Runtime database processor: handles database requests at runtime
- Stored data manager: transfers data between disk and memory.


### Database System Utilities

- Loading utilities:  load existing data files; mentions the source file format and target data file structure; automatically reformats the data and stores it in the database file.
- File reorganization utilities: reorganizes database files into new file organizations.
- Performance monitoring utilities: monitor database usage, provides statistics to the DBA.

### Other Tools

- CASE tools: used during database design.
- Data dictionary (or data repository) system: stores catalog information.
- Application development environments: provide an environment for database application development (examples: JBuilder, PowerBuilder).
- Communication software: provides remote access to the database.

### Centralized DBMS Architecture

- Earlier mainframe computers processed all system functions.
- Users accessed systems via terminals with only display capabilities but no processing.
- Processing performed remotely on the computer system, whereas display information was sent to the terminals.
- Terminal prices declined, leading to PCs and workstations replacing terminals.

### Basic Client/Server Architectures

- Goal: define specialized servers with specific functionalities.
- Client: user machine with an interface and local processing.
- Server: provides services to client machines.

### Two-Tier Client/Server Architectures

- In RDBMS, user interfaces and application programs moved to the client side.
- Query and transaction functionality is on the server side (query server/transaction server).
- DBMS access is required; programs establish a connection with the DBMS (on the server side).
- Open Database Connectivity (ODBC) provides application programming interfaces (APIs) that enable client-side programs to interact with the DBMS.


### Three-Tier Client/Server Architectures

- Additional intermediate layer (Application server or web server) between client and database server
- Stores rules used to access data.
- Accepts requests from the client.
- Processes client requests.
- Sends commands to the database server.