Database design & modeling

• These are all the study notes for the BUSI 2053 - Data & Information Management chapters 1-4 recap

Chapter 1: Introduction

• Data are stored facts in various formats like text, numbers, and images
• Information is data accessed for specific tasks, making it useful
• Metadata describes the structure of data to make it usable, including names and types
• A database is an organized collection of data designed for quick access
• DBMS is software used to create, manage, and use databases
• DBMS allows users to work directly with the database in a direct interaction
• Users accessing databases through apps or interfaces is an indirect interaction

Steps to Build a Database

• Plan: Identify the data needed and its intended use
• Model: Design a blueprint for the database, using diagrams
• Build: Use a DBMS to create the database
• Add Features: Develop user-friendly interfaces like forms and menus
• Deploy: Make the database available for use
• Maintain: Handle security, backups, and updates

Database Roles

• Analysts define the purpose of the database
• Designers create the structure of the database
• Developers build the database and its interfaces
• DBAs maintain and secure the database
• Users access and use the data for various tasks

Types of Databases

• Operational databases support daily tasks and transactions
• Analytical databases support decision-making by analyzing data

Chapter 2: Database Requirements & ER Diagrams

• ER Modeling: a database design tool
• Key Concepts are entities, attributes, relationships, and ER Diagrams (ERDs)
• ERDs serve as a blueprint for database creation

Chapter 2 Breakdown

• Entity-Relationship (ER) Modeling helps organize database requirements and design in a technique
• ER diagrams (ERDs) serve as the blueprints for databases

Entities

• Represented as rectangles
• Entities are things like people, places, or objects
• Attributes describe entities, for example: name and/or ID
• Strong Entities: Can exist independently (e.g., Employee).
• Weak Entities: Depend on a strong entity and cannot exist alone (e.g., OrderItem).

ERD Basics

ERDs

• ERDs visually represent a database’s structure
• They map entities (tables), attributes (columns), and relationships between entities

Core components of an ERD

• Entities represented as rectangles denoting objects/things such as Customer, Order, and Product
• Strong entities, such as employees, can exist independently
• Weak entities, such as OrderItem, depend on strong entities and cannot exist alone
• Attributes represented as ovals connected to entities, describing their properties, such as details
• Relationships represented as diamonds between entities
• Relationships show how entities are related such as: Customer Places Order, Employee Assigned To Project

Attributes

• Simple: cannot divide (e.g., FirstName)
• Composite: it can be broken into smaller parts (e.g., FullName → FirstName + LastName)
• Derived: Calculated (e.g., Age derived from DateOfBirth)
• Multi-Valued: Can have multiple values

Primary Key

• Primary keys(PK) are attributes uniquely identifying each record in an entity
• Primary keys are represented as underlined text

Special Cases

• Weak entities depend on other entities for identification
• Associative entities simplify complex relationships, especially many-to-many connections
• Ternary relationships involve three entities

Relationships

• Mandatory Many / One or Many / 1:M: An instance of Entity A must be related to at least one or more instances of the other entity
• Optional Many / Zero or Many / 0:M: An instance of Entity A can be related to zeor , one or many isntances of teh otehr entity
• Mandatory One / One and Only One / 1:1: An instance of Entity A must be related to exactly one instance of the other entity
• Optional One / Zero or One / 0:1: An instance of Entity A can be related to zero or one instance of the other entity

ER Diagram Practices

• Naming conventions should be clear
• Diagrams should avoid over-complication,
• Organization processes not database info., should not be the focus

Common Mistakes

• Confusing entities with ER diagrams
• Including unnecessary details

Design Process for ERD

• Requirements must be understood by talking to stake holders to see what it needs to store
• Entities are determined by figuring out the main objects
• Define Relationships by determining how entities interact with each other
• Attributes for each entity can be defined
• Cardinality (1:1, 1:N, M:N) must be determined by defining the # of instances in each relationship
• ERD must then be drawn using the symbols

Best Practices

• The design must stay clear of over complex ERDs but grouping up logical similiar data instead
• Naming should be consistent by descriptive names (e.g.,Customer_ID, not C_ID)
• Redundancy is avoided if attributes are not duplicated
• Stakeholder should then verify the diagram to see if it reflects what it shows
• Sample Scenarios should be tested by real usage

Tools to Creating ERDs

• ERDPlus for free
• Lucidchart the most popular option for free
• Paid Microsoft Visio

Common Mistakes

• Not defining a primary key for every entity
• Failing to identify proper relationships(misidentifying cardinality)
• Ignoring derived attributes
• Adding redundant entities or attributes

Chapter 3: Relational Database Modelling

• Introduction to the relational database model to design databases by mapping real-world data and relationships into a logical framework. This ensures data intergrity.
• Entity Relationship ER diagrams transaltion to relation schemas handle various attribute types
• Database designs show how contraints and modeling techniques work together to creat efficient, consistent, and scalable databases.

10 Key Takeaways

• Relational Databases organize data into tables
• Specific rules qualify tables as relations
• Primary keys make each row in a table unique
• Composite primary keys use multiple columns to identify rows
• ER Diagrams help the design of tables and its relationships
• Foreign keys connect related tables
• Different types of relationships are handles differently in tables
• Constraints ensure data accuracy and consistency
• Special attributes like multivalued and derived are handled separately
• ER Diagram make easier understanding and communication for database designs

What is a relational database?

• Structured tables are the organization for data

Relational Database components

• Tables: entity representations
• Rows: indivisula records
• Columns: entity attributes

Rules for relations

Data in each column must follow a predefined format Each cell must have a single value. Column must have a unique name

Primary key

• The definition of this is that primary key is a column or set of columns that identifies each table row
• This makes sure there is no duplicate rows and a unique identifier is presented

Foreign Key

• The definition of this is that foreign key is a column that one table that references the primary key in another table
• It maintains referential intergrity ensure no orders can exists for non existant customers

Translating entities to tables

• Break down into indivisual columns
• Each entity becomes a table
• Attributes of the entity becomes columns
• Unique attributrs are mapped as primary keys

Relationships - Mapping

• 1-M is the reuqirment for "many side"
• M:N create foreign keys tables for both entities
• 1:1 ensures keys are mapped as is to the tables

Data intergrity

• Ensures no duplicates row
• Enitity intergrity constraint must not have null vaules

Special attributes

• Store multiple tables with foreign keys
• Calculated application and not always in a database

Mapping Unary and Ternary Relationships

• Ternary Relationships involve three entities, e.g., Doctors, Patients, and Appointments.
• Create a table with foreign keys for three entities
• Unar Relationships are the same entity, e.g., employees managing employees
• Multiple tables with foreign keys

Cardinality

• The relationship is one-to-many (1:N)
• One side is the foreign key to the (DEVICE) through the EID table

How to deal with each type

• One-to-One: add forigen keys w. the table merge or unique constriants
• One-to-Many: add forigen key in the 'many' table
• Many-to-Many: make a table with keys from both entities

Chapter 4: Update Operations, Update Anomalies, and Normalization

• Updates are considered as insert, delete, modify for operations that keep a database running smoothly
• Update: Existing Records are updated (changing a customer's)
• Deletion: Remove outdated or unnecessary records
• Insert: Add new records

Update Anomalies

• Insertion Anomaly: Add new related extra info
• Deletion Anomaly: Removing one record deleting other important info
• Modification Anomaly: data has to be updated in multiple places ( error-prone)

Functional Depencies-The Rules of Data Relationships

• A column's value uniquely determines another column's value
• Avoiding pitfall ( A-A is an obivous statement) _ Equivalent ( One vaue determines another)

Power of Normalization

• Reduces the amount of data and avoids anomalies by breakign down tables into efficient structures
• 1 Normal Form (1NF): (no multi vaules multiple vaules in a single column)
• 2 Second normal Form (2NF): no patial dependencies on a composite key
• 3 Third Normal Form (3NF) non- key column should not depend on antoher non-key colum

First Normal Form Rules

Multi-valued columns or duplicate rows are not permissable but each feild contains only one vaules and every ow is unique

Second Normal Form Rules

For every column in the primary key is a composition that should depend the values of the key

Third Normal Form Rules

• Is split into employee table, because of a non-key column that does not determine another non-key column

When do you NOT Normalization

• When queries can get faster, simpilier reports
• Balance is key! Normalize for Accuracy, Speed for denormalize

Normalization Perspective

• To aovid and update anamolies, this should be descompse into INF A movies table that has MOvieID, AND MOvieName that also contains a move cast table

Test your knowledge of database concepts, ER modeling, and DBMS functions. Explore relationships between entities and database characteristics.