Use the ERD in Figure 1 to answer these questions. How many 1-to-1 relationships
ID: 3767831 • Letter: U
Question
Use the ERD in Figure 1 to answer these questions.
How many 1-to-1 relationships are in the ERD?_____
How many 1-to-M relationships are in the ERD?_____
How many M-to-M relationships are in the ERD?_____
How many weak entities are in the ERD?_____
How many tables will you have when you convert the ERD to relational tables?______
Short Essay Questions (10 points, 5 pts each)
Use the ERD in Figure 1 to answer these questions. Limit your responses to 50 words.
Where do you place the foreign key to capture the “applied_to” relationship between Coupon and Sales Order?Explain why.
Describe one business policy (think assumption!) this business has that is captured in their ERD.
Multiple Choice Questions (30 points, 2 pts each)
Underline your answer. Each question only has one correct answer.
***Use the ERD in Figure 1 for Questions 1 through 9.
***Question 10 through 15, do NOT depend on Figure 1.
What is the primary key for the SO_Line_Item table? (Be sure and turn off spellchecking in Word so you can see the underlines in the choices below!)
SO_Line_Item_ID
SOrder_ID, Product_ID
SOrder_ID , Product_ID
SOrder_ID
Product_ID
The SO_Line_Item table does not have a primary key.
The owner of the business comes to you with the ERD and explains, “ I need to be able to track the number of each product that I sell, so I can track the best- and worst-selling products.”Then she asks, “To track this, which of that ERD entities do I need to place a ‘quantity’ attribute on?”
Coupon
Customer
Sales Order
SO_Line_Item
Products
PO_Line_Item
Purchase Order
Vendor
Later the business owner comes back to you and says, “Okay, next problem. I want to run a perpetual inventory system. I’m always re-ordering products from my vendors and most of my products are seasonal so the price fluctuates. For example, if I order a specific scarf in December the vendor may charge me $2.75 per scarf. However, if I order it in July, the vendor may charge me $5.25 per scarf. I need to be able to track the price I paid for a particular product on a given purchase order, so I can accurately calculate my cost of goods sold. So, to which of the ERD entities should I add the attribute ‘UnitCost?’”
Coupon
Customer
Sales Order
SO_Line_Item
Products
PO_Line_Item
Purchase Order
Vendor
What table would you look in to find the number of products on a specific sales order?
Sales Order
SO_Line_Item
Products
Either A or C
Any of above three tables will contain the needed information
None of them, this database is not designed to answer this question
Which of the following is NOT true about the primary key(s) in the PO_Line_Item relational table?
There are two primary keys
It is a composite primary key
The primary key includes POrder_ID and Product_ID
All of the above are true
What ERD entity constitutes the “border” between the revenue cycle information and the expenditure cycle information?
Coupon
Customer
Sales Order
SO_Line_Item
Products
PO_Line_Item
Purchase Order
Vendor
This ERD does not distinguish between revenue and expenditure cycle information.
What primary key from the ERD will functionally determine the list price of a product? List price is defined as the price we list the product for in the catalog.
SOrder_ID
SOrder_ID, Product_ID
Product_ID
POrder_ID, Product_ID
POrder_ID
You need to be able to identify the customer (i.e., Cust_ID) associated with every sales order.What primary key in the ERD will functionally determine the Cust_ID?
Cust_ID
SOrder_ID
Cust_ID, SOrder_ID
POrder_ID, Product_ID
POrder_ID
What’s the primary key for the Coupon entity?
SOrder_ID
SOrder_ID, Cust_ID
Cust_ID
Any of the above
None of the above
***NOTE: Questions 10 through 15 do NOT depend on the ERD in Figure 1***
The inability to remove data from a database for one type of entity without removing data for another type of entity is referred to as a(n)
update anomaly.
insert anomaly.
integrity anomaly.
deletion anomaly.
addition anomaly.
insertion dilemma.
None of the above.
ACME manufacturing wants to track post-sales customer service by collecting information about each customer service call: who called, when the call happened, which customer service representative handled the call, how long the call lasted, which sales transaction prompted the call, and which inventory items were discussed. To begin the service call, the customer service representative (CSR) always requests the sales invoice number that the customer is calling about.This allows the CSR to pull up the specific invoice in the system and associate comments and details of the post-sales service call with that invoice.If the customer calls in multiple times about the same sales invoice, the CSR associates a new set of comments to that sales invoice.The relationship between the Sales Invoice and Post-sales Service Call comments should be modeled as which of the following?
one-to-one
< >
many-to-many
< >
< >
D or E are both possible options
A relational database in which customer data is not maintained independently of sales invoice data (i.e., one table, instead of two tables, is used to store data) will be susceptible to which of the following problems:
an update anomaly.
an insert anomaly.
a deletion anomaly.
an integrity anomaly.
A and B
B and C
A and C
A, B, and C
A, B, C, and D
None of the above
Tom Khat is chair of the Purebred Cat (PC) Society, which maintains a database of registered purebred cats and their breeding history in Siamese, CA. He’s designing a database to track these child-parent relationships. One table will store the name, birth date, and other characteristics of all of the cats that have been registered with the PC society. He wanted to track weight, but he was told that would not be PC.Each cat in that table is uniquely identified by a registration number. A second table will contain data that link each cat to its male and female parents by means of their registration numbers. The primary key in the second table is:
< >
birth date
the combination of both parents’ primary keys in the first table
the same primary key in the first table that identifies the child cat
either C or D would be appropriate
none of the above
Scuz Bootes has been doing custom choppers, piercings, and tattoos for over thirty years. His home and place of business is a garage in the harbor district of Seattle, Washington. He has meticulous records of every job he has ever done, carefully handwritten with the customer name and address, a description of the job, and an attached picture of the bike or body part before and after customization. His unique style has recently attracted the attention of national media after several celebrities sought him out and showcased his work. Business is booming. Consequently, Scuz has hired you to construct an accounting information system, beginning with the historical records. As you read through the records, you notice that some customer last names have different first names in different records. For example, R. Framington Farnsworth (custom chopper), Bob Farnsworth (tattoo), and Snake Farnsworth (tattoos and piercings) all seem to be the same person. So you compile all the customers and their information in a single spreadsheet and take it to Scuz. Scuz helps you reconcile the different names, so the table only has one name per actual customer. But Scuz is a personable guy and he insists on identify his customers in the database by name. Your database knowledge tells you that such a design would:
violate the referential integrity rule.
suffer from the update anomaly problem.
suffer from the insert anomaly problem.
violate the entity integrity rule.
be okay.
B and D
Heidi Canal is a headhunter with Career Funnel in Boca Raton, Florida. Heidi is proud of the company's motto: We funnel workers into jobs. CF tracks information about Employers and Clients in two separate tables. They frequently place many clients with a single employer and occassionaly place a single client with many employers. This requirements them to track their placements in separate “Placement” table. In the Placement table, the client ID serves as
the primary key.
part of the primary key
a foreign key.
a non-key attribute
B and C
A, B, and C
None of the above
Multiple ANSWER Questions (15 points, 5 pts each)
Use the ERD in Figure 1 to answer these questions.
Underline your answers. These questions may have multiple answers. Underline them all for full credit.
If you convert this ERD to a set of relational tables, which table(s) will NOT have a foreign key in them?(Choose all that apply.)
Coupon
Customer
Sales Order
SO_Line_Item
Products
PO_Line_Item
Purchase Order
Vendor
Which of the following attributes could POrder_ID potentially functionally determine? (Select all that apply.)
QtyOnHand
OrderDate
OrderAmount
Address
ListPrice
ExtendedPrice
What is/are the foreign key(s) in the SO_Line_Item relational table?(Choose all that apply.)
SOrder_ID
Product_ID
SOrder_ID, Product_ID
SO_Line_Item_ID
None of these are a foreign key
SQL (50 points)
Use the above schema to create your SQL queries for the following questions. Remember that in Access:
The wildcard to match 0+ characters or numbers is *. To match 1 and only 1 character or number use _ (underscore).
Use [Order Details] (in square brackets) because it has a space.
UnitPrice in the Products table has a different meaning than UnitPrice in the [Order Details] table.
Dates need to be includes in #’s.
*************IMPORTANT: Paste your query as text and your result as a graphic.**************
(5 points) List the first and last names of employees hired after February 1, 1994.
QUERY:
RESULTS:
(10 points) List the company name and contact name of every customer that Anne Dodsworth (an employee) handled an order for before January 1, 1997.
QUERY:
RESULTS:
(15 points) How many orders has the oldest (in age) employee handled? HINT: You’ll need a nested query. Start by finding the oldest birthdate.
QUERY:
RESULTS:
(20 points) Your manager needs a list of order IDs that contain the most expensive beverage and the revenue the most expensive beverage brought in on each order (i.e., do NOT include other products on the order in your revenue total). He only wants orders in the list where the most expensive beverage generated more than $10,000.
HINT: ‘most expensive’ should tip you off that you need a nested query. Find the price of the most expensive beverage first, then translate it to a product ID. From there calculate the total revenue that the most expensive product generated on each order. For this problem, you’ll definitely want to be a groupby – you know, someone who is more interested in a relationship with SQL than the music it creates! ;-)
QUERY:
RESULTS:
(10 extra credit points) Modify your query in #4 to create a list of order IDs and the total revenue for the entire order (i.e., use all products on that order). All other conditions in #4 hold.
QUERY:
RESULTS:
Database Design: ERD and Relational Tables (40 points)
Children’s want lists are ever growing. Santa has finally decided he needs to move from a paper-based list to an electronic relational database. His elves tell him relational databases are the thing these days (which isn’t really true…they are kind of behind the times). So they worked up a description for the database. Here are the requirements:
Every child is allowed to write only one letter every Christmas. So a letter is associated with one and only one child. Unfortunately, mail gets lost or some children don’t believe in Santa Claus. So a letter is not received for every child in the database. When letters are received, the elves stamp them each with a unique ID. The elves need a “contents” field to store the text of the letter in.
Santa likes to analyze the different countries that he gets letters from.For instance, he always has a country dashboard where he displays the number of letters he received from each country.So the elves have a separate filing cabinet for each country where they store the letters. A letter can only go into a single filing cabinet, but a filing cabinet can hold many letters. Every filing cabinet entity needs to be able to track the country’s population.
Of course, the children’s letters contain their list of gifts they are requesting for Christmas. The elves need to track what gifts are to be delivered to each child.Surprisingly, some children never really request anything for themselves, but only request gifts for other children. Of course, there are plenty of children that request many gifts to be delivered to themselves.
For every child, the database will track his/her name and GPS coordinates.
The elves don’t start producing the gifts until they have compiled the requests from every child’s letter. This means they never make gifts that aren’t delivered to some child (i.e., they have no leftover gifts). However, they almost always make more than one of each type of gift. So one gift (i.e., type of gift) can go to many children. Every gift has a name, a description, and a weight.
In order to ensure that each child receives the correct gifts, the elves pack a “Christmas box” for every child that contains the gifts going to each child (wink, wink). Many Christmas boxes have more than one of the same type of gift, so the elves have to track the quantity of each gift that goes to each child (wink, wink). Papa elf lectured the junior elf for hours on functional dependencies.He said, “Remember! In order to track the number of a specific gift that goes to a specific child, we need to identify both…” Unfortunately, at that point the elves dozed off.
The elves also must track the total weight of each Christmas box so they know when they’ve reached the capacity of Santa’s sleigh.
ERD (20 points)
Create a complete ER diagram based on the above narrative.
Relational Tables (20 points)
Convert the ER diagram you created above into a set of relational tables. Use the following format for the relational tables:
TableA(PrimaryKeyA, Attribute1, Attribute2, ForeignKey)
TableB(PrimaryKeyB, Attribute1, Attribute2, Attribute3)
Explanation / Answer
i.2
ii.3
iii.0
iv.0
(a)Frogen key:
A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement
Yes forgen key can used in applied to it can refers both cupon and sales order table that means cupn table value must be in sales order table.
(b)Ans:
If the customer place an sales order to get cupon and contain products once it acept then place an purchage order to get goods this request taken to vendors along with customer details.
1.SOrder_ID
2.Purchase Order
3.PO_Line_Item
4.Either A or C
5.All of the above are true
6.This ERD does not distinguish between revenue and expenditure cycle information
7.Product_ID
8.Cust_ID, SOrder_ID
9.SOrder_ID, Cust_ID
10.deletion anomaly
11.D or E are both possible options
12.A, B, and C
13.the combination of both parents’ primary keys in the first table
14.B and D
15.a foreign key
16.Coupon,Products,Vendor
17.QtyOnHand
OrderDate
OrderAmount
Address
ListPrice
ExtendedPrice
18.SOrder_ID, Product_ID
SO_Line_Item_ID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.