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 .
- 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 points, which represents roughly 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
- Choosing data types for all columns.
- Writing
CREATE TABLEstatements (and determining the correct order of creation). - Setting Primary Keys.
- Adding Foreign Keys.
- 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:intfirstName:varcharlastName:varchargender:char(1)DOB:datestaffID(Foreign Key):intpassportNumber(Foreign Key):varchar(10)
- Lecturer Table Columns and Types:
staffID:intfirstName:varcharlastName: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
lecturerandpassporttables must be created before thestudenttable because thestudenttable 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 KEYdirectly on the line where the column and data type are defined. - Table Level Declaration: Defining the primary key at the end of the
CREATE TABLEstatement.
- Inline Declaration: Adding the keyword
- In simple schemas, inline declaration is often the most appropriate and easy to use.
- There are two methods for declaring primary keys:
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
courseIDin astudent_enrollmenttable must correspond to a valid entry in thecoursetable.
Constraints: Primary Key vs. Foreign Key
- Primary Keys: Must be unique and cannot be 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:
- Entity Integrity: Pertains to the table level, specifically the requirement of a Primary Key.
- Referential Integrity: Pertains to relationships between tables via Foreign Keys.
- Domain Integrity: Defined at the column level to restrict the "domain" of acceptable values.
- 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 likevarcharare not needed when usingENUMbecause 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."
- A simple declaration:
Numeric Pattern Matching
- Constraints can use standard operators:
age >= 18 AND age <= 28. - The
BETWEENkeyword is a cleaner alternative:studentID BETWEEN 1000 AND 1999. - The
BETWEENoperator is inclusive of the values provided.
- Constraints can use standard operators:
String Pattern Matching: LIKE vs. REGULAR EXPRESSION (REGEXP)
LIKEis 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_ematches "take", "tame", or "tape".
REGEXP(Regular Expression) is used for complex pattern matching with various symbols:.(Period): Represents a single character (equivalent to underscore inLIKE).[](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:^ameans the value must start with "a".^(inside brackets): Negates the range. Example:[^0-9]means any value that is not a digit.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 acceptcd). - To enforce case sensitivity, the
BINARYkeyword must be added to the constraint declaration.
- By default, MySQL string patterns are not case-sensitive (e.g.,
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
BETWEENoperator inclusive or exclusive?Response: It is inclusive. For example,
BETWEEN 1000 AND 1999will accept both and .