ExamExam Revision Checklist CSE4DBF – 2020 EXAM REVISION Check List TOPIC 2 and 3: Relational...

Question

ExamExam 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  between 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 Algebra 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 algebra expression  for it.  • Given a query written in relational algebra on a given data set, understand the  output/result of the query.  TOPIC 6: SQL    SELECT statement • Simple Query  • Operators (, =, , >=,   [(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  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   > 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 error, 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:

Deepti · Accepted Answer

Q9. A. 
CREATE OR REPLACE PROCEDURE BidsDetail (itemNumber NUMBER)
AS CURSOR ItemCursor
SELECT auctionNumber, bidderUsername, bidAmount, bidDateTime
FROM Auction, Bid
WHERE itemNumber = Auction.itemNumber
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.

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

Answer To: Exam Revision Checklist CSE4DBF – 2020 EXAM REVISION Check List TOPIC 2 and 3: Relational Data Model...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment