• Lecturer
• Course Admin
• Tutors
• How COMP3311 23T3 will run
• Know your course website
• Your DB Server
• Exercise: Play with vxdb2
• Data Modelling
• Some Design Ideas
• Exercise: Course Outline Data Model
• Exercise: Instagram Data Model
• Exercise: GMail Data Model
• Entity-Relationship Data Modelling
• Entity-Relationship (ER) Diagrams
• Exercise: Relationship Semantics

COMP3311 23T3
Database Systems

❖ Lecturer

❖ Course Admin

❖ Tutors

Many, many students  ⇒  many tutors ...

❖ How COMP3311 23T3 will run

Sources of information:

• lectures (Lectures) ... summarize content + examples
• videos (Topic Videos) ... primary content
• Course Notes ... more detailed version of topic video slides
• textbooks ... most detailed version of topics

Activities:

• lectures ... summarize, 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

❖ How COMP3311 23T3 will run (cont)

Classes:

• lectures ... live, recorded but not live-streamed
• tutorials ... mainly live (f2f), some online, starting week 2
• exam ... 3-hour, in-lab, invigilated, closed-book   (no offshore/at-home)

Note on lecture enrolments:

• 1UGB = I plan to attend live lectures (CLB7, aka OShane 104)
• WEB = I do not plan to attend live lectures

Not everyone can attend live: CLB7 holds ~450, Class is ~700

❖ Know your course website

Access via https://webcms3.cse.unsw.edu.au/COMP3311/23T3/

• Home: where Notices and Upcomings appear
• Course Outline: the "contract"
• Timetable: info on tutors and locations for tutorials
• videos: short-form (20-40 mins), topic based videos
• Lectures: slides, videos and exercise solutions
• Tute Exercises: questions and, eventually, answers
• Course Notes: more detailed slides (incl. optional material)
• Activities: quizzes and polls

❖ Your DB Server

You run you own PostgreSQL server on the host nw-syd-vxdb2

How to access the vxdb2 server

• from Vlab:    ssh YourUserName@nw-syd-vxdb2
• from Home:    ssh YourUserName@d2.cse.unsw.edu.au

On the vxdb2 server you have

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

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

❖ Exercise: Play with vxdb2

Login to vxdb2

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 02.

❖ 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: Course Outline Data Model

Imagine that we wanted a database of course outlines.

Work out requirements by looking at real course outlines.

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: 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

❖ 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)

The ER model is not a standard, so notational variations exist

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

❖ Entity-Relationship (ER) Diagrams

ER diagrams are a graphical tool for data modelling.

An ER diagram consists of:

• a collection of entity set definitions
• a collection of relationship set definitions
• attributes associated with entity and relationship sets
• connections between entity and relationship sets

Terminology abuse:

• we say "entity" when we mean "entity set"
• we say "relationship" when we mean "relationship set"
• we say "entity instance" to refer to a particular entity

Key = set of attributes that uniquely identifies each entity instance

❖ Entity-Relationship (ER) Diagrams (cont)

Example ER diagram:

❖ Entity-Relationship (ER) Diagrams (cont)

Example of attribute notations:

❖ Exercise: Relationship Semantics

Describe precisely the semantics of the following relationships: