Skill Set 6 – DBMS (PostgreSQL)Class 11 Information Technology Chapter 0 Notes – Maharashtra Board

Textbook: Information Technology – Class 11 (Balbharati, official free PDF) · pages 113–134

Time to run real SQL. This skill set uses PostgreSQL, a free and powerful database server, through its psql shell: create, list, connect to and drop databases; create tables; insert, view and update rows; add primary and foreign keys; and link two tables one-to-one. The page works through all three practicals (hospital, school and employee–salary databases) with tested-style SQL, and also covers the book's appendix — installing Ubuntu, LibreOffice, PostgreSQL and GNUKhata.

🎧 Listen to the summary

Summary: Skill Set 6 – DBMS (PostgreSQL)

PostgreSQL is a free, open-source relational database system. On Ubuntu it is installed with sudo apt-get update and sudo apt-get install postgresql postgresql-contrib; you then switch to the default postgres user (sudo -i -u postgres) and start the psql shell by typing psql. The prompt shows the current database, e.g. postgres=# or college=#.

Database commands: CREATE DATABASE name; makes a database, \l lists all databases, \c name connects to one, \dt lists its tables, \d table shows a table's structure, and DROP DATABASE name; deletes a database (you must first connect to a different one). Backslash commands are psql shortcuts and need no semicolon; SQL statements end with a semicolon.

Table commands: CREATE TABLE with column names and data types (integer, text, varchar, date, numeric); INSERT INTO … VALUES … adds rows; SELECT * FROM table; shows them; UPDATE … SET … WHERE … changes them; DROP TABLE name; deletes the table.

Keys and relationships: ALTER TABLE t ADD PRIMARY KEY (col); or PRIMARY KEY in the column definition; ALTER TABLE t ADD FOREIGN KEY (col) REFERENCES other(col); or FOREIGN KEY … REFERENCES … while creating. In the book's example, Marks.roll_no is both the primary key of Marks and a foreign key to XI.roll_no, so each student has at most one marks row — a one-to-one relationship — and a join shows both tables' data together.

Practicals: SOP 1 — hospital database with a patients table; SOP 2 — school database with a students table and 5 records; SOP 3 — split employee fields into Employee and Salary tables with primary keys, a foreign key and a one-to-one link. The appendix explains making an Ubuntu installer (DVD, Rufus, Etcher or dd), installing Ubuntu, LibreOffice, PostgreSQL and GNUKhata, plus minimum hardware.

Key points to remember

  • Start: sudo -i -u postgres, then psql; quit with \q
  • CREATE DATABASE hospital; then \c hospital to work inside it
  • \l lists databases, \dt lists tables, \d name shows a table's structure
  • SQL statements end with ; — backslash commands do not need it
  • Text and dates go in single quotes: 'Sachin', '2009-03-14'
  • Names with hyphens or capitals need double quotes — easier to use school_master
  • UPDATE and DELETE without WHERE change every row
  • Primary key: unique + not null; foreign key: must match the other table's key
  • One-to-one: the foreign key column is also unique (or is the primary key)
  • DROP TABLE t; deletes a table; DROP DATABASE d; needs you to be connected elsewhere
Want a teacher to explain Skill Set 6 – DBMS (PostgreSQL)?

Get a ✓ Verified tutor for “Skill Set 6 – DBMS (PostgreSQL)” — live online or at home. We aim to suggest up to 3 tutors within 24 hours.

Get a tutor for this chapter →

👩‍🏫 Class 11 Information Technology classes — live online, request a call back

Prefer a real teacher? TalentJR teachers take live online classes anywhere in India. Tell us what you need and our team will call you with timings and fees.

Live online classesSmall groups or 1:1 with a teacher, from home, any city.
We call you backOur team asks what your child needs, then shares timings and fees.
Home tutor?We are still building our list of home tutors — ask on the call if there is one near you.

Keep learning