Class Diagrams and Normalized Schemas

MIS3500 Midterm Exam Preparations

2015

Exercises on

class diagrams and normalized schemas

The exam will have:

- Long answers (2); class diagram, schema

- Short answers (7) which can be

(a) textual

(b) query reading

- Multiple choice & true/false (30)

Note: These questions are identical to those practiced in class, and given for homework.

1. A dentist who runs a small office employing himself, three hygienists, and a receptionist

wants a database to schedule appointments. He has a commercial billingsystem to handle payments and insurance, but appointments are currently writtenon a paper form similar to the sample shown here. The form currently tracks onlythe patient name and the primary procedure to be performed (cleaning, X-ray, filling,and so on). He wants to add a little more detail and list all of the procedures

planned, the estimated fee for each procedure, and the estimated insurance coverage.The base fees should be stored with the procedures, but the insurance amountsare highly variable and will be entered by the receptionist who can get estimatesfrom the billing system. The system also has to track the patient’s phone numberand e-mail address and record how he or she wants to be notified. Eventually, itcould send e-mail messages to patients as reminders. Create a schema forthis system.

Above is the schema.

Below is a class diagram of the Dentistry Database abbreviated. Do NOT use this form on the exambut fully populate the diagram with attributes!

A question may also ask to state business rules on multiplicity. The statements must match the multiplicity numbers shown in your diagram. Makes sure you use a proper form of multiplicity statement and so consistently for each side of a relationship.

- Business rules on multiplicity, example:

Each appointment can contain many procedures, and each procedure can be used in many appointments.

Note: Alternative modeling details are as follows.

- In table FootPattern, the attribute Foot (which has attributes Left and Right) need not to be part of the key because PatternDate suffices to make this table “many”. The attribute PatternObject can be deleted

- In table OrderItem, the attribute ItemSequence appears opaque and can be renamed into ItemID. This would be a unique key identifying a pair of shoes.

Class Diagram of the Shoe Manufacturing Company’s Database System (abbreviated; do NOT use this form on the exam)

Questions on reading queries

(The Schema of PetStore system will be provided.)

What will these queries output?

SELECT Merchandise.ItemID, Merchandise.Description, Merchandise.ListPrice

FROM Merchandise

WHERE (((Merchandise.ListPrice)>(Select Avg(ListPrice) FROM Merchandise)))

ORDER BY Merchandise.ListPrice DESC;

Answer:The query will outputitem IDs, decriptions and list price for the merchandise items with a list price greater than the average list price of all merchandise items (or, than the average item price).(4 pieces)

SELECT Animal.Gender, Avg([SaleDate]-[OrderDate])

FROM Sale INNER JOIN (AnimalOrder INNER JOIN

( (Animal INNER JOIN AnimalOrderItem ON

Animal.AnimalID=AnimalOrderItem.AnimalID) INNER JOIN

SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON

AnimalOrder.OrderID = AnimalOrderItem.OrderID) ON

Sale.SaleID = SaleAnimal.SaleID

WHERE Animal.Category="Cat"

GROUP BY Animal.Gender;

Answer:The query will outputthe gender andthe average time between ordering and sellingcats. (3 pieces)

Or, what is the the average time between ordering and selling male and female cats?

SELECT Sale.CustomerID, Customer.LastName, Customer.FirstName, Sum([SalePrice]*[Quantity]) AS [Monthly Total]

FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID)

INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID

WHERE Month(Sale.SaleDate)=5

GROUP BY Sale.CustomerID, Customer.LastName, Customer.FirstName

ORDER BY Sum([SalePrice]*[Quantity]) Desc;

Answer:The query will outputcustomers’ IDs, last names, first names, and their total expenditures in May and sort the output by the total expenditure in decreasing order. (6 pieces)

SELECT DISTINCT Employee.EmployeeID, Employee.LastName,

Employee.FirstName

FROM Employee INNER JOIN Sale ON Employee.EmployeeID = Sale.EmployeeID

WHERE (((Sale.SaleDate) Between #6/15/2007# And #6/30/2007#));

Answer:The query will outputIDs, last names and first names for those employees that sold somethingbetween June 15-30, 2007 (or in the last two weeks of June 2007). (5 pieces)

SELECT Animal.AnimalID, Animal.Name, Animal.Category, [DateOrder]-[OrderBorn]

AS Days, AnimalOrder.OrderDate

FROM AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem

ON Animal.AnimalID = AnimalOrderItem.AnimalID)

ON AnimalOrder.OrderID = AnimalOrderItem.OrderID

WHERE ((([OrderDate]-[DateBorn]) Between 0 And 30));

Answer:The query will outputIDs, names, category and the period between the birth and order date(or the age on the order date) for those animals that were ordered up to 30 days after thier birth (or that were less than a month old when ordered). (5 pieces)

SELECT DISTINCT Employee.LastName

FROM Employee INNER JOIN (Sale INNER JOIN SaleAnimal

ON Sale.SaleID = SaleAnimal.SaleID) ON Employee.EmployeeID = Sale.EmployeeID

WHERE (((Sale.SaleDate) Between #2/1/2007# And #2/28/2007#))

AND Employee.EmployeeID NOT IN

(SELECT Sale.EmployeeID

FROM Sale INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID

WHERE (((Sale.SaleDate) Between #2/1/2007# And #2/28/2007#)));

Answer:The query will outputlast names of the employeesthat sold animals but no merchandise in February 2007? (4 pieces)

NOTE: Marking is based on both the number of pieces and their difficulty (e.g., items in the SELECT line are less difficult to identify than the items in the WHERE line).

Questions Asking for Short Answer

For thyse quesitons as well as multuple chouice and true/false, an optimal study streatgy is as follows:

1. Focus on the concepts list on the course website.

2. Define each concept (in your own words, not necessarily memorizing verbatim the definitions from the study materials)

3. Relate a concept to similar ones as appropriate and identify similarities and differences between these

4. Learn some additional detail about each concept (this could be an example, a drawing, a characteristic, a purpose, etc.).

Examples

1 Define class, provide an additional detail about it, and identify two differences between class and object. (2 marks)

Answer:

Definition: Class is a representation of an aspect of reality (or any of these - person, thing, event, concept) that groups together relevant attributes of that aspect. (Optional definitions are possible.) (0.5)

One detail: And example is class Student representing students at a university through the characteristics of student identification, name, address, GPA, etc. (An option: draw class student and an object student.) (0.5)

Differences:

Class is a whole, while object is a part of class.

Object is an instance of class.

Class consists of metadata (or it is a skeleton), while objects contain (are made of) data.

Associations are defined at the class level, and objects must comply with the definition.

Objects have the property of multiplicity, while classes do not.

Classes are implemented as a set of columns, while objects are implemented as rows.

(0.5 each)

2. A question may ask about defining a normal form (NF) and providing an example of it. Do not miss to specify in your definition that a higher NF accounts for a lower NF (e.g., a table is in 2NF if it is in 1NF and …).

3. A question may give a partially populated B+-tree (or some other type of index) and then ask which values should be placed in blank nodes.

Multiple Choice & True/False Questions

In addition to standard questions about definitions and details of concepts, there may questions that demand some analysis leading to picking a choice. These questions may also be about estimating if a table is in a particular NF, or whether particular queried values will find a match in records that contain Boolean operators.

Example:

Is this table is 3NF – Yes/No?

Salary(RecordNumber, PayDate, EmployeeID, JobID, JobDescription, Amount, Deductions)

Answer: No.

Attribute JobDescription depends on JobID, a non-key attribute, which is an instance of transitive dependence and a violation of 3NF (it should be in a separate table Job).