SIT103 Data And Information Management - Assessment Task 2

Part-2 Of Summative Assessment Task-2

1. Conceptual Design
Answer the following tasks for your ER diagram required for your service.

1. Identify the business rules for your service. [15 Marks]
2. Prepare an entity relationship diagram (ERD) for your service [50 Marks]
3. Provide a description for all entities, attributes and relationships used in your ERD. [10 Marks]

2. Logical design:

1. Map all different types of entities and relationships, provide definitions of entities and relationships. [30 Marks]
2. Provide a data dictionary for all of your tables which should have table name, attribute name, content (description of attribute), data type, format, value range, mandatory/optional attributes, primary/foreign key and foreign key referenced table. [15 Marks]

3. Normalization [20 Marks] Explain partial and transitive dependencies. Prepare a dependency diagram which has partial and transitive dependencies (similar to the following figure) using an example table definition from your design (Task-2 Logical design above), If required you can add additional attributes for the table in the dependency diagram. Normalize the table from your initial dependency diagram into more tables which should be in third normal form (3NF), show the dependency diagram for each table of 3NF. Briefly explain your normalization process. (An example dependency diagram is given below from the text book, chapter-6, please see Hints for more details.)

Get Your Customize Task on any subject starting 10$/Page

Order Now Whatsapp Now

4. Implementation and loading
Instructions: All required tables in the Task-4(A to F) should be from your design (Task-1 ,Task-2 of Part-2 in the above).

1. Create all tables in Oracle SQL according to your definitions in the logical design and populate the tables with sample data (at least 10 records in each table). [30 Marks]
   You need to provide all SQL statements in your answer document. After creating tables and entering sample data into them in Deakin Oracle DBMS, you are required to use “SELECT ” command to list all sample data for each table, and provide screenshot(s) to show that all tables are populated with sample data.
2. Demonstrate an execution of SQL UPDATE command on a table with a substitution variable. Provide SQL command and screenshot in your answer. Repeat the update command with double ampersand (&&), show your screenshot and write the commands. [15 Marks]
   For example to increase salary for all employees by a given bonus rate (e.g., 5%, 3%, or other rates). Hints: see practical-2 (week-3) section-5.
   SQL UPDATE command: UPDATE employee SET salary = salary * (1+&bonus);
3. Create a query using a SQL SELECT command with “left outer join” from two tables and display the result of the query. Write the SQL statements and provide screenshot of the results in your answer. If required, please insert more data into your tables. [10 Marks] Hints: See week-5 lecture slides.
4. Create a query using a SQL SELECT command with “Equijoin” from two tables and any comparison operator, then display the result of the query. Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [10 Marks]
5. Create a query using a SQL “SELECT” command and aggregate functions COUNT, SUM, AVG. The query should retrieve data from more than one table, join the tables based on common attributes and resulting rows should be grouped into collections based on the same values of more than one attributes (GROUP BY). Then your query should reduce each collection into a single row by using aggregate functions. Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [15 Marks]
6. Create a nested query using SQL “SELECT” command with “WHERE” clause which needs information generated by an inner SELECT subquery on the right side of “WHERE” clause. The subquery should be created by using SQL “SELECT” and an aggregate function (e.g. COUNT, AVG etc). Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [10 Marks]

5. Topics on Big Data and Bitcoin
Write the answers of the following questions in your own words, (cite and reference if required).

1. What is Big Data? Discuss what are the challenges that the traditional database technologies face when it comes to Big Data? [8 marks]
2. Describe the current technologies that deal with Big Data. [5 Marks]
3. Briefly discuss bitcoin address, wallet and transactions. [7 Marks]