DATABASE DESIGN

DD1 : MSACCESS, MySQL , MSSQL , Oracle , Advanced Databases 

DD4 : MSACCESS , MSSQL , MariaDB , MongoDB , MySQL , MySQLi 

DD5 : ORACLE , SQL , SQLite , T-SQL , PL/SQL , PostgreSQL , ArangoDB , Apache Presto 

DD7 : CouchDB , DB2 , DocumentDB SQL , DocumentDB , DynamoDB , H2 Database , HSQLDB , IMS DB , Memcached 

DD9 : Neo4J , OBIEE , OrientDB , PouchDB , Redis , SQL Certification 


1) EM0156-EM131415513M27

Assignment Description

Assignment 1: Using the DBMS_ALERT Package
Brewbean’s wants to add an alert in the product management page to advise the manager of a product stock level falling below the reorder point. The alert simply needs to state which product needs reordering.
1. Start SQL Developer, if necessary.
2. Create a database trigger on the BB_PRODUCT table, using the DBMS_ALERT package to send an alert when the stock value falls below the reorder value. Name the alert reorder and have it contain a message stating that a product needs to be reordered. Include the product number in the message.
3. If an error is raised because you don’t have permission to use the DBMS_ALERT package, you need to log on as SYSDBA and enter the following command to grant the necessary permissions. (This code assumes you’re the user PLBOOK.) GRANT EXECUTE ON DBMS_ALERT TO PLBOOK;
4. Start a second session of SQL Developer as SYSTEM MANAGER.
5. Type and run the following block to register the alert: BEGIN DBMS_ALERT.REGISTER(‘reorder’); END;
6. Type and run the following block to initiate the wait for an alert: DECLARE lv_msg_txt VARCHAR2(25); lv_status_numNUMBER(1); BEGIN DBMS_ALERT.WAITONE(‘reorder’, lv_msg_txt, lv_status_num, 120); DBMS_OUTPUT.PUT_LINE(‘Alert: ‘|| lv_msg_txt); DBMS_OUTPUT.PUT_LINE(‘Status: ‘|| lv_status_num); END;
7. The preceding code sets a wait period of two minutes. Return to the first SQL Developer session. Type and run the following code to cause the alert to fire: UPDATE bb_product SET stock = stock – 2 WHERE idproduct = 4; COMMIT;
8. Return to the second SQL Developer session. The alert message should be displayed.

Assignment 2: Using the DBMS_DDL Package
Because Brewbean’s is in the middle of constructing its application and making several database modifications, the developers are going to build a procedure to recompile all invalid objects automatically.
1. Start SQL Developer, if necessary.
2. Type and run the following code: SELECT object_name, status FROM user_objects WHERE object_type = ‘PROCEDURE’;
3. If you see the BB_JOBTEST procedure listed with a VALID status onscreen, skip to Step 7. If this procedure doesn’t exist, proceed with Step 4.
4. Open the assignment02.txt file in the Chapter10 folder, and use its code to create the BB_JOBTEST procedure.
5. Type and run the following code: SELECT object_name, status FROM user_objects WHERE object_type = ‘PROCEDURE’;
6. Verify that the query results list BB_JOBTEST with a VALID status, which tells you the procedure exists.
7. Type and run the following statement to modify the BB_JOBQ table. Because the BB_JOBTEST procedure uses this table, the procedure changes to the status INVALID, indicating the need for recompiling. ALTER TABLE bb_jobq MODIFY (msg VARCHAR2(30));
8. Type and run the following query to confirm the status INVALID: SELECT object_name, status FROM user_objects WHERE object_type = ‘PROCEDURE’;
9. Create an anonymous block, using DBMS_DDL.ALTER_COMPILE to compile all INVALID objects. To simplify this process, retrieve all the INVALID objects in a cursor. Run the anonymous block.
10. Type and run the following query to confirm that these objects’ status is now VALID: SELECT object_name, status FROM user_objects WHERE object_type = ‘PROCEDURE’;

Assignment 3: Using the UTL_FILE Package to Read and Insert Data Brewbean’s has struck a deal with a new tea product supplier, who has sent a test file showing how files containing product names and descriptions will be submitted. Create a PL/SQL block, using the UTL_FILE package, to read from the file and insert data in the BB_PRODUCT table.
1. Start Windows Explorer. Open the tea.txt file in the Chapter10 folder. Check the file’s contents to confirm that it contains product names and descriptions, and review the data’s format.
2. Start SQL Developer, if necessary.
3. Create an anonymous block that reads each line in the tea.txt file and inserts the values in the BB_PRODUCT table. Use the UTL_FILE reading feature in a loop to read each line from the text file.
4. Run the block to perform the INSERT statements.
5. Type and run the following code. The results should display the three tea products from the tea.txt file. SELECT productname, description FROM bb_product;

Assignment 4: Using the UTL_FILE Package to Export Data ColumnsTheBrewbean’s manager wants to have a file extracted from the database containing product information for inventory and cash flow analysis. The manager uses this file in spreadsheet software on a laptop computer. Using the UTL_FILE package, create a PL/SQL block that places data columns in a comma-delimited text file named prod_ext.txt in the c:\oraclass directory. The extracted file should contain one line per product and the following columns of the BB_PRODUCT table: IDPRODUCT, PRODUCTNAME, PRICE, TYPE, STOCK, ORDERED, and REORDER.

Assignment 5: Sending E-mail with UTL_SMTP
The Brewbean’s manager decided he wants to get e-mail notifications when product stock levels fall below the reorder point. Create the BB_STKALERT_TRG trigger on the BB_PRODUCT table to perform this task. The e-mail body should state the product ID and name. Use the following UPDATE statement to set up a product to test:
UPDATE bb_product
SET stock = 26 WHERE idProduct = 4;
COMMIT;
Test the trigger with this code:
UPDATE bb_product
SET stock = stock – 2
WHERE idproduct = 4;
COMMIT;
When you finish the assignment, disable the trigger so that it doesn’t affect other assignments.

Assignment 6: Using DBMS_OUTPUT Using the DBMS_OUTPUT package, create and run a PL/SQL block that displays lines for each product in the BB_PRODUCT table. If the stock level for a product is above the reorder point, only a single line should be displayed that looks like the following code:
Product 5 – Sumatra does NOT need ordering
If the stock level is below the reorder point, lines such as the following should be displayed:
Product 5 – Sumatra needs ordering!
Stock = 24 , reorder point = 25
Before running the block, issue the following statements to make sure product 4’s stock level is below the reorder point:
UPDATE bb_product
SET stock = 24 WHERE idProduct = 4;
COMMIT;

Assignment 7: Using the DBMS_SQL Package
Brewbean’s employees want an application page where they can query the CUSTOMER table for ID and last name based on one criterion on any customer information column. To perform this task, you create a procedure by using DBMS_SQL to set up a dynamic query. Name the procedure DYN_CUST_SP and verify that the procedure works by issuing two queries: one with the state NC and one with the e-mail value ratboy@msn.net. Keep in mind that any column can be used as a criterion, which could result in the query returning more than one row. For example, two customers might have the same last name. Therefore, this procedure needs to be able to handle multiple rows being returned.
1. Start SQL Developer, if necessary.
2. Open the assignment07.txt file in the Chapter10 folder, and use its code to create the DYN_CUST_SP procedure.
3. Next, you need to run the procedure with the two test cases. First, use the STATE column criteria, and run an anonymous block that includes the statement dyn_cust_sp (‘state’, ‘NC’);.
4. Now check the customer e-mail address by using an anonymous block that includes the statement dyn_cust_sp(‘e-mail’, ‘ratboy@msn.net‘);.

Assignment 8: Using Native Dynamic SQL to Add ColumnsTheBrewbean’s manager wants an application page that makes it easy for employees to add columns to the database. Create a procedure that accepts input and uses native dynamic SQL to perform this task. Test the procedure by adding a column named MEMBER to the BB_SHOPPER table with the data type CHAR(1).
1. Start SQL Developer, if necessary.
2. Open the assignment08.txt file in the Chapter10 folder. Review the uncompleted procedure code, and then finish the EXECUTE IMMEDIATE statement to allow column additions.
3. Create the procedure in SQL Developer.
4. Run the procedure by using the following block to add the MEMBER column: BEGIN dyn_addcol_sp(‘member’,’bb_shopper’,’CHAR(1)’); END;
5. Type DESC bb_shopper and press Enter to list the table structure and confirm the addition of the MEMBER column.

Assignment 9: Using Native Dynamic SQL for Product SearchesBrewbean’s wants to allow customers to do product search by selecting a product name or description, and then typing a search term. Using native dynamic SQL, create a procedure named SEARCH_SP that returns the product name, description, and price based on users’ search criteria. This procedure needs to handle multiple rows being returned.

Assignment 10: Understanding Business Intelligence
Business intelligence (BI) is a term used to describe providing more database power for users. Describe briefly what it means in applications, including the terms “data mining,” “data marts,” “OLAP,” and “executive dashboards.” What role might dynamic SQL play in BI? Describe BI products that Oracle offers.

Assignment 11: Using the Wrap Utility
In this assignment, you show how to use the Oracle wrap utility to obfuscate the BB_JOBTEST procedure’s source code. Create an .sql file (job.sql) using the script file for the procedure (available in assignment02.txt in the Chapter10 folder of the data files). Use the wrap utility on the job.sql file to hide the script’s source code. Check the file the utility produces.

 


2) 32378- Big Data Analytics

Assignment Description

Big Data Analytics: Security & Privacy Concerns

Table of Contents
Title 2
Introduction 2
Background of the Selected Topic 2
Rationale 2
Problem Domain 2
Purpose & Justification 2
Sponsor or Supervisor Recommendation 3
Research Questions 3
Conceptual /Theoretical Framework 3
Methodology 4
Research & System Development Methods 4
Data Collection Methods 4
Ethical Issues 4
Compliance Requirements 5
Analysis of Data 5
Project Plan 5
Deliverables 5
Work Breakdown Structure 6
Risk Analysis 6
Duration 7
Gantt chart 7
References 9

Background of the Selected Topic Big Data is a technology that is being used by the business organizations to handle and manage their vast data sets. The need of the data and information management for the organizations is increasing and there are vaIntrodried sources of data that the organizations use to gather the data sets. The Big Data tools that are deployed in the organizations provide them with the benefits of easier data management, enhanced data storage, and streamlining of data operations. However, there are security and privacy concerns that are associated with the technology. These concerns need to be handled so that the data confidentiality, privacy, integrity, and availability is always maintained and protected [1].

Rationale Problem Domain
Big Data has the properties as volume, velocity, and variety. These properties are associated with Big Data as huge clusters of data are managed by these tools that may have varied types and structures and brought at rapid speed. The presence of such volumes and variety of data leads to the enhanced probability of security and privacy attacks that may come up [2].
Purpose & Justification The purpose of this research work is to have a detailed account of the security and privacy issues that are associated with Big Data [3].
The justification of the research can be obtained from the increased frequency of security risks and attacks on the Big Data. There are numerous such incidents that are reported every other day that impact the confidentiality and privacy of the data sets. Such occurrences also lead to the legal obligations and punishments. It is necessary to have the details of the security and privacy concerns so that the associated measures may be adopted for preventing, controlling, and mitigating such issues.
Sponsor or Supervisor Recommendation The research objectives will be achieved only when there is an adequate guidance provided at each step. Such activities will be performed by the sponsor or supervisor of the project. Therefore, the supervisor having the knowledge and experience with the Big Data tools and techniques shall be selected. The main focus of the research will be on the security and privacy aspect of Big Data and therefore, the supervisor shall be aware and updated of the security tools, protocols, and norms as well [8].

Research Questions
The following research questions will be answered through the conduction of the research project on the topic as Big Data Analytics: Security & Privacy Concerns.
• What are the Big Data tools and techniques that are used by the business organizations?
• What are the security and privacy concerns that these tools and technologies are exposed to?
• What are the possible impacts of the occurrence of these security and privacy issues?
• What are the prevention, control, and mitigation measures that may be implemented to handle such issues? [7]
Conceptual /Theoretical Framework
Conceptual FrameworkMethodology
Research & System Development Methods
There is a methodology that will be needed to carry out the research activities. The methodology that will be adopted in this case will be qualitative research methodology. It is the methodology in which the exploration approach will be followed to achieve the research objectives. In this methodology, the data collection methods will be used to gather the information sets for the research. The security and privacy concerns will be listed on the basis of the information sets collected. The sample size will be kept small and automation will be introduced by using automated data analytics tools [4].
There will also be mathematical concepts needed to have a further understanding of the security and privacy issues associated with Big Data analytics [5]. For this purpose, quantitative methods will be used as secondary research methodology for the project.

Data Collection Methods
• Questionnaires: There will be questionnaires that will be prepared in which the organization representatives will be asked about the Big Data tools being used in their respective organizations, security & privacy concerns issues, and measures being used to handle such issues [6].
• Domain Analysis: The Big Data tools have been implemented in the organizations belonging to varied sectors, such as healthcare, automobiles, marketing, e-retail, and many more. The domain analysis of each of these domains will be conducted to understand the domain-specific issues.
• Interviews: There will be interview sessions scheduled and conducted to gain a detailed perspective of varied entities associated with the project.

Ethical Issues
There are various ethical theories and norms that are present and it is necessary to adhere to certain ethical theories and principles.
In this case, there may be violation of certain ethical theories. For instance, if the attackers succeed in giving shape to the security and privacy attacks, then there is a violation of legal and regulatory policies that occur. This violates the ethical theory of Deontology ethics. The occurrence of such issues also leads to the negative consequences for the data owners and the end-users. It is because the confidentiality and privacy of the information sets gets compromised. The theory of Consequentialism is violated as a result.

Compliance Requirements
There are certain laws and acts that shall be adhered to, such as Data Protection Act, IT Laws, Cyber Laws, and Intellectual Property rights.
The resources associated with the project must also adhere to the professional codes and ethical theories and policies.

Analysis of Data
The data sets that will be gathered using the methods as interviews, domain analysis, and questionnaires will be required to be analysed using the qualitative and quantitative methods. There are automated tools that are present for the analysis of the data sets and these tools will be used for the purpose of analysis. These tools will be used as the manual analysis of the data sets will not be possible due to the volume and variety of these data sets.

Project Plan Deliverables
There shall be status and progress reports that must be submitted every week to have an idea of the project status, project issues, risks, and pending activities. There shall be a research plan that shall be provided comprising of the research topic, research questions, research aims, research methodology, and data collection methods. There will be several activities carried out in the project timeline, and the literature review along with the statement of research proposal shall be submitted. The results, discussions, findings, and limitations shall be included in the final research report and the closure activities shall also be highlighted in the same.

Work Breakdown Structure Risk Analysis
There are various risks that are associated with the project. The risk register is depicted below to provide the name and details of the risks involved with the project.
Risk Name Risk Probability Risk Impact Risk Rank Response/Mitigation Strategy
Schedule Overrun/Budget Overrun 3 5 15 Risk Avoidance & Mitigation
Technical Faults 4 3 12 Risk Transfer
Operational Issues 3 3 9 Risk Avoidance
Security Attacks 4 5 20 Risk Avoidance & Mitigation
Quality Issues 2 5 10 Risk Avoidance
Ethical Risks 2 5 10 Risk Avoidance
Legal Obligations 1 5 5 Risk Avoidance

 


3) 177605-Introduction to Database Systems

Assignment Description

Introduction

The objective of this assignment is for you to put into practice the many different skills that you are learning in this unit into a single cohesive database project.

You will be designing a database to meet a specific organizational need. To do this you will work through the various stages of database design, including identifying user requirements, developing an understanding of the entities required and the relationships between them, as well as identifying the business rules associated with the processes that are driving the need for the database. You will then develop appropriate data models and design and implement the database. You will demonstrate that your database implementation is viable through a series of queries and updates on the database.

Scenario

VU University (VU)organizes table tennis match at university wide for both staff and students. This is an annual activity that has a history of 11 years. You are asked to design a small database for the organizer to record data for better management service in the future.

Every year, there are 20 teams participating in this table tennis match. The team can be formed by staff and students together. For each team, there is at least one staff to be the leader of the team, who will communicate with organizer and leaders from the other teams. A team needs 4 to 8 members, including the leader. A form was used to collect the member information of each team. The leader needs to submit this form to organizer two weeks before the first game. In the form, all members’ name, contact email address, role (leader, primary player, or substitute), staff/student ID are provided. One staff or student can only play for one team. Each team has a name for itself, which is also provided in the form.

1. The match lasts four weeks for four rounds. Games are all played at weekends. Four teams will be put in one group. Each team needs to play with all other three teams in its group. Each group has two top teams to enter the next round. In the second round, 10 teams will be put into two groups. Each team plays with other four teams, and the top two teams will enter into the semi-final games. Four teams in the semi-final games, and each team plays with other three teams, the top two teams will enter into the final game.

Each game has three sections. In each section, the team who firstly achieve 11 points wins. Out of three sections, the team who firstly wins two section wins the game. Both single and pair games are played. Each team only allow four members to play the single games. That is four single games will be played between two teams. For pair games, there are also four players allowed. Then two pair games will be played between two teams. Therefore, totally six games will be played between two teams. All the section scores need to be recorded together with team names, players and date of the game. Please be noticed that not all games play three sections. If one team wins the first two sections, then the game is over. By end of the match, all teams are ranked based on their total points achieved. This ranking list is recorded with the ranking numbers and team names in ascending order.

The tournament has 15 referees; a referee is assigned to a single match at the first round. The organizers keep records for referee performance for each match (you can measure performance on a scale from 1 to 10). For the second round, four referees with highest score will be selected. For the semi-final, two referees will be chosen, and for the final the referee with highest score will be assigned.

Your task

You have been commissioned to develop a database system that is capable of keeping records for FU’s table tennis matches from now on.

The database needs to keep a record of:

• All team information, including players’ information
• All games, sections, the players involved and the scores
• The winner team of each game
• The teams play in each round
• The match winner team

• The ranking of the teams of each year’s match

• The referee assigned for each game and their performance score

Further, it should be possible to generate a report on:

• Game scores after each round, including section scores
• The total number of games that each team played in a match
• The total scores of each team after a match
• The ranking list of teams after a match

• The winner list of all recorded matches

• Referees ranking, referee with best performance at all matches

Steps you need to take to develop your database application

1. Complete the analysis and design of your database application

a. List the business rules for your system.
b. Identify the entities and relationships in your system.
c. Identify the characteristics of the entities in your system.
d. Develop an ER diagram to model your system.
e. Develop table structures from the ER model.
f. Conduct a dependency analysis of the table structures and normalize your tables where appropriate, to at least 3NF.
g. Create a data dictionary for your database.

2. Implement your project

a. Create a database that hosts your application data
b. Create tables in your database. These must be consistent with your design.
c. Populate all tables with sample data (at least 10 entries in each)
d. Create the required views, stored procedures etc. to meet the requirements of your system

3. to write SQL commands

1. SQL codes are provided for database and table creation
2. SQL codes are provided for data record insertion Reports by SQL:
3. to show the winner of each game in each round
4. to sort all teams according to their scores (ascending)
5. to provide a report for a referee with all the games he served
6. to provide a list of the loser teams
7. to provide a list of all players in all teams
8. Game scores after each round, including section scores
9. The total number of games that each team played in a match
10. The total scores of each team after a match
11. The winner list of all recorded matches
12. Referees ranking, referee with best performance at all matches

You need to be able to demonstrate that your database application meets the requirements detailed in the scenario as well as be consistent with the model you have developed.

Submission Requirements

Your assignment should be composed of the following parts:

1. Project document which includes the following:

1.1. A list of the business rules;

1.2. ER Diagram(s) prepared using software such as MS Office Visio or any other ER diagram tool. These should include all necessary information about the entities, attributes and relationships. Please provide clear and easy-to-read screenshot of your ER Diagram(s). If you draw your diagram(s) in MS Visio, please submit the Visio file too;

1.3. Data dictionary in the format shown in the lecture notes;

1.4. Database design and table structures showing table names, as well as any related entity integrity and referential integrity constraints. Include in the table structures attribute data types, sizes, primary keys, foreign keys and any other relevant information;

1.5. Diagrams showing the dependency analysis for each of the tables. You need to demonstrate that all tables are in 3NF. Show the process of normalization including tables in 1NF, 2NF and 3NF respectively. Please refer to the examples in lecture notes;

1.6. The SQL code you used to:

1.6.1. Create and populate the database;

1.6.2. Create each of the reports identified in the assignment specification.

2. Database implementation in XAMPP

2.1. All the tables of your assignment must be implemented in XAMPP. Please provide screenshots as evidence of your implementation.

2.2. All the tables must be well defined with appropriate primary keys and foreign keys where applicable. Please provide screenshot of table structures.

2.3. All tables must be populated with sample data (at least 10 entity instances – rows – in each table). Please provide screenshots of table data records.

 


4) 188715 – DBMS- Database management system 

Assignment Description

WORK LEVEL: PostgraduateREFERENCE STYLE: APA

You are required to conduct research on how you will implement the web database, described in Assignment 1 (Modelling) business case, in a cloud instance.
Your research should be based on recent developments in the field of database technology.
Your response should address not only the implementation considerations but also the security issues.
Rationale This assessment task will assess the following learning outcome/s: be able to research a recent development in the field of database technology.

 


5) 753779- data and system integration

Assignment Description

This assignment consists of two deliverables, being: demonstration code 75%, and a report
25%. All code relating to this assignment should be contained within a folder named: Task 2
– Your Name – Student Number, the folder is then to be zipped and uploaded to blackboard.
The report must be uploaded separately.
Important Note: For ease of demonstration, your code must be self-contained. In addition to using
Standards based HTML and Python, PETL, Bottle and Parser are the only additional frameworks you
should need. If you use others they must be provided and not require installation.
Demonstration Code To demonstrate your code, you have been provided with four data sources compiled with fictitious sample data. These are:
• “clinics.csv” contains the information about the clinics,
• “cliniclocations.xml” contains the location coordinates for each clinic
• “services.csv” contains a list of clinical services
• “clinicservices.csv” contains a list of clinics and the services they offer

Task 1 – Data integration demo.
You are to submit a python script named “data_merger.py”.
Your script should firstly clean the data then merge it into one CSV file named
“clinicservicelocations.csv”.
Task 1.1 Data Cleansing (6 marks)
Clean data in the email address field from “clinics.csv” such that:
• All spaces are removed,
• The domain “myclinic.com.au” is appended to those address without a domain,
Task 1.2 Data Merge (15 marks)
Merge the data from the four data sources into “clinicservicelocations.csv” with the
following fields (attributes):
• ClinicServicesID a unique field identifying each record from clinicservices
• ServiceID a foreign key linking to services from clinicservices
• Service the linked title of the service from services
• ClinicID a foreign key linking to clinics and locations
• Clinic the name of the clinic from clinics
• Suburb from clinics
• State from clinics
• Email cleaned email address from clinics
• Lat from cliniclocations
• Lon from cliniclocations
ICT705 Data and System Integration – Assignment 2 Page 5 of 9

Task 2 – RESTful Web Service Demo
You are to submit a python script named “clinic_locator.py”.
For tasks 2.1 to 2.2 Your script should build a RESTful web service that supports a ‘getclinics’
query from the client browser similar to “/getclinics?serviceid=x”. The server Web Service
will return a JSON table with the following fields (attributes): Clinic, Service, Suburb, State,
Email, Lat, Lon. Data is to come from the “clinicservicelocations.csv” you built in task 1.2.
Task 2.1 Return clinics for a service (6 marks)
The server will return a table of records (tuples) of clinics offering the service ID passed to it.
Task 2.2 Return all unique clinics (6 marks)
In the advent of a call to “getclinics?serviceid=0”, the server will return a distinct list of
clinics offering any service. That is a list of all clinics listed once.
Task 2.3 List of Services (6 marks)
To populate your drop-down list in task 3.1 you will need to return a JSON table of all
records from “services.csv” with the following fields (attributes): ServiceID, Service. The
server should respond to a “getservices” request.
Task 2.4 Error Handling (6 marks)
Your code should handle any errors including incorrect requests.

Task 3 Mashup Demo
You are to submit a HTML file “clinic_map.html” and optionally a CSS file named
“clinic_map.css”.
Task 3.1 Form Layout (10 marks)
You are to create an input form that accepts user input for a medical service and a
postcode. The input for the medical service should be a drop-down list with the first item of
being “Any Service” with a value = 0 remaining items should be created dynamically from a
call to getservices from task 2.3. Note: The input for the postcode should be a text box.
These inputs should be accompanied with descriptors and a “Locate Clinics” button.
Task 3.2 Google Map (10 marks)
Clicking the “Locate Clinics” button the will trigger a “getclinics” web service to retrieve a
table of clinics matching the service with attributes described in task 2. From this data you
are to display a Google map marking the position of the returned clinics. The map should be
centred on the input postcode and zoomed to an appropriate level to show nearby clinics,
alternatively you may choose to use marker clustering which automatically centres and
zooms map based on clustering.
Task 3.3 Information Window Demographics (5 marks)
Upon clicking a marker bubble on the map, the user will be displayed an information
window containing: Clinic Name, Suburb, State and a clickable email address.
ICT705 Data and System Integration – Assignment 2 Page 6 of 9
Task 3.4 Information window email (5 marks)
Clicking an email address link in the information window should launch the user’s email
application and automatically insert the subject line “Clinic Enquiry”.

Report
You are required to write a report explaining the key concepts around the design and
implementation of your demonstration system. You are also required to explain how to run
your code. Finally, you are to include all code submitted in the appendices to the report,
note that the code will not count towards the word count.
Your report should follow the following template:
Executive Summary
Table of Contents
Table of Figures (if needed)
Table of Tables (if needed)
1.0Introduction
2.0Key System Concepts
2.1 Data merging and cleaning
2.2 RESTful Web Services
2.3 Mashups
3.0Demo Running Instructions
4.0Conclusion
Bibliography
Appendices

 


6) Re: SA0010-SA240820180138-MN405-Data and Information Management

Assignment Description

Part A: (30 marks)
The quiz will open after week 5. Paste below the summary of your Assignment 1 quiz results from Moodle.
Part B: (70 marks)

1. The snapshot of Party_BookingDB database structure is given below. Party_BookingDB is a database that keeps track of information about the Bookings, Staff, Menus and Customers registered in the system

You are working as an IT specialist in this organisation and are required to extract information from this database by executing SQL queries according to the instructions given below.

The primary keys are marked in the following snapshot of Party_BookingDB database shown in figure 1.

a. First you need to implement the above database in MS Access. You only have to create 5 tables for Bookings, Menus, Menus_Booked, Menu_meals and Meals. You do not have to create other tables in the Figure1. And populate those tables with suitable data (at least 3 records per table).

You can use Datasheet view in MS Access or SQL statement (as given below) to enter suitable data records.
INSERT into TableName
VALUES (“..”,”..”,…..)
NB: You only have to create 5 tables.
And you need to upload your database on submission link along with the word document.

This is an individual assignment; it should be your own individual work (You should not copy Ms Access Database). If not, this is considered cheating and you will get zero marks for the whole assignment.
(10 Marks)

Write SQL queries for the following questions.
Execute the queries on the “Party_BookingDB” database you created in MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions (3 marks for each screen shot & remaining marks for the SQL query)

b. Prepare a list of BookingID, StaffID, CustomerID and date_of_event of all Bookings. You are required to sort the list in descending order of CustomerID (10 Marks)

c. Assume that you want to count how many bookings are there on the date “24th August 2018”. Write a query to find the number. (10 Marks)

d. Display BookingID, StaffID, CustomerID, Customer_details, date_booked of all events scheduled for 24th August 2018. (Hint: Join Bookings and Customers) (10 Marks)

2. Figure 2 shows the ER diagram that captures important information in the tracking of Suppliers and items in a retail store. This retail store purchases items from a number of suppliers. It keeps track of the items purchased from each supplier, and it also keeps a record of suppliers’ addresses. Items are identified by ITEM-TYPE and have a DESCRIPTION. There may be more than one such address for each supplier, and the price charged by each supplier for each item is stored. Suppliers are identified by SUPPLIER-ID.

a. Convert the ER diagram into a relational database schema. Be certain to indicate primary keys (underline).
Eg Supplier (SupID: NUMBER; SuppName: text,…..) (10 Marks)

b. Identify Associative / Bridge entity in the ER diagram of figure 2. Explain purpose of using Associative / Bridge entity. (3 Marks)

c. Do we need a separate entity for supplier address? Can we include it as an attribute in the Supplier entity? Explain your answer. (2 Marks)

3. Business intelligence (BI) is a term that describes a comprehensive, cohesive, and Integrated set of tools and processes used to capture, collect, integrate, store, and analyse data with the purpose of generating and presenting information to support business decision making. Components of the BI framework are given in the Figure 3.
(Reference : Chap 13 -Database Systems: Design, Implementation, & Management Ⓒ 2017 ISBN 9781305627482-By Carlos Coronel and Steven Morris )

Data visualization is one of the BASIC BI ARCHITECTURAL COMPONENT.

Answer following questions.

a. Why is data visualisation important in Business Intelligence? (5 Marks)

b. Tableau (www.tableau.com) is a data visualization tool. Tableau can help anyone see and understand their data. Connect to almost any database, drag and drop to create visualizations.
Install Tableau Public (https://public.tableau.com/s/ ) on your laptop / computer and create any 2 visualisations. Follow the following instructions:

i. First go to Tableau Public https://public.tableau.com/s/ and enter your email address and select “Download the App”

ii. Then you can download the software and run the .exe file to install.

iii. Now you will get the following starting screen. Here you can upload MS excel or MS Access file. Watch this video to find more details on “How to”. https://public.tableau.com/en-us/s/resources

iv. You can use any MS excel, MS Access file or any data file to create 2 visualisations. It should be your own individual work.

 


7) 768709-IT Database system

Assignment Description

Task 1: Knowledge test (5%)
The knowledge test – Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modeling and Normalization.
BIT231 Database Systems Assessment 1 – Design Report Melbourne Polytechnic Page 3 of 9

Task 2 : Database Modelling and Implementation (130 marks):

Question 1 – ER Modelling (60 marks):
1. Business rules – Write Business rules to create ERD (15 marks)
2. Entities and Attributes – List Entities, Attributes – Include all attributes that you believe would
be useful (10 marks)
3. ER Diagram – Design an ER diagram for the above case study. (35 marks)
o Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software.
Hand-drawn diagrams will not be accepted
o Only use “crow’s foot notation”
o ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.
o When completed, insert your ER Diagram into a Word document either by taking a
screenshot of the diagram and pasting it in the word document, or by exporting the diagram
as an image and the inserting it in the word document

Question 2 – Data Dictionary (20 marks):
 Create a data dictionary for the above ER Diagram using the following sample format.
 All data items must have suitable data types and all tables must have appropriate primary key and
foreign key
BIT231 Database Systems Assessment 1 – Design Report
Melbourne Polytechnic Page 4 of 9

Question 3 – Dependency Diagram (10 marks):
 Draw dependency diagram (using the following sample format) for your final ER Diagram, in the
MS Word document, showing where necessary that you have progressed through to 3NF.

Question 4 – SQL Statements – DDL and DML (40 marks):
Based on the specification you have provided in the data dictionary:
i. CREATE TABLE – Write the SQL code to create all tables (in the ER Diagram and Data
dictionary) to implement the relational data logical model (in ONE script). [10 marks deducted for
code that does not work!] 15
ii. Constraints – In part i, define primary keys, foreign keys and NOT NULL constraints in the
CREATE TABLE statement ONLY. [Note: a foreign key constraint requires the existence of the
referenced table]. (10 marks)
iii. DEFAULT clause – In part I, choose an appropriate column to use the DEFAULT clause. Explain
what is meant by DEFAULT and why this column is suitable to take such values. (5 marks)                       iv. Write INSERT INTO statements to populate each table. (at least 3 rows per table) (10 marks)

 

 


8) 765401-database development

Assignment Description

The assessment includes 2 key elements:

1. Database Design (Table Specifications)
2. Database Development (Script Files)

DATABASE DEVELOPMENT
This assignment requires you to complete the following tasks. You will need to complete all the assignment tasks to achieve the learning outcomes for this module. You should complete this assessment individually.

TASK A: TABLE SPECIFICATIONS
Develop a correct set of table specifications based on the entity relationship diagram (see appendix A) with reference to the good practise. You should consider the following;

 appropriate table name, datatypes, constraints and defaults
 integrity
 case

Consider the design requirements before implementation and use the example table structure provided in appendix B.

TASK B: SCRIPT FILES
Use the entity relationship model and your table specifications to create the following script files: All files should have an appropriate extension and be in Notepad++ with correct run commands and comments.

The focus of this assignment is on good practice and professionalism, as highlighted throughout term. Files must adhere to appropriate industry standards and conventions. See good practice notes provided.

Achievement:
Students aiming for a good grade must submitted the required completed work to a high standard. You should aim to demonstrate a range of skills, methods and approaches, within the boundaries of good practice and standard conventions. ie if there are 2 methods of achieving a task, both good practice, you will get more marks for demonstrating both methods, eg inserts with and without column lists.

An exceptional grade cannot be achieved if the basics are not correct or are of poor quality. In addition to completing the requirements demonstrate consideration and submit a comprehensive and quality assignment.

See below for tasks details.

1. create_yourgroupnumber.txt (eg create_10.txt)
This file should contain:
 Create table commands for all tables
 Tables should be in the correct order to maintain integrity
 Include:
 Any changes to the attributes you consider important
 Any not null constraints and defaults you consider important for integrity
 Any sequencing you consider useful

2. constraint_ yourgroupnumber .txt
This file should contain:
 Alter table… add constraint commands for all tables
 Constraints should enforce integrity
 Constraints should be in the correct order to maintain integrity
 Any validation or defaults you consider relevant

3. insert_ yourgroupnumber.txt
This file should contain:
 Insert commands for all tables
 Minimum of 5 records should be inserted as test data for each table
 Inserts should be in the correct order to maintain integrity
 Inserts should be appropriate to support queries in task 5
 Inserts should demonstrate range of skills and apply methods used in task 1 and 2

4. drop_ yourgroupnumber.txt
This file should contain:
 Drop table, constraints and sequencing commands for all structures
 Drops should be in the correct order to maintain integrity
 Any additional commands you consider important

5. queries_ yourgroupnumber.txt
This file should contain the following as a minimum. You will need to consider the ERM:
 Queries to achieve the following data:
a) All the records from one table
b) A projection with 3 columns
 data returned sorted in reverse alphabetical order
c) Restriction queries with multiple clauses demonstrating the following
 a predicate eg for people who live in Northampton
 a condition matching a pattern eg beings with M
 a negative condition predicate eg do not live in Northampton
 a date range condition eg last year January – December
d) Join queries with pertinent data from
 2 tables
 3 tables
e) Bonus: Extra query demonstrating additional or integrated skills eg b, c and d

Additional: You may wish to include the following:
Additional statements and queries to check the databases development
A run file to execute the complete database

DELIVERABLES
This assessment requires you to submit the following:

 Completed Table Specifications
 Runable script files

 You should use SQL and Oracle to produce your database
 Submission should be in the appropriate format eg .txt or .sql files
 Deliverables do not include additional database documentation
 All submissions should be through the module Blackboard ‘Submit Your Work’ section
 All practical elements of this assessment are in groups
Group submission only require one submission per group

 


9) 768715-IT data bases

Assignment Description

This assignment is centered on ER modelling, a graphical tool used in database design and Normalization which is a text based tool to remove unnecessary redundancy in a database. At the completion of these assessments students will be able to:
1. Identify the different components of an ERD
2. Recognize some business rules from the relationships contained in an ERD
3. Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and their for the
linking of tables in the RDM ((relational data model)
4. To develop relational models (RDM) where each table has a primary key (entity integrity) and some
tables may have foreign keys (referential integrity)

Assessment Instructions
1. Convert M:N relationships into 1:M and M:1 relationships
2. Convert the conceptual ER model , given a problem scenario, into a RDM with appropriate primary
keys (PKs) and foreign keys (FKs)
3. Only use Crow’s Foot notation
4. Draw dependency diagram to use the 1NF, 2NF and 3NF

Submission Instructions
1. Must be submitted to Moodle
2. ALL components of the assignment must be done in MS Word, including the ER diagram (If
you are using Visio or some other tool export the image/snip the image and insert it into MS
Word).
BIT231 Database Systems Assessment 1 – Design Report
Melbourne Polytechnic Page 2 of 9
Airlines Case Study
MPoly Airlines has asked you to create a database for their airline operations i.e. its flight and airplane
history.

The database requirements are as follows.
A MPoly Airline flight is uniquely identified by the combination of a flight number and a date. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on a MPoly flight has a unique passenger number plus their name, address, and telephone number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number, first name, last name, date of birth, and date of hire. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet.
Each airplane has a unique serial number, a model, manufacturer name, passenger capacity, and year built.
A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.
MPoly Airline also wants to maintain data about its airplanes’ maintenance history. A maintenance
procedure has a unique procedure number, a procedure name and the frequency with which it is to be
performed on every airplane. For each such event it wants to know the date of the event, a maintenance
location and the duration.

Tasks List
There are multiple tasks in the assessment
Task 1: Knowledge test (5%)
The knowledge test – Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modeling and Normalization.
BIT231 Database Systems Assessment 1 – Design Report Melbourne Polytechnic Page 3 of 9

Task 2 : Database Modelling and Implementation (130 marks):
Question 1 – ER Modelling (60 marks):
1. Business rules – Write Business rules to create ERD (15 marks)
2. Entities and Attributes – List Entities, Attributes – Include all attributes that you believe would
be useful (10 marks)
3. ER Diagram – Design an ER diagram for the above case study. (35 marks)
o Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software.
Hand-drawn diagrams will not be accepted
o Only use “crow’s foot notation”
o ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.
o When completed, insert your ER Diagram into a Word document either by taking a
screenshot of the diagram and pasting it in the word document, or by exporting the diagram
as an image and the inserting it in the word document

Question 2 – Data Dictionary (20 marks):
 Create a data dictionary for the above ER Diagram using the following sample format.
 All data items must have suitable data types and all tables must have appropriate primary key and
foreign key
BIT231 Database Systems Assessment 1 – Design Report
Melbourne Polytechnic Page 4 of 9

Question 3 – Dependency Diagram (10 marks):
 Draw dependency diagram (using the following sample format) for your final ER Diagram, in the
MS Word document, showing where necessary that you have progressed through to 3NF.

Question 4 – SQL Statements – DDL and DML (40 marks):
Based on the specification you have provided in the data dictionary:
i. CREATE TABLE – Write the SQL code to create all tables (in the ER Diagram and Data
dictionary) to implement the relational data logical model (in ONE script). [10 marks deducted for
code that does not work!] 15
ii. Constraints – In part i, define primary keys, foreign keys and NOT NULL constraints in the
CREATE TABLE statement ONLY. [Note: a foreign key constraint requires the existence of the
referenced table]. (10 marks)
iii. DEFAULT clause – In part I, choose an appropriate column to use the DEFAULT clause. Explain
what is meant by DEFAULT and why this column is suitable to take such values. (5 marks)
iv. Write INSERT INTO statements to populate each table. (at least 3 rows per table) (10 marks)

 


10) 749824-data system and integration
       750975- data and system integration
       697992- data and system integration
       734763- data and system integration
       733687- data and system integration
       732331- data and system integration
       730515- data and system integration

 

Assignment Description

This assignment consists of two deliverables, being: demonstration code 75%, and a report
25%. All code relating to this assignment should be contained within a folder named: Task 2
– Your Name – Student Number, the folder is then to be zipped and uploaded to blackboard.
The report must be uploaded separately.
Important Note: For ease of demonstration, your code must be self-contained. In addition to using
Standards based HTML and Python, PETL, Bottle and Parser are the only additional frameworks you
should need. If you use others they must be provided and not require installation.
Demonstration Code
To demonstrate your code, you have been provided with four data sources compiled with
fictitious sample data. These are:
• “clinics.csv” contains the information about the clinics,
• “cliniclocations.xml” contains the location coordinates for each clinic
• “services.csv” contains a list of clinical services
• “clinicservices.csv” contains a list of clinics and the services they offer

Task 1 – Data integration demo.
You are to submit a python script named “data_merger.py”.
Your script should firstly clean the data then merge it into one CSV file named
“clinicservicelocations.csv”.
Task 1.1 Data Cleansing (6 marks)
Clean data in the email address field from “clinics.csv” such that:
• All spaces are removed,
• The domain “myclinic.com.au” is appended to those address without a domain,
Task 1.2 Data Merge (15 marks)
Merge the data from the four data sources into “clinicservicelocations.csv” with the
following fields (attributes):
• ClinicServicesID a unique field identifying each record from clinicservices
• ServiceID a foreign key linking to services from clinicservices
• Service the linked title of the service from services
• ClinicID a foreign key linking to clinics and locations
• Clinic the name of the clinic from clinics
• Suburb from clinics
• State from clinics
• Email cleaned email address from clinics
• Lat from cliniclocations
• Lon from cliniclocations
ICT705 Data and System Integration – Assignment 2 Page 5 of 9

Task 2 – RESTful Web Service Demo
You are to submit a python script named “clinic_locator.py”.
For tasks 2.1 to 2.2 Your script should build a RESTful web service that supports a ‘getclinics’
query from the client browser similar to “/getclinics?serviceid=x”. The server Web Service
will return a JSON table with the following fields (attributes): Clinic, Service, Suburb, State,
Email, Lat, Lon. Data is to come from the “clinicservicelocations.csv” you built in task 1.2.
Task 2.1 Return clinics for a service (6 marks)
The server will return a table of records (tuples) of clinics offering the service ID passed to it.
Task 2.2 Return all unique clinics (6 marks)
In the advent of a call to “getclinics?serviceid=0”, the server will return a distinct list of
clinics offering any service. That is a list of all clinics listed once.
Task 2.3 List of Services (6 marks)
To populate your drop-down list in task 3.1 you will need to return a JSON table of all
records from “services.csv” with the following fields (attributes): ServiceID, Service. The
server should respond to a “getservices” request.
Task 2.4 Error Handling (6 marks)
Your code should handle any errors including incorrect requests.

Task 3 Mashup Demo
You are to submit a HTML file “clinic_map.html” and optionally a CSS file named
“clinic_map.css”.
Task 3.1 Form Layout (10 marks)
You are to create an input form that accepts user input for a medical service and a
postcode. The input for the medical service should be a drop-down list with the first item of
being “Any Service” with a value = 0 remaining items should be created dynamically from a
call to getservices from task 2.3. Note: The input for the postcode should be a text box.
These inputs should be accompanied with descriptors and a “Locate Clinics” button.
Task 3.2 Google Map (10 marks)
Clicking the “Locate Clinics” button the will trigger a “getclinics” web service to retrieve a
table of clinics matching the service with attributes described in task 2. From this data you
are to display a Google map marking the position of the returned clinics. The map should be
centred on the input postcode and zoomed to an appropriate level to show nearby clinics,
alternatively you may choose to use marker clustering which automatically centres and
zooms map based on clustering.
Task 3.3 Information Window Demographics (5 marks)
Upon clicking a marker bubble on the map, the user will be displayed an information
window containing: Clinic Name, Suburb, State and a clickable email address.
ICT705 Data and System Integration – Assignment 2 Page 6 of 9
Task 3.4 Information window email (5 marks)
Clicking an email address link in the information window should launch the user’s email
application and automatically insert the subject line “Clinic Enquiry”.

Report
You are required to write a report explaining the key concepts around the design and
implementation of your demonstration system. You are also required to explain how to run
your code. Finally, you are to include all code submitted in the appendices to the report,
note that the code will not count towards the word count.
Your report should follow the following template:
Executive Summary
Table of Contents
Table of Figures (if needed)
Table of Tables (if needed)
1.0Introduction
2.0Key System Concepts
2.1 Data merging and cleaning
2.2 RESTful Web Services
2.3 Mashups
3.0Demo Running Instructions
4.0Conclusion
Bibliography
Appendices

 


11) 756123- Web Application Development

Assignment Description

For this assignment you will hand in two web applications. They will look identical from the front-end, but be built with two different stacks.

The first stack will use Flask + MongoDB (and a middle, data access layer such as MongoEngine).

The second stack will use Node.js + Postgres (and a middle, data access layer such as Sequelize).

You will be visualising 5 data dimensions from the https://www.gapminder.org/data/ site. These can be things like country, year, population, national debt, etc, etc… There are typically about 3 dimensions in each file, so you’ll probably need to use at least 2 files.

You need to use d3.js to display your visualisations.

You need to read your data from your databases (e.g. not straight from the csv file).

You will use Bootstrap for the base CSS, but any modifications need to be done with SASS (and then compiled to CSS).

I need to see some evidence of AJAX and APIs in your app… the most obvious way to do this is to simply load your data via an API route, through an AJAX call…

The apps need to be deployed on the same server… so run them on different ports (maybe use “screen” to achieve this).

You’ll need to hand in your code, and the live app.

 


12) 671452-database

Assignment Description

Task 1: Database Modelling (Total marks: 50%)

1.1. Develop an Entity Relationship (ER) model to characterize the information requirements of business illustrates in the case study mentioned above. The diagram of your ER model must: -Marks: 25%

A. Demonstrate all necessary entities
B. Demonstrate all attributes
C. The relationships between entities
D. Demonstrate unique identifiers and nature of interaction among entities

1.2. Discovered business rules for your ERD -Marks: 5%
1.3. Map the Entity Relationship Diagram (ERD) into a set of relations in at least Third Normal Form (3NF). You should show that each relation satisfies the rules for 1NF, 2NF, and 3NF. -Marks: 20%

For example STUDENT (StudentID, FirstName, SecondName, Phone). This relation is in 1NF as it does not contain any repeating groups and show primarykey.

Data dictionary:

Your report you must also include a data dictionary. You should briefly describe all entities and attributes are included, descriptions are clear, reasonable field formats, etc. For example,

Attributes Meaning Data Type Other
Student No. A unique identifier of Students Integer(8) Index – Yes
(No Duplicate)
Given Name First name of students Text(20) Example,
“Reuben”
Family Name Family name of students Text(20) Example,
“Steel”
Phone No. Phone no. of students Integer(12) Not Null
Street No. Street no. of students Integer(6) Not Null
City City where students live Text(10) Example,
“Melbourne”
State State where student belong Integer(6) Example,
“Victoria”
Post code Postcode of students Integer(8)
Not Null
Job Type Job type of students Text(15) Example,
“Part –Time”
Positions Positions of Students Text(20) Example,
“Mentoring”
Roaster Schedule of work Date & Time DD/MM/YYYY
Facility Type Type of facility Text(20) Example,
“pool-table”

Task 2: Database Implementation (Total Marks: 50%)

A. Implement your data dictionary to create a relational database in PhPMyAdmin and WordPress.

B. Populate the database with sample data. Adequate sample data must be included to show that the queries, view(s), and report(s) work properly

C. Build the following three forms and two reports, requiring a separate query to assure every of the following information needs:

1. A Student information entry form
2. A form to allow the director to change Member details and update the job and roster information without losing data integrity
3. A report that prints the students address details
4. Form for the director to fill in the jobs for the up-coming week with the volunteers available
5. A report that lists all of the people with particular dietary requirements so that this can be given to the chef before social functions

Grades Description

Grade Description
A Confirmed a clear understanding of the concepts and issues applicable to the tasks, and their wider implications, in a substantiated view that was organised and cohesive. Demonstrated a very high level of scholarship. Used fluent and persuasive language with assurance and precision, to achieve a highly effective communication with the reader.
B Demonstrated understanding of the complexities of the concepts and issues relevant to the tasks. Achieved effective selection and ordering of ideas. Demonstrated a high level of scholarship. Presented a view on the proposition with confidence, using fluent and persuasive language
C Recognized major aspects of the tasks. Demonstrated an adequate level of scholarship. Achieved a coherent ordering of ideas and expressed a view in clear and accurate language
D Recognized some aspects of the tasks. Demonstrated some evidence of scholarship. Presented some ideas relevant to the proposition. Demonstrated adequate organisation and language control.

 


13) 7005-Data Design

Assignment Description

4.3 A logical level business function (process) to physical design technique matrix that cross references the processes defined in your functional decomposition diagram with the seven physical design techniques (de-normalisation (one-to-one, one-to-many with reference data, associative entity with non-key attributes, and the duplication of non-key attributes), indexing and partitioning (both vertical and horizontal)). Please state clearly which physical design techniques cannot be used and why they cannot be used in the application.

4.4 Considerations for using those physical design techniques that you have identified (in your business function (process) to physical design technique matrix) as being possible to use to improve the data retrieval performance of the nine processes. This section should be the largest and most detailed part of your report. You will need one subsection for each of the nine processes in the application. In each subsection you will examine each of the physical design techniques that you can use for that process and discuss how it can be used, discuss the benefits of using it, discuss the disadvantages of using it (with reference to other processes), and finally discuss whether or not you will use it.

4.5 Final ERD (and a set of relations in standard relation notation) that reflect that any changes you have made to the entities and/or the attributes. If a relation has a foreign key, state the appropriate on-delete action in the child table.

4.6 The SQL queries needed to implement the data retrieval operational business rules for each process.

4.7 Two re-written data usage maps – one for each of the two most important processes.

4.8 A data dictionary, containing details about each table for the case study (e.g. field name, field description, data type, size, domain, range, example, required, indexed, primary key, foreign key, format and default value), organised for easy reference. The data dictionary must reflect the Final ERD.


14) 660124-Database Management System

Assignment Description
Part A – Database and Tables
1. Create a database called Cus_Orders.
2. Create a user defined data types for all similar Primary Key attribute columns (e.g. order_id, product_id,title_id), to ensure the same data type, length and null ability. See pages 12/13 for specifications.
3. Create the following tables (see column information on pages 12 and 13 ):customers
orders
order_details
products
shippers
suppliers
titlesThe database diagram is shown on the preceding page4. Set the primary keys and foreign keys for the tables.5. Set the constraints as follows:customers table – country should default to Canadaorders table – required_date should default to today’s date plus ten daysorder details table – quantity must be greater than or equal to 1products table – reorder_level must be greater than or equal to 1
– quantity_in_stock value must not be greater than 150suppliers table – province should default to BC6. Load the data into your created tables using the following files:customers.txt into the customers table (91 rows)
orders.txt into the orders table (1078 rows)
order_details.txt into the order_details table (2820 rows)
products.txt into the products table (77 rows)
shippers.txt into the shippers table (3 rows)
suppliers.txt into the suppliers table (15 rows)
titles.txt into the titles table (12 rows)
employees.txt into the employees table which is created in Part C (See Note)A load script (bulk insert tables.txt) has been supplied for this purpose. Load the Content of this file into a new Query Window and execute to load the data.The load script assumes that the data will be picked up from:
C:\Textfiles\ – so make sure that your data is here. When I run your script,
I will make sure that the data is also there. So it is important that you stick to this.Notes:
The first part of the load script, loads data into an employee file which is created in Part C. You will need to remove this part of the script for Part A and use it in Part C, once the employee table has been created. See Part C for more information.

By default, BULK INSERT (used to import the data) does not execute CHECK constraints and so it will allow data to be loaded which violates these constraints.
This is the case for some of the data you have been given. For our purposes this is OK.

Part B – SQL Statements

1. List the customer id, name, city, and country from the customer table. Order the result set by the customer id. The query should produce the result set listed below.

customer_id name city country
————— ———————————- ————- —————
ALFKI Alfreds Futterkiste Berlin Germany
ANATR Ana Trujillo Emparedados y helados México D.F. Mexico
ANTON Antonio Moreno Taquería México D.F. Mexico
AROUT Around the Horn London United Kingdom
BERGS Berglunds snabbköp Luleå Sweden

WHITC White Clover Markets Seattle United States
WILMK Wilman Kala Helsinki Finland
WOLZA Wolski Zajazd Warszawa Poland

(91 row(s) affected)

2. Add a new column called active to the customers table using the ALTER statement. The only valid values are 1 or 0. The default should be 1.

3. List all the orders where the order date is sometime in January or February 2004. Display the order id, order date, and a new shipped date calculated by adding 7 days to the shipped date from the orders table, the product name from the product table, the customer name from the customer table, and the cost of the order. Format the date order date and the shipped date as MON DD YYYY. Use the formula (quantity * unit_price) to calculate the cost of the order. The query should produce the result set listed below. (306 rows should be returned)

4. List all the orders that have not been shipped. Display the customer id, name and phone number from the customers table, and the order id and order date from the orders table. Order the result set by the order date. The query should produce the result set listed below. (21 row(s) affected)

5. List all the customers where the region is NULL. Display the customer id, name, and city from the customers table, and the title description from the titles table. The query should produce the result set listed below.

customer_id name city description
————– ———————————– ————— ———————-
ALFKI Alfreds Futterkiste Berlin Sales Representative
ANATR Ana Trujillo Emparedados y helados México D.F. Owner
ANTON Antonio Moreno Taquería México D.F. Owner
AROUT Around the Horn London Sales Representative
BERGS Berglunds snabbköp Luleå Order Administrator

WARTH Wartian Herkku Oulu Accounting Manager
WILMK Wilman Kala Helsinki Owner/Marketing Assistant
WOLZA Wolski Zajazd Warszawa Owner

(60 row(s) affected)

6. List the products where the reorder level is higher than the quantity in stock. Display the supplier name from the suppliers table, the product name, reorder level, and quantity in stock from the products table. Order the result set by the supplier name. The query should produce the result set listed below.

supplier_name product_name reorder_level quantity_in_stock
———————— ——————— —————- —————–
Armstrong Company Queso Cabrales 30 22
Cadbury Products Ltd. Ipoh Coffee 25 17
Cadbury Products Ltd. Røgede sild 15 5
Campbell Company Gnocchi di nonna Alice 30 21
Dare Manufacturer Ltd. Scottish Longbreads 15 6

Steveston Export Company Gravad lax 25 11
Steveston Export Company Outback Lager 30 15
Yves Delorme Ltd. Longlife Tofu 5 4

(18 row(s) affected)

7. Calculate the length in years from January 1, 2008 and when an order was shipped where the shipped date is not null. Display the order id, and the shipped date from the orders table, the customer name, and the contact name from the customers table, and the length in years for each order. Display the shipped date in the format MMM DD YYYY. Order the result set by order id and the calculated years. The query should produce the result set listed below.

order_id name contact_name shipped_date elapsed
———– ————————— ————————— ———— ——-
10000 Franchi S.p.A. Paolo Accorti May 15 2001 7
10001 Mère Paillarde Jean Fresnière May 23 2001 7
10002 Folk och fä HB Maria Larsson May 17 2001 7
10003 Simons bistro Jytte Petersen May 24 2001 7
10004 Vaffeljernet Palle Ibsen May 20 2001 7

11066 White Clover Markets Karl Jablonski Mar 28 2004 4
11067 Drachenblut Delikatessen Sven Ottlieb Mar 30 2004 4
11069 Tortuga Restaurante Miguel Angel Paolino Mar 30 2004 4

(1057 row(s) affected)

8. List number of customers with names beginning with each letter of the alphabet. Ignore customers whose name begins with the letter F or G. Do not display the letter and count unless at least six customer’s names begin with the letter. The query should produce the result set listed below.

9. List the order details where the quantity is greater than 100. Display the order id and quantity from the order_details table, the product id and reorder level from the products table, and the supplier id from the suppliers table. Order the result set by the order id. The query should produce the result set listed below.

order_id quantity product_id reorder_level supplier_id
———– ———– ————– —————– ———–
10193 110 43 25 10
10226 110 29 0 12
10398 120 55 20 15
10451 120 55 20 15
10515 120 27 30 11

10895 110 24 0 10
11017 110 59 0 8
11072 130 64 30 12

(15 row(s) affected)

10. List the products which contain tofu or chef in their name. Display the product id, product name, quantity per unit and unit price from the products table. Order the result set by product name. The query should produce the result set listed below.

product_id name quantity_per_unit unit_price
————– ————————————- ———————- ————–
4 Chef Anton’s Cajun Seasoning 48 – 6 oz jars 22.0000
5 Chef Anton’s Gumbo Mix 36 boxes 21.3500
74 Longlife Tofu 5 kg pkg. 10.0000
14 Tofu 40 – 100 g pkgs. 23.2500

(4 row(s) affected)

Part C – INSERT, UPDATE, DELETE and VIEWS Statements

1. Create an employee table with the following columns:

Column Name Data Type Length Null Values
employee_id int No
last_name varchar 30 No
first_name varchar 15 No
address varchar 30
city varchar 20
province char 2
postal_code varchar 7
phone varchar 10
birth_date datetime No

2. The primary key for the employee table should be the employee id.

3. Load the data into the employee table using the employee.txt file; 9 rows. In addition, create the relationship to enforce referential integrity between the employee and orders tables.

4. Using the INSERT statement, add the shipper Quick Express to the shippers table.

5. Using the UPDATE statement, increate the unit price in the products table of all rows with a current unit price between $5.00 and $10.00 by 5%; 12 rows affected.

6. Using the UPDATE statement, change the fax value to Unknown for all rows in the customers table where the current fax value is NULL; 22 rows affected.

7. Create a view called vw_order_cost to list the cost of the orders. Display the order id and order_date from the orders table, the product id from the products table, the customer name from the customers tble, and the order cost. To calculate the cost of the orders, use the formula (order_details.quantity * products.unit_price). Run the view for the order ids between 10000 and 10200. The view should produce the result set listed below.

order_id order_date product_id name order_cost
——— —————————— ————– ———————————- ————–
10000 2001-05-10 00:00:00.000 17 Franchi S.p.A. 156.0000
10001 2001-05-13 00:00:00.000 25 Mère Paillarde 420.0000
10001 2001-05-13 00:00:00.000 40 Mère Paillarde 736.0000
10001 2001-05-13 00:00:00.000 59 Mère Paillarde 440.0000
10001 2001-05-13 00:00:00.000 64 Mère Paillarde 498.7500

10199 2002-03-27 00:00:00.000 3 Save-a-lot Markets 400.0000
10199 2002-03-27 00:00:00.000 39 Save-a-lot Markets 720.0000
10200 2002-03-30 00:00:00.000 11 Bólido Comidas preparadas 588.0000

(540 row(s) affected)

8. Create a view called vw_list_employees to list all the employees and all the columns in the employee table. Run the view for employee ids 5, 7, and 9. Display the employee id, last name, first name, and birth date. Format the name as last name followed by a comma and a space followed by the first name. Format the birth date as YYYY.MM.DD. The view should produce the result set listed below.

employee_id name birth_date
—————- ——————————– ————
5 Buchanan, Steven 1955.03.04
7 King, Robert 1960.05.29
9 Dodsworth, Anne 1966.01.27

(3 row(s) affected)

9. Create a view called vw_all_orders to list the columns shown below. Display the order id and shipped date from the orders table, and the customer id, name, city, and country from the customers table. Run the view for orders shipped from August 1, 2002 and September 30, 2002, formatting the shipped date as shown. Order the result set by customer name and country. The view should produce the result set listed below.

(51 row(s) affected)

10. Create a view listing the suppliers and the items they have shipped. Display the supplier id and name from the suppliers table, and the product id and name from the products table. Run the view. The view should produce the result set listed below, although not necessarily in the same order.

supplier_id supplier_name product_id product_name
————— —————————————- ————– ——————————
9 Silver Spring Wholesale Market 23 Tunnbröd
11 Ovellette Manufacturer Company 46 Spegesild
15 Campbell Company 69 Gudbrandsdalsost
12 South Harbour Products Ltd. 77 Original Frankfurter grüne Soße
14 St. Jean’s Company 31 Gorgonzola Telino

7 Steveston Export Company 63 Vegie-spread
3 Macaulay Products Company 8 Northwoods Cranberry Sauce
15 Campbell Company 55 Pâté chinois

(77 row(s) affected)

Part D – Stored Procedures and Triggers

1. Create a stored procedure called sp_customer_city displaying the customers living in a particular city. The city will be an input parameter for the stored procedure. Display the customer id, name, address, city and phone from the customers table. Run the stored procedure displaying customers living in London. The stored procedure should produce the result set listed below.

customer_id name address city phone
————— ———————— ———- ————————————- —— ————
AROUT Around the Horn 120 Hanover Sq. London (71) 555-7788
BSBEV B’s Beverages Fauntleroy Circus London (71) 555-1212
CONSH Consolidated Holdings Berkeley Gardens 12 Brewery London (71) 555-2282
EASTC Eastern Connection 35 King George London (71) 555-0297
NORTS North/South South House 300 Queensbridge London (71) 555-7733
SEVES Seven Seas Imports 90 Wadhurst Rd. London (71) 555-1717

(6 row(s) affected)

2. Create a stored procedure called sp_orders_by_dates displaying the orders shipped between particular dates. The start and end date will be input parameters for the stored procedure. Display the order id, customer id, and shipped date from the orders table, the customer name from the customer table, and the shipper name from the shippers table. Run the stored procedure displaying orders from January 1, 2003 to June 30, 2003. The stored procedure should produce the result set listed below.

order_id customer_id customer_name shipper_name shipped_date
———- ————— ————————————- ——————– ———————-
10423 GOURL Gourmet Lanchonetes Federal Shipping 2003-01-18 00:00:00.000
10425 LAMAI La maison d’Asie United Package 2003-01-08 00:00:00.000
10427 PICCO Piccolo und mehr United Package 2003-01-25 00:00:00.000
10429 HUNGO Hungry Owl All-Night Grocers United Package 2003-01-01 00:00:00.000
10431 BOTTM Bottom-Dollar Markets United Package 2003-01-01 00:00:00.000

10615 WILMK Wilman Kala Federal Shipping 2003-06-30 00:00:00.000
10616 GREAL Great Lakes Food Market United Package 2003-06-29 00:00:00.000
10617 GREAL Great Lakes Food Market United Package 2003-06-28 00:00:00.000

(188 row(s) affected)

3. Create a stored procedure called sp_product_listing listing a specified product ordered during a specified month and year. The product and the month and year will be input parameters for the stored procedure. Display the product name, unit price, and quantity in stock from the products table, and the supplier name from the suppliers table. Run the stored procedure displaying a product name containing Jack and the month of the order date is June and the year is 2001. The stored procedure should produce the result set listed below.

product_name unit_price quantity_in_stock supplier_name
——————————————— ————- ——————— ———————————–
Jack’s New England Clam Chowder 10.1325 85 Silver Spring Wholesale Market
Jack’s New England Clam Chowder 10.1325 85 Silver Spring Wholesale Market
Jack’s New England Clam Chowder 10.1325 85 Silver Spring Wholesale Market
Jack’s New England Clam Chowder 10.1325 85 Silver Spring Wholesale Market

(4 row(s) affected)

4. Create a DELETE trigger on the order_details table to display the information shown below when you issue the following statement:

DELETE order_details
WHERE order_id=10001 AND product_id=25

You should get the following results:

5. Create an INSERT and UPDATE trigger called tr_check_qty on the order_details table to only allow orders of products that have a quantity in stock greater than or equal to the units ordered. Run the following query to verify your trigger.

UPDATE order_details
SET quantity = 30
WHERE order_id = ‘10044’
AND product_id = 7

6. Create a stored procedure called sp_del_inactive_cust to delete customers that have no orders. The stored procedure should delete 1 row.

7. Create a stored procedure called sp_employee_information to display the employee information for a particular employee. The employee id will be an input parameter for the stored procedure. Run the stored procedure displaying information for employee id of 7. The stored procedure should produce the result set listed below.

(1 row(s) affected)

8. Create a stored procedure called sp_reorder_qty to show when the reorder level subtracted from the quantity in stock is less than a specified value. The unit value will be an input parameter for the stored procedure. Display the product id, quantity in stock, and reorder level from the products table, and the supplier name, address, city, and province from the suppliers table. Run the stored procedure displaying the information for a value of 5. The stored procedure should produce the result set listed below.

product_id name address city province qty reorder_level
————– ———————————- ———————— —————— ——– — ————-
2 Edward’s Products Ltd. 1125 Howe Street Vancouver BC 17 25
3 Edward’s Products Ltd. 1125 Howe Street Vancouver BC 13 25
5 New Orlean’s Spices Ltd. 1040 Georgia Street West Vancouver BC 0 0
11 Armstrong Company 1638 Derwent Way Richmond BC 22 30
17 Steveston Export Company 2951 Moncton Street Richmond BC 0 0

68 Dare Manufacturer Ltd. 1603 3rd Avenue West Burnaby BC 6 15
70 Steveston Export Company 2951 Moncton Street Richmond BC 15 30
74 Yves Delorme Ltd. 3050 Granville Street New Westminster BC 4 5

(23 row(s) affected)

9. Create a stored procedure called sp_unit_prices for the product table where the unit price is between particular values. The two unit prices will be input parameters for the stored procedure. Display the product id, product name, alternate name, and unit price from the products table. Run the stored procedure to display products where the unit price is between $5.00 and $10.00. The stored procedure should produce the result set listed below.

product_id name alternate_name unit_price
————– ——————————— ——————————– ————–
13 Konbu Kelp Seaweed 6.30
19 Teatime Chocolate Biscuits Teatime Chocolate Biscuits 9.66
23 Tunnbr÷d Thin Bread 9.45
45 R°gede sild Smoked Herring 9.975
47 Zaanse koeken Zaanse Cookies 9.975
52 Filo Mix Mix for Greek Filo Dough 7.35
54 TourtiÞre Pork Pie 7.8225
75 Rh÷nbrõu Klosterbier Rh÷nbrõu Beer 8.1375

(8 row(s) affected)


15) 735294-Date base

Assignment Description
Q1 (8 marks)
You may use any combination of views, SQL functions and PLpgSQL functions in this question.
However, you must define at least a PLpgSQL function called Q1.
Please write a PLpgSQL function Q1(course_id integer) that takes a course_id as parameter and outputs two numbers: (1) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course (i.e., rooms.capacity >= the total number of students enrolled in this course); (2) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course and also carry all the students in the enrolment waitlist of this course (i.e., rooms.capacity >= the total number of students enrolled in this course + the total number of students in the enrolment waitlist of this course).
You should use the following type definition and function header:
create type RoomRecord as (valid_room_number integer,
bigger_room_number integer);
create or replace function Q1(course_id integer) returns setof
RoomRecord…
Note:
(1). If a given course_id is not a valid UNSW course id, please throw an exception ‘INVALID
COURSEID’.
(2). All the students enrolled (in a given course) should be counted even though his/her mark is null.
Sample results (details can be found in check.sql):
proj2=#select * from q1(52491);
Q2 (8 marks)
You may use any combination of views, SQL functions and PLpgSQL functions in this question.
However, you must define at least a PLpgSQL function called Q2.
Please write a PLpgSQL function Q2(staff_id integer) that takes a staff’s id as parameter and returns all teaching records of the given staff. Each transcript tuple should contain the following information:
cid, term, code, name, uoc, avg_mark, highest_mark, median_mark, totalEnrols.
You should use the following type definition for the transcript tuples:
create type TeachingRecord as (
cid integer, — course ID
term char(4), — semester code (e.g. 98s1)
code char(8), — UNSW-style course code (e.g. COMP1021)
name text, — corresponding subject name of the course
uoc integer, — units of credit of this course
average_mark integer, — the average of marks of this course
highest_mark integer, — the highest mark of this course
median_mark integer, — the median of marks of this course
totalEnrols integer, — the total number of students
enrolled in this course with non-null
mark
);
Note:
(1). If a given staff_id is not a valid UNSW staff id, please throw an exception ‘INVALID
STAFFID’.
(2). The average_mark and median_mark should be rounded to the nearest integer.
(3). A student whose mark is null should not be counted when you calculate the average_mark,
highest_mark, median_mark and totalEnrols.
(4). Ignore the teaching record where totalEnrols = 0.
(5). Return null value if the uoc is null.
You should use the following and function header:
create or replace function Q2(staff_id integer) returns setof
TeachingRecord…
Sample results (details can be found in check.sql):
proj2=#select * from q2(50413833);
Q3 (9 marks)
You may use any combination of views, SQL functions and PLpgSQL functions in this question.
However, you must define at least a PLpgSQL function called Q3.
Given the id of an organizational unit, please write a PLpgSQL function Q3 (org_id integer, num_courses integer, min_score integer) to help the UNSW administrative officers to find out all the students satisfying the following: (1) he/she has taken more than num_courses courses offered by the given organization (note that a student may fail a course and take it again, thus we need to count duplicate courses); (2) he/she has got score no less than min_score for at least one course offered by the given organization. Each tuple should include:
• His/Her unswid (should be taken from People.unswid)
• His/Her student_name (should be taken from People.name)
• His/Her course_records
course_records of a student is a concatenation of several records with ‘,’. Each record is about
a course he/she has taken and is offered by the given organization. Each record should include the
code of the course (Subjects.code), the name of the course (Subjects.name), the semester
that he/she has learned this course (Semesters.name), the name of the organization that offers
the course (OrgUnits.name), and the score he/she got (Course_enrolments.mark).
Note:
(1). If a given OrgUnits.id is not a valid UNSW organization id, please throw an exception
‘INVALID ORGID’.
(2). A given organization may have lots of sub-organizations. For example, the faculty of engineering
has 9 schools, such as biomedical engineering and CSE, and each school may have several divisions
or departments. You need to include all the sub-organizations recursively.
(3). Course records of a student should be sorted in descending order of score (null score value should always be sorted to the end and displayed as ‘null’). If two or more course records have the same score, they should be sorted in ascending order of Courses.id. Records should be concatenated by ‘\n’, so that each record will be displayed in a separate line.
(4). Please note that, for course_records, there is a space after each ‘,’. All text fields are verified with exact text matching.
(5). Only the first five course records sorted in above order should be displayed. If the course records of a student are less than five, all the course records should be displayed.

16) 755117- data analytics and business intelligence

Assignment Description
There 2 presentation thats need to be done.
1st presentation is only 5 slides. And the document explains what it should include. The second presentation should have 7-8 slides. The presentation will be about the assignment that will be done from the questions. You have to choose one of them. The data sets are in the moodle page of uni so i will provide the access.2 individual presentations (15 min.): mid June (12) and
late-June(26).
Each presentation to outline your thinking about the
coursework project. We will mark and provide feedback.
The first presentation should focus on the idea, the data you
need, linking and cleaning steps and theory background.
The second presentation should focus on revisions to the idea,
first descriptive results and any problems you are encountering.
. One 5000 word coursework on an analysis you do based on a
project from the Dataverse datasets.
The coursework structure is:
> Introduction
> Theory (short)
> Sources of Data, Types of Data
> Descriptive Analysis
> Analytical Results
> Conclusion
> R Code with comments

17) 754447- data science pratice

Assignment Description

1. Utilising Python 3 Build the following regression models:
 Decision Tree
 Gradient Boosted Tree
 Linear regression

2. Select a dataset (other than the example dataset given in section 3) and apply the Decision Tree and Linear regression models created above. Choose a dataset from Kaggle https://www.kaggle.com/datasets
3. Build the following in relation to the gradient boost tree and the dataset choosen in step 2
a) Gradient boost tree iterations (see section 6.1)
b) Gradient boost tree Max Bins (see section 7.2)
4. Build the following in relation to the decision tree and the dataset choosen in step 2
a) Decision Tree Categorical features
b) Decision Tree Log (see section 5.4)
c) Decision Tree Max Bins (see section 7.2)
d) Decision Tree Max Depth (see section 7.1)
5. Build the following in relation to the linear regression and the dataset choosen in step 2
a) Linear regression Cross Validation
i. Intercept (see section 6.5)
ii. Iterations (see section 6.1)
iii. Step size (see section 6.2)
iv. L1 Regularization (see section 6.4)
v. L2 Regularization (see section 6.3)
b) Linear regression Log (see section 5.4)
6. Follow the provided example of the Bike sharing data set and the guide lines in the sections that follow this section to develop the requirements given in steps 1,3,4 and 5


18) 750060-data base syatems

Assignment Description

Task 1

1.1. Develop an Entity Relationship (ER) model to characterize the information requirements of business illustrates in the case study mentioned above. The diagram of your ER model must:

A. Demonstrate all necessary entities

B. Demonstrate all attributes

C. The relationships between entities

D. Demonstrate unique identifiers and nature of interaction among entities

1.2. Discovered business rules for your ERD -Marks
1.3. Map the Entity Relationship Diagram (ERD) into a set of relations in at least Third Normal
Form (3NF). You should show that each relation satisfies the rules for 1NF, 2NF, and 3NF.

For example STUDENT (StudentID, FirstName, SecondName, Phone). This relation is in 1NF as it does not contain any repeating groups and show primarykey.

Data dictionary:

Your report you must also include a data dictionary. You should briefly describe all entities and attributes are included, descriptions are clear, reasonable field formats, etc. For example,

Data Other Attributes Meaning Type

Student No. A unique Integer(8) Index – Yes
identifier (No Duplicate)
of Students
Given Name First name Text(20) Example,
of students “Reuben”
Family Family Text(20) Example,
Name name of “Steel”
students
Phone No. Phone no. Integer(12) Not Null
of students
Street No. Street no. Integer(6) Not Null
of students
City City where Text(10) Example,
students “Melbourne”
live
State State Integer(6) Example,
where “Victoria”
student
belong
Post code Postcode Integer(8) Not Null
of students
Job Type Job type of Text(15) Example,
students “Part –Time”
Positions Positions Text(20) Example,
of Students “Mentoring”
Roaster Schedule Date & DD/MM/YYYY
of work Time
Facility Type of Text(20) Example,
Type facility “pool-table”

Task 2: Database Implementation

A. Implement your data dictionary to create a relational database in PhPMyAdmin.

B. Populate the database with sample data. Adequate sample data must be included to show that the queries, view(s), and report(s) work properly

C. Design the following three forms and two reports, requiring a separate query to assure every of the following information needs:

1. A Student information entry form

2. A form to allow the director to change Member details and update the job and roster information without losing data integrity

3. A report that prints the students address details

4. A form for the director to fill in the jobs for the up-coming week with the volunteers available

5. A report that lists all of the people with particular dietary requirements so that this can be given to the chef before social functions


19) 748944- Data analytics

Assignment Description

1. Develop the dataset for at least one product segment with at least 1000 records and with attributes such as
• product name,
• product price,
• shipping type (free or customer paid)
• monthly sales ($)
• geographic region
• No. Of customers who bought the product,
• Customer type (New or existing)
You are also allowed to add any more attributes to describe product segment
Note: You must develop your own unique and original dataset – copying a dataset from another student or from the internet will result in reduced or zero marks.
2. Research any specific data mining or classification technique and propose a suitable technique or model to determine any association or relationships among the attributes.
3. Develop a predictive model to predict monthly sales for a given geographic region. You can use any of the methods such as Naive Bayes, decision trees or linear regression. You are also welcome to do a comparative analysis of all the methods you come across in your research and use the comparative analysis to justify your approach and research findings.
4. Based on your analysis, present recommendations to the board for the following business problems:
• What is the most likely geographic region to target new customers to increase sales and profit?
• Which products should be prioritised for increase in sales?
• What will be impact on product sales if free shipping is provided to all products?
• Any new innovative ideas to improve company’s profitability supported by your data analytics.

Proposed Report Structure

The report should be structured using the following headings (2000 words)
1. Executive Summary
2. Table of Contents
3. List of Abbreviations and assumptions made
4. Introduction – What is the problem?
5. Research Methodology
6. Analytical Findings
7. Recommendations to the company
8. An implementation plan based on the recommendations you have provided
9. Conclusions
10. List of References
11. Appendix (E.g. Python code)


20) 751088- data visualisation

752472- Data Visualization and Exploration

Assignment Description
1. Identify which findings from the Data Exploration Project you wish to communicate and who the intended audience is. Be selective, you do not need to and should not communicate everything you found. The intended audience might be your classmates, general public or politicians or whoever you like.
2. Design a narrative visualisation to communicate your findings to the intended audience. It should allow some viewer interaction and be designed using the five-sheet design methodology.
3. Implement your visualisation as a web-based presentation using R or D3.
4. Presentation to the tutorial class [Week 12]
Submit final report and source code [Start Exams: 4/6/18]
Report & Final Product: At the start of the Exam Period you need to submit (through Moodle) a directory containing the implementation code for your narrative visualization together with a written report of no more than 15 pages that contains
1. Introduction: Precise description of what message you wanted your narrative visualisation to convey and who the intended audience is.
2. Design: Description of the visualization design process including the 5 design sheets detailing the alternatives you considered and the reasons for choosing your final design.
3. Implementation: Description of the implementation including libraries used and reasons for the implementation decisions for your narrative visualisation.
4. User guide: Instructions for viewing and exploring the narrative visualisation using a standard web browser and images showing how the visualization works.
5. Conclusion: Summarise what you achieved and a reflection on what you learnt in this project and what in hindsight you might have done differently to improve the result 6. Appropriate references and bibliography.

21) 7120-Database Work

Assignment Description
In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations.
The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study.
Your ERD must
• show all necessary entities, attributes and relationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes, if any
• show participation and cardinality
• show associative entities, if appropriate
• show weak entities, if any
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes as specified in the unit text bookSome business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ERD accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one policy because it is assumed that the business does not record customer details until the customer opts for a policy with them. To get yourself started, ask yourself, ‘If I were running this business, what things I need to keep a list of?’ Write those things down. For each thing, what information would you need to record about it? How can it be identified? The answers to these questions will help you to develop your ERD.2 Assumptions and Business rules
Provide assumptions and business rules relevant to your ERD.
3 Create Logical Design :
If you would like to perform this optional component of this assignment, you can map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide all the relations in the following format:
Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email)Invoice (InvoiceNumber, invoiceDate, Total, CustomerId)foreign key (CustomerId) references Customer(CustomerId)How and what to submit:You must make an electronic submission for this assignment using the appropriate assignment submission link in the unit web site.
You should submit a Microsoft Word document containing:
• A copy of your ERD.
• A list of business rules and assumptions including their justifications.
• 3NF Relations –
Your document should contain appropriate identifying information (your student number, name, unit number, etc.) and should contain appropriate headings for each section.Important points
• You must work individually on this assignment.
• The model solution to this assignment will be released approximately TWO (2) weeks after the due date.
• Assignments that are submitted after the release of the model solution will receive zero marks.
• You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as ‘This assignment was completed using only the COIT20247 unit materials’ to mention that you have used only the items provided by the unit COIT20247.
• Before submitting your assignment, review the marking criteria that are available in the assignment specification. Ensure that all the criteria are addressed in your submission.
• After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure that they are correct.
• Penalty related to late submission and plagiarism is handled as per the university policies.

22) 675941-Database Concepts

Assignment Description

The project skeleton, there are 9 files in which code is partially implemented, you have to complete that code. When you will open the folder namely “project skeleton” it has few folders in it. Inside that, there is a folder “src” open that. There are three folders inside it, You just need to open the folder namely “impl” and you can see 9 files over there. Those 9 files have incomplete code with labels we have to do that. I am attaching three files and giving you description below that which file will help you in which part of the code.

Files namely BPlusTree.HPP, HashTable.HPP and Query.CPP have label TODO’s starting with 3.X.X you can do that by using file 3.
Files namely ColumStore.Hpp, ColumStore.CPP, RowStore.Hpp, RowStore.Cpp and Query.CPP have label TODO’s starting with 1.X.X you can do that by using file 1.
Files namely Compression.Hpp, Compression.CPP and Query.CPP have label TODO’s starting with 2.X.X you can do that by using file 2.

I request you to do the code in the given order, first complete BPlusTree.HPP, HashTable.HPP and Query.CPP (having label TODO’s starting with 3.X.X) by using file 3. Then do code for ColumStore.Hpp, ColumStore.CPP, RowStore.Hpp, RowStore.Cpp and Query.CPP (having label TODO’s starting with 1.X.X) by using file 1. And in the last complete the code for Compression.Hpp, Compression.CPP and Query.CPP (having label TODO’s starting with 2.X.X) by using file 2.


23) 659659-database development management

Assignment Description

Create the following table and insert values in it according to following table.

Note: Employees is work table for these queries

Table: Employees

_________________________________
Employee First_Name Last_Name Dept Hire_Date Credit Phone Manager_id
Id Code Limit Ext
_________________________________

201 Susan Brown Exe 01-Jun-1998 $30.00 3484 (null)
202 Jim Kern Sal 16-Aug-1999 $25.00 8722 201
203 Martha Woods Shp 02-Feb-2004 $25.00 7591 201
204 Ellen Owens Sal 01-Jul-2003 $15.00 6830 202
205 Henry Perkins Sal 01-Mar-2000 $25.00 5286 202
206 Carol Rose Act null null null (null)
207 Dan Smith Shp 01-Dec-2004 $25.00 2259 203
208 Fred Campbell Shp 01-Apr-2003 $30.00 1752 203
209 Paula Jacobs Mkt 17-Mar-1999 $15.00 3357 201
210 Nancy Hoffman Sal 16-Feb-2004 $25.00 2974 203
________________________________

>> Write the query for the following tasks:

#1. List the following columns of the Employees table in this order:
first name, last name, hire_date, phone_ext and department code

Change the name of the hire_date column to Date_of_Joining within the result table. List the employees from the Sales and Marketing department only.

Sort the rows of the result table by the last_name column in descending order.

#2. Write a SQL statement to add a new employee Rick Adam to the Employees table. Rick joined the Accounting department.
His date of joining is 18th Sept, 2011. He works for manager with id: 203. He is allotted any phone extension – 3757.
His credit limit is set to $35.00
—–
SQL QUery:

#3. List the employees that have a phone number starting with 7 or whose names are starting with letter – ‘N’.
Show the employee_id, first_name, last_name and phone_ext. Sort the rows by employee_id.

—–
SQL QUery:

#4. Using the Employees table, list the following columns:
dept_code, credit_limit, last_name, first_name
Place the columns in that order. Sort the rows by :
dept_code in ascending order
credit_limit in descending order
last_name in ascending order.
And get only those employees who are having credit limits between 20 and 40.
——
Sql Query:

#5. From the Employees table, list the employee_id, first_name, last_name and credit_limit columns for employees
with the first names: Martha, Carol, Nancy
Sort the rows by the last name.
——
SQl Query:

#6. From the Employees table, get the department and credit limits for the employees ‘Jim Kern’.

——
SQl Query:

#7. List the department and phone extension for all the employees who don’t have a manager.
Sort the rows by the employee_id.
—-
Sql Query:
#8. Write a query to display distinct department codes credit_limits.

#9. Delete all the employees who work for Shipping Department or who have manager_id null.

—-
SQl Query:
#10. Increase the credit limits of all the employees by $15.00 who work for Accounting department.

—-
SQl Query:

#11. Create sequence to uniquely identify each row in the employees table. Employee_id for the employee table:
Start with 220; do not allow caching of the values. Name the sequence Employee_id Seq. Verify the existence of sequence in the Data Dictionary.

—-
SQL Query:

#12. Write a SQL statement to add a new employee John Adam to the Employees table. John joined the Accounting department.
His date of joining is 18th Sept, 2011. He works for manager with id: 203. He is allotted any phone extension – 3758.
His credit limit is set to $40.00. Use the sequence to uniquely identify the new employee.
—–
SQL QUery:

Task#1:

For each manager_id, list the number of employees ( except the Shipping Department ) each one manages.
Also list the range of their employees’s credit limits by showing the minimum and maximum.
Omit the employee having first name: Susan and last name: Brown

>> Sql Query:…….

Task#2:

For each department, find the sum of the credit_limits. Display only those departments
whose total credit limits are greater than $50.00 but less then $100.00.

>> Sql Query:

Task#3: For each manager_id between 202and 203, show the number of employees each manager supervises.
Show only those managers who manage more then 2 employees.

>> SQL Query:

Task#4: Find how many employees have joined between the year 2002 and 2005 in the accounting department.

Task#5: List all the employees, their hire dates and the number of days each person will have worked
for the company as of January 1, 2005.

Task#6: Find out in which month the employee – Carol Rose joined.

Sql Query:

Task#7: Write the Sql statement for the following:

a) Create a table named : Foods which has the following columns:

menu_item as numeric of size 2,
supplier_id as character type of 3,
product_code as character type of 2,
description varchar2(20),
price as numeric with scale and precision of 4, 2,
price_increase as numeric with scale and precision of 4, 2
Sql Query:

b) Rename the product_code to product_id.

Sql Query:

c) Add a check constraint to price column ensuring that price of all the items are less then $10.00

Sql Query:

d) Add a unique constraint to description column.

 


24) 185131-DBMS

Assignment Description

You have been commissioned to create a database for Flying High travel services (FHTS). FHTS is a new and specialised travel agency who cater for Australian companies with high travel needs. Their market niche is in low cost business travel packages by airlines. They keep their prices low by dealing directly with the airlines, who provide combined cheap flights and accommodation.
When a FHTS customer wishes to book business flights with accommodation, it is always done by the respective employees of that Australian company, who book online. This model is used to reduce administration costs.
When a booking is made, a FHTS staff member sources flights by airlines that contain the dates and destinations specified by the customers. Accommodation will always be included automatically in the final destination, this is provided by the airline. It may not always be the same accommodation, but is always of high quality to satisfy the customer’s comfort.
The following business rules and information gathered about the current business activities will allow you to derive entities. Your submission is not expected to have many to many relationships left unresolved. You may add entities or attributes as you see fit. Assumptions can be made to include further entities and their relationships, but referential integrity and normalisation processes must be adhered to. Reasons should be given for any relation that is not maintained in 3NF.

• A company (a business client of FHTS) is referenced by a unique company number, it has a company name, phone, fax, and email contact.
• A customer of FHTS is an employee of a company who is requiring the business travel services of FHTS. Each customer can only belong to one company. A customer will have a unique customer ID, first name, last name, mobile phone number, address, post code and email address. These details are needed in case travel arrangements change and they need to be notified.
• A customer may also be a VIP of FHTS. The level of the VIP status is dependent on the number of years and the amount of money that the customer has spent with FHTS. There are three levels of VIP status, these include Gold, Silver and Bronze. A customer VIP status will give a discount to the customer booking.
• A customer can also be a frequent flyer. As a frequent flyer, a customer may receive a discount on their flights. All airlines that FHTS deal with, recognise their customer’s frequent flyer status. The amount of a frequent flyer discount is only dependent on the amount of frequent flyer points they have accumulated.
• Also a frequent flyer will accumulate more points based on the number flying kilometres they accumulate. The number of kilometres travelled by a flight needs to be recorded.
• A customer can make many travel bookings with FHTS, but only one customer is allowed to be on a booking. FHTS records the date of a travel booking.
• There are many staff working at FHTS who take many bookings from the customers. However, only one staff member is allocated to each booking.
• A number of staff members are supervisors who supervise one or more other staff members. However, each staff member is supervised by only one supervisor.
• A staff member needs a unique ID, first name, last name, phone number, date of birth, start date, tax file number, home address, email address and a Supervisor ID.
• A booking also needs an attribute to state if there has been supervisor intervention for a respective booking. “Yes” or “No” is all that is required.
• A booking may contain one or more flights, by one or more airlines. Each flight has only one location departure and destination.
• A flight details include a unique ID, flight number, departure location ID and location name, departure date and time, destination location ID and location name, destination date and time.
• Each flight has cabin class fares. Cabin class fares are directly associated with the cabin classes. The cabin classes are first, business and economy. A customer may have different class fares for each booking i.e. for different flights, but only one class fare for each flight.
• A cabin class fare is set by each Airline. As FHTS deals with the Airlines directly, all class fares remain fixed across all Airlines. However the overall flight fare is determined by the cabin class fare and an additional flight fee charge. FHTS stores the flight fees.
• No flights will have stop overs, all flights will have final end locations (destinations). A location will have one or many flights that can be either departure or destination flights.
• A location may have one or many accommodation sites. However, a customer will only be allocated one accommodation site at their travel location destination.
• A location will need at least, a unique ID, name and the number of days for the stay.
• Information regarding an accommodation site is kept including, unique ID, name, address details, email address, date of arrival and date of departure.


25) 195621-DBMS

Assignment Description

• Every patient admitted has a unique patient id. If the patient is a koala, it will have
also have a koala tag. It may have a microchip. Animals including, but not limited to,
wallabies, kangaroos, and possums may have ear tags in one or both ears that
uniquely identify them (The tags should have the same number but should be able
to tell if one is missing). Turtles may also have a tag. Not all tag number formats will
be the same (more info to come in the sample data).
• In addition to formal tags, some animals will have one or more alternate identifiers,
being either a Queensland Parks and Wildlife identifier, or transfer from or to another
facility such as Currumbin Wildlife Hospital, RSPCA, or Australia Zoo, these must
all be maintained and searchable.
• Wildlife are grouped into types which represent both their ‘taxon’ and a macro-level grouping within that type.
• In addition to type, animals are sorted into ‘breeds’, of which there are nearly 1000
in the current system. Each breed must be associated with exactly one ‘type’.
• All animal wildlife may be admitted more than once, if they are re-admitted their previous patient number should be re-used, along with the date they were readmitted – all historical admissions should be maintained (and not over written).
• All animals may have a name, and a picture associated. (Erica’s hint: look at ‘MEDUIMBLOB’ type for the image! – also user TEXT for the notes on the accession form)
• DNA samples may be taken from the animals and results recorded in the database.
This should be stored with the date/time the sample was taken, the date/time it was
entered into the system, and the results of the sample analysis (for the results
use the TEXT datatype).
• The database needs to record who brought in the animal, where it was found,
including the regional or local council area it was found it – reports are generated
for particular councils upon request. There should be a link between the postcode
that the animal was found in and the local council it belongs to.
• A set of wildlife carers are maintained by the system. These are persons who have
animals released into their care after acute treatment, but before they are able to be
released back into the wild.
• The system should be loss-less, no data should be over written.
• Aetiology is the term used to describe the diagnosis categories for the wildlife.
Animals can and will present with more than one aetiology. In addition, animals may be diagnosed with multiple diagnoses within a category – e.g. an animal may have multiple broken bones/anatomical issues.
• During treatment, the vets will put notes on the forms, this information should be maintained where possible using searchable text fields (Use the TEXT datatype).
• A wildlife patient can be assigned a treatment, this could be multiple medicines, or particular surgery or other actions. For medicine, the system should allow the start and stop date of each medicine/treatment. A treatment will be uniquely identified for patient, accession, and date it was prescribed.
• Once the patient is improving it may be sent to a wildlife carer. Wildlife carers are part of carer groups. These groups must have a current permit, which has a permit number and an expiry date. The groups have particular specialisations. Within the groups, are a number of individual people that can be contacted.
• AZWH maintains a contact list – they have other hospitals, other zoos/wildlife parks, government departments, other organisations, wildlife carers, vets, researchers, volunteers and general public that have brought in a patient. For all contacts, AZWH maintains, their first name, last name, title/salutation, email, phone number(s), street address, suburb, state, country, postcode, and what sort of contact they are.

 


26) 201640-Advance database development

Assignment Description

Task 1A
Write a 500 word summary of this paper. • S. Voulgaris et al, “A robust and scalable Peer-to-Peer gossiping protocol”

All the articles can be downloaded in pdf format from the assessment page in blackboard. They can also be found easily on the Web.

Submit your 500 word summary of the paper onto your groups wiki via Blackboard by the stated deadline of 28th October.

Please ensure that you have put your name and student number in the title of your contribution to the wiki.
Coursework 1 Assessment Criteria

The aim of this deliverable is twofold: Firstly to assess your understanding of the module’s introductory part so far and secondly to provide you with early feedback regarding your progress in the module.

Your summary for Task 1a is worth 70 marks will be assessed on the following criteria: a) Accuracy 50%, i.e. does it incorporate all the article’s major points? b) Structure 20%, i.e. does it have clear sections, read well & flow logically? c) Brevity 10%, i.e. is it within the 500 word limit? d) Critique 20%, i.e. is the critique balanced, unbiased and fully identifies the weaknesses/presumptions of the article?

 


27) 552252-Database Systems

Assignment Description

Use the description provided above about RowHill Airport. You can make your own assumptions
based on the requirements given. However, the assumptions should be clearly indicated / defined.
Answer following questions.
Provide the following:
1. Identify business rules (5 marks)
2. Design the database using Entity Relationship Diagram. Clearly indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes relationships (e.g. multivalued attributes/ ISA relationships, etc). (30 marks)
3. Develop relational schemas. (20 marks)
4. Identify primary and foreign keys and clearly indicate them in the relational schema.
(10 marks)
5. Perform Normalization (up to 3rd Normal form). (10 marks)
6. Justify the choices you have made? What assumptions have you made. (5 marks)
7. Write 5 queries with where clause that would think used by the company. You are required to write what the functionality of each query. You will not be given marks, If you failed to explain what each query does. (20 marks)

 


28) EM0001-EM201870ANU525CN

Assignment Description

a. Analyse the all user requirements given above, identify and list all entities described in each user requirement. (5 Marks)
b. Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes/ database schema. You are required to arrange them as given in the example below. (5 Marks)
Note: Select suitable primary key for each table and underlineit.
Eg. Participant(Participant_ID, P_name, email, W_name.)

c. Outline all business rules that describe relationships between entities. (5 Marks)
Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules.
Eg. Every workshop needs an allocated meeting room
d. Determine the functional dependences. (5 Marks)
Eg. Participant_ID  name, address, ……

e. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it.(5 Marks)

f. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that doesn’t show from the problem, they must be described.

Note: You are required to use Visio or any other software tool to create the ER diagram.(15 Marks)

2. Build this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table.(10 Marks)

3. Write SQL queries for the following questions and execute them on the database you created in

Question 4.
Include screen shots of the outputs and all SQL statements you used to answer following questions this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table.

a. List details of all workshops.(3 Marks)

b. The management is interested in knowing names and email addresses of participants outside of Victoria who have already registered for the workshops.(3 Marks)

c. Explain how to formulate a query to print details of workshop name, room number, floor number and room capacity
Hint : You need to join tables using a suitable query.