database and design-Q2

1

Assessment Task – Tutorial Questions Unit Code: HS2021 Unit Name: Database Design and Use Assignment: Tutorial Questions 2 Due: 11:30pm 19th June 2020 Weighting: 25% Purpose: This assignment is designed to assess your level of knowledge of the key topics covered in this unit Unit Learning Outcomes Assessed:

1. Design and implement a relational database by considering business needs and context of

the systems.

2. Retrieve and present information from cross functional areas information using SQL tools

and techniques.

3. Discuss implementation approaches and associated risks associated with the

implementation of a Database Management System.

4. Contribute and influence business decisions by consolidating information from databases.

6. Understand the ICT profession and the expectations of ICT professionals in Database

Management Systems roles.

Description: Each week students were provided with three tutorial questions of varying degrees of difficulty. These tutorial questions are available in the Tutorial Folder for each week on Blackboard. The Interactive Tutorials are designed to assist students with the process, skills and knowledge to answer the provided tutorial questions. Your task is to answer a selection of tutorial questions for weeks 6 to 10 inclusive and submit these answers in a single document. The questions to be answered are;

2

Week 6 – Question C Convert the following Entity-Relationship diagram to Relational Schema:

(10 marks) Week 7 – Question B Given the following scenario identify entities, attributes, and relationships. Determine cardinality and participation constraints. Finally assign the appropriated attributes to each entity and relationship, and conclude determining the appropriate identifiers. Uber, the famous ride sharing company, is looking to update its information system aiding the booking and managing of rides. Uber information systems, contains information about two type of people drivers and passengers. For each person it will store first name, last name, date of birth, and account number. Additionally, drivers will have to provide their driver license and their vehicle registration number. Each time a ride is booked by a passenger, the date of the ride, the destination, the designated driver, and a preliminary price for the ride fare are recorded. Finally, once the customer reaches its destination the record is updated in order to reflect the final fare.

(10 marks)

Week 8 – Question A Provide a definition for Functional Dependencies and define their relation to Primary Keys

(10 marks)

Completed Student

SubjectCode

SubjectName

FirstName

Subject

LastName

3

Week 9 – Question A Create the Entity-Relationship diagrams (both unexpanded and expanded versions) for the following scenario: A construction company deals with several construction projects at the same time. This is done by employing several workers and owning several types of vehicle. A construction project may require several types of vehicle and several workers. A worker may be authorized to drive several types of vehicle and be involved in several construction projects. Similarly, a vehicle may be used in several projects at once. Finally, a construction project may require several workers driving the same type of vehicle but within a construction project a worker will only drive a single type of vehicle.

(10 marks)

Week 10 – Question B Given the following table determine its stage of normalization and normalize it to 3rd Normal Form (3NF) illustrating stepwise all the various intermediate stage of normalization. Conclude providing the Relational Schema associated with the 3NF version of the table.

(10 marks)

Campus Course SubjectCode Year Semester Title CovenorID CovenorName CovenorOffice Address

Melbourne

BIS

IFN001

2019 1

Database 000001 John Doe B101

185 Spring St, Melbourne VIC 3000

2

2020 1 Database 000002 Jack White C221

IFN101 2020 1

Programming 000003 Will Black D415 2

IFN202 2020 2 IS Consulting 000002 Jack White C221

MBA HI5001 2019 1 Accounting for Business Decisions

123456 Steve Brown H234

Sydney MBA

HI5001 2020 1 Accounting

for Business Decisions

125555 Katy Sallivan A001 6/91 York St, Sydney NSW 2000

2

HI5002 2020 1 Finance for Business

4

Submission Directions:

The assignment has to be submitted via Blackboard. Each student will be permitted one submission to Blackboard only. Each student needs to ensure that the document submitted is the correct one.

Academic Integrity Academic honesty is highly valued at Holmes Institute. Students must always submit work that represents their original words or ideas. If any words or ideas used in a class posting or assignment submission do not represent the student’s original words or ideas, the student must cite all relevant sources and make clear the extent to which such sources were used. Written assignments that include material similar to course reading materials or other sources should include a citation including source, author, and page number. In addition, written assignments that are similar or identical to those of another student in the class is also a violation of the Holmes Institute’s Academic Conduct and Integrity Policy. The consequence for a violation of this policy can incur a range of penalties varying from a 50% penalty through to suspension of enrolment. The penalty would be dependent on the extent of academic misconduct and the student’s history of academic misconduct issues. All assessments will be automatically submitted to SelfAssign to assess their originality. Further Information: For further information and additional learning resources, students should refer to their Discussion Board for the unit.