-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathseed.sql
More file actions
59 lines (52 loc) · 4.2 KB
/
seed.sql
File metadata and controls
59 lines (52 loc) · 4.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- =============================================================
-- Section 03 — Normalization: SEED DATA
-- =============================================================
-- Load this AFTER schema.sql. The data is chosen to make every
-- anomaly (update / insert / delete) visible.
--
-- psql -U postgres -d sql_exercise \
-- -f 03-database-design/03-normalization/seed.sql
-- =============================================================
SET search_path TO dd_normalization;
-- -------------------------------------------------------------
-- bad_student_courses — notice the repetition
-- -------------------------------------------------------------
INSERT INTO bad_student_courses VALUES
('S001', 'Alice Johnson', 'Computer Science', '555-0100, 555-0101', 'CS101', 'Intro to Programming', '3', 'Prof. Knuth', 'Computer Science', 'A'),
('S001', 'Alice Johnson', 'Computer Science', '555-0100, 555-0101', 'CS202', 'Algorithms', '4', 'Prof. Dijkstra', 'Computer Science', 'B'),
('S001', 'Alice Johnson', 'Computer Science', '555-0100, 555-0101', 'MA150', 'Linear Algebra', '3', 'Prof. Noether', 'Mathematics', 'A'),
('S002', 'Bob Martinez', 'Mathematics', '555-0200', 'MA150', 'Linear Algebra', '3', 'Prof. Noether', 'Mathematics', 'B'),
('S002', 'Bob Martinez', 'Mathematics', '555-0200', 'MA220', 'Real Analysis', '4', 'Prof. Cauchy', 'Mathematics', 'A'),
('S003', 'Carol Wu', 'Computer Science', '555-0300, 555-0301, 555-0302', 'CS101', 'Intro to Programming', '3', 'Prof. Knuth', 'Computer Science', 'A'),
('S003', 'Carol Wu', 'Computer Science', '555-0300, 555-0301, 555-0302', 'CS202', 'Algorithms', '4', 'Prof. Dijkstra', 'Computer Science', 'A');
-- Observation: Alice's phone numbers are repeated on THREE rows.
-- If she changes one number, you have to update it in three
-- places. Miss one and the database has two "truths" for Alice.
-- -------------------------------------------------------------
-- bad_orders — realistic messy order history
-- -------------------------------------------------------------
INSERT INTO bad_orders VALUES
(1001, 50, '2026-03-01', 1, 'Acme Corp', 'London', 'EC1A 1BB', 'USB Cable', 'Accessories', 9.99, 3, 29.97),
(1001, 51, '2026-03-01', 1, 'Acme Corp', 'London', 'EC1A 1BB', 'Keyboard', 'Peripherals', 49.00, 1, 49.00),
(1001, 52, '2026-03-01', 1, 'Acme Corp', 'London', 'EC1A 1BB', 'Monitor 27"', 'Displays', 299.00,1, 299.00),
(1002, 50, '2026-03-02', 2, 'Beta Ltd', 'Manchester','M1 1AE', 'USB Cable', 'Accessories', 9.99, 5, 49.95),
(1002, 53, '2026-03-02', 2, 'Beta Ltd', 'Manchester','M1 1AE', 'Mouse', 'Peripherals', 19.50, 2, 39.00),
(1003, 51, '2026-03-04', 1, 'Acme Corp', 'London', 'EC1A 1BB', 'Keyboard', 'Peripherals', 49.00, 2, 98.00),
(1003, 54, '2026-03-04', 1, 'Acme Corp', 'London', 'EC1A 1BB', 'Webcam 1080p', 'Peripherals', 59.00, 1, 59.00),
(1004, 50, '2026-03-05', 3, 'Delta Inc', 'Leeds', 'LS1 4AP', 'USB Cable', 'Accessories', 9.99, 10,99.90);
-- Observation: "Acme Corp / London / EC1A 1BB" is duplicated
-- across five rows. "USB Cable / Accessories / 9.99" across four.
-- Change the product price in one row and the database lies.
-- -------------------------------------------------------------
-- bad_employees — transitive dependency data
-- -------------------------------------------------------------
INSERT INTO bad_employees VALUES
(1, 'Anna', 'Singh', 'anna@corp.io', 55000, 'ENG', 'Engineering', 'London', 'Raj Patel'),
(2, 'Ben', 'Owusu', 'ben@corp.io', 62000, 'ENG', 'Engineering', 'London', 'Raj Patel'),
(3, 'Carlos', 'Mendes', 'carlos@corp.io', 48000, 'SAL', 'Sales', 'Birmingham', 'Lisa Chen'),
(4, 'Diana', 'Petrov', 'diana@corp.io', 51000, 'SAL', 'Sales', 'Birmingham', 'Lisa Chen'),
(5, 'Erik', 'Johansson','erik@corp.io', 58000, 'MKT', 'Marketing', 'Manchester', 'Omar Ali'),
(6, 'Fatima', 'Rahman', 'fatima@corp.io', 72000, 'ENG', 'Engineering', 'London', 'Raj Patel');
-- Observation: if Engineering moves from London to Bristol, you
-- must UPDATE three rows in lockstep. Miss one and half the
-- company thinks Engineering is in London and half in Bristol.