Maharashtra Board Class 11 Information Technology Set 6 DBMS PostgreSQL Solutions

SOP 1: Create a database, using Postgres SQL named hospital. In this database, create a table of patients with the following fields Patient_ID, Patients_Name, Address, Room_number and Doctor’s_name.
Answer:
1. Command to create the database:
CREATE DATABASE hospital;

2. Command to connect to the database:
\c hospital;

3. Command to create the table:
CREATE TABLE patients (
    Patient_ID INT PRIMARY KEY,
    Patients_Name VARCHAR(100),
    Address VARCHAR(255),
    Room_number INT,
    Doctor_name VARCHAR(100)
);
In simple words: First, we create a database named hospital. Then, we create a table inside it called patients to store details like patient ID, name, address, room number, and doctor's name.

🎯 Exam Tip: Always define a primary key (like Patient_ID) when creating a table to uniquely identify each record and ensure data integrity.

Question 1. Give appropriate data type for each field.

Answer:
Step 1: Open Command Terminal. Switch over to the Postgres account on your server by typing.
$ sudo -i -u Postgres

Step 2: You can now access a Postgres prompt immediately by typing.
$ psql

Step 3: To create a database hospital;
create database hospital;

Step 4: Connect to Database using \c
\c hospital;

Step 5: Create a table in the database. Create Table Command is used. This ensures that each field is allocated the correct storage format in the database.
create table patients(patients_Id Integer,patients_name text,Address text,Room_number integer,Doctor_name text);

Step 6: Let’s see the result of the patient’s table.
select * from patients;
or
\d patients;
In simple words: To store patient details, we first log into the database system and create a database named 'hospital'. Then, we create a table where we define the type of data each column can hold, like numbers for IDs and text for names.

🎯 Exam Tip: Always match the data type to the nature of the data; for example, use Integer for IDs and Text/Varchar for names to prevent database errors.

SOP 2. Create a database using PostgreSQL named Schoolmaster.
• In this database create a table of students with the following fields: student_ID, student_name, Address, Phone_number, Date_of_Birth.
• Give appropriate data types for each field. Enter at least 5 records.
Answer:
Step 1: Create a database Schoolmaster.
create database school_master;

Step 2: Now to connect to the database use \c Command.
\c school_master;

Step 3: Create a table of students with the following fields. Give appropriate data type for each field.
Fields: student_ID, student_name, Address, Phone_number, Date_of_Birth.
create table students(student_ID integer, student_name text, Address text, Phone_number bigint, Date_of_Birth date);

Step 4: Enter at least 5 records.
Insert into students values(001, 'ZAHRA LALANI', 'MAZGAON', 123456789, '20-08-2000');
Insert into students values(002, 'MUHAMMAD LALANI', 'BYCULLA', 987654210, '30-01-2000');
Insert into students values(003, 'AARAV SHARMA', 'BANDRA', 981234567, '15-05-2001');
Insert into students values(004, 'SANYA IYER', 'ANDHERI', 982345678, '12-11-2000');
Insert into students values(005, 'ROHAN MEHTA', 'DADAR', 983456789, '05-02-2001');
In simple words: This practical experiment teaches you how to set up a database for a school, create a table to store student details, and add five sample student records into it.

🎯 Exam Tip: Make sure to use the correct date format (YYYY-MM-DD or DD-MM-YYYY as configured) and wrap text values in single quotes when inserting records.

Insert into students values(003, 'KUNAL KAPOOR', 'WALKESHWAR', 987224210, '15-07-2000');
Insert into students values(004, 'AKSHAY SINGH', 'CHARNI ROAD', 937224210, '19-06-2000');
Insert into students values(005, 'RUKHSHAR BANU', 'DIWANPARA', 937226210, '18-08-2000');

Step 5: Show all records using select command
select * from students;

Question. SOP 3: Given the list of fields: Empld, EmpName, EmpDepartment, Salaryld, Salary Amount, Bonus in the tables Employee and Salary respectively. Define primary key, foreign key and segregate for above fields into employee and salary table. Also create a one-to-one relationship between Employee and Salary Table.
Answer:
Step 1: Create a school database
Create database school;
In simple words: To solve this practical, we start by creating the database, and then we will organize the employee and salary details into separate tables linked by a common key.

🎯 Exam Tip: When writing SQL commands for database creation, ensure you end each statement with a semicolon to avoid syntax errors.

Question 1. Create a database named school. In this database, create two tables Employee and Salary with the following fields: EmpId, EmpName, EmpDepartment, SalaryId, SalaryAmount, Bonus. Define primary key, foreign key and segregate these fields into the employee and salary tables.
Answer:
Step 1: Create database
CREATE DATABASE school;

Step 2: Connect to database
\c school;

Step 3: Create the tables with Primary and Foreign keys
Create table salary:
CREATE TABLE salary(salaryId Integer PRIMARY KEY, SalaryAmount integer, Bonus integer);

Create table employee:
CREATE TABLE employee(EmpId integer PRIMARY KEY, EmpName text, EmpDept text, SalaryId integer, FOREIGN KEY(salaryId) REFERENCES Salary(salaryId));

Step 4: View both tables to verify creation
SELECT * FROM salary;
SELECT * FROM employee;
In simple words: First, we create a database called school and connect to it. Then, we create the 'salary' table first, and then the 'employee' table, linking them together using 'salaryId' as a foreign key so that each employee's record is connected to their correct salary details.

🎯 Exam Tip: When creating tables with relationships, always define the table containing the Primary Key (parent table) first, before creating the table with the Foreign Key (child table) to avoid referential integrity errors.