In this article, I will provide you with comprehensive notes on Unit 3 Database Management Computer Science Class 12. So here we go!
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:
- Mannual : By entry in registers
- 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:
- 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.
- 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.
- 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.
- The register to maintain patient in records contains the patient id, room number, date of admit, date of discharge, advance and labno.
- The patient out records can be maintined by another register and contains recorded with patient id, date and lab number.
- To maintain the room related records a separate register can be maintained which contains information regarding room number, room type and status.
- 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:
- 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.
- 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!
- If the file is operated by different people, it is difficult to work on files at same time. This leads to data inconsistency.
- Data mapping or linking is not possible in the file system between two files. New application or program required for new file.
- 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.
- 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:
|Banking||Customer, Accounts, Loans, Transaction etc.|
|Inventory Management||Products, Orders, Customers and Delivery|
|Organize Resources||Employees, Payroll, department, branches|
|Online Shopping||Item inventory, Users, customers, vendors|
|Education||Institute, Students, Parents|
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 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
The attributes are columns or fields of a table. Columns contain characteristics or parameters for which data are to be stored in the table.
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.
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.
The total number of columns in a table is known as a degree.
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:
- 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.
- Alternate Key: After assigning the candidate key and primary key the remaining attributes of the relation is alternate key.
- Primary Key: The primary key is the column which can be used to identify the reacords uniquely.
- Composite Primary Key: When more than one attribute is assigned as primary key, is known as composite primary key.
- 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.
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
- To work on database
In the above example, the employee is the database name.
The following points need to be kept in mind while using SQL:
- Every command in MySQL ends with a semicolon(;).
- SQL is case insensitive language.
- You can write multiple lines in a single command. Press enter to start a new while writing command.
- 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!
|char(n)||Used to specify the fixed-length characters. n refers to a number of characters from 0 to 255. Fixed-length means declaring 10 characters consume 10 characters space in the column.|
|varchar(n)||Used to specify the variable-length character type of data from 0 to 65535. Variable-length means declaring 10 characters, can store 10 characters but the actual allocated characters will be retained in the column. It needs the space according to the value entered in the column.|
|int||Used to specify the integer value without decimal places. It occupies 4 bytes ranging from 0 to 4,294,967,295. For larger values, BIGINT can be used, which occupies 8 bytes.|
|float||Used to specify the numbers with decimal points. It occupies 4 bytes.|
|date||Used to store the date in ‘YYYY/MM/DD’ format, where YYYY is 4 digit year, MM is 2 digit month and DD is 2 digit date. The dates are ranging from ‘1000-01-01’ to ‘9999-12-31’.|
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:
|Primary Key||Used to identify the rows uniquely. It cannot be null or duplicated.|
|Foreign Key||The column which refers to a value of an attribute is defined as a primary key in another table.|
|Not Null||Ensures that a column cannot have NULL values. NULL refers to missing/unknown/not applicable value.|
|Unique||Ensures that the values in a column are distinct/unique|
|Default||It refers to the value specified for the column when|
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:
- Creating tables and other database objects
- Defining schemas
- Defining and Givning names to the constraints
- Defining the datatype for each attribute
- Modifying relation schemas
- Deleting relation schemas
Some of the DDL commands are following:
Create Database Command
The create database command is used to create a database. The syntax is:
create database <databasename>
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:
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.
|Field Name||Data Type||Constraints|
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:
- Column level
- Table level
- Alter Table
- 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));
- 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.
- 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:
- The table with primary key must be created for reference.
- 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);
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;
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;
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>;
alter table patient modify gender char(2) default 'M';
alter table <tablename> drop column;
alter table patient drop gender;
Remove primary key
alter table <tablename> drop primary key;
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.
- 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.
The insert command has the following syntax:
insert into <tablename> values (value1,....,valuen);
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:
- 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>;
update patient set dateofadm='2021-12-02' where pid=1; update patient set dateofadm='2021-12-25',charges=450, gender='F' where pid=2;
- The delete command deletes the sepcified rows from the table
- The syntax for delete command is as follows:
delete from <tablename> where <conditon>
- 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:
- All rows all columns
- All rows limited columns
- Limited rows all columns
- 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>
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:
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;
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:
 Display names starting with a specific character ‘d’
select * from patient where pname like 'd%';
 Display records of patients whose names ends with ‘a’
select * from patient where pname like '%a';
 Display records of patients whose second letter is ‘a’
select * from patient where pname like '_a%';
 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:
Watch this video for more understanding:
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;