Information Systems homework help

1. Explain how entities are transformed into tables.

a. Tables are designed for each entity and given the name of that entity

Don't use plagiarized sources. Get Your Custom Essay on
Information Systems homework help
From $10/Page
Order Essay

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 and the 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 is assigned many students

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

10. Example: In the SALESPERSON table, SalespersonNumber is the foreign key, where as in the table SALES_ORDER SalespersonNumber is a foreign key referencing Salesperson Number in 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

WHERE CUBICLE.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 term intersection 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 table relate to the tables in your answer to question 5.20.

The student table is the parent table and the classes table is the child table. Given the StudentID 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 the children for one of the entities in the original data model, given the primary key of the table based on the second entity. Also, describe a method for obtaining the children for the second entity, given the primary key of the table based on the first entity.

Given the 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 that our student has in his schedule.

24. For your answer to question 5.20, code an SQL statement that creates a relation that has all data from all tables.

SELECT

FROM CLASSES, STUDENT_CLASS, STUDENT

WHERE CLASS.Schedule = STUDENT_CLASS.Schedule

AND STUDENT_CLASS.StudentID = STUDENT.StudentID

25. Why is it not possible to represent N:M relationships with the same strategy used to represent 1:N relationships?

Because in a 1:N relationship we place the foreign key from one table into the other and in 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 question 5.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 a relation has with itself.

31. Code an SQL statement that creates a table with all columns from the parent and child tables in your answer to question 5.30.

SELECT

FROM Student

WHERE Student.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:N nonrecursive relationships?

Student can be in relation to any StudentID, ProfessorID, or StudentName. A recursive relationship 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

FROM Student

WHERE Student.ProfessorID = Student.StudentID;

34. Show how to represent the M:N recursive relationship in your answer to question 5.29. How does this differ from the representation of M:N nonrecursive relationships?

Student can be in relation to StudentId, ProfessorID, and StudentName. A recursive relationship is a relationship that a relation has with itself.

35. Code an SQL statement that creates a table with all columns from the parent and child tables in your answer to question 5.34. Code an SQL statement using a left outer 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

FROM Student

WHERE Student.ProfessorID = Student.StudentID

AND Student.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.

To continue with the answer check on mycoursewriter.com/

MyCourseWriter
Order NOW For A 35% Discount!
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Homework Writing

Do you need to ace your test? We have qualified writers to assist you in writing 100% quality homework writing. Mycoursewriter offers students unique and personalized custom papers that help them in getting good grades. Our academic experts will assist you with your dissertation papers, thesis writing, research papers, term papers, and case study homework help. Our writers are experienced and have perfected the art of crafting the best essay writing papers regardless of your academic level. Order now and get our superb homework writing services.

Expert Academic Writers

A student needs professional assistance outside class time. Before hiring our writers, they undergo thorough tests and strict educational backgrounds. After passing a test, our writers undergo training to ensure they deliver expert professional papers to our customers. We ensure they meet the timely delivery of papers and follow our customers' provided instructions. Every writer is an ENL writer and is from the USA, Canada, UK, or Australia. Our experts approach papers effectively depending on their area of specialization.

Affordable Price Guarantees

Is the deal too good to be true? Mycoursewriter is the most affordable assignment help website on the market. We consider students' daily needs for academic help. Our paper costs are reasonably priced to approximately 90% of students around the globe. Our main aim is to ensure students achieve academic excellence, and we believe inconsiderable prices should not hinder students from getting quality homework writing help. In case of any dissatisfaction, we offer unlimited revisions and rewrites without extra charges.

Timely delivery of papers

Tight deadline? Is your paper due in the next few hours? Worry no more. Our writers are here for your rescue. Many college students find it hard to balance their social and academic life and get caught up with academic deadlines. Drafting your paper at the last minute does not allow you to conduct extensive research and craft the correct answers. We have expert writers ready to work on your assignment writing. Urgent writing services are the ultimate solutions you need. We guarantee you timely delivery of papers at your convenience and specified time delivery of the customer.

100% Original Tasks

The ultimate deal-breaker in achieving good grades is submitting imitative papers. Plagiarism is a breach of examination standards and has serious consequences. Students need to deliver papers written in their understanding by paraphrasing their research findings and relevant citations. Our expert's primary consideration is providing free plagiarized papers that meet the lecturers' requirements. Our editors will proofread your paper before delivery ad ensure your papers will not be substandard. We guarantee extreme originality, and in case of any customer concerns, we offer a plagiarism report.

All Round the clock Customer Support

Our website is user-friendly and super useful! Mycoursewriter is the most trusted online writing website always available to provide you with assignment writing services that you need. Our primary concern is meeting customer need specifications by our friendly customer support team that Is available all the time. Our website usability is self-learnable but in case of any problem, feel free to contact us directly through our website email: support@mycourserwiter.com. We have diligently satisfied thousands of students, and you can witness this on our sites' review page.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Advantages of Using Our Assignment writing Services

Mycoursewriter welcomes you for superb essay writing services. We are a legit site offering help with your most challenging homework writing assignments at affordable prices. Your essay paper will be assigned to a professional writer who will craft the paper from scratch according to your provided instructions.

Essays

Essay Writing and Proofreading Services

Essay writing is a common assignment writing for most college students. It takes a lot of time to craft a complete perfect essay. Mycourserwiter offers the best essay writing and proofreading services for most college students. Your paper is assigned to an expert writer who does thorough research on fresh ideas and personalizes your essay paper according to your assignment writing guidelines. Our essays are entirely exceptional and undergo a strict procedure that includes papers free from plagiarism and thorough proofreading.

Admissions

Admission and Business Papers

Writing an admission paper is not every student's desire, and this should not hinder any student from earning an admission. Writing a perfect personal statement to earn a position in a dream school or job can be stressful. Personal statements should display a high level of confidence and uniqueness. Admission essays require you to have a vast knowledge of the current happenings in the world and knowhow of the business world. We have qualified writers with skills who will write your admission paper through comprehensive research. All you have to do is provide us with little details about yourself, instructions and our writers will craft a perfect paper for you.

Editing

Editing and Proofreading

Mycoursewriter offers you assignment writing services free from grammatical and formatting mistakes. An error-free paper is significant in scoring the desired maximum grades. We have a team of editors who will go through your paper after our expert writers have delivered their fully crafted papers. Our writers will cover all the aspects of your paper instructions, such as fonts, spacing, and educational formats. Our team of editors will check through your paper to make sure it meets the formatting style provided in the paper instructions. Under no circumstances will a poorly formatted paper will be forwarded to you as our team of editors will ensure careful proofreading.

Coursework

Technical Papers

Assignment writing is part of a learning process, and one may not have the knowledge to answer all questions correctly. This should not be a stress to you as we have qualified essay writing experts to help you. Our qualified writers are holders of PhDs, Degrees, and Masters in different areas of study and can handle any papers regardless of the complexity. Multiple assignments may be challenging to finish on time. Our skilled professional writers are here to assist you in crafting all the correct answers for your assignment. Stop stressing about your homework writing and place an order with us, and our expert writers will provide you with the best assignment help.