database-objects.md

Object type	Page
Tag	dbmslab
Backlinks	dbms-setup.md dbms-lab.md cluster-configuration.md
Creation date	2026-03-03T14:00:17Z
Created by	Wild Iron
Links	dbms-setup.md cluster-configuration.md
Emoji	🪣
Cover image or color	sky
id	bafyreiafrke4qjvexx3c6bodkhvbzc4qnxzj4arfzfpdjfxxfv74fabr6a

Database Objects

Database Objects

Tables, Constraints & Defaults

• create table 'person' having colums thet describe a person's attributes: first name, last name, shoe size and ID

• add the following table constraints:

  • a primary key
  • a "not null" constraint for first name and last name
  • a default for shoe size

• add members of the muppet show to the person table:

  • Elmo, Statler and Waldorf, Kermit the Frog, Miss Piggy, Rowlf, Beaker and your favorite Muppets.

• create a second table called department with the following layout:

  colum name	data type	constraints / default
  id	int	primary key /, not null
  dept_name	varchar	null / null
  location	varchar	not null / "muppez theatre"
  member_id	int	null / null

• create a foreign key constraint: column member_id of table department references column id of table person.

• add some meaningful data to table department (all at the Muppet theater location):

  • artists
  • administration
  • guests
  • and add members to each of them using the member_id of records in the person table

Views & Triggers

• create a view querying the first name, last name & the calculated value "shoe size/1.6" (which is approximately the shoe size in centimeter) of records in table person.
• add column entry_date (data type: date, null allowed, default: 1970-01-01) to table person.
• create a trigger on table person that updates the value of column entry_date to the current date when inserting a new row.
• add person Dr. Bunsen to department Muppet Labs.

Joins

• create a script joins.sql that contains the following SQL statements:

CREATE TABLE occurrences (
    id      INTEGER PRIMARY KEY,
    date    DATE NOT NULL,
    number  INTEGER NOT NULL
);

INSERT INTO occurrences (id, date, number)
SELECT
g.id,
(
    DATE '1970-01-01' + (floor(random() * (DATE '1988-12-31' - DATE '1970-01-01'))) * INTERVAL '1 day'
)::date AS date,
floor(random())::int AS number
FROM generate_series(1, 100) AS g(id);

• execute the SQL script within DB labdb using psql.
• display the surname and number of occurrences aka "total" of muppets that have more than 10 occurences per show.

← Previous
Next →
DBMS Setup
Cluster Configuration