This chapter is from the book
This chapter is from the book
This chapter is from the book
Lab 1.3 Exercise Answers
1.3.1 Answers
What does the Student schema diagram represent?
Answer: The Student schema diagram is a graphical representation of tables in a relational database.
A schema diagram is a useful tool during the software development lifecycle. English-like words should be used to name tables and columns so that anyone, whether developer or end-user, can look at a schema diagram and grasp the meaning of data, and the relationships among them, represented there. Developers study it to understand the design of a database, long before they put hands to keyboard to develop a system, and end-users can use it to understand how their data is stored.
Does the Student schema diagram tell you where a student lives? Explain.
Answer: No. The Student schema diagram tells you how data is organized in a relational database: the names of tables, the columns in those tables, and the relationship among them. It cannot tell you what actual data looks like. You use the SQL language to interact with a relational database to view, manipulate, and store the data in the tables.
What four columns are common to all tables in the Student schema diagram?
Answer: The four columns are created_by, created_date, modified_by, and modified_date.
Database tables are often created with columns similar to these four to create an audit trail. These columns are designed to identify who first created or last modified a row of a table and when the action occurred. You will typically find these columns only on the physical schema diagram, not on the logical model. Some of these values in the columns can be filled in automatically by writing triggers. You will see an example of a table trigger in Chapter 12, "Create, Alter, and Drop Tables." (Triggers are described in further detail in the Oracle PL/SQL Interactive Workbook by Benjamin Rosenzweig and Elena Silvestrova; Prentice Hall, 2003.)
What is the primary key of the course table?
Answer: The primary key of the course table is the column course_no.
You can identify the primary key with the "PK" symbol listed next to the column. In general a primary key uniquely identifies a row in a table, and the column or columns of the primary key are defined as NOT NULL.
How many primary keys does the enrollment table have? Name the column(s).
Answer: A table can have only one primary key. The primary key of the enrollment table consists of the two columns student_id and section_id.
As mentioned earlier, a primary key uniquely identifies a single row in a table. In the case of the enrollment table, two columns uniquely identify a row and create a composite primary key.
Looking at the schema diagram you also notice that these two columns are also foreign keys. The STUDENT_ID column is the foreign key to the STUDENT table and the SECTION_ID is the foreign key to the SECTION table. Both foreign key relationships are identifying relationships.
How many foreign keys does the section table have?
Answer: Two. The foreign keys of the section table are course_no and instructor_id.
Will a foreign key column in a table accept any data value? Explain using the STUDENT and ZIPCODE tables.
Answer: No. A foreign key must use the values of the primary key it references as its domain of values.
The ZIP column is the primary key in the ZIPCODE table. The STUDENT table references this column with the foreign key ZIP column. Only values that exist in the ZIP column of the ZIPCODE table can be valid values for the ZIP column of the STUDENT table. If you attempt to create a row or change an existing row in the STUDENT table with a zip code not found in the ZIPCODE table, the foreign key constraint on the STUDENT table will reject it.
In general, a foreign key is defined as being a column, or columns, in the child table. This column refers back to the primary key of another table, referred to as the parent table.
The primary key values are the domain of values for the foreign key column. A domain is a set of values that shows the possible values a column can have. The primary key values of the parent table are the only acceptable values that may appear in the foreign key column in the other table. (Domains are not only used in context with primary key and foreign key relationships, but can also be used for a list of values that may not be stored in a table. For example, common domains include Yes/No, Gender: Male/Female/Unknown, Weekday: Sun/Mon/Tue/Wed/Thu/Fri/Sat.)
If the relationship between the zipcode and student tables were optional, what would have to change in the student table?
Answer: The foreign key column zip in the student table would have to be defined as allowing NULL values. It is currently defined as NOT NULL. The relationship should be indicated as optional instead of mandatory as shown in Figure 1.34.
Figure
1.34 The relationships of the ZIPCODE table.
)
Figure
1.34 There is such an optional relationship between the INSTRUCTOR and ZIPCODE tables. All the nonnull values of ZIP in the INSTRUCTOR table must be found in the ZIPCODE table.
From what domain of values (what column in what table) do you think the prerequisite column of the course table gets its values?
Answer: From the course_no column in the course table.
In this case, the prerequisite column refers back to the course_no column, which provides the domain of values for the prerequisite column. A prerequisite is valid only if it is also a valid course number in the course table. This relationship is shown in Figure 1.35.
)
Figure
1.35 The self-referencing relationship of the COURSE table.
Explain the relationship(s) the enrollment table has to other table(s).
Answer: The student table and the section table are the parent tables of the enrollment table. The enrollment table is one of the parent tables of the grade table.
As shown in Figure 1.36, the relationship between the STUDENT and SECTION tables signifies a student may be enrolled in zero, one, or many sections. One individual student can be enrolled in one specific section only once, otherwise the unique combination of the two columns in the ENROLLMENT table would be violated. The combination of these two foreign key columns represents the primary key of the enrollment table.
Figure
1.36 The relationships of the ENROLLMENT table.
The relationship of the enrollment table as the parent of the grade table shows that for an individual student and her or his enrolled section there may be zero, one, or many grades. The primary key columns of the enrollment table (student_id and section_id) are foreign keys in the grade table that become part of the grade table's composite primary key. Therefore, only enrolled students may have rows in the grade as indicated with the optional line. If a row in GRADE exists, it must be for one specific enrollment in a section for one specific student.
)
Figure
1.36 Note: In some cases, the foreign keys become part of a table's primary key, as in the enrollment or the GRADE table. If a composite primary key contains many columns (perhaps more than four or five), a surrogate key may be considered for simplicity. The decision to use a surrogate key is based on the database designer's understanding of how data is typically accessed by the application programs.