Relational Database
Relational Database
Relational database:
- = a collection of data organised into a table structure
- 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 management system: (RDBMS)
- Software that gives users ability to update query, and administer a relational database
- https://www.ibm.com/analytics/relational-database
- Relational model: conceptual basis of relational databases
Relational data model
- Organises and represents data in a form of table
| Formal terms | Alternative 1 | Alternative 2 |
|---|---|---|
| Relation = a table with rows (records) and columns (fields) Relation name is distinct from all other relation names in the relational schema (i.e.all other tables in database) Each cell of relation contains exactly 1 atomic (single) value | Table | File |
| Tuple = a row of a relation Each is distinct, no duplicates A complete set of data about a single item (e.g. a student particular) Order/sequence has no significance (i.e. insignificant) | Row | Record |
| Attribute = a named column of a relation Each has a distinct name 1 piece of data about a single item/record (e.g. name) Values of an attribute are from the same domain/kind Order/sequence has no significance Atomic values ⇒ table does not contain repeating groups or multivalued columns | Column | Field |
| Domain = a set of allowable values for each attribute |
Relational keys
- Candidate key = minimal set of attributes that can uniquely identify each tuple in a relation (e.g. student ID and NRIC)
- Never NULL or empty
- Can have >1 for a table
- Can be a combination of >1 field
- Primary key = candidate key used to identify tuples uniquely in a relation (i.e. main key)
- Should not change over time (e.g. student ID (flushed to the left in a table))
- Criteria for choosing primary key:
- Minimum set of attributes
- Less likely to have its values changed
- Less likely to lose uniqueness in the future
- With fewest characters
- Easier to use from the user’s point of view
- I.e. a primary key tells a particular record apart from another record
- Secondary / Alternate key = candidate keys that are not selected to be a primary key (e.g. NRIC)
- Composite / Compound key = a combination of 2 or more attribute in a relation that can be used to uniquely identify each tuple in the relation
- Foreign Key = attribute which provide a logical link between tables
= attribute in 1 table that refers to the primary key in another table 
Relation notation / table description
- Table description can be expressed as:
- Primary key is indicated by underlining one or more attributes (solid underline)
- Foreign key is indicated by using a dashed/dotted underline (e.g. Attribute2)
