Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# 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...

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)
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
• 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.

• When to use Sub-queries or Nested Queries (with equality, with IN, with aggregate
function), and when to use Join?
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
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:
Answered Same Day Jun 22, 2021 CSE4DBF La Trobe University

## Solution

Deepti answered on Jun 23 2021
Q9. A.
CREATE OR REPLACE PROCEDURE BidsDetail (itemNumber NUMBER)
AS CURSOR ItemCurso
FROM Auction, Bid
WHERE itemNumber = Auction.itemNumbe
B.
RETURN Number(11,2);
BEGIN
SELECT AVG(RatingGiven) INTO AvgRating FROM Auction
GROUP BY itemNumber;
RETURN (AvgRating);
END;
C.
CREATE OR REPLACE TRIGGER trgPreventUpdate
BEFORE INSERT OR UPDATE ON Item
For Each Row
BEGIN
If new.itemNumber = Item.itemNumbe
dbms_output.put(‘Update Prevented. Item has existing Bid’)
End If;
END
Q8.
A.
CREATE VIEW DoctorRefe
edPatients
AS
SELECT DoctorName, Count(DoctorNo)...
SOLUTION.PDF