CSC 8490: Database Systems

Fall, 2009
Homework

Table of Contents

hw #1:

For this assignment, consider the following journals: And consider the following topics: Find an article in one of those journals on one of those topics, and write a brief (font size at least 12, limit one page) summary of the article. Include the topic, journal, date, title, and author. Classify as well the type of database user the article would most likely interest, according to the classification in slides 1-16 through 1-19. It's not necessary to attach a copy of the article.

You can access any of those journals on line by logging in as a Villanova student to the university's home page. If you link to the Falvey Library site, you will be eligible to read any of the journals subscribed to, which includes the four mentioned above. You'll need to identify the affiliation (ACM for TODS and SIGMOD, IEEE for TKDE, Science Direct for DKE). Hard copy is also available for a few of them in MSC 159.

This isn't supposed to take very long, nor should it require deep understanding of the paper. I expect you to remember the journal, author, and title, though.

For this and all homework submissions (unless some graphics are necessary) remember to use a word processor. In general, you may hand in the hard copy in class (no later than the beginning) or email it to the TA by that deadline, with cc to me (if you wish).

hw #2:

Draw two ER diagrams for the enterprise described below: one using standard (Chen) notation, and one using ordered pair (Merise) notation. Indicate clearly which is which. Remember to indicate keys and relationship classifications in the diagram. Use your common sense on any classifications that aren't made explicit in the specs. Don't add any more ingredients than are described here. If you can, use software (e.g., MS-Visio, freeware or Paint) to generate the diagram. At any rate, make sure it's very legible.

If you have questions about these specs, I'll play the role of the client who will resolve them.

This enterprise is to serve an HMO. Doctors have names, social security numbers and addresses. Each doctor also has a specialty and may have another doctor under whom s/he trained. Each patient has a name, patient id and primary physician. S/he may also have other doctors whom she has consulted, with the last date of each such consultation. If the patient is currently in hospital, then we store the date admitted and the ward to which s/he has been admitted. Wards have unique names; they also have a floor. Lab tests have codes and names; for examples, L293 may be the code for an ECG, and L102 may be the one for an endoscopy. For any test ordered for a patient, only one doctor is approved to have ordered it. We keep track of the last date any test was ordered for any patient by any doctor.

hw #3:

Consider this figure. It's based on Figure 7.8 in Elmasri/Navathe V but is not exactly the same one. Convert it algorithmically to a relational database schema. Your deliverable is a set of relation schemas, with primary and foreign keys indicated in the usual way.

hw #4:

Assuming the relational database schema for COMPANY as it appears in Figure 5.7 in our text, formulate the following queries in the relational algebra. Remember, they must be logically correct. It's not enough if they happen to work on the sample instance in the text.

  • the genders and salaries of Usha Bhavanam's immediate subordinates
  • the names of the other men who work in the same department as Takashi Binns
  • the last names and salaries of the department managers
  • the locations of the departments that don't control any of the projects located in Hyderabad

    • hw #5:

    Take the same queries as the last assignment, but formulate them in SQL this time. Assume we don't want any duplicates in our answers.

  • the genders and salaries of Usha Bhavanam's immediate subordinates
  • the names of the other men who work in the same department as Takashi Binns
  • the last names and salaries of the department managers
  • the locations of the departments that don't control any of the projects located in Hyderabad

    • hw #6:

    For the same relational database schema, formulate these queries in SQL as well:

  • each project name, together with the average salary of the women who work on it
  • each supervisor's (note: there's a difference between a manager (viz mgrssn) and a supervisor (superssn)) first name, with the maximum salary of her/his immediate subordinates
  • the project names worked on by the highest-paid employee(s)
  • for each department manager, her/his ssn and last name, together with the number of women in his/her department, for those departments where the average salary (including men this time) is less than $85K

    • hw #7:

    (a) Write a Java class called hw7 that will take a username and password as arguments, connect to Oracle on csdb and print out the ssn, last name and sex of each department manager whose salary exceeds $39000 and who has at least one dependent. Assume the COMPANY database from our text.

    (b) How would you modify this to let the minimum salary permitted, rather than be "hard-coded" as 39K, be learned only at run-time (say, by making it a third command-line argument)?

    Although this assignment and the next are only "paper and pencil" ones, you may try your solutions out on csdb on the tables owned by user dbta (e.g., dbta.employee, dbta.department, etc).

    hw #8:

    Write PL/SQL code that does the same thing as part (a) of the last homework problem, but this time assume you are already on csdb and logged into your Oracle account there.

    hw #9:

    Design an EER model with the usual conventions that uses subclass structures wherever appropriate for the following situation: Persons have ssn's, names, and dates of birth. They may be employees, customers, both or neither. Each bank account is identified by an ID. There's also a current balance and interest rate that we keep track of. It was set up by a unique employee and goes to a unique customer's taxable income, although it may be a joint account of several customers. Accounts are either savings, checking or money market ones. Depending on which type they are, we note either their classification, current check number or category, resp.

    hw #10:

    Take the EERD in this figure, and convert it to a UML class diagram. You may omit methods and data types.

    hw #11:

    For the same figure, convert it to the relational data model. Implement the Fiction/Nonfiction specialization by using strategy (A) (the superclass and each subclass get a relation schema) and the History/Science/Essay one by strategy (C) or (D) (flatten the hierarchy). For this stage, include the "extensional database": all table names, primary keys and foreign keys.

    Next, provide SQL code for any one of the "intensional" views.

    Finally, take one constraint, tell which one it is, and write SQL code for enforcing it.

    hw #12:

    As we've seen, one of the key tasks in database development is the conversion between nontechnical and technical language. In the case of functional dependencies, suppose, e.g., we have the schema

    Widgets = {color, location, shape, ambience}

    Don't worry about the attributes' semantics: they're not supposed to mean anything. Now consider the two English sentences

    a) Widgets with different shapes must come from different locations.
    b) Widgets with different ambiences could still have the same shape.

    These can be translated into the language of FD's as follows (do you see why?):

    a) l -> s holds.

    b) s -> a fails.

    Now take each of the following statements (don't impute any "meaning" to them, nor are they necessarily consistent), and translate it similarly into an assertion that some FD holds or fails.

    1) It's impossible for widgets of the same color to have different shapes.
    2) It is possible for widgets of different colors to come from the same location.
    3) Widgets from the same location must have the same ambience.
    4) It's possible for widgets of the same color to have different locations and yet the same shape.

    hw #13:

    Suppose now this time the schema Widgets = {area, brightness, changeability, density, energy}, with the FD's
    b->a
    b->c
    b->e
    b->d
    a->e
    ac->b

    a) This is not a minimal set of FD's. Explain.
    b) Display graphically a minimal set equivalent to the given set.
    c) How many keys are there for this set?
    d) List them all.
    e) Take one of those keys, and prove it's one.
    f) What is the highest normal form the schema is in? Explain.
    g) Give a scenario for an delete anomaly caused by this (relatively) low NF.

    hw #14-15:

    This assignment is longish, so it is equivalent to two regular ones. Assume the schema R = {a, b, c, d, e, f, g, h, i, j}. Below are three sets F of functional dependencies and three decompositions D. In each case, explain with reasons whether D is FDP and whether it is LJD, and give the NF's of its components.

    hw #16:

    Classify each of the following schedules as strict, cascadeless, recoverable or not recoverable. Choose the narrowest term in each case, and give reasons.

    S1: r2(X), w2(X), r1(X), r2(Y), w2(Y), w1(X), C2, C1.
    S2: r2(X), r1(X), w1(X), r1(Y), w1(Y), C1, w2(X), C2.
    S3: r2(X), w2(X), r1(X), w1(X), r2(Y), w2(Y), C1, C2.
    S4: r2(Y), r1(Y), w2(Y), r2(X), w1(Y), w2(X), C1, C2.

    hw #17:

    Suppose these three transactions are given:
    T1: r1(Z); r1(Y); w1(Z); w1(Y);
    T2: r2(X); r2(Y); w2(Y);
    T3: r3(X); r3(Z); w3(X);

    For each of the schedules below, tell whether or not it is conflict-serializable and whether or not it is view-serializable. Give reasons. And if any answer is "yes," then provide the serial schedule to which it is equivalent:
    S1: r3(X); r1(Z); r3(Z); r2(X); r2(Y); w3(X); w2(Y); r1(Y); w1(Z); w1(Y);
    S2: r3(X); r1(Z); r2(X); r3(Z); r1(Y); r2(Y); w3(X); w1(Z); w2(Y); w1(Y);

    hw #18:

    TBA

    hw #19:

    TBA

    Back to CSC8490 main page