Get a unique, high-quality and non-plagiarized paper from us today at the most affordable price
Email us : premieredtutorials@gmail.com

Unnormalized tables

 

  1. Explain how entities are transformed into tables.
  2. Tables are designed for each entity and given the name of that entity
  3. Make the primary key of the relation the identifier of the entity
  4. Create a column in the relation for each attribute in the entity.
  5. Apply the normalization process.

 

  1. 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.

 

  1. 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.

 

 

  1. 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.

  1. When is denormalization justified?

Denormalization is justified when usability or performance considerations indicate that a fully normalized set of tables is not optimum.

 

 

  1. 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.

 

  1. 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.

 

 

 

 

 

 

  1. 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.

 

  1. 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

 

 

 

  1. Define the term foreign key and give an example.

 

 

 

  1. 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.

 

 

 

  • 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.

  • 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

 

  • 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.

 

  • 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;

 

  • 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.

 

  1. 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.

 

  1. 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.

 

 

  1. 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

 

 

  1. 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.

 

 

 

 

  1. 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.

 

  1. 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.

 

 

 

  1. 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.

 

  1. 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;

 

 

 

  1. 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.

 

  1. 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;

 

 

 

  1. 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.

 

  1. 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;

 

 

 

  • 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.

 

 

Looking for the best essay writer? Click below to have a customized paper written as per your requirements.

 

 

 

How to Place an Order 

Send the assignment details such as the instructions, due date/deadline, number of pages and college level to the customer support agent online on live chat,  fill in the assignment details at place an order or send the information to our email address premieredtutorials@gmail.com and a customer support agent will respond to you immediately. 

Once you place your order, we choose for you the best and competent writer for your assignment based on each writer’s competence in handling a subject. 

When the homework is completed, we have a quality assurance team that proofreads the assignment to ensure it meets the required rubric instructions from your professor.

After thorough review of your assignment, we send the paper to the client. In case you need any changes at this point, you can let us know so that we can handle it for you at no extra charge. 

Homework Help Website

Why we should write your Paper 

  1. Money Return guarantee
  2. 0% Plagiarism Rate
  3. Guaranteed Privacy
  4. Written from scratch by highly qualified writers 
  5. Communication at Any Time (24/7)
  6. Flexible Pricing and Great Discount Programs
  7. Timely Deliveries
  8. Free Amendments
Looking for a similar assignment and in urgent need for help? Place your order and have excellent work written by our team of professionals to ensure you acquire the best grades.

  We are here to assist you.

 

Statistics about Us

130 New Projects
235 Projects in Progress
315 Inquiries
420 Repeat clients

© 2021 Premiered Tutorials
All rights reserved. We provide online custom written papers, such as term papers, research papers, thesis papers, essays, dissertations and other custom writing services.

All papers inclusive of research material are strictly intended to be used for research and study purposes only. Premiered Tutorials does not support or condone plagiarism in any form. These custom papers should be used with proper reference.

Place an Order
error: Content is protected !!