GROUP BY columns. Consider the relation PROFESSOR of Fig. 94.7. Each tuple of the above relation gives the name of a faculty and his department and academic year salary. Suppose that we want to know the number of faculty in each department and the result to be ordered by department. This query requests for each department a count of the number of faculty. Faculty are therefore categorized according to the attribute name department. As a result, department is referred to as a GROUp BY attribute. In SQL, the above query is formulated as follows: SELECT department, COUNT (faculty) BY The result of applying the COUNT aggregation function is a new relation with two attribute names. They are a GROUP BY attribute(department in this case)and a new attribute called CoUnT. The tuples are ordered lexicographically in ascending order according to the ORDER BY attribute, which is department in this case department count (facult Computer sc Electrical Eng Mechanical Eng The relations created through the CREATE TABLE command are known as base relations. a base relation exists physically and is stored as a file by the DBMS. SQL can be used to create views using the CREAtE VIEw command. In contrast to base relations the creation of a view results in a virtual relation that is, one that does not necessarily correspond to a physical file. Consider the database of Fig 94.4, and suppose that we want to create a view giving the name of all suppliers located in Metairie, the products each one provides, and the corresponding prices. Such a view, called METAIRIE_SUPPLIER, can be created as follows REATE VIEWI METAIRIE SUPPLIER PRO INFO. supplier name, product name, price PRO INFO, SUP LOC WIHIERE PRO INFO. supplier name SUP LOC. supplier name AND location = "Metairie" Because a view is a virtual relation that can be constructed from one or more relations, updating a view may lead to ambiguities. As a result, when a view is generated from more than one relation, there are, in general, estriction on updating such a view. Hierarchical Databases The hierarchical data model [Elmasri and Navathe, 1994 ] uses a tree data structure to conceptualize associations between different record types. In this model, record types are represented as nodes and associations as links Each record type, except the root, has only one parent; that is, only parent-child (or one-to-many) relationships allowed. This restriction gives hierarchical databases their simplicity. Since links are only one way, from a parent to a child, the design of hierarchical database management systems is made simpler, and only a small set of data manipulation commands are needed. Because only parent-child relationships are allowed, the hierarchical model cannot efficiently represent two main types of relationships: many-to-many relationships and the case where a record type is a child in more than one hierarchical schema. These two restrictions can be handled by allowing redundant record instances. However, such a duplication requires that all the copies of the same record should be kept consistent at all times. The example of Fig 94.8 shows a hierarchical schema. The schema gives the relationship between a DEPart MENT, its employees(D_EMPLOYEE), the projects(D_PROJECT)handled by the different departments, and how employees are assigned to these projects. It is assumed that an employee belongs to only one department a project is handled by only one department, and an employee can be assigned to several projects. Notice that since a project has several employees assigned to it, and an employee can be assigned to more than one project, the relationship between D_PROJECT and D_EMPLOYEE is many-to-many. To model this relationship mul tiple instances of the same record type D-EMPLOYEE may appear under different projects e 2000 by CRC Press LLC© 2000 by CRC Press LLC GROUP BY columns. Consider the relation PROFESSOR of Fig. 94.7. Each tuple of the above relation gives the name of a faculty and his department and academic year salary. Suppose that we want to know the number of faculty in each department and the result to be ordered by department. This query requests for each department a count of the number of faculty. Faculty are therefore categorized according to the attribute name department. As a result, department is referred to as a GROUP BY attribute. In SQL, the above query is formulated as follows: SELECT department, COUNT (faculty) FROM PROFESSOR GROUP BY department ORDER BY department The result of applying the COUNT aggregation function is a new relation with two attribute names. They are a GROUP BY attribute (department in this case) and a new attribute called COUNT. The tuples are ordered lexicographically in ascending order according to the ORDER BY attribute, which is department in this case: department COUNT (faculty) Computer Sc. 4 Electrical Eng. 3 Mechanical Eng. 2 The relations created through the CREATE TABLE command are known as base relations. A base relation exists physically and is stored as a file by the DBMS. SQL can be used to create views using the CREATE VIEW command. In contrast to base relations, the creation of a view results in a virtual relation, that is, one that does not necessarily correspond to a physical file. Consider the database of Fig. 94.4, and suppose that we want to create a view giving the name of all suppliers located in Metairie, the products each one provides, and the corresponding prices. Such a view, called METAIRIE_SUPPLIER, can be created as follows: CREATE VIEW METAIRIE_SUPPLIER AS SELECT PRO_INFO.supplier_name, product_name, price FROM PRO_INFO, SUP_LOC WHERE PRO_INFO.supplier_name = SUP_LOC.supplier_name AND location = “Metairie” Because a view is a virtual relation that can be constructed from one or more relations, updating a view may lead to ambiguities. As a result, when a view is generated from more than one relation, there are, in general, restrictions on updating such a view. Hierarchical Databases The hierarchical data model [Elmasri and Navathe, 1994] uses a tree data structure to conceptualize associations between different record types. In this model, record types are represented as nodes and associations as links. Each record type, except the root, has only one parent; that is, only parent-child (or one-to-many) relationships are allowed. This restriction gives hierarchical databases their simplicity. Since links are only one way, from a parent to a child, the design of hierarchical database management systems is made simpler, and only a small set of data manipulation commands are needed. Because only parent-child relationships are allowed, the hierarchical model cannot efficiently represent two main types of relationships: many-to-many relationships and the case where a record type is a child in more than one hierarchical schema. These two restrictions can be handled by allowing redundant record instances. However, such a duplication requires that all the copies of the same record should be kept consistent at all times. The example of Fig. 94.8 shows a hierarchical schema. The schema gives the relationship between a DEPART￾MENT, its employees (D_EMPLOYEE), the projects (D_PROJECT) handled by the different departments, and how employees are assigned to these projects. It is assumed that an employee belongs to only one department, a project is handled by only one department, and an employee can be assigned to several projects. Notice that since a project has several employees assigned to it, and an employee can be assigned to more than one project, the relationship between D_PROJECT and D_EMPLOYEE is many-to-many. To model this relationship mul￾tiple instances of the same record type D-EMPLOYEE may appear under different projects