3. Terminology
Database Design for Mere Mortals: A Hands - on Guide to Relational Database Design
Terminologies
A special language to talk about RDBMS
Expresses the process itself
Used everywhere where RDBMS is discussed
Four categories of terms are: value-related, structure-related, relationship-related, integrity-related
Value Related Terms
Data vs Information
Data is what you store, information is what you retrieve.
Data has no meaning without context
Null
A condition that represents a missing or unknown value
The Value of Null
Missing Value Cause(s)
Human error while data entry
Unknown Value Cause(s)
The value currently doesn't exist
If the value is not applicable, better to write it as "N/A" compared to Null
The Problem with Null
Mathematical operations are messed up e.g. 2 + Null = Null
Has an adverse effect on aggregate functions
Structure Related Terms
Table
Relations = Tables = Tuples (Records) + Attributes (Fields)
Each table always represents a single, specific subject
Logical ordering of the tuples and attributes doesn't matter
Each record can be identified by a unique key called the primary key
The subject can be either an event or an object
Object = Something tangible such as person, place, or thing
Event = Something that occurs at a given point in time e.g. judicial hearings, movie shoots, elections
Data Table = One which stores the data to supply information
Validation Table = Lookup table = Stores the date to implement the data integrity
e.g. city names, product codes
Mostly static data as it changes very rarely
Used indirectly to validate the values in the data table
Field
The smallest structure in the database represents characteristics of the subject of the table
Fields = Attributes = Actually store the data
Contains only one value and its name defines the type of value it holds e.g. FIRSTNAME, CITY, STATE
Poorly designed DB fields,
Multipart fields (composite field) - contains two or more distinct items within its value
Multivalued field - multiple instances of the same type of value
Calculated field - value contains the result of some expression
Record
Represents a single row in the table, all attributes considered whether they have value or not
Also called a tuple in relational algebra
View
Different representation of base table(s)
Views derive the data from base table(s), the only information stored about views in the database is their structure
Benefits of View
Can work with multiple tables at once
Imposes security by hiding base tables
Can be used to implement data integrity, called a validation view
Keys
Primary Key - Uniquely identifies a record in the table
Foreign Key - Primary key of another table which "foreign" in the current table
Keys are used to establish a relationship between tables
Index
Used for optimizing DB performance
It has absolutely nothing to do with logical database structure
Keys are logical structures you use to identify records within a table, and indexes are physical structures you use to optimize data processing.
Relationship Related Terms
Relationship
Enables creating multi-table views
Reduces redundant data and eliminate duplicate data
Types of Relationships
One-to-One - Single record in Table_A is related to zero or one and only one record in Table_B, and a single record in Table_B is related to one and only one record in Table_A.
Creates a parent-child relationship between tables. Table_A is parent and Table_B is a child.
The relationship is established by incorporating the parent's primary key into the child's table
Both tables may share the same primary key i.e. parent's primary key
One-to-Many - Single record in Table_A is related to zero, one or more record in the Table_B, and a single record in the Table_B is related to one and only one record in the Table_A.
Parent-Child relationship is still applicable. Table_A is parent and Table_B is child.
Most common relationship pattern. Helps reduce data redundancy.
Many-to-Many - Single record in Table_A can be related to zero, one, or many records in the Table_B and likewise a single record in the Table_B can be related to zero, one, or many records in the Table_A
The relationship is maintained using the linking table.
Primary keys from both tables form the composite primary key of the linking table.
Types of Participation
Optional - If it doesn't require Table_A to have any records before creating any records in Table_B then Table_A is optionally participating in the relationship
Mandatory - If it requires Table_A to have at least one record before creating any records in Table_B then Table_A is mandatorily participating in the relationship
Degree of Participation
Degree of participation of Table_A is written as "x,y" which means a single row in Table_A can be associated with minimum x and maximum y records of Table_B.
Integrity Related Terms
Field Specification
Also know as domain, represents all the elements of a field.
Incorporates three types of elements,
General - most fundamental information about the field such as Field Name, Description, and Parent Table
Physical - Implementation level details such as Data Type, Length, and Character Support
Logical - Describe the values stored in the field with attributes such as Required Value, Range of Values, and Null Support
Data Integrity
Refers to validity, consistency, and accuracy of the data in a database
Level of accuracy of information is directly proportional to level of data integrity imposed. One of the most important aspect.
Four types of data integrity,
Table-level - Ensures PK for each record in the table is unique and never Null
Field-level - Structure and value of the field are valid, consistent, and accurate
Relationship-level (Referential integrity) - The records are synchronized between related (by foreign keys or link tables) tables when CRUD operations happen.
Business rules - Limitations applied based on how organization/busines uses the data. E.g. types of values stored in a field, degree of participation etc.