代写INFO6001、代做EER Diagram、SQL代写、SQL编程设计调试

日期：2019-04-25 10:55

School of Electrical Engineering and Computing

INFO6001: Database Management 1

Assignment 3: Marking Guide

Marks: out of 150 (Note: Assignment 3’s weight is 15% for the course)

1. Revised content of assignment 1 and assignment 2: (30)

Requirement Specification (including data requirements, transaction requirements and

business rules) (5)

EER Diagram and Data Dictionary (5)

Mapping of the EER model to the relational model in DBDL (10)

Normalizing the relational schema to Boyce-Codd Normal Form (10)

2. Completed major steps in the physical database design: (115)

2.1 Write SQL scripts which create the normalised SEEC Resource Access database, including all

necessary tables with right parameters such as primary key, foreign key, default value. (40)

+ The codes work, can create the database (including having codes to delete the tables

and re-create tables) (10)

+ Right tables (according to your normalised table list) (10)

+ Right attributes & data types (2)

+ Right Primary key (3)

+ Right Foreign Keys with referential actions (10)

+ have at least 2 Nulls (2)

+ have at least 3 defaults (3)

2.2 Write SQL statements satisfying the transaction requirements including the following (75)

2.2.1 Input proper data (as you consider legitimate) of at least three rows for every table (25)

2.2.2 Implement the following queries (make sure to populate with enough and proper data into

related tables so that non-void result is shown for each query. A query will be given zero mark if it

has void output or no output). (50)

Q1: For a staff member with id number xxx, print his/her name and phone number. (5)

Q2: Print the name of student(s) who has/have enrolled in the course with course id xxx. (5)

Q3: Print the name(s) of the student member(s) who has/have borrowed the category with the

name of camera, of which the model is xxx, in this year. Note: camera is a category, and model

attribute must be in movable table. (10)

Q4: Find the moveable resource that is the mostly loaned in current month. Print the resource id

and resource name. (10)

Q5: Print the maximal number of speakers that the student with name xxx can borrow. The student

is enrolled in the course with course id yyy. Note: speaker is a category. (10)

Q6: For each of the three days, including 14 February 2019, 18 March 2019 and 1 April 2019,

print the date, the name of the room with name xxx, and the total number of reservations made for

the room on each day. (10)

3. Report Writing (5 marks)

+style, grammar, etc.

Note: During the viva, the authenticity of your assignment will be verified. Also, you will be

asked to point out any additional features you have identified, designed and developed.

NAME (print & sign): StudentNO: Date & Time: