CS405 Highlighted Handouts by Aila Noor

Contents

Module-01: Conceptual Data Modeling & Entity Relationship Diagram (ERD) Review ................. 3 Module 02: Introduction to Oracle 11g on Cloud. ................................................................................. 9 Module 03: SQL Recap .................................................................................................. 12 Module 04: PL/SQL Concepts ....................................................................................... 21 Module 05: General Programming Language Fundamentals of PL/SQL .............................................. 24 Module 06: SQL in PL/SQL............................................................................................. 27 Module 07: Conditional Control – I ............................................................................... 36 Module 08: Conditional Control – II .............................................................................. 44 Module 09: Iterative Control – I ..................................................................................... 50 Module 10: Iterative Control – II .................................................................................... 63 Module 11: Cursor ........................................................................................................ 68 Module 12: Error Handling & Built-in Exceptions ................................................................................ 79 Module 13: User Defined Exceptions ............................................................................ 85 Module 14: Advance Exceptions ................................................................................... 88 Module 16: Collection ................................................................................................... 92 Module 17: Records ...................................................................................................... 98 Module 18: Procedures. .............................................................................................. 102 Module 19: Functions ................................................................................................. 111 Module 20: Triggers ..................................................................................................... 117 Module 20: Package .................................................................................................... 122 data is related to each other.

“en” which means being. representation of the relationships between data in a database.

Module-01: Conceptual Data Modeling & Entity Relationship Diagram (ERD) Review

1. Concept of ERD

Entity–relationship modeling was developed by Peter Chen and published in 1976; it serves as building block of relational database design. Entity relationship diagram is a graphical It is the result of using process and it just only visualize the business data instead of defining the business process. In very simple terms, ERD is a visual representation of data that

2. Components of ERD

There are three main components of ERD and these are:

•  Entity
•  Attributes
•  Relationships

3. Entity & Attributes

The word entity is rooted from the Latin word Entity is name of place, An entity can be a real-world object that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity. An entity set is a collection of similar types of entities. Entities are represented by means of their properties, called Attribute or Column. All attributes have values which are the qualities or data about Entities that is to be stored. An Attribute Continuing with the above example, a student entity may have unit of any database.

4. Relationships

Relationship represents Attribute is the smallest storage

how data is connected among entities in a given System. The association among the entities can also be termed as relationships. In our school example, the two entities e.g. student and course have an association or relation with each other as student enroll in a course. Interaction among entities is captured using relationships. In a database, relationships are created between different entities in order to remove the redundancy and ultimately improve the database performance.

Relationship define how data is connected among the entities in a given system or in other words how one entity is logically connected with another entity of the system. Relationships in describes how the

name, class, and age as attributes. describes a property or characteristics of an entity person or thing about which something can be stored in a system. systematic child table per parent record. It can either a database are said to be a combination of cardinality and optionality where optional relationship is one in which there may or may not be a matching record in parent / child table and cardinality represents the concept of “how many” and normally it is 0 or more. Equation for creating relationship is as follow:

R???????? ℎ ?? ?? ? = ??????????

The concepts of cardinality and optionality are explained below in details.

Relationships are bi-directional in nature; Relationship between two entities A and B is as follow: i. Relationship from A to B ii. Relationship from B to A

5. Optionality

Participation in entity relationship is either optional or mandatory (…. Or ). This means that minimum number of record which are present in

If we examine the Parent & Child relationship, it is quite possible for parent not to have any child record. Therefore, child is optional to parents.

On the other side, a child must have a parent and therefore, parent is mandatory to child. Hence minimum number of child record per parent in child table will show either 0 (…) or one ( ) . A dotted or solid line shows this kind of relationship. To translate …… or following rules are to be followed:

Mandatory:

: Exactly one be zero or one.

Optional: --- zero or one