SQL 语句代做、代做SQL 程序设计、代写SQL Database Assignment、代写数据库

日期：2018-09-05 03:31

INFO90002 Database Systems and Information Modelling Assignment

Case

You are the SQL programmer for an app that supports student group work. The app allows students

to form groups and then share their hours of availability. Your job is to write SQL queries that allow

the app to work. The data model is as follows:

Figure 1: data model

How the app will be used

First, the id and names of the university’s 10,000 students are entered in the system.

Each individual student can then record their weekly hours-of -

availability to work on the project; i.e., when they are free each

week, using the user interface on the left.

The UI shows the days of the working week along the top and

the work hours from 9am to 5pm down the left-hand side. The

student clicks to highlight their free hours, which are recorded

in the Availability table.

Students can then form and name their groups, recording their

group details in the system.

The Calendar table restricts the times of interest to the working

week, and allows us to label some special times, e.g. “lunch”.

Figure 2: User interface for entering free times

INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s2

© 2018 The University of Melbourne

Setup Script

To set up the database in your MySQL server, download the file asst2-2018s2-setup.sql from LMS

and run it in Workbench. This script creates the database tables and populates them with test data.

Note the comments near the start of the script. There are actually 2 versions of the script – it is

different depending on whether you run it on the UniMelb server or your own server. If it is your

own server, you will want to uncomment some lines near the top, so that you create a new schema to

store the tables in. You can’t create a new schema on the UniMelb server.

The SQL queries you must write

Over the page are listed 10 questions for you to answer. Write one SQL statement to answer each

question. Do not use views or temporary tables to answer the questions.

Beside each question is a maximum mark which reflects the difficulty of the question.

Your total score will be scaled to 10% of your overall marks in the subject.

Your SQL queries should use the same inputs that are used in the questions. For example, a question

that mentions “lunch” should involve a search of the calendar for times marked “lunch”.

You should answer “yes/no” questions by printing ‘yes’ or ‘no’, or ‘1’ or 0’.

An example of a yes/no question is “Is student 10001 free at 10am on Wednesdays?”

(MySQL prints True as ‘1’ and False as ‘0’. To see this, run the statement: SELECT 1 = 2; )

Make sure your code and output are readable – this will affect your mark. Where it would improve

readability, order your output and use aliases. Format large numbers and fractions appropriately.

Assessment

Your work will be assessed according to three criteria:

• Correctness of output (70%)

• Simplicity of code (20%)

• Correctness of formatting (10%)

The “simplicity” requirement means that your code should not be unnecessarily complex. For

example, a query that produces correct output, but say, joins more tables than is necessary, may not

achieve full marks, even though it produces the right output.

INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s2

© 2018 The University of Melbourne

The Questions

(marks are in brackets)

1. What is the longest student name? (The length of a student’s name is the

sum of the lengths of their given and family names) (1)

2. List the names of students who have not yet entered any free times. (1)

3. Which students are free on Wednesday at 10am? (show id and name) (2)

4. List each student's name. For those who are in a group, list also the name

of their group. (2)

5. For any groups that have more than 3 students, list the group’s id, name

and number of students (3)

6. Is student “Alice Smith” free at lunch on Wednesdays? (3)

7. List all times when students 10001 and 10002 are both free. (4)

8. For each group, list the group id and name of the student whose family

name is alphabetically first in the group. (4)

9. Which students are free on Wednesdays between 10am and 12 noon?

Show their ids and names. (5)

10. Are the members of 'WeLoveDb' all free on Wednesday at 10am? (5)

INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s2

© 2018 The University of Melbourne

Submission

Submit a single PDF showing your ten answers to LMS by midnight on the due date of Monday 10th

September, at the start of week 8.

Ensure that you place your student number at the top of every page of your submission.

For each question, present an answer in the following format:

• Show the question number and question in black text.

• Show your answer (the SQL statement) in blue text (not a screen shot)

• Show a screenshot from Workbench showing output of 10 or fewer lines.

• Show how many rows were returned, in red text

For example:

7. List all users with the last name ‘Altman’

SELECT *

FROM User

WHERE lastName = 'Altman';

5 Rows Returned

SQL queries must be formatted in an easy-to-read manner. This means writing keywords in all-caps,

placing most clauses on new lines, and indenting subqueries. For example, this is acceptable:

SELECT DISTINCT saleId

FROM Sale

WHERE departmentID IN

(SELECT departmentId FROM Department

WHERE floor = 2);

whereas this is not acceptable:

select distinct saleId FROM Sale where departmentID IN (SELECT departmentId from

DEPARTMENT WHERE Floor = 2);;

INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s2

© 2018 The University of Melbourne

Academic Honesty

This assignment must be your own work. Plagiarism - the copying of another's work without proper

acknowledgment - is not permitted. Nor is allowing another person to copy your work. Work

submitted for assessment purposes must be the independent work of the student concerned. for details about academic honesty.

Academic misconduct occurs when students portray someone else's work as their own. There are

many ways in which academic misconduct can occur. Some of these are:

• Sham Paraphrasing: Material copied verbatim from text, with source acknowledged in-line,

but represented as paraphrased.

• Illicit Paraphrasing: Material paraphrased from text without in-line acknowledgement of

source.

• Other Plagiarism: Material copied from another student's assignment with the knowledge of

the other student.

• Verbatim Copying: Material copied verbatim from text without in-line acknowledgement of

the source.

• Recycling: Same assignment submitted more than once for different subjects.

• Ghost Writing: Assignment written by third party and represented as own work.

• Purloining: Assignment copied from another student's assignment or other person's papers

without that persons knowledge.

The University is committed to graduating students with "a profound respect for truth, and for the

ethics of scholarship... we want our graduates to be capable of independent thought, to be able to do

their own work, and to know how to acknowledge the work of others" .

As such, the university takes a dim view of students who are not able to correctly acknowledge the

work of others, or who try to pass this work off as their own.