• In today's lecture ...
• CSE Environment
• ER Model (recap)
• Exercise: ER Diagram
• Exercise: ER Models
• Class Hierarchies
• Exercise: Student/Lecturer Class Hierarchy
• Developing ER Models
• Exercise: Medical Information
• Data Models
• Relational Model

❖ In today's lecture ...

Topics:

• Entity-Relationship (ER) Model (cont)
• Relational Model
• Mapping ER → SQL
• SQL Data Definition Language (DDL)

Reminders:

• tutes start in week 02
• set up your PG server

  ssh nw-syd-vxdb2
  3311 pginit
  psql -l
  

❖ CSE Environment

❖ ER Model (recap)

ER models give a visual representation of database structure

Primary components

• Entities = objects of interest in the scenario
• Relationships = associations between entities
• Attributes = properties of entities

ER models don't capture all information from the scenario

• e.g. types and constraints on attribute values

ER models are useful as a step towards an SQL schema

❖ ER Model (recap) (cont)

ER design elements:

❖ ER Model (recap) (cont)

Relationships:

Thick line = total participation;   thin line = partial participation

❖ 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

❖ Class Hierarchies

ER also implements super-class / sub-class hierarchies

• both super- and sub-classes consist of entities
• super-class has common properties of all entities in hierarchy
• sub-classes can add extra properties to specialise
• entities in super-class may have corresponding entities in sub-class
• sub-classes can be
  • disjoint ... entities are members of only one sub-class
  • overlapping ... entities can be members of several sub-classes
• sub-classes can be involved in separate relationships

❖ Class Hierarchies (cont)

ER class hierarchies use circle to connect super/sub-classes:

❖ Class Hierarchies (cont)

Consider a concrete example of a class hierarchy:

❖ Exercise: Student/Lecturer Class Hierarchy

Modify the student-lecturer class hierachy to represent

• some people in the database are students or lecturers
• all people in the database are students or lecturers
• all people in the database are students XOR lecturers
• some people in the database are students XOR lecturers

❖ Developing ER Models

A complete ER model should ...

• include all entities and their attributes
• identify key attributes
• capture accurate semantics for all relationships
• include class hierarchies where appropriate

There are frequently different kinds of people (⇒ sub-classes)

❖ Exercise: Medical Information

Develop an ER model for the following scenario:

• Patients are identified by an SSN, and their names, addresses and ages must be recorded.
• Doctors are identified by an SSN. For each doctor, the name, specialty and years of experience must be recorded.
• A pharmacist is identified by an SSN, he/she can only work for one pharmacy. For each pharmacist, their name must be recorded.
• Each pharmacy has a name, address and phone number. A pharmacy must have a manager, who is a pharmacist.
• For each drug, the trade name and formula must be recorded.
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs, and has a price for each. A drug could be sold at several pharmacies, and the price could vary between pharmacies.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and quantity associated with it.

❖ Data Models

Entity-relationship (ER) model

• world is modelled via entities, relationships, attributes

Relational model

• world is modelled via tuples, relations, constraints

SQL schemas

• a good approximation of the relational model

Also ODL, UML, and a variety of others ... but not in this course.

❖ Relational Model

Attribute = data item with a name and a type/domain

• e.g.   account_balance has domain non-negative integer

Tuple = list of values   (cf. Python tuples, C  structs)

• e.g.   (1234567, John Smith, BE, SENG, 75.2)

Relation = set of tuples

• e.g.   { (1,2,3), (3,2,1), (1,3,5), (2,4,6) }

Constraint = logical statements on valid data

• e.g.   zID is unique   and   0 ≤ WAM ≤ 100

❖ Relational Model (cont)

Correspondence between ER and Relational models:

• Relational attributes correspond to ER attributes
  • although ER attributes generally don't have explicit domains
  
  
• Relational tuples correspond to ER entities
  
  
• Relations correspond to sets of ER entities
  
  
• Relations also correspond ER relationships