Turn the prompt into the right database answer.

Write this — the exam answer

Draw it. If you also want words beside the drawing, this is all that is needed:

Write this — the exam answer

Then the DDL, one table per relation, parents before children. This version uses the short, common PostgreSQL spelling varchar and puts each one-column foreign key directly on its column:

CREATE TABLE hm_hotel (
  ho_id integer PRIMARY KEY,
  ho_name varchar(255) NOT NULL,
  ho_street varchar(255) NOT NULL,
  ho_house_number varchar(50) NOT NULL,
  ho_zip_code varchar(50) NOT NULL,
  ho_city varchar(255) NOT NULL
);

CREATE TABLE hm_type (
  ty_id integer PRIMARY KEY,
  ty_name varchar(255) NOT NULL UNIQUE,
  ty_description varchar(255),
  ty_price numeric(10,2) NOT NULL CHECK (ty_price >= 0)
);

CREATE TABLE hm_room (
  ro_id integer PRIMARY KEY,
  ro_hotel integer NOT NULL REFERENCES hm_hotel(ho_id),
  ro_type integer NOT NULL REFERENCES hm_type(ty_id),
  ro_living_area numeric(10,2) NOT NULL CHECK (ro_living_area > 0)
);

CREATE TABLE hm_guest (
  gu_id integer PRIMARY KEY,
  gu_name varchar(255) NOT NULL,
  gu_street varchar(255) NOT NULL,
  gu_house_number varchar(50) NOT NULL,
  gu_zip_code varchar(50) NOT NULL,
  gu_city varchar(255) NOT NULL
);

CREATE TABLE hm_booking (
  bo_room integer REFERENCES hm_room(ro_id),
  bo_guest integer REFERENCES hm_guest(gu_id),
  bo_start_date date,
  bo_end_date date NOT NULL,
  PRIMARY KEY (bo_room, bo_guest, bo_start_date),
  CHECK (bo_end_date > bo_start_date)
);

Write this — the exam answer

State the semantic FDs and candidate keys first. We infer dependencies from the prompt, not from accidental repetition in the twelve sample rows.

Final verdict: the corrected relational model is in BCNF (therefore also 3NF, 2NF, and 1NF) under the stated FDs.

Write this — the complete paper answer

Assumption: the current PostgreSQL connection is to database lecture.

-- current database: lecture
CREATE SCHEMA invoiceschema;

CREATE TABLE invoiceschema.customer (
  cu_id integer PRIMARY KEY,
  cu_firstname varchar(255) NOT NULL,
  cu_lastname varchar(255) NOT NULL,
  cu_city varchar(255) NOT NULL
);

CREATE TABLE invoiceschema.invoice (
  in_id integer PRIMARY KEY,
  in_customer integer NOT NULL REFERENCES invoiceschema.customer(cu_id),
  in_items integer NOT NULL CHECK (in_items > 0),
  in_date date NOT NULL,
  in_amount numeric(10,2) NOT NULL CHECK (in_amount >= 0)
);

If Task 1.1 treated the full customer name as one atomic display value, replace the first-name and last-name columns with cu_name varchar(255) NOT NULL. The schema commands do not change.

Write this — the exam answer

Classroom working example · build every stage

The original answer above is already correct. The sequence below expands its decomposition into the 1NF → 2NF → 3NF → BCNF working used in class.

Final BCNF design: STUDENT(StudentID, Name, Gender) and STUDENT_UNIT(StudentID, Unit, Grade).

Write this — the exam answer

Classroom working example · build every stage

The original answer above is the concise solution. The sequence below shows how to arrive at those final relations one normal form at a time.

Final BCNF design: COUNTRY, CITY, and CITY_ATTRACTION as shown above.

Write this — the exam answer

Worked BCNF example · optional extension

Task 3.2 asks only for the BCNF verdict. The cards below keep that original answer intact and show one independently checked lossless BCNF attempt as extra practice.

Exam stopping point: “Not in BCNF because A → B and A is not a superkey.”

Write this — the exam answer

Write this — the exam answers

Write this — model answers

Exam answer

HOTEL 1:N ROOM; ROOM N:1 TYPE; GUEST M:N ROOM through BOOKS(start_date, end_date). HOTEL and GUEST each have a composite address. Keys: hotel_id, room_id, type_id, guest_id.

Exam answer

CUSTOMER 1:N CAR; CAR 1:N ACCIDENT (0 allowed on Accident side); POLICY M:N CAR; POLICY 1:N PREMIUM_PAYMENT. PAYMENT has period_start, period_end, due_date, received_date. Use IDs for all entities and VIN as a candidate key for CAR.

Exam answer

Primary key of BOOKING = (room_id, guest_id, start_date).

Exam answer

CREATE TABLE hm_hotel (
  ho_id integer PRIMARY KEY,
  ho_name varchar(255) NOT NULL,
  ho_street varchar(255) NOT NULL,
  ho_house_number varchar(50) NOT NULL,
  ho_zip_code varchar(50) NOT NULL,
  ho_city varchar(255) NOT NULL
);

CREATE TABLE hm_type (
  ty_id integer PRIMARY KEY,
  ty_name varchar(255) NOT NULL UNIQUE,
  ty_description varchar(255),
  ty_price numeric(10,2) NOT NULL CHECK (ty_price >= 0)
);

CREATE TABLE hm_room (
  ro_id integer PRIMARY KEY,
  ro_hotel integer NOT NULL REFERENCES hm_hotel(ho_id),
  ro_type integer NOT NULL REFERENCES hm_type(ty_id),
  ro_living_area numeric(10,2) NOT NULL CHECK (ro_living_area > 0)
);

CREATE TABLE hm_guest (
  gu_id integer PRIMARY KEY,
  gu_name varchar(255) NOT NULL,
  gu_street varchar(255) NOT NULL,
  gu_house_number varchar(50) NOT NULL,
  gu_zip_code varchar(50) NOT NULL,
  gu_city varchar(255) NOT NULL
);

CREATE TABLE hm_booking (
  bo_room integer REFERENCES hm_room(ro_id),
  bo_guest integer REFERENCES hm_guest(gu_id),
  bo_start_date date NOT NULL,
  bo_end_date date NOT NULL,
  PRIMARY KEY (bo_room, bo_guest, bo_start_date),
  CHECK (bo_end_date > bo_start_date)
);

INSERT INTO hm_hotel VALUES
(1,'Grand Hotel','Am Hauptplatz','1','84567','Pleinting'),
(2,'Beach Hotel','Strandvej','15b','1234','Copenhagen'),
(3,'Bates Motel','Park Avenue','59','01624','Fairvale');

INSERT INTO hm_type VALUES
(1,'Single Apartment','Nice apartment with bathroom and bed',130),
(2,'King Size Apartment','Nice apartment with king size bed and bathroom',260),
(3,'Mini Suite','Luxury apartment with king size bed, living room and bathroom',320),
(4,'Presidential Suite','Luxury apartment with king size bed, living room, bathroom and sauna',610);

INSERT INTO hm_room VALUES
(1,1,1,28),(2,1,3,64),(3,1,1,32),(4,2,2,44),(5,2,1,31),
(6,2,2,44),(7,2,4,110),(8,3,1,34),(9,3,1,32),(10,3,1,36);

INSERT INTO hm_guest VALUES
(1,'Susanne Brandt','Saskia-Geyer-Gasse','25','53672','Stuhr'),
(2,'Lorin Lee','Daniel-Geiger-Weg','3','82827','Biberach'),
(3,'Rubie Kub','Magdalena-Bühler-Allee','1','70119','Esslingen'),
(4,'Paul Brunel','Kieferweg','6','06145','Aschersleben'),
(5,'Mika Aikonen','Förstergasse','3','88888','Hinterwinkl'),
(6,'Antonia Valetta','Am Kai','12','28793','Radelshaven'),
(7,'Illes Mihaly','Oswald-Römer-Strasse','85','48732','Alfhausen'),
(8,'Renuga Sangalimuthu','Innerer Ring','119','10274','Berlin'),
(9,'John Smith','Am Rande','15','87934','Bofenhausen'),
(10,'Dominica Valderama','Falkenweg','9','58691','Mainburg');

INSERT INTO hm_booking VALUES
(3,1,'2024-06-03','2024-06-05'),(9,2,'2024-06-04','2024-06-09'),
(6,3,'2024-06-08','2024-06-21'),(2,1,'2024-06-09','2024-06-10'),
(4,4,'2024-06-09','2024-06-12'),(2,5,'2024-06-10','2024-06-15'),
(7,6,'2024-06-11','2024-06-18'),(3,7,'2024-06-11','2024-06-19'),
(8,8,'2024-06-13','2024-06-17'),(1,4,'2024-06-14','2024-06-17'),
(10,9,'2024-06-15','2024-06-20'),(2,10,'2024-06-15','2024-06-21');

Exam answer

R(A,B,C,D,E), F = { A→C, E→D, B→C }

Attributes absent from every RHS are A, B, E, so every key must contain ABE.

(ABE)⁺ = ABE → add C using A→C (or B→C) → add D using E→D = ABCDE.

Candidate key = {A,B,E}. R is not BCNF because A, B, and E are not superkeys although each determines a non-trivial attribute.

Exam answer

After splitting repeating groups, use SCHOOL(sid, name, class, leader, aid, activity, hours).

FDs: sid→name,class; class→leader; aid→activity; (sid,aid)→hours.

(sid,aid)⁺ = sid,aid,name,class,leader,activity,hours, so (sid,aid) is the candidate key.

Each resulting determinant is a key of its relation, so the decomposition is BCNF under the stated FDs.

Classroom working example · build every stage

The original final relations above remain the answer. This example exposes the intermediate 1NF, 2NF, and 3NF schemas.

Exam answer

customer_id→customer attributes; order_id→date, payment_id, customer_id; payment_id→card_number,payment_amount; item_id→name,unit_price; (order_id,item_id)→quantity.

Candidate key of the original 1NF relation: (order_id, item_id).

ALTER TABLE ec_order RENAME TO ec_order_old;

CREATE TABLE ec_customer (
  customer_id integer PRIMARY KEY, gender varchar(6),
  firstname varchar(50), lastname varchar(50), street varchar(75),
  house_number varchar(5), zip_code char(5), city varchar(75)
);
CREATE TABLE ec_order (
  order_id integer PRIMARY KEY, order_date date,
  customer_id integer NOT NULL REFERENCES ec_customer(customer_id)
);
CREATE TABLE ec_payment (
  payment_id bigint PRIMARY KEY, card_number varchar(20),
  payment_amount numeric(10,2), order_id integer NOT NULL UNIQUE
  REFERENCES ec_order(order_id)
);
CREATE TABLE ec_item (
  item_id integer PRIMARY KEY, item_name varchar(100),
  unit_price numeric(10,2)
);
CREATE TABLE ec_order_item (
  order_id integer REFERENCES ec_order(order_id),
  item_id integer REFERENCES ec_item(item_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id,item_id)
);

INSERT INTO ec_customer
SELECT DISTINCT or_customer_id,or_gender,or_firstname,or_lastname,
  or_street,or_house_number,or_zip_code,or_city FROM ec_order_old;
INSERT INTO ec_order
SELECT DISTINCT or_order_id,or_date,or_customer_id FROM ec_order_old;
INSERT INTO ec_payment
SELECT DISTINCT or_payment_id,or_card_number,or_payment_amount,or_order_id
FROM ec_order_old;
INSERT INTO ec_item
SELECT DISTINCT or_item_id,or_name,or_unit_price FROM ec_order_old;
INSERT INTO ec_order_item
SELECT or_order_id,or_item_id,or_quantity FROM ec_order_old;

-- Verified source range is 8.56 … 988.01, so this loses no rows.
ALTER TABLE ec_item ADD CONSTRAINT unit_price_bounds
  CHECK (unit_price BETWEEN 0 AND 10000);

DROP TABLE ec_order_old;

Classroom working example · build every stage

The FDs, candidate key, final five-table DDL, and data-copy statements above remain the original answer. This example shows the intermediate normal-form schemas.

Exam answer

C and E never appear on a RHS, so every key contains CE. Closures show ACE, BCE, and CDE each determine all attributes; removing any attribute from each fails.

Candidate keys: ACE, BCE, CDE. Not BCNF: A→B has determinant A, which is not a superkey (and other violations can be shown similarly).

Exam answers

1.1 Economics students

Miriam Seifert; Irina Romanova; Ramesh Venkatesh; Michele Bartoli; Rune Tronne; Gal Geva; Randy Rust.

1.2 Professors in rooms with two places

Anne Smith; Bettina Krause; Konstantin Pfeiffer.

1.3 Courses on Wednesday

Operations Research; Programming Languages.

1.4 Students who passed both Operations Research and Information Systems

Irina Romanova.

1.5 Students who attend no courses

Ramesh Venkatesh.

1.6 Failed exams in WT2023

Exam answer

σ_{Name='Konstantin Pfeiffer'}(Professor) → professor #004. Project Professor# → {004}. Join with TakesPlace on Professor# → lecture 002 WT2023 and lecture 003 WT2023.

The final projection removes all other columns; these two tuples remain.

Exam answers

-- 5.1.1 rooms below €300
SELECT r.ro_id
FROM hm_room r JOIN hm_type t ON t.ty_id=r.ro_type
WHERE t.ty_price < 300
ORDER BY r.ro_id;
-- → 1, 3, 4, 5, 6, 8, 9, 10

-- 5.1.2 area between 20 and 50, descending
SELECT r.ro_id, t.ty_name
FROM hm_room r JOIN hm_type t ON t.ty_id=r.ro_type
WHERE r.ro_living_area BETWEEN 20 AND 50
ORDER BY r.ro_id DESC;
-- → 10, 9, 8, 6, 5, 4, 3, 1 with their type names

-- 5.2.1 hotels offering a King Size Apartment
SELECT DISTINCT h.ho_name
FROM hm_hotel h
JOIN hm_room r ON r.ro_hotel=h.ho_id
JOIN hm_type t ON t.ty_id=r.ro_type
WHERE t.ty_name='King Size Apartment';
-- → Beach Hotel

-- 5.2.2 all hotels and number of bookings
SELECT h.ho_name, COUNT(b.bo_room) AS visits
FROM hm_hotel h
LEFT JOIN hm_room r ON r.ro_hotel=h.ho_id
LEFT JOIN hm_booking b ON b.bo_room=r.ro_id
GROUP BY h.ho_id,h.ho_name
ORDER BY h.ho_id;
-- → Grand Hotel 6; Beach Hotel 3; Bates Motel 3

-- 5.3.1 guests who ever booked Grand Hotel
SELECT DISTINCT g.gu_name
FROM hm_guest g
JOIN hm_booking b ON b.bo_guest=g.gu_id
JOIN hm_room r ON r.ro_id=b.bo_room
JOIN hm_hotel h ON h.ho_id=r.ro_hotel
WHERE h.ho_name='Grand Hotel';
-- → Susanne Brandt, Paul Brunel, Mika Aikonen, Illes Mihaly, Dominica Valderama

-- 5.3.2 guests spending more than €800
SELECT g.gu_name,
       SUM((b.bo_end_date-b.bo_start_date)*t.ty_price) AS spent
FROM hm_guest g
JOIN hm_booking b ON b.bo_guest=g.gu_id
JOIN hm_room r ON r.ro_id=b.bo_room
JOIN hm_type t ON t.ty_id=r.ro_type
GROUP BY g.gu_id,g.gu_name
HAVING SUM((b.bo_end_date-b.bo_start_date)*t.ty_price) > 800
ORDER BY g.gu_name;

Exam answers

-- 4.1.1 all customer names
SELECT firstname,lastname FROM ec_customer;

-- 4.1.2 customers with any order total > €5,000
SELECT c.firstname,c.lastname
FROM ec_customer c
WHERE EXISTS (
  SELECT 1 FROM ec_order o
  JOIN ec_order_item oi ON oi.order_id=o.order_id
  JOIN ec_item i ON i.item_id=oi.item_id
  WHERE o.customer_id=c.customer_id
  GROUP BY o.order_id
  HAVING SUM(oi.quantity*i.unit_price)>5000
);
-- → Khairiya al-Mustafa €5,282.34; Awni el-Huq €6,488.12;
--   Analisa Hogeda €5,169.99

-- 4.1.3 count customers who never bought a name beginning A
SELECT COUNT(*) FROM ec_customer c
WHERE NOT EXISTS (
  SELECT 1 FROM ec_order o
  JOIN ec_order_item oi ON oi.order_id=o.order_id
  JOIN ec_item i ON i.item_id=oi.item_id
  WHERE o.customer_id=c.customer_id AND i.item_name LIKE 'A%'
);
-- → 204

-- 4.2.1 orders newest first
SELECT * FROM ec_order ORDER BY order_date DESC;

-- 4.2.2 revenue by gender
SELECT c.gender,SUM(oi.quantity*i.unit_price) AS revenue
FROM ec_customer c JOIN ec_order o USING(customer_id)
JOIN ec_order_item oi USING(order_id)
JOIN ec_item i USING(item_id)
GROUP BY c.gender;
-- → female €241,582.99; male €277,355.37

-- 4.3.1 revenue per item
SELECT i.item_name,SUM(oi.quantity*i.unit_price) AS revenue
FROM ec_item i JOIN ec_order_item oi USING(item_id)
GROUP BY i.item_id,i.item_name;

-- 4.3.2 items with total revenue below €12,000
SELECT i.item_name,SUM(oi.quantity*i.unit_price) AS revenue
FROM ec_item i JOIN ec_order_item oi USING(item_id)
GROUP BY i.item_id,i.item_name
HAVING SUM(oi.quantity*i.unit_price)<12000;
-- → 70 items

-- 4.3.3 products ordered by customers in a city ending “burg”
SELECT DISTINCT i.item_name
FROM ec_customer c JOIN ec_order o USING(customer_id)
JOIN ec_order_item oi USING(order_id)
JOIN ec_item i USING(item_id)
WHERE c.city LIKE '%burg';
-- → 28 distinct product names

Exam answers

INSERT INTO aircraft(aircraft_code,model,range)
VALUES('NEW','Airbus A400',10000);

UPDATE flight
SET actual_arrival=actual_arrival+INTERVAL '45 minutes',
    status='Delayed'
WHERE flight_id=5;

-- models with range > 5,000
SELECT model FROM aircraft WHERE range>5000;
-- Airbus A330-200; Airbus A380; Boeing 777-200LR

-- planned flights per model on 15 June
SELECT a.model,COUNT(*) AS flights
FROM flight f JOIN aircraft a USING(aircraft_code)
WHERE f.scheduled_departure::date=DATE '2024-06-15'
GROUP BY a.model;
-- A318 6; A330-200 3; A380 1; B757-300 3; B777-200LR 2

-- distinct models scheduled to arrive in Munich on 15 June
SELECT DISTINCT a.model
FROM flight f JOIN aircraft a USING(aircraft_code)
JOIN airport p ON p.airport_code=f.arrival_airport
WHERE p.city='Munich'
  AND f.scheduled_arrival::date=DATE '2024-06-15';
-- Airbus A318; Boeing 757-300; Boeing 777-200LR

-- distinct direct destinations from Madrid, sorted
SELECT DISTINCT arrival.city
FROM flight f
JOIN airport origin ON origin.airport_code=f.departure_airport
JOIN airport arrival ON arrival.airport_code=f.arrival_airport
WHERE origin.city='Madrid'
  AND f.scheduled_departure::date=DATE '2024-06-15'
ORDER BY arrival.city;
-- Chennai; Jakarta; Munich; Reykjavik

-- Munich → Reykjavik with exactly one feasible stop
SELECT f1.scheduled_departure,f2.scheduled_arrival,
       f2.scheduled_arrival-f1.scheduled_departure AS duration,
       stop.city AS stopover
FROM flight f1
JOIN airport origin ON origin.airport_code=f1.departure_airport
JOIN flight f2 ON f2.departure_airport=f1.arrival_airport
JOIN airport stop ON stop.airport_code=f1.arrival_airport
JOIN airport destination ON destination.airport_code=f2.arrival_airport
WHERE origin.city='Munich' AND destination.city='Reykjavik'
  AND f2.scheduled_departure>f1.scheduled_arrival
  AND f1.scheduled_departure::date=DATE '2024-06-15';
-- Helsinki: 04:30→13:20 (9:50), 04:30→18:25 (14:55),
--           11:45→18:25 (7:40), elapsed with time-zone offsets.

-- cities touched by no Boeing departure/arrival that day
SELECT p.city FROM airport p
WHERE NOT EXISTS (
  SELECT 1 FROM flight f JOIN aircraft a USING(aircraft_code)
  WHERE a.model LIKE 'Boeing%'
    AND (
      (f.departure_airport=p.airport_code AND f.scheduled_departure::date=DATE '2024-06-15')
      OR
      (f.arrival_airport=p.airport_code AND f.scheduled_arrival::date=DATE '2024-06-15')
    )
);
-- → Madrid

Exam answer

Excel can store tables and evaluate formulas, but it does not satisfy core relational-DBMS requirements: it lacks a DBMS-managed relational catalog and declarative integrity, complete set-level data language, transparent logical/physical data independence, transaction isolation/recovery, and centrally enforced authorization. A spreadsheet cell grid is therefore not an RDBMS merely because rows and columns resemble a relation.

Exam answer

A checkpoint flushes dirty data pages and writes a checkpoint record to WAL so crash recovery can begin from the corresponding redo point. PostgreSQL 18 starts a checkpoint when checkpoint_timeout expires or max_wal_size is about to be exceeded; current defaults are 5 minutes and 1 GB. A 1-second timeout would force dirty-page flushing far too often and, with full-page writes, increase WAL volume after every checkpoint, causing heavy I/O and latency.

Exam answer

8 million machines × at most 500 sensors = 4,000,000,000 sensor tables. One reading = 8-byte timestamp + 4×8-byte numeric = 40 bytes. At 100 Hz: 4,000 bytes/s per sensor.

Payload-only time = 32 TiB ÷ 4,000 B/s ≈ 278.7 years.

Page-cap cross-check: floor(8192/40)=204 payload readings/page; (2³²−1) pages × 204 ÷ 100 readings/s ≈ 277.6 years.

One table per sensor is not feasible in one PostgreSQL database: 4.0 billion relations exceeds the current 1,431,650,303 relations/database limit, before indexes and practical overhead. Use shared measurement tables keyed by machine_id and sensor_id, with partitioning. The payload-only lifetime is about 278 years; real tuple/page/index overhead makes the practical value lower.

Answers

1 Install and connect. Install PostgreSQL with pgAdmin, start the server, connect in pgAdmin to host localhost, port 5432, maintenance DB postgres; in psql use psql -U postgres -d postgres. Verify with SELECT version(); and SELECT current_database(),current_user;.

2 Name/history. PostgreSQL descends from the POSTGRES project at UC Berkeley, itself a successor to Ingres; “SQL” was added to the name as SQL support became central. Final line: POSTGRES + SQL → PostgreSQL.

3 Capacity. 8,000,000×500 = 4,000,000,000 tables, exceeding the current relations/database limit. Per sensor: 100×(8+4×8)=4,000 B/s; 32 TiB/4,000 B/s = 278.731 payload-only years. Page-only cross-check = 277.643 years; use shared/partitioned measurements, not one table/sensor.

Answers

1. Use one paper sheet per relation; stable IDs link sheets rather than copying names.

2.1. USER(user_id,nickname,first_name,last_name,birthday); CHAT(chat_id,title,created_at,created_by); CHAT_MEMBER(chat_id,user_id); MESSAGE(message_id,chat_id,author_id,reply_to,text,created_at). Create 6 User rows, 3 Chat rows, 9 membership rows (4+2+2 with one local user reused as appropriate), and 20 Message rows (10+5+5). Access a conversation by selecting MESSAGE rows with chat_id and sorting created_at; index message(chat_id,created_at).

2.2. Birthday belongs once in USER; lookup by user_id/nickname. Index nickname if it is a frequent unique search key.

2.3. Delete MESSAGE WHERE author_id=:u; USER remains, so birthday remains. With replies, use ON DELETE SET NULL for reply_to or delete replies in safe order.

2.4. Global latest: order all messages by created_at DESC, message_id DESC, take first. Per chat: ROW_NUMBER() OVER(PARTITION BY chat_id ORDER BY created_at DESC,message_id DESC)=1, or PostgreSQL DISTINCT ON (chat_id).

2.5. Update one CHAT row. 2.6. Update one USER row; messages reference the ID. Copying the name on every message would require many edits and cause inconsistency.

3.1. Point lookup by PK is fast; scanning message text or counting by unindexed surname prefix is slower. Index known access paths, but no single set of indexes optimizes every read and write: indexes cost storage and update work.

3.2. Concurrent edits need a transaction and a conflict policy. Lock the row briefly (SELECT … FOR UPDATE) or use optimistic version checking; keep transactions short. Last-writer-wins is fast but may lose an update.

3.3. Working memory holds only a small changing set; a DB buffer/cache holds hot pages while persistent storage retains the complete set. 3.4. Compute AVG(age) by scanning birthdays; an index usually cannot remove all aggregation work. A maintained summary can be faster but must be updated correctly; computing age from birthday avoids a stale stored age.

Answers

1. Excel lacks DBMS-managed relational catalog, comprehensive relational data language, declarative integrity/data independence, and transaction/concurrency/recovery services; therefore it is not an RDBMS.

2. A database page is the fixed-size unit the DBMS moves between disk and buffer pool. PostgreSQL commonly uses 8 KiB pages containing a header, item pointers, tuples, and free space. Pages amortize I/O and support addressing, caching, replacement, and recovery.

3. A checkpoint flushes dirty pages and records a WAL recovery point. PostgreSQL 18 defaults: 5 min timeout and 1 GB max WAL trigger. One second means frequent dirty-page writes and extra full-page WAL, harming throughput/latency.

Answers

-- 1 in psql
\l+             -- databases and sizes
\du             -- roles

-- 2.1–2.2 run as an administrative role
CREATE ROLE lecture LOGIN CREATEDB PASSWORD 'choose-a-password';
CREATE DATABASE lecture OWNER lecture;
-- reconnect to database lecture as lecture
CREATE SCHEMA messenger AUTHORIZATION lecture;
SET search_path TO messenger;

-- 2.3–2.7
CREATE TABLE me_user(
 us_id bigint PRIMARY KEY,us_nickname varchar(50),
 us_lastname varchar(50),us_firstname varchar(50),us_birthday date);
CREATE TABLE me_chat(
 ch_id bigint PRIMARY KEY,ch_title varchar(255),
 ch_created_at timestamp,ch_created_by bigint REFERENCES me_user);
CREATE TABLE me_chatuser(
 cu_userid bigint REFERENCES me_user,
 cu_chatid bigint REFERENCES me_chat,
 PRIMARY KEY(cu_userid,cu_chatid));
CREATE TABLE me_message(
 ms_id bigint PRIMARY KEY,
 ms_chat bigint REFERENCES me_chat,
 ms_refer bigint REFERENCES me_message(ms_id),
 ms_text text,ms_created_at timestamp,
 ms_created_by bigint REFERENCES me_user);

-- 3.2 after executing MessengerData.sql
SELECT * FROM messenger.me_message;
INSERT INTO messenger.me_message
 (ms_id,ms_chat,ms_refer,ms_text,ms_created_at,ms_created_by)
SELECT 11,c.ch_id,NULL,'I will be back.',CURRENT_TIMESTAMP,u.us_id
FROM messenger.me_chat c CROSS JOIN messenger.me_user u
WHERE c.ch_title='Saturday Night Fever' AND u.us_nickname='terminator';
\copy messenger.me_message TO 'messages.csv' CSV HEADER

2.8 integrity checks. Personal/group chats: structurally yes (membership count is not constrained). Delete a user’s messages but retain birthday: yes, delete Message rows only. Delete User yet retain their name in messages: no under the supplied schema/FK; use soft deletion/anonymized User row or an explicit author-name snapshot. Replies: yes through nullable self-FK ms_refer.

3.1. Execute MessengerData.sql in database lecture; verify counts: 5 users, 3 chats, 9 memberships, 10 messages before the new insert.

Answers

1. COMPANY(company_id,name,legal_form) 1:N DEPARTMENT(department_id,name); EMPLOYEE(employee_id,name,birthday) works for exactly one Department; Department has one head Employee; EMPLOYEE has a recursive 1:N BOSSES relation (one boss to many subordinates). COMPANY has a disjoint, total specialization into PRODUCER(production_capacity) and RETAILER(storage_capacity), because “either … or …” excludes both/neither.

2. Replace current-valued links with temporal relationships: WORKS_FOR(employee,department,start_date,end_date), HEADS(employee,department,start_date,end_date), BOSSES(boss,subordinate,start_date,end_date). Suggested keys: (employee_id,start_date) for works/head history when an employee can hold one such role at an instant; (boss_id,subordinate_id,start_date) for boss history. Add end>start and business rules for non-overlap if required.

3. Monst-ER Park is an external interactive practice activity and has no written solution artifact. Completion requires playing the site; it is safe to skip in this three-day exam route after the paper drills.

Answers

1 Insurance. CUSTOMER(customer_id,name) 1:N CAR(vin,model); CAR 1:N ACCIDENT(accident_id,date,…), with zero accidents allowed; POLICY(policy_id,…) M:N CAR; POLICY 1:N PREMIUM_PAYMENT(payment_id,period_start,period_end,due_date,received_date,amount). “One or more” means total participation on Customer→Car, Policy→Car, and Policy→Payment sides.

2 CARESE. LOCATION(location_id,address) 1:N VEHICLE(vehicle_id,brand,seats); VEHICLE M:N VEHICLE_TYPE(type_id,name). CUSTOMER supertype (customer_id,name,address,phone) has disjoint subtypes PRIVATE(birthday,credit_card) and BUSINESS(vat,discount_category). RESERVATION is a ternary event linking Customer, VehicleType, and Location with pickup_date and return_date; add reservation_id or a composite event key. New types are rows.

3 Requirements. Record Lecture ID/title/description/type; semester offerings; professor assignments; rooms, weekdays, and timeslots; student attendance; exams, examiner/location; each student’s grade; prerequisites. Clarify whether two professors may co-teach, one room hosts overlapping lectures, repeated attempts, and tutor limits.

4 Lecture ER. PROFESSOR 1:N TEACHES LECTURE per Semester; LECTURE M:N STUDENT through ATTENDS(Semester); LECTURE N:M ROOM through TAKES_PLACE(Semester,Weekday,Timeslot); EXAM belongs to Lecture and has student attempts/examiner/grade; LECTURE has recursive M:N PREREQUISITE. Use offering (lecture,semester) as the anchor when semester-specific facts repeat.

Answers

CREATE SCHEMA moviemanager;
CREATE TABLE moviemanager.movie(
 movie_id bigint PRIMARY KEY,title varchar(200) NOT NULL,
 publication_date date);
CREATE TABLE moviemanager.actor(
 actor_id bigint PRIMARY KEY,first_name varchar(80),
 last_name varchar(80),stage_name varchar(120),birthday date);
CREATE TABLE moviemanager.library(
 library_id bigint PRIMARY KEY,name varchar(120) NOT NULL,
 address text);
CREATE TABLE moviemanager.movie_actor(
 movie_id bigint REFERENCES moviemanager.movie,
 actor_id bigint REFERENCES moviemanager.actor,
 PRIMARY KEY(movie_id,actor_id));
CREATE TABLE moviemanager.library_movie(
 library_id bigint REFERENCES moviemanager.library,
 movie_id bigint REFERENCES moviemanager.movie,
 PRIMARY KEY(library_id,movie_id));
CREATE TABLE moviemanager.opening_hours(
 library_id bigint REFERENCES moviemanager.library,
 weekday smallint CHECK(weekday BETWEEN 1 AND 7),
 opens time,closes time,PRIMARY KEY(library_id,weekday),
 CHECK(closes>opens));

1.4 explanation. pgAdmin converts each M:N relation into a junction table because a foreign key on only one side can represent at most 1:N. 1.6. Generate SQL, inspect PK/FK targets and schema qualification, execute, then compare catalog tables to the model.

2. The supplied lecturemanager.sql implements Room, Professor, Student, Lecture, Attends, Teaches, Examines, TakesPlace, and Prerequisite with bridge keys. Import into schema lecturemanager; verify with \dt lecturemanager.* and catalog/ER view.

Answers

1. Union = {007 James Bond, 008 Hans-Dieter Kurz, 013 Carla Chester, 014 Jodie Leer, 021 Anna Koivonnen, 023 Wladimir Ivanov, 042 Simon Jones, 123 Tawfik Hamed}. Intersection = {007 James Bond, 042 Simon Jones}. Table1−Table2 = {013 Carla Chester, 014 Jodie Leer, 123 Tawfik Hamed}. (The reverse difference would be {008 Hans-Dieter Kurz, 021 Anna Koivonnen, 023 Wladimir Ivanov}.)

2. character(n)/char(n)/bpchar(n): fixed-length and blank-padded. character varying(n)/varchar(n): variable length with limit. text: variable unlimited length. PostgreSQL’s lengthless bpchar is variable unlimited length with trailing spaces semantically insignificant; lengthless character means character(1). For general strings PostgreSQL recommends text or varchar; see the official PostgreSQL 18 character-type documentation.

3.

Answers

1.1 Order database. CUSTOMER(customer_id,name); CATEGORY(category_id,name); PRODUCT(product_id,name,category_id FK); ORDERS(customer_id FK,product_id FK).

1.2 extension. Model CATEGORY 1:N recursive parent; PRODUCT 1:N VERSION; an ORDER event for customer/date/time and ORDER_LINE for version, quantity, and price. This permits the same customer/version at different times and simultaneous product versions.

1.3 refined model. CUSTOMER(customer_id,name); CATEGORY(category_id,name,parent_id FK→CATEGORY nullable); PRODUCT(product_id,name,category_id FK); VERSION(version_id,product_id FK,description); CUSTOMER_ORDER(order_id,customer_id FK,ordered_at); ORDER_LINE(order_id FK,version_id FK,quantity,unit_price). Check quantity>0 and unit_price≥0.

2.1 Lecture model. PROFESSOR(professor_id,name,expertise,room_id FK); ROOM(room_id,places); STUDENT(student_id,name,course_of_study); LECTURE(lecture_id,title,type,description); ATTENDS(lecture_id,student_id,semester); TEACHES(lecture_id,semester,professor_id); EXAMINES(lecture_id,student_id,semester,professor_id,grade,location); TAKES_PLACE(lecture_id,semester,room_id,weekday,timeslot); PREREQUISITE(lecture_id,prerequisite_id).

2.2 evaluation. Multiple courses/student: yes. Duplicate student names: yes (ID is key). One professor in a room: possible. Three professors in one room: possible (room FK is not UNIQUE). Same lecture in different semesters: yes because semester is in Attends key. Two professors teaching same lecture/semester: no under TEACHES PK(lecture,semester). A professor can sit in a room where another teaches: yes; no constraint links office occupancy to teaching.

3.1 tutors. TUTOR(student_id PK/FK→STUDENT); SUPPORTS(lecture_id,semester,student_id FK→TUTOR). PK(lecture_id,semester) enforces at most one tutor.

3.2 books. BOOK(book_id,title,publisher); AUTHOR(author_id,name); BOOK_AUTHOR(book_id,author_id); RECOMMENDED(lecture_id,book_id). 3.3 each M:N becomes the two-FK bridge just listed.

Answers

Homework 05 1.1–1.2 and 2.1–2.2: expressions and result names are in Chapter 4 under “Homework 05.” Natural vs theta join and unary/binary definitions are stated there.

Learning Lab 06 1.1–1.6 and 2: all expressions, result rows, and the operation-tree trace are in Chapter 4.

Learning Lab 06 3.1 IMDB.

3.2 reformulation. Push selections genre='Drama'∧prob≥0.5, actor name, and genre='Thriller' to their base relations before joins; project to required IDs/labels only after retaining join keys. This reduces intermediate tuples without changing set semantics.

Answers

1.1–1.3. Full FDs, key, normal-form analysis, and BCNF decomposition are in Chapter 3. 1.4 SQL:

CREATE SCHEMA school;
CREATE TABLE school.class(
 class_id varchar(3) PRIMARY KEY,leader varchar(100) NOT NULL);
CREATE TABLE school.student(
 sid integer PRIMARY KEY,name varchar(100) NOT NULL,
 class_id varchar(3) NOT NULL REFERENCES school.class);
CREATE TABLE school.activity(
 aid integer PRIMARY KEY,activity varchar(80) NOT NULL UNIQUE);
CREATE TABLE school.participation(
 sid integer REFERENCES school.student,
 aid integer REFERENCES school.activity,
 hours integer NOT NULL CHECK(hours>=0),
 PRIMARY KEY(sid,aid));

2.1. Candidate key ABE. 2.2. Not BCNF because A→C, B→C, and E→D have determinants that are not superkeys. 2.3. A 2NF violation requires a non-prime attribute to depend on a proper subset of a candidate key. A one-attribute key has no non-empty proper subset, so such partial dependency cannot occur.

Answers

1.1. StudentID→Name,Gender; (StudentID,Unit)→Grade. Unit alone determines no other stored attribute. 1.2. Candidate key: (StudentID,Unit). 1.3. Original relation: 1NF yes; 2NF no because StudentID→Name,Gender is partial on (StudentID,Unit); therefore 3NF no and BCNF no. Final BCNF relations: STUDENT(StudentID,Name,Gender) and STUDENT_UNIT(StudentID,Unit,Grade), with StudentID as FK→STUDENT. Check grade 0–100 and gender in ('M','F','X').

2.1 CIA. Before 1NF: City→Country,CityPopulation,CountryPopulation,Capital,Attractions-list and Country→CountryPopulation. After 1NF: City→Country,CityPopulation,CountryPopulation,Capital and Country→CountryPopulation; City does not determine Attraction. 2.2. Candidate key before 1NF: City; after 1NF: (City,Attraction). Country is not a candidate key because Country⁺={Country,CountryPopulation}. 2.3. Original relation: 1NF no because Attractions contains a list; therefore 2NF, 3NF, and BCNF no. Final BCNF relations: COUNTRY(Country,CountryPopulation), CITY(City,Country FK→COUNTRY,CityPopulation,Capital), and CITY_ATTRACTION(City,Attraction), with City as FK→CITY.

3.1. Candidate keys ACE, BCE, CDE (closure-verified). 3.2. Not BCNF; A→B alone is a counterexample because A is not a superkey. No decomposition is requested; the supplied three-table decomposition does not preserve every listed dependency locally.

Answers

1.1. FDs: IID→CID,Items,Date,Amount and CID→Name,City; key IID. Assuming Name is one atomic display value, the table is 1NF and 2NF (single-column key), but not 3NF/BCNF because CID is not a superkey. Decompose CUSTOMER(CID,Name,City) and INVOICE(IID,CID FK,Items,Date,Amount); both BCNF. Splitting first/last name is a separate domain/design choice, not required by 1NF.

Classroom working example · stage labels

1NF: INVOICE_1NF(IID,CID,Name,City,Items,Date,Amount). 2NF: unchanged because IID is a one-column key. 3NF: split IID→CID→Name,City into CUSTOMER and INVOICE. BCNF: unchanged because IID and CID are keys of their own relations.

1.2. A PostgreSQL schema is a namespace inside the currently connected database. Therefore connect to lecture first, create invoiceschema, and schema-qualify both table names:

-- current database: lecture
CREATE SCHEMA invoiceschema;
CREATE TABLE invoiceschema.customer(
 cu_id integer PRIMARY KEY,
 cu_firstname varchar(255) NOT NULL,
 cu_lastname varchar(255) NOT NULL,
 cu_city varchar(255) NOT NULL);
CREATE TABLE invoiceschema.invoice(
 in_id integer PRIMARY KEY,
 in_customer integer NOT NULL REFERENCES invoiceschema.customer(cu_id),
 in_items integer NOT NULL CHECK(in_items>0),
 in_date date NOT NULL,
 in_amount numeric(10,2) NOT NULL CHECK(in_amount>=0));

The qualified form needs no search-path assumption. If Name stayed atomic in Task 1.1, use one cu_name column instead of first and last name.

1.3. ER: CUSTOMER 1:N INVOICE; keys CID/IID; invoice attributes Items, Date, Amount. Exporting with pgAdmin is a tool action; the model is the assessable output.

2.1 true/false. More than 1,000 aircraft impossible: false (char(3) has far more values). Each airport has exactly one city value: true per NOT NULL column, though several airports may share a city. Same departure/arrival airport forbidden: false, no such CHECK. Equal scheduled departure/arrival: true, impossible, because arrival>departure. Delayed flights selectable: true from scheduled vs actual/status. Aircraft cannot leave before it arrived: false; no cross-flight constraint.

2.2.

ALTER TABLE aircraft RENAME COLUMN range TO max_distance;
ALTER TABLE flight ALTER COLUMN status SET DEFAULT 'On Time';
ALTER TABLE airport ADD CHECK(airport_code ~ '^[A-Za-z]');

Answers

1. Use the Lecture relations in Learning Lab 05 above; the supplied LearningLab08.sql creates all nine tables with PK/FK constraints in schema lectureschema. The composite keys make semester-specific attendance/teaching/exam/placement facts unique and BCNF under the intended determinants.

-- 2.1 split name safely (populate before dropping old)
ALTER TABLE lm_student ADD COLUMN st_firstname varchar(50);
ALTER TABLE lm_student ADD COLUMN st_lastname varchar(50);
-- UPDATE with a reviewed parsing rule; names are not reliably split by first space.
ALTER TABLE lm_student DROP COLUMN st_name;

-- 2.2–2.3
ALTER TABLE lm_lecture ADD UNIQUE(le_title);
ALTER TABLE lm_examines ALTER COLUMN ex_grade SET NOT NULL;

-- 2.4 remove references, then room
ALTER TABLE lm_professor DROP COLUMN pr_room;
ALTER TABLE lm_takes_place DROP COLUMN tp_room;
DROP TABLE lm_room;

-- 2.5
CREATE TABLE lm_assistant(
 assistantnumber integer PRIMARY KEY,
 firstname varchar(50),lastname varchar(50),
 professor integer NOT NULL REFERENCES lm_professor(pr_number));

3 shopping tables.

CREATE TABLE customer(customer_id integer PRIMARY KEY,company varchar(120));
CREATE TABLE article(article_id integer PRIMARY KEY,name varchar(120));
CREATE TABLE purchase(
 customer_id integer REFERENCES customer,
 article_id integer REFERENCES article,
 bought_at timestamp,quantity integer CHECK(quantity>0),
 unit_price numeric(10,2) CHECK(unit_price>=0),
 PRIMARY KEY(customer_id,article_id,bought_at));
CREATE TABLE newsletter(
 newsletter_id integer PRIMARY KEY,topic varchar(120),publish_date date,name varchar(120));
CREATE TABLE subscription(
 customer_id integer REFERENCES customer,
 newsletter_id integer REFERENCES newsletter,
 PRIMARY KEY(customer_id,newsletter_id));

Answers

1. The W3Schools quiz is an external ungraded practice activity and has no stable written result; it is safe to skip after source-aligned SQL drills. 2.1–2.2 and 3.1–3.2: all INSERT, UPDATE, six corrected SELECT statements, explanations, and verified result rows appear in Chapter 5 under “Homework 08.”

Answers

SET search_path TO sales;

-- 1.1 Customers
SELECT COUNT(*) FROM customer;
SELECT territoryid,COUNT(*) FROM customer
GROUP BY territoryid ORDER BY COUNT(*) DESC;
SELECT * FROM customer WHERE territoryid IS DISTINCT FROM 1;
SELECT customerid FROM customer
GROUP BY customerid
HAVING COUNT(DISTINCT territoryid)>1;
-- no rows are possible if customerid is the table PK and one row has one territoryid

-- 1.2 Territory
SELECT * FROM salesterritory;
SELECT name FROM salesterritory
WHERE countryregioncode='US' ORDER BY name;

-- 1.3 SalesOrderHeader
SELECT onlineorderflag,COUNT(*) AS orders
FROM salesorderheader GROUP BY onlineorderflag;
SELECT orderdate::date AS day,COUNT(*) AS orders
FROM salesorderheader GROUP BY orderdate::date
HAVING COUNT(*)>100 ORDER BY day;
-- inspect returned dates for month-/quarter-end or promotion patterns

-- 1.4 SalesOrderDetail
SELECT productid,COUNT(DISTINCT salesorderid) AS orders
FROM salesorderdetail GROUP BY productid ORDER BY orders DESC;
SELECT productid,SUM(orderqty) AS units
FROM salesorderdetail GROUP BY productid ORDER BY units DESC;
SELECT productid,SUM(orderqty*unitprice) AS revenue
FROM salesorderdetail GROUP BY productid ORDER BY revenue DESC;
SELECT SUM(orderqty*unitprice) AS total_revenue
FROM salesorderdetail;

-- 2.1 Customers
SELECT DISTINCT h.customerid,t.name AS territory
FROM salesorderheader h
JOIN salesterritory t ON t.territoryid=h.territoryid
WHERE h.orderdate::date=DATE '2012-05-01';

SELECT h.customerid,SUM(d.orderqty) AS units
FROM salesorderheader h JOIN salesorderdetail d USING(salesorderid)
WHERE d.productid=729 GROUP BY h.customerid;

SELECT DISTINCT h.customerid
FROM salesorderheader h
JOIN salesterritory t ON t.territoryid=h.territoryid
WHERE h.orderdate>=DATE '2011-10-01'
  AND h.orderdate<DATE '2012-01-01'
  AND t.countryregioncode='DE';

SELECT h.customerid,SUM(d.orderqty*d.unitprice) AS revenue
FROM salesorderheader h JOIN salesorderdetail d USING(salesorderid)
GROUP BY h.customerid ORDER BY revenue DESC LIMIT 10;

-- 2.2 Product
SELECT d.productid,SUM(d.orderqty*d.unitprice) AS revenue
FROM salesorderheader h JOIN salesorderdetail d USING(salesorderid)
WHERE h.orderdate>=DATE '2011-08-01'
  AND h.orderdate<DATE '2011-09-01'
GROUP BY d.productid ORDER BY revenue DESC;

-- 2.3 Sales: corrected territory join
SELECT t.name AS territory,r.name AS salesreason,
       SUM(d.orderqty*d.unitprice) AS revenue
FROM salesterritory t
JOIN salesorderheader h ON h.territoryid=t.territoryid
JOIN salesorderdetail d USING(salesorderid)
JOIN salesorderheadersalesreason hr USING(salesorderid)
JOIN salesreason r USING(salesreasonid)
GROUP BY t.territoryid,t.name,r.salesreasonid,r.name
ORDER BY t.name,r.name;

Answer map

1 ER: Chapter 1 visual and answer. 2 relational model + normalization: Drill Lab 2 maps the model, states all semantic FDs and keys, and proves every corrected relation BCNF; Chapter 2 gives the relation lines. 3 DDL: Chapter 2 complete five-table SQL. 4 population: Chapter 2 all 3 hotels, 4 types, 10 rooms, 10 guests, and 12 bookings. 5.1–5.3: Drill Lab 5 pairs relational algebra with SQL for every subpart and checks the results; Chapter 5 repeats the final SQL set.

Answer map

1 import: execute OrderDatabase.sql into lecture; it creates schema ecommerce and ec_order. 2.1–2.3: Chapter 3 gives corrected FDs, key, five 3NF tables, all copy statements, and source drop order. 3: Chapter 3 verifies existing unit prices 8.56–988.01, then adds a BETWEEN 0 AND 10000 CHECK without dropping data. 4.1–4.3: Chapter 5 gives all eight query answers plus verified facts from 1,280 rows.