Database Relational Model & SQL Advanced Flashcards -L4

Database Design Process

  • Introduction and overview
    • Data vs. Information
    • Data stored in databases
    • Database management system (DBMS)
    • Database design defines database structure
    • The next big thing: Big Data
  • Conceptual Model
    • Entity-Relationship Modelling technique
    • Chen’s notation can be good for high-level conceptual model initially
    • Crow’s Foot is the design standard in the textbook and this course
    • Entity type and entity instance
    • Attribute and value
    • Relationship (Degree, Connectivity, Cardinality)
  • Logical Model
    • Converting Conceptual model to detailed Logical Model (using Crow’s Foot) ready for DB implementation
    • Advanced topics
      • Relationship strength
      • Composite entity
      • Relationship degree
      • Supertype and Subtype
      • Selecting Primary Key
  • Relational Model
    • Convert ER model to a set of tables (relations) in the relational model
    • Apply Normalisation on the relations to remove any anomalies
  • SQL Data Definition & Manipulation Language
    • Use Relational Model to implement database by creating a table for each normalised relations
    • Data Definition Language defines the tables
    • Data Manipulation Language queries/updates the tables

Relational Model Advanced

  • Where to place FK for a relationship?
  • Misleading / wrong relationships

SQL Advanced: Functions

  • Date Functions
  • Numeric Functions
  • String Functions
  • Conversion functions

Converting Logical Model to Relational Model

  • Make sure logical model had taken care of composite attributes and multi-value attributes
  • Translate each entity (including original entity, child entity and composite entity) into a relation (table)
  • The attributes of the entity become the attributes of relation
  • Translate relationships to FK links (more details on the next page)
  • Preferable to use only these component attributes as columns
    • Composite Attributes
      • Customer_Address
        • Street
        • City
        • State
        • Post Code
    • CUSTOMER
      • Customer_ID
      • Customer_Name
      • Street
      • City
      • State
      • Post Code

Foreign Key Placement in 1:M Relationship

  • The PK of PAINTING is PAINTINGNUM; it is not derived from the PK of PAINTER (PAINTERNUM)
  • PAINTING is a strong entity (it has its own unique identifier)
  • In a 1:M relationship, we place the PK of the "1" side (parent entity) as a Foreign Key in the "M" side (child entity)
  • What if we try to put the primary key of the M side on the 1 side as FK?
    • Multi-value attributes
      • PAINTER (PAINTER_NUM,…)
      • PAINTING (PAINTINGNUM, PAINTERNUM)

1:1 Non-Recursive Relationship with Mandatory and Optional Sides

  • Business rule: one EMPLOYEE is the head of one DEPARTMENT, and one DEPARTMENT is managed by one EMPLOYEE.
  • Which side is mandatory/optional in the relationship?
    • An EMPLOYEE may or may not head a department
    • A DEPARTMENT must be linked to 1 EMPLOYEE as the head
  • We place FK on the optional side DEPARTMENT
  • Shall we place a foreign key in both entities and make two-way references? No
    • duplicates data
    • Conflicts with another 1:M relationship: one department employs many employees (EMPLOYEE’s home department id shall be FK referencing DEPARTMENT’s PK)
    • EMPLOYEE (EMPLOYEE_ID,…)
    • DEPARTMENT (DEPARTMENTID, HEADID, …)

Other Scenarios of 1:1 Non-Recursive Relationship

  • Business rule
    • Employees may or may not have an assigned parking space.
    • Parking spaces may or may not be assigned to an employee.
    • If assigned, one employee gets exactly one parking space, and one parking space belongs to exactly one employee.
  • Are both sides optional in the relationship? Yes
  • Which side shall host the FK?
    • Select the FK that causes less nulls (empty values)
    • If putting FK on either side causes similar count of nulls, place the FK in the entity that "belongs" to the other entity, e.g., PARKING_LOT "belongs" to the employee based on common sense
  • What if both sides are mandatory in the relationship?
    • Ensure that the two entities do not belong together in a single entity! Review your entities!

Maintaining History of Time-Variant Data

  • To track salary histories for each employee, EMP_SALARY would become multivalued
  • new entity in a 1:M relationship with the original entity
  • 1 or more records in SALARY_HIST for each employee
  • FK is in the new entity, referencing PK of EMPLOYEE
  • EMPLOYEE (EMP_NUM, …)
  • SALARYHIST(EMPNUM, SALARYSTARTDATE,…)

Design Trap: Inconsistent Relationship with Real World (1/2)

  • “incomplete” business rules
    • Basketball league has many divisions
    • Each division has many players
    • Each division has many teams
  • Misleading ERD
    • DIVISION is in a 1:M relationship with TEAM and in a 1:M relationship with PLAYER
    • Seems that every player is linked to every team!
    • But we need to identify which players belong to which team!
  • Most common: fan trap - one entity in two 1:M relationships to other entities, leading to a link between the other two entities

Design Trap: Inconsistent Relationship with Real World (2/2)

  • Corrected ERD
    • DIVISION is in a 1:M relationship with TEAM
    • TEAM is in a 1:M relationship with PLAYER
    • Able to identify which players belong to which team
  • Let’s remove the fan trap…
    • DIVISION (DIV_ID, …)
    • TEAM(TEAMID, DIVID,…)
    • PLAYER(PLAYERID, TEAMCODE,…)

Redundant Relationships

  • Redundant relationships are seldom good - multiple relationship paths between entities
  • We can determine the player’s division through the TEAM entity
  • The relationship between DIVISION and PLAYER is redundant, and can be deleted safely
  • MGR_HIST entity is about the history of department heads
  • It has a 1:M relationship with EMPLOYEE and a 1:M relationship with DEPARTMENT
  • An employee could be the head of many different departments over time
  • “manages” dashed link is redundant; but justified if we really need a quick way to identify current heads of each department

Recap: Placing Foreign Keys

  • Placing Foreign Key in
    • 1:M relationship
    • 1:1 non-recursive relationship
      • One side optional, one side mandatory
      • Both sides optional
      • Both sides mandatory
  • Maintaining history of time-variant data
  • Design trap (most commonly fan trap)
  • Redundant relationships

SQL Basic: Logical (Boolean) Operators

  • OR: the entire condition is true, if either one of the conditions is true

  • AND: the entire condition is true, only if both conditions are true

  • NOT: opposite of a condition - true becomes false; false becomes true

  • List products where the vendor code is ‘21344’ or ‘24288’

    SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT WHERE V_Code = 21344 OR V_Code = 24288;

  • List products where either ‘the product indate is after July 15, 2015 and the product price is less than 50.00’ or the vendor code is 24288

    SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT WHERE (P_Price < 50 AND P_Indate > TO_DATE('1999-09-15', 'YYYY-MM-DD')) OR V_Code = 24288;

SQL Basic: Special Operators in Oracle

  • IS NULL: check whether an attribute value is null.

  • IS NOT NULL: check whether an attribute value is NOT null.

  • BETWEEN: define range limits.

  • LIKE: check for similar character strings.

  • IN: check whether an attribute value matches a value contains within a subset of listed values.

  • List the products with prices between 50 and 100

    SELECT * FROM PRODUCT WHERE P_Price BETWEEN 50.00 AND 100.00;

    SELECT * FROM PRODUCT WHERE P_Price >= 50.00 AND P_Price <= 100.00;

  • List the details of all vendors whose last name begins with “Smith”:

    SELECT V_Name, V_Contact, V_AreaCode, V_Phone FROM VENDOR WHERE V_Contact LIKE 'Smith%';--% is wild card

  • List the contents of the product table where the product price is exactly $50 or $100:

    SELECT * FROM PRODUCT WHERE P_Price IN (50.00, 100.00);

  • List the details of products with existing (not- NULL) vendor codes:

    SELECT * FROM PRODUCT WHERE V_Code IS NOT NULL;

SQL Basic: Order BY

  • sort by a column or a combination of columns

  • List the details of product table listed by product price in ascending order (default)

    SELECT * FROM PRODUCT ORDER BY P_Price;

  • List the details of products with an in-date before 15 September 1999 and a price less than A$50. Put the results in ascending (default) order of vendor code and descending order of price.

    SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT WHERE P_Indate < TO_DATE('1999-09-15', 'YYYY-MM-DD') AND P_Price < 50 ORDER BY V_Code, P_Price DESC;

Single Row Functions

  • Built-in in most database environments (including Oracle)
  • Produce a single result value for each row based on calculations, transformations, or operations.
  • Examples:
    • Date functions, e.g., MONTHSBETWEEN, ADDMONTHS, SYSDATE
    • Numeric functions, e.g., ABS, ROUND
    • Conversion functions, e.g., TO_CHAR
    • String functions, e.g., UPPER, SUBSTR, TO_CHAR, LENGTH

SQL Advanced: Date Functions (1/2)

  • Get the current system date (today's date)

    SELECT SYSDATE AS current_date FROM DUAL;

  • Convert a string into a DATE using TO_DATE, for comparison

    SELECT employee_id, first_name, last_name, hire_date FROM employees where hire_date > TO_DATE('15-04-2008', 'DD-MM-YYYY');

  • Calculate the number of months between today's date and hire_date

    SELECT employee_id, hire_date, round(MONTHS_BETWEEN(SYSDATE, hire_date)) AS months_worked FROM employees;

  • SYSDATE → Today’s current date, as per your computer system

  • TO_DATE → convert a character string to DATE type by a certain format

  • MONTHS_BETWEEN → Months difference between two dates

SQL Advanced: Date Functions (2/2)

  • Find employees hired in the last 12 months

    SELECT employee_id, hire_date FROM employees WHERE hire_date >= ADD_MONTHS(SYSDATE, -12);

  • Find the next Monday after the hire_date for HR meeting

    SELECT employee_id, hire_date, NEXT_DAY(hire_date, 'Monday') AS next_monday_after_hire FROM employees;

  • ADD_MONTHS → Add a set number of months to a date field

  • NEXT_DAY → The next given weekday after the given date

SQL Advanced: Numeric Functions

  • We can use the system table ‘DUAL’ (automatically created in Oracle) to do miscellaneous calculations.

  • Find the round of 0.4556

    SELECT ROUND(0.4556, 2) FROM DUAL;

  • Truncate 0.4556 to 2 decimal places, not rounding

    SELECT TRUNC (0.4556, 2) FROM DUAL;

  • Find the remainder of 4 divided by 3

    SELECT MOD(4, 3) FROM DUAL;

  • Calculate 2^3

    SELECT POWER(2, 3) FROM DUAL;

  • ROUND → Round numeric fields to the stated precision

  • TRUNC → Delete numeric points to the stated precision

  • MOD → Remainder of a division operator

  • ABS → Absolute value of a field

  • POWER → Statistical power

  • You can think about actual use case of these functions in the real-world datasets

SQL Advanced: Conversion Functions (1/2)

  • If sales quantity is above 2, it is popular

  • If exactly 2, it is good;

  • otherwise, fair

    SELECT ISBN, QUANTITY, CASE WHEN QUANTITY > 2 THEN 'popular' WHEN QUANTITY = 2 THEN 'good' ELSE 'fair' END as "Comment based on sales quantity" FROM ZJLB_ORDERITEMS;

  • CASE → different operations depending on conditions

SQL Advanced: Conversion Functions (2/2)

  • Convert PHONE_NUMBER (with all '.' removed) to a numeric format

    SELECT EMPLOYEE_ID, TO_NUMBER(replace(PHONE_NUMBER,'.','')) AS PHONE_AS_NUMBER FROM employees WHERE PHONE_NUMBER IS NOT NULL;

  • Format HIRE_DATE as 'YYYY-mm-dd'

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY-mm-dd') AS HIRE_DATE_FORMATTED FROM employees;

  • Format SALARY with commas and currency symbol

    SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,  TO_CHAR(SALARY, '$999,999.00') AS SALARY_FORMATTED FROM employees;

  • TO_NUMBER → Convert a string to a number field (useful for arithmetic calculations)

  • TO_CHAR → Use to format values (e.g., convert dates and numbers)

    • TOCHAR(HIREDATE, 'YYYY') would extract the year
    • TOCHAR(HIREDATE, 'mm') would extract the month
    • TOCHAR(HIREDATE, 'dd') would extract the day

SQL Advanced: String Functions (1/5)

  • LOWER → returns a string with all characters converted to lowercase.

  • UPPER → returns a string with all characters converted to uppercase.

  • INITCAP → converts the first letter of every word to uppercase, and every other letter to lowercase.

  • Run query on oracle database with given mock data

    SELECT  employee_id, upper(last_name),  initcap(first_name),lower(email) FROM  employees;

SQL Advanced: String Functions (2/5)

  • find all phone numbers with 44 as regional code

    SELECT * FROM employees WHERE substr(phone_number, 5, 2) = '44';

  • find all phone numbers with 44 starting from position 5 (count starts from 1)

    SELECT * FROM employees WHERE INSTR(phone_number, '44') = 5;

  • find all employees with very long last names

    SELECT * FROM employees WHERE length(last_name) > 9;

  • SUBSTR → Extract a portion of the string

  • INSTR → Return the numeric position of a substring within the string

  • LENGTH → no. of characters in the character string

SQL Advanced: String Functions (3/5)

```latex
SELECT department_id from DEPARTMENTS;
```

  • take note that some are 2 char

  • some have 3 chars

  • Make all department codes 5 characters padded with 0, for invoices to look neat

    SELECT LPAD(department_id, 5, '0') from DEPARTMENTS;

  • LPAD → Left-pad a string with another string, to a certain length

  • RPAD → Right-pad a string with another string, to a certain length

SQL Advanced: String Functions (4/5)

  • in case employees type in email with spaces at both ends

    select trim(email) from employees;

  • trim spaces only on the left side of email

    select ltrim(email) from employees;

  • remove all '0' and '.' from left side

  • This stops at the first char that is not '0' or '.'

    select ltrim(phone_number,'.0') from employees;

  • TRIM → Removes leading & trailing spaces from both ends

  • LTRIM

    • Remove leading spaces from left side only
    • Remove all occurrences of selected characters from the left-hand side of the string

  • RTRIM → Same as LTRIM, but from the right-hand side

SQL Advanced: String Functions (5/5)

  • replace ‘IT’ in department names with Tech: rebranding

    SELECT department_name, replace(department_name, 'IT', 'Tech’)  from DEPARTMENTS;

  • Find employees whose last name sounds like "smyth"

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM employees WHERE SOUNDEX(LAST_NAME) = SOUNDEX('smyth’);

  • REPLACE → Substitute a string with another string

  • SOUNDEX → Use to find values based on their “sound”

SQL Advanced: Other Useful Functions

  • Replace NULL COMMISSION_PCT values with default 5%

    SELECT EMPLOYEE_ID, FIRST_NAME,  NVL(COMMISSION_PCT, 0.05) AS DEFAULT_COMMISSION FROM employees;

  • Display different messages based on whether COMMISSION_PCT is NULL

    SELECT EMPLOYEE_ID, FIRST_NAME,  NVL2(COMMISSION_PCT, 'Has Commission', 'No Commission’)   AS COMMISSION_STATUS FROM employees;

  • NVL → Substitute a value for any NULLS; valid values stay as they are

  • NVL2 → Substitute a value for any NULLS, and a value for any fields that are not null

Recap and Next Lecture

  • Relational Model Advanced
    • Placing Foreign Key in
      • 1:M relationship
      • 1:1 non-recursive relationship
    • Maintaining history of time-variant data
    • Design trap (most commonly fan trap)
    • Redundant relationships
  • SQL Advanced: Functions
    • Date Functions
    • Numeric Functions
    • String Functions
    • Conversion functions
  • Next lecture Recap
    • Introduction to Normalisation
    • SQL advanced continued