Database Concepts | Computer Awareness and Proficiency - SSC CGL PDF Download

A database is a systematically organized collection of related information that allows for easy access, management, and updating. It supports various operations, including adding, updating, and deleting data.

Fundamentals of a Database

• Data: These are raw and unprocessed facts, including digital representations of text, numbers, images, or sound. For example, a student's test score is a piece of data.

• Information: When data is processed, organized, or structured within a context to make it meaningful, it becomes information. For example, the average score of a class derived from individual test scores is information.

Types of Databases

1. Network Database: Data is represented as a set of records and the relationships between them are shown as links.
2. Hierarchical Database: Data is organized in a tree-like structure with nodes connected by links.
3. Relational Database: Also known as a structured database, data is stored in tables where columns define the type of data and rows represent the individual records.

Components of a Database

A database is made up of several components, each referred to as an object:

1. Tables: These are the core elements of a relational database where actual data is defined and stored. Tables consist of cells formed at the intersection of rows and columns, with operations including storing, filtering, retrieving, and editing data.
   • Field: A specific area within a record reserved for a particular piece of data, such as customer number, name, or address. Fields are also known as columns.
   • Record: A collection of fields related to a single entity, such as a person or transaction. Records are also known as rows or tuples, and the total number of records is called the cardinality of the table.
2. Queries: These are questions formulated to retrieve specific data from the database. A query specifies which fields, records, and summaries are needed, allowing users to extract data based on defined criteria.
3. Forms: These are used to view and modify data in a more controlled manner compared to tables. Forms allow users to interact with data one record at a time and can be designed to resemble paper forms, making data entry easier and more intuitive.
4. Reports: These are designed to print records retrieved from the database. Reports can be customized for various purposes, such as producing mailing labels, and some database systems provide wizards to assist in report creation.

Database Management System (DBMS)

• A Database Management System (DBMS) is a suite of programs designed to manage and retrieve data from a database.
• It organizes data into a coherent whole, rather than treating it as separate, unrelated files.
• The main objective of a DBMS is to create a convenient and efficient environment for storing and retrieving data.
• Examples of DBMSs include MySQL, Oracle, FoxPro, dBASE, Sybase, and MS Access.
• The DBMS serves to bridge the gap between data and the information derived from it.

Basic Functions of a DBMS

1. Defining Data Types and Structures: Specifying the types, structures, and constraints for data used in an application.
2. Storing Data: Saving data in a structured format.
3. Manipulating Data: Performing operations to modify or manage the data.
4. Querying Data: Retrieving specific information from the database based on user queries.
5. Updating Data: Modifying the content within the database.

DBMS Architecture

The architecture of a DBMS is typically divided into three levels:

1. Internal Level: Also known as the physical level, this is the lowest level of data abstraction. It deals with the physical storage of data on the computer, defining how data is actually stored and organized.
2. Conceptual Level: Known as the logical level, this provides a high-level view of the database. It describes what data is stored, the relationships among the data, and is independent of the physical storage.
3. External Level: Also known as the view level, this is the highest level of data abstraction. It defines how users interact with the system and allows data to be viewed in different ways according to user needs.

Advantages of a DBMS

1. Reduced Data Redundancy: DBMS centralizes data storage, avoiding the duplication of data and ensuring that all applications refer to a single, controlled source of data.
2. Improved User Interaction: Updated information enhances data access and responsiveness to user queries.
3. Enhanced Data Security: DBMSs offer security features such as usernames and passwords to control access.
4. Maintained Data Integrity: DBMSs enforce integrity constraints to ensure the accuracy of data across multiple users.
5. Backup and Recovery: Provides mechanisms to back up data and recover it in case of hardware or software failures.

Disadvantages of a DBMS

1. High Costs: DBMSs require expensive hardware and software, necessitating upgrades and investments.
2. Complexity: The advanced functionality of DBMSs can make them complex and difficult to manage, leading to potential design issues if not properly understood.
3. Training Costs: Training staff to use complex DBMS software can be costly.
4. Technical Staffing: Employing skilled professionals such as database administrators and application programmers adds to the cost.
5. Database Failure: Centralizing all data in one database can pose a risk of significant data loss or system downtime if the database becomes corrupted.

Applications of DBMS

1. Banking: Managing customer information, accounts, loans, and transactions.
2. Reservation Systems: Handling reservations and scheduling information.
3. Universities: Tracking student information, course registrations, and grades.
4. Credit Card Transactions: Processing credit card purchases and generating statements.
5. Telecommunications: Recording call details and generating bills.
6. Finance: Storing information about financial holdings, sales, and purchases.
7. Sales: Managing customer, product, and purchase data.

Relational Databases

In a relational database, data is stored across multiple tables, with relationships defined between them. A Relational Database Management System (RDBMS) manages these tables and their relationships. An RDBMS can spread a single database's data across several tables. Examples include Base, Oracle, DB2, Sybase, and Informix.

Terms Related to Relational Databases

1. Relation: A relation in a relational database is a table with rows and columns representing data items and their relationships. It has three key properties:

   • Name: The name of the relation, also known as the table name or entity identifier.
   • Cardinality: The number of tuples (rows) in the relation.
   • Degree: The number of attributes (columns) in each tuple.

2. Domain: A domain is the set of all possible values that an attribute or column in a table can take. If the elements of a domain are indivisible, the domain is considered atomic.

3. Attributes: Attributes are the column headings in a table, each having a distinct name.

4. Tuples: Tuples are the rows in a relation, each consisting of a set of values for the attributes.

5. Key: A key is a column or set of columns used to uniquely identify rows in a table or establish relationships with other tables. For example, in a table with columns like ID, name, and address, each of these columns could serve as a key to uniquely identify records.

Types of Keys

1. Primary Key: This is a set of one or more attributes that uniquely identifies each tuple in a relation. The primary key must be unique and non-null. It can be either atomic (a single attribute) or composite (multiple attributes).
2. Candidate Key: These are the set of all possible attributes that can uniquely identify each tuple in a relation. Among them, one is chosen as the primary key. Candidate keys are minimal keys, meaning they cannot have any unnecessary attributes.
3. Alternate Key: After selecting the primary key, the remaining candidate keys are known as alternate keys.
4. Foreign Key: This is a non-key attribute in one table that refers to the primary key of another table, establishing a relationship between the two tables. A table can have multiple foreign keys, each referencing different tables.

Database Languages

1. Data Definition Language (DDL): DDL is used to define the structure of database tables and other objects. It specifies the database schema through various definitions.
2. Data Manipulation Language (DML): DML provides commands for accessing and manipulating data within the database, including inserting, retrieving, deleting, and modifying data.
3. Data Control Language (DCL): DCL commands are used to manage database security and user access, assigning roles and privileges to users.

Entity-Relationship Model (E-R Model)

The E-R Model is a diagrammatic representation of the entities in a database and their relationships. It is also known as the E-R Diagram.

• Entity: An entity is an object that exists in the real world and about which data is collected. Entities are represented by rectangles in E-R diagrams. For example, in a scenario where a customer buys items, both "Customer" and "Items" are entities.
• Attributes: Attributes describe the properties or characteristics of an entity. In tables, attributes are represented by columns and are drawn as ellipses in E-R diagrams. For example, the "Items" entity might have attributes like "ItemId" and "Price."
• Entity Set: An entity set is a collection of entities of the same type with shared properties or attributes. For instance, all student entities in a database form an entity set.

There are two types of entity sets:

• Strong Entity Set: An entity set with a primary key or sufficient attributes to uniquely identify each entity.
• Weak Entity Set: An entity set that lacks sufficient attributes to form a primary key.

Relationship: A relationship represents how multiple entities are associated with one another. It is depicted by a diamond shape in E-R diagrams. Relationships can be categorized into three types:

• One-to-One: A single record in Table A is related to a single record in Table B, and vice versa.
• One-to-Many: One record in Table A is related to multiple records in Table B, but each record in Table B relates to only one record in Table A.
• Many-to-Many: Each record in Table A can relate to multiple records in Table B, and each record in Table B can relate to multiple records in Table A.

Tit-Bits

• Dr. EF Codd presented 12 principles for Relational Database Management System (RDBMS) in 1970.
• Schema stands for the logical structure of the database.
• Instances refer to the actual data present in the database at a specific time.
• Data Duplication not only uses up unnecessary space but also leads to a critical issue known as data inconsistency.
• Data Mining involves sifting through extensive data collections to recognize patterns and establish connections for problem-solving via data analysis.