编程代写｜CMPUT291 – Fall 2022 Mini Project I (group project)

这是一篇来自加拿大的关于需要运用SQL语言完成任务目标的编程代写，并且还可以自由地用Python、Java、C、C++、Perl或任何其他适合该任务的语言编写代码

Introduction

The goal of this assignment is twofolds: (1) to teach the use of SQL in a host programming language, and (2) to demonstrate some of the functionalities that result from combining SQL with a host programming language. Your job in this project is to build a system that keeps the enterprise data in a database and to provide services to users. You will be storing data in a SQLite database and will be writing code in Python (or similarly Java/JDBC, C, etc.) to access it. Your code will implement a simple command line interface. You are free to implement a GUI interface instead but there will be no support nor bonus for doing that. You are also free to write your code in Python,Java, C, C++, Perl or any other language that is suited for the task. If you decide to use any language other than Python, you should let the instructor know in advance.

Your project will be evaluated on the basis of 84% of the mark for implementing the functionalities listed in this specification; this component will be assessed in a demo session. Another 12% of the mark will be assigned for both the documentation and the quality of your source code. 4% of the mark is assigned for the quality of your group coordination and the project break-down between partners.

Group work policy

You will be doing this project with one or two other partners from the 291 class. Register your group at the group registration page. It is assumed that all group members contribute somewhat equally to the project, hence they would receive the same mark. In case of difficulties within a group and when a partner is not lifting his/her weight, make sure to document all your contributions. If there is abreak-up, each group member will get credit only for his/her portion of the work completed (losing the mark for any work either not completed or completed by the partners).

Database Specification

You are given the following relational schema.

These tables are derived from the specification of Assignment 1 and are identical to those in Assignment 2 except the tables users and artists, which have now a field for password (referred to as pwd). The SQL commands to create the tables of the system are given here (right click to save as a file). Use the given schema in your project and do not change any table/column names.

Login Screen

The first screen of your system should provide options for both users and artists to login. Both class of users should be able to login using a valid id (respectively denoted as uid and aid for users and artists) and a password, denoted with pwd. After a successful login, the system should detect whether it is a user or an artist and provide the proper menus as discussed next. If the entered id is a valid id in both users and artists tables, then the user will be asked if they want to login as a user or as an artist.

Unregistered users should be able to sign up by providing a unique uid and additionally a name, and a password. Passwords are not encrypted in this project. After a successful signup, users should be able to perform the subsequent user operations (possibly chosen from a menu) as discussed next.

Artists cannot sign up through your system (and you assume they are already in the database).

Users should be able to logout, which directs them to the first screen of the system. There must be also an option to exit the program directly. When exiting the program, any session that is still open should be close automatically.

System Functionalities

After a successful login, users should be able to perform all of the following tasks.1. Start a session. The user should be able to start a session. For each session, a session number unique for the user should be assigned by your system, the session start date should be set to the current date and the session end date should be set to null.

Song actions: When a song is selected, the user can perform any of these actions: (1) listen to it,(2) see more information about it, or (3) add it to a playlist. More information for a song is the names of artists who performed it in addition to id, title and duration of the song as well as the names of playlists the song is in (if any). When a song is selected for listening, a listening event is recorded within the current session of the user (if a session has already started for the user) or within a new session (if not). When starting a new session, follow the steps given for starting a session. A listening event is recorded by either inserting a row to table listen or increasing the listen count in this table by 1. When adding a song to a playlist, the song can be added to an existing playlist owned by the user (if any) or to a new playlist. When it is added to a new playlist, a new playlist should be created with a unique id (created by your system) and the uid set to the id of the user and a title should be obtained from input.

Artists should be able to perform the following actions:1. Add a song. The artists should be able to add a song by providing a title and a duration. The system should check if the artists already has a song with the same title and duration. If not,the song should be added with a unique id (assigned by your system) and any additional artist who may have performed the song with their ids obtained from input.

String matching. Except the password which is case-sensitive, all other string matches (include id,name, title, etc.) are case-insensitive. This means the keyword “drake” will match Drake, DRAKE, and DrAke, and you cannot make any assumption on the case of the strings in the database. The database can have strings in uppercase, lowercase or any mixed format.

Error checking. Every good programmer should do some basic error checking to make sure the data entered is correct. We cannot say how much error checking you should or should not do, or detail out all possible checkings. However, we can say that we won’t be trying to break down your system but your system also should not break down when the user makes a mistake.

Groups of size 3 must counter SQL injection attacks and make the password non-visible at the time of typing.

Testing

At development time, you will be testing your programs with your own data sets but conforming to the project specification.

At demo time, you will be given a database file name that has our test data (e.g., prj-test.db), and you will be passing the file name to your application as a command line argument. Don’t hard-code the database name in your application since the database name is not known in advance, and you don’t want to change your code at demo time (see next). The database will include the tables given above (created using these SQL statements) and with our own test data. Your application will be tested under a TA account.

Every group will book a time slot convenient to all group members to demo their projects. At demo time, all group members must be present. Once we create our tables and populate them with our data, you will be asked to start your application and perform various tasks, showing how your application is handling each task. A mark will be assigned to your demo immediately after the testing.Here are some important details about our testing process and your choices:

Otherwise, you cannot demo your project on any machine other than the lab machines.

Instructions for Submissions

Your submission includes (1) the application source code, (2) README.txt, and (3) your design document Report.pdf.

The file README.txt is a text file that lists the names and ccids of all group members. This file must also include the names of anyone you collaborated with (as much as it is allowed within the course policy) or a line saying that you did not collaborate with anyone else. This is also the place to acknowledge the use of any source of information besides the course textbook and/or class notes.

Your design document must be type-written and is saved as a PDF and be included in your submission. Your design document cannot exceed 4 pages.

The design document should include (a) a general overview of your system with a small user guide,(b) a detailed design of your software with a focus on the components required to deliver the major functions of your application, (c) your testing strategy, and (d) your group work break-down strategy.

The general overview of the system gives a high level introduction and may include a diagram showing the flow of data between different components; this can be useful for both users and developers of your application. The detailed design of your software should describe theresponsibility and interface of each primary function or class (not secondary utility functions/classes) and the structure and relationships among them. Depending on the programming language being used, you may have methods, functions or classes. The testing strategy discusses your general strategy for testing, with the scenarios being tested, the coverage of your test cases and (if applicable) some statistics on the number of bugs found and the nature of those bugs. The group work strategy must list the break-down of the work items among partners, both the time spent (an estimate) and the progress made by each partner, and your method of coordination to keep the project on track. The design document should also include a documentation of any decision you have made which is not in the project specification or any coding you have done beyond or different from what is required.

Your design document will not include the source code. However your source code will be inspected for source code quality (whether the code is easy to read and if data processing is all done in SQL and not in the application) and self-documentation (whether the code is properly commented).