DATABASES AND DATA MANAGEMENT Relational Database
RELATIONAL DATABASE
A relational database is a collection of data organized into a
table structure.
Within the table structure, the rows are called “records” or “tuples”
and the columns are called “attributes.”
The structure allows users to identify and access data in relation to
another piece of data in the table, or other tables within the
database.
Tables can be modified, or rows and columns can be added or
removed without affecting the rest of the database.
RELATIONAL DATABASE
An RDBMS, or relational database management system, is the
software that gives users the ability to update, query and
administer a relational database.
https://www.ibm.com/analytics/relational-database
The relational model is the conceptual basis of relational
databases.
RELATIONAL MODEL TERMINOLOGY
Relational Data Model
Based on the mathematical concept of a relation
Organizes and represents data in a form of table
A relation is a table with rows and columns
A tuple is a row of a relation
An attribute is a named column of a relation
A domain is a set of allowable values for each attribute.
RELATIONAL MODEL TERMINOLOGY
Alterative Terminology
Formal Terms Alternative 1 Alternative 2
Relation Table File
Tuple Row Record
Attribute Column Field
RELATIONAL MODEL TERMINOLOGY
1. Tables are made up of records and fields.
2. A record is a complete set of data about a single item. Eg. A
student particular
3. A field refers to one piece of data about a single item/record.
Eg. Name in Student particular.
RELATIONAL MODEL TERMINOLOGY
A set of A set of character A set of character M or F 1 to 4
integer strings that strings that
Domain value represents names represents NRIC of
of student student
Relation/Table/File
Student Name NRIC Gender Class
ID
Tuples/Rows 1 John Smith T1234567A M 3
/Records 2 Jenny Tan T7654321B F 4
Attributes/Columns/Fields
PROPERTIES OF RELATION
1. Relation name is distinct from all other relation names in the
relational schema
2. Each attribute has a distinct name
3. Each cell of relation contains exactly one atomic (single) value
PROPERTIES OF RELATION
4. Values of an attribute are from the same domain
5. Each tuple is distinct, there are no duplicate tuples
6. Order of the attributes has no significance
7. Order of tuples has no significance
PROPERTIES OF RELATIONS (ANOTHER VERSION)
1. A table has a name that is distinct from all other tables in the
database.
2. Each column has a distinct name.
3. Values in columns are atomic. The table does not contain
repeating groups or multivalued columns.
PROPERTIES OF RELATIONS (ANOTHER VERSION)
4. Values of a column are from the same domain/kind.
5. Each row is distinct, there are no duplicate rows.
6. The sequence of columns is insignificant.
7. The sequence of rows is insignificant.
RELATIONAL KEYS
1. Candidate key – minimal set of attributes that can uniquely
identifies each tuple in a relation.
2. Primary key – candidate key used to identify tuples uniquely in
a relation. Should not change overtime.
3. Secondary/Alternate key – candidate key not selected to be
primary key.
RELATIONAL KEYS
Student ID Name NRIC Gender Class ID
1 John Smith T1234567A M 3
2 Jenny Tan T7654321B F 4
3 James Lee T9876123C M 1
4 Joyce Lim T8769452D F 2
Identify the candidate key, primary key and secondary key in the table above.
RELATIONAL KEYS
4. Composite/Compound key – is a combination of two or more
attribute in a relation that can be used to uniquely identify each
tuples in the relation.
5. Foreign key – attribute which provide a logical link between tables.
RELATIONAL KEYS
.
Foreign key – attribute which provide a logical link between tables
Links between
PK FK 2 tables PK
Student Name NRIC Gender Class Class Class
ID ID ID Name
1 John Smith T1234567A M 3 1 19S60
2 Jenny Tan T7654321B F 4 2 19S6A
3 James Lee T9876123C M 1 3 19S6F
4 Joyce Lim T8769452D F 2 4 19S7D
RELATIONAL KEYS
Criteria for choosing a primary key
1. Minimum set of attributes
2. Less likely to have its values changed
3. Less likely to lose uniqueness in the future
4. With fewest characters
5. Easier to use from the user’s point of view
RELATION NOTATION
Students(StudentID, Name, NRIC, Gender, ClassID)
Classes(ClassID, ClassName)
Student Name NRIC Gender Class Class Class
ID ID ID Name
1 John Smith T1234567A M 3 1 19S60
2 Jenny Tan T7654321B F 4 2 19S6A
3 James Lee T9876123C M 1 3 19S6F
4 Joyce Lim T8769452D F 2 4 19S7D
