One single database application will be created during the project. The project tasks are to be presented at five different seminars. The first three project tasks, and the first three seminars, are mandatory. The fourth project task, and the fourth seminar, is optional, and serves only to improve the grade. The fifth seminar, in January, is an opportunity to re-submit seminars you did not pass or to improve your score for one or more seminars. You can get scores for all tasks, both mandatory and optional, also at the fifth seminar.

You are encouraged to collaborate and discuss with as many other students as you wish when doing the project, group discussions always give a better result than individual work. You are however allowed to create the actual solution in groups of max three students. That is, your solution is allowed to be identical to the solution of at most two other students. You are also allowed to work alone, but that is not recommended. The written report is individual, and may not be written together with any other student.

1. Project Description, the Soundgood Music School

The purpose is to facilitate information handling and business transactions for the Soundgood music school company, by developing a database which handles all the school's data and also an application that handles the operations specified in sections 1.1-1.3.

1.1 Business Overview

Soundgood sells music lessons to students who want to learn to play an instrument. When someone wants to attend the school, they submit contact details, which instrument they want to learn, and their present skill. If there is room, the student is offered a place. There is no concept like 'course' or sets of lessons. Instead, students continue to take lessons as long as they wish. Students pay per lesson and instructors are payed per given lesson.

1.2 Detailed Descriptions

Lesson

There are individual lessons and group lessons. A group lesson has a specified number of places (which may vary), and is not given unless a minimum number of students enroll. A lesson is given for a particular instrument and a particular level. There are three levels, beginner, intermediate and advanced. Besides lessons for a particular instrument, there are also ensembles, where students playing different instruments participate at the same lesson. Ensembles have a specific target genre (e.g., punk rock, gospel band), and there is a maximum and minimum number of students also for ensembles.

Group lessons and ensembles are given at scheduled time slots. Individual lessons do not have a fixed schedule, but are rather to be seen as appointments, like for example an appointment with a dentist. Administrative staff must be able to make bookings, it must therefore be possible to understand which instructor is available when, and for which instruments.

There is no concept like 'course' or sets of lessons, a student who has been offered a place, and accepted, continue to take lessons as long as desired, and can either book one lesson at the time or book many lessons during a longer time period.

Student

A student can take any number of lessons, for any number of instruments. Person number, name, address and contact details must be stored for each student. It must also be possible to store contact details for a contact person (e.g., parent) for each student. Furthermore, it must be possible to see which students are siblings, since there is a discount for siblings. It's not sufficient to show just whether a student has siblings, it must be possible to see who's a sibling of who.

Instructor

An instructor can be assigned to group lessons and ensembles, and can also be available to give individual lessons during specified time periods. An instructor can teach a specified set of instruments, and may also be able to teach ensembles. Person number, name, address and contact details must be stored for each instructor.

Student Payment

Students are charged monthly for all lessons taken during the previous month. Currently, there is one price for beginner and intermediate levels, and another price for the advanced level. Also, there are different prices for individual lessons, group lessons and ensembles. There is also a discount for siblings, if two or more siblings have taken lessons during the same month, they all get a certain percentage discount. Soundgood wants to have a high level of flexibility to change not just prices, but also pricing scheme. They might, for example, not always have the same price for beginner and intermediate lessons.

Instructor Payment

There are no instructors with fixed monthly salaries, instead they are payed monthly for all lessons given during the previous month. Instructor payments depend on the same things as student fees (see above), namely level of lesson and whether a given lesson was a group or individual lesson. Instructor payments are not affected by sibling discounts.

Renting Instruments

Soundgood offers students the ability to rent instruments to be delivered at their home. There is a wide selection of instruments, wind, string etc., supporting different brands and in different quantities in stock at the soundgood music school. Each student can rent up to two specific instruments at any given period, the renting happens with a lease up to 12 month period. Students can list and search current instruments and rent them if they don't exceed their two-instrument quota. Instruments are rented per month. The fee is payed the same way lessons are payed, each month students are charged for the instruments that where rented the previous month.

1.3 Requirements on the Soundgood Music School Application

The database must store all data described above, in sections 1.1 and 1.2, but no other data. There will also be an application providing a user interface which can be used by administrative staff to manage student enrollments, instrument rentals, bookings and payments. In addition, the database will also be used to retrieve reports and statistics of all possible kinds, but a user interface is not required for that purpose. It will instead be done by manually querying the database.

The database will not be used for any financial purpose like bookkeeping, taxes or bank contacts. What is written above regarding student fees and instructor payments is only about calculating what sum shall be payed to or by who, and sending that information to Soundgood's financial system.

2. Grading

There are four tasks, which are described below. Each task gives max 10p, and is divided into two parts giving 5p each. The first three tasks have a mandatory part (5p) and a higher grade part (5p). The fourth task has two higher grade parts worth 5p each. To pass the project all mandatory tasks must be passed. The higher grade tasks are optional, and contribute to the final grade as specified in the Course Layout. No partial score is given, either a part of a task is accepted and gives five points, or it isn't accepted and does not give any points. A task is passed when both seminar and written report are passed. Reporting is explained in detail below.

3. How to Get Help

Questions about the project can be asked these ways:

At tutorials, tutorials exist only to provide help with project tasks. You are welcome to ask about anything related to project work at tutorials.
In Piazza. Don't send direct emails to teachers, instead post questions in Piazza since then all students can be helped by the answers. Mind that posts can be anonymous to peer students in Piazza.

4. How to Report

Each solved task shall be reported both in writing and at a seminar. A task is passed only when both written report and seminar have been accepted. You are encouraged to collaborate and discuss with as many other students as you wish when doing the project, group discussions always give a better result than individual work. You are however allowed to create the actual solution in groups of max three students. That is, your solution is allowed to be identical to the solution of at most two other students. You are also allowed to work alone, but that is not recommended. The written report is individual, and may not be written together with any other student.

4.1 Written Report

The written report must follow this report template, iv1351-report-template.pdf. Note that the template is a pdf, a similar document must be created. Layout changes are allowed, it is only the content that's important. The template pdf is created using latex, if you want to try that you can use the following tex file, iv1351-report-template.tex, which was used to create the template pdf. The report shall be submitted under the corresponding seminar, the report for task 1 is submitted under seminar 1, etc. It's individual, and will be plagiarism checked. You're not allowed to submit the same report as another student.

4.2 Seminar

The seminar schedule is found on the Seminar Schedule page, and you can see which group you belong to on the seminar groups page. To pass a seminar, you must have solved the specified tasks and participate in the discussion during the seminar. It's not required that the solution is correct in all parts or that all statements made at the seminar are correct. The seminars are divided into two parts, one for peer assessment and one for presentation.

4.2.1 First Seminar Part, Peer Assessment

You'll be divided into groups of three, each group will assess the solutions of members of one other group. The results of the assessments shall be uploaded to Canvas and emailed to the assessed student. You're not required to bring a printout of your report.

4.2.2 Second Seminar Part, Presentation

One randomly chosen student from each group will give a five-minute presentation of their own solution to the entire seminar. Prepare this presentation before the seminar.
One randomly chosen student from the assessing group will give comments on this solution and there will be a brief discussion. Five minutes are allocated for comments and discussion together.

5. Tasks

You are encouraged to collaborate and discuss with as many other students as you wish when doing the project, group discussions always give a better result than individual work. You are however allowed to create the actual solution in groups of max three students. That is, your solution is allowed to be identical to the solution of at most two other students. You are also allowed to work alone, but that is not recommended. The written report is individual, and may not be written together with any other student.

5.1. Task 1, Conceptual Model

How To Prepare

When To Solve

You're recommended to start working on this task as soon as possible at course start, but remember to first do the preparations mentioned above. The deadline for submitting the report is found on the seminar 1 assignment page.

Intended Learning Outcomes

Model needs for information based on an organizational description and convert the model to a functioning database.

Mandatory Part

Create a conceptual model for the Soundgood music school database. The conceptual model must cover the entire description of the Soundgood music school company in section one above. The diagram must be made either in UML or in one of all possible crow foot notations (for example IE notation). Below follows guidelines for what shall be written in the report.

Higher Grade Part

Both the following two requirements must be met to get the higher grade points. You can not get half of the points by meeting just one requirement.

The report must include a relevant and extensive Discussion chapter about the mandatory part of the task.
Use inheritance in at least one place in the conceptual model. You will have to read about inheritance first (for example in the text book), since it is not much covered in the lectures. Also show how the same relation could be modeled without using inheritance. The Result chapter of the report must include one conceptual model diagram with inheritance, using the inheritance symbol, and one diagram that models the same thing without inheritance. Discuss advantages of using inheritance and advantages of not using inheritance. This discussion shall be placed in the Discussion chapter of the report.

5.2. Task 2, Logical and Physical Model

How To Prepare

Understand the lectures on normalisation (given live and recorded) and on logical and physical models (only recorded, not given live). You can start working on the task before the normalisation lecture, but you have to understand that lecture before completing the task.
Read the document tips-and-tricks-task2.pdf.

When To Solve

You're recommended to start working on this task as soon as possible after seminar 1, but remember to first do the preparations mentioned above. The deadline for submitting the report is found on the seminar 2 assignment page.

Intended Learning Outcomes

Model needs for information based on an organizational description and convert the model to a functioning database.
Use relational databases and query languages.

Mandatory Part

Translate the conceptual model from task one into the same kind of model that was created in the lecture on logical and physical models, that is a logical model with enough physical aspects to enable creating a database. You are allowed to change the conceptual model if you discover flaws, but only change if it is necessary. The model that is created must cover the entire description of the Soundgood music school company in section one above. The diagram must be made in a crow foot notation (for example IE notation). Also create a database based on the model, the database must be exactly as described in the model. If you discover flaws when creating the database, and want to change it, also the model must be changed. You are advised to also look at tasks three and four already now, to get an understanding of how your database will be used. Below follows guidelines for what shall be written in the report.

In the Method chapter of your report, mention diagram editor(s) and other tool(s) you used, and explain the procedure you followed to create the model. You do not have to mention all eleven steps covered in the videos on on logical and physical models, but it must be clear how you proceeded. Do not explain the result of each step of your procedure, only explain the steps themselves.
In the Result chapter of your report, show and briefly explain your model. Also include a link to a git repository where you have stored SQL scripts that create your database. There shall be one script that creates the database, and another script that inserts data. HINT: You might want to use an online generator to create the data, for example https://generatedata.com/, instead of creating the data manually. You're also not required to write the script that creates the database manually. If you create the database using a GUI tool, you may generate the script using for example pg_dump (note that pg_dump is a Postgres tool, but there are similar tools for MySQL). You may explain the SQL in the scripts if you wish, and think it clarifies the model, but it is not required to write such an explanation.
In the Discussion chapter of your report, evaluate your model. Suggested assessment criteria are found in assessment-criteria-seminar2.pdf, you do not have to cover them all. These same criteria will also be used to grade your project report.

Higher Grade Part

All three requirements below must be met to get the higher grade points. You can not get part of the points by meeting just some of the requirements.

The report must include a relevant and extensive Discussion chapter about the mandatory part of the task.
Make sure that the models created in this task contain all data required by Soundgood, and don't require an application using the database to manage any data at all. It could for example be tempting to create a database that doesn't store the number 2, which is mentioned in the text each student can rent up to two specific instruments, and instead hardcode the number 2 into the application calling the database. Carefully read the description of the Soundgood music school in section one above, and make sure your models include all data mentioned in that text. You don't have to consider any data that's not clearly mentioned in section one. Discuss advantages and disadvantages of storing all data in the database, as is done here, instead of having some data in the application. This discussion shall be placed in the Discussion chapter of the report.
This task is to handle the fact that prices can change. Say for example that a student takes a certain kind of lesson, and that the cost of that lesson changes before the student has payed. In this situation, it must be possible to find the cost of the lesson when the student took it, not just the current cost, since the student shall pay the cost that was used when the lesson was taken. You have to solve this task by creating a model which allows adding a new price, when a price changes, instead of updating the existing price. This also means the model must make it possible to tell which of all stored prices for a certain lesson is currently valid. Discuss advantages and disadvantages of keeping multiple versions of data, as is done here. This discussion shall be placed in the Discussion chapter of the report. HINT: A solution to this problem often includes storing the time a certain value is inserted into the database. You're however not required to do that, there are also other possible solutions.

5.3. Task 3, SQL

How To Prepare

Understand the lecture on SQL (given live and recorded).
Read the document tips-and-tricks-task3.pdf.

When To Solve

You're recommended to start working on this task as soon as possible after seminar 2, but remember to first do the preparations mentioned above. The deadline for submitting the report is found on the seminar 3 assignment page.

Intended Learning Outcomes

Use relational databases and query languages.
Describe and explain basic concepts, principles and theories in the area of data/databases/data storage and in information administration and database design

Mandatory Part

The goal here is to create OLAP, Online Analytical Processing, queries and views. Such queries serve to analyze the business and to create reports. You're also required to analyze the efficiency of one of the queries with EXPLAIN ANALYZE. Even though that's not standard SQL, it's available in both PostgreSQL and MySQL. You also have to make sure the database contains sufficient data to check that all queries work as intended. If needed, update the script that inserts data, created in task two. You're allowed to change the database you created in task two if needed. The queries that shall be created are explained below, only OLAP queries will be created here. The OLTP (Online Transaction Processing) queries used by the business itself, which in the case of Soundgood is to rent out instruments, register taken and given lessons, etc, will be created in task 4, together with the program executing them.

HINT: You might want to use an online generator to create the database content, for example https://generatedata.com/, instead of creating the data manually.

The following queries will be executed manually, to generate analysis reports.

Show the number of lessons given per month during a specified year. This query is expected to be performed a few times per week. It shall be possible to retrieve the total number of lessons per month (just one number per month) and the specific number of individual lessons, group lessons and ensembles (three numbers per month). It's not required that all four numbers (total plus one per lesson type) for a particular month are on the same row; you're allowed to have one row for each number as long as it's clear to which month each number belongs. However, it's most likely easier to understand the result if you do place all numbers for a particular month on the same row, and it's an interesting exercise, therefore you're encouraged to try. Table 1 below is an example result of such a query, illustrating the expected output.

**Table 1.** Expected output for query 1. This example is only meant to illustrate the expected rows and columns. It's perfectly fine to change text formatting, and also to change the values.
Month	Total	Individual	Group	Ensemble
Oct	2	1	0	1
Nov	3	0	2	1
Dec	10	4	4	2

Show how many students there are with no sibling, with one sibling, with two siblings, etc. This query is expected to be performed a few times per week. The database must contain students with no sibling, one sibling and two siblings, but doesn't have to contain students with more than two siblings. Note that it's not allowed to solve this by just adding a column with sibling count (maybe called no_of_siblings or something similar) to the student table. Such a solution would be almost impossible to maintain since it doesn't tell who's a sibling of who. If a student quits, there wont be any way to update the sibling count of that student's siblings. Table 2 below is an example result of such a query, illustrating the expected output.

**Table 2.** Expected output for query 2. This example is only meant to illustrate the expected rows and columns. It's perfectly fine to change text formatting, and also to change the values in the *No of Students* column. The values in the *No of Siblings* column must however be as specified: 0, 1, 2.
No of Siblings	No of Students
0	3
1	2
2	5

List ids and names of all instructors who has given more than a specific number of lessons during the current month. Sum all lessons, independent of type, and sort the result by the number of given lessons. This query will be used to find instructors risking to work too much, and will be executed daily. Table 3 below is an example result of such a query, illustrating the expected output.

**Table 3.** Expected output for query 3. This example is only meant to illustrate the expected rows and columns. It's perfectly fine to change text formatting, and also to change the values.
Instructor Id	First Name	Last Name	No of Lessons
13	Albus	Dumbledore	5
15	Pomona	Sprout	5
2	Gilderoy	Lockhart	4

The following queries will be performed programmatically, and the results will be displayed on Soundgood's web page. You only have to create the queries, not the web page.

List all ensembles held during the next week, sorted by music genre and weekday. For each ensemble tell whether it's full booked, has 1-2 seats left or has more seats left. HINT: you might want to use a CASE statement in your query to produce the desired output. Table 4 below is an example result of such a query, illustrating the expected output.

**Table 4.** Expected output for query 4. This example is only meant to illustrate the expected rows and columns. It's perfectly fine to change text formatting, and also to change the values.
Day	Genre	No of Free Seats
Tue	Gospel	No Seats
Wed	Punk	1 or 2 Seats
Wed	Rock	No Seats
Fri	Rock	Many Seats

Below is specified what shall be written in the report.

In the Method chapter of your report, mention which DBMS you use, which tool is used to develop SQL queries, and how you have verified that your SQL queries work as intended. You shall just tell which method you used for verifying that the queries work as intended, not explain in detail how you verified the functionality of each query..
In the Result chapter of your report, include a link to a git repository where you have stored a script with all queries. Also explain each query and show that all queries work as intended by including the output of each query. The git repository must also contain the scripts that create the database and insert data. It shall be possible to test your solution by executing first the script that creates the database, then the script that inserts data, and finally any of the queries created in this task.
In the Discussion chapter of your report, evaluate your queries. Suggested assessment criteria are found in assessment-criteria-seminar3.pdf. You don't have to cover them all, but you must at least cover the bullet on EXPLAIN, which means you have to analyze at least one of your queries. The assessment criteria in this document will also be used to grade your project report.

Higher Grade Part

Both the following two requirements must be met to get the higher grade points. You can not get half of the points by meeting just one requirement.

The report must include a relevant and extensive Discussion chapter about the mandatory part of the task.
This task concerns historical data and denormalization. Historical data is normally a strong candidate for denormalization, since it will not be updated. That makes write performance uninteresting, and also strongly reduces the risk of data anomalies. You will first have to read about denormalization (for example in the text book), since that is not much covered in the lectures.
The task is that, for marketing purposes, the Soundgood music school wants to be able to see which lessons each student has taken, and at which cost, since they first joined the school. This means you are required to keep records of all given lessons, including participants and price for each lesson. One way to do this would be to just save a copy of the entire database each day, or each time a price is changed. This is however unnecessarily complicated, since it will be slow to generate reports from such a database, and since you would have to add some kind of time interval for the prices, instead of just keeping the current price. A better way to solve the problem is to create a denormalized historical database. One example of an appropriate denormalization is to store prices of lessons in a column in the lessons table, instead of keeping prices in a separate table, even though this will lead to duplicated data, with the same price appearing in all lessons of the same type. Another possible denormalization is to merge individual, group and ensemble lessons into one single lesson table.
Create a historical database like the one described above, and also create SQL statements for copying data from your present database to the historical database. The historical database shall contain the following data: lesson type (group, individual or ensemble), genre (empty if it's not en ensemble), instrument (empty if it's an ensemble), lesson price, student name, student email. You're not required to write a program that automates moving data to the historical database, it's sufficient to create SQL statements that are executed manually. Discuss advantages and disadvantages of using denormalization. This discussion shall be placed in the Discussion chapter of the report.

5.4. Task 4, Programmatic Access

How To Prepare

Before solving this task you have to understand the lectures on transactions (given live and recorded) and on Database Applications (only recorded, not given live).
Read the document tips-and-tricks-task4.pdf

When To Solve

You're recommended to start working on this task as soon as possible after seminar 3, but remember to first do the preparations mentioned above. The deadline for submitting the report is found on the seminar 4 assignment page.

Intended Learning Outcomes

Describe how a program can access a database and write such a program.

Mandatory Part

This project task has no mandatory part; you don't have to solve it unless you want points to improve your grade.

Higher Grade Part, Task A (gives 5p)

The assignment is to develop part of Soundgood's web site. You're however only required to develop a very limited set of functionalities, namely what's used when instruments are rented. Also, since focus here is on database access, you're not required to develop the web interface, but a command line user interface is sufficient. You're allowed to reuse as much code as you wish from all classes in the view layer of the JDBC bank example at the page Database Applications, but all code included in your program is your responsibility. You're not allowed to blame any deficiency in your application on the bank program. Your program shall be stored in a public git repository, for example on GitHub. The program is required to handle ACID transactions properly, which means autocommit must be turned off, instead the the program must call commit and rollback as required. Handling transactions properly also means that SELECT FOR UPDATE must be used when required. Finally, you have to make sure the database contains sufficient data to check that all queries work as intended. If needed, update the script that inserts data, created in task two. You're allowed to change the database you created in task two if needed.

The program must have the following functionality:

List instruments It shall be possible to list all instruments of a certain kind (guitar, saxophone, etc) that are available to rent. Instruments which are already rented shall not be included in the listing. The listing shall show brand and price for each listed instrument.
Rent instrument It shall be possible to specify which student is renting the instrument, and which instrument is being rented. Since different instruments of the same kind might have different prices, it must be possible to specify exactly which particular instrument to rent, not just any instrument of the desired kind. Remember that a student is not allowed to rent more than two instruments at the same time, your program must check that this limit is not exceeded.
Terminate rental It shall be possible to terminate an ongoing rental. You are free to decide how the user specifies which rental to terminate. You are not allowed to delete all information about a terminated rental from the database. Instead, the database must still contain all information about the rental, but also show that the rental has been terminated.

Below follows guidelines for what shall be written in the report.

In the Method chapter of your report, mention which IDE(s) and other tool(s) you used and explain how you proceeded and reasoned when writing the program. Do not explain the result of each step you took, only explain the steps themselves.
In the Result chapter of your report, briefly explain the program and in particular explain ACID transaction handling. Include links to your git repository, and make sure the repository is public. Also include a printout of a sample run. The git repository must also contain the scripts that create the database and insert data. It shall be possible to test your solution by executing first the script that creates the database, then the script that inserts data, and finally execute your program.
The Discussion chapter of your report must include a relevant and extensive evaluation of your program. Suggested assessment criteria are found in assessment-criteria-seminar4.pdf, you do not have to cover them all. These same criteria will also be used to grade your project report.

Higher Grade Part, Task B (gives 10p, since solving this task means also task A is solved)

This task is identical to task A, but the program being developed must also be well designed and have a properly layered architecture. The required level of design and architecture is that of the JDBC bank example at the page Database Applications. The following must be showed in the discussion chapter of the report.

- The code must be easy to understand. This is of course subjective, what is required is to show that you have tried sufficiently to make the code easy to understand.
- The MVC and Layer patterns must be used correctly. There must be enough layers, packages and classes. Neither controller nor model are allowed contain any code related to the view (input or output). Also, the integration layer (the DAO) is only allowed to contain methods that create, read, update or delete rows in the database. There must not be any logic at all in the integration layer. As an example, this means you're not allowed to have a method in a DAO that checks if an instrument is available to rent, then checks if the student is allowed to rent, and finally creates a rental. Instead, to handle this scenario, the controller must first call a method in a DAO that reads rentals, then the controller checks if the instrument is available to rent, then calls another DAO method that reads student data, then checks if the student is allowed to rent, and then finally calls a DAO method that creates a rental.
- There must not be any duplicated code.