Final Exam Review for ISOM 3260 Database Design and Administration

Willis WAN, Chun Yu

Posted 11 May 2019

Database

IS

ISOM

ISOM3260

Information Systems

This was my revision notes when I prepared for the final exam of ISOM 3260 Database Design and Administration. This is transcribed from my old website so the formatting might be off. The content might not be updated as well.
This is served as a reference only, and I have no intention to update this note.

Section 1 - Logical Database Design (Relational Database)

Relational Data Structure

Properties of Relations

  1. Each relation in a database must have a unique name.
  2. Each entry at the intersection of each row or column is atomic (or single valued). No multivalued attributes are allowed in a relation.
  3. Each row is unique.
  4. Each attribute within a table must have a unique name.
  5. The sequence of columns is insignificant.
  6. The sequence of wors is insignificant.

Integrity Constraints

Transforming (E)ER Diagrams Into Relations

Step 1: Mapping Strong Entities

Step 2: Mapping Weak Entities

Step 3: Mapping Binary Relationships

Step 4: Mapping Associative Entities

Step 5: Mapping Unary Relationships

Step 6: Mapping $n$-ary Relationships

Step 7: Mapping Supertype/Subtype Relationships

Well-structured Relations (Not Tested)

Normalization

Functional Dependencies

Candidate Keys

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Section 2 - Physical Database Design (Oracle)

Purpose of Physical Database Design

Physical Design Process

Designing Fields

Choosing Data Types

Controlling Data Integrity

Handling Missing Data

Designing Physical Database Files

Section 3A - Structured Query Language - Data Definition Language (SQL - DDL)

What is SQL?

Benefits of a Standardized Relational Language

Types of SQL Commands

Creating Tables (Using the CREATE Command)

CREATE TABLE {table_name} (
    {col_name}    {col_definition} [, ...]
    {constraints} [, ...]
);

Explanation

Example

CREATE TABLE department (
    deptID         VARCHAR2(10)     NOT NULL,
    deptName     VARCHAR2(100)     NOT NULL,
    CONSTRAINT department_PK PRIMARY KEY (deptID)
);
CREATE TABLE student (
    -- studentID are numbers but not for calculation
    -- so VARCHAR2 is used instead to save space
    sID         VARCHAR2(8)     NOT NULL,
    firstName     VARCHAR2(30)     NOT NULL,
    middleName     VARCHAR2(30),
    lastName     VARCHAR2(30)     NOT NULL,
    DOB         DATE             NOT NULL,
    -- Notice the student.deptID must have the same
    -- data type of department.deptID
    deptID         VARCHAR2(10)     NOT NULL,
    CONSTRAINT student_PK PRIMARY KEY (sID),
    CONSTRAINT student_FK FOREIGN KEY (deptID) 
                            REFERENCES department (deptID),
    CONSTRAINT student_CHECKNAME CHECK (lastName = UPPER(lastName))
);

Changing Table Definition (Using ALTER Command)

ALTER TABLE {table_name}
-- One of the following commands
[ADD {col_name} {col_definition}]
[ADD CONSTRAINT {constraint_name} {constraint_definition}]
[MODIFY {col_name} {changed_col_def}]
[DROP COLUMN {col_name}]
[DROP CONSTRAINT {constraint_name}]
[RENAME COLUMN {col_name} TO {new_col_name}];

Notes

Removing Tables (Using the DROP TABLE Command)

DROP TABLE {table_name};

Section 3B - Structured Query Language - Data Manipulation Language (SQL - DML) (Part 1)

Inserting Records (Using the INSERT INTO Command)

-- Inserting values from users
INSERT INTO {table_name} [({col_order})]
VALUES {val_order};
-- Inserting values from the result of a query
INSERT INTO {table_name}
{select_stmt};

Explanation

Deleting Records (Using the DELETE Command)

DELETE FROM {table_name}
[{where_condition}];

Updating Records (Using the UPDATE Command)

UPDATE {table_name}
SET {field_name} = {new_val} [, ...]
[{where_condition}];

Querying Records (Using the SELECT Command)

SELECT {column_names}
FROM {table_names}
[{where_condition}]
[GROUP BY {column_names}]
[HAVING {condtion}]
[ORDER BY {column_name}, ...];

Illustration - PRODUCT Relation

PRO_IDPRO_NamePRO_DescPRO_PricePRO_MemberDiscount
$\dots$$\dots$$\dots$$\dots$$\dots$
  1. Write a SQL statement to select all product information. 
     SELECT * FROM PRODUCT;
  2. Write a SQL statement to select all products which name starts with ‘Deluxe'. 
     SELECT * FROM PRODUCT
 WHERE PRO_Name LIKE 'Deluxe%';
  3. Write a SQL statement to select the product ID and product Name that a member cannot get a discount for. 
     SELECT PRO_ID, PRO_Name FROM PRODUCT
 WHERE PRO_MemberDiscount < 1;
  4. Write a SQL statement to select the product ID and the member price of the product, ordered by the member price in descending order. 
     SELECT PRO_ID, PRO_Price * PRO_MemberDiscount AS MemberPrice
 FROM PRODUCT
 ORDER BY PRO_Price * PRO_MemberDiscount DESC;

Using Aggregate Function in Query

COUNT()
MIN()
MAX()
SUM()
AVG()

Illustration - PRODUCT Relation

  1. Write a SQL statement to select the number of products in the system. 
     SELECT COUNT(*) AS totalProduct
 FROM PRODUCT;
  2. Write a SQL statement to select the lowest member price in the system. 
     SELECT MIN(PRO_Price * PRO_MemberDiscount) AS minMemberPrice
 FROM PRODUCT;
  3. Write a SQL statement to the number of products in the system, where the name of the product ends with ‘Deluxe'. 
     SELECT COUNT(*) AS totalEndDeluxe
 FROM PRODUCT
 WHERE PRO_Name LIKE '%Deluxe';

Using Wildcards

Using Boolean Operators

Using Ranges

SELECT ... FROM ...
WHERE {column_name} BETWEEN {start_val} AND {end_val};
-- Values are inclusive.

Using IN and NOT IN Clause

SELECT ... FROM ...
WHERE {column_name} [NOT] IN ({list_of_val});

Using DISTINCT

SELECT DISTINCT {column_name}
FROM {table_name}
...;

Illustration - PRODUCT Relation

  1. Write a SQL statement to select the product ID of all products with the word ‘Deluxe' (case-insensitive) in the name, and all ‘desk' product with the price lower than 200. 
     SELECT PRO_ID FROM PRODUCT
 WHERE LOWER(PRO_Name) LIKE '%deluxe%'
 OR LOWER(PRO_Name) LIKE '%desk%'
 AND PRO_Price < 200;
  2. Write a SQL statement to select the product ID of all product which provides 10% off or 20% off to members. 
     SELECT PRO_ID FROM PRODUCT
 WHERE PRO_MemberDiscount IN (0.8, 0.9);
  3. Write a SQL statement to select the product ID of all product which does not provide 50% off or 60% off to members. 
    SELECT PRO_ID FROM PRODUCT
WHERE PRO_MemberDiscount NOT IN (0.4, 0.5);

Aggregating Result with GROUP BY Clause and HAVING Clause

STU_IDSTU_FirstNameSTU_LastNameSTU_AdmittedYearMAJ_IDNAT_ID
$\dots$$\dots$$\dots$$\dots$$\dots$$\dots$
  1. Write a SQL statement to select the number of student in each major. Sort the result in descending order of the major first, then in ascending order with the major ID. 
    SELECT MAJ_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
GROUP BY MAJ_ID
ORDER BY COUNT(MAJ_ID) DESC, MAJ_ID [ASC];
  2. Modify the query in Example 11 to show majors that have over 150 students only. 
    SELECT MAJ_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
GROUP BY MAJ_ID
HAVING COUNT(MAJ_ID) > 150
ORDER BY COUNT(MAJ_ID) DESC, MAJ_ID [ASC];
  3. Modify the query in Example 11 to show majors except 'IS' and 'OM'. 
    -- Method 1: Filtering with WHERE
SELECT MAJ_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
WHERE MAJ_ID NOT IN ('IS', 'OM')
GROUP BY MAJ_ID
ORDER BY COUNT(MAJ_ID) DESC, MAJ_ID [ASC];

    -- Method 2: Filtering through HAVING
SELECT MAJ_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
GROUP BY MAJ_ID
HAVING MAJ_ID NOT IN ('IS', 'OM')
ORDER BY COUNT(MAJ_ID) DESC, MAJ_ID [ASC];
  4. Write a SQL statement to show the number of student in each major who is admitted in 2016. 
    SELECT MAJ_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
WHERE STU_AdmittedYear = '2016'
GROUP BY MAJ_ID;
  5. Write a SQL statement to show the number of student in each major from each country. Order by MAJ_ID in ascending order, then NAT_ID in ascending order. 
    SELECT MAJ_ID, NAT_ID, COUNT(MAJ_ID) AS totalStudent
FROM STUDENT
GROUP BY MAJ_ID, NAT_ID
ORDER BY MAJ_ID, NAT_ID;

    Section 3C - Structured Query Language - Data Manipulation Language (SQL - DML) (Part 2)

    Processing Multiple Tables

    • Join
    • a relational operation that temporarily combines two or more tables with common columns
    • the common columns in joined tables are usually the primary key of the dominant table and the foreign key of the dependent table. * Equi-join
    • based on equality between values in the common columns. * Natural join
    • an equi-join in which one of the duplicated common columns is eliminated in the result table. * Outer Join
    • a join in which rows that do not have matching values in common columns are nevertheless included in the result table.

Subquery

Illustration - STUDENT relation, MAJOR relation, DEPARTMENT relation, and NATIONALITY relation

STU_IDSTU_FirstNameSTU_LastNameSTU_AdmittedYearSTU_GPAMAJ_IDNAT_ID
$\dots$$\dots$$\dots$$\dots$$\dots$$\dots$$\dots$
MAJ_IDMAJ_NameMAJ_OfficeRoomDEP_ID
$\dots$$\dots$$\dots$$\dots$
DEP_IDDEP_NameDEP_HeadName
$\dots$$\dots$$\dots$
NAT_IDNAT_Name
$\dots$$\dots$
  1. Write a SQL statement to show the name of the student and the name of their major. 
     SELECT s.STU_FirstName, s.STU_LastName, m.MAJ_Name
 FROM STUDENT s, MAJOR m
 WHERE s.MAJ_ID = m.MAJ_ID;
  2. Write a SQL statement to show the name of the major and the number of student in each major. Only show those majors with more than 150 students in the system. 
     SELECT m.MAJ_Name, COUNT(s.STU_ID) AS totalStudent
 FROM MAJOR m, STUDENT s
 WHERE m.MAJ_ID = s.MAJ_ID
 GROUP BY m.MAJ_Name
 HAVING COUNT(s.STU_ID) > 150;
  3. Write a SQL statement to show the difference of GPA for each student against the average of all students. 
     SELECT STU_GPA - avgGPA diffGPA
 FROM STUDENT, (SELECT AVG(STU_GPA) avgGPA FROM STUDENT);
  4. Write a SQL to show the name of the student, and the name of the department head of their respective department. 
     SELECT s.STU_FirstName, s.STU_LastName, d.DEP_HeadName
 FROM STUDENT s, MAJOR m, DEPARTMENT d
 WHERE s.MAJ_ID = m.MAJ_ID
 AND m.DEP_ID = d.DEP_ID;
  5. Write a SQL statement to show the name of the department and the number of student in each department. Show only the department with over 200 students. Order by the number of student in descending order. 
     SELECT d.DEP_Name, COUNT(s.STU_ID) AS totalStudent
 FROM DEPARTMENT d, MAJOR m, STUDENT s
 WHERE d.DEP_ID = m.DEP_ID
 AND m.MAJ_ID = s.MAJ_ID
 GROUP BY d.DEP_NAME
 HAVING COUNT(s.STU_ID) > 200
 ORDER BY COUNT(s.STU_ID) DESC;
  6. Using a non-correlated subquery, select the name(s) of the student(s) who get(s) the highest GPA. 
     SELECT STU_FirstName, STU_LastName
 FROM STUDENT
 WHERE STU_GPA = (SELECT MAX(STU_GPA) FROM STUDENT);
  7. Using a correlated subquery, select the name(s) of the student(s) who get(s) the highest GPA. 
     SELECT STU_FirstName, STU_LastName
 FROM STUDENT a
 WHERE a.STU_GPA >= ALL (
     SELECT STU_GPA
     FROM STUDENT b
     WHERE b.STU_GPA != a.STU_GPA
 );
  8. Write a SQL statement to show all names of nationality, and the number of student in each nation. Show all nationalities regardless whether there are students in that nation or not. 
     SELECT n.NAT_Name, COUNT(s.STU_ID)
 FROM NATIONALITY n
 LEFT OUTER JOIN STUDENT s
 ON n.NAT_ID = s.NAT_ID
 GROUP BY n.NAT_ID;
  9. Using a non-correlated subquery, select the name of nationality which no students belong to. 
     SELECT NAT_Name
 FROM NATIONALITY
 WHERE NAT_ID NOT IN (
     SELECT DISTINCT NAT_ID
     FROM STUDENT
 );
  10. Using a correlated subquery, select the name of nationality which no students belong to. 
    SELECT NAT_Name
FROM NATIONALITY n
WHERE NOT EXISTS (
    SELECT * FROM STUDENT s
    WHERE s.NAT_ID = n.NAT_ID
);

Section 4 - Data and Database Administration

Importance of Data/Database Administration

Data/Database Administration

Data Administration Functions

Managing Data Security

Threats to Data Security

Data Management Software Security Features

Backing Up Databases

Backup Facilities

Journalizing Facilities

Recovery and Restart Procedures

Switch

Section 5 - Data Warehousing

Data and Competitve Advantage

Data Warehouse

Enterprise Data Warehouse

Data Mart

Independent Data Mart

Dependent Data Mart

Operational Data Store (ODS)

Logical Data Mart

Real-time Data Warehouse

The User Interface of a Data Warehouse

About the Author

Willis WAN, Chun Yu

A graduate in BBA(Information Systems) of HKUST. Tech Enthusiast, Teacher, Learner.

ICT

Copyright © 2021 All Rights Reserved