Exam Revision Checklist
CSE4DBF – 2020
EXAM REVISION Check List
TOPIC 2 and 3: Relational Data Model and ER/EER MODEL
PROPERTIES of an ER/EER Diagram
• ENTITY
• ATTRIBUTE
• RELATIONSHIP
• PRIMARY KEY
DEGREE of relationships
• UNARY
• BINARY
• TERNARY
EER special types of relationships:
• SUBCLASS/SUPERCLASS ENTITIES
• SPECIALIZATION/GENERALIZATION RELATIONSHIPS: disjoint and
overlapping
• UNION TYPES (CATEGORIES) RELATIONSHIPS
CONSTRAINTS of relationships
• CARDINALITY
• PARTICIPATION (TOTAL and PARTIAL)
SPECIAL CASES
• WEAK ENTITY
• MULTI-VALUED ATTRIBUTES
• DERIVED ATTRIBUTES
• Given a problem statement, understand how to represent the problem using an
ER/EER diagram. Make sure to identify all properties and constraints.
TOPIC 3 (cont): RELATIONAL DATABASE DESIGN FOR ER/EER
RELATIONAL DATABASE DESIGN
• The Steps:
Problem Specification -> ER/EER Diagram -> Transform To Relational
Schemas/Tables -> Implementation
• Transformation from ER/EER Diagram into Relational Schema (total 9 steps):
Step 1: Transform entities
Step 2: Transform weak entities
Step 3: Transform 1-1 relationships
Step 4: Transform 1-N relationships
Step 5: Transform M-N relationships
Step 6: Transform multi-valued attributes
Step 7: Transform n-ary (>2) relationships
Step 8: Transform Specialisation Generalisation relationships
(8a, 8b, 8c, and 8d)
Step 9: Transform Union type of relationships
TOPIC 4: NORMALIZATION
BOTTOM UP APPROACH using NORMALIZATION theory.
• Represent all user views (e.g forms, reports etc.) as a collection of relations.
• Normalize these relations, user view by user view.
• Combine all the relations which have the same primary keys.
• NORMALIZATION:
• UNF (Unnormalized Form): a relation with repetitions.
Example:
ORDER( Order #, Customer #, Customer Name, Customer
Address, City StatePostCode, Order Date,(Product #,
Description, Quantity, Unit Price))
• 1NF (First Normal Form):
- there are no repeating groups.
- a unique key has been identified for each relation.
- all attributes are functionally dependent on all or part of the key.
ORDER( Order#, Customer#, Customer Name, Customer Address,
CityStatePostCode, OrderDate)
ORDER_PRODUCT (Order#, Product#, Description, Quantity, Unit
Price)
• 2NF (Second Normal Form):
- the relation is in 1 NF
- no partial dependency: all non-key attributes are fully functionally dependent
on the entire key.
ORDER_PRODUCT(Order#, Product#, Quantity)
PRODUCT(Product#, Description, Unit_Price)
• 3NF (Third Normal Form):
- the relation is in 2 NF
- no transitive dependencies: non-key attribute dependent on another non-key
attribute.
ORDER (Order#, Customer#, Order Date)
CUSTOMER (Customer#, Customer Name, CustomerAddress,
CityStatePostCode)
ORDER_PRODUCT(Order#, Product#, Quantity)
PRODUCT(Product#, Description, Unit_Price)
• BCNF (Boyce Codd Normal Form):
- the relation is in 3 NF
- no non-key that determines partial key: any remaining anomalies that result
from functional dependencies have been removed.
BRANCH-CUSTOMER(CustomerNo, BranchNo, SalespersonNo,
VisitingFrequency, DateRelationshipEstablished)
The underlying reason for the normalization problems is that there is a dependency
etween SalespersonNo and BranchNo (SalespersonNo is a determinant of BranchNo).
The conversion of the relation to BCNF relations:
CUSTOMER-SALESPERSON (CustomerNo, SalespersonNo,
VisitingFrequency, DateRelationshipEstablished)
SALESPERSON (SalespersonNo, BranchNo)
TOPIC 5: RELATIONAL ALGEBRA
Relational Alge
a Operators:
1. PROJECTION
2. SELECTION
3. UNION
4. INTERSECTION
5. DIFFERENCE
6. PRODUCT
7. NATURAL JOIN
8. OUTER JOIN
• For all the above operators: understand the notations, how to use them
independently and in combination with other operators.
• Given a query written in natural language, write the relational alge
a expression
for it.
• Given a query written in relational alge
a on a given data set, understand the
output
esult of the query.
TOPIC 6: SQL
SELECT statement
• Simple Query
• Operators (<, >, =,
, >=, <=)
• Set membership (IN, NOT IN)
• Pattern Match Search (LIKE, NOT LIKE) with wildcard ‘%’
• Sorting Output (ORDER BY)
• Grouping Output (GROUP BY)
• Grouping Output with Restrictions (HAVING)
• SQL Aggregate Functions (count, avg, max, min, sum)
• Sub-queries or Nested Queries (with equality, with IN, with aggregate function)
• Simple Join
• Join using EXISTS or NOT EXISTS
• Outer Join (Left, Right, and Full)
• Combining result table (UNION, INTERSECT, EXCEPT)
INSERT statement
• INSERT all attributes
• INSERT particular attribute/s only
• INSERT from another table
UPDATE statement
• UPDATE one particular record/tuple
• UPDATE a number of selected records/tuples
DELETE statement
• DELETE all records in a table
• DELETE particular record/s only
VIEW
• How to define a View
• How to use a View
• Given a set of relations or tables, understand how to write queries in SQL. Make
sure to understand the different statements for data selection, insertion, update,
deletion, as well as view creation.
Additional Notes for TOPIC 6
Frequently Asked Question:
• When to use Sub-queries or Nested Queries (with equality, with IN, with aggregate
function), and when to use Join?
Answer:
Sub-Query is normally used when an aggregate operation or calculation needs to be
performed in order to get a comparison value which is then used to get the overall
esult from the outer query. For example: “Find the managers whose salaries greater
than the average manager salary”, or “Find the employees (from employee table) who
are not managers (from Department table)”.
The first query above requires a sub-query to calculate the average salary, whereas
the second query requires a sub-query to get a list of managers and then check each
employee who is NOT IN the list of managers.
SELECT E.Name
FROM Employees E
WHERE E.EmployeeID NOT IN
(SELECT ManagerID
FROM Department)
Join is used when we can perform a row-based merging or comparison. This means
for each row in one table, we perform a comparison with another row in another
table. For example, “Find the employees (from employee table) who are working in
department of computer science (from department table). Because department ID is a
foreign key in employees, we can perform a join based on this foreign key to find out
if the department name = computer science. We should also be aware of the
functionality of outer joins (left, right, full) and when to use them.
SELECT E.Name, E.Salary
FROM Employees E, Department D
WHERE E.DepartmentID = D.DepartmentID
AND D.DeptName = ‘Computer Science’;
TOPIC 7: STORED PROCEDURES (AND FUNCTIONS)
Stored Procedure General Syntax:
CREATE [OR REPLACE] PROCEDURE
[(parameter [{IN | OUT | IN OUT}] type,....,
parameter [{IN | OUT | IN OUT}] type)] AS
[local_variable_declarations]
BEGIN
procedure_body;
END ;
Stored Function General Syntax:
CREATE [OR REPLACE] FUNCTION
[(parameter [{IN | OUT | IN OUT}] type,....,
parameter [{IN | OUT | IN OUT}] type)]
RETURN
eturn type> IS
[local_variable_declarations]
BEGIN
function_body;
END ;
For this topic, it is important to understand:
• when to use a cursor and when to use simple local variables.
• the different ‘output’ of a stored procedure, eg. display to the screen, store results
into another table, etc.
• the ways to use SQL statements inside a stored procedure/function.
• For a stored function, how to display the output eg. using SQL statement or another
stored procedure.
TOPIC 8: TRIGGER
Trigger General Syntax:
CREATE [OR REPLACE] TRIGGER {BEFORE | AFTER | INSTEAD OF } {UPDATE | INSERT | DELETE}
[OF ] ON [FOR EACH ROW ]
[DECLARE ;]
BEGIN
trigger body goes here
END ;
Different types of triggers:
• Statement Trigger
• Row Trigger: with two context variables :old and :new
• Before and After Trigger
Questions to answer when you design a trigger:
• Which table/view that link to the trigger?
• Which operation that will fire the trigger?
• When will the trigger be fired?
• How many times will the trigger be fired?
• What operations will the trigger do?
For this topic, it is important to understand:
• the different ‘results’ of a trigger, eg. raise application e
or, store (back-up) old
values into another table, perform an instead of operation, perform automatic update
on particular attributes.
• how to produce a sequence and how to use it.
TOPIC 2 and 3: Relational Data Model and ER/EER MODEL
TOPIC 3 (cont): RELATIONAL DATABASE DESIGN FOR ER/EER
TOPIC 4: NORMALIZATION
TOPIC 5: RELATIONAL ALGEBRA
TOPIC 6: SQL
Additional Notes for TOPIC 6
TOPIC 7: STORED PROCEDURES (AND FUNCTIONS)
TOPIC 8: TRIGGER
Exam Revision Checklist
CSE4DBF – 2020
EXAM REVISION Check List
TOPIC 2 and 3: Relational Data Model and ER/EER MODEL
PROPERTIES of an ER/EER Diagram
• ENTITY
• ATTRIBUTE
• RELATIONSHIP
• PRIMARY KEY
DEGREE of relationships
• UNARY
• BINARY
• TERNARY
EER special types of relationships:
• SUBCLASS/SUPERCLASS ENTITIES
• SPECIALIZATION/GENERALIZATION RELATIONSHIPS: disjoint and
overlapping
• UNION TYPES (CATEGORIES) RELATIONSHIPS
CONSTRAINTS of relationships
• CARDINALITY
• PARTICIPATION (TOTAL and PARTIAL)
SPECIAL CASES
• WEAK ENTITY
• MULTI-VALUED ATTRIBUTES
• DERIVED ATTRIBUTES
• Given a problem statement, understand how to represent the problem using an
ER/EER diagram. Make sure to identify all properties and constraints.
TOPIC 3 (cont): RELATIONAL DATABASE DESIGN FOR ER/EER
RELATIONAL DATABASE DESIGN
• The Steps:
Problem Specification -> ER/EER Diagram -> Transform To Relational
Schemas/Tables -> Implementation
• Transformation from ER/EER Diagram into Relational Schema (total 9 steps):
Step 1: Transform entities
Step 2: Transform weak entities
Step 3: Transform 1-1 relationships
Step 4: Transform 1-N relationships
Step 5: Transform M-N relationships
Step 6: