MyAssignmenthelp

Get Help From World's No.1 Online Tutoring Company

Get Online Tutoring through WhatsApp

Question & Answers

SIT103 Part 2 of Summative assessment Task 2

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]

Expert's Answer

Contact – This entity contains attributes such as home number, email, mobile number of the traveler which is required in order to communicate with the customer.

Traveler – This entity has attributes such as name of traveler, gender, nationality, passport number etc. The airline needs to keep record of these information of all the passengers in a flight.

Address – As the name of the entity applies, it holds information of whereabouts of the customers. This may be useful if something ever has to be perhaps mailed to the customer.

Flight – This entity holds information about the flight details itself such as name of departure, name of arrival terminal, and number of seats.

Airline Agency – Has attributes such as id airline, address, and branch office.

Schedule – Accounts the schedule of flights and has attributes such as time of departure, time of arrival, and duration of the flight.

Visa – Visa is obviously a very important tool in travelling; hence, we have an entity for it keeping information about the issue date of visa, expiry date and country.

Payment – Payment details from the customer to the airline for the booking of a ticket. It has attributes such as total amount payable, discount and commission (if any).

Tickets – Finally we have details about the ticket which has which type of class the customer has been assigned to (economy, first class etc.), total allowed baggage weight and status.

For Viewing Complete Solution

Chat with our Experts

Want to contact us directly? No Problem. We are always here for you

Professional

Online Tutoring Services

17,148

Orders Delivered

4.9/5

5 Star Rating

748

PhD Experts

 

Amazing Features

Plagiarism Free

Top Quality

Best Price

On-Time Delivery

100% Money Back

24 x 7 Support

Ask a New Question
*
*
*
*
*

TOP

Connect on WHATSAPP: +61-416-195006, Uninterrupted Access 24x7, 100% Confidential

X