/CS1555TermProject

Term Project for Pitt CS1555 Database Management Systems

Primary LanguagePLSQLMIT LicenseMIT

CS1555TermProject

Term Project for Pitt CS1555 Database Management Systems (Summer 2016).

CS1555 Website | Recitation Website

A full list of this project's requirements can be viewed in 1555_term_project.pdf

Milestone 0: Assemble Teams (Due: July 6 @ 11:59PM)

  1. Add nfarnan to the contributors for the repository.
  2. Add weg21 to the contributors for the repository.
  3. Add all team members (name, pitt username, github username) to team_members.txt.
  4. Create a commit labeled "Milestone 0 submission" and push it to the repository.
  5. Send an email to nlf4@pitt.edu and weg21@pitt.edu:
  • The email title should be "[CS1555] Project milestone 0 submission".
  • Insert a link to the repository.
  • Insert the GitHub commit ID (40-character string / SHA-1 hash).

Milestone 1: The 'FaceSpace' Database Schema & Example Data (Due: July 11 @ 11:59PM)

  1. Database Schema Overview:
  • Choose appropriate data types to make up the attributes of each relation.
  • Define all structural and semantic integrity constraints.
  • All assumptions must be stated using comments within the database creation script.
  • Messages will be constrained to be less than 100 characters.
  1. Database Schema
  • Users
    • ID number(10) PK
    • FName varchar2(50) NOT NULL
    • LName varchar2(50) NOT NULL
    • Email varchar2(80) NOT NULL
    • DOB date NOT NULL
    • LastLogin timestamp
    • DateCreated timestamp NOT NULL
  • Friendships
    • UserID number(10) FK Users(ID) NOT NULL
    • FriendID number(10) FK Users(ID) NOT NULL
    • Approved number(1)
    • DateApproved timestamp
  • Groups
    • ID number(10) PK
    • Name varchar2(100) NOT NULL
    • Description varchar2(100)
    • Limit number(10)
    • DateCreated timestamp NOT NULL
  • GroupMembers
    • GroupID number(10) FK Groups(ID) NOT NULL
    • UserID number(10) FK Users(ID) NOT NULL
    • DateJoined timestamp NOT NULL
  • Messages
    • ID number(20) PK
    • SenderID number(10) FK Users(ID) NOT NULL
    • Subject varchar2(30) NOT NULL
    • Body varchar2(100) NOT NULL
    • RecipientID number(10) FK Users(ID) NOT NULL
    • DateCreated timestamp NOT NULL
  1. EXAMPLE DATA
  • 100+ Users
  • 200+ Friendships
  • 10+ Groups
  • 300+ Messages
  1. Create a commit labeled "Milestone 1 submission" and push it to the repository.
  2. Send an email to nlf4@pitt.edu and weg21@pitt.edu:
  • The email title should be "[CS1555] Project milestone 1 submission".
  • Insert a link to the repository.
  • Insert the GitHub commit ID (40-character string / SHA-1 hash).

Milestone 2: A JDBC Application To Manage 'FaceSpace' (Due: July 24 @ 11:59PM)

  1. Create a 'FaceSpace' Java application:
  • Must interface the Pitt Oracle server (unixs.cis.pitt.edu).
  • Must use the Java Database Connectivity (JDBC) API.
  • All Tasks must check for, and properly react, to any errors reported by the DBMS.
  • All Tasks must provide appropriate SUCCESS or FAILURE feedback to the user.
  • Transactions must be defined appropriately:
    • Design all SQL transactions only when necessary.
    • Use concurrency control mechanisms supported by Oracle to ensure inconsistent states do not occur:
      • Isolation Level
      • Locking Modes
    • Assume multiple requests can be made at the same time, on behalf of multiple users.
  • Implement the following functions:
    • createUser
      • Given a name, email address, and date of birth, add a new user to the system.
    • initiateFriendship
      • Create a pending friendship from one user to another.
    • establishFriendship
      • Create a bilateral friendship between two users.
    • displayFriends
      • Given a user, look up all of that user's establish and pending friendships. Print out this information in a nicely formatted way.
    • createGroup
      • Given a name, description, and membership limit, add a new group to the system.
    • addToGroup
      • Given a user and a group, add the user to the group so long as that would not violate the group's membership limit.
    • sendMessageToUser
      • Given a message subject, body, recipient, and sender, create a new message.
    • sendMessageToGroup
      • This should operate similarly to sendMessageToUser only it should send the message to every member currently in the specified group.
    • displayMessages
      • Given a user, look up all of the messages sent to that user (either directly or via a group that they belong to). The program should print out the user's messages in a nicely formatted way.
    • displayNewMessages
      • Operates similarly to displayMessages, but only displays messages sent since the user's last login.
    • searchForUser
      • This provides a simple search function for the system. Given a string on which to match any user in the system, any item in this string must be matched against any significant field of a user's profile. That is if the user searches for "xyz abc", the results should be the set of all profiles that match "xyz" UNION the set of all profiles that matches "abc". The names of all matching users should be printed out in a nicely formatted way.
    • threeDegrees
      • This task explores the user's social network. Given two users (userA and userB), find a path, if one exists, between the userA and the userB with at most 3 hop between them. A hop is defined as a friendship between any two users. The path should be printed out in a nicely formatted way.
    • topMessages
      • Display the top K who have sent or received the highest number of messages during for the past X months. X and K should be input parameters to this function.
    • dropUser
      • Remove a user and all of their information from the system. When a user is removed, the system should then delete the user from the groups he or she was a member of using a trigger. Note that messages require special handling because they are owned by both the sender and the receiver. Therefore, a message is deleted only when both the sender and all receivers are deleted. Attention should be paid handling integrity constraints.
  1. Create a commit labeled "Milestone 2 submission" and push it to the repository.
  2. Send an email to nlf4@pitt.edu and weg21@pitt.edu:
  • The email title should be "[CS1555] Project milestone 2 submission".
  • Insert a link to the repository.
  • Insert the GitHub commit ID (40-character string / SHA-1 hash).

Milestone 3: Bringing It All Together (Due: August 4 @ 11:59PM)

  1. Create a Java driver program to demonstrate the FaceSpace database by calling all of the created methods from Milestone 2.
  • Rewrite all of the methods to do the following:
    • Take input parameters instead of querying for data.
    • Use @throws SQLException and remove all exception handling. Let the driver handle the exceptions.
  • Remove the input Scanner object from the methods and place it in the driver program.
    • The driver will ask for the input, and pass to the methods.
  1. Create a commit labeled "Milestone 3 submission" and push it to the repository.
  2. Send an email to nlf4@pitt.edu and weg21@pitt.edu:
  • The email title should be "[CS1555] Project milestone 3 submission".
  • Insert a link to the repository.
  • Insert the GitHub commit ID (40-character string / SHA-1 hash).

Milestone 4: JUnit Testing

  1. Create at least one JUnit test for each method in DatabaseConnections.java
  2. Create unit tests for the following conditions:
  • Normal cases: Use each method with input returning expected results.
  • Edge cases: Use each method with input returning unexpected results.