INFO 605 900 DB Questions

Questions 1 - 7 will all deal with normalizing a table based on the following information.

You have been asked to review a database design for a small dog grooming business that has been having problems with data quality. Upon inspecting the database, you see a single table with information about grooming appointments:

Appointments(DogId, VisitNum, Name, Breed, DateOfBirth, OwnerNumber, GroomerId, GroomerName, GroomerNumber, Service1, Service2, Service3, ApptDate)

Discussion with the manager reveals the following functional dependencies:

• DogId, VisitNum -> any other attribute in the table, but perhaps transitively
• DogId -> Name
• DogId -> Breed
• DogId -> DateOfBirth
• DogId -> OwnerNumber
• GroomerId -> GroomerName
• GroomerId -> GroomerNumber

The manager tells you that the service attributes represent places to record services that were used in each appointment. There can be multiple services applied to each appointment, with no set maximum number of services, and some appointments only have one service. Also, it doesn't really matter whether a given service gets recorded in Service1, Service2, or Service3, as they all mean the same thing.

Question 1

What is the highest normal form satis!ed by the Appointments table as it is depicted above?

Question 2

Which of the following relations would be created by putting Appointments from Question 1 in First Normal Form?

• Groomers(GroomerId, GroomerName)
• Dogs(DogId, Name, Breed)
• AppointmentServices(DogId, VisitNum, Service)
• None of the other answers is correct.

Question 3

Which of the following relations would be created by putting Appointments in Second Normal Form?

• Dogs(DogId, Name, Breed, DateOfBirth, OwnerNumber)
• Groomers(GroomerId, GroomerName, GroomerNumber)
• None of the other choices is correct
• Appointments(DogId, VisitNum, GroomerName, GroomerNumber)

Question 4

Which of the following relations would be created by moving from Second Normal Form to Third Normal Form?

• Appointments(DogId, VisitNum, GroomerName, GroomerNumber)
• Dogs(DogId, Name, Breed, DateOfBirth, OwnerNumber)
• Groomers(GroomerId, GroomerName, GroomerNumber)
• None of the other choices is correct.

Question 5

After Appointments has been put into Third Normal Form, how many additional tables will have been created? (Do not count Appointments itself - count only the additional tables that were created by applying the !rst, second, and third normal forms to Appointments.)

Question 6

Putting Appointments into Second Normal Form would have mimized the potential for redundancy (duplication of the same facts) in which of the following attributes?

• Name
• VisitNum
• ApptDate
• GroomerName

Question 7

Putting Appointments into Third Normal Form would have minimized the potential for redundancy (duplication of the same facts) in which of the following attributes? (Assume for this question that we had already gotten Appointments into Second Normal Form, and are talking speci!cally about the di"erence between Second Normal Form and Third Normal Form.)

• GroomerName
• Name
• ApptDate
• VisitNum

Questions 8 - 15 will all deal with the database described by the following set of relation schemas and data dictionaries: see image.

Tornado: Records shark-infested tornados (waterspouts, actually, but "Sharkspout" really doesn't have much of a ring to it, now does it?)

Shark: Sharks picked up by tornados

Victim: Some sad bugger who got eaten by a tornado shark

[Note: If the above scenario is not instantly familiar to you, then you need to watch this: https://www.youtube.com/ watch?v=wBgLpZEMT1s ]

Question 8

Which of the following attributes from this database would be the worst !t for a CHECK constraint?

• Species
• LastName
• ToothCount
• EFScaleRating

Question 9

The DDL for which table(s) would *NOT* contain the keyword REFERENCES?

• Tornado
• Shark
• Both Shark and Victim.
• Victim

Question 10

You have already created the Tornado table according to the speci!cation above, and are about to execute the following SQL to create the Shark table:

1 CREATE TABLE Shark (
2 TagId CHAR(12) CONSTRAINT shark_pk PRIMARY KEY,
3 Species VARCHAR2(100) NOT NULL,
4 ToothCount NUMBER(4) CONSTRAINT shark_tooth_ck CHECK (ToothCount BETWEEN 1 AND 2000),
5 Tornado CHAR(8) CONSTRAINT shark_tornado_fk REFERENCES Tornado.TornadoId
6 )

Which of the above lines will cause Oracle to respond with an error message when this statement is executed? (Note that you should indicate the line which is the source of the error - this would not necessarily be the line number mentioned in any error messages.)

Question 11

The SQL statement from problem 10 also shows a bad practice that would not cause an error message but should be avoided anyway. What is this practice?

Question 12

Which of the following statements would de!nitely fail with an error message? (Just because a statement doesn't change any values doesnt mean that it fails - this question is asking you to identify cases in which Oracle would report an error and show you an error number along with an error description.)

• UPDATE Tornado SET EFScaleRating = 4 WHERE EFScaleRating = 5;
• SELECT * FROM Shark WHERE ToothCount > 0;
• DELETE FROM Victim WHERE LastName = 'Ziering';
• INSERT INTO Shark VALUES ('ABCD1234WXYZ','Tiger','TORNADO1');

Question 13

You need to write a SQL statement that shows the last names of any victims who have been killed by the sharks from tornados with an EFScaleRating of 4 or greater. Which of the following columns does not need to be mentioned in your SQL statement in order to meet the requirements?

• Duration
• LastName
• Tornado
• TagId

Question 14

You want to write a report that shows only the species of shark which have more than 1000 teeth recorded in the database, along with the number of teeth recorded for those species. You start with the following statement:

1 SELECT species, SUM(toothcount)
2 FROM Shark
3 GROUP BY species
4 HAVING toothcount > 1000;

Which line will you have to edit in order to meet the speci!ed requirements?

• 1
• 4
• None of the lines need to be edited.
• 2

Question 15

You want to create a report that shows the tag id and tornado id of the sharks that have more teeth than the average shark in their respective tornados. Which of the following queries will achieve your goal?

• None of these queries will achieve the stated goal.
• SELECT s.TagId, s.Tornado FROM Shark s WHERE ToothCount > (SELECT AVG(ToothCount) FROM Shark);
• SELECT s.TagId, s.Tornado FROM Shark s, (SELECT Species, AVG(ToothCount) as AvgCount FROM Shark GROUP BY Species) AvCnt WHERE s.Species = AvCnt.Species AND s.ToothCount > AvCnt.AvgCount;
• SELECT s.TagId, s.Tornado FROM Shark s, (SELECT Tornado, AVG(ToothCount) as AvgCount FROM Shark GROUP BY Tornado) AvCnt WHERE s.Tornado = AvCnt.Tornado AND s.ToothCount > AvCnt.AvgCount;

Question 16

Consider the following relational schemas from a rental car database: see image.

Now consider the complete contents of the Location table:

Based on the information given, which of the following values de!nitely could not appear in the HomeLocation column of the Car table?

• NULL (the absence of a value)
• 103
• 105
• 101

Question 17

Assume that we need to store the value 12345.67 in a numeric data type. Which of the following types would be just large enough allow us to capture such a number? That is, if the type got any smaller, it would no longer be able to capture the number, but it is able to capture the number as stated below.

• NUMBER(7)
• NUMBER(7, 2)
• NUMBER(5, 2)
• NUMBER(9, 2)

Question 18

Consider the following ER diagram: see image.

Which of the following choices best describes the requirements portrayed by this diagram?

• Every bene!ciary receives many payments, but each payment has only one bene!ciary.
• Every payment has a single beneficiary, whereas a beneficiary might receive a single payment, multiple payments, or no payment at all.
• A payment can have multiple beneficiaries, and a beneficiary must receive multiple payments.
• There must be at least one beneficiary for each payment, and there must be at least one payment for each beneficiary.

Question 19

Sandeep has a Products table in his database. Each row in the table represents one of the 19,000 products his company o"ers. The Products table contains a column called Unit_Price. The maximum value the column can currently hold is 9999.99. However, Sandeep was just informed that his company will be o"ering a new product (a Kardashian-themed set of bowling pins) that will sell for roughly thirteen thousand dollars. Which of the following kinds of SQL statements should Sandeep use to make sure that his Products table can accommodate the new product?

• ALTER
• SELECT
• INCREASE
• DROP