Reference no: EM132196228

Review Questions

1. Fill in the following table as you discuss what data is collected by the different organisation types, and how this data is used regarding individuals and groups.

Organisation Type	Data collected for individual	What information can be gained from this data about individuals?	What information can be gained from this data about groups?
Government Departments
Banking / Finance
Retail
Education Sector
Search Engines
Social networking sites

2. What is data redundancy, and which characteristics of the file system can lead to it?

3. What is data independence, and why is it lacking in file systems.

4. What is a DBMS and what are its functions?

5. What is structural independence, and why is it important?

6. Explain the difference between data and information.

7. What is metadata in the context of a database system?

Portfolio Questions

Figure 1

1. Given the file structure shown in the Figure 1, answer the followingquestions:

a) How many records does the file contain? How many fields are there perrecord?

b) What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure?

c) If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure?

d) What data redundancies do you detect? How could those redundancies lead to anomalies?

Figure 2

2. Given the file structure shown in the Figure 2, answer the following questions.
a) Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure 2

b) Looking at the EMP_NAME and EMP_PHONE contents in Figure 2, what changes would you recommend?

c) Identify the different data sources in the file you examined in Problem 2a).

d) Given your answer to Problem 2c), what new files should you create to help eliminate the data redundancies found in the file shown in Figure 2?

Topic - Entity Relationship Modelling

Review Questions

1. Identify issues in the Crow's Foot notations used in the following ERD.

2. Lucidchart - this product is a browser based diagramming tool; it is able to draw a wide range of different diagrams, including ER Diagrams. As a University student you are entitled to a free Lucidchart account. You can sign up for the free account using your university email address. When your account is created and an invitation email sent to your FedUni student account (check Spam if the message is not in your inBox). Click on the link in your Lucidchart welcome email and you will be able to make use of the software. As a first step you should look at the provided tutorials, in particular "Entity Relationship Diagrams".

To prepare conceptual models (ERDs), you may use Lucidchart or any other drawing package.

3. Given a scenario represented by the following entities, where customers place orders for products:

CUSTOMER - customer number, name, address, phone number
ORDER - order number, order date, customer number and for each product ordered the quantity ordered and the total line price
PRODUCT - product number, product description and product unit price

This ERD only shows the primary keys of each of the entities, sometimes an ERD is drawn such that it will show all the non-primary key attributes for the model. In such a complete ERD you must not show or label foreign keys, the use of foreign keys indicates that you are looking at a logical model where a choice has been made to use a relational database, rather than a conceptual model.

Replicate the above diagram in your preferred diagramming software package. Once you have done that, add all the non-primary key attributes listed in the scenario above.

In this case, if you were to add the foreign keys to the respective entities, can you see any problems moving from a conceptual model to a logical/relational model? What would you need to do to fix the problem?

4. Using the above diagram and the business rules below, create an Entity Relationship Diagram using Crow's Foot notations.
o Include:
• all appropriate connectivities,
• all cardinalities and
• at least the minimum number of attributes required to implement the model

o Business Rules:
• A department employs many employees, but each employee is employed by one department.
• Some employees, known as "rovers," are not assigned to any department.
• A division operates many departments, but each department is operated by one division
• An employee may be assigned to many projects, and a project may have many employees assigned to it.
• A project must have at least one employee assigned to it.
• One of the employees manages each department, and each department is managed by one employee
• One of the employees runs each division, and each division is run by one employee.

5. Design an ER diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to many number of recorded accidents.

6. Acme Pty Ltd is made up of a number of departments that manage none or more projects. Each project is made up of none or more team members.
Each team member belongs to one department. Each team member belongs to zero to one project.
One of the team members supervise the other team members on the project.

Portfolio Questions

7. Given the following business rules, create an ER diagram for each of the specified relationships:
- A company operates four departments
- Each department employs staff
- Each staff may work for one or more departments
- A staff may be supervised by another staff member
- Each department has one manager. Identify the entities and the attributes

8. Design an ER diagram for an university registrar including the following entities:
- Courses, including course number, title, credits, syllabus, and prerequisites;
- Course offerings, including course number, year and teaching period, instructors, timings and classroom
- Students, including student-id, name, and program; and
- Instructors, including identification number, name, department, and title.
- Furthermore, the enrolment of students in courses and grades awarded to students in each course they are enrolled in, must be appropriately modelled.
Document all assumptions that you have made

Prepare an Entity Relationship Diagram (ERD) showing all primary and non-primary key attributes for the following description of a Property Rental System:

- Properties are rented by tenants. Each tenant is assigned a unique number by the Agency. Data held about tenants include family name, given name, property rented, contact address - street, city, state, postcode & telephone number. A tenant may rent more than one property and many tenants may rent parts of the same property (eg. a large shopping complex).
- Properties are owned by owners. Each property is assigned a unique building number. The agency only recognises a single owner for any of the properties it handles. The owner, address, and value are recorded for each property. In addition the lease period and bond are recorded for each property or sub property rented. An owner may own several properties.
- Properties are subject to damage and the agency records all instance of damage to its properties - property, date, type of damage and repair cost are recorded. Repair costs are charged directly to tenants
- Normal property maintenance is also noted - property, date, type of maintenance and cost are recorded. Maintenance costs are charged to the property owner.
- Tenants pay accounts to the Agency - these consist of weekly rental payments, bond payments (for new properties) and damage bills. The date of payment, tenant, property, type of account (Rental, Bond, Damage) and amount are recorded.

Topic - ER Diagram

Case: A Project ID identifies each project. Each project is also given a short title that may or may not be unique and may be given a longer description. A project manager must manage each project and may have many team members assigned to work on it. Some of the team members may work on more than one project. The project has a start date and scheduled completion date. The project also has a total estimated cost. Employee ID identifies people in the company, although the project manager needs to know the name, phone number and e-mail address assigned to each member of his/her team. The project manager also needs to know when an employee is officially assigned to a project and when is relieved from a project. An employee can be a full-time employee or a part-time employee. For a full-time employee, information on the medical benefit provided by the company to the employee is kept. For a part-time employee, information on the contact term and duration is kept. Each project consists of several tasks, identified by a Task ID. Each task has a scheduled start date and a scheduled completion date. As the tasks are performed the actual start and end dates are also recorded. Each task has a planned duration. A task may also have one or more tasks that must precede it.

Portfolio Question

Based on the case above, produce an Entity Relationship diagram (ERD) that models the problem which includes:

1. all entities, relationships (including relationship names) and attributes;

2. primary (underlined) and foreign (italic) keys identified;

3. cardinality and participation (optional / mandatory) symbols; and

4. assumptions you have made, e.g. how you arrived at the cardinality/participation for those not mentioned or clear in the business description, etc.

The ERD should be completed using the standards/conventions of this course (i.e. using crow's feet)

Objectives:

• Learn to implement advance SQL statements
• To practise joining tables for retrieving data
• To practise the use of relational set operators to merge data

Activities

Task

1) Create a new database called Food_Beverages and import the fileFood_Beverages.sql.

2) Draw an ER diagram to represent thedatabase.

3) Use this new database to implement the SQL statements to complete the following:

a) In a following SELECT statement, how many rows will there be in the output? Explain how you have derived at this number. Describe how you can confirm the number iscorrect.

SELECT P.ProductID, C.CategoryID FROM Product AS P, Category AS C;

b) Are all the rows in the output of (a) meaningful? How can you modify that SELECT statement so that the rows in the two tables are logically joined and every row in the output ismeaningful?

c) Display the ID and the shipped date of every order along with the ID and name of the company in charge of shippingit.

d) Show the name and the quantity of the product(s) being listed in eachorder.

e) List the full name and the title of the person which each employee reports to. The ID and last name of the employee should be included in theoutput.

f) Display the ID and contact person of the supplier of the discontinued products. The ID of the product and category ID it belongs to should be included in theoutput.

g) Show the average unit price of the products in each category. The name of each category should be included in theoutput.

h) List the name and title of every customer contact person from Berlin, followed by the full name and title of the employees based in London. The city should be included in the output. (hint: use a relational setoperator)

i) Show the orders which have more than 5 products listed in them? The Order ID and the number of products list in it should be in theoutput.

j) What is the largest discount given to a product beingordered?

Topic - Big Data Analytics Foundation

Case Study Discussions: EBay Big Data Solution:

Please read the relevant case study and answer the following questions

1. Why Big Data is a Big Deal for EBay?
2. What were the challenges, the proposed solution, and the obtained results?
3. Can you think of other e-commerce business that may have Big Data challenges comparable to that of EBay?

Investment Banks:

Please read the relevant case study and answer the following questions

1. How can Big Data benefit large-scale trading banks?
2. How did MarkLogic infrastructure help ease the leveraging of Big Data?
3. What were the challenges, the proposed solution, and the obtained results?

Portfolio Questions
1. What is Big Data? How is it different from the traditional data?

2. Discuss the differences between structured and unstructured data with the help of examples

3. Discuss various rationales for organizations to use Big Data Analytics. Please search for two organizations online and explain their motivations for using Big Data Analytics

4. Briefly discuss the various critical success factors for Big Data Analytics.

5. Discuss various types of analytical techniques and how they could be applied to different data such as call centre's call records, Netflex Video streaming, Twitter hashtags for new products and credit card fraud prediction

Attachment:- Bigdata and analytics.rar

Attachment:- LAB 2 bigdata and analytics.rar

Attachment:- LAB 3 bigdata and analytics.rar