The Power of ONE

1. The Power of ONE

2. Today, we will evaluate chapter 5 of this book and continue to teach you the essential database concepts, technology, and techniques that you will need to begin a career as a database developer.

3. 5.1 Explain how entities are transformed into relations. To transform an E-R data model into a relational database design, a relation is created for each entity. The attributes of the entity become the columns of the relation, and the identifier of the entity becomes the primary key of the relation.

4. 5.2 Why is it necessary to apply the normalization process to the relations created according to your answer in question 5.1? It is necessary to apply the normalization process to the relations created because the process is then applied to each relation, and additional relations are created. The attributes of the functional dependencies out of the relation, making a copy of their determinants in the original relation as foreign keys.

5. 5.3 What is denormalization? Denormalization- the process of intentionally designing a relation that is not normalized. Denormalization is done to improve performance or security. 5.4 When is Denormalization justified? Denormalization is justified when breaking groups into separate tables will make the designs hard to use.

6. 5.5 explain the problems that unnormalized relations 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 until a customer has that zip code. However, we will never want to do that. We only care about zip code data when one of our customers has that zip code. Therefore, leaving the zip data in CUSTOMER does not pose problems when inserting. Update: If a city changes its zip code, then we might have to change multiple rows in CUSTOMER. How frequently do cities change their zip code though? Because the answer is almost never, updates in the denormalized relation are not a problem. Delete: If only one customer has the zip data (80210, Denver, Colorado), then if we delete that customer, we will lose the fact that 80210 is in Denver. This does not really matter though because when another customer with this zip code is inserted, that customer also will provide the city and state.

7. 5.6 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 another table since they have an “ID” dependency.

8. 5.7 List the three types of binary relationships and give an example of each. The simplest form of binary relationship is a one-to-one (1:1) relationship, in which an entity of one type is related to at most one entity of another type. Ex: Employee to Cubicle Number. Each single employee is assigned to their own single cubicle The second type of binary relationship, known as one to many (1:N), is a relationship in which an entity of one type can be related to many entities of another type. Ex: Counselor to Student. One high school counselor is assigned to many students. The third and final type of binary relationship is many to many (M:N), in which an entity of one type corresponds to many entities of the second type, and an entity of the second type corresponds to many entities of the first type. Ex: A student too many classes and a class to many students. Many-to-many relationships cannot be represented directly by relations in the same way that one-to-one and one-to-many relationships are. The reason being, we cannot put multiple values in one cell, but it’s understandable that many students correspond too many classes.

9. 5.8 Define the term “foreign key” and give an example. • Foreign key: An attribute that is a key of one or more relations other than the one in which it appears. • Ex: When you place student ID into the enrollment table. The “student ID” in the enrollment table is the foreign key.

10. 5.9 Show a way to represent the 1:1 relationship in your answer to question 5.7. Use data structure diagrams. 5.10 For your answer to question 5.9, describe a method for obtaining data about one of the entities, given the key of the other.

11. 5.11Code the SQL statements to create a join having all data about both relations from your work for question 5.9 SELECT * FROM STUDENT,LOCKER WHERE SUDENT.StudentID = LOCKER.StudentID; 5.12. Why are some 1:1 relationships considered suspicious? Under what conditions should relations in a 1:1 relationship be combined into 1 relation? Some 1:1 relationships are considered suspicious because they have hash marks that indicate the relationship is mandatory in both directions. Conditions where they should be combined into one relation are when both entities have the same key.

12. 1.13 Define the terms parent and child and give a definition of each. The terms parent and child can be applied to the 1:N relationship where the parent is on the “one” and the child is on the “many” side. In the above diagram, professor would be the “parent”, and student would be the “child”.

13. Class Student Primary Key ClassID StudentID ClassName ClassID StudentName Location Foreign Key 1.14 Show how to represent the 1:N relationship in your answer to question 5.7. Use a data structure diagram.

14. 5.15 For your answer to question 5.14, describe a method for obtaining data for all of the children, given the key of parent. Describe a method for obtaining data for the parent given a key of the child. • Given a class ID, we can find all the students in that class. • Given the child key, we can find what class a student has. 5.16 . For your answer to question 5.14, code a SQL statement that creates a relation having all data from both tables. SELECT *FROM Class, StudentWHERE Class.ClassID = Student.ClassID;

15. 5.17 For a 1:N relationship, explain why you must place the key of the parent in the child, rather than placing the key of the child in the parent. In 1:N relationships, the parent relation is the “one” and the child relation is the “many”. Because attributes in a relation can have only a single value, each Child record has room for only one Parent record. The key in placed in the Child because the Parent is not able to contain multiple values for the many Child records.

16. 5.18 Give examples of binary 1:N relationships, other than those in this text, for an optional-to-optional relationship, an optional-to-mandatory relationship, a mandatory-to- optional relationship, and a mandatory-to-mandatory relationship Illustrate your answer using data structure diagram. • Class • ClassIDClassName Location • Student • StudentIDSchedule StudentName • Optional-to-optional because a class can be created before students actually enroll and students are not required to be enrolled in that class.

17. 5.18 cont’d • Class • ClassIDClassName Location • Student • StudentIDSchedule StudentName • Optional-to-mandatory relationship, ClassName is optional and StudentID is mandatory to distinguish between other students.

18. 5.18 cont’d • Class • ClassIDClassName Location • Student • StudentIDSchedule StudentName • Mandatory-to- optional relationship, a ClassID is mandatory to distinguish between other classes and StudentName is optional because that student is not required to be in that class.

19. 5.18 cont’d • Class • ClassIDClassName Location • Student • StudentIDSchedule StudentName • Mandatory-to-mandatory relationship, ClassID and StudentID are both mandatory to determine which student is in that class and what class that student is enrolled.

20. 5.19 Show how to represent the N:M relationship in your answer to question 5.7. Use a data structure diagram. • Student Classes • Student-Class • Student to Student-Class and Classes to Student-Class are in essence a 1:N relationship StudentIDStudentName ScheduleClassDesc StudentIDSchedule

21. 5.20 For your answer to question 5.19, describe a method for obtaining the children for one entity, given the key of the other. Also, describe a method for obtaining the children for the second entity, given the key of the first. • Given a StudentID, we can look up the appropriate row in Student-Class and the Schedule of that Student. • Since we obtained the Schedule, we can look up the ClassDesc (classes) that our Student has in his Schedule.

22. 2.21 For your answer to question 5.19, code a SQL statement that creates a relation having all data from all tables.SELECT *FROM CLASSES, STUDENT_CLASS, STUDENTWHERE CLASSES.Schedule = STUDENT_CLASS.ScheduleAND STUDENT_CLASS.StudentID = STUDENT.StudentID 5.22 Why is it not possible to represent a N:M relationship with the same strategy used to represent 1:N relationship. • Because in a 1:N relationship we are placing the foreign key from one table into the other. (Note: in a 1:N the foreign key always goes into the many-side of the relationship. • In a N:M relationship we are creating a new relation called an intersection relation, which has a composite key consisting of the keys from each of the tables that formed it.

23. Student StudentID ProfessorID StudentName 5.23 Explain the meaning if the term intersection relation.This is when a new relation is made from the keys from each of the tables that formed it. 5.24 Define 3 types of recursive binary relationships and give an example of each. • A recursive relationship is a relationship that a relation has with itself. • Recursive relationships adhere to the same rules as the binary relationships. • 1:1 and 1:M relationships are saved using foreign keys • M:N relationships are saved by creating an intersecting relation ProfessorID is a Foreign Key referencing the Primary Key StudentID

24. 5.26 Code a SQL statement that creates a table with all columns from the parent and child tables in your answer to question 25. SELECT *FROM StudentWHERE Student.ProfessorID = Student.StudentID; 5.27 Show how to represent the 1:N recursive relationship in your answer to question 24. How does this differ from the representation of 1:N non-recursive relationships? -A recursive relationship is a relationship that a relation has with itself. 5.28 Code a SQL statement that creates a table with all columns from the parent and child tables in your answer to question 27. SELECT * FROM Student WHERE Student.ProfessorID = Student.StudentID;

25. 5.29 Show how to represent the M:N recursive relationship in your answer to question 24. How does this differ from the representation of M:N non-recursive relationships?-A recursive relationship is a relationship that a relation has with itself.

26. This Concludes Our Presentation on Chapter 5 “Getting Started With Database Technology” Thank You for Choosing:

27. Team 1 Consultants: Jonathan Jackson Ronald Braun Kirby Mack Michael Runfola Travis Riley Ben Wessing Surapheal Belay Reza Ahmadi University of Houston co. 2005