• Lecturer
• Course Admin
• Tutors
• How COMP3311 23T1 will run
• Know your course website
• Software
• 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
• Exercise: ER Diagram
• Exercise: ER Models

COMP3311 23T1
Database Systems

❖ Lecturer

❖ Course Admin

❖ Tutors

Many, many students  ⇒  many tutors ...

❖ How COMP3311 23T1 will run

Sources of information:

• content videos (Slides and Videos) ... primary content
• lectures (Lecture Material) ... summarises content + examples
• Course Notes ... more detailed version of content video slides
• textbooks ... most detailed version of topics

Activities:

• lectures ... work though (new) 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 23T1 will run (cont)

Classes:

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

Note on lecture enrolments:

• 1UGB = I plan to attend live lectures (CLB6)
• WEB = I do not plan to attend live lectures

Not everyone can attend live: CLB6 holds ~200, Class is ~600

❖ Know your course website

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

• Home: where Notices and Upcomings appear
• Course Outline: the "contract"
• Timetable: info on tutors and locations for tutorials
• Slides and videos: short-form, 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

❖ Software

Software versions that we'll be running this semester (TBC):

• PostgreSQL 13,   SQLite 3.x,   Python 3.9+,   psycopg2 2.8+

If you install them at home:

• get versions "close to" these
• test all work at CSE before submitting

Alternative to installing at home:

• log in to the vxdb2 server in a terminal window and work there

❖ Software (cont)

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 actual hostname of the vxdb2 server is nw-syd-vxdb2

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

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:

❖ Exercise: ER Diagram

Using the ER diagram below

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

❖ Exercise: ER Models

For each of the informal data models

• course outline
• Instagram
• Gmail

give a suitable ER diagram