Working With Data : Database Assignment Due Date 16th December 11pm
Part A ER Diagram 25
For this part of the assignment you will need to complete one of the following ER diagrams and associated tasks. The ER Diagram exercise you need to complete is based on your student number.
If your student number ends with a 0zero, 2, 4, 6, 8 you need to complete ER Diagram Exercise 1. If you student number ends with a 1, 3, 5, 7, 9 you need to complete ER Diagram Exercise 2.
For each ER Diagram Exercise, you will need to complete the following along with explanations of why you did things for each point:
Complete ER diagram, using Oracle Data Modeler
ER model to include primary and foreign keys, mandatory attributes, etc
Include all additional indexes and other physical database features in the Physical model
Generate the DDL script and save as a file.
Run the DDL script in your Oracle Schema show evidence for this
Test your tables by adding a minimum of 5 records for each.
Provide some SQL queries, and their results, to demonstrate using the tables and their data.
In addition to including details of the above in your Assignment report document in PDF format, you should also submit the Oracle Data Model files and the DDL script file.
ER Diagram Exercise 1
Ireland DIY is a national DoItYourself company with retail outlets throughout the country. From these outlets it sells products that are obtained from a number of suppliers. Each outlet has an identification number as well as an address and telephone number.
Each outlet must be managed by a single staff member, who is identified by his or her staff number. A record is required of a staff members name and home telephone number. A staff member acting as a manager is never responsible for more than one outlet at the same time. It can happen that some staff members, who have been managers, are not currently responsible for any outlet as a result of staff transfers, promotions, etc..
In order to assist in the analysis of sales, the company allocates each product which it sells to one of its sales categories. Each category has a category number and a description such as Kitchens, Gardens, Home Furnishings, and so on.
Some of the larger retail outlets are divided into departments. However, none of the small retail outlets are subdivided into departments. Where a retail outlet does have departments, the department name and floor area in square metres of each department at that store are recorded. Small outlets have the floor area for the outlet recorded.
Each of the products sold by the company has a unique product code. In addition, for each product there is a need to record its description. Each product is supplied by a single supplier. Details of the suppliers identifying code and name and address are held only for those suppliers currently supplying products. It is important that the number of items of each product stocked at each retail outlet is recorded. There is a company policy that no more than 40 of any product are held in stock in a small outlet.
ER Diagram Exercise 2
Dublin Logistics is a distribution company that maintains a series of depots throughout the UK from which it distributes retail products on behalf of a range of suppliers. Each depot has a unique depot code as well as an address and telephone number. Each depot has a manager, who can be identified by their staff number. In addition, a record is required of their name and office and home telephone numbers. At any one time, a manger is never responsible for more than one depot and some managers
Working With Data : Database Assignment Due Date 16th December 11pm
employed by Dublin Logistics have other managerial responsibilities instead.
Every depot operates ten or more vehicles which are used to distribute various products. Each vehicle has a registration number, and details of the make and model are also needed. When a vehicle requires routine maintenance, this is carried out by a larger depot that has the necessary facilities. Such large depots are known as national depots, as opposed to other depots which are known as local depots. A vehicle is always operated from the same depot and always routinely maintained by the same national depot. Each national depot has an additional telephone number for enquiries regarding maintenance.
Each of the products distributed by Dublin Logistics has a unique product code, is supplied by a single supplier and distributed from at least one depot. In addition, a description of each product needs to be recorded. Each supplier has a unique supplier code as well as an address and telephone number, and supplies one or more products. Every depot holds stocks of one or more products and it is important that the number of items of each product stocked at each depot which may be zero is recorded.
Part B Data Audit Report using PLSQL and SQL Statistical Functions 50
For this part of the assignment you will need to use the Portuguese Bank data set https:archive.ics.uci.edumldatasetsBankMarketing. You will need to download this data set.
Load this data set into a table in your Oracle Schema. Document the steps taken to load the data set into your Oracle schema and provide the code. Your code should be documented.
Write a PLSQL Procedure to perform a data audit of this analytic record. You will need to use a variety of statistical functions, and may require slightly different SQL statistical functions to be used based on the data type, for example VARCHAR2, NUMBER, DATE.
The output from the data audit can be stored in a table.
Deliverables for this part of assignment.
Details of how the data set was loaded into the tables.
Design for the code
PLSQL code used to perform the data audit.
Output from the data audit table formatted to make it readable
IMPORTANT: you are not allowed to use the DBMSSTATFUNCS.SUMMARY function in PLSQL
Part C Machine Learning using SQL 25
For this part of the assignment you will create three machine learning models using the indatabase machine learning features. Use the analytical record create in Part B for the Customer Churn data set.
NOTE : Decision Trees machine learning algorithm should not be used. If used, no marks will be allocated to its use.
You may need to create additional tables, views, and use sampling to prepare the training and test data sets. You may need to create a view that contains the predicted values for the testing data set.
Complete this process by evaluating the accuracy of the models.
Write a PLSQL program to combine the accuracy measures from the various models and to present them the user. For example, you can use DBMSOUTPUT function to display the results
Working With Data : Database Assignment Due Date 16th December 11pm
Marking Scheme
The marking scheme for this assignment is:
25
50
25
IMPORTANT:
Part A ER Diagram
Part B Data Audit Report using PLSQL and SQL Statistical Functions Part D Machine Learning Using SQL
Your report should not consist solely of code, images andor screen shots. Explanations should be given in addition to documented code.
All code for a sectionpart of the assignment should be in that section and not in a separate appendix in a different part of the report.
Everything should be in one PDF document, organised to have sections for each part of the assignment.
Your assignment report should contain all the workings for each component of the assignment. This report should be converted into PDF format. This file, along with any additional files requested, should be ZIPPED not RAR, TAR or any other compressing formats and submitted on Brightspace.
The ZIPPED file should be in ZIP format, and not in RAR, TAR or any other compression formats.
The documentation for your assignment must contain your name, your student number, your class, course TU?? and year information, assignment, lecturer name and your Username and Password for the data mining tool. Failure to give this information will incur a 10 penalty.
The assignment most be performed individually.
Each submission must be original work as plagiarism will result in a zero mark 0.
There will be a 10 penalty for each day the assignment is late.
There is no penalty for submitting early.
DIT Plagiarism Policy : http:www.dit.iemediadocumentscampuslifeplagiarism.doc
Assignment Feedback
Given the timing of the assignment which is unavoidable, feedback will be provided via Brightspace. This will consist of marks and a short comment for your assignment submission. The feedback provided in Brightspace will be a brief description of areas where you did well and indications of areas where marks were lost.
All marks are subject to external examiners, modules boards and progression boards.
I will endeavour to provide feedback and marks within two weeks of the assignment submission date. I hope to have all marks and comments returned to you before Christmas. But given the submission date it is likely that feedback may be provided in early January. But no later than midJanuary.
If you would like to arrange a brief face to face meeting to discuss, this can be arranged during January or first week of Semester 2.