Deep Queries and Subselects in Object-Oriented Database Systems

OBJECT ORIENTED DATABASE SYSTEMS CHAPTER 13 PAGE 372-381 INSTRUCTOR: DR. MORRIS LIAW presented by: D Gayitri Aneela Presentation ID: 11

DEEP QUERIES A deep query is one that depends on multiple fields scattered throughout a set of entities that form a hierarchy.

SUBSELECT Subselects are SELECT statements that are merged into other SELECT statements. found in WHERE, FROM, or HAVING clauses gathers data in subqueries, which then act as criteria in the enclosing query. Example: // First define Comparator Comparator<Expert> expertCmp = new Comparator<Expert>() { public int compare(Expert e1, Expert e2) { return e1.getName().compareTo(e2.getName()); } }; // then use Comparator to sort Query List< Expert > result = db.query(new Predicate< Expert >() { public boolean match(Expert expert) { return true; } },expertCmp);

OPERATORS: LIKE, IN, BETWEEN, EXIST SQL: SELECT * FROM trainee WHERE name LIKE A%"; DB4O: List<Trainee> students = container.query<Trainee>(new Predicate<Trainee>() { public boolean match(Trainee trainee) { return trainee.startsWith("A"); } });

SORTING DB40: // First define Comparator Comparator<Expert> expertCmp = new Comparator<Expert>() { public int compare(Expert e1, Expert e2) { return e1.getName().compareTo(e2.getName()); } }; // then use Comparator to sort Query List< Expert > result = db.query(new Predicate< Expert >() { public boolean match(Expert expert) { return true; } },expertCmp); SQL: SELECT * FROM expert ORDER BY name ASCENDING;

VIEWS Views are routinely used in SQL for filtering data from one or more tables, and providing only the relevant parts of the big picture. Data that otherwise would have been gathered by using a complex join can then be queried by a simple SELECT statement. Views are often used by administrators for security purposes, as many users have no need to view every item of data in a given row

AN EXAMPLE CREATE VIEW roster AS SELECT e.name as expert, c.name as course FROM course c, expert e, assignment a WHERE e.idEXPERT=a.fk_idEXPERT AND c.idCOURSE=a.fk_idCOURSE; SELECT * FROM roster ORDER by expert;

AGGREGATE FUNCTIONS AND GROUPING COUNT SUM, AVG, MAX, and MIN //doesn t provide any of these functions GROUP BY

COUNT In order to count the total number of rows in the database, you would execute a SELECT state- ment using the count() function, like this: SELECT COUNT(*) FROM courses; The number of objects returned by a db4o query can be found using the size method of fObjectSet result = container.get(new Course()); System.out.println("Courses in academy: +result.size()); objects are instantiated unless you iterate through the list by calling result.next() t

IMPLEMENTATION OF AVG int sum = 0; ObjectSet result = container.get(new Course()); while (result.hasNext()) { sum += ((Course) result.next()).getDuration(); } System.out.println("AVG:" + (result.size() == 0 ? 0 : sum / result.size()));

GROUP BY Grouping in SQL lets the results of a query be grouped together based on some common attribute. Grouping is often used in conjunction with the aggregate functions. SELECT expert, COUNT(course) FROM roster GROUP BY expert; change the object model itself in DB4O. To be more precise, we could add a list of Courses to the Expert class. Courses are directly grouped with the relevant Experts. ObjectSet result = container.get(new Course()); HashMap<Expert, Integer> groupedResults = new HashMap<Expert, Integer> while (result.hasNext()) { Integer count = groupedResults.get(result.getExpert()); groupedResults.put(result.getExpert(),(count == null ? 1 : count + 1)); }

CONSTRAINTS AND REFERENTIAL INTEGRITY Constraints enforce rules on tables. In relational databases these constraints are directly imposed on tables. Examples of constraints include primary keys, foreign keys, and NOT NULL, and many more can be defined. The group of constraints -Not Null/check can be implemented directly by the object itself. We can catch exceptions when a field is set to a null value (NOT NULL), or enforce other rules (CHECK) with assertions in the appropriate setters of the object. Instead of a primary key, it assigns a unique object ID (OID) to every object stored. The OID does not depend on the value of any field

THANK YOU!