Project
The project consists of one large assignment, which is described below. You are expected to work on the project assignment throughout the course. Each time a module is completed, the tasks performed during that module can be completed also on the project.
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 can handle some of the transactions.
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 apply by submitting contact details, which instrument they want to learn, and their present skill. If there is room, the student is offered a place, which can be accepted or rejected. 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, age, address, contact details and contact details for parents must be stored for each students. It must also be possible to see which students are siblings, since there is a discount for siblings.
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, age, address and contact details must be stored for each instructor.
Student Enrollment
When someone wants to attend the school, they apply by submitting personal data (like name and contact details), which instrument (or ensemble) they apply for, and how skilled they are at playing that instrument. If there is a place available in the desired kind of lesson, the student is offered to participate.
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 and group lessons. 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 ten points, and is divided into a mandatory part (5p) and a higher grade part (5p). 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 is not accepted and does not give any points. A task is passed when both oral and written reports are passed. Reporting is explained in detail below.
3. 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 two students. That is, your solution is allowed to be identical to the solution of at most one other student. 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.
3.1. Task 1, Conceptual Model
How To Prepare
Before solving this task you have to understand the conceptual model lecture (only recorded, not given live). Note that the conceptual model lecture page contains several videos, which together lasts about three hours.
When To Solve
Your are recommended to start working on this project task as soon as possible at course start, but remember to first watch the recorded lecture on the conceptual model. The seminar where this task is discussed is held Nov 12.
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.
- In the Method chapter of your report, explain the procedure you followed to create the conceptual model. You shall mention all steps that are covered in the videos on conceptual modeling. If you did not perform a particular step, explain why the result was better (or at least not worse) without that step. Do not explain the result of each step, only explain the steps themselves.
- In the Result chapter of your report, show and briefly explain your conceptual model.
- In the Discussion chapter of your report, evaluate your conceptual model. Suggested assessment criteria are listed below, you do not have to cover them all. These same criteria will also be used to grade your project report.
- Are naming conventions followed? Are all names sufficiently explaining?
- Is the notation (UML or crow foot) correctly followed?
- Is there a reasonable number of entities? Is some important entity missing?
- Are there attributes for all data that shall be stored? Is cardinality specified for all attributes?
- Are all relevant relations specified? Do all relations have cardinality at both ends and name at least at one end?
- Are all business rules and constraints that are not visible in the diagram explained in plain text?
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. Discuss advantages of using inheritance and advantages of not using inheritance. This discussion shall be placed in the Discussion chapter of the report.
3.2. Task 2, Logical and Physical Model
How To Prepare
Before solving this task you have to understand the lectures on normalisation (given live and recorded) and on logical and physical models (only recorded, not given live).
When To Solve
Your are recommended to start working on this project task after having solved task 1, attended the lecture on normalisation which is given Nov 15, and watched the recorded lecture on logical and physical models. The seminar where this task is discussed is held Nov 26.
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, 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. 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 listed below, you do not have to cover them all. These same criteria will also be used to grade your project report.
- Are naming conventions followed? Are all names sufficiently explaining?
- Is the crow foot notation correctly followed?
- Is the model in 3NF? If not, is there a good reason why not?
- Are all tables relevant? Is some table missing?
- Are there columns for all data that shall be stored? Are all relevant column constraints and foreign key constraints specified? Can all column types be motivated?
- Can the choice of primary keys be motivated?
- Are all relevant relations correctly specified?
- Is it possible to perform all tasks listed in section one, Project Description, above?
- Are all business rules and constraints that are not visible in the diagram explained in plain text?
Higher Grade Part
This higher grade part can not be solved without first having solved the higher grade part of task one. 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.
- For the relation where inheritance was used in the conceptual model, compare how it is implemented in the model created here, when using inheritance and when not using inheritance. You will first have to read about implementing inheritance in the logical model (for example in the text book), since that is not covered in the lectures. Is there any difference in the logical model depending on whether inheritance was used in the conceptual model? Why/why not? Discuss advantages of using inheritance and advantages of not using inheritance, concerning the logical model. This discussion shall be placed in the Discussion chapter of the report.
3. 3. Task 3, SQL
How To Prepare
Before solving this task you have to understand the lecture on SQL (given live and recorded).
When To Solve
Your are recommended to start working on this project task after having solved task 2, and attended the lecture on SQL, which is given Nov 11. The seminar where this task is discussed is held Dec 7.
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 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 are also 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.
The following queries will be executed manually, to generate analysis reports.
- Show the number of lessons given per month during a specified year. 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). This query is expected to be performed a few times per week.
- The same as above, but retrieve the average number of lessons per month during the entire year, instead of the total for each month.
- List 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.
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.
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 testing the queries, not explain each test in detail.
- 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, for each query motivate why you did or did not create a view or a materialized view.
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. 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 will lead to duplicated data, with the same price appearing in all lessons of the same type. We migth even want to simplify the database one more step, and 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 is only required to contain lessons, prices and students. You are 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.
3.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).
When To Solve
Your are recommended to start working on this project task after having solved task 3, attended the lecture on transactions which is given Nov 29, and watched the recorded lecture on Database Applications. The seminar where this task is discussed is held Dec 17.
Intended Learning Outcomes
- Describe how a program can access a database and write such a program.
Mandatory Part
The assignment is to develop part of Soundgood's web site. You are however only required to develop a very limited set of functionalities, namely what is used when instruments are rented. Also, since focus here is on database access, you are not required to develop the web interface, but a command line user interface is sufficient. You are 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 are 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, and shall have the functionality listed below. 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 are also allowed to change the database you created in task two if needed.
- 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, 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. - In the Discussion chapter of your report, evaluate your program. Suggested assessment criteria are listed below, you do not have to cover them all. These same criteria will also be used to grade your project report.
- Are naming conventions followed? Are all names sufficiently explaining?
- Is transaction management correct? Are there ACID transactions, which are committed or rolled back correctly?
- Is the program working as expected and does it meet all functional requirements listed in this task?
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.
- The program must 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. The controller shall not handle any logic and the model shall not 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.
- There must not be any duplicated code.
4. 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.
- At seminars. The purpose of seminars is to give feedback to solutions, but you can also ask about things that are unclear.
- In Piazza
Links to an external site.. Do not 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.
5. How to Report
Each solved task shall be reported both orally and in writing. A task is passed only when both reports 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 two students. That is, your solution is allowed to be identical to the solution of at most one other student. 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 Oral Report
The oral report is given individually, over zoom. Only tasks included in the written report may be presented. You are allowed (and encouraged to) participate and report all tasks covered in your written report, even if you haven't solved all project tasks, since you will get points for the tasks you report even if the entire project isn't completed. A reporting session lasts two hours, and is attended by four students and one teacher. Each of the four students explain all tasks they've solved, and also listens to the other three student's reports. This means each participant gets about 30 minutes to explain solutions to all tasks, and also answer questions about the solutions. For this to work, the presentation must be prepared in advance.
The oral report takes place January 12. You shall book a place in a two-hour reporting session between 8-12 and 13-17, by assigning yourself to a group at the page Project Report Groups. Book a time only after submitting the written project report. The table below will be completed with zoom links for each report session.
5.2 Written Report
The written report must follow this report template, iv1351-report-template.pdf Download 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 must follow the template. The report shall be submitted under the Project assignment. It is individual, and will be plagiarism checked. You are not allowed to submit the same report as another student.
If you have not completed all tasks, you are still allowed (and encouraged to) submit a report covering those tasks you have completed, since you will get points for the tasks you report even if the entire project isn't completed. Remember that this is the last opportunity to get points for higher grade tasks.
6. Late or Failed Report
If you do not complete all tasks before deadline, or if your report is not accepted, it will be possible to report later. However, it is only possible to report mandatory parts of tasks after deadline; you will not be able to get points for a higher grade part after deadline.