Convex 225 Week 4: Database Design Phase 3 and Integrity Constraints

Administrative Announcements and Software Setup

  • Practical Assignment Submission Details

    • The practical assignment is due next week on Monday at 9:00 AM9:00\,AM.
    • Submission requirements include zipping all files and uploading them to Moodle.
    • Submissions are marked by accessing a specific URL provided by the student.
    • It is critical that web pages are accessible through the provided URL; failure to ensure accessibility results in a deduction of approximately 1010 points, which represents roughly 10%10\% of the overall grade for the assignment.
    • Students experiencing difficulties with URL accessibility should utilize lab timings for assistance from demonstrators or email the instructor as a last resort.
  • Accessing phpMyAdmin and MySQL

    • The primary tool for managing MySQL databases is the phpMyAdmin DBMS.
    • Accessing phpMyAdmin through the university's web server is restricted to the vicinity of the university.
    • Students working from personal computers outside the university should use the XAMPP software suite. Recordings by Nilesh are available on Moodle to guide the setup process on personal machines.
  • Lab Exercise 3

    • Lab Exercise 3 is currently available on Moodle.
    • The focus is Phase 2 of database design: transitioning from an Entity-Relationship (ER) diagram to a relational model.

Database Design Phase 3: From Relational Schema to MySQL Tables

  • The Five Steps of Phase 3

    1. Choosing data types for all columns.
    2. Writing CREATE TABLE statements (and determining the correct order of creation).
    3. Setting Primary Keys.
    4. Adding Foreign Keys.
    5. Defining Integrity Constraints.
  • Step 1: Choosing Data Types

    • Data types must be assigned to every column in the relational schema.
    • Student Table Columns and Types:
      • studentID: int
      • firstName: varchar
      • lastName: varchar
      • gender: char(1)
      • DOB: date
      • staffID (Foreign Key): int
      • passportNumber (Foreign Key): varchar(10)
    • Lecturer Table Columns and Types:
      • staffID: int
      • firstName: varchar
      • lastName: varchar
    • Passport Table Columns and Types:
      • passportNumber: varchar(10)
      • visa: char(1) (intended for 'Y' or 'N' values).
      • issueDate: date
  • Step 2: Table Creation Order

    • The order of table creation is dictated by dependencies. Tables that do not contain foreign keys (parent tables) must be created before tables that reference them (child tables).
    • In the provided schema, the lecturer and passport tables must be created before the student table because the student table contains columns that reference the other two.
  • Step 3: Setting Primary Keys

    • There are two methods for declaring primary keys:
      • Inline Declaration: Adding the keyword PRIMARY KEY directly on the line where the column and data type are defined.
      • Table Level Declaration: Defining the primary key at the end of the CREATE TABLE statement.
    • In simple schemas, inline declaration is often the most appropriate and easy to use.

Foreign Keys and Referential Integrity

  • Definition and Characteristics of Foreign Keys

    • A foreign key is used to link two tables together by pointing to a key in another table, representing a relationship.
    • The primary purpose of a foreign key is to enforce referential integrity.
    • Referential integrity ensures that a value in a child table must exist in the parent table first. For example, a courseID in a student_enrollment table must correspond to a valid entry in the course table.
  • Constraints: Primary Key vs. Foreign Key

    • Primary Keys: Must be unique and cannot be null (NOT NULLNOT\,NULL).
    • Foreign Keys: Do not need to be unique and can be null.
  • Syntax for Foreign Keys

    • The column representing the foreign key must first be included in the list of column definitions.
    • The constraint is declared using: FOREIGN KEY (column_name) REFERENCES parent_table(parent_column_name).
    • The foreign key column name in the child table does not have to match the column name in the parent table.

Integrity Constraints

  • Overview of Constraints

    • Integrity constraints are restrictions added to columns regarding values, ranges, or other logic to ensure data is valid and consistent with business requirements.
    • There are four main types:
      1. Entity Integrity: Pertains to the table level, specifically the requirement of a Primary Key.
      2. Referential Integrity: Pertains to relationships between tables via Foreign Keys.
      3. Domain Integrity: Defined at the column level to restrict the "domain" of acceptable values.
      4. User-Defined Integrity: Complex constraints covered in the second half of the course.
  • Domain Integrity Constraints

    • NOT NULL: Prevents a column from being empty.
    • UNIQUE: Prevents duplicate values in a column that is not necessarily a primary key.
    • DEFAULT: Sets a fallback value if no data is entered for that column (Syntax: DEFAULT = value).
    • ENUM: Similar to an HTML dropdown, it limits input to a predefined list of acceptable strings. Data types like varchar are not needed when using ENUM because the list defines the type.
    • CHECK: Uses a condition to restrict data (e.g., numeric ranges or string patterns).

The CHECK Clause and Pattern Matching

  • Syntax and Best Practices

    • A simple declaration: CHECK (condition).
    • Recommended Practice: Use CONSTRAINT name CHECK (condition). Naming the constraint (e.g., CKID) ensures that if an insertion fails, the error message specifically identifies which constraint was violated, rather than providing a generic error like "Constraint 1 has failed."
  • Numeric Pattern Matching

    • Constraints can use standard operators: age >= 18 AND age <= 28.
    • The BETWEEN keyword is a cleaner alternative: studentID BETWEEN 1000 AND 1999.
    • The BETWEEN operator is inclusive of the values provided.
  • String Pattern Matching: LIKE vs. REGULAR EXPRESSION (REGEXP)

    • LIKE is used for simple pattern matching with two specific symbols:
      • % (Percentage): Represents zero or more characters. Example: uni% matches "uni", "union", or "university".
      • _ (Underscore): Represents exactly one single character. Example: ta_e matches "take", "tame", or "tape".
    • REGEXP (Regular Expression) is used for complex pattern matching with various symbols:
      • . (Period): Represents a single character (equivalent to underscore in LIKE).
      • [] (Square Brackets): Matches any character within the brackets. Example: [ab] matches "a" or "b". [j-l] matches any character from "j" to "l". [0-9] matches any digit.
      • ^ (Caret): Asserts the start of a string. Example: ^a means the value must start with "a".
      • ^ (inside brackets): Negates the range. Example: [^0-9] means any value that is not a digit.
      • </code>(DollarSign):Assertstheendofastring.Example:<code>a</code> (Dollar Sign): Asserts the end of a string. Example: <code>a means the value must end with "a".
      • * (Asterisk): Matches zero or more occurrences of the preceding character.
      • {} (Curly Brackets): Specifies the exact number of repetitions. Example: [0-9]{3} means three digits must appear.
  • Case Sensitivity and the BINARY Keyword

    • By default, MySQL string patterns are not case-sensitive (e.g., [A-Z] will accept cd).
    • To enforce case sensitivity, the BINARY keyword must be added to the constraint declaration.

Questions & Discussion

  • Question: If an insert violates multiple check clauses, does it show you all of them or just the first one?

  • Response: This will be tested and addressed in the next lecture.

  • Question: Is the BETWEEN operator inclusive or exclusive?

  • Response: It is inclusive. For example, BETWEEN 1000 AND 1999 will accept both 10001000 and 19991999.