• Lecturer
• Course Admin
• Tutors
• How COMP3311 25T1 will run
• Lecture Videos
• Know your course website
• Your DB Server
• Exercise: Play with vxdb02
• Data Modelling
• Some Design Ideas
• Exercise: Instagram Data Model
• Exercise: GMail Data Model
• Exercise: Handbook Data Model
• Entity-Relationship Data Modelling
• Exercise: Relationship Semantics
• Exercise: ER Diagram

COMP3311 25T1
Database Systems

❖ Lecturer

❖ Course Admin

❖ Tutors

Many, many students  ⇒  many tutors ...

❖ How COMP3311 25T1 will run

Sources of information:

• Topic Videos ... primary content
• Lectures ... summary content + examples
• textbooks ... most detailed version of topics

Activities:

• lectures ... work though examples, ask questions
• tutorials ... work through exercises, ask questions
• prac exercises ... learn the systems and skills
• assignments ... practice your skills
• exam ... demonstrate your knowledge/skills

❖ Lecture Videos

Echo360 records the lecture; I also record the lecture.

After the lecture, Echo360 processing takes ~1.5 hours

After the lecture ...

• I do some post-processing (~1 hour)
• I upload to YouTube (~1 hour)
• I link it to the web site (when I remember)

I do not monetize the YouTube videos; any ads are courtesy of Google.

❖ Know your course website

Access via https://webcms3.cse.unsw.edu.au/COMP3311/25T1/

• Home: where Notices and Upcomings appear
• Course Outline: the "contract"
• Timetable: info on tutors and locations for tutorials
• Topic Videos: short-form (20-40 mins), topic based videos
• Lectures: slides, videos and exercise solutions
• Assignments: assignment specs and other material
• Tutorials : questions and, eventually, answers
• Prac Work : lab-like practical exercises
• Activities: quizzes and polls

❖ Your DB Server

You run you own PostgreSQL server on the host vxdb02

How to access the vxdb02 server

• from Vlab:    ssh vxdb02
• from Home:    ssh YourUserName@vxdb02.cse.unsw.edu.au

On the vxdb02 server you have

• your standard CSE directories
• a special directory /localstorage/YourUserName/

The only software you should run on vxdb02 is PostgreSQL  (no vscode)

❖ Exercise: Play with vxdb02

Login to vxdb02

Find your /localstorage directory

Set up your environment

Install your PostgreSQL server

Explore the files in your PostgreSQL server

More details on doing this are in Prac Exercise 01.

❖ Data Modelling

Aims of data modelling:

• describe what information is contained in the database
  (e.g., entities: students, courses, accounts, branches, patients, ...)
• describe relationships between data items
  (e.g., John is enrolled in COMP3311, Tom's account is held at Coogee)
• describe constraints on data
  (e.g., 7-digit IDs, students can enrol in no more than 3 courses per term)

Data modelling is a design process

• converts requirements into a data model

❖ Some Design Ideas

Consider the following while working through exercises:

• start simple ... evolve design as problem better understood
• identify objects (and their properties), then relationships
• most designs involve kinds (classes) of people
• keywords in requirements suggest data/relationships
  (rule-of-thumb: nouns → data, verbs → relationships)
• don't confuse operations with relationships
  (operation: he buys a book; relationship: the book is owned by him)
• consider all possible data, not just what is available

❖ Exercise: Instagram Data Model

Consider Instagram (photos, videos, viewers, posters, ...)

Develop an informal data model for it by identifying:

• the data items involved (objects and their attributes)
• relationships between these data items
• constraints on the data and relationships

❖ Exercise: GMail Data Model

Consider the GMail system (or any other modern mail client)

Develop an informal data model for it by identifying:

• the data items involved (objects and their attributes)
• relationships between these data items
• constraints on the data and relationships

❖ Exercise: Handbook Data Model

Imagine that we wanted a database for the UNSW Handbook

Work out requirements by looking at the actual Handbook

Develop an informal data model for it by identifying:

• the data items involved (objects and their attributes)
• relationships between these data items
• constraints on the data and relationships

❖ Entity-Relationship Data Modelling

The world is viewed as a collection of inter-related entities.

ER has three major modelling constructs:

• attribute: data item describing a property of interest
• entity: collection of attributes describing object of interest
• relationship: association between entities (objects)

ER diagram: modelling notation containing collections of

• entities (rectangles) and their associated attributes (ovals)
• relationships (diamonds) between entities   (1:1, 1:n, n:m, total, partial)
• keys: sets of attributes that uniquely define entities

Lecture notes use notation from SKS and GUW books (simple)

❖ Entity-Relationship Data Modelling (cont)

Example ER diagram:

❖ Entity-Relationship Data Modelling (cont)

ER design elements:

❖ Entity-Relationship Data Modelling (cont)

Example of attribute notations:

❖ Entity-Relationship Data Modelling (cont)

Relationships:

Thick line = total participation;   thin line = partial participation

❖ Exercise: Relationship Semantics

Describe precisely the semantics of the following relationships:

❖ Exercise: ER Diagram

Using the ER diagram below

• give examples of entity values
• describe the semantics of the relationships