NEB Grade 12 Computer Science Complete Notes

 (Subject Code TH=4281, PR=4282)

Unit 1 Database Management System (DBMS)

Data: 

• It is a raw fact.
• It doesn't have meaning.
• It may be a number, figure, character, or symbol.
• Examples: Ram 20, ledger, bank staff, student record, etc.

Information:

• After processing data we can get information.
• It is meaningful.
• Example: Ram is 20 years old.

Structure Query Language (SQL)

1. It is the official language of the database.
2. It is the relational language for the end-user.
3. Query language allows users to retrieve data from the database.
4. SQL is used as an intermediary for accessing many data from a database.

Types of database language or SQL

1. DDL (Data Definition Language)

1. DDL is a subset of SQL used to manage the structure of a database.
2. It creates, modifies, and deletes database objects like tables, views, and indexes.
3. DDL statements include CREATE, ALTER, and DROP for creating, modifying, and deleting objects.

In summary, DDL is essential for defining and managing the structure of a database, creating and modifying objects, enforcing data integrity, and controlling data access.

2. DML (Data Manipulation Language)

1. DML is a subset of SQL used to manipulate and retrieve data in a database.
2. It includes statements like INSERT, UPDATE, DELETE, and SELECT.
3. INSERT is used to add new data rows to a table.
4. UPDATE modifies existing data within a table.
5. DELETE removes specific data rows from a table.
6. SELECT retrieves data from one or more tables.

In summary, Data Manipulation Language (DML) enables the manipulation and retrieval of data in a database through INSERT, UPDATE, DELETE, and SELECT statements, respecting data integrity and supporting transactional operations.

Examples of DML Video Link

3. DCL (Data Control Language)

1. DCL is a subset of SQL used for authorization and access control in a database.
2. It grants or revokes permissions and privileges to users or roles.
3. GRANT statement is used to give specific privileges to users or roles.
4. REVOKE statement removes previously granted privileges.
5. DCL controls data access, ensuring data security and preventing unauthorized modifications.

In summary, Data Control Language (DCL) manages authorization and access control by granting or revoking permissions, controlling data access, and ensuring data security in a database.

Data Dictionary:

1. A data dictionary is like a storage place for information about data.
2. It helps us understand data and its properties, ensures consistency, and supports the analysis and management of data.

(V.V.Imp) Database Model:

1. A database model is a way to organize and structure data in a database.
2. It helps store, manage, and access data efficiently.

Types of Database Models:

1. Hierarchical Database Model.
2. Network Database Model.
3. Relational Database Model.

1. Hierarchical Database Model

1. The hierarchical database model organizes data in a tree-like structure.
2. Each record has a parent record and can have one or more child records.
3. Data is accessed and managed in a top-down manner, following a hierarchical path.
4. Relationships between records are represented by links.
5. The model provides a way to organize data based on parent-child relationships.
6. The hierarchical model may be less flexible for representing complex data relationships.
7. It is suitable for representing one-to-many relationships where each parent can have multiple children.
8. The hierarchical model was one of the earliest database models used in early computer systems.

In summary, the hierarchical database model organizes data in a tree-like structure with parent-child relationships. It follows a top-down approach, has limited flexibility for complex relationships, and was historically significant in early computer systems.

Advantages:

1. Easiest model.
2. Easy and fast if the parent is known.
3. It helps to build complex systems from simple components.

Disadvantages:

1. Old model.
2. It can't handle 'Many-to-Many'.
3. It increases data redundancy.

              

Fig: Hierarchical Database Model

2. Network Database Model

1. The network database model organizes data in a network-like structure.
2. It handles complex data relationships more effectively than the hierarchical model.
3. Data are represented as records connected by links.
4. The model allows for many-to-many relationships between records.
5. Each record can have multiple parent and child records, enabling flexible data retrieval.
6. The network model was developed as an improvement over the hierarchical model.
7. Data is accessed by following the links between records.
8. The network model was a significant development in the evolution of database models.

In summary, the network database model organizes data with complex relationships using a network-like structure. It allows for many-to-many relationships, provides flexibility in data access, and was an improvement over the hierarchical model in handling complex data relationships.

Fig: Network Database Model

Advantages:

1. It handles 'Many-to-Many' relationships.
2. It reduces data redundancy.
3. Searching is fast.

Disadvantages:

1. It is a more complex database model.
2. It needs larger programs to handle the relationships.
3. It is less secure compared to the hierarchical model.

3. Relational Database Model

1. The relational database model organizes data into tables.
2. Each table represents a specific entity or relationship.
3. Data is stored in rows (records) and columns (attributes) within the tables.
4. Relationships between tables are established through keys.
5. The model provides a logical and structured approach to data management.
6. It enables efficient storage, retrieval, and manipulation of data.
7. The relational model is widely used in various applications and industries.
8. It is known for its simplicity, ease of use, and flexibility in representing data relationships.

In summary, the relational database model organizes data into tables, represents entities and relationships, uses rows and columns, establishes relationships through keys, and provides a logical and structured approach to data management. It is widely used, known for its simplicity and flexibility in handling data.

Fig: Relational Database Model

Advantages:

1. Low data redundancy.
2. Normalization is possible.
3. Rapid processing of database.

Disadvantages:

1. Easiest model.
2. It is a more complex model than others.
3. Too many rules make the database non-user-friendly.

Relational Database Management System (RDBMS) vs Flat-file system:

Relational Database Management System (RDBMS):

1. A Relational Database Management System (RDBMS) is a software system.
2. It manages relational databases, which are structured collections of related data.
3. RDBMS allows users to store, retrieve, and manipulate data.
4. It follows predefined rules and operations to ensure data integrity.
5. RDBMS provides efficient data organization.
6. It supports powerful querying capabilities.
7. Popular examples of RDBMS are MySQL, Oracle, SQL Server, and PostgreSQL.

Flat-file system:

1. A flat-file system is a basic type of database system.
2. It stores data in a single, plain-text file.
3. There is no structured organization or relationships between data elements.
4. Each line in the file represents a record.
5. Fields within the record are separated by delimiters like commas or tabs.
6. Flat-file systems are simple to understand and implement.
7. They lack efficient querying and managing capabilities for complex data relationships.
8. They are commonly used for small-scale applications or when simplicity is preferred.

Advantages of RDBMS compared to the flat file system:

1. Organized structure for efficient storage and retrieval.
2. Ensures data accuracy and consistency.
3. Enables linking data elements together.
4. Powerful searching capabilities using SQL.
5. Handles large amounts of data effectively.
6. Allows multiple users to access data simultaneously without conflicts.
7. Strong security measures for data protection.
8. Facilitates data backup and recovery.
9. Reduces data duplication, saving storage space.
10. Supports easy application development.

Entity, Attribute, and Relationship:

Entity: An entity refers to a person, place, object, or concept that can be uniquely identified and represented in a database. It can be a real-world object like a customer or an abstract concept like an order.

Attribute: An attribute is a characteristic or property of an entity. It describes specific details or qualities related to the entity. For example, in a customer entity, attributes can include their name, address, or email.

Relationship: A relationship represents a connection or association between two or more entities. It describes how entities are related or interact with each other. For instance, a relationship between a customer and an order can represent that a customer places an order. The relationship can have additional attributes like the date of the order or the quantity ordered.

Types of Relationships:

1. One-to-one

One-to-One relationship is a type of relationship in a database where each record in one table is associated with exactly one record in another table. For example:

Fig: One-to-one

2. One-to-many

A One-to-Many relationship is a type of relationship in a database where a record in one table can be associated with multiple records in another table, but each record in the second table can only be associated with one record in the first table. For example: 

Fig: One-to-many

3. Many-to-one

A Many-to-One relationship is a type of relationship in a database where multiple records in one table can be associated with a single record in another table. For example:

Fig: Many-to-one

4. Many-to-many

A Many-to-Many relationship is a type of relationship in a database where multiple records in one table can be associated with multiple records in another table. For example:

Fig: Many-to-many

Fig: Illustrate record, field

E-R diagram:

1. E-R diagram represents relationships between entities in a database.
2. It uses boxes for entities and lines for relationships.
3. It helps understand the structure and organization of a database.
4. It serves as a blueprint for database design and management.

To build an E-R diagram we use the following components:

Fig: Component to build E-R diagram

For Example:

Fig: Example of E-R diagram

Advantages of ER diagram:

1. It provides a visual representation of the database.
2. It is simple and easier to understand.
3. It helps in identifying and defining entities in the database.
4. They illustrate relationships between entities

Concept of Normalization(VVImp):

1. It is a technique to remove or reduce redundancy from a table.
2. Process of breaking down or decomposing relationships into small relationships to reduce redundancy without losing any information.

Advantages of Normalization:

1. It reduces data redundancy.
2. It improves faster sorting and index creation.
3. It improves the performance of the system.
4. It avoids the loss of information.

The process to change the unnormalized table to a different Normalization form:

Let's consider an Unnormalize table:

1 NF (First Normal Form)  :

• All the attributes should be atomic.
• There should not be repeated groups.

2 NF (Second Normal Form) :

• It should be in 1 NF.
• Introduce the primary key and foreign key to eliminate functional/partial dependencies.

3 NF (Third Normal Form) :

• It should be in 2 NF.
• Remove transitive dependency.

Old Questions:

Centralized vs Distributed database:

Imp questions 

Write the Difference between Centralized and Distributed databases.

Centralized Database:

1. A centralized database stores all data in one central location or server.
2. It allows for easy management and control of data in one place.
3. Data access and modifications are controlled by a single authority.
4. Centralized databases are often used in small to medium-sized organizations.
5. Centralized databases might face performance problems with a large number of users or data.
6. The central server fails, and the whole system is affected.

Fig: Centralized database

Advantages:

1. It is simple to implement and manage.
2. It has less or no risk of data loss.
3. Low cost of implementation and operation.
4. Suitable for small organizations.

Disadvantages:

1. If the central server goes down, the entire system is affected.
2. It is not reliable for distributed databases.
3. It is not suitable for big organizations.

Distributed Database:

1. A distributed database stores data in multiple locations or servers.
2. Data is spread out in different places for better access and speed.
3. It can handle system failures better because data is copied in different locations.
4. Users can access data locally, which reduces reliance on the network.
5. It can grow easily by adding more locations to store data.
6. Faster performance because it can work on tasks simultaneously and balance the workload.
7. It is a good choice for organizations with many offices in different places.

Fig: Distributed database

Advantages:

1. Data is stored closer to users, leading to faster access times.
2. The workload is distributed, allowing for better performance.
3. Can easily handle more data and users by adding more servers.
4. Each site can control its data independently.
5. Local access reduces the need for extensive data transfers.

Disadvantages:

1. Setting up and managing a distributed database is more complex.
2. Ensuring data consistency across multiple sites can be challenging.
3. Implementation and maintenance costs can be higher due to multiple sites.
4. Network issues can affect data access and performance.

Difference between centralized and distributed databases:

Data Security:

1. Data security means keeping data safe from unauthorized access or misuse.
2. Data security includes things like passwords, encryption, and controls to limit who can access data.
3. Data security is important because it helps prevent things like hacking or stealing personal information.

Some Other terms related to DBMS:

Data Abstraction:

1. Data abstraction is a concept in computer science that focuses on hiding unnecessary details of data and exposing only relevant information.
2. Abstraction helps in simplifying the complexity of data representation and manipulation.

Database Administrator (DBA):

1. Database Administrator (DBA) is a professional responsible for managing and maintaining databases.
2. DBAs monitor database performance, optimize queries, and handle backups and recovery.

Data Integrity:

1. Data Integrity refers to the accuracy, consistency, and reliability of data.
2. Techniques like validation rules, checksums, and encryption are used to ensure data integrity.

Practical

1. DDL

Create:

Syntax: CREATE DATABASE DATABASENAME;

Eg. CREATE DATABASE class_12;

Create a table:

CREATE TABLE student_info (

    regno INTEGER,

    name CHARACTER(25),

    class INTEGER,

    gender CHARACTER(1),

    address CHARACTER(25)

);

Drop:

Syntax: DROP DATABASE DATABASENAME;

Eg. DROP DATABASE class_12;

Alter:

Modify database table:

ALTER TABLE student_info

ADD COLUMN age INTEGER;

2. DML

Insert:

Syntax: INSERT INTO TABLENAME (COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2,....);

Eg. INSERT INTO student_info (regno, name, class, gender, address)

VALUES (12345, 'John Doe', 10, 'M', '123 Main Street');

Update:

UPDATE student_info

SET class = 11, address = '456 Elm Street'

WHERE regno = 12345;

Select:

Syntax: SELECT COLUMN1,COLUMN2....... FROM TABLENAME;

Eg. SELECT *FROM student;

SELECT regno, name, class, gender, address

FROM student_info;

Delete:

DELETE FROM student_info

WHERE regno = 12345;

3. 

1. Consider the following schema and create the following table.

Student (snum, sname, major,age);

a. Specify your own required data type and length for attributes.

b. Specify the primary key.

c. Insert details in the student table.

d. Find the name of the student whose major is in Computer Science.

IMP MCQ

Question 1: What does DBMS stand for?

A) Database Migration System

B) Digital Business Management System

C) Data-Based Monitoring System

D) Database Management System

Answer: D) Database Management System

Question 2: Which of the following is NOT a function of a DBMS?

A) Data Storage

B) Data Retrieval

C) Data Encryption

D) Data Analysis

Answer: C) Data Encryption

Question 3: Which component of DBMS is responsible for enforcing data integrity rules?

A) Query Optimizer

B) Data Dictionary

C) Transaction Manager

D) Data Constraint

Answer: D) Data Constraint

Question 4: In a relational database, what is a primary key used for?

A) Storing large data sets

B) Sorting data in tables

C) Identifying unique records

D) Creating database backups

Answer: C) Identifying unique records

Question 5: Which type of relationship allows multiple records in one table to be associated with multiple records in another table?

A) One-to-One

B) One-to-Many

C) Many-to-One

D) Many-to-Many

Answer: D) Many-to-Many

Question 6: Which language is commonly used to query and manipulate data in a DBMS?

A) Java

B) C++

C) SQL

D) HTML

Answer: C) SQL

Question 7: Which DBMS model stores data in a hierarchical structure with parent-child relationships?

A) Relational DBMS

B) Hierarchical DBMS

C) Network DBMS

D) NoSQL DBMS

Answer: B) Hierarchical DBMS

Question 8: Which ACID property ensures that a transaction brings the database from one consistent state to another?

A) Atomicity

B) Consistency

C) Isolation

D) Durability

Answer: B) Consistency

Question 9: What is the purpose of indexing in a DBMS?

A) Encrypting data for security

B) Organizing data storage

C) Creating backup copies

D) Performing complex calculations

Answer: B) Organizing data storage

Question 10: Which database model is designed to handle unstructured or semi-structured data?

A) Relational Database Model

B) Document Database Model

C) Hierarchical Database Model

D) Network Database Model

Answer: B) Document Database Model

Question 11: What is a DBMS schema?

A) A backup copy of the entire database

B) A collection of database tables

C) A blueprint that defines the structure of the database

D) A report generated from the database

Answer: C) A blueprint that defines the structure of the database

Question 12: Which type of key uniquely identifies a record within a table and is used as a reference in other tables?

A) Foreign Key

B) Primary Key

C) Composite Key

D) Secondary Key

Answer: B) Primary Key

Question 13: Which normal form ensures that every non-key attribute is fully functionally dependent on the primary key?

A) First Normal Form (1NF)

B) Second Normal Form (2NF)

C) Third Normal Form (3NF)

D) Boyce-Codd Normal Form (BCNF)

Answer: C) Third Normal Form (3NF)

Question 14: Which SQL command is used to retrieve data from a database?

A) UPDATE

B) INSERT

C) DELETE

D) SELECT

Answer: D) SELECT

Question 15: What is the purpose of a database index?

A) To encrypt sensitive data

B) To enforce data integrity

C) To optimize data retrieval speed

D) To generate reports

Answer: C) To optimize data retrieval speed

Question 16: In the ACID properties of a transaction, what does "Isolation" refer to?

A) Ensuring data consistency after a transaction

B) Allowing multiple transactions to occur simultaneously

C) Preventing unauthorized access to data

D) Ensuring that a transaction is completed successfully

Answer: B) Allowing multiple transactions to occur simultaneously

Question 17: Which type of join returns only the matching records from both tables?

A) Inner Join

B) Outer Join

C) Self Join

D) Cross Join

Answer: A) Inner Join

Question 18: What is a data dictionary in a DBMS?

A) A table that stores user data

B) A collection of SQL commands

C) A catalog of metadata about the database

D) A report generated from the database

Answer: C) A catalog of metadata about the database

Question 19: Which type of DBMS is designed for handling large volumes of unstructured data?

A) Relational DBMS

B) NoSQL DBMS

C) Hierarchical DBMS

D) Network DBMS

Answer: B) NoSQL DBMS

Question 20: Which DBMS component manages concurrent access to the database by multiple users?

A) Data Dictionary

B) Query Optimizer

C) Transaction Manager

D) Backup and Recovery Manager

Answer: C) Transaction Manager

Old Questions Solution

Write SQL DDL commands to execute the following task with reference to the schema given below:

student_info (regno as integer, name as character (25), class integer, gender character (1), address

character(25).

Answer:

CREATE TABLE student_info (

    regno INTEGER,

    name CHARACTER(25),

    class INTEGER,

    gender CHARACTER(1),

    address CHARACTER(25)

);

This command creates a table named student_info with five columns: regno, name, class, gender, and address, each with its specified data type. Make sure to execute this command in your SQL database management system to create the table.

Explain the Relational Data Model with an example.