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

1 answer below »
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:
Answered Same Day Jun 22, 2021 CSE4DBF La Trobe University

Solution

Deepti answered on Jun 23 2021
132 Votes
Q9. A.
CREATE OR REPLACE PROCEDURE BidsDetail (itemNumber NUMBER)
AS CURSOR ItemCurso
SELECT auctionNumber, bidderUsername, bidAmount, bidDateTime
FROM Auction, Bid
WHERE itemNumber = Auction.itemNumbe
B.
CREATE FUNCTION AvgRatingScale (sellerUsername)
RETURN Number(11,2);
BEGIN
    DECLARE userName char(20);
FETCH sellerUsername INTO username;
    SELECT AVG(RatingGiven) INTO AvgRating FROM Auction
WHERE Item.itemNumber = Auction.itemNumber AND Username=Item.sellerUsername
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

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here