This chapter is from the book
This chapter is from the book
This chapter is from the book
Lab 1.2 Exercise Answers
1.2.1 Answers
- Describe the nature of the relationship between the
ORDER_HEADER table and the ORDER_DETAIL table (Figure 1.23).
Answer: The relationship depicts a mandatory one-to-many relationship between the ORDER_HEADER and the ORDER_DETAIL tables. The ORDER_HEADER table contains data found only once for each order, such as the ORDER_ID, the CUSTOMER_ID, and the ORDER_DATE. The ORDER_DETAIL table holds information about the individual order lines of an order. One row in the order_header table must have one or many order details. One ORDER_DETAIL row must have one and only one corresponding row in the ORDER_HEADER table.
Mandatory Relationship on Both Ends
The mandatory relationship indicates from the ORDER_HEADER to ORDER_DETAIL that a row in the ORDER_HEADER table cannot exist unless a row in ORDER_DETAIL is created simultaneously. This is a "chicken and egg" problem whereby a row in the ORDER_HEADER table cannot be created without an ORDER_DETAIL row and vice versa. In fact, it really doesn't matter as long as you create the rows within one transaction. Furthermore, you must make sure that every row in the ORDER_HEADER table has at least one row in the ORDER_DETAIL table and rows in the ORDER_DETAIL table have exactly one corresponding row in the ORDER_HEADER table. There are various ways to physically implement this relationship.
Another example of a mandatory relationship on the figure is the relationship between ORDER_HEADER and CUSTOMER. You can see the bar on the many side of the relationship as an indication for the mandatory row. That means a customer must have placed an order before a row in the CUSTOMER table is saved, and an order can only be placed by a customer.
However, for most practical purposes a mandatory relationship on both ends is rarely implemented unless there is a very specific and important requirement.
No duplicates allowed
On the previous diagrams, such as Figure 1.23, you noticed that some foreign keys are part of the primary key. This is frequently the case in associative entities; in this particular example it requires the combination of ORDER_ID and PRODUCT_ID to be unique. Ultimately the effect is that a single order containing the same product twice is not allowed. Figure 1.28 lists sample data in the ORDER_DETAIL table for ORDER_ID 345 and PRODUCT_ID P90, which violates the primary key and is therefore not allowed. Instead, you must create one order with a quantity of 10 or create a second order with a different ORDER_ID so the primary key is not violated. You will learn about how Oracle responds with error messages when you attempt to violate the primary key constraint and other types of constraints in Chapter 10, "Insert, Update, and Delete."
)
Figure
1.28 Sample data of the ORDER_DETAIL table.
1.2.2 Answers
- One of the tables in Figure 1.24 is not fully normalized.
Which normal form is violated? Draw a new diagram.
Answer: The third normal form is violated on the ORDER_HEADER table. The RETAIL_PRICE column belongs to the PRODUCT table instead (Figure 1.29).
Figure
1.29 Fully normalized tables.
)
Figure
1.29 Third normal form states that every nonkey column must be a fact about the primary key column, which is the ORDER_ID column in the ORDER_HEADER table. This is clearly not the case in the ORDER_header table, as the retail_price column is not a fact about the ORDER_HEADER and does not depend upon the ORDER_ID; it is a fact about the PRODUCT. The QUOTED_PRICE column is included in the ORDER_DETAIL table because the price may vary over time, from order to order, and from customer to customer. (If you want to track any changes in the retail price, you may want to create a separate table called PRODUCT_PRICE_HISTORY that keeps track of the retail price per product and the effective date of each price change.) Table 1.2 provides a review of the normal forms.
Table 1.2 The Three Normal Forms
|
Description |
Rule |
|
First Normal Form (1NF) |
No repeating groups are permitted |
|
Second Normal Form (2NF) |
No partial key dependencies are permitted |
|
Third Normal Form (3NF) |
No nonkey dependencies are permitted. |
How would you change Figure 1.24 to add information about the sales representative that took the order?
Answer: As you see in Figure 1.30, you need to add another table that contains the sales representative's name, SALES_REP_ID, and any other important information. The SALESREP_ID then becomes a foreign key in the ORDER_HEADER table.
Figure
1.30 ORDER_HEADER with SALES_REP_ID column.How would you change Figure 1.25 if an employee does not need to belong to a department?
Answer: You change the relationship line on the DEPARTMENT table end to make it optional. This has the effect that the DEPARTMENT_ID column on the EMPLOYEE table can be null; that is, a value is not required (Figure 1.31).
Figure
1.31 EMPLOYEE to DEPARTMENT with optional relationship line.Based on Figure 1.25, why do you think the social security number (SSN) column should not be the primary key of the EMPLOYEE table?
Answer: The requirement for a primary key is that it is unique, not subject to updates, and not null.
)
Figure
1.30 )
Figure
1.31 ){kind=link}
){kind=link}
){kind=link}
Although the SSN is unique, there have been incidents (though rare) of individuals with the same SSN or individuals who had to change their SSN. It is conceivable to have an employee without a SSN assigned yet (e.g., a legal alien with a work permit), hence the column is null. There is a myriad of reasons for not using a SSN, therefore it's best to create a surrogate or artificial key.
1.2.3 Answers
Figures 1.26 and 1.27 depict the logical and physical model of a fictional movie rental database. What differences do you notice between the following entity relationship diagram and the physical schema diagram?
Answer: You can spot a number of differences between the logical model (entity relational diagram) and the physical model (schema diagram). While some logical and physical models are identical, these figures exhibit distinguishing differences you may find in the real world.
){kind=link}
){kind=link}
The entity name of the logical model is singular versus plural for the table name on the physical model. Some table names have special prefixes that denote the type of application the table belongs to. For example, if a table belongs to the purchase order system, it may be prefixed with PO_; if it belongs to the accounts payable system, the prefix is AP_; and so on. In the logical model, the spaces are allowed for table and column names. Typically, in Oracle implementations, table names are defined in uppercase and use the underscore (_) character to separate words.
Although the logical model may include the datatypes, here the datatype (such as DATE, VARCHAR2, NUMBER) shows on the physical model only. The physical model also indicates if a column allows NULL values.
The attribute and column names differ between the two models. For example, the RATING attribute changed to RATING_CD, which indicates the values are encoded such as for example "PG" rather than a descriptive "Parental Guidance" value. Designers create or follow established naming conventions and abbreviations for consistency. Naming conventions can help describe the type of values stored in the column.
The STOCK_QTY is another example of using the abbreviation QTY to express that the column holds a quantity of copies. Notice this column is absent from the logical model; it is a derived column. The quantity of movies for an individual movie title could be determined from the MOVIE_COPIES table. The database designer deliberately denormalized the table by adding this column. This simplifies any queries that determine how many copies of this particular title exist. Rather than issuing another query that counts the number of rows in the MOVIE_COPIES for the specific title, this column can be queried. Adding a derived column to a table requires that the value stay in sync with the data in the related table (MOVIE_COPIES in this case). The synchronization can be accomplished by writing a program that is executed from the end-user's screen. Alternatively, the developer could write a PL/SQL trigger on the table that automatically updates the STOCK_QTY value whenever a new row is added or deleted on the MOVIE_COPIES table for each individual title. (For an example of a table trigger, refer to Chapter 12, "Create, Alter, and Drop Tables.")
The schema diagram prominently exhibits columns that did not exist in the logical data model, namely CREATED_DATE, MODIFIED_DATE, CREATED_BY, and MODIFIED_BY. Collectively these columns are sometimes referred to as "audit columns." They keep information about when a row was created and last changed together with the respective user that executed this action.
On the logical data model the relationship is labeled in both directions. On the physical model, the name of the foreign key constraint between the tables is listed instead. You may find that some physical models depict no label at all. There are no set standards for how a physical or logical model must graphically look and therefore the diagrams produced by various software vendors that offer diagramming tools not only look different, they also allow a number of different display options.