ISOM 3260 - Database Design and Administration
Final Exam Review

To access the final exam practice questions, press here.

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 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_ID PRO_Name PRO_Desc PRO_Price PRO_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

Illustration - STUDENT Relation

STU_ID STU_FirstName STU_LastName STU_AdmittedYear MAJ_ID NAT_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

Subquery

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

STU_ID STU_FirstName STU_LastName STU_AdmittedYear STU_GPA MAJ_ID NAT_ID
\dots \dots \dots \dots \dots \dots \dots

MAJ_ID MAJ_Name MAJ_OfficeRoom DEP_ID
\dots \dots \dots \dots

DEP_ID DEP_Name DEP_HeadName
\dots \dots \dots

NAT_ID NAT_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

Restore/Rerun

Forward Recovery (rollforward)

Backward Recovery (rollback)

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