Part-2 of Summative assessment Task-2:

Answer for all tasks of Part-2 should be put together in a single document including all necessary diagrams and figures. You also need to submit the original file for ER diagram with format of the design software.

You need to implement your design (i.e. creating all tables, loading all data, creating queries and all SQL operations) in Deakin Oracle DBMS for summative assessment task-2.

Tasks:

1.     Conceptual Design

Answer the following tasks for your ER diagram required for your service.

1. Identify the business rules for your [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

[10 Marks]

Requirements for your ER diagram (ERD):

• Mention all required entities and
• You should have different types of entities including strong entities, weak entities, different types of relationships (strong, weak, M:N relationship) in your ERD . Include at least 6 entities (tables) to fulfil the You can have more entities if required.
• Mention all connectivities and cardinalities for
• Use professional database design and modeling software (e.g., MS Office Visio, LucidChart) to draw the ER Crow’s Foot notation is preferable.
• You must copy the ER diagram in your answer

2.     Logical design:

1. Map all different types of entities and relationships, provide definitions of entities and [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 [15 Marks]

Hints: A sample data dictionary has been presented in Week-5 Lecture slide (28)

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.)

Hints: See normalization process in your textbook “[1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author)”, Chapter-6 page 205-212.

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. A sample screenshot:

1. 
   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 [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); Screenshot:

1. Create a query using a SQL SELECT command with “left outer join” from two tables and display the result of the 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.
2. Create a query using a SQL SELECT command with “Equijoin” from two tables and any comparison operator, then display the result of the Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables.

[10 Marks]

Hints: Week 6, Lecture slides (45-60).

1. 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 If required, please insert more data into your tables. [15 Marks]

Hints: An example of such query has been explained in the text book “[1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author)”, Chapter-7-7a, page-281-290. You should consider aggregate functions COUNT, AVG, SUM.

1. 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 [10 Marks]

Hints: An example of nested query has been explained in the text book “[1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author)”, Chapter-7-8, page-290-293.

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 [5 Marks]
3. Briefly discuss bitcoin address, wallet and [7 Marks]