Database Technologies
1. Introduction to Logical Data Modelling &
RDBMS Design
Database design:
- we seek to build data models that faithfully represent
the real world User Documents
(Input Forms, Tabular Reports, etc.)
- the way relational databases physically represent the real
world can be some distance removed from it
- Levels of abstraction:
Users Views / Schema
o User documents = physical, tangible artefacts (Description of Data Needed)
(e.g. inputs and outputs)
o User views = the data that the system must
capture Conceptual Schema
(Logical Design Model)
o Conceptual schema = logical structure
o Internal schema = how data is physically stored
(tables, indexes, etc.) Internal Schema
(Physical Database Design)
Business documents: if the currency is wrong / changes: potential
huge cost! currency = separate field (safer)
Conceptual schema
Entity-Relationship modelling = technique for logical database design
- ERD = Diagrams of the E-R Models
- Entity = something about which the organisation needs to record data (e.g.
Student, Course, …)
- Instance = single occurrence of an entity (e.g. Database Technology = instance
of Course)
- Attribute = how an entity is described (e.g. Student StudentID, Name, Adress,
…)
- Domain = set of allowable values (e.g. 0 < Student Mark < 100, Adress: max 80
characters)
- Relationships:
o One-to-One Relationship (1:1)
for any instance of either entity there is at most one instance of the
other
Quite rare in actuality
Sometimes used for privacy or performance reasons
o One-to-Many Relationship (1:M)
for every instance of the first entity there may be several instances of
the second 1:M relationships subsume 1:1 relationships
o Many-to-Many Relationship (M:M)
for every instance of either entity there may be numerous instances
of the other
1
, - Synonymous terms:
o “Entities” / “Classes” = “Tables” / “Relations”
o “Records” = “Instances” = “Rows” / “Tuples”
o “Fields” = “Attributes” = “Columns”
o “Identifiers” = “Primary Keys”
o “Cardinality” = “Multiplicity”
o “Relationships” = “Associations”
- Variety of notations:
o Chen Notation (1976)
o Crow’s Foot Notation
o UML notation
Primary keys:
- Every record in a database must be uniquely identified
- The chosen key should be:
o Unique: the key attribute(s) must be a unique combination
o Minimal: choose the fewest number of attributes
o Stable: choose a key that seldom changes (preferably never)
o Simple / familiar: something that is easy to remember
o Not null: there must always be a value (in the case of composite keys, all
component attributes must have a value)
o Secure/Private: be conscious of potential privacy & cybersecurity issues
when choosing a primary key
- A primary key can be a single attribute, or a combination of two or more
attributes (composite/concatenated key), for example:
o Student = Student ID
o Examination result = Student ID + Course Code + Exam Session/Year
o Football match = Date + Time + Team1 + Team2 (consider what happens
with same day blitz). Alternatively, Match Number as a surrogate single-
attribute PK.
1:M Relationships:
- Foreign key at the M side
- 1:1 relationship = 1:M relationship with uniqueness constraint
- Uniqueness constraint = no 2 records can have the same value
Strong weak entities
- Instance of a strong entity: can exist independently
- Instance of a weak entity: can only exist if there is a related insance of some
other entity
- Example:
o A Degree may be taken by zero or more Students dotted line
o A Student must register for a Degree full line
Separate Relationships between a Pair of Entities
M:M Relationships:
- Relational databases cannot implement M:M relationships directly
- Must create a third entity, a so-called “intersection” or “associative” entity
2
1. Introduction to Logical Data Modelling &
RDBMS Design
Database design:
- we seek to build data models that faithfully represent
the real world User Documents
(Input Forms, Tabular Reports, etc.)
- the way relational databases physically represent the real
world can be some distance removed from it
- Levels of abstraction:
Users Views / Schema
o User documents = physical, tangible artefacts (Description of Data Needed)
(e.g. inputs and outputs)
o User views = the data that the system must
capture Conceptual Schema
(Logical Design Model)
o Conceptual schema = logical structure
o Internal schema = how data is physically stored
(tables, indexes, etc.) Internal Schema
(Physical Database Design)
Business documents: if the currency is wrong / changes: potential
huge cost! currency = separate field (safer)
Conceptual schema
Entity-Relationship modelling = technique for logical database design
- ERD = Diagrams of the E-R Models
- Entity = something about which the organisation needs to record data (e.g.
Student, Course, …)
- Instance = single occurrence of an entity (e.g. Database Technology = instance
of Course)
- Attribute = how an entity is described (e.g. Student StudentID, Name, Adress,
…)
- Domain = set of allowable values (e.g. 0 < Student Mark < 100, Adress: max 80
characters)
- Relationships:
o One-to-One Relationship (1:1)
for any instance of either entity there is at most one instance of the
other
Quite rare in actuality
Sometimes used for privacy or performance reasons
o One-to-Many Relationship (1:M)
for every instance of the first entity there may be several instances of
the second 1:M relationships subsume 1:1 relationships
o Many-to-Many Relationship (M:M)
for every instance of either entity there may be numerous instances
of the other
1
, - Synonymous terms:
o “Entities” / “Classes” = “Tables” / “Relations”
o “Records” = “Instances” = “Rows” / “Tuples”
o “Fields” = “Attributes” = “Columns”
o “Identifiers” = “Primary Keys”
o “Cardinality” = “Multiplicity”
o “Relationships” = “Associations”
- Variety of notations:
o Chen Notation (1976)
o Crow’s Foot Notation
o UML notation
Primary keys:
- Every record in a database must be uniquely identified
- The chosen key should be:
o Unique: the key attribute(s) must be a unique combination
o Minimal: choose the fewest number of attributes
o Stable: choose a key that seldom changes (preferably never)
o Simple / familiar: something that is easy to remember
o Not null: there must always be a value (in the case of composite keys, all
component attributes must have a value)
o Secure/Private: be conscious of potential privacy & cybersecurity issues
when choosing a primary key
- A primary key can be a single attribute, or a combination of two or more
attributes (composite/concatenated key), for example:
o Student = Student ID
o Examination result = Student ID + Course Code + Exam Session/Year
o Football match = Date + Time + Team1 + Team2 (consider what happens
with same day blitz). Alternatively, Match Number as a surrogate single-
attribute PK.
1:M Relationships:
- Foreign key at the M side
- 1:1 relationship = 1:M relationship with uniqueness constraint
- Uniqueness constraint = no 2 records can have the same value
Strong weak entities
- Instance of a strong entity: can exist independently
- Instance of a weak entity: can only exist if there is a related insance of some
other entity
- Example:
o A Degree may be taken by zero or more Students dotted line
o A Student must register for a Degree full line
Separate Relationships between a Pair of Entities
M:M Relationships:
- Relational databases cannot implement M:M relationships directly
- Must create a third entity, a so-called “intersection” or “associative” entity
2
