Sanjay Goel, School of Business, UAlbany

ITM 520: Database Management

Coursework

DATABASE MANAGEMENT PROJECT (30 points)

Instructions: Read the following case and use it to perform the below tasks. Ideally, groups should be working on the project throughout the semester as each class progresses to avoid too much work at the end of the semester. The project is due on December 3, 2009 submitted in-person in class before the exam or anytime before that in BA 310b to either Professor Goel or Damira. Points will be taken off for lateness.

The School of Business would like to help its MBA students with placement in various companies and firms. To facilitate this, they need to design a database with the primary purpose of scheduling interviews and facilitating searches by students and companies that are looking for candidates. The database should have information about students, job openings, companies, interviewers, interviews and conference rooms for interviews. Students have a lot of information that needs to be processed. This includes a first name, last name, student id number, social security number, concentration, and expected graduation date. Companies post job openings which students can apply for. The job posting date is recorded as well as the deadline of the posting. Also included is the title of the position, base salary, minimum requirements and description. Students apply for open positions in companies and the date and time of each application is recorded. Students should also be able to see the status of their application (whether there will be a follow-up interview and whether they were turned down for the position). Interviews can be facilitated by the University or as a result of job openings and need to have a date, time, and conference room location. Multiple students can be interviewed at the same time and multiple interviewers can participate in a student interview. Companies send employees who interview students. An employee has an employee id, room #, telephone #, fax #, and position title. The company keeps track of which employees interviewed which students as well as the result of the interview (if it resulted in a job offer and/or hiring). A company has an identifier, name, main contact #, and main fax number. Conference rooms are reserved by a company from the Business School and the company needs to specify the resources needed (e.g. computer, projector, etc.). There are multiple conference rooms and multiple instances of the same resource (e.g. there may be three projectors). The School should be able to track which company hired which students as well as how many students came from which concentration. In addition, such statistics as the average salary for the jobs per year should be available. Students should also be able to track their job offers.

Entity-Relationship Model (5pts): For each entity, identify the attributes and relationships. List all possible keys for each entity and select a suitable primary key. Draw an entity relationship model.Also, list all assumptions.

Normalization (5pts): Normalize your data to Boyce-Codd Normal Form and describe your normalization. List all assumptions you made to come up with your normalization. Also create a data definition table.

Database (2pts) Create the database in Access and populate the tables with the data.

Queries (12pts): Put enough data to demonstrate the queries on the data. Formulate at least 12 queries, including SELECT, CROSSTAB, nested queries and Data Definition Language (DDL) queries. Run the queries and present the results of running the query. Make sure that you have both single table and multiple table queries. Also show use of aggregate functions. Use the SQL option for entering the queries. Explain what each of your queries are doing -- they should all represent answers to questions that a user of the database would want to ask.

Forms/Reports (4pts):As a part of your project, you should prepare a project report which describes the design of your database system and the assumptions which you have made. Please include the E-R diagram, data definition table (with table names, attribute names, primary key and foreign key relationships)and the results of the queries as well as the SQL syntax used for running them.

Project Submittal (2pts):Please provide a hard copy of the project report (including all the elements of the project report and the database on a marked CD-R or CD-RW. If necessary, use a floppy disk, but these may lead to complications in transference from one computer to another.

Project Teams: Not more than 3 persons should work in a team. Teams should work together for the project. Each member should highlight his/her contributions.

ITM 520: Database Management

Coursework

E-R DIAGRAM HOMEWORK (8 Points)

Normalization Homework (8 Points)

SQL Class Assignment

SQL Queries Homework (14 points)

DATABASE MANAGEMENT PROJECT (30 points)