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

1 answer below »
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 (<,>, =, , >=, <=)> (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 result 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 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.



Answered 28 days AfterMay 18, 2022

Answer To: CSE4DBF – 2020 EXAM REVISION Check List TOPIC 2 and 3: Relational Data Model and ER/EER MODEL ...

Sparsh answered on Jun 16 2022
75 Votes
Relational Data Model and ER/EER Model
ER Diagrams
The name ER diagram or entity relationship diagram, comes from the fact that it depicts the relationships between entities. One of the most important applications of ER diagrams is to arrange data within databases.
ER diagrams are divided into two types: conceptual and physical. As a foundation for data-model integration, conceptual diagram models can offer the framework for logical data models or highlight commonality linkages amongst ER models.
EER Diagram
EER diagrams are a more comprehensive version of ER diagrams. For creating high-level database models, EER models are useful. You may plan databases more fully employing their expanded capabilities by digging deeper into the traits and restrictions with more accuracy.
All of the components of an ER diagram are also present in an EER diagram:
· Connection inheritance.
· Attribute inheritance.
· Union/Categories Types
· Generalization and spec
ialization
· There are two sorts of classes: subclasses and superclasses.
In general, an EER diagram extends an ER diagram by adding features that allow for aggregation, generalization, and specialization.
Generalization and specialization are diametrically opposed. The process of integrating lower-level entities into a higher-level entity is called generalization. Specialization, on the other hand, splits high-level entities into smaller ones. When two entities are aggregated, they are handled as if they were one.
Properties of ER/EER Diagram
· Entities - These are crucial data-representing objects or concepts. These are also known as strong / parent entities, frequently have weak entities that rely on them.
· Attributes - The attributes of an entity is defined as its distinguishing qualities (i.e., many-to-many or one-to-one).
· Relationship - Relationships may be defined as the connections that exist between two or more entities.
· Primary Key - The column or columns containing values that uniquely identify each row in a table are known as primary keys.
Degree Of Relationships
· Unary - In this type of relationship both the associating entity types are identical. We can raise a point that there is an existence of unary relationships because both entity types are the same, and the degree of relationship is one. A unary relationship, in other words, is one in which just one entity set is involved.
· Binary - A Binary connection has two different types of entity associates. We call a binary relationship between two types of entities when the degree of relationship is two and there are two kinds of entities. To put it another way, a binary relationship occurs when two entity sets are involved in a relationship. This is the most common type of relationship, and creating a relational table from it is simple.
· Ternary - In a Ternary connection, there are three types of entity associates. We call a Ternary relationship when there are three types of entities, and the degree of relationship is three. Converting E-R to a relational table becomes more challenging as the number of entities increases. Now let's look at a few examples.
ERR Special Types of Relationships
· Specialization/Generalization Relationships - We've looked at the different types of relationships that might exist between objects. A hierarchy of relationships exists in some entities. A shipping company, for example, may employ a variety of ship types. The relationship between the concept of a ship and the different types of ships creates a hierarchy. It's referred to as a "superclass" ship. The many kinds of ships are divided into subclasses.
· Superclass - A generic concept is represented by a high-level entity type.
· Subclass - An idea is represented by a lower-level entity type.
· A superclass is stated to be the parent of a subclass. Many superclasses can be inherited by a subclass in the hierarchy. When a subclass inherits from one or more superclasses, all of their attributes are passed down to it. A subclass can declare its own special properties in addition to the inherited ones. A subclass inherits membership in the relationship set of which its superclass is already a member of.
· Generalization is the process of creating a superclass from a collection of subclasses. Specialization is the process of creating subclasses from a general notion.
· Specialization - A method for discovering sub-groups of entities inside an entity collection that have traits not shared by all of the entities (top-down).
· Generalization - Multiple sets of entities are combined to form a higher-level entity set based on shared characteristics (bottom-up).
Disjoint Constraints
The disjoint requirement applies when a superclass has many subclasses. An entity occurrence can only belong to one of the subclasses if the subclasses are distinct., e.g. postgrads or undergrads; you can't be in both. †Orâ€TM is used to express a discontinuous superclass/subclass connection.
Overlapping Constraints
This is true when an entity occurrence belongs to more than one subclass, such as student and staff – some persons are members of both. In the ER diagram, †Andâ€TM is used to indicate the overlapping specialization/generalization relationship.
Constraints of Relationships
· Cardinality - Cardinality is an important factor in database administration. Cardinality may be defined as the number of times an entity from one set participates with another set. The number of tuples (rows) in a relationship is called cardinality. The examples of Cardinality between tables are given below.
· One-to-One - This is the type of entity mapping in which each entity in A can only be linked to one entity in B. We may also say that each unit or item in B is connected to only one unit or item in A.
· One-to-Many - This is the type of Cardinality mapping in which an entity from A is linked to any number of entities from B. In other words, only one unit or item in B can be connected to one unit or thing in A.
· Many-to-One - This is the type of Cardinality mapping in which each entity in A can only be linked to one entity in B. To put it another way, a unit or item in B can be linked to any number (zero or more) of entities or objects in A.
· Many-to-Many - In this type of cardinality mapping, an entity in A is linked to an unlimited number of entities in B, and an entity in B is linked to an unlimited number of entities in A.
· Total Participation Constraint - It stipulates that each entity from the entity set must engage in at least one relationship instance of that particular relationship set, and is thus referred to as mandatory participation. Between the entity set and the relationship set, a double line is used to indicate it. Example of Total Participation Constraint is
· It states that each student must be registered in at least one course, with "student" as the entity set and "enrolled in" as the connection indicating total involvement.
· This means that each student must have taken at least one course
· Partial Participation Constraint - Each entity available in the entity set can choose whether to participate in the relationship instance of the relationship set or not. A single line connecting the entity and relationship sets is illustrated in the ER diagram. Partial Participation Constraints can be shown in the following example.
· A single line between the entities in a connection, such as courses and enrolled, denotes partial participation, which suggests that some courses may have no enrollments, i.e. enrollments are optional in that situation.
Weak Entity
A weak entity is one that lacks one or more of the attributes. One can uniquely identify it by looking at the primary key of another entity. Participation from weak entity sets is required for this. The properties of Weak Entities are:
· There are insufficient properties to create a primary key.
· A double rectangle symbol is used to denote it.
· A dashed underline symbol denotes a Partial Key.
· A subordinate entity set is a weak entity set's member.
· The primary, partial keys of the strong entity set are combined in a weak entity set.
· The double diamond symbol depicts the point of relation between one powerful and one weak entity.
· The weak entity set for recognising relationships is connected by a double line.
MultiValued Attributes
Multiple values can be assigned to multivalued properties. A student, for example, may have multiple phone numbers, email addresses, and other contact information.
Derived Attributes
The physical database does not have this type of attribute. Their values, on the other hand, are obtained from other database properties. For example - Consider the age of an employee, the age should not be directly stored. It should be calculated using the provided employee’s Date Of Birth.
Relational Database Design
While designing the database, we should keep in account the scalability of the application, the business logic of the application and optimization as well. There are some set of steps that are being used to design a relational database from scratch.
Steps to Create Relational Database from Scratch
· Define purpose of the Database - For this database, you're looking for and gathering requirements. Decide what the project's goal is. You must have to figure out what you need to record in the database by sampling queries and results you might expect from the query. You can separate the collected data into separate tables. Decide on the columns (fields) that will be used in each table. It's crucial to keep in mind that information should not be repeated.
· Figure out Primary key for each table -The...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here