DATA2001 · Data Science, Big Data and Data Variety
Accessing Data in a Database
Week 2 moves from the conceptual ER model to the relational model (Codd, 1970) and to physically accessing data: translating an ER schema into tables with primary and foreign keys, and connecting to a PostgreSQL database through pgAdmin to run DDL and DML. The ER-to-relational mapping and basic SELECT-FROM-WHERE queries taught here are directly rehearsed in the tutorials and the Early Feedback Task, and they are the foundation of the Week 7 SQL Challenge and the 50% final exam.
What this chapter covers
- 01The relational model: relations (tables) of tuples (rows) over atomic attribute values; a relation is a set, so rows are unique and unordered
- 02ER-to-relational mapping — entities to tables, a 1:N relationship folded into the N-side as a foreign key, an M:N relationship to its own link table
- 03SQL sublanguages: DDL (CREATE/ALTER/DROP TABLE), DML (INSERT/UPDATE/DELETE/SELECT), DCL (permissions)
- 04Keys and constraints in SQL: PRIMARY KEY, UNIQUE, FOREIGN KEY ... REFERENCES, NOT NULL, composite keys
- 05Base data types: INTEGER/BIGINT, NUMERIC(p,q), VARCHAR(q) vs CHAR(q), DATE/TIMESTAMP
- 06Select-From-Where queries, DISTINCT, comparison and logical operators, LIKE with % and _, ORDER BY, AS aliases
- 07Connecting to PostgreSQL via pgAdmin; loading a denormalised CSV into a normalised schema (fact vs dimension tables)
Mapping a many-to-many relationship to relational tables
- +1Map each entity set to a table whose primary key is the entity's identifier: Musician(musician_id INTEGER, name VARCHAR(50), PRIMARY KEY (musician_id)) and Album(album_id INTEGER, title VARCHAR(80), PRIMARY KEY (album_id)).
- +1A many-to-many relationship cannot be folded into either entity's table (that would repeat rows or lose pairings), so it becomes its own link table holding the two foreign keys: PlaysOn(musician_id, album_id, role, ...).
- +1Set the link table's key and referential integrity. The combined participating primary keys form the key: PRIMARY KEY (musician_id, album_id). Add FOREIGN KEY (musician_id) REFERENCES Musician and FOREIGN KEY (album_id) REFERENCES Album so every pairing points at real entities.
- +1Place the relationship attribute on the link table: role VARCHAR(20) belongs on PlaysOn, because it describes the pairing (a musician's role on a particular album), not the musician or the album alone.
Key terms
- Relation
- A named two-dimensional table of rows (tuples) and columns (attributes) in which every value is atomic and every row is unique (a relation is a set). Column and row order are immaterial; an RDBMS relaxes this by allowing duplicate rows and NULLs.
- Foreign key
- An attribute (or set) in one table that references the primary/candidate key of another, letting a child table point at a parent row. Declared FOREIGN KEY (cols) REFERENCES Parent; it may be NULL unless combined with NOT NULL, and enforces referential integrity.
- DDL vs DML
- Data Definition Language (CREATE/ALTER/DROP TABLE) defines and changes the schema; Data Manipulation Language (INSERT/UPDATE/DELETE/SELECT) changes and reads the data. DCL (GRANT/REVOKE) controls permissions.
- CHAR vs VARCHAR
- CHAR(q) is a fixed-length string right-padded with spaces to q characters; VARCHAR(q) is variable-length up to q with no padding. Use VARCHAR for names and codes of varying length to avoid trailing-space surprises.
- LIKE pattern matching
- A string comparison in WHERE using wildcards: % matches any substring (including empty) and _ matches exactly one character. For example WHERE code LIKE 'INFO%' finds all codes starting with INFO.
- pgAdmin
- The graphical client used in DATA2001 to connect to a PostgreSQL server, browse schemas, and run SQL. A denormalised CSV is typically loaded by creating the normalised tables from a schema file, then importing the CSV (Header on) or bulk-inserting.
Accessing Data in a Database FAQ
How does an ER diagram become relational tables?
Each entity set becomes a table keyed on its identifier. A 1:N relationship is represented by adding the 'one' side's primary key as a foreign key on the 'many' side's table. A many-to-many relationship becomes its own link table holding both foreign keys as a composite primary key, plus any relationship attributes. Multi-valued attributes also become their own tables. The mechanical nature of this mapping is exactly why the conceptual ER step in Week 1 matters.
What makes a table a valid relation?
Every relation has a unique name, its attributes have unique names, every value is atomic (no lists or nested records in a cell), and — mathematically — the rows form a set, so there are no duplicate rows and row order carries no meaning. Real RDBMSs extend this by permitting duplicate rows, a stored order, and NULLs for unknown values, but the atomic-values rule is the one exams test.
When do I need a foreign key with NOT NULL?
By default a foreign key column may be NULL, meaning 'no related parent yet'. Add NOT NULL when the relationship is mandatory — for example, if every enrolment must reference an existing student, the student_id foreign key should be NOT NULL. This mirrors a total-participation constraint from the ER diagram.
Can AI help me connect to PostgreSQL and write my first queries?
Yes. Sia can explain the pgAdmin connection steps, the difference between DDL and DML, and how to write and read a SELECT-FROM-WHERE query, and it can check a CREATE TABLE statement's keys and types. Practise on your own tutorial schema and ask it to explain each error message. It supports your learning and does not do graded work for you; follow the University of Sydney academic-integrity policy.
Exam move
Get fluent at two things this week: the ER-to-relational mapping and running SQL in pgAdmin. Take every ER diagram from the tutorials and write out the tables, primary keys and foreign keys by hand, saying aloud why each 1:N relationship needs only a foreign key while each M:N needs its own table. Then set up PostgreSQL early (via the Canvas instructions), create a couple of tables, load a small CSV, and run SELECT-FROM-WHERE queries with DISTINCT, LIKE and ORDER BY until the syntax is automatic — this directly de-risks the Week 7 SQL Challenge. Memorise the common data types (INTEGER, NUMERIC(p,q), VARCHAR(q), DATE, TIMESTAMP) and the constraint keywords (PRIMARY KEY, UNIQUE, FOREIGN KEY ... REFERENCES, NOT NULL). Ask Sia to quiz you on mapping and to check your table definitions; confirm any tooling or assessment details on Canvas.
Working through Accessing Data in a Database in DATA2001? Sia is AskSia’s AI Data Science tutor — ask any DATA2001 Accessing Data in a Database question and get a clear, step-by-step explanation grounded in how DATA2001 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
