CMT207留学生代做、代做Database Systems、代做SQL编程语言、SQL代写

日期：2019-03-28 08:55

Cardiff School of Computer Science and Informatics

Coursework Assessment Pro-forma

Module Code: CMT207

Module Title: Information Modelling and Database Systems

Assessment Title: Relational data modelling

Assessment Number: 1

Date Set: 04 March 2019

Submission Date and Time: 12 April 2019 at 9:30AM

Return Date: 03 May 2019

This assignment is worth 50% of the total marks available for this module. The penalty for late or

non-submission is an award of zero marks.

Your submission must include the official Coursework Submission Cover sheet, which can be found

here:

https://docs.cs.cf.ac.uk/downloads/coursework/Coversheet.pdf

Submission Instructions

A submission page will be created on Learning Central in Week 11. You will need to submit two

documents:

Description Type Name

Cover sheet Compulsory One PDF (.pdf) file [student_number].pdf

Coursework Compulsory

One WORD file (.docx)

NOTE: Use the template provided on Learning Central as

described in the coursework description below.

[student_number].docx

Any deviation from the submission instructions above (including the number and types of files

submitted) may result in a mark of zero for the assessment or question part.

Assignment

The coursework is based on the following brief. Please read carefully.

A relational database is used to store information about students. For each student, the following

information is stored: surname, first name, home address, date of birth, student number.

In addition, we know the student's place of birth (by place name and country). There may be places

where no student was born or currently resides.

Students complete a set of modules each academic year. Each module has got a title, a code and the

number of credits. Some modules are mandatory while others are optional. Each module is assessed

by a combination of an exam and coursework assignments each worth a certain percentage of the

overall mark. For each student, their performance (i.e. mark out of 100) is recorded for each

assessment. Each mark is accompanied by the date of assessment and the attempt number.

Each module is taught by lecturers. Some lecturers may not teach any modules in some academic

years. They can also teach more than one module per academic year. Each module can be taught by

more than one lecturer. For each module, we know how many lectures are given by each lecturer in

each academic year. Optional modules may not be taught at all, e.g. if no students have opted for

that module.

For each lecturer, the following information is stored: surname, first name, date of the employment

start, annual salary, staff number.

In addition, we know where the lecturers live (by place name and country).

Please answer the questions in the Word submission template, which is provided as a separate

document on Learning Central and distributed together with this pro-forma. The questions are

divided into four parts.

Part 1: Normalisation [0–20 marks]

The following relation is defined to store information about assessment results:

ASSESSMENT_RESULT(STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE,

ASSESSMENT, PERCENTAGE, MARK, ATTEMPT, ASSESSMENT_DATE)

1. Suggest the attributes that should become part of the primary key.

2. List all functional dependencies.

3. Provide all reasons why this relation is not in the first normal form.

4. Provide all reasons why this relation is not in the second normal form.

5. Normalise this relation so that is in Boyce–Codd normal form.

Part 2: ER modelling [0–20 marks]

Draw an entity-relationship (ER) diagram for the problem described in the brief above. The diagram

has to be neat and readable to get full marks.

Part 3: Data definition [0–30 marks]

Translate the ER diagram you created in Part 2 into tables using SQL CREATE TABLE definitions,

which should include primary and foreign keys (if any). Describe the logic behind the choice of the

keys and their role in managing the data stored in the database. Use examples to illustrate the latter

point.

Part 4: Data manipulation [0–30 marks]

Using the database defined in Part 3, write SQL queries to perform the following actions:

1. Find all student pairs born in the same place. Note: Do not double count the students.

2. Find all student-lecturer pairs who live in the same place.

3. Find a total number of students per module in the academic year 2017/2018.

4. Find the average mark for each module in the academic year 2017/2018.

5. Find all students who failed a module in the academic year 2017/2018.

6. Find the best performing student(s) in the academic year 2017/2018 based on the number

of credits and the marks achieved.

7. Find the highest paid lecturer(s).

8. Find lecturer(s) who are not teaching any modules in the academic year 2017/2018.

Learning Outcomes Assessed

Understand how to create a conceptual model and map it to efficient representation

in a database schema.

Understand relational database management systems.

Criteria for assessment

The maximum mark for each task is given in brackets with a total maximum of 100 marks. Marks will

be assigned on the basis of how well you achieved the task objective. In general, your work will be

assessed according to the following factors:

Part 1: Correctness and completeness of answers. Correctness of the resulting normalised

relations together with the quality of explanation of the steps taken to normalise

the relations, where normalisation is necessary.

Part 2: Correctness/simplicity of the model. Following good practice in conceptual design

described in the lecture slides presented in Week 3.

Part 3: Correctness and simplicity of table definitions and how well they are aligned with

the ER model from Part 2. Following good practice in logical database design

described in the lecture slides presented in Week 3.

Part 4: Correctness, simplicity and efficiency of queries.

The overall mark can be interpreted as follows:

Distinction 70–100

Merit 60–69

Pass 50–59

Fail 0–49

Feedback

Feedback on your coursework will address the above criteria. It will be entered using track changes

and comments in the Word submission document. The marked document will be returned to you via

Learning Central within three weeks of submission. This will be supplemented with group feedback,

which will also be made available on Learning Central. Further individual feedback in person can be

arranged upon request.