CS405 Official Handouts

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

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 representation of the relationships between data in a database. It is the result of using systematic 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 describes how the data is related to each other.

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 “en” which means being. Entity is name of place, person or thing about which something can be stored in a system. 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 describes a property or characteristics of an entity. Continuing with the above example, a student entity may have name, class, and age as attributes. Attribute is the smallest storage unit of any database.

4. Relationships

Relationship represents 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 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 child table per parent record. It can either be zero or one.

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:

Optional: -----: zero or one

Mandatory: ____: Exactly one