Project Assignment
Overall Description:
The project requires you to envision a database application, and to implement it in several stages: (a) analyze the requirements of your application and represent them using a conceptual model, like ER (b) translate the ER schema into a relational schema/SQL DDL (c) populate your tables with sample data (d) analyze the operations you would like to support for your application, represent these operations in SQL DML and test your SQL statements. Also depending on your individual interests, you can investigate different advanced SQL aspects like views, triggers, stored procedures etc.
You will submit the project in multiple phases, starting with the Project Group Information and then 3 more phases. A demo with all the group members being present is required for Phases I, II and III. Failure to show the demo will get you 0 credit.
Project Phase 0: Group Information. Due midnight on Sep 20.
The scope of the project is such that a single person will be able to finish the project satisfactorily. However, you are encouraged to form groups, as you will learn from other members of the group. Groups can have a maximum of four members (the recommended size for a group is 3).
You will send an email to the instructor and all the group members with the group information before the due date. Send an email to the instructor even if you are only a group of one. Also cc all your group members in your email. [4 pts]
Phase I: ER Design Phase:
Decide what application you will work on, give it a title, and determine your application requirements. (Note that an interface to access the database application is outside the scope of our project).
Come up with a conceptual schema for this application in ER. To be realistic and practical, I recommend that your application should have a minimum of four entity types and four relationship types, and at most ten entity types and ten relationship types. However, you have the flexibility depending on your application requirements. Discuss with the instructor when in doubt.
In your report, you will include the title for your project, a short description of the project, the application requirements, the complete ER schema, and what each group member worked on.
Schedule a time-slot with the instructor for demonstrating your project. All members of the group MUST be present during the demo (physically or using software such as teamviewer desktop sharing software).
You will be graded based on your demonstration, report and answering questions for this and the remaining phases.
Phase II: SQL schema/DDL and data:
Take the above ER schema, and translate this to relational model. Come up with SQL DDL statements for your relational schema. You must test your DDL statements. Come up with test data, and load your tables with this data.
Your report must include your ER schema (from Phase I, with modifications if any), your complete SQL DDL statements, all the data in your tables, and what each group member worked on.
Schedule a time-slot with the instructor for demonstrating your project. All members of the group MUST be present during the demo (physically or using software such as teamviewer desktop sharing software).
Phase III: Operations:
Think of what operations you would like to support on your database. Can a user insert new tuples? What can he/she query? Your set of operations should be reasonably comprehensive for your application.
Your queries may include one or more parameters, whose value must be filled at run-time. For example,
Retrieve name of student given his/her ID
SELECT name
FROM student
WHERE student_ID = ‘X'
Here ‘X' is the student ID supplied at run-time.
Also try to incorporate complex queries (such as nested queries, aggregates etc) among your queries, as reasonable.
Once you have come up with the set of operations, write them down in your report, a short rationale behind these operations, and write the corresponding DML statements. A sample report explaining two scenarios is:
Scenario: user can obtain student details based on student ID.
User submits: student ID
Result displayed: name of student
Queries:
SELECT name
FROM student
WHERE student_ID = ‘X'
Scenario: user can obtain courses a student's GPA from his/her name.
User submits: student name
Result displayed: GPA of student.
Two Queries are used in this scenario.
first get student ID from name
SELECT student_ID
FROM student
WHERE name = ‘X'
Now, calculate GPA from the grades the student has in his courses.
SELECT avg (grade)
FROM enrollement
WHERE student_ID = ‘Y'
You must test your DML statements.
Your report should include the ER schema, the SQL DDL statements, the scenarios for Phase III, the SQL statements for those scenarios, and a description of what each group member worked on.
Schedule a time-slot with the instructor for demonstrating your project. All members of the group MUST be present during the demo (physically or over phone or using software such as teamviewer desktop sharing software).
Additional Tips
To do a successful project, be on schedule. Also, remember late projects will not receive any credit (you will get a zero for any late submissions). A group member who is not present at a demo for a phase will not receive any credit for that phase.
Alternate project for the adventurous
If you are very confident, I suggest that you think about an alternate project, that students have not done in the past. It is a project done at UMass Amherst in their db course (actually their 3rd DB course), and involves using python and postgres (the instructions and code given are for postgres DB - but if you want to translate the instructions to Oracle DB, you may use Oracle. I recommend that you use postgres DB itself). Note that these are all open source tools and software. If you are pursuing this project, I recommend that you get a linux VM and complete the project on the linux VM.
PHASE III-
Description of Project:
Morgan's Auto Emporium is a full service, retailer of fine used cars. Since the business is thriving, we have now opened multiple locations to server our customers more conveniently. Because we care about our salespeople, and because they are the most important asset to the company, the decision has been made to award an employee from each branch who records the most total sales per month. Therefore it is necessary to track each employee's sales at the branches.
Requirements:
1. Track sales of cars
2. Track employees sales for the month
3. Keep a record of car Inventory for the branches
4. Keep a record of company branch locations
5. At the end of each month determine awards for top salesperson at each branch
Given our requirements, we should now consider some functions to run on our data to see if our entity relationship model for our database is robust. Our design should be able to handle several different types of actions. Our proposed list is comprised of the following:
1. Insert new data to the tables - inventory, employees, customer, and sale table, etc.
2. Update data in existing tables - inventory, employees, customer, branch (new branch), etc.. - Deletes or changing of names, or corrections to incorrect data entry.
3. Find the top 3 salespersons for each branch
4. Find the top 3 salesperson for all branches combined (our top 3 breadwinners)
5. Find the best customers (customers who purchased the most vehicles)
6. Find the most expensive vehicles in the inventory (car, truck, SUV)
7. Find the most expensive cars (top 3) in the inventory
8. Find the most expensive SUV's (top 3) in the inventory
9. Find the most expensive Trucks (top 3) in the inventory
10. Find the top salesperson for sales award
11. Find the top employee for a service award
12. Update a table with data from another table - correlated update
13. Cascade delete data from tables - those that have dependencies should delete the data.
14. Find branches that sell (x) model of car - use group by and order by
15. Find the min price of car and max price of car - order from least expensive to most expensive
16. Sum each Employee's total sales $$ for the month.
17. Identify branches with more than (x) number of certain cars. - Identifying excess inventory to discount
18. Give a list of all employees and their start dates in order from most seniority to least seniority.
19. Use a TRIGGER in oracle to automatically generate a sales report for each branch after every business day.