CS465 Midterm -- Spring 2021

  1. This exam is open-note, open-book.
  2. You must answer all of the questions.
  3. The final question requires that you draw a B+-tree. You will take a picture of your drawing and upload it immediately after you exit the exam. You will have until 1:10pm to complete your upload. The link is called "Midterm-Upload". If you decide to draw a picture of your ER diagram then you will also need to take a picture of this drawing and upload it as well.
  1. (Queries: 21 points) Write the following queries in the requested language. The queries all refer to the Appointments database.

    1. (SQL) Print the patient name, date and time of appointment, groupNo, and clinic address of where the shot will be administered for all patients who have appointments to get a shot between 2021-03-01 and 2021-03-10 inclusive.
    2. (SQL) Print all clinics that have a staff count that is greater than 10 on 2021-03-05. For each clinic you only need to print the clinicNo and number of staff. Name number of staff "staffCount".
    3. (Relational Calculus--CS465 students only) Print the name, address, groupNo, group description, and the group start date for the patient named 'Brad Vander Zanden'.
    4. (Relational Calculus--CS565 students only) Print the patient name, day, and the name of the vaccine of every patient who has been administered a shot by 'Wily Coyote'.

  2. (Physical Design: 3 points) Which of the following attribute in the Appointment database would be the best candidate for a cluster index?

    1. Appointment(PatientNo)
    2. Appointment(Date)
    3. Group(Description)
    4. Staffing(StaffNo)
    5. Patient(Address)

  3. (Physical Design: 3 points) Which of the following relations should not have any indices associated with them (3 correct answers)?

    1. Patient
    2. Clinic
    3. Staff
    4. Appointment
    5. Vaccine
    6. MedicalRecord
    7. Group

  4. (ER Diagrams: 14 points)
    1. List the entities in the Appointment database
    2. Draw each of the relationships that exist among entities in the Appointment database. Do not show me multiplicities. You may either 1) draw your solution on a piece of paper, take a photo of it with your phone, and upload it to canvas as soon as the exam completes, or 2) use ascii "art" to draw the relationships. If using ascii "art", then draw a binary relationship as a directed edge as follows: 
      	         relationshipName
	      A -------------------> B
      Draw a non-binary relationship by drawing edges from each of the entities to the relationshipName as follows: 
      	      A
	      |
	C---Owns---D
      To get nice formatting, go to "Paragraph" and select "Preformatted". If using ascii "art" then draw each of the relationships separately on separate lines. For example, you would draw each of the above two relationships on separate lines.

  5. (ER Diagrams: 8 points) A university database has three entities named students, courses, and teachers with the following relationships: 
                 take              teach
Students------------>Courses<-------------Instructors	  
      (a)          (b)      (c)         (d)
    You may assume that:
    1. Students take a minimum of 0 and a maximum of 7 courses
    2. Courses enroll a minimum of 10 and a maximum of 50 students
    3. An instructur teaches a minimum of 1 and a maximum of 4 courses
    4. A course is taught by exactly 1 instructor

    For each of the above letters a-d, enter the multiplicity for that entity in the relationship (e.g., (a) represents the multiplicity of students that may occur in the Students take Courses relationship).

    1. ______________________
    2. ______________________
    3. ______________________
    4. ______________________

  6. (Normalization: 22 points) You are constructing a database for an online order company. A customer places an order for one or more products. A service representative fulfills this order by finding the appropriate warehouse for each product, bundling the products if necessary, and sending them to the customer's address. Since different warehouses may be used to fulfill the order, some products may arrive on different dates. A customer may place multiple orders on the same day and the same service representative will handle all orders for that day. Based on the above information, you have derived the following relation:

    Order(customerNo, customerName, city, street, zipcode, dateOrdered, timeOrdered, [productNo, productName, description, pricePerItem, quantity, dateDelivered, warehouseNo, warehouseName, warehouseAddress, quantityAvailable]*, serviceRepNo, serviceRepName, totalOrderCost)

    You want to decompose this relation into a set of relations in 3rd normal form. You can make the following assumptions:
    1. The following functional dependencies exist for this relation: 
      	  customerNo --> customerName, city, street, zipcode
	  productNo --> productName, description, pricePerItem
	  warehouseNo --> warehouseName, warehouseAddress
	  warehouseNo, productNo --> quantityAvailable
	  serviceRepNo --> serviceRepName
	  customerNo, dateOrdered --> serviceRepNo
	  customerNo, dateOrdered, timeOrdered --> totalOrderCost, [productNo, quantity, dateDelivered, warehouseNo]*
    2. The primary key is (customerNo, dateOrdered, timeOrdered)
    3. [productNo, productName, description, pricePerItem, quantity, dateDelivered, warehouseNo, warehouseName, warehouseAddress, quantityAvailable] is a multi-valued attribute of the Order relation giving information about the item that was ordered. The * means 1 or more items.

    Answer the following questions.

    1. This table is susceptible to various types of anomalies that can be eliminated using normalization. For each of the following items, indicate if it is or is not an update anomaly and if it is an anomaly, then indicate whether it is an insert, delete, or modification anomaly. If changing/deleting/inserting something can cause an inconsistency that cannot be eliminated via normalization, then it is not an anomaly.

      1. If you delete the last order associated with a product, you lose all information associated with that product.
      2. You cannot delete a warehouse unless you have deleted all products associated with that warehouse.
      3. If you change a customer's number, then you must change every instance of that customer's number in the database or you will have an inconsistency.
      4. You cannot enter a new warehouse unless it has an order associated with it.
      5. If you change a product's name you must change every instance of that product's name or you will have an inconsistency.
      6. If you change the day or time of an order, you must change every instance of that day or time or you will have an inconsistency.
      7. If you change a service representative's number you must change every instance of that number or you will have an inconsistency.

    2. Next to each of the following functional dependencies, indicate whether they are:
      1. Used to convert the relation from 1st to 2nd normal form
      2. Used to convert the relation from 2nd to 3rd normal form
      3. Not used in the decomposition because the dependency represents a candidate or primary key 
      a  b  c	  customerNo --> customerName, city, street, zipcode
a  b  c	  productNo --> productName, description, pricePerItem
a  b  c	  warehouseNo --> warehouseName, warehouseAddress
a  b  c	  warehouseNo, productNo --> quantityAvailable
a  b  c	  serviceRepNo --> serviceRepName
a  b  c	  customerNo, dateOrdered --> serviceRepNo
a  b  c	  customerNo, dateOrdered, timeOrdered --> totalOrderCost, [productNo, quantity, dateDelivered, warehouseNo]*
    3. List the final 3rd normal form relations that you should derive. Use the notation R(a,b,c) where R is the name of the relation and a, b, and c are the names of the attributes. I do not care to see your 2nd Normal form relations. It is okay to add an OrderNo attribute to the final 3rd normal form of the Order relation. This might help you with foreign keys in one of the other relations. Hint One of the 3rd normal form relations will be formed when you convert the original Order relation from unnormalized form to 1st normal form and hence will not use any of the above functional dependencies.

  7. (MongoDB: 6 points) The following Mongo collection represents a catalog of autos that we have for rent. 
    1. {	
      "_id" : 100,
      "make" : "Ford",
      "model" : "Fusion",
      "year" : 2014,
      "options" : [ "engine start", "moon roof" ],
      "price" : 135,
      "location" : { "street" : "1414 Cherry Lane",
                     "zipcode" : 37996 }
    }
    2. {
      "_id" : 200,
      "make" : "Honda",
      "model" : "Accord",
      "year" : 2014,
      "options" : [ "spoiler", "alloy wheels", "sunroof" ],
      "price" : 169,
      "location" : { "street" : "518 Magnolia Ave",
                     "zipcode": 37916 }
    }
    3. {
      "_id" : 300,
      "make" : "Honda",
      "model" : "Civic",
      "year" : 2013,
      "options" : [ "spoiler", "engine start", "sunroof" ],
      "price" : 85,
      "location" : { "street" : "518 Magnolia Ave",
                     "zipcode": 37916 }
   }
    4. {
      "_id" : 400,
      "make" : "Ford",
      "model" : "Mustang",
      "year" : 2021,
      "options" : [ "turbocharger" ],
      "price" : 215,
      "location" : { "street" : "1414 Cherry Lane",
                     "zipcode": 37996 }
   }
    5. {
      "_id" : 500,
      "make" : "Ford",
      "model" : "Mustang",
      "year" : 2019,
      "options" : [ "turbocharger", "satellite radio", "moon roof" ],
      "price" : 155,
      "location" : { "street" : "110 Apple Orchard Circle",
                     "zipcode": 37996 }
       }

    For each of the following Mongo queries, circle the tuples that would be returned by the query (these are all or nothing questions so you must get all tuples correct in order to get full credit): 

    1   2   3   4   5   : db.catalog.find({ "price" : { $gte : 100, $lte: 200 } })
1   2   3   4   5   : db.catalog.find({ "location.zipcode" : 37996, options: { $all : ["turbocharger", "moon roof"] } } )
1   2   3   4   5   : db.catalog.find({ $or: [ { year: 2013 }, { options: { $in: ["turbocharger", "engine start"] } } ] } )
  8. (Normalization, Functional Dependencies: 12 points) Here is a possible table for airline reservations and some sample data:

    FlightNoDateDeparture TimeDeparture AirportDeparture GateArrival TimeArrival AirportArrival GateCustomerNoCustomer NameSeatNoTicket PriceAircraftNoAircraft Model
    UA4342021-03-1015:00TYSA616:40OHDB43C438Wily Coyote4a550A53Boeing 737
    UA4342021-03-1015:00TYSA616:40OHDB43C138Bugs Bunny1d1080A53Boeing 737
    UA4342021-03-1115:03TYSA1016:45OHDB10C53Tom The Cat1d1185A59Boeing 787

    You should make the following assumptions (A "flight" means flightNo):

    1. A flight always has the same departure and arrival airports, regardless of the day.
    2. Each day a flight might have a different departure time/gate and arrival time/gate, and a different plane flying the route
    3. A seat might have a different price on each date on which a flight occurs
    4. A flight only goes once each day
    5. Seats are never double booked on any flight and customers may not have more than one seat on any given flight. Of course it is okay for a seat to have different customers on different days and for a customer to have reservations on the same flight on two different days.
    6. A customer is allowed to have multiple reservations per day since they might require multiple flights to get to their destination.
    7. An aircraft number determines the aircraft model, but the reverse relationship does not hold.
    8. The customer number determines the customer's name, but the reverse relationship does not hold.

    List the functional dependencies for this table. Do not worry about classifying them as partial or transitive and do not worry about identifying the primary key.

  9. (B+ Trees: 8 points) Show what the following 4 degree B+ tree looks like after 150 is added it. For this problem M = 4 and L = 3. If an interior node splits, then the left node should get the extra child. You will need to draw your solution on a piece of paper, take a picture of it with your smart phone, and upload it to Canvas after you exit the exam.

  10. ( Relational Algebra-3 points) Which of the following queries requires set division in order to answer the query?

    1. Find all staff making more than $100,000
    2. Find all students who have taken CS100
    3. Find all people who have visited all the Baskin Robbins stores in Knoxville
    4. List all the students who have scored more than 90 on every exam in a course
The following information should be used in answering the questions on this exam. You are given the following relations about a vaccine appointment website that is maintained by a governmental agency: 
Patient(PatientNo, Name, Address, DateOfBirth, GroupNo)
Clinic(ClinicNo, Name, Address)
Staff(StaffNo, Name)
Staffing(ClinicNo, StaffNo, Date)
Appointment(PatientNo, ClinicNo, Date, Time)
Vaccine(VaccineNo, Name, Dosage, AgeFrom, AgeTo)
MedicalRecord(PatientNo, VaccineNo, Date, Administerer (StaffNo))
Group(GroupNo, Description, StartDate)

You may make the following assumptions about these relations: