Weebly
[pic]
Sinhgad Technical Education Society’s
Sinhgad College of Engineering, Pune
DEPARTMENT OF INFORMATION TECHNOLOGY
(Accredited by NBA)
LABORATORY MANUAL
COMPUTER PRACTICE LAB - I
BE (I.T) SEMESTER – I (2010-11)
|Prepared by | |
| | |
|Mr. G.R. Pathak |Mr. V.V. Puri |
|Mrs. M. J. Chouhan |Mrs. S.D. Shinde |
|Mr. S.M. Jaybhaye | |
|Teaching Scheme | Examination |
|Practical Session : 4hrs/week |Scheme |
| |Team Work : 50 marks |
| |Practical : 50 marks |
| |Oral : 50 marks |
LAB. OVERVIEW
Computer Practice Lab – I (CPL-I) comprises three subjects ADBMS, OOMD and STQA. This lab provides an exposure to the students with hands on experience on various software / tools such as database tool -Oracle, design tool – Enterprise Architecture / Rational Rose - UML 2.0, and testing tool - QTP etc.
The main focus of this lab is to understand the “software development life cycle” so that students will implement the mini-project in Visual Basic 6.0 as a frontend and Oracle as a backend. The lab teaches the basics of database technology and how to develop programming in SQL and PL SQL.
This lab is divided into following components.
1. Prerequisite : A
2. Design Methodology : B
3. Database Processing : C
4. Testing : D
The interdependency is as shown above. However, 4 assignments are planned independently for B (design methodology) and C (database processing), and 2 assignments each on A (prerequisite) and D (testing). One integrated assignment which comprises B, C, and D i.e. mini-project.
There will be total 15 assignments based on database, design and testing in the lab sessions. There will also be a project assignment for which, student will work in groups to design a database, implement it, and build forms and reports using Visual Basic that can be used to interact with the database. The project assignment will be submitted in two phases. Phase 1 will consist of a database design, UML diagrams; Phase 2 will consist of an implemented database and a working set of forms that interact with the database and test cases for the same.
Prerequisites
1. Database Design
2. Normalization Concepts
3. Basic knowledge of the SQL Language
4. Basic Knowledge of Visual Basic 6.0
List of Assignments and Schedule
I N D E X
|Sr. No. |Name of Assignments |Conduction |
|a. |Prerequisite | |
|1 |Entity Relationship Diagram and Normalization |Week – 1 |
|2 |Structure Query Language (SQL) |Week – 2 |
|3 |Data Flow Diagram |Week – 3 |
|b. |Advanced Database Management System | |
|4 |PL / SQL Block |Week – 4 |
|5 |Procedure and Function |Week – 4 |
|6 |Trigger |Week – 5 |
|7 |Cursor |Week – 5 |
| |Mid Term Submission |Week – 6 |
|c. |Object Oriented Modeling and Design | |
|8 |Use Case Diagram |Week – 7 |
|9 |Class Diagram |Week – 8 |
|10 |Sequence Diagram |Week – 9 |
|11 |Activity Diagram |Week – 10 |
|12 |Component Diagram |Week – 11 |
|d. |Software Testing and Quality Assurance | |
|13 |Manual Testing |Week – 12 |
|14 |Automated Testing |Week – 13 |
| |Final Submission |Week – 14 |
a. PREREQUISITE
The overall purpose of this assignment is to practice the process of modeling and designing a relational database given a certain scenario. This assignment involves extending a given ER diagram, and then translating that extension to the relational model. The student should become familiar with how to create tables in SQL, define primary and foreign keys, and insert and update data into tables.
After the assignment no. 1, the student should be able to model real world scenarios in terms of ER model and translate an EER diagram into a relational database implementation.
ASSIGNMENT NO 1
(DATABASE DESIGN - ENTITY RELATIONSHIP DIAGRAM)
This lab session introduced the key concepts of entity relationship modeling. After this lab session student should be able to:
1. Define entity relationship models and describe its benefits
2. Recognize and understand the basic concepts and constructs of an entity relationship diagram (ERD)
3. Distinguish between unary, binary, and ternary relationships, and give an example of each
4. Learn how to identify entities from a simple description.
5. Learn how to identify entity relationships using an Entity Relationship collection.
6. Draw ERD diagrams
ENTITY RELATIONSHIP DIAGRAM (ERD)
ERD complements Data Flow Diagram (DFD). While DFD focuses on processes and data flow between them, ERD focuses on data and the relationships between them. It helps to organize data used by a system in a disciplined way. It helps to ensure completeness, adaptability and stability of data. It is an effective tool to communicate with senior management (what is the data needed to run the business), data administrators (how to manage and control data), database designers (how to organize data efficiently and remove redundancies). It consists of three components.
Entity: It represents a collection of objects or things in the real world whose individual members or instances have the following characteristics:
• Each can be identified uniquely in some fashion.
• Each plays a necessary role in the system we are building.
• Each can be described by one or more data elements (attributes).
Entities generally correspond to persons, objects, locations, events, etc. Examples are employee, vendor, supplier, materials, warehouse, delivery, etc.
There are five types of entities.
• Fundamental entity: It does not depend on any other entity for its existence. For e.g. materials
• Subordinate entity: It depends on another entity for its existance. For example, in an inventory management system, purchase order can be an entity and it will depend on materials being procured. Similarly invoices will depend on purchase orders.
• Associative entity: It depends on two or more entities for its existence. For example, student grades will depend on the student and the course.
• Generalisation entity: It encapsulates common characteristics of many subordinate entities. For example, a four wheeler is a type of vehicle. A truck is a type of four wheeler .
• Aggregation entity: It consists of or an aggregation of other entities. For example, a car consists of engine, chasis, gear box, etc. A vehicle can also be regarded as an aggregation entity, because a vehicle can be regarded as an aggregation of many parts.
ATTRIBUTES : They express the properties of the entities.
Every entity will have many attributes, but only a subset, which are relevant for the system under study, will be chosen. For example, an employee entity will have professional attributes like name, designation, salary, etc. and also physical attributes like height, weight, etc. But only one set will be chosen depending on the context.
Attributes are classified as entity keys and entity descriptors.
Entity keys are used to uniquely identify instances of entities. Attributes having unique values are called candidate keys and one of them is designated as primary key. The domains of the attributes should be pre-defined. If 'name' is an attribute of an entity, then its domain is the set of strings of alphabets of predefined length.
ATTRIBUTE TYPES
• Atomic / Simple Attribute
• Composite Attributes
• Single-Valued
• Multi Valued
• Stored Attribute
• Derived Attribute
• Simple (or atomic) attribute can't be broken down into smaller components.
• Composite attributes, can be broken down into component parts.
• Single-valued attribute (represented by a single line ellipse) is an attribute which has only one value in a table.
• Multi-valued attribute has multiple values per instance, such as: all courses taken by a student, shown above. It is represented by double line ellipse
• Stored attribute - a regular attribute
• Derived attribute which is calculated from a stored attribute, and thus should not be shown on the ERD.
Relationships: They describe the association between entities.
They are characterized by optionality and cardinality.
Optionality is of two types, namely, mandatory and optional.
1. Mandatory relationship means associated with every instance of the first entity there will be at least one instance of the second entity.
2. Optional relationship means that there may be instances of the first entity, which are not associated with any instance of the second entity. For example, employee-spouse relationship has to be optional because there could be unmarried employees. It is not correct to make the relationship mandatory.
Cardinality is of three types: one-to-one, one-to-many, many-to-many.
1. One-to-one relationship means an instance of the first entity is associated with only one instance of the second entity. Similarly, each instance of the second entity is related to one instance of the first entity.
2. One-to-many relationship means that one instance of the first entity is related to many instances of the second entity, while an instance of the second entity is associated with only one instance of the first entity.
3. In many-to-many relationship an instance of the first entity is related to many instances of the second entity and the same is true in the reverse direction also.
Other types of relationships are multiple relationships between entities, relationships leading to associative entities, relationship of entity with itself, EXCLUSIVE-OR and AND relationships
ERD NOTATION : PETER CHEN NOTATION
|COMPONENT |REPRESENTATION |
|ENTITY OR OBJECT TYPE |PURCHASE ORDER |
|RELATIONSHIP |[pic] |
|CARDINALITY |[pic] |
|OPTIONALITY |[pic] |
Example
[pic]
Creating Entity Relationship Diagrams
Components of an Entity Relationship Diagram
An entity relationship diagram (ERD) typically consists of four different graphical components:
1. Entity. An entity in ERD stands for some physical or abstract object in reality. Entity can represent:
• persons (e.g., customer, student)
• things (e.g., a tangible product such as car)
• organizations (e.g., store, university department)
• events (e.g., purchase, course enrollment)
• concepts (e.g., order, GPA).
Specific examples of an entity are called instances (e.g., the customer John Jones).
2. Relationship. A relationship represents a connection that exists between entities (e.g., a customer places an order). Relationships in ERD usually reflect relationships between objects in reality.
3. Cardinality. Cardinality defines how many instances of related entities can participate in the relationship (e.g., a customer may place many purchase orders, and each purchase order is placed by one particular customer). In an ERD, cardinality is specified by those funny looking crow feet, bars and circles (or sometimes simply by numbers).
4. Attribute. An attribute is a piece of data, a characteristic common to all or most instances of a particular entity (e.g., customer number, name, address, and telephone number are all attributes of the entity customer). Attributes are also referred to as fields or columns.
An attribute or a combination of attributes that uniquely identify one and only one instance of an entity is called primary key or just key (e.g., customer ID is the primary key for the entity customer).
The figure below is an ERD containing all the four components discussed above. Primary keys are underlined.
[pic]
Figure 1. Components of Entity Relationship Diagram
Procedure for Developing Entity Relationship Diagram
Typically, you will start with a case study or perhaps some business documents belonging to the company in case. The following procedure will demonstrate how to convert that information into an ERD.
1. Identify Entities. Identify the persons, organizations, things, events, and concepts that you want to present as entities in your ERD.
2. Identify Relationships. Figure out relationships between pairs of entities.
3. Draw a Rough Diagram. Draw rectangles for entities and lines with diamond shapes inserted for relationships connecting the entities.
4. Define Primary Keys. Identify the data attribute(s) that can be used for identifying each instance of an entity.
5. Identify Attributes. List other data attribute(s) for your entities.
6. Map Attributes into Entities. For each attribute, match it with that specific entity it belongs to.
7. Draw a Fully Attributed Diagram. Adjust the diagram to account for attributes, entities, or relationships discovered in step 6.
8. Check Results. Ask yourself, does the final entity relationship diagram accurately depict the case or document data?
Example
The above procedure will be illustrated by working out the following example.
A store has customers. A customer places orders with the store. An order contains products. The important data fields are customer first name, customer middle name, customer last name, street address, city, province, postal code, phone number, product name, product unit price, quantity in stock, order date, and the quantity of the product ordered. Each of the following sections corresponds to a specific step above.
1. Identify Entities
In this stage, you analyze the information you have in order to identify the people roles, events, organizations, things, and concepts. One approach is to highlight those words which you think correspond to entities, as follows:
a. A store has customers.
b. A customer places orders with the store.
c. An order contains products.
The important data attributes are customer first name, customer middle name, customer last name, street address, city, province, postal code, phone number, product name, product unit price, quantity in stock, order date, and the quantity of the product ordered.
This example is quite simple because entities are labeled and the text explicitly specifies attributes.
2. Identify Relationships
In this step, the aim is to identify relationships, that is, the connections between pairs of entities. There are minimally two relationships in example.
a. A customer places an order
b. An order contains products.
3. Draw a Rough Data Diagram and place all the entities in rectangles
4. Use diamonds and lines to represent the relationships between entities.
Arrange the entities so there is no overlap of the relationships. The figure below is an example of a rough diagram that represents information from Step 2.
Figure 2. A Rough ERD
5. Define Primary Keys
A primary key is an attribute, or a group of attributes, that can be used to uniquely identify a specific instance of an entity. The name "Bob Smith" is not a primary key as there are many people with that name. We usually use whole numbers for primary keys because each number is unique and thus can uniquely identify each instance of an entity. So, a customer number is the key in this case, and we usually use the term “ID” (identifier) for naming this key as well as other keys.
You can identify primary keys by examining and evaluating the information about the business that you want to represent in your ERD. In this example, the following are specified as unique identifiers: CustomerID, ProductID, OrderID. The diagram below includes the keys. Cardinality is also specified to make the diagram complete.
[pic]
Figure 3. Rough ERD with Primary Keys
7. Identify Attributes
A data attribute is a characteristic common to all or most instances of a particular entity. In this step, you try to identify and name all the attributes essential to the business you are studying without trying to match them to particular entities. The best way to do this is by studying forms, files, and reports currently available and taking a note of each potential attribute. Cross out extraneous items such as signatures and information that is same for all instances of the form (e.g., your company name and address). If so indicated, cross out any attributes which are no longer used or which are not to be used in the future.
The remaining items should represent the attributes you need. You should always verify these with the people in the actual business. (Sometimes forms or reports are out of date.)
The only attributes indicated in our example case are the customer first name, customer middle name, customer last name, street address, city, province, postal code, product name, product unit price, quantity in stock, order date, and quantity of product ordered.
6. Map Attributes
For each attribute, we need to match it with exactly one entity. Often it seems like an attribute should go with more than one entity (e.g., name). In this case, you need to add a modifier to the attribute name to make it unique (e.g., Customer Name, Employee Name, etc.) or determine which entity an attribute “best” describes. It is a convention to use a single word to name a field, such as using CustName instead of Customer Name).
If you have attributes leftovers without corresponding entities, you may have missed an entity and its corresponding relationships. Identify these missed entities, and add them to your list. In our example, there is just one such “odd” attribute, the quantity of products being ordered – QuantityOrdered. This attribute does not belong to either Product or Order but to something that is a bridge between these two. This is a “bridge” entity (called weak entity or association entity). In the table below, the association entity is OrderDetail. It contains the attribute QuantityOrdered as well as the ProductID (to track which product is ordered in a particular quantity) and OrderID (to track on which orders the particular products in specific quantities occur).
Attribute-Entity Mapping Table
|Customer ( CustomerID, CustFirstName, |Order ( OrderID, OrderDate ) |
|CustMiddleName, CustLastName, | |
|CustAddrLine1, CustAddrLine2 |Product ( ProductID, ProductName, UnitPrice |
|CustCity, CustProvince, CustPostalCode |QuantityInStock) |
|CustPhone, CustEmail ) | |
| |OrderDetail -- New Entity |
| |(OrderID, ProductID, QuantityOrdered) |
Another way of thinking about this new entity OrderDetail is that it allows for one order to contain many products (e.g., Order #1 can contain apples and oranges, which are represented by their ProductIDs). Notice that the key for entity OrderDetail is a combination of OrderID and ProductID. This key makes it possible to match the same OrderID – 1 in our example – with different ProductIDs – those for apples and oranges in our example.
Another useful property of this new entity OrderDetail is that it allows for apples to be ordered many times (the same goes for oranges). So, you can have apples on orders #1, #2, and so on. Therefore, different OrderIDs are matched with the same ProductID.
7. Draw a Fully Attributed ERD
To show all the attributes belonging to the entities and the new association entity, you need to redraw your ERD, as depicted in the figure below.
[pic]
Figure 5. Completed ERD
8. Check Your Results
Look at your diagram from the point of view of a person who is familiar with the situation, form, or process being modeled. Is everything clear? Also, look over the list of attributes associated with each entity to see if anything has been omitted.
Hints:
• Begin labeling relationships using single words or simple phrases that describe how two items relate to each other, but be consistent in your use of words or phrases. Avoid using different labels such as ‘uses’ and ‘makes use of’ if ‘uses’ could accurately apply for both situations.
• Always look for relationships between the items in the diagram, and add these when they become apparent. The strength of an ERD is in showing the relationships, dependencies, and complexity in an organization.
PRACTICE ASSIGNMENTS
Create ERDs for the examples below.
LEARNING OBJECTIVES After studying these examples, you should be able to:
1. Draw an Entity Relationship Diagram of your tables and their relationships.
2. Separate from your ERD, list the information for each table:
3. The name of the table.
4. The names of each of the fields in the table.
5. Indicate which fields are part of the primary key for the table
6. Indicate which fields are part of a foreign key and the name of the other table that the foreign key is related to.
Assignment No. 1
Draw an ERD for a small college database comprising the entity types: DEPARTMENT, COURSE, STUDENT and LECTURER.
Each department has many lecturers, one of whom is the head of department. A lecturer belongs to only one department. Each department offers many different courses, and many lecturers can teach on a single course. Lecturers can also teach on more than one course. A student may enroll for many courses offered by different departments.
Assignment No. 2
A local education authority (LEA) requires a system to hold details of the applicants who apply for courses run by local colleges. Each course is run at only one college, though more than one course is run at each college. An applicant may apply for several courses. Details are to be kept of the standard qualifications that the majority of applicants have, such as GCSEs. Draw an ERD is this system.
Assignment No. 3
A college has a student enrolment system. Each student is enrolled on one course. The students choose which modules they wish to study. Students from several courses may choose to study the same module. A course will typically have several modules, and an individual module may be offered on several different courses. Requirements of the system include the ability to determine which students are enrolled on a particular course and which modules a student has chosen to study; on which course a module may be studied; which modules are offered by a particular course. Draw an ERD of the student enrolment system.
Assignment No. 4
A country bus company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several buses. Each route is made up of several stages which can passes through a number of towns. A town may be situated along several stages (of different routes of course). One or more drivers are allocated to each stage of a route. Prepare an ERD for this scenario.
SUPPLEMENTARY ASSIGNMENTS
1. Company Employees
A company has a number of employees, identified by Employee Numbers. The company wants to keep track of the employees’ names, addresses and ages. The company also has several projects. Projects are assigned a unique project identifier, and also have the attributes project name and start date. Each employee may be assigned to one or more projects, or may not be assigned to any projects. A project must have at least one employee assigned to it, and may have any number of employees assigned. An employee’s billing rate depends on the project he or she is assigned to. The company wishes to keep track of the employees’ billing rates on every project, and when the employees started to work on their projects.
2. Courses
A university has a large number of courses in its catalog. Attributes of Course include Course_Number (identifier), Course_Name, and Credits. Each course may have one or more different courses as prerequisites, or may have no prerequisites. Similarly, a particular course maybe a prerequisite for any number of courses.
3. Laboratory
A laboratory has several chemists who work on one or more projects. Chemists also may use certain types of equipment on each project. Attributes of chemists include employee ids, names and phone numbers. The lab also wants to keep track of project ids and start dates, and equipment serial numbers and costs. The organization wishes to keep track of the date a specific piece of equipment was assigned to a specific chemist to work on a specific project.
4. Hospital
A hospital has a large number of registered physicians, each with a unique physician number. Physicians all have at least one specialty. Patients are admitted to the hospital by physicians. The hospital keeps track of the patients’ names and addresses, and assigns each patient a unique patient identifier. Any patient who is admitted must have one and only one admitting physician. Once admitted, a patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the date and time of the treatment.
a. PREREQUISITE
The purpose of this assignment is to get understanding of the different normal forms and of the problems that can be prevented by normalization. After this assignment, the student should be able to determine if a database schema conforms to the Boyce-Codd normal form (BCNF). The students should be familiar with modeling real world scenarios in terms of ER model and translate an ER diagram into a relational database schema.
ASSIGNMENT NO 2
(DATA NORMALIZATION)
Database normalization aims to remove irregularity (abnormality) of update. The un-normal database is difficult to maintain the correctness of database after update while the normalized database schema is more user friendly for database update. On the other hand, the denormalization is the reverse of normalization. It transforms the normalized database design into un-normal form. As a result, the denormalized database is difficult to update. Nevertheless, the denormalized database can perform faster than normalized database because it requires less join operations for query.
The unnormal form implies that the table tuples containing multiple values and without key value. The first normal form implies that the table tuples in atomic value, that is, single value, and with key value. The second normal form implies that all the relations are in fully functionally dependency and there is no partial functional dependency. The third normal form implies that all the relations are in fully functionally dependency, and there is no transitive functional dependency. Boyce Codd Normal Form implies that all the candidate keys in each relation are also determinant. They are also called strong third normal form, that is, they are more restricted than third normal form. The fourth normal form implies that all the relations do not have multiple multi-valued dependencies. In other words, the 2-decomposibility of multiple multi-valued dependency in a relation is decomposed into two single multi-valued dependency relations. The fifth normal form implies that all the relations do not have join dependency. In other words, the 3-decomposibility of join dependency in a relation is decomposed into three relations which can join together into an un-normal form relation with the join dependency.
Functional dependency implies that a determinant can determine dependency fields. Inclusion dependency implies that a subclass data must be within its superclass data. Multi-valued dependency implies that each determinant can determine a multiple value. Join dependency implies that the join of all of its attributes implies a n-ary relationship within the relation.
Relational data normalization is to eliminate data redundancy and update irregularity of relational schema. Irregularity of database design need to be normalized in order to unsure user friendliness of updating relational database.
There are unormal form, followed by first, second, third, Boyce Codd, fourth and fifth normal form. We need to analyze data dependency to determine data normal form. Data dependency relates data with each other in certain constraint rules. The unnormal form can be normalized up to fifth normal form as follows:
Data redundancy means that the elimination of redundant data is without loss of information. An unnormalized relation implies that too many data relationship (dependency) in the same table, which is difficult to operate. Normalized database is easy-to-use for operations of insert, update and delete.
Normalization starts with un-normal form (UNF) with a universe of relations such that each table may have multiple values. For example, the following table is an unnormal form with its multiple valued attributes as shown below:
[pic]
UNF relation R (S#, S-Name, {Enrol})
Where { } is repeating group
A relation is in 1NF if there is no multiple valued attributes in the relation. In other words, all attributes must have atomic values..
UNF -------------------------------------------------( 1NF
Eliminate multiple valued attributes
After normalization by transforming multiple valued attributes into atomic (single) value, the unnormal form becomes first normal form as follows:
[pic]
1NF Relation R (S#, S-Name, Enroll)
In general, a functional dependency describe the relationship between attributes as shown below:
Determines
FD: Determinant ------------( Dependent field
For example,
[pic]
For a First normal form (1NF), all attributes must have atomic value, and there is a key attribute with uniquely identifiable value which can determine non-key attributes.
For example,
FD: X → Y => value X determines value Y (X is a key with Y as its attribute)
Key values determines non-key value. Non-key value is dependent on key value.
For example, Relation Student (StudentNumber, StudentAge) is in 1NF where StudentNumber is a key which can determine StudentAge.
A relation is in 2NF if there is no partial data dependency with the relation.
Normalized into
1NF ==========================( 2NF
Eliminate partial functional dependency
For example,
X is fully functionally dependent on Y1 in relation Y1Y2X (Y1, Y2, X)
⇨ FFD: Y1 → X
which means that X is partially functionally dependent on Y1, Y2(compound key)
⇨ PFD: Y1, Y2 → X
As a result, Y1, Y2 and X can be normalized into 2NF as follows:
Relation Y1Y2 (*Y1, Y2)
Relation Y1X (Y1, X)
where the above two relations are all in FFD. (FFD: Y1, Y2→0 & FFD: Y1→X)
A relation is in 3NF if there is no transitive dependency between the attributes in the relation.
If X is functionally dependent on Y, and Y is functionally dependent on Z => X is transitively dependent on Z
Normalized into
2NF =============================( 3NF
Eliminate transitive functional dependency
For example, relation XYZ (X, Y, Z) can be normalized into Relation XY(X, Y) and relation YZ(Y, Z) in 3NF such that there is no transitive dependency in the normalized two relations XY and YZ.
A relation is in BCNF if all determinant is a candidate key.
Normalized into
3NF =========================-( BCNF
Each determinant is a candidate key
For example, relation SCSC(S#, C#, S-Name, C-Name) is not in BCNF because C# is a determinant but which is not a candidate key.
The following three relations are in BCNF because all determinants are candidate keys.
Relation S (S#, S-Name) => FFD: S# → S_Name
Relation C (C#, C-Name) => FFD: C# → C_Name
Relation SC(*S#, *C#) => FFD: S#, C# → 0
A relation is in 4NF if there is no MVD within the relation.
In general, a MVD(multi-valued data dependency) is defined as a determinant determines a multiple valued dependent attribute as shown below:
MVD: Determinant -------------------------------------( Dependent multiple values
Normalized into
BCNF =================( 4NF
Eliminate MVD
For example: relation ABC can be normalized into relations AB and AC in 4NF.
[pic]
A relation is in 5NF if there is no join dependence within the relation as shown below:
JD:Project Attributes[pic] Project Attributes…[pic]Project Attributes => UNF Relation
Normalized into
UNF Relation with JD ==================( 5NF Relations(without JD)
Eliminate JD
A join dependency means that if there binary relationships occur in three attributes a, b, c, then there is a join dependency in these three attributes.
For example:
JD(join dependency): If (a1, b1), (b1, c1), (a1, c1) occur
Then (a1, b1, c1) occurs
JD: R(A, B, C) = {A, B} natural join {B, C} natural join {A, C}
[pic]
For ternary relationship, that is, relation with all three attributes as keys, the decomposability of the relation into three binary relationships may or may not exist. If a relation can be decomposed into three relations, which implies that there is join dependency in the relation as shown in the above example of relation SPJ. Otherwise, the relation cannot be decomposed into three relations because there is no join dependency in the relation.
Therefore, for the three relations as a result of three decomposability as shown in relation SPJ with JD, these three relations can be joined together into original relation SPJ without information loss. However, for any three relations without decomposability, there may be connection trap for them unable to join together into a relation. For example, the information that
a) Smith supplies lamps
b) Lamps are used in the Project Housing
c) Housing project is supplied by Smith
The above information (a), (b) and (c) does not implied that Smith supplies lamps for project Housing as a result of connection trap. In other words, it can only imply Smith supplies lamps to some projects. Lamps parts are used in Project Housing supplied by some supplier. Project Housing is supplied by Smith using some Part. These statement cannot explicit describe Smith supplies lamps for Project Housing.
Similarly, for two relations AB(A, B) and AC(A, C), they can be reconstructed into relation ABC(A, B, C) with MVD A( B|C in 2-decomposibility. However, they cannot be joined together to reconstructed relation ABC(A, B, C) without MVD.
PRACTICE ASSIGNMENT
The following form is to record problem occurred during user acceptance test. Each problem report is associated with one problem. Each tester can issue a problem report. However, only the test coordinator can assign the problem report number. The data fields of “Attention_To”, “Company”, “Diagnosis_date, Description and “Reported_By” are repeating groups.
[pic]
(a) Design a database satisfying the requirements of First Normal Form for the above problem report form. You can state your assumptions.
(b) Normalize the First normal form design in (a) into Second Normal Form.
(c) Normalize the Second Normal Form design in (b) into Third Normal Form.
SUPPLEMENTARY ASSIGNMENT
NORMALIZATION
The purpose of this exercise is to get understanding of the different normal forms and of the problems that can be prevented by normalization. After the lab, the student should be able to determine if a database schema conforms to the Boyce-Codd normal form (BCNF). The students should be familiar with modeling real world scenarios in terms of ER model and translate an ER diagram into a relational database schema.
The scenario – A Library Database
The city library of Uppsala has a database to keep track of its books, the people who borrow books, and which books have been borrowed by who. The tables, with the data, are included below in sections 4 and 5. (For well-known reasons, there are very few books in the library.)
Unfortunately, the design of the database is not very good. Your mission is to analyze the problems with the design, and suggest a better one.
The output of your work should be a report that addresses all the faults you find regarding normalization along with a description of why they are problematic. Furthermore, you should develop an alternative design that is in BCNF.
Exercises
1. Specify all functional dependencies in the library scenario. Then state in which normal form (1NF, 2NF, 3NF or BCNF) each of the existing tables is, and why. Assume that there is one telephone number per address.
2. For each table that doesn’t fulfill the requirements for BCNF, explain the problems that this lack of normalization has and their potential consequences. Give some examples.
3. Design a new database for the library, where all the tables fulfill BCNF without losing any information. Use the top-down approach for the relational database design by starting with an ER-diagram for the database and mapping the diagram to relational tables.
The schema of the existing database
There are three tables:
• A table called BOOK, which contains data about the books. It has the attributes TitleNr (a number that this library assigns), ISBN, CopyNr (which is used to separate different copies of the same book), Title, PublYear, Author, and AuthorNat. The primary key consists of TitleNr, CopyNr and Author. An alternative key is formed by ISBN, CopyNr and Author.
• A table called CUSTOMER, which contains data about the persons who can borrow books. It has the attributes CustomerNr (a unique number identifying a person, assigned by the library), PersonNr (which is a unique number identifying a person, assigned by the Swedish state), Name, Address, Tel, and NrBooks (the number of books that this person has borrowed at the moment). CustomerNr is the primary key. PersonNr is an alternative key.
• A table called LOAN, where the loans are stored. It has the attributes TitleNr, CopyNr, CustomerNr, Date (which is the date when the book was borrowed), and BorrowerName (which is the name of the customer who borrowed the book). The primary key consists of TitleNr and CopyNr.
The contents of the existing database
BOOK
+---------+----------+--------+--------------+----------+--------------+-----------+
| TitleNr | ISBN | CopyNr | Title | PublYear | Author | AuthorNat |
+---------+----------+--------+--------------+----------+--------------+-----------+
| 1 |0071148108| 1 | Database | 1997 | Silberschatz | USA |
| 1 |0071148108| 1 | Database | 1997 | Korth | USA |
| 1 |0071148108| 1 | Database | 1997 | Sudarshan | India |
| 2 |0805317538| 1 | Fundamentals | 1994 | Elmasri | USA |
| 2 |0805317538| 1 | Fundamentals | 1994 | Navathe | USA |
| 2 |0805317538| 2 | Fundamentals | 1994 | Elmasri | USA |
| 2 |0805317538| 2 | Fundamentals | 1994 | Navathe | USA |
| 3 |0198642253| 1 | Mord | 1995 | Guillou | Sweden |
| 3 |0198642253| 2 | Mord | 1995 | Guillou | Sweden |
| 4 |3411021764| 1 | Våld | 1998 | Guillou | Sweden |
+---------+----------+--------+--------------+----------+--------------+-----------+
CUSTOMER
+------------+----------+----------------+---------+------+---------+
| CustomerNr | PersonNr | Name | Address | Tel | NrBooks |
+------------+----------+----------------+---------+------+---------+
| 1 |6312111658| McCarthy | Vägen 7 |282677| 1 |
| 2 |4403149901| Larsson | Gatan 6 |146000| 1 |
| 3 |4010229910| Ericsson | Gatan 8 |241000| 1 |
| 4 |4501129921| Schwarzenegger | Vägen 3 |174590| 0 |
+------------+----------+----------------+---------+------+---------+
LOAN
+---------+--------+------------+------+----------------+
| TitleNr | CopyNr | CustomerNr | Date | BorrowerName |
+---------+--------+------------+------+----------------+
| 1 | 1 | 3 |7/1 98| Ericsson |
| 3 | 2 | 1 |1/9 98| McCarthy |
| 2 | 1 | 2 |7/1 98| Larsson |
a. PREREQUISITE
The overall purpose of this assignment is to practice the process of modeling and designing a DFD on a certain scenario. The student should understand the logical modeling of processes through studying data flow diagrams, how to draw data flow diagrams using rules and guidelines and how to decompose data flow diagrams into lower-level diagrams
After the assignment no. 1, the student should be able define the scope of the system, indicating which elements are inside and outside the system.
ASSIGNMENT NO. 3
DATA FLOW DIAGRAMS
Data Flow Diagrams help by concentrating on the data, and do not have any relationship to a program's flow of control, or to the physical characteristics of the system. The data flow diagram (DFD) is the fundamental modelling tool for the systems analyst. It represents the essential functions of an information processing system in a highly abstract way using a minimum set of symbols. These notes introduce data flow diagrams as tools for describing information processing systems. They define each of the essential components and explain the symbols and conventions used in data flow diagrams. The emphasis is upon how to read, understand and hence draw data flow diagrams.
Components of the DFD
You will remember that a system was defined as an interrelated set of components and methods, all working together to achieve some common purpose. A system also has a boundary distinguishing the system from its environment. An information processing system has three principal purposes: transmission, storage, and transformation of information. Thus an appropriate abstraction for an information processing system would depict it in terms of just three types of components: one which shows the movement of information, one which shows the storage of information, and one which shows the transformation of information. These components are the data flow, data store, and process (also called transform), respectively. A Data Flow Diagram models a specific information processing system by showing the relationships among components of these three types. During the analysis Data Flow Diagrams are useful for communicating with the user.
Basic Definitions
A data flow diagram is a network model of an information processing system. The arcs of the network represent data flows, and the nodes represent data stores, transforms, or selected elements of the environment.
A data flow is a movement of information within the system or across the system boundary.
A data flow which crosses the system boundary to enter the system is called a net system input or simply system input. A data flow which crosses the boundary to leave the system is called a net system output or system output.
A data store is a time-delayed repository of information, where data is kept temporarily or permanently, avoid thinking of them in computer "file" terms, there are many quite different forms.
A process, or or transform, is a transformation that inputs one type of data and outputs a different type. It is a conversion of data from one form to another. A process cannot output data without getting some in, data cannot be created in a process.
To show the connection between a system and its environment, a fourth type of element is used. A source (origin) is a person or organisation or system outside the system under consideration that provides information to the system under study in the form of an incoming data flow . A sink (destination) is a person or organisation or system outside the system under study that receives a system output. Sources and sinks are sometimes collectively called external entities. These are often the users, suppliers of commodities, or customers of the system.
Symbols
There are two major schools of Data Flow Diagramming. One is that of Yourdon and De Marco (who has been encouraging their use) the other is that of Gane and Sarson, who introduced them in 1979. The only difference between them is the graphics used. Students are required to be able to read diagrams in either convention and to be able to draw diagrams in a standard set of conventions used for this course, and to state the purpose of Data Flow Diagrams.
The Gane and Sarson method (also called the LST method) uses labelled straight arrows (vectors) for the data. It uses rectangles with rounded corners and a vertical long axis for the processes. Processes may have a reference number at the top in a small ruled off section. A data store is an open-ended rectangle with the long axis horizontal. Sources / sinks are shadowed squares, with a bold black outline behind them.
The Yourdon graphics use circles for the process, and the term process bubble has arisen from this use; the extra identification information given in LST is often absent. A straight heavy line (sometimes double) is used data stores. The data flow lines are arched and free flowing, again with arrowheads for direction.
|Sr. No. | | DeMarco & Yourdon | Gane & Sarson | Used in TEE course |
|1 |External Entity |[pic] |[pic] |[pic] |
|2 |Process |[pic] |[pic] |[pic] |
|3 |Data Flow | | | |
| | |[pic] |[pic] |[pic] |
|4 |Data Store | [pic] | | |
| | | |[pic] |[pic] |
A data flow is a movement of information from one point within the system to another. It may be thought of in several ways depending upon which aspect of the movement is emphasised. A data flow has a direction. If we view the data flow as connecting the points between which it moves, we see it as an interface between a process and a file, or a process and another process, or between the system and an external entity. From the standpoint of a process, a data flow is an input or output. A data flow entering or leaving a data store is information stored or retrieved by an access to that data store. These aspects of information flow are shown explicitly on a data flow diagram. Data is what is important not the way it is implemented, e.g. a message delivered to you could be via phone lines, computer network, or the mail. The message would be the data, not how it got there.
A data store is often referred to as a "file " Although file is a shorter word, the term data store is better it avoids connotations of file being something to do with discs or tapes or suggestions of organisation of the data by sequential or direct access. Data stores arise if information must be retained within the system; if information must wait before being transformed; if a transformation requires an putting together, or collection, of similar data flows to derive its output; or if different transformations occur at different time cycles. Examples of data stores in a non-automated system include: a tax table used in calculating federal income tax deductions from employee pay packets, a collection of invoices for a customer determining credit card entries on the monthly statement , time cards on which medical fund contributions are filled out and collected weekly in a system in which paycheques are produced monthly, and customer orders in a restaurant that are waiting to be filled as soon as a cook is available.
The ability to change the form or content of information permits an information processing system to support a business or other organisation. Using the term transform for the system components which derive outputs from inputs emphasises the act of changing one set of data flows into another. However, transforms are sometimes called by a variety of other names such as process or function. In this context process is synonymous with transformation, as shown in the familiar phrase input-process-output.
Transformations are the parts of the system that do the work. Associated with each transformation is a procedure or set of instructions for making its inputs into outputs. A data flow diagram merely shows which transforms are required in the system; the procedures themselves are not shown in a DFD. Other documents, the process descriptions explicitly and unambiguously state the users' business policies for the transformations with sufficient rigour, completeness, and consistency for the system designers to supply the additional detail to generate a computational algorithm for them.
Allowed connections among components
The movement of information interconnects processes and data stores. It also connects the system to the sources and sinks of information in the system's environment. Data flows are thus the required interfaces between other types of system components. The conventions for permissible connections are shown below.
Data flows may connect a process to any other type of system component. Data stores, sources, and sinks may not be connected to each other directly, but require an intervening process. Direct flow between external entities, is by definition, outside the system and is not included in a DFD.
[pic] [pic]
[pic] [pic]
Allowed connections among components
Example
Data Flow Diagrams (DFD)
Data flow diagrams show the movement of data between processes and files. We check them out and see how to create them. Data flow diagrams are sometimes called information flow diagrams.
Notation
Curved Arrows represent the flow of data.
Completed-Enrolment-Form
Parallel lines represent a data store. A data store could be a file on disk or a paper file.
Student-Records
Circles represent processes. A process acts on data. A process could be to store data on a file, or to retrieve data from a file.
Rectangles represent sources or sinks of data outside the system. A source is where data comes from. A sink is where data goes to.
Data flows, data stores, processes, sources and sinks all have descriptive names.
Example 1 - Student Enrolment System
[pic]
A student completes an enrolment form, has it checked and signed by a lecturer, and hands it to the enrolment clerk for processing. The enrolment clerk checks the course file for the requested course and issues a Student Id Card. The student and course details get placed on the student records file.
Example 2 - Motor Vehicle Registration
Level 0 : A motor vehicle has a registered keeper (who is not necessarily the owner). When you acquire a motor vehicle you are obliged to inform DVLA (Driver and Vehicle Licencing Agency) who update their register.
[pic]
Level 1 When a vehicle is MOT'd (Ministry of Transport safety test) its record is updated by the MOT station tester.
[pic]
Level 2 If the vehicle was seen at the scene of a crime, the police might like to know who its registered keeper is.
[pic]
PRACTICE ASSIGNMENT
A borrower chooses a copy of a book from a library shelf and takes it to the check-out desk. The librarian scans both the borrower's library card and the copy's accession number. (An accession number uniquely identifies a copy - there may be several copies of the same book title.) If the borrower has a fine outstanding, or has reached their limit on the number of loans permissible at any one time, the loan is refused. If there are no fines outstanding and if the borrower has not reached his or her loan limit, the accession number and the date due for return are recorded on the borrower's computer record and the date due for return is also stamped in the book copy.
SUPPLEMENTARY ASSIGNMENTS
1. When a copy of a book is returned to a library, the librarian scans its accession number. The computer system locates the borrower's record and records the return of the copy. The computer system checks and reports if a fine is due. The librarian checks whether a copy of the book has been reserved by another borrower: if it has, the copy is placed on the reserved shelf; if it has not the copy is placed in its proper place on the lending shelves.
2. When a book title becomes out of date, all its copies are removed from circulation and disposed off, and the catalogue is updated. When copies of a new book title are received by the library, each copy is given a unique accession number, the catalogue is updated and the copies placed on the appropriate place on the shelves. If a borrower wants to know what books are available on a particular subject, they supply the subject and the computer system searches the catalogue and displays the results.
a. PREREQUISITE
In this assignment, student would be understood RDBMS tool. The objective of the assignment is to get familiar with the functionality and supports provided by database software – Oracle and understand how to use it to meet data storage and system requirements.
At the end of the assignment, student should be understand and to use RDBMS tool for data access and updating and should be comfortable writing PL/SQL programs at database level using Oracle.
ADVANCED DATABASE MANAGEMENT SYSTEM
PL/SQL
Detailed objectives of the lab are:
• Students will learn PL/SQL which would provide the ability to do iterative programming at database level to:
o Write programming blocks with conditionals, assignments, loops, etc
o Stored procedures, functions, packages.
o Cursors which would allow row wise access of data.
o Triggers which would allow you define pre and post actions when something changes in the database tables.
ASSIGNMENT NO 4
PL / SQL
PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code.
Benefits of PL/SQL:
PL/SQL can improve the performance of an application. The benefits differ depending on the execution environment:
• PL/SQL can be used to group SQL statements together within a single block and to send the entire block to the server in a single call thereby reducing networking traffic. Without PL/SQL, the SQL statements are sent to the Oracle server one at a time. Each SQL statement results in another call to the Oracle server and higher performance overhead. In a network environment, the overhead can become significant.
• PL/SQL can also operate with Oracle server application development tools such as Oracle forms and Oracle reports.
PL/SQL Block Structure:
Every unit of PL/SQL comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks which can contain any number of nested sub-blocks. Therefore one block can represent a small part of another block, which in turn can be part of the whole unit of code.
Modularized program development:
• Group logically related statements within blocks.
• Nest sub-blocks inside larger blocks to build powerful programs.
• Break down a complex problem into a set of manageable well defined logical modules and implement the modules with blocks.
• Place reusable PL/SQL code in libraries to be shared between Oracle forms and Oracle reports, applications, or store it in a Oracle server to make it accessible to any application that can interact with an Oracle database.
Identifiers/Variables:
In PL/SQL, you can use identifiers to do the following:
• Declare variables, cursors, constants and exceptions and then use them in SQL and procedural statements.
• Declare variables belonging to scalar, reference, composite and large object (LOB) data types.
• Declare variables dynamically based on the data structure of tables and columns in the database.
Procedural Language Control Structures:
• Execute a sequence of statements conditionally.
• Execute a sequence of statements iteratively in a loop.
• Process individually the rows returned by a multiple row query with a cursor.
Errors:
• Process Oracle server error with exception-handling routines.
• Declare user-defined error conditions and process them with exception-handling routines.
PL/SQL Block Syntax:
DECLARE [Optional]
Variables, cursors, user defined exceptions
BEGIN [Mandatory]
- SQL Statements –
- PL/SQL Statements –
Exception [Optional]
-- Actions to be performed when errors occur ----
END [Mandatory];
Example:
DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT column_name
INTO v_variable
FROM table_name;
EXCEPTION
WHEN exception_name THEN
-
-
END;
PROCEDURE AND FUNCTION BLOCKS
Procedure:
- No return.
- PROCEDURE name IS
Function:
- Returns a value
- FUNCTION name RETURN data-type IS
Programming Constructs:
- Declaring Variables:
o Identifier [CONSTANT] data-type [NOT NULL]
[:= | DEFAULT expr];
- Assignment:
o Identifier := expr;
- IF Statement:
o IF condition THEN
Statements;
[ELSE IF condition THEN
Statements;]
[ELSE
Statements;]
END IF;
- CASE Statement:
o CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
.
.
WHEN expression THEN resultn
[ELSE resultn1;]
END;
- BASIC Loops:
o LOOP
Statement 1;
.
.
EXIT [WHEN condition];
END LOOP;
- WHILE Statement:
WHILE condition LOOP
Statement1;
.
.
END LOOP;
- FOR Statement:
FOR counter IN [REVERSE]
Lower_bound..upper_bound LOOP
Statement1;
.
.
END LOOP;
PRACTICE ASSIGNMENT
PROCEDURE AND FUNCTION BOCKS
a. Problem Statement:
Write a PL/SQL block which declares a variable and reads the last name of employee with id = 5 and outputs that to standard output.
Solution:
1. Declare a varchar variable
2. Write the select into clause inside PL/SQL Begin END block.
b. Problem Statement:
Write a PL/SQL block which declares a variable with a value and prints in all capitals if the value starts with ‘S’, in all smalls if it starts with ‘R’, and in initial capitals if otherwise.
Solution:
1. Use IF or CASE.
c. Problem Statement:
Write a PL/SQL block which declares a variable and reads the last name of employees and outputs that to standard output.
Solution:
1. Declare a varchar variable
2. Write a LOOP.
d. Problem Statement:
Create a stored procedure which takes deparment_id as parameter, inserts all employees of that department in a table called dept_employee with the same structure.
Solution:
1. Learn how to use parameter in to stored procedure.
e. Problem Statement:
Create a function which takes deparment_id as parameter and returns the name of the department.
Solution:
1. Learn how to use parameter in functions.
CURSORS
Every SQL statement executed by the Oracle server has an individual cursor associated with it and are called implicit cursors. There are two types of cursors.
Implicit cursors: Declared for all DML and PL/SQL SELECT statements.
Explicit cursors: Declared and names by the programmer.
Explicit Cursors:
a. Individually process each row returned by a multiple row select statement.
b. A PL/SQL program opens a cursor, processes rows returned by a query, and then closes the cursor. The cursor marks the current position in the active set.
1. Can process beyond the first row returned by the query, row by row.
2. Keep track of which row is currently being processed.
3. Allow the programmer to manually control explicit cursors in the PL/QL block.
Controlling Explicit Cursors:
1. Declare the cursor by naming it and defining the structure of the query to be performed. Within it.
2. Open the cursor: The OPEN statement executes the query and binds the variables that are referenced. Rows identified by the query are called the active set and are now available for fetching.
3. Fetch data from the cursor: After each fetch, you test the cursor for any existing row. If there are no more rows to process, then you must close the cursor.
4. Close the cursor: The CLOSE statement releases the active set of rows. It is now possible to reopen the cursor to establish a fresh active set.
Syntax:
Declaring a cursor:
CURSOR cursor_name IS
Select_statement;
Opening a cursor:
OPEN cursor_name;
Fetch data from a cursor:
FETCH cursor_name INTO [variable1, variable2,….]| record_name];
Closing a cursor:
Close cursor_name;
Example:
Set SERVEROUTPUT ON
DECLARE
V_empno employees.employee_id%TYPE;
V_ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
BEGIN
OPEN emp_cursor;
FOR I IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, b_ename;
DBMS_OUTPUT. PUT_LINE (TO_CHAR(v_empno) || ‘ ‘ || v_ename);
END LOOP
CLOSE emp_cursor;
Attributes of an Explicit Cursor:
o %ISOPEN [is cursor open]
o %NOTFOUND [is row not found]
o %FOUND [is row found]
o %ROWCOUNT [rows returned so far]
Cursors can be passed parameters. Cursors also have FOR UPDATE option which allows more fine grained control of locking at a table level. WHERE CURRENT OF can be used to apply the update or delete operation to current row in the cursor.
PATRICE ASSIGNEMENT
CURSOR
a. Problem Statement:
Declare a cursor which runs through all employees who belong to department with id = 2, open the cursor and close the cursor without doing anything.
Solution:
Use open and close operations on cursors. This is similar to opening and closing file handles.
b. Problem Statement:
Declare a cursor which runs through all employees who belong to department with id = 2, open the cursor and fetches one employee at a time, prints the last name and then closes the cursor after all employees are done.
Solution:
1. Declare cursor.
2. Open the cursor.
3. In a loop, fetch each row of a cursor into a variable.
4. Print the variable.
5. Continue the loop till there are no more rows.
6. Close the cursor.
c. Problem Statement:
Use a cursor to look at each employee who belongs to department with id 10, check the job grade and append NEW_ to all job_grades.
Solution:
1. Use the WHERE CURRENT to identify the currently fetched row from cursor and run the update query.
2. This is useful for update and deletes.
d. Problem Statement:
Define a cursor which runs through all employees who belong to department with id = 2.
Solution:
Use Declare cursor syntax within the PL/SQL block to define the cursor with a simple query which is select from where using employee table and a condition department_id = 2
TRIGGERS
What are triggers?
A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, of the database. It executes implicitly whenever a particular event takes place. It can either be:
1. Application trigger: Fires whenever an event occurs with a particular application.
2. Database Trigger: Fires whenever a data event (such as DML) occurs on a schema or database.
When triggers used?
Triggers are to be used when we want to perform related actions and when we want to centralize global operations. Create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy. The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications. Triggers should not be used where functionality needed is already built into the Oracle server or when it is duplicating what is done by other triggers.
Example:
A trigger called check_sal which runs every time a new employee is getting inserted to enforce some rules on what should be the minimum salary.
Elements in a Trigger:
• Trigger timing
o For table: BEFORE, AFTER
o For view: INSTEAD OF
• Trigger event: INSERT, UPDATE, OR DELETE
• Table name: On table, view
• Trigger Type: Row or statement
• When clause: Restricting condition
• Trigger body: PL/SQL block
“Before triggers” execute the trigger body before the triggering DML event on a table. These are frequently used to determine whether that triggering statement should be allowed to complete. This situation enables you to eliminate unnecessary processing of the triggering statement and it eventual rollback in cases where an exception is raised in the triggering action.
“After triggers” are used when the triggering statement is to be completed before the triggering action and to perform a different action on the same triggering statement if a BEFORE trigger is already present.
“Instead of Triggers” are used to provide a transparent way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable. You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
Triggering user events:
o INSERT
o UPDATE
o DELETE
Trigger Components:
o Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all.
o Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.
Trigger Body:
The trigger body is a PL/SQL block or a call to a procedure.
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
Timing
Event1 [OR event2 OR event3]
ON table_name
Trigger_body
PRACTICE ASSIGNMENTS
TRIGGERS
a. Problem Statement:
Create a trigger which writes a record called “employees table being changed” with time in a log table whenever anyone attempts to change employees table.
Solution:
Write a statement trigger that records the fact that someone is changing the employees table in a log table using before trigger.
b. Problem Statement:
Create a trigger which writes a record called “employees table has been changed” with time in a log table whenever someone successfully changes the employee table.
Solution:
Write a statement trigger that records the fact that some-one changed the employees table in a log table using after trigger that acts on insert, update and delete operations.
SUPPLEMENTARY ASSIGNMENTS
1. Design a database for library management in the college, populate data and write queries to return information like books issued, books issued to user, number of books issued, books reserved, etc.
2. Design a database for fee management in the college, populating data on fee payments, fee receipts and writing querying to return data like uncollected fees, which paid fee, etc.
MINI PROJECT
FOR MINI-PROJECT
SYSTEM REQUIREMENT SPECIFICATION
Aim: To Prepare System Requirement Specification (SRS).
Objective: To collect system requirements, to understand the business cases, functional flow of the system and the preparation of Entity –Relationship (E-R) Diagram.
Software Requirements Specification
1 Problem Statement :
Identify the product whose software requirements are specified in this document, including the revision or release number. Describe the scope of the product that is covered by this SRS, particularly if this SRS describes only part of the system or a single subsystem.
2 Document Conventions:
Describe any standards or conventions that were followed when writing this SRS, such as fonts or highlighting that have special significance. For example, state whether priorities for higher-level requirements are assumed to be inherited by detailed requirements, or whether every requirement statement is to have its own priority.
3 Intended Audience and Reading Suggestions:
Describe the different types of reader that the document is intended for, such as developers, project managers, marketing staff, users, testers, and documentation writers. Describe what the rest of this SRS contains and how it is organized.
4 Project Scope:
Provide a short description of the software being specified and its purpose, including relevant benefits, objectives, and goals. Relate the software to corporate goals or business strategies.
5 References:
List any other documents or Web addresses to which this SRS refers. These may include user interface style guides, contracts, standards, system requirements specifications, use case documents, or a vision and scope document. Provide enough information so that the reader could access a copy of each reference, including title, author, version number, date, and source or location.
6 Product Perspective:
Describe the context and origin of the product being specified in this SRS. For example, state whether this product is a follow-on member of a product family, a replacement for certain existing systems, or a new, self-contained product. If the SRS defines a component of a larger system, relate the requirements of the larger system to the functionality of this software and identify interfaces between the two. A simple diagram that shows the major components of the overall system, subsystem interconnections, and external interfaces can be helpful.
7 Product Features:
Summarize the major features the product contains or the significant functions that it performs or lets the user perform. Organize the functions to make them understandable to any reader of the SRS. A picture of the major groups of related requirements and how they relate, such as a top level data flow diagram or a class diagram, is often effective.
8 User Classes and Characteristics:
Identify the various user classes that you anticipate will use this product. User classes may be differentiated based on frequency of use, subset of product functions used, technical expertise, security or privilege levels, educational level, or experience. Describe the pertinent characteristics of each user class. Certain requirements may pertain only to certain user classes. Distinguish the favored user classes from those who are less important to satisfy.
9 Operating Environment :
Describe the environment in which the software will operate, including the hardware platform, operating system and versions, and any other software components or applications with which it must peacefully coexist.
10 Design and Implementation Constraints:
Describe any items or issues that will limit the options available to the developers. These might include: corporate or regulatory policies; hardware limitations (timing requirements, memory requirements); interfaces to other applications; specific technologies, tools, and databases to be used; parallel operations; language requirements; communications protocols; security considerations; design conventions or programming standards (for example, if the customer’s organization will be responsible for maintaining the delivered software).
11 User Documentation:
List the user documentation components (such as user manuals, on-line help, and tutorials) that will be delivered along with the software. Identify any known user documentation delivery formats or standards.
12 Assumptions and Dependencies:
List any assumed factors (as opposed to known facts) that could affect the requirements stated in the SRS. These could include third-party or commercial components that you plan to use, issues around the development or operating environment, or constraints. The project could be affected if these assumptions are incorrect, are not shared, or change. Also identify any dependencies the project has on external factors, such as software components that you intend to reuse from another project, unless they are already documented elsewhere (for example, in the vision and scope document or the project plan).
System Features:
This template illustrates organizing the functional requirements for the product by system features, the major services provided by the product. You may prefer to organize this section by use case, mode of operation, user class, object class, functional hierarchy, or combinations of these, whatever makes the most logical sense for your product.>
1 System Feature
1.Description and Priority
Provide a short description of the feature and indicate whether it is of High, Medium, or Low priority. You could also include specific priority component ratings, such as benefit, penalty, cost, and risk .
2. Functional Requirements
Itemize the detailed functional requirements associated with this feature. These are the software capabilities that must be present in order for the user to carry out the services provided by the feature, or to execute the use case.
Each requirement should be uniquely identified with a sequence number or a meaningful tag of some kind.
REQ-1:
REQ-2:
External Interface Requirements
1 User Interfaces
Describe the logical characteristics of each interface between the software product and the users. This may include sample screen images, any GUI standards or product family style guides that are to be followed, screen layout constraints, standard buttons and functions (e.g., help) that will appear on every screen, keyboard shortcuts, error message display standards, and so on. Define the software components for which a user interface is needed. Details of the user interface design should be documented in a separate user interface specification. communication security or encryption issues, data transfer rates, and synchronization mechanisms.
Other Nonfunctional Requirements
1 Performance Requirements
If there are performance requirements for the product under various circumstances, state them here and explain to help the developers understand the intent and make suitable design choices. Specify the timing relationships for real time systems. Make such requirements as specific as possible. You may need to state performance requirements for individual functional requirements or features.
Security Requirements
Specify any requirements regarding security or privacy issues surrounding use of the product or protection of the data used or created by the product. Define any user identity authentication requirements. Refer to any external policies or regulations containing security issues that affect the product. Define any security or privacy certifications that must be satisfied.
Software Quality Attributes
Specify any additional quality characteristics for the product that will be important to either the customers or the developers. Some to consider are: adaptability, availability, correctness, flexibility, interoperability, maintainability, portability, reliability, reusability, robustness, testability, and usability. Write these to be specific, quantitative, and verifiable when possible. At the least, clarify the relative preferences for various attributes, such as ease of use over ease of learning.
Other Requirements
Define any other requirements not covered elsewhere in the SRS. This might include database requirements, internationalization requirements, legal requirements, reuse objectives for the project, and so on. Add any new sections that are pertinent to the project.
Appendix A: Glossary
Define all the terms necessary to properly interpret the SRS, including acronyms and abbreviations. You may wish to build a separate glossary that spans multiple projects or the entire organization, and just include terms specific to a single project in each SRS.
ASSIGNMENT NO – 5
CLASS RESPONSIBILITY COLLABORATOR
Aim: To Prepare Class Responsibility Collaborator (CRC) Cards and the Class subsystem Diagram.
Objectives:
The typical objectives of a CRC card are to informally document the responsibilities and collaborators of a type or class.
1. Think in an Object Oriented manor.
2. Use a responsibility-driven approach to object oriented development.
3. Coordinate a CRC card session.
4. Use CRC cards of analysis and design.
Conceptual Overview
A CRC cards is an index card that is use to represent the responsibilities of classes and the interaction between the classes. CRC cards are an informal approach to object oriented modeling. The cards are created through scenarios, based on the system requirements, that model the behavior of the system. The name CRC comes from Class, Responsibilities, and Collaborators which the creators found to be the essential dimensions of object oriented modeling.
CRC cards where introduced by Kent Beck and Ward Cunningham in there paper "A Laboratory for Teaching Object-Oriented Thinking" released in OOPLSA '89. There original purpose was to teach programmers the object-oriented paradigm. When Kent Beck wrote the draft version of their paper he changed Collaborators to helpers. Ward Cunningham changed it back to Collaborators when he reviewed the paper. The initials of Cunningham's son are CRC.
Why uses CRC cards?
• They are portable... No computers are required so they can be used anywhere.
• They allow the (group) participants to experience first hand how the system will work. No computer tool can replace the interaction that happens by physically picking up the cards and playing the roll of that object.
• They are a useful tool for teaching the object-oriented paradigm.
• They can be used as a methodology them selves or as a front end to a more formal methodology such as Booch, Wirfs-Brock, Jacobson, etc.
• The typical benefits of a CRC card are to maximize object abstraction, cohesion, and encapsulation.
The Cards
The cards should look something like this:
|Class Name: |
|Responsibilities |Collaborators |
|1. |1. |
|2. |2. |
|3. |3. |
The exact format of the card can be customized to the preferences of the group, but the minimal required information is the name of the class, it's responsibilities and the collaborators. The back of the card can be used for a description of the class. During the design phase attributes of the class can be recorded on the back as well. One way to think of the card is the front as the public information, and the back as the encapsulated, implementation details.
Creating class
The first step in modeling a system in the object-oriented paradigm is to identify the class in the problem domain. So this is the first step in a CRC card session. Using the problem statement or requirements document, identify the classes that are obvious in the subset of the problem that is going to be explored in this session. One useful tool is to find all of the nouns and verbs in the problem statement. The nouns are a good key to what class are in the system, and the verbs show what there responsibilities are going to be. Use this information for the basis of a brainstorming session and identify all the class that you see. Remember in a brainstorming session there should be no or little discussion of the ideas. Record them and filter the results after the brainstorming. After the classes have been chosen pass out cards and assign the class to the member of the group. Each person should be responsible for at least on class. They are the owner of that class for the session. Each person records the name of their class on a card. One class per card.
Responsibilities
Once a reasonable set of classes have be assigned to the group, responsibilities can be added. Add responsibilities that are obvious from the requirements or the name of the class. You don't need to find them all or any. The scenarios will make them more obvious. The advantage of finding some in the beginning is that it helps provide a starting place.
Guidelines
• Use the CRC cards mostly as an elicitation and modeling techniques, rather than as a documentation tool.
• As soon as practical, transfer the information into deliverable documents or Java class comments. There is little benefit in maintaining the CRC cards thereafter.
• Use large index cards; the small ones are too little to hold the necessary information.
• Concentrate on the responsibilities for doing and enforcing rather than for knowing (which promotes a data-driven design).
• List collaborators across from the associated responsibilities so that readers can tell which collaborators help implement which responsibilities.
• During requirements elicitation and analysis, concentrate on types rather than their implementations (classes).
Output: CRC cards and Class Subsystem Diagram.
|Bank Account: |
|Super Classes: |
|Sub Classes : |
|Description: |
|Attributes: |
|Name |Description |
|1.Account manager |A unique value to identify Account |
|Responsibilities : |
|Name |Collaborator |
|1. | |
|2. | |
CRC cards for analysis.
Analysis is the process of modeling what a system does, not how it does it. Lets start by modeling a sample system using CRC cards.
Exercise
Do a CRC analysis
• What cards do you create?
• What are the responsibilities of each object?
• What other object does each object work with to accomplish these responsibilities?
Activity 2
Problem statement.
This application will support the operations of a technical library for an R&D organization. This includes the searching for and lending of technical library materials, including books, videos, and technical journals. Users will enter their company ids in order to use the system; and they will enter material ID numbers when checking out and returning items.
Each borrower can be lent up to five items. Each type of library item can be lent for a different period of time (books 4 weeks, journals 2 weeks, videos 1 week). If returned after their due date, the library user's organization will be charged a fine, based on the type of item ( books Rs10/day, journals Rs 20/day, videos Rs30/day).
Materials will be lent to employees with no overdue lendables, fewer than five articles out, and total fines less than Rs100.
With this problem statement identify the class and assign them to the group members. Make a card for each class.
Some ideas for class:
application , library, material, books, videos, journal, company, system, ID numbers , item, borrower, date, organization, employee, article, fine
This list is made up of the nouns in the problem statement. It is up to you to decide which ones if any are good classes for this problem. Now let’s try our first scenario, taken from the requirement that the system must allow users to check out lendables.
Activity 3
• Do CRC cards for a bookstore website
• Do CRC cards for an ATM
ASSIGNMENT NO - 6
USE CASE DIAGRAM.
Aim: To Prepare the Use Case Diagram.
Objective: To find actors, use cases, to prepare use case documentation and the use case
Diagram (Context Level and the Requirements Level).
Theory:
01] About Actors
02] About use cases
03] About Use Case Diagram
a) Contents
b) Common Uses.
Use Case Model:
The Use Case Model captures the requirements of a system. Use cases are a means of communicating with users and other stakeholders about what the system is intended to do.
Actors
A Use Case Diagram shows the interaction between the system and entities external to the system. These external entities are referred to as Actors. Actors represent roles which may include human users, external hardware or other systems. An actors is usually drawn as a named stick figure, or alternatively as a class rectangle with the «actor» keyword.
[pic] [pic]
Actors can generalize other actors as detailed in the following diagram: [pic]
Use Cases
A use case is a single unit of meaningful work. It provides a high-level view of behavior observable to someone or something outside the system. The notation for a use case is an ellipse.
[pic]
The notation for using a use case is a connecting line with an optional arrowhead showing the direction of control. The following diagram indicates that the actor Customer uses the Withdraw use case.
[pic]
The uses connector can optionally have multiplicity values at each end, as in the following diagram which shows that a customer may only have one withdrawal session at a time, but a bank may have any number of customers making withdrawals concurrently.
[pic]
Use Case Definition
A Use Case Typically Includes:
• Name and Description
• Requirements
• Constraints
• Scenarios
• Scenario Diagrams
• Additional information.
Name and Description
A use case is normally named as a verb-phrase and given a brief informal textual description.
Requirements
The requirements define the formal functional requirements that a use case must supply to the end user. They correspond to the functional specifications found in structured methodologies. A requirement is a contract or promise that the Use Case will perform an action or provide some value to the system.
Constraints
A constraint is a condition or restriction that a Use Case operates under and includes pre, post and invariant conditions. A precondition specifies the conditions that need to be met before the Use Case can proceed. A post condition is used to document the change in conditions that must be true after the execution of the Use Case. An invariant condition specifies the conditions that are true throughout the execution of the Use Case
Scenarios
A Scenario is a formal description of the flow of events that occur during the execution of a Use Case instance. It defines the specific sequence of events between the system and the external Actors. It is normally described in text and corresponds to the textual representation of the Sequence Diagram.
Including Use Cases
Uses Cases may contain the functionality of another Use Case as part of their normal processing. In general it is assumed that any included use case will be called every time the basic path is run. An example of this is to have the execution of the Use Case to be run as part of a Use Case .
[pic]
Use Cases may be included by one or more Use Case, helping to reduce the level of duplication of functionality by factoring out common behavior into Use Cases that are re-used many times.
Extending Use Cases
One Use Case may be used to extend the behavior of another; this is typically used in exceptional circumstances. For example, if before modifying a particular type of customer order, a user must get approval from some higher authority, then the Use Case may optionally extend the regular Use Case.
[pic]
Sample Use-Case Diagram to fulfill orders:
[pic]
Example - Use case Diagram for ATM System
[pic]
Format for Use Case Documentation:
|Type |Type Description |
|Use Case Id | |
|Use Case Name | |
|Description | |
|Pre-Condition | |
|Post-Condition | |
|Basic Flow | |
|Alternate Flow #1…..& so on | |
Input: Refer UML User Guide – Rambaugh for Object Oriented Analysis and Design (OOAD)
Output: Use Case Document and Use Case Diagram.
ASSIGNMENT NO - 7
CLASS DIAGRAM
Aim: To Prepare the Class Diagram.
Objective: To find classes, relationship, to prepare the class Diagram.
Theory:
01] About Classes:
a)Symbol
b)Syntax for Attribute and Operations.(Scope, Visibility, Property Strings)
02] Relationships with example.
03] About Class Diagram
a) Contents
b) Common Uses.
Class Diagram
The Class diagram shows the building blocks of any object-orientated system. Class diagrams depict the static view of the model or part of the model, describing what attributes and behaviors it has rather that detailing the methods for achieving operations. Class diagrams are most useful to illustrate relationships between classes and interfaces. Generalizations, aggregations, and associations are all valuable in reflecting inheritance, composition or usage, and connections, respectively.
The diagram below illustrates aggregation relationships between classes. The lighter aggregation indicates that the class Account uses AddressBook, but does not necessarily contain an instance of it. The strong, composite aggregations by the other connectors indicate ownership or containment of the source classes by the target classes, for example Contact and ContactGroup values will be contained in AddressBook.
Classes
A class is an element that defines the attributes and behaviors that an object is able to generate. The behavior is the described by the possible messages the class is able to understand along with operations that are appropriate for each message. Classes may also contain definitions of constraints tagged values and stereotypes.
Class Notation
Classes are represented by rectangles which show the name of the class and optionally the name of the operations and attributes. Compartments are used to divide the class name, attributes and operations. Additionally constraints, initial values and parameters may be assigned to classes.
[pic]
In the diagram the class contains the class name in the topmost compartment, the next compartment details the attributes, with the "center" attribute showing initial values. The final compartment shows the operations, the setWidth, setLength and setPosition operations showing their parameters. The notation that precedes the attribute or operation name indicates the visibility of the element, if the + symbol is used the attribute or operation has a public level of visibility, if a - symbol is used the attribute or operation is private. In addition the # symbol allows an operation or attribute to be defined as protected and the ~ symbol indicates package visibility.
[pic]
Interfaces
An interface is a specification of behavior that implementers agree to meet. It is a contract. By realizing an interface, classes are guaranteed to support a required behavior, which allows the system to treat non-related elements in the same way – i.e. through the common interface.
[pic]
Interfaces may be drawn in a similar style to a class, with operations specified, as shown below. They may also be drawn as a circle with no explicit operations detailed. When drawn as a circle, realization links to the circle form of notation are drawn without target arrows.
[pic]
Tables
A table is a stereotyped class. It is drawn with a small table icon in the upper right corner. Table attributes are stereotyped «column». Most tables will have a primary key, being one or more fields that form a unique combination used to access the table, plus a primary key operation which is stereotyped «PK». Some tables will have one or more foreign keys, being one or more fields that together map onto a primary key in a related table, plus a foreign key operation which is stereotyped «FK».
[pic]
Associations
An association implies two model elements have a relationship - usually implemented as an instance variable in one class. This connector may include named roles at each end, cardinality, direction and constraints. Association is the general relationship type between elements. For more than two elements, a diagonal representation toolbox element can be used as well. When code is generated for class diagrams, associations become instance variables in the target class.
[pic]
Generalizations
A generalization is used to indicate inheritance. Drawn from the specific classifier to a general classifier, the generalize implication is that the source inherits the target's characteristics. The following diagram shows a parent class generalizing a child class. Implicitly, an instantiated object of the Circle class will have attributes x_position, y_position and radius and a method display(). Note that the class Shape is abstract, shown by the name being italicized.
[pic]
The following diagram shows an equivalent view of the same information.
[pic]
Aggregations
Aggregations are used to depict elements which are made up of smaller components. Aggregation relationships are shown by a white diamond-shaped arrowhead pointing towards the target or parent class.
A stronger form of aggregation - a composite aggregation - is shown by a black diamond-shaped arrowhead and is used where components can be included in a maximum of one composition at a time. If the parent of a composite aggregation is deleted, usually all of its parts are deleted with it; however a part can be individually removed from a composition without having to delete the entire composition. Compositions are transitive, asymmetric relationships and can be recursive.
The following diagram illustrates the difference between weak and strong aggregations. An address book is made up of a multiplicity of contacts and contact groups. A contact group is a virtual grouping of contacts; a contact may be included in more than one contact group. If you delete an address book, all the contacts and contact groups will be deleted too; if you delete a contact group, no contacts will be deleted.
[pic]
Association Classes
An association class is a construct that allows an association connection to have operations and attributes. The following example shows that there is more to allocating an employee to a project than making a simple association link between the two classes: the role that the employee takes up on the project is a complex entity in its own right and contains detail that does not belong in the employee or project class. For example, an employee may be working on several projects at the same time and have different job titles and security levels on each.
[pic]
Dependencies
A dependency is used to model a wide range of dependent relationships between model elements. It would normally be used early in the design process where it is known that there is some kind of link between two elements but it is too early to know exactly what the relationship is. Later in the design process, dependencies will be stereotyped (stereotypes available include «instantiate», «trace», «import» and others) or replaced with a more specific type of connector.
Traces
The trace relationship is a specialization of a dependency, linking model elements or sets of elements that represent the same idea across models. Traces are often used to track requirements and model changes. As changes can occur in both directions, the order of this dependency is usually ignored. The relationship's properties can specify the trace mapping, but the trace is usually bi-directional, informal and rarely computable.
Realizations
The source object implements or realizes the destination. Realize is used to express traceability and completeness in the model - a business process or requirement is realized by one or more use cases which are in turn realized by some classes, which in turn are realized by a component, etc. Mapping requirements, classes, etc. across the design of your system, up through the levels of modelling abstraction, ensures the big picture of your system remembers and reflects all the little pictures and details that constrain and define it. A realization is shown as a dashed line with a solid arrowhead and the «realize» stereotype.
[pic]
Nestings
A nesting is connector that shows that the source element is nested within the target element. The following diagram shows the definition of an inner class although in EA it is more usual to show them by their position in the Project View hierarchy.
[pic]
[pic]
Class Diagram for ATM System
Input: Refer UML User Guide – Rambaugh for Object Oriented Analysis and Design (OOAD)
Output: Class Diagram.
ASSIGNMENT NO. 8
ACTIVITY DIAGRAM
Aim: To Prepare the Activity Diagram.
Objective: To learn behavioral modeling and creation of activity diagram.
Theory:
01] About Activity Diagram
a) Contents
b) Common Uses.
Input: Refer UML User Guide – Rambaugh for Object Oriented Analysis and Design
(OOAD)
Output: Activity Diagram.
In UML an activity diagram is used to display the sequence of activities. Activity Diagrams show the workflow from a start point to the finish point detailing the many decision paths that exist in the progression of events contained in the activity. They may be used to detail situations where parallel processing may occur in the execution of some activities. Activity Diagrams are useful for Business Modeling where they are used for detailing the processes involved in business activities.
Example Diagram
[pic]
[pic]
ASSIGNMENT NO. 9
SEQUENCE DIAGRAMS
Aim: To Prepare the Sequence and Collaboration Diagram.
Objective: To learn behavioral modeling and creation of sequence diagram.
Theory:
01] About Sequence and Collaboration Diagram
a) Contents
b) Common Uses.
Input: Refer UML User Guide – Rambaugh for Object Oriented Analysis and Design
(OOAD)
Output: Sequence and Collaboration Diagram.
A sequence diagram is a form of interaction diagram which shows objects as lifelines running down the page and with their interactions over time represented as messages drawn as arrows from the source lifeline to the target lifeline. Sequence diagrams are good at showing which objects communicate with which other objects and what messages trigger those communications. Sequence diagrams are not intended for showing complex procedural logic.
Lifelines
A lifeline represents an individual participant in a sequence diagram. A lifeline will usually have a rectangle containing its object name. If its name is self then that indicates that the lifeline represents the classifier which owns the sequence diagram..
[pic]
Sometimes a sequence diagram will have a lifeline with an actor element symbol at its head. This will usually be the case if the sequence diagram is owned by a use case. Boundary, control and entity elements from robustness diagrams can also own lifelines.
[pic]
Messages
Messages are displayed as arrows. Messages can be complete, lost or found; synchronous or asynchronous; call or signal. In the following diagram, the first message is a synchronous message (denoted by the solid arrowhead) complete with an implicit return message; the second message is asynchronous (denoted by line arrowhead) and the third is the asynchronous return message (denoted by the dashed line).
[pic]
ExecutionOccurrence
A thin rectangle running down the lifeline denotes the execution occurrence or activation of a focus of control. In the previous diagram, there are three execution occurrences. The first is the source object sending two messages and receiving two replies; the second is the target object receiving a synchronous message and returning a reply; and the third is the target object receiving an asynchronous message and returning a reply.
SelfMessage
A self message can represent a recursive call of an operation, or one method calling another method belonging to the same object. It is shown as creating a nested focus of control in the lifeline’s execution occurrence.
[pic]
Lost and Found Messages
Lost messages are those that are either sent but do not arrive at the intended recipient, or which go to a recipient not shown on the current diagram. Found messages are those that arrive from an unknown sender, or from a sender not shown on the current diagram. They are denoted going to or coming from an endpoint element.
[pic]
Lifeline Start and End
A lifeline may be created or destroyed during the timescale represented by a sequence diagram. In the latter case, the lifeline is terminated by a stop symbol, represented as a cross. In the former case, the symbol at the head of the lifeline is shown at a lower level down the page than the symbol of the object that caused the creation. The following diagram shows an object being created and destroyed.
[pic]
Duration and Time Constraints
By default, a message is shown as a horizontal line. Since the lifeline represents the passage of time down the screen, when modeling a real-time system, or even a time-bound business process, it can be important to consider the length of time it takes to perform actions. By setting a duration constraint for a message, the message will be shown as a sloping line.
[pic]
Combined Fragments
It was stated earlier that Sequence diagrams are not intended for showing complex procedural logic. While this is the case, there are a number of mechanisms that do allow for adding a degree of procedural logic to diagrams and which come under the heading of combined fragments. A combined fragment is one or more processing sequence enclosed in a frame and executed under specific named circumstances. The fragments available are:
• Alternative fragment (denoted “alt”) models if…then…else constructs.
• Option fragment (denoted “opt”) models switch constructs.
• Break fragment models an alternative sequence of events that is processed instead of the whole of the rest of the diagram.
• Parallel fragment (denoted “par”) models concurrent processing.
• Weak sequencing fragment (denoted “seq”) encloses a number of sequences for which all the messages must be processed in a preceding segment before the following segment can start, but which does not impose any sequencing within a segment on messages that don’t share a lifeline.
• Strict sequencing fragment (denoted “strict”) encloses a series of messages which must be processed in the given order.
• Negative fragment (denoted “neg”) encloses an invalid series of messages.
• Critical fragment encloses a critical section.
• Ignore fragment declares a message or message to be of no interest if it appears in the current context.
• Consider fragment is in effect the opposite of the ignore fragment: any message not included in the consider fragment should be ignored.
• Assertion fragment (denoted “assert”) designates that any sequence not shown as an operand of the assertion is invalid.
• Loop fragment encloses a series of messages which are repeated.
The following diagram shows a loop fragment.
[pic]
There is also an interaction occurrence, which is similar to a combined fragment. An interaction occurrence is a reference to another diagram which has the word "ref" in the top left corner of the frame, and has the name of the referenced diagram shown in the middle of the frame.
Gate
A gate is a connection point for connecting a message inside a fragment with a message outside a fragment. EA shows a gate as a small square on a fragment frame.
[pic]
Part Decomposition
An object can have more than one lifeline coming from it. This allows for inter- and intra-object messages to be displayed on the same diagram.[pic]
State Invariant / Continuations
A state invariant is a constraint placed on a lifeline that must be true at run-time. It is shown as a rectangle with semi-circular ends.
[pic]
A Continuation has the same notation as a state invariant but is used in combined fragments and can stretch across more than one lifeline.
ASSIGNMENT NO. 10
TESTING
Types of Testing
• Black box testing - not based on any knowledge of internal design or code. Tests are based on requirements and functionality.
• White box testing - based on knowledge of the internal logic of an application's code. Tests are based on coverage of code statements, branches, paths, conditions.
• unit testing - the most 'micro' scale of testing; to test particular functions or code modules. Typically done by the programmer and not by testers, as it requires detailed knowledge of the internal program design and code. Not always easily done unless the application has a well-designed architecture with tight code; may require developing test driver modules or test harnesses.
• incremental integration testing - continuous testing of an application as new functionality is added; requires that various aspects of an application's functionality be independent enough to work separately before all parts of the program are completed, or that test drivers be developed as needed; done by programmers or by testers.
• integration testing - testing of combined parts of an application to determine if they function together correctly. The 'parts' can be code modules, individual applications, client and server applications on a network, etc. This type of testing is especially relevant to client/server and distributed systems.
• functional testing - black-box type testing geared to functional requirements of an application; this type of testing should be done by testers. This doesn't mean that the programmers shouldn't check that their code works before releasing it (which of course applies to any stage of testing.)
• system testing - black-box type testing that is based on overall requirements specifications; covers all combined parts of a system.
• end-to-end testing - similar to system testing; the 'macro' end of the test scale; involves testing of a complete application environment in a situation that mimics real-world use, such as interacting with a database, using network communications, or interacting with other hardware, applications, or systems if appropriate.
• sanity testing - typically an initial testing effort to determine if a new software version is performing well enough to accept it for a major testing effort. For example, if the new software is crashing systems every 5 minutes, bogging down systems to a crawl, or destroying databases, the software may not be in a 'sane' enough condition to warrant further testing in its current state.
• regression testing - re-testing after fixes or modifications of the software or its environment. It can be difficult to determine how much re-testing is needed, especially near the end of the development cycle. Automated testing tools can be especially useful for this type of testing.
• acceptance testing - final testing based on specifications of the end-user or customer, or based on use by end-users/customers over some limited period of time.
• load testing - testing an application under heavy loads, such as testing of a web site under a range of loads to determine at what point the system's response time degrades or fails.
• stress testing - term often used interchangeably with 'load' and 'performance' testing. Also used to describe such tests as system functional testing while under unusually heavy loads, heavy repetition of certain actions or inputs, input of large numerical values, large complex queries to a database system, etc.
• performance testing - term often used interchangeably with 'stress' and 'load' testing. Ideally 'performance' testing (and any other 'type' of testing) is defined in requirements documentation or QA or Test Plans.
• usability testing - testing for 'user-friendliness'. Clearly this is subjective, and will depend on the targeted end-user or customer. User interviews, surveys, video recording of user sessions, and other techniques can be used. Programmers and testers are usually not appropriate as usability testers.
• install/uninstall testing - testing of full, partial, or upgrade install/uninstall processes.
• recovery testing - testing how well a system recovers from crashes, hardware failures, or other catastrophic problems.
• security testing - testing how well the system protects against unauthorized internal or external access, willful damage, etc; may require sophisticated testing techniques.
• compatability testing - testing how well software performs in a particular hardware/software/operating system/network/etc. environment.
• exploratory testing - often taken to mean a creative, informal software test that is not based on formal test plans or test cases; testers may be learning the software as they test it.
• ad-hoc testing - similar to exploratory testing, but often taken to mean that the testers have significant understanding of the software before testing it.
• user acceptance testing - determining if software is satisfactory to an end-user or customer.
• comparison testing - comparing software weaknesses and strengths to competing products.
• alpha testing - testing of an application when development is nearing completion; minor design changes may still be made as a result of such testing. Typically done by end-users or others, not by programmers or testers.
• beta testing - testing when development and testing are essentially completed and final bugs and problems need to be found before final release. Typically done by end-users or others, not by programmers or testers.
• mutation testing - a method for determining if a set of test data or test cases is useful, by deliberately introducing various code changes ('bugs') and retesting with the original test data/cases to determine if the 'bugs' are detected. Proper implementation requires large computational resources.
Levels of Testing
[pic]
ASSIGNMENT NO. 11
ATM TESTING
Aim: To Prepare and Implement Manual Test Cases.
Objective: To learn Test Case Design and Manual Testing
Functional Test Cases for Example ATM System
The following initial test cases can be identified early in the design process for checking that the implementation is basically correct. These cases represent an initial check that the functionality specified by the use cases is present.
|Use Case |Function Being Tested |Initial System State |Input |Expected Output |
|System Startup |System is started when the switch|System is off |Activate the "on" switch|System requests initial cash amount |
| |is turned "on" | | | |
|System Startup |System accepts initial cash |System is requesting cash |Enter a legitimate |System is on |
| |amount |amount |amount | |
|System Startup |Connection to the bank is |System has just been |Perform a legitimate |System output should demonstrate that a |
| |established |turned on |inquiry transaction |connection has been established to the Bank|
|System Shutdown |System is shut down when the |System is on and not |Activate the "off" |System is off |
| |switch is turned "off" |servicing a customer |switch | |
|System Shutdown |Connection to the Bank is |System has just been shut | |Verify from the bank side that a connection|
| |terminated when the system is |down | |to the ATM no longer exists |
| |shut down | | | |
|Session |System reads a customer's ATM |System is on and not |Insert a readable card |Card is accepted; |
| |card |servicing a customer | |System asks for entry of PIN |
|Session |System rejects an unreadable card|System is on and not |Insert an unreadable |Card is ejected; |
| | |servicing a customer |card |System displays an error screen; |
| | | | |System is ready to start a new session |
|Session |System accepts customer's PIN |System is asking for entry|Enter a PIN |System displays a menu of transaction types|
| | |of PIN | | |
|Session |System allows customer to perform|System is displaying menu |Perform a transaction |System asks whether customer wants another |
| |a transaction |of transaction types | |transaction |
Black Box Testing
This specification describes testing methods which can be used to ensure that the system fulfils the requirements indicated in the project brief.
The following procedures are described:
1. Card Authorization
2. Cash Withdrawal (with or without receipt)
3. Display Balance (on screen or printout)
4. Order Statement or Chequebook
1. Card Authorization
Before any transactions take place the customer must insert their card into the system and enter their Personal Identification Number (PIN). If the number entered matches the number on the card the customer is allowed to continue with their transaction. If the customer fails to enter the correct PIN their card will be confiscated and a message is sent to the bank's main computer. The customer is allowed three attempts to enter their PIN.
OBJECTIVE: To test the PIN verification procedure
TEST 1 : Enter the correct PIN
EXPECTED OUTCOME : Successful PIN validation
TEST 2 :Enter 1 incorrect PIN and then the correct PIN
EXPECTED OUTCOME : Successful PIN validation
TEST 2 : Enter 2 incorrect PINs and then the correct PIN
EXPECTED OUTCOME : Successful PIN validation
TEST 3 : Enter 3 incorrect PINs
EXPECTED OUTCOME: Unsuccessful PIN validation - retain card, notify bank
Cash Withdrawal
The system should allow customers to withdraw money from their current accounts. Before allowing the withdrawal, the system should check the customer's balance to ensure that funds are available to cover it. If not, the system should offer any funds that are available (if any) or otherwise refuse the withdrawal. Each transaction should be recorded by the bank's main computer and the customer's accounts updated. A receipt for the transaction may be requested.
OBJECTIVE: To test the cash withdrawal procedure
TEST 1 : Request available funds without receipt
EXPECTED OUTCOME : Requested cash dispensed, accounts updated
TEST 2 : Request available funds with receipt
EXPECTED OUTCOME : Requested cash & receipt dispensed, accounts updated
TEST 3 : Request more than available funds, accept available
EXPECTED OUTCOME : Available cash dispensed, accounts updated
TEST 4 : Request more than available funds, refuse available
EXPECTED OUTCOME : No cash dispensed
TEST 5 : Request funds with none available
EXPECTED OUTCOME : No cash dispensed
Display Balance
The customer should be able to check the balance in their account. This must be retrieved from the bank's main computer. A printout of the balance and available funds may be requested.
OBJECTIVE: To test the balance checking procedure
TEST 1: Request balance on screen
EXPECTED OUTCOME: The balance is retrieved and displayed on the screen
TEST 2: Request printout of balance
EXPECTED OUTCOME: The balance is retrieved and a printout is dispensed
ASSIGNMENT NO. – 12
AUTOMATED TESTING
Aim: To Prepare and Implement Automated Test Cases.
Objective: To learn Test Case Design and Automated Testing.
QTP: Quick Test Professional
Testing Process
The Quick Test testing process consists of 7 main phases:
1 Preparing to record
Before you record a test, confirm that your application and Quick Test are set to match the needs of your test. Make sure your application displays elements on which you want to record,
such as a toolbar or a special window pane, for example, and that your application options are set as you expect for the purposes of your test. You should also view the settings in the Test Settings dialog box (Test > Settings) and the Options dialog box (Tools > Options) to ensure that Quick Test will record and store information appropriately. For example, you should confirm that the test is set to use the appropriate object repository mode.
2 Recording a session on your application
As you navigate through your application or Web site, Quick Test graphically displays each step you perform as a row in the Keyword View. A step is any user action that causes or makes a change in your application, such as clicking a link or image, or entering data in a form.
3 Enhancing your test
➤ Inserting checkpoints into your test lets you search for a specific value of a page, object, or text string, which helps you determine whether your application or site is functioning correctly.
➤ Broadening the scope of your test, by replacing fixed values with parameters, lets you check how your application performs the same operations with multiple sets of data.
➤ Adding logic and conditional or loop statements enables you to add sophisticated checks to your test.
4 Debugging your test
You debug a test to ensure that it operates smoothly and without interruption.
5 Running your test
You run a test to check the behavior of your application or Web site. While running, Quick Test opens the application, or connects to the Web site, and performs each step in your test.
6 Analyzing the test results
You examine the test results to pinpoint defects in your application.
7 Reporting defects
If you have Quality Center installed, you can report the defects you discover to a database.
ASSIGNMENT NO. - 13
XML DATABASE AND ITS RETRIEVAL
Aim: To implement the XML Database and its retrieval.
Objective: To understand XML,XML DTDs and its retrieval.
Theory:
01] About XML
Output: XML DTDs.
XML Data Embedded in HTML
What is XML?
1. XML stands for EXtensible Markup Language
2. XML is a markup language much like HTML
3. XML was designed to describe data
4. XML tags are not predefined. You must define your own tags
5. XML uses a Document Type Definition (DTD) or an XML Schema to describe the data
6. XML with a DTD or XML Schema is designed to be self-descriptive
7. XML is a W3C Recommendation
The Main Difference Between XML and HTML
XML was designed to carry data.
XML is not a replacement for HTML.
XML and HTML were designed with different goals:
XML was designed to describe data and to focus on what data is.
HTML was designed to display data and to focus on how data looks.
HTML is about displaying information, while XML is about describing information.
XML Does not DO Anything
XML was not designed to DO anything.
Maybe it is a little hard to understand, but XML does not DO anything. XML was created to structure, store and to send information.
The following example is a note to Tove from Jani, stored as XML:
| |
|Tove |
|Jani |
|Reminder |
|Don't forget me this weekend! |
| |
The note has a header and a message body. It also has sender and receiver information. But still, this XML document does not DO anything. It is just pure information wrapped in XML tags. Someone must write a piece of software to send, receive or display it.
XML is Free and Extensible
XML tags are not predefined. You must "invent" your own tags.
• The tags used to mark up HTML documents and the structure of HTML documents are predefined. The author of HTML documents can only use tags that are defined in the HTML standard (like , , etc.).
• XML allows the author to define his own tags and his own document structure.
• The tags in the example above (like and ) are not defined in any XML standard. These tags are "invented" by the author of the XML document.
• XML is a Complement to HTML
XML is not a replacement for HTML.
It is important to understand that XML is not a replacement for HTML. In future Web development it is most likely that XML will be used to describe the data, while HTML will be used to format and display the same data.
My best description of XML is this: XML is a cross-platform, software and hardware independent tool for transmitting information.
XML in Future Web Development
XML is going to be everywhere.
We have been participating in XML development since its creation. It has been amazing to see how quickly the XML standard has been developed and how quickly a large number of software vendors have adopted the standard.
We strongly believe that XML will be as important to the future of the Web as HTML has been to the foundation of the Web and that XML will be the most common tool for all data manipulation and data transmission.
An XML Data Island is XML data embedded into an HTML page. Here is how it works; assume we have the following XML document ("note.xml"):
| |
| |
|Tove |
|Jani |
|Reminder |
|Don't forget me this weekend! |
| |
| |
Then, in an HTML document, you can embed the XML file above with the tag. The id attribute of the tag defines an ID for the data island, and the src attribute points to the XML file to embed:
| |
| |
| |
| |
| |
However, the embedded XML data is, up to this point, not visible for the user.The next step is to format and display the data in the data island by binding it to HTML elements.
Bind Data Island to HTML Elements In the next example, we will embed an XML file called "cd_catalog.xml" into an HTML file. The HTML file looks like this:
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
The datasrc attribute of the tag binds the HTML table element to the XML data island. The datasrc attribute refers to the id attribute of the data island. tags cannot be bound to data, so we are using tags. The tag allows the datafld attribute to refer to the XML element to be displayed. In this case, it is datafld="ARTIST" for the element and datafld="TITLE" for the element in the XML file. As the XML is read, additional rows are created for each element.
ASSIGNMENT NO - 14
DATA MINING ALGORITHM
Aim: To implement the Data Mining Algorithm.
Objective: To understand Data Mining, Study of Decision Tree Algorithm.
Theory:
01] About Data Mining
02] Algorithm
Output: Decision-Tree Program.
-----------------------
Assignment No : 1
Assignment : Entity Relationship Diagram (ERD)
Date :
Remark :
[pic]
[pic]
[pic]
[pic]
Assignment No : 2
Assignment : DATA NORMALIZATION
Date :
Remark :
Assignment No : 3
Assignment : DATA FLOW DIAGRAM
Date :
Remark :
Enter
Student
And
Course
Student
Assignment No : 4
Assignment : PL / SQL
Date :
Remark :
Assignment No : 1
ASSIGNMENT : ENTITY RELATIONSHIP DIAGRAM (ERD)
Date :
Remark :
ASSIGNMENT : SYSTEM REQUIREMENT SPECIFICATION
Date :
Remark :
Assignment No : 5
ASSIGNMENT : CLASS RESPONSIBILITY COLLABORATOR
Date :
Remark :
Assignment No : 6
ASSIGNMENT : USE CASE DIAGRAM
Date :
Remark :
Assignment No : 7
ASSIGNMENT : CLASS DIAGRAM
Date :
Remark :
Assignment No : 8
ASSIGNMENT : ACTIVITY DIAGRAM
Date :
Remark :
Assignment No : 9
ASSIGNMENT : SEQUENCE DIAGRAM
Date :
Remark :
Assignment No : 10
ASSIGNMENT : TESING
Date :
Remark :
Assignment No : 9
ASSIGNMENT : XML DATABASE AND ITS RETRIEVAL
Date :
Remark :
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.