Description
The objective of this team project is for you to apply what you have learned in class to design and develop a database application using PostgreSQL
(https://www.postgresql.org/docs/current/index.html). The project is to be done in teams of four students.
1. Project Tasks
You are to develop a database application for a IT company that conducts training courses. The project consists of the following five tasks.
1. Design a ER data model for the application. Your ER model should capture as many of the application’s constraints as possible.
2. Translate your ER data model into a relational database schema. Your relational schema should enforce as many of the application’s constraints as possible.
5. For each table R in your database schema that is not in 3NF, find a dependency-preserving BCNF decomposition of R if it exists; if not, find a 3NF decomposition of R.
2. Application
A customer’s course package is classified as either active if there is at least one unused session in the package, partially active if all the sessions in the package have been redeemed but there is at least one redeemed session that could be refunded if it is cancelled, or inactive otherwise. Each customer can have at most one active or partially active package.
For a credit card payment, the company’s cancellation policy will refund 90% of the paid fees for a registered course if the cancellation is made at least 7 days before the day of the registered session; otherwise, there will no refund for a late cancellation. For a redeemed course session, the company’s cancellation policy will credit an extra course session to the customer’s course package if the cancellation is made at least 7 days before the day of the registered session; otherwise, there will no refund for a late cancellation.
Each manager manages zero or more course areas, and each course area is managed by exactly one manager.
Each course offering is managed by the manager of that course area.
3. Application Functionalities
Your application must support the following functionalities, each of which is to be implemented as a SQL or PL/pgSQL routine (i.e., function or procedure). Some of these routines return a JSON value. PostgreSQL provides many useful functions for JSON data type (e.g., row_to_json (https://www.postgresql.org/docs/current/functions-json.html) , json_agg (https://www.postgresql.org/docs/current/functions-aggregate.html)).
Note that the specifications for the routines are incomplete by design; for example, the data types for the input parameters are not specified, and certain parameters (e.g., course offering identifier) are not explicitly defined. For any criterion/process/design issue that is not explicitly stated, you are free to decide on how to address that issue in a reasonable way and justify your decisions in your project report.
5. add_course: This routine is used to add a new course. The inputs to the routine include the following: course title, course description, course area, and duration. The course identifier is generated by the system.
The routine returns a table of records consisting of employee identifier and name.
12. get_available_course_packages: This routine is used to retrieve the course packages that are available for sale. The routine returns a table of records with the following information for each available course package:
13. buy_course_package: This routine is used when a customer requests to purchase a course package. The inputs to the routine include the customer and course package identifiers. If the purchase transaction is valid, the routine will process the purchase with the necessary updates (e.g., payment).
17. register_session: This routine is used when a customer requests to register for a session in a course offering. The inputs to the routine include the following: customer identifier, course offering identifier, session number, and payment method (credit card or redemption from active package). If the registration transaction is valid, this routine will process the registration with the necessary updates (e.g., payment/redemption).
19. update_course_session: This routine is used when a customer requests to change a registered course session to another session. The inputs to the routine include the following: customer identifier, course offering identifier, and new session number. If the update request is valid and there is an available seat in the new session, the routine will process the request with the necessary updates.
20. cancel_registration: This routine is used when a customer requests to cancel a registered course session. The inputs to the routine include the following: customer identifier, and course offering identifier. If the cancellation request is valid, the routine will process the request with the necessary updates.
22. update_room: This routine is used to change the room for a course session. The inputs to the routine include the following: course offering identifier, session number, and identifier of the new room. If the course session has not yet started and the update request is valid, the routine will process the request with the necessary updates. Note that update request should not be performed if the number of registrations for the session exceeds the seating capacity of the new room.
23. remove_session: This routine is used to remove a course session. The inputs to the routine include the following: course offering identifier and session number. If the course session has not yet started and the request is valid, the routine will process the request with the necessary updates. The request must not be performed if there is at least one registration for the session. Note that the resultant seating capacity of the course offering could fall below the course offering’s target number of registrations, which is allowed.
25. pay_salary: This routine is used at the end of the month to pay salaries to employees. The routine inserts the new salary payment records and returns a table of records (sorted in ascending order of employee identifier) with the following information for each employee who is paid for the month: employee identifier, name, status (either part-time or full-time), number of work days for the month, number of work hours for the month, hourly rate, monthly salary, and salary amount paid. For a part-time employees, the values for number of work days for the month and monthly salary should be null. For a full-time employees, the values for number of work hours for the month and hourly rate should be null.
package; this registration fees is given by the price of the course package divided by the number of sessions included in the course package (rounded down to the nearest dollar). There must be one output record for each manager in the company and the output is to be sorted by ascending order of manager name.
4. Project Deadlines & Deliverables
There are two deadlines for this project.
Each team is to upload a pdf file named teamNN.pdf, where NN is the team number, to the LumiNUS file folder named ER_model_submissions.
The submitted pdf file must be at most 4 pages and consists of the following contents:
Project team number & names of team members (on the first page).
Justification for any non-trivial design decisions made.
List down 5 of the application’s constraints that are not captured by the proposed ER data model.
4.2. Project Report
The project report (up to a maximum of 20 pages in pdf format with at least 10-point font size) should include the following contents:
Names and students numbers of all team members and project team number (on the first page).
A listing of the project responsibilities of each team member.
The ER data model for your application.
Provide justifications for any non-trivial design decisions in your ER model.
List down 5 of the application’s constraints that are not captured by your ER model.
For your relational database schema,
Provide justifications for any non-trivial design decisions in your relational database schema.
List down up to 5 of the application’s constraints that are not enforced by your relational schema (i.e., the constraints that are to be enforced using triggers).
A description of the three most interesting triggers implemented for the application. For each of these triggers,
Provide the name of the trigger.
Explain the usage of the trigger.
Justify the design of the trigger implementation.
For each table R in your database that is not in 3NF, find a dependency-preserving BCNF decomposition of R if it exists; if not, find a 3NF decomposition of R.
A summary of any difficulties encountered and lessons learned from the project.
Submit your project deliverables by creating a directory named teamNN with the following four files, where NN is your team number:
report.pdf: project report in pdf format schema.sql: SQL commands to create your application’s database schema data.sql: SQL commands to load data into your application’s database proc.sql: SQL or PL/pgSQL routines of your implementation
It is important that your submission files are named as indicated above and that you have tested your code using the following command before submission:
$ cat schema.sql data.sql proc.sql | psql
Compress the directory into a zip file named teamNN.zip as follows:
$ zip -r teamNN teamNN
