Why databases
Databases can manage complex relationships between different data sets, which spreadsheets cannot do effectively. Databases are designed to store and organise information on different business objects and their interrelationships, such as customers, products, suppliers, transactions and more. They handle things, people, and processes that have more than a single instance.
What does databases contain
- Tables
- Represent business objects (e.g. Student, customer, employee, project, etc)
- Rows: rows are called records
- Columns: columns are called fields
- Characters: characters in each cell are called bytes
- Relationships among tables
- Relationships between rows in different tables represent business rules
- Primary keys: a column or group of columns that uniquely identifies a row in a table
- Foreign keys: fields that are primary keys in other tables
- Metadata
- Support the organisation and management of the database
- Field name: what is the column called
- Data type: what types of the data stored in a column
- Field properties: such as length
- Description: what is in it
Database modelling
Data/Conceptual modelling
In this step the Entity Relationship Diagram (ERD) is developed.
Steps in creating an ERD
- Identify entities business objects you need to store information about.
- Identify business rules Find “Noun - Verb - Noun” relationship in narrative.
- Define relationships and represent cardinality Same business objects are actually relationships - associative entity.
- Identify attributes Characteristics of entities Represent information that you want to store about entities.
Golden rule
- Relationships
- The PK of the entity on the 1-side of the relationship is always the FK to the entity on the M-side of the relationship.
- Classifying relationship
- Use a Type relationship when an attribute is not unique to each entity instance, but to a type of entity. (e.g. ROOM and ROOM TYPE, rate changes based on type)
- Transactions
- Modelled with an associative entity, extend the composite primary key (e.g. with time) to allow for multiple transactions between the same object over time (e.g. hotel booking with the same guest and room)
Back to parent page: