tailieunhanh - Oracle SQL Plus The Definitive Guide- P4

Oracle SQL Plus The Definitive Guide- P4:Every day, computer professionals the world over wake up, travel to the office, sit down in front of a computer, and begin another day working with that database called Oracle. Programmers write queries and stored procedures. Database administrators monitor performance, make database changes, and perform other maintenance tasks. Operations people may need to back up or recover a database. | previous page page_10 next page Page 10 The Data Model Figure 1-2 shows an Entity Relationship Diagram or ERD for the sample database. EMPLOYEE PROJECT Figure 12. The sample database As you can see from the ERD there are only three entities EMPLOYEE PROJECT and PROJECT_HOURS. Table 12 gives a brief description of each entity. Table 12. Entity Descriptions Entity Name Description EMPLOYEE Contains one record for each employee. This record contains the employee s name hire date termination date and billing rate. The primary key is an arbitrary employee ID number. The termination date for current employees is set to NULL. PROJECT Contains one record for each project that an employee may work on. Contains the project name and budget. The primary key is an arbitrary project ID number. PROJECT_HOURS Each time an employee logs time to a project a record is generated in this table. The record contains the number of hours charged against the project as well as the total dollar amount charged. The dollar amount charged is calculated at the time the record is created because an employee s billing rate may fluctuate over time. The primary key is a combination key made up of an employee ID a project ID and the date. The number of employees and projects is fairly small. However there is a fairly large amount of data in the PROJECT_HOURS table to allow for the generation of previous page page_10 next page previous page page_11 next page Page 11 multiple page reports which are needed to demonstrate pagination page headings page footings and summarization. The Tables This section shows the column descriptions including column datatypes and lengths for each of the three sample tables. This is the same information you would get using SQL Plus s DESCRIBE command. The EMPLOYEE table Name Null Type EMPLOYEE ID EMPLOYEE_NAME EMPLOYEE HIRE DATE EMPLOYEE TERMINATION DATE EMPLOYEE_BILLING_RATE The PROJECT_HOURS table NOT NULL NUMBER VARCHAR2 40 DATE DATE NUMBER NAME NULL TYPE PROJECT ID .