CP2404: Database Modelling - Database Implementation and Query Formulation - IT Assignment Help

Assignment Task:

Task:

Rationale This assignment has been designed to give students experience using Structured Query Language (SQL) and other database management systems (DBMS) facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following learning objectives for this subject:

• Develop and implement a database model using the E-R model and facilities provided by a DBMS

• Formulate queries using a database query language

This assignment consists of two main tasks:

1. Creating the database (by following three subtasks):

o Create a relational database for a given conceptual model (ERD) using MySQL Workbench

o Create a physical database model on MySQL Workbench by applying the forward engineer process o Import raw data from the external file to a table using the MySQL Workbench facility or using SQL queries

2. Writing SQL queries for given problems. Further details about each task are presented in the following pages. Submission

• An MySQL Workbench file containing the ERD you created (.mwb)

• A database dump file built and exported on MySQL Workbench (.sql)

• A WORD or text file containing all SQL query codes and result tables (.doc, .docx, or .txt)

Task 1: Creating the database [42 marks] 1. Use MySQL Workbench to create an ERD to present a relational database model supplied as below. This is a sample ERD for a simplified University Enrolment Database. • A faculty means an academic member who usually runs (teaches) courses (subjects).

• A course means a subject run by University

• A course is offered by the schedule set by University

• A student make a registration for each term by enrolling to a number of courses offered. PKs should be correctly specified. All necessary attributes should be specified by setting appropriate data types and appropriate field lengths. [Save the completed model as a uniEnrolDB.mwb file] The final ERD you create should correctly correspond with the ERD provided above in terms of structure, though the way to present components (PK or FK) in the ERD created using MySQL Workbench is not exactly same as what is presented in the ERD provided above

2. Create a physical database model using the forward engineering process on MySQL Workbench.

3. Insert the data (provided for this assignment) into the database. You may choose to use either the SQL INSERT syntax or the import facility provided by MySQL Workbench. (Caution: The name or order of attributes in the raw data files provided may not exactly match with those shown in the ERD. You will have to be careful to check it when you import data from Excel files to each table of your database)

4. Finally, dump the database into one integrated file on MySQL Workbench. [Save the file as uniEnrolDB.sql file] CP2404 / CP5633 Database Modelling - Assignment #2 - Page 3 -

Task 2: Creating queries [40 marks] Using the database you constructed in Task 1, create the following SQL queries in MySQL Workbench. For each question, the sample result table is provided to help you get an idea of what the table head and data format of your query result should look like. (Note that the sample data shown in the result table provided are just a made-up result shown as ‘sample’, thus they are not matched with actual correct result expected.) [Paste your SQL query into a Word document to submit.

The final Word file should contain all SQL queries (Q1~Q20) you made for this task]

Notes: - Queries should be written so that they would work with all reasonable sets of test data, not just that which has been supplied as a sample data. - Marks may be deducted if your SQL is excessively (or unnecessarily) complicated. - Full marks will be awarded where the solution provided is correct in all respects. - Partial marks may be allocated where students are deemed to have provided a significant effort toward a correct result, but the solution contains some error. - No marks are awarded where either no solution is provided, or the solution provided is deemed to be mostly incorrect

This CP2404: IT Assignment has been solved by our IT Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.