Unit 3 Database Management Computer Science Class 12 – Comprehensive Notes

In this article, I will provide you with comprehensive notes on Unit 3 Database Management Computer Science Class 12. So here we go!

Introduction

Every organization is handling data. Keeping and maintaining records is one of the important tasks for the organization for future transactions and growing the business or services.

Organizations can maintain and keep the track record of transactions in 2 ways:

1. Mannual : By entry in registers
2. Computerised: By a software

Mannual Record Keeping

In the manual record-keeping system the entries are done in the registers physically. Each and every record is entered by a person at the reception. The following steps are considered as an entry for the hospital management system:

1. The doctor informaton is recorded into the register of doctors. Whenever a doctor join or resigns from the hospital, his/her information will be added, updated or removed from the register.
2. A patient id is generated by a specific set of rules (Constraints) and pateint information such as name, age, height, weight, gender, address, phoneno, disease and doctorid.
3. The lab register contains the record for lab information which stores the fields like labid, patient id, weight, doctor id, date, category, patient type and amount.
4. The register to maintain patient in records contains the patient id, room number, date of admit, date of discharge, advance and labno.
5. The patient out records can be maintined by another register and contains recorded with patient id, date and lab number.
6. To maintain the room related records a separate register can be maintained which contains information regarding room number, room type and status.
7. To keep and record bill details, a bill book can be maintained with handwritten bills with the fields bill number, patient id, patient type, doctor charge, medicine charges, room charges, operation charges, number of days, nursing charges, advance, health card, lab charges and bill amount.

Record-keeping using software

Normally database allows to create, edit, delete and retrieve the necessary information as and when required. Let us explore an example of a patient information system database.

To work efficiently and search records as and when required the pen-paper approach is difficult when these records are in huge amounts. So to avoid such tedious tasks it should be done by software so the staff can work efficiently when a patient is admitted or visited the hospital. This can be done by file system or database management system.

File System – Unit 3 Database Management Computer Science Class 12

A file is a container that stores particular data and information in computer which can be stored in storage device via text or program code or in CSV or in form of images, videos, audios, web pages.

File System

The data stored in files can be accessed directly via a particular program. So every register can have separate files to store data. A number of registers as a number of files are required to maintain the records and organize the data to work upon. In addition to this, the following are limitations of the file system:

1. To access data stored in files, separate programs are needed. So while writing the code the developer may not consider all the aspects required for the files. So it is difficult to access such data in the required format.
2. There will be a huge changes of repeating records in files. As once the entry is made it is difficult to remember whether such record has been already exists or not!
3. If the file is operated by different people, it is difficult to work on files at same time. This leads to data inconsistency.
4. Data mapping or linking is not possible in the file system between two files. New application or program required for new file.
5. Data stored in the files have their own structure format. Every file can have different file format. So specific application or program needs to be installed to access data.
6. If more than one user is working on the data then it is not possible to share details at same time.

In the next section of Unit 3 Database Management Computer Science Class 12, we are going to discuss the database management system topic.

Database Management System – Unit 3 Database Management Computer Science Class 12

To overcome the limitations of the file system, a database management system can be used. A database can store, organize and maintain data easily and efficiently.

Database Management System refers to a software that is used to create and manage database. This softeware allows to create database, store, manage, update/modify and retrive data from the database as and when required by application.

A few examples of DBMS software are MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB.

DBMS offers data abstraction features. Abstraction refers that data hiding. The data is not directly available for the users. Users need to have specific access to programs to access, modify and retrieve stored data. Actually DBMS servers as an interface between database and users.

To retrieve data from the database in DBMS requires a special type of command known as a query. Users can alter/modify the database itself using various commands as well.

Application Areas of DBMS

Let us understand the various application areas of DBMS in this article Unit 3 Database Management Computer Science Class 12. The application areas of DBMS are as follows:

File system to Database Management System

As we have seen different files can be created in the file system but they cannot be linked together. Whereas DBMS allows to link or join them using a common field.

For example, we have two files: doctor and patient. The doctor table has a field doctor_id that holds the unique id for every doctor working in the hospital. When the doctor is handling a case of a particular patient, that patient can be assigned to the doctor using this doctor_id. So common field for both is doctor_id and then the data can be retrieved from both tables. Then if the patient is admitted into the hospital it can be further linked with the room and bill table using patient_id.

Data Models

Data model refers to a specificatio of structure of database. Data model defines how the database can be structured and can be used.

Few popular data models are as follows:

1. Flat model:This may not strictly qualify as a data model. The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another.
2. Hierarchical model:The hierarchical model is similar to the network model except that links in the hierarchical model form a tree structure, while the network model allows arbitrary graph.
3. Network model:This model organizes data using two fundamental constructs, called records and sets. Records contain fields, and sets define one-to-many relationships between records: one owner, many members. The network data model is an abstraction of the design concept used in the implementation of databases.
4. Relational model: It is a database model based on first-order predicate logic. Its core idea is to describe a database as a collection of predicates over a finite set of predicate variables, describing constraints on the possible values and combinations of values. The power of the relational data model lies in its mathematical foundations and a simple user-level paradigm.
5. Object-relational model: It is similar to a relational database model, but objects, classes and inheritance are directly supported in database schemas and in the query language.
6. Object-role modeling: A method of data modeling that has been defined as “attribute free”, and “fact-based”. The result is a verifiably correct system, from which other common artifacts, such as ERD, UML, and semantic models may be derived. Associations between data objects are described during the database design procedure, such that normalization is an inevitable result of the process.

Now let’s have a look at a few terms are associated with the relational data model for Unit 3 Database Management Computer Science Class 12.

Relational Data Model

The most popular data model among the data models is the relational data model. Let’s discuss a few terms associated with the relational data model.

Relation

The relation refers to a database table in the relational data model. A relation contains records (row or tuples) and fields (columns or attributes). The record represents a set of values. The column name should be unique.

Attributes

The attributes are columns or fields of a table. Columns contain characteristics or parameters for which data are to be stored in the table.

Tuple

Each row of a table is known as a tuple or record. In a table with n columns, a tuple is a relationship between n related values.

Domain

The set of values from which each attribute takes a value for each row. To specify the data for the domain, the data type can be determined.

Degree

The total number of columns in a table is known as a degree.

Cardinality

The total number of rows in the table is known as cardinality.

Keys in a relational database

Keys in a relational database refer to the restrictions or constraints on the values of attributes and contents related to each other. The keys are specified at the time of defining the database through keys. These keys are:

1. Candidate Key : Candidate keys are those attributes of a table which can be used to identify the records uniquely. These attributes can be candidates for primary key.
2. Alternate Key: After assigning the candidate key and primary key the remaining attributes of the relation is alternate key.
3. Primary Key: The primary key is the column which can be used to identify the reacords uniquely.
4. Composite Primary Key: When more than one attribute is assigned as primary key, is known as composite primary key.
5. Foreign Key: The foreign key in a relation is derived from another table, whic is primary key of anothrer table.

Watch this video for more understanding:

Now in the next section of Unit 3 Database Management Computer Science Class 12, we are going to discuss the next topic Structured Query Language.

Structured Query Language

• DBMS offers a special kind of command language known as Structured Query Language.
• This language is used to create, manage and manipulate the database.
• SQL is the most popular query language used by MySQL, Oraccle, SQL Server etc.
• SQL is case insensitive language and the commands are written in English language.
• SQL provides commands for creating, modifying and manipulating data as well as they also provide commands for defining structure, declaring constraints, retrieving data and removing database objects.
• In this syllabus we are going to use MySQL for queries.

Downloading and Installing MySQL

• Downloading MySQL is quite easy.
• Follow these steps to download MySQl.
  • Open the official website of MySQL community. Click here.
  • Install MySQL and enjoy querying.
  • Follow this link to configure MySQL in windows: Click here

Starting MySQL and working with database

• Click on Start button
• Scroll for MySQL and expand
• Select MySQL Command Line Client
• It will open the screen enter the password you have given while configuring MySQL
• To view the database list, type

show databases;

• To work on database

use employee

In the above example, the employee is the database name.

The following points need to be kept in mind while using SQL:

1. Every command in MySQL ends with a semicolon(;).
2. SQL is case insensitive language.
3. You can write multiple lines in a single command. Press enter to start a new while writing command.
4. If any mistake made in the previous line, you cannot edit that line once enter is pressed. You have to start it again from the beggining.

In the next section of Unit 3 Database Management Computer Science Class 12, we are going to discuss MySQL data types.

MySQL Data Types

Each attribute in the database has a data type. Data type represents which kind of data is going to be entered in the column. The data type can be determined according to the column.

Sometimes calculations can be performed on columns. Hence selecting the appropriate data type is a must for the attributes of relations. Let us talk about some commonly used data types available in MySQL. Here we go!

Constraints in MySQL

• Cosntraints are some set of rules used for data validation.
• They are some rectrictions applied on attribute values.
• It enforces the standards and flawless data.
• Its not necessary to define constraints for each attribute of a table.

Commonly used MySQL constraints

The commonly used constraints are as follows:

Now in the next section of Unit 3 Database Management Computer Science Class 12 we are going to talk about SQL for Data Definition. Here we go!

SQL for Data Definition

Data definition refers to defining the structure of database objects. It includes commands as follows:

1. Creating tables and other database objects
2. Defining schemas
3. Defining and Givning names to the constraints
4. Defining the datatype for each attribute
5. Modifying relation schemas
6. Deleting relation schemas

Some of the DDL commands are following:

1. Create
2. Alter
3. Drop
4. Truncate
5. Rename

Create Database Command

The create database command is used to create a database. The syntax is:

create database <databasename>

Example:

create database employee

Observe the following screenshot indicates the command after successfully execution of create database command.

After creating the database you need to open the database using the use command. Initially, the database is empty. You can check it by using the following command:

show tables;

It shows Empty Set, as the database is empty. You need to create tables using create table command. So let’s talk about create table command.

Create Table Command – Unit 3 Database Management Computer Science Class 12

The create table has the following syntax:

create table <tablename>
(column_name <datatype(size)> [constraint],...,column_name <datatype(size)> [constraint]);

• Where create table is a command
• <tablename> is name of table to be created
• column_name is name of column
• <datatype(size)> is a data type and size refers to number of digits or characters hold bhy the field
• [constraint] is constraint specification (this is optional part)

Observe the following table structure.

Create table without constraint:

create table patient
(pid int(4),
pname varchar(20),
age int(2),
department varchar(15),
dateofadm date,
charges double(7,2),
gender char(1));

In the above statement, every column definition is separated by a comma, MySQL shows -> symbol when you start a new line. Semicolon (;) is used to end the command.

Applying constraints to a table

You can apply the constraints in the following ways:

1. Column level
2. Table level
3. Alter Table

Column Level

• The column lelvel constraint is specified along with the column definition.
• The constraint will be applied immediately after datatype(size) in column definition.
• Have a look at the following command:

create table patient
(pid int(4) primary key,
pname varchar(20) not null,
age int(2),
department varchar(15),
dateofadm date,
charges double(7,2),
gender char(1));

Table Level

• The table level cosntraint will be written at the end of create table command.
• The cosntraint sepcification will be written immediately after the alst column definition followed by comma.
• Have a look at the following command:

create table patient
(pid int(4),
pname varchar(20),
age int(2),
department varchar(15),
dateofadm date,
charges double(7,2),
gender char(1), primary key(pid));

In the above screenshot, observe the last line of the create table command, pid is assigned as the primary key.

Alter table

• The constraints will be applies through alter table
• Alter table command followed by add constraint is used to apply constraint.
• Have a look at the following example

alter table patient 
add primary key(pid);

Add foreign key

To assign a foreign key follow these points:

1. The table with primary key must be created for reference.
2. Data type and column name should be the same.

The syntax is as follows:

alter table <tablename> add foreign key (columnname) references referenced_table_name (columnname);

Example:

alter table patient add foreign key (doc_id) references doctor (doc_id);

Add unique key using alter

The unique key can be added using alter table command. The syntax is as follows:

altert table <tablename> add unique key (columnname);

Obser this example:

alter table patient add unique key (email);

Add column to the table

To add a column alter table command is used. The syntax is as follows:

alter table <tablename> add column <columnname datatype(size)>;

Observe this example:

alter table patient add column remark varchar(20);

Modify the datatype of the column

The syntax is as follows:

alter table <tablename> modify columnname datatype;

Example:

alter table patient modify age int(3);

Modifying a constraint not null

You can modify constraint not null by using this command:

alter table <tablename> modify <columname> datatype(size) not null;

Example:

alter table patient modify pname varchar(20) not null;

Add default value

The syntax is as follows:

alter table <tablename> modify <columnname> datatype default <defaultvalue>;

Example:

alter table patient modify gender char(2) default 'M';

Remove column

Syntax:

alter table <tablename> drop column;

Example:

alter table patient drop gender;

Remove primary key

Syntax:

alter table <tablename> drop primary key;

Example:

alter table patient drop primary key;

Describe Table (Desc command)

This command is used to view the structure of the table. You can use describe or desc command for the table.

desc patient;

Drop Command

• The drop command is used to remove the database as well as database object such table.
• Removing the database

drop database hosptial;

• Removing the table

drop table patient

Watch this video for more understanding:

SQL for Data Manipulation (DML)

The data manipulation in MySQL can be done using different commands. These commands are: insert, update, delete etc.

insert command

The insert command has the following syntax:

insert into <tablename> values (value1,....,valuen);

Example:

insert into patient values (1,'Dipak',34,'Cardiac','2022/12/02',400,'M');

You can leave some columns blank as follows:

insert into patient (pid,pname,age,department) values (2,'Mansi',45,'Orthopadic');

You can observe the columns having a null value which are not provided in the bracket before the word of the value. Observe the following:

Update Command

• The update command is used to make changes in the values of one or more column of the table.
• It allows modifications in any record by specifying new value and column name.
• The syntax for update is:

update <tablename> set <columnname>=<newvalue> where <condition>;

• Example:

update patient set dateofadm='2021-12-02' where pid=1;
update patient set dateofadm='2021-12-25',charges=450, gender='F' where pid=2; 

Delete Command

• The delete command deletes the sepcified rows from the table
• The syntax for delete command is as follows:

delete from <tablename> where <conditon>

• Where
  • delete is a command to delete record
  • from <tablename> clause mention the table name from where record is goign to be deleted
  • where <condition> clause contains the criteria for deleting records
• Observe this example:

delete from patient where pid=2;

• You can delete all records using following command:

delete from patient;

SQL for Data Query (Select command)

In this section of Unit 3 Database Management Computer Science Class 12, we are going to cover SQL for data queries. For data query, the select command is used. It has various forms. You can use select to fetch records from the relation. You can use the select command to fetch:

1. All rows all columns
2. All rows limited columns
3. Limited rows all columns
4. Limited rows Limited columns

All rows all columns

• The * symbol is used to fetch all columns from a relation in select clause
• To fetch all records from the relation select command is used as follows:

select * from <tablename>;

select * from patient;

All rows limited columns

• The limited columns will be retrieved by separting columns using comma in select clause
• Mention all the required columns in the select clause
• Separate all the columns using comma
• The syntax is as follows:

select <column1, column2, column3,...,columnn> from <tablename>

• Example

select pid,pname,dateadm from patient;

Limited rows all columns

• Limited rows fetched using where clause
• Where clause is specified using various criteria
• The different operators are used to fetch limited rows
• These operators are:
  • relational operators (=,!=,<,>,<=,>=)
  • logical operators (and,or,not)
  • is, is not (For null)
  • pattern matching operator like

Relational Operators in select command

• Observe these examples for relational operators used in the select command

select * from patient where pid=1;

The above example returns the patient information whose pid is 1. Few more examples!!!

select * from patient where department='Men';
select * from patient where gender='F';

Let us see the example of != operator:

select * from patient where department!='Cardiac';

This command ignores the records of ‘Cardiac’ department from the table and returns the rest of all records. Observe the output:

The following examples demonstrate the use of <,>,<=,>=. Observe these queries:

select pid,pname,age,department,dateofadm,charges from patient where charges <2500;

select pid,pname,age,charges from patient where age>30;

select * from patient where dateofadm<'2021--01';

select * from patient where dateofadm>'2021-10-15';

select * from patient where charges>=1800;
select * from patient where age<=41;
select * from patient where dateofadm<='2021-10-19'

Logical operators in select command

The logical operators are used to combine more than one condition at a time. It is also used to specify the range, list of values and exclusion of the values. There are three operators used in this category:

1. and
2. or
3. not

Observe these queries:

select * from patient where charges>=500 and charges<=1800;

The above query can be also done in the following way:

select * from patient where charges between 500 and 1800;

Note: When you don’t want to include the starting value and ending value of range use and operator only. Between-and operator includes the upper and lower value of range in the output. Now observe this query.

select * from patient where department='cardiac' or department='orthopadic' or deparmtent='men';

Observe the following query:

select * from patient where department in ('cardiac','orthopadic','men');

The in and or operator is used to fetch records from one column. The records coming from the same column here. You can provide the list of values for the same. Now if you want to display values that are not in the specific criteria, then you can use not operator. Observe this query and output:

select * from patient where department not in ('cardiac','orthopadic');

is-is not operator

The is and is not operator is used to fetch the records from the table having null values. If you want to access the null value record. Observe this query:

select * from patient where charges is null;
select * from patient where charges is not null;

Like operator

The like operator is used to fetch the record by identifying the character pattern. To find a specific positional _ is used and for any number of characters, % is used. Observe the following:

[1] Display names starting with a specific character ‘d’

select * from patient where pname like 'd%';

[2] Display records of patients whose names ends with ‘a’

select * from patient where pname like '%a';

[3] Display records of patients whose second letter is ‘a’

select * from patient where pname like '_a%';

[4] Display records whose name contains ‘a’

select * from patient where pname like '%a%';

Functions in MySQL

In this section of Unit 3 Database Management Computer Science Class 12, we are going to discuss functions in MySQL. Follow these links for the same:

1. MySQL Math and Text functions
2. MySQL aggrgate functions
3. MYSQL group by and having

Watch this video for more understanding:

MySQL Joins

We can perform different operations on more than one table in MySQL. Joins are used to fetch records from more than one table in MySQL. To perform joins you need to have tables with primary key and foreign key.

Cartesian Product (X)

• It refers to the records fetched from two tables
• It combines records of two tables
• The result of cartesian product is all pairs of rows from two tables
• It fetches the sum of columns and product of rows from both tables
• It is denoted by X
• Suppose you have two tables, one table contains 5 rows and 3 columns and another table contains 14 rows and 7 columns then the cartesian product will be 70 rows and 12 columns
• The query will be:

select * from table1, table2;

Displaying records that matches with exact values

 select * from student, guardian where student.guid=guardian.guid;