T. Andrew Yang

Tel.: (281) 283-3835
Last updated: 11/04

CSCI 5333 Data Base Management Systems

Fall 2004

Assignment 1
 Assignment 2
 Assignment 3
Project 1
Project 2
  • Assignments

    • NOTE:     Page and figure numbers referenced on this page refer to the 4th edition of the Elmasri & Navathe book.
Assignment 1 

1.A (5 pts) Visit the discussion group created for this class, and join the group as a member.  Throughout this class, you are responsible for regularly visiingt the discussion group to find recent announcements, reminders, and discussions.  The group's url is available from the syllabus page.  Print out the confirmation message in response to your membership request, and attach it to this assignment.
1.B  (15 pts) Give a concise definition of the following terms:
    (a) integrity constraints (b) domain constraints    (c) identifying relationship    
    (d) category (i.e., union in ER)    (e) embedded SQL programming

1.C (30 pts) The following questions are based on the relational database schema as given in Figure 5.5 on page 136. If you would need a refresher of your SQL knowledge, please refer to Chapter 8, your undergraduate database text, or any online SQL tutorial.
  1. Explain the choice of primary key (ESSN, DEPENDENT_NAME) in the relation DEPENDENT. Can ESSN alone be the primary key? Can DEPENDENT_NAME alone be the primary key?
  2. The attribute DNO in the relation EMPLOYEE is a foreign key. Explain its purpose by, for example, identifying the referenced relation and the role of the foreign key.
  3. The attribute DNUM in the relation PROJECT is also a foreign key. Explain its purpose by, for example, identifying the referenced relation and the role of the foreign key.
  4. The attribute SUPERSSN in the relation EMPLOYEE is also a foreign key. Explain its purpose by, for example, identifying the referenced relation and the role of the foreign key.
  5. Write the SQL equivalent of the following query: "List information about employees whose department manager is Franklin Wong.".
  6. Write the SQL equivalent of the following query: "List information about employees whose supervisor is Franklin Wong.".
  7. Write the SQL equivalent of the following query: "List the average salary of employees in each of the departments." (Note: GROUP BY is needed.)
  8. Explain and correct the error(s) in the following SQL query:  SELECT COUNT(*) FROM EMPLOYEE GROUP BY DNO;
  9. Explain and correct the error(s) in the following SQL query:  SELECT DNAME, COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNUM = DNUMBER GROUP BY DNUM;
  10. Given the relational database state as shown on page 137, show the result of the following query: 
SELECT DNAME, COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER;

Go to the Index

Assignment 2
2.A This exercise is based on the AIRLINE relational database schema as shown in Figure 5.8, page 146). For the semantics of the model, please refer to the ER diagram in Figure 3.17, p.80.

For each of the following queries, write down (a) the relational algebra expression, and (b) the SQL query. NOTE: For this exercise, do NOT use nested SQL queries.
  1. (10 pts) For each flight, list the flight number, the departure airport for the first leg of the flight, and the arrival airport for the last leg of the flight.
  2. (10 pts) List the flight numbers and weekdays of all flights or flight legs that depart from Houston Intercontinental Airport (airport cose 'IAH') and arrive in Los Angeles International Airport (airport code 'LAX').
  3. (10 pts) List the flight number, departure airport code, scheduled departure time, arrival airport code, scheduled arrival time, and weekdays of all flights or flight logs that depart from some airport in the city of Houston and arrive at some airport in the city of Los Angeles.
  4. (10 pts) Retrieve the number of available seats for flight number 'CO197' on '1999-10-09'.
2.B (20 pts) (Continued from above) Consider the following update operations for the AIRLINE database: (a) To enter a reservation on a particular light, (b) To enter a reservation on a particular flight leg on a given date.
  1. Give a sample query for each of the two update operations.
  2. What types of constraints would you expect to check?
  3. Which of these constraints are key, entity integrity, and referential integrity constraints, and which are not?
  4. Specify all the referential integrity constraints that hold on the schema.
Assignment 3

3.A (30 pts) This exercise is based on Exercise 10.18 (p.328). Prove or disprove the functional dependencies a through g. (5 pts per functional dependency)

3.B (20 pts) This exercise is based on Exercise 10.19 (p.329). Use both approach one and two. (10 pts per approach)

3.C (15 pts) This exercise is based on Exercise 10.33 (p.331)

Go to the Index

Projects
  • Project 1  (Database Design) 
  • Project Objectives
To design and implement a relational database application using UNIX Oracle.

NOTE: If you are not familiar with UNIX or UNIX Oracle, refer to the first several links at this FAQ page: http://sce.uhcl.edu/UnixLabFAQ.asp.
  • Requirements
The BANK ER model as shown on page 81 (Figure 3.18) is an over-simplified model of banking.  Visit the web site(s) of one or more major banks (e.g., http://www.jscfcu.org/ or http://www.citibank.com/ ) to understand common services provided by banks.  Modify the BANK ER diagram on page 81 with the following extensions:
  1. Refine the LOAN entity type by creating subclasses such as VehicleLoan, Mortgage, HomeEquity, HomeImprovement, etc.
  2. Refine the CUSTOMER entity type by creating subclasses to accommodate different types of customers, including individuals and organizations.
  3. Refine the ACCOUNT entity type to accommodate different types of acounts (saving, checking, CD, investment, etc.).
  • Preliminary Report (40%)
The preliminary report shall be handed in as a hard copy.  
The preliminary report shall contain the following items:
  1. An EER diagram. You may extend the ER diagram on page 81 (Fig. 3.18) to incorporate the requirements listed above.  Refer to Chapter 4 for EER modeling.
  2. A UML diagram for the same model, depicting the definition of the classes and their relationships.  Refer to Chapter 4 and the UML links on the class web site for UML modeling.  Use MS Visio, available in the labs, or any proper drawing tool to draw the model.  Make sure your model adhere to the following guidelines:
    1. Attributes and methods to be defined in the classes should be clearly represented. 
    2. In addition to its name,  a method's definition includes its parameters, the type of each of the parameters, and the type of the returned data from the method. 
    3. Multiplicity constraints shall be clearly marked for each of the associations.
  3. Convert the conceptual model into a relational database model.  Refer to Chapter 9 for steps of conversion.
  • Final Report (60%)
The final report shall be submitted electronically as a single zip file to yang@uhcl.EDU , cc'ed to the TA's email account.

A Note about Electronic Submissions: If you'd like to receive a receipt from the instructor for your submission, make sure you set up your email to automatically request a receipt.  Typically a confirmation is not sent due to the large number of submissions.  Alternatively, you may see the TA or the instructor during their office hours to confirm the receipt of your submission.

The final report shall contain the following items:
    1. The relational database model:  Feel free to modify the database model per the  grader's suggestions.
    2. SQL statements to implement the relational database model
    3. SQL statemetns to insert records into the database.  Note: You should arrange the data in such a way that the queries in item 4 below would return some meaningful result.
    4. SQL queries to implement the following sample operations on the data:
    1. List all individual customers whose balance of all accounts exceed $500,000.
    2. List the average checking account balance of all corporate customers.
    3. List the customers' names, the loan amounts and the length of the loans (15 year, 30 year, etc.) of mortgage loans for all customers whose properties are located in Houston, TX.
    4. List the total HomeEquity loan amount for the branch office at 'Clear Lake City, TX'.
    5. List the addresses of the branch offices that have issued 6 or more VehicleLoans.
    6. List the total loan amount of each type of loans for each of the branch offices.  Note: 'group by' is needed for this query.
Important!   As part of the final project, you must arrange a time to give the TA a demo of your project.  Those who fail to give a demo within a week of the due date or before the last teaching day, whichever comes first, will receive zero for the demonstration part of the project.
  • Project 2
    • Description: 
In this project, you are going to implement the algorithm of calculating the closure of a given set of functional dependencies, by using the Armstrong's three inference rules as discussed in the class. That is, the algorithm will iteratively produce new functional dependencies from the existing set of functional dependencies, by applying each of the three rules, until no more new functional dependency would be produced from the inference rules.
The language of choice is Java or C++. 
    • Requirements: 
  1. Define a class called FunctionalDependency.  The class has the following attributes: leftSide, rightSide . Both leftSide and rightSide are subsets of attributes of a given relation schema, and the leftSide functionally determines the rightSide. The class should support necessary public methods, such as  getLeftSide ( ), getRightSide( ), memberOfLeftSide( ), memberOfRightSide( ), etc. Define the methods with appropriate parameters.
  2. Define a class called RelationSchema. The class contains the following attributes: setOfAttributes, setOfFunctionalDependencies , and closreOfFunctionalDependencies. setOfAttributes is a set of attributes for the given relation schema. setOfFunctionalDependencies is a set of FunctionalDependency (as defined in requirement 1). closreOfFunctionalDependencies stores the closure of functional dependencies, which is to be calculated by your program. Define appropriate methods to support the class. One of the methods that you must define for this class is the calculateClosure method, which calculates the closure of a given set of functional dependencies and place the result in the attribute closreOfFunctionalDependencies.
  3. Define a driver class called CalculateClosure.  The class first reads from a file to fill setOfAttributes and setOfFunctionalDependencies. Apparently the file contains the set of attributes and functional dependencies for a given relation schema. The class then calls appropriate method to calculate the closure of functional dependencies for the given relation schema and fill the result in the attribute closreOfFunctionalDependencies. Define a method called storeClosure, which stores the closure in a data file. You may define the class in such a way that it is capable of handling multiple relation schemas.
  • Design of the project (to be handed in as hardcopies): Check the syllabus for the due date. 
  • Content of the design:
  1. A UML class diagram depicting the definition of the classes and their relationships.  Attributes and methods to be defined in the classes should be clearly represented.  In addition to the name, the definition of a method include its parameters, the type of each of the parameters, and the type of the returned data from the method.  Multiplicity constraints shall be clearly marked for each of the associations in the UML diagram.
  2. Show the pseudocode of the method calculateClosure( ).
    • Final Report
      • To be submitted electronically to yang@uhcl.edu as a single zip file, CC'ed to the TA.
      • Check the syllabus for the due date.
      • As part of the final project, you must arrange a time to give the TA a demo of your project.  Those who fail to give a demo within a week of the due date or before the last teaching day, whichever comes first, will receive zero for the demonstration part of the project.
      • NOTE: If you'd like to receive a receipt from the instructor for your submission, make sure you set up your email to automatically request a receipt.  Typically a confirmation is not sent due to the large number of submissions.
  • Content of the final report:
  1. Include the design.  You may modify the initial design to include refinements or corrections since its initial submission.
  2. The source program implementing the design.
  3. To test your program, use the following three sample relation schemas:
    1. R1 = (A, B, C, D}; set of functional dependencies = {fd1: A --> C, AB --> D, AC --> D}
    2. R2 = (A, B, C, D, E, F, G, H}; set of functional dependencies = {fd1: A --> C, AC --> D, E --> AD, E --> H}
    3. R3 = (A, B, C, D, E, F, G, H}; set of functional dependencies = {fd1: A --> CD, E --> AH}
  4. For each of the test cases, include the input and the output data files in the final report. The input data file stores the attributes and set of functional dependencies of the given relation schema, and the output data file stores the closure of functional dependencies. 
  5. In the output data files, for each of the calculated functional dependencies, your program should append an anotation by listing the inference rule and the source functional dependencies from which the new one is derived.
Go to the Index

dd   Main Page

dd   Biography

dd   Teaching

dd    Research

dd    Services

dd     Other Links