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