1. Explain how entities are transformed into tables.
a. Tables are designed for each entity and given the name of that entity
b. Make the primary key of the relation the identifier of the entity
c. Create a column in the relation for each attribute in the entity.
d. Apply the normalization process.
2. Explain how attributes are transformed into columns. What column properties do you take into account when making the transformations?
The attributes become the columns of the relation and the identifier of the entity becomes the primary key of the relation.
3. Why is it necessary to apply the normalization process to the tables created according to your answer to question 5.1? Because the process is then applied to each relation and additional relations are created.
4. What is denormalization?
Denormalization is not normalizing a database to a fully normalized status. One or more tables may be left in a state that we know is not fully normalized.
5. When is denormalization justified?
Denormalization is justified when usability or performance considerations indicate that a fully normalized set of tables is not optimum.
6. Explain the problems that unnormalized tables have for insert, update, and delete actions
. Insert: If we leave ZIP, City, and State in CUSTOMER, then we will not be able to insert data for a new ZIP code. We only care about the ZIP code data when one of the customers has that ZIP code. Therefore, leaving the ZIP data in CUSTOMER does not pose any issues when inserting. Update: If a city changes its ZIP code, then we might have to change multiple rows in CUSTOMER. Because cities hardly ever change their ZIP codes, updates in the denormalized relation are not a problem. Delete : If only one person has a certain ZIP code and delete that person from CUSTOMER, we will not only delete the ZIP, but also the city. This doesn't matter, because when another customer has ZIP code inserted, then the data for the city and state is provided.
7. Explain how the representation of weak entities differs from the representation of strong entities.
Weak entities logically depend on another entity. Strong entities are not dependent on other tables since they have ID dependency.
8. Explain how supertype and subtype entities are transformed into tables.
The identifier of the supertype entity becomes the primary key of the supertype table andthe identifiers of the subtype entities become the primary keys of the subtype table.
8. List the three types of binary relationships and give an example of each. Do not use the examples in the text.
1:1 Relationship: Employee to cubicle number. Each employee gets assigned a cubicle. 1:N Relationship: Counselor to Student. A school counselor isassigned many students
10. Define the term foreign key and give an example.
10. Example: Inthe SALESPERSON table, SalespersonNumberis the foreign key, whereas in the tableSALES_ORDERSalespersonNumber is a foreign key referencing Salesperson Numberin SALESPERSON.
11.Show two different ways to represent the 1:1 relationship in your answer to questions 5.9. Use IE Crow's Foot E-R diagrams.
12.For your answers to question 5.11, describe a method for obtaining data about one of the entities, given the key of the other. Describe a method for obtaining data about the second entity, given the key of the first. Describe methods for both of your alternatives in question 5.11.
Given the Cubicle Number; we can locate the Employee Name.
Given the Employee Name; we can locate the Cubicle in which they are assigned.
13.Code SQL statements to create a join that has all data about both tables for your
work for question 5.11.
SELECT
FROM
CUBICLE, EMPLOYEE
WHERECUBICLE.CubicleNo = EMPLOYEE.EmployeeNameERSON.
14 Define the terms parent and child as they apply to tables in a database design and give an example of each.
A parent is a row on the one side of a 1:N relationship, while a child is a row on the many side of a 1:N relationship. For example, if DEPARTMENT and EMPLOYEE have a 1:N relationship, a row of DEPARTMENT is a parent and the rows of EMPLOYEE that pertain to that department are the children.
15 Show how to represent the 1:N relationship in your answer to question 5.9. Use an IE Crow's Foot E-R diagram.
BOAT to RENTAL_FEE is 1:N.
Assume relations:
BOAT (LicenseNumber, Type, Length)
RENTAL_FEE (ChargeNumber, ChargeDate, ChargeAmount).
Place LicenseNumber in RENTAL-CHARGE as a foreign key.
BOAT (LicenseNumber, Type, Length)
RENTAL_FEE (ChargeNumber, ChargeDate, ChargeAmount, LicenseNumber)
Where
LicenseNumber in RENTAL_FEE must exist in LicenseNumber in BOAT
16 For your answer to question 5.15, describe a method for obtaining data for all the children, given the key of the parent. Describe a method for obtaining data for the parent, given a key of the child.
Given a value of LicenseNumber in BOAT [Parent], look up all rows in RENTAL_FEE [Child] having that value for LicenseNumber.
Given a ChargeNumber, look up the row in RENTAL_FEE [Child] having that number, and then use that value to look up BOAT [Parent] data to obtain the value of the LicenseNumber for the parent.
17 For your answer to question 5.15, code a SQL statement that creates a table that has all data from both tables.
SELECT
FROM BOAT, RENTAL_FEE
WHERE BOAT.LicenseNumber = RENTAL_FEE.LicenseNumber;
18 For a 1:N relationship, explain why you must place the key of the parent table in the child table, rather than place the key of the child table in the parent table.
There is literally not enough room in the parent, since we can only have one value per cell in the relational model.
21. Explain the meaning of the termintersection table
The term intersection table is when a new relation is made from the keys for each of the
tables that formed it.
22.Explain how the terms parent table and child tablerelate to the tables in your answerto question 5.20.
The student table is the parent table and the classes table is the child table. Given theStudentID we can get the student's schedule and the class descriptions.
23. For you answer to question 5.20, 5.21, and 5.22, describe a method for obtaining thechildren for one of the entities in the original data model, given the primary key ofthe table basedon the second entity. Also, describe a method for obtaining thechildren for the second entity, given the primary key of the table based on the firstentity.
Given the StudentID, we can look up the appropriate row in Student-Class and theSchedule of that Student.Since we obtained the schedule, we can look up the ClassDesc that our student has in hisschedule.
24. For your answer to question 5.20, code an SQL statement that creates a relation thathas all data from all tables.
SELECT
FROMCLASSES, STUDENT_CLASS, STUDENT
WHERECLASS.Schedule = STUDENT_CLASS.Schedule
ANDSTUDENT_CLASS.StudentID = STUDENT.StudentID
25. Why is it not possible to represent N:M relationships with the same strategy used torepresent 1:N relationships?
Because in a 1:N relationship we place the foreign key from one table into the other andin the N:M relationship we create a new relation called an intersection relation.
25. Why is it not possible to represent N:M relationships with the same strategy used to represent 1:N relationships?
Because there is only room for one value per cell in a relation, there is literally not enough room. Thus neither of the two relations in relationship can hold foreign key values referencing the other relation.
26. What is an associative entity? What is an association relationship? Give an example of an association relationship other than one shown in this text. Illustrate your answer using an IE Crow's Foot E-R diagram.
An associative entity is the equivalent of an intersection table with additional columns beyond the composite primary key. Although an "intersection entity" is not included in a data model, an associative entity can be, and indeed must be. The pattern of two (or more) strong entities related to each other via an associative entity is called an association relationship. We can turn the N:M BOAT and OWNER example into an association relationship by simply adding a non-key column to BOAT_OWNER. For example, we can add a column named PercentOfOwnership.
30. Show how to represent the 1:1 recursive relationship in your answer to question5.29. How does this differ from the representation of 1:1 nonrecursive relationships?
Student has a relation with StudentID. A recursive relationship is a relationship that arelation has with itself.
31. Code an SQL statement that creates a table with all columns from the parent andchild tables in your answer to question 5.30.
SELECT
FROMStudent
WHEREStudent.ProfessorID = Student.StudentID;
32. Show how to represent a 1:N recursive relationship in your answer to question 5.29.How does this differ from the representation of 1:Nnonrecursive relationships?
Student can be in relation to any StudentID,ProfessorID, or StudentName. A recursiverelationship is a relationship that a relation has with itself.
33. Code an SQL statement that creates a table with all columns from the parent and child tables in your answer to question 5.32.
SELECT
FROMStudent
WHEREStudent.ProfessorID = Student.StudentID;
34. Show how to represent the M:N recursive relationship in your answer to question5.29. How does this differ from the representation of M:Nnonrecursiverelationships?
Student can be in relation to StudentId, ProfessorID, and StudentName. A recursiverelationship is a relationship that a relation has with itself.
35. Code an SQL statement that creates a table with all columns from the parent andchild tables in your answer to question 5.34. Code an SQL statement using a leftouter join that creates a table with all columns from the parent and child tables.Explain the difference between these two SQL statements.
SELECT*
FROM
Student
WHERE Student.ProfessorID = Student.StudentID;
SELECT
FROMStudent
WHEREStudent.ProfessorID = Student.StudentID
ANDStudent.StudentID = StudentName;
36. Consider the following relation that holds data about employee assignments: ASSIGNMENT (EmployeeNumber, ProjectNumber, ProjectName, ?HoursWorked) ?Assume that ProjectNumber determines ProjectName and explain why this rela- tion is not normalized. Demonstrate an insertion anomaly, a modification anomaly, and a deletion anomaly. Apply the normalization process to this relation. State the referential integrity constraint.
Without the join you will only get the StudentID as opposed to the join you will get the
StudentID as well as the StudentName.
The Assgin relations is not normalized because there is a determinant , Project number , which is not a candidate key.
Insertion anomaly: we cannot record the correspondence between a project number and project name until we have at least one assignment for that project.
Update anomaly: If the project changes it's name there are potentially many rows that will have to be updated.
Deletion anomaly: deleting the last assignment for a project will lose the correspondence between a project number and project name.