Database Design Notes

DATABASE DESIGN CONTINUED

RECAP OF KEY QUESTIONS
  • Identify the entities (objects, individuals) in the organization.
  • Determine the relationships among these entities.
  • Identify the attributes (information) to be stored.
  • Understand the business rules that govern entity interactions.
  • Define integrity constraints arising from these rules.
  • Utilize an Entity Relationship Diagram (ERD) to represent these components.
CHEN'S NOTATION
  • Entity: e.g., Student
  • Attribute: e.g., GivenName, StudentNumber
  • Relationship: e.g., enrols
  • Cardinality: Defines the relationships in terms of numbers, such as 1-to-n.
EXAMPLE OF CHEN'S NOTATION
  • ERD includes entities like STUDENT and COURSE with various attributes:
    • STUDENT: studno, given, family name, YEARREG
    • COURSE: courseno, subject, equip
  • Relationships such as ENROL and TEACH define interactions between entities.
CROW'S FOOT NOTATION
  • Symbols and Their Meanings:
    • Mandatory-One (长)
    • Mandatory-Many (O+)
    • Optional-One (0)
    • Optional-Many (O)
TOOLS FOR DRAWING ERDS
  • Software options:
    • Microsoft Office Word
    • draw.io
    • lucidchart.com
    • creately.com
    • smartdraw.com
OBJECTIVES OF DATABASE DESIGN
  • Translate conceptual models into logical models.
  • Identify anomalies in database designs.
  • Understand the necessity of normalisation.
  • Define the normalisation process up to the third normal form (3NF).
LOGICAL DATABASE DESIGN
  • Transforms the conceptual design into a logical model focusing on:
    • Attributes
    • Cardinalities
    • Primary keys, foreign keys, and composite keys.
  • Emphasizes the relational model for its wide application.
RELATIONAL MODEL - BASIC DEFINITIONS
  1. Data Structure: Data organized in tables (rows and columns).
  2. Data Manipulation: Use of SQL for data manipulation.
  3. Data Integrity: Rules to maintain data integrity during operations.
RELATIONAL DATA STRUCTURE CONDITIONS
  • Each relation must have a unique name.
  • Entries must be atomic (no multi-valued attributes).
  • Each row must be unique.
  • Each column must have a unique name.
  • The order of rows and columns is insignificant.
INTEGRITY CONSTRAINTS
Domain Constraints
  • All column values must come from the same domain, defined by:
    • Data type
    • Length
    • Format
    • Range
    • Allowable values
  • Advantages:
    • Validates attribute values.
    • Conserves effort in describing attributes.
Entity Integrity/Null Value
  • Every relation must have a primary key with no null values.
  • NULL indicates absence of data and is not permissible in keys.
Referential Integrity/Foreign Keys
  • Defined to establish relationships between two tables.
  • The foreign key can be NULL or match a primary key.
MAPPING ERD TO RELATIONS
  • Process for transforming entities to relations:
    • Create relations from each entity type.
    • Skip derived attributes in relations.
    • Use attributes of the entity key as the primary keys.
ANOMALIES IN DATABASE DESIGN

Three types of anomalies:

  • Insertion Anomaly: Problems arise when new entries lack necessary data (e.g., student without adviser).
  • Deletion Anomaly: Deletion of data removes required information from the database.
  • Update Anomaly: Failing to update data leads to redundancy and inconsistencies.
NORMALIZATION PROCESS
  • Aim: Eliminate redundancy and ensure logical data dependencies.
  • Functional Dependency: Indicates relationships between attributes (e.g., via arrow notation).
First Normal Form (1NF)
  • Ensures atomic values with unique row identification.
Second Normal Form (2NF)
  • Achieved if it meets 1NF and all attributes depend on the primary key, without partial dependencies.
Third Normal Form (3NF)
  • Must satisfy 2NF; eliminates non-key dependencies.
EXAMPLES AND SOLUTIONS TO ANOMALIES
  • Use practical examples (e.g., tables with student and adviser data) to show how normalization corrects issues.
  • Transform relations step-by-step through 1NF, 2NF, and 3NF to illustrate the process.
SUMMARY
  • Focused on logical design, common anomalies, and the normalisation process to optimize database design.