SQL project

Project for CIS/CSC 384. Fall 2017

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: Due midnight on Oct 11.

Decide what application you will work on, give it a title, and determine your application requirements. You can check sample project at:

http://infolab.stanford.edu/~ullman/dscb.html#projects (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. [7 pts]

Phase II: SQL schema/DDL and data: Due midnight on Nov 13.

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). [7 pts]

Phase III: Operations: Due midnight on Dec 11.

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). [7 pts]

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.

The project description is available at http://avid.cs.umass.edu/courses/645/s2017/hw/hw1.html

The phases for this project are as follows:

Phase 0: Group formation, and indicating project intent – before midnight on Sep 20.

Phase 1: Install the necessary software, and Step 1 of the project – before midnight on Oct 11.

Phase 2: Step 2 of the project – before midnight on Nov 13.

Phase 3: Steps 3, 4 of the project – before midnight on Dec 11.

Steps 5, 6 in the project description are optional.

Again, you will demonstrate each of the phases 1 – 3 with me; additional tips still apply.