ICT285 Databases

ICT285 Databases
Final Examination Jan Trimester, 2022

Student Name:

Student Number:

Instructions:

  • Download this document to your own computer
  • Rename the file as ICT_285_Final_Exam_TJ2022_StudentID_FirstName_LastName.docx where StudentID is your student ID and FirstName and LastName are your first and last names
  • Enter your Name and Murdoch student number in the space above
  • Type your answers beneath the questions
  • Make sure you save the document often while you are working on it
  • When you are completed, upload the document to the Final Examination area on the LMS.

Please remember that while the exam is ‘Open-book’, the questions are not written in such a way so as they can simply be googled, or the answers found in the textbook or lecture slides.

Most questions ask you to explain a concept using the example provided. For example, you are not asked for a definition of what a Primary Key is, but you might be given a relation and asked to explain what a primary key is using that relation as an example.

Spending too much time trying to find the answers to these questions will result in you quickly running out of time.

We take academic integrity seriously. Trying to find out the answers from your classmates or friends is 1) an academic misconduct, and 2) unwise as it will waste the time you have to complete the paper.

All submitted exams will be checked using text matching software.

The exam contains 6 questions, and the marks are distributed as follows:

QuestionTotal marksStudent’s mark
Question 110 
Question 220 
Question 315 
Question 415 
Question 525 
Question 615 
TOTAL (out of 100)100 


QUESTION 1 (10 MARKS)

Read the following scenario and answer ALL questions. Your explanations MUST be entirely based on the provided relation.

A company uses below “Inventory” database to record data about their IT equipment. The database is mainly used to store data about two types of hardware: Laptops and Printers. The company stores data on the vendor (manufacturer), Asset_ID (unique number of the asset), Asset_type (i.e., laptop or printer), L_ram (RAMs for laptops), L_screen (screen size of laptops), P_type (Printer type i.e ink_jet, laser, etc..), P_Colour (colour of printer), and Prices of assets.

Inventory Database (unpopulated with data):

VendorAsset_IDAsset_TypeL_ramL_ScreenP_TypeP_ColourPrice
       
  1. Explain what are the major concerns or problems associated with this database design. Provide examples of these problems, your explanation should be entirely based on the above relation. (3 marks)
  • How would you change the design above to address the problems you have identified? Make sure you provide the new design, define the primary keys (by underlying the attributes that are primary keys), define the foreign keys (by indicating them in bold). (4 marks)
  • Consider the following SQL query and explain what information will be retrieved when it’s executed (1.5 marks). As the DBA in this company, explain what you can do to restrict access to this retrieved information for a specific group of users (*hint you should provide an SQL query to support your response) (1.5 marks).  (3 marks total)

SELECT R1.P_Type

FROM Printer R1

WHERE NOT EXISTS

(SELECT *

FROM Printer R2

WHERE R1.price > R2.price)

QUESTION 2 (20 MARKS)

The following tables form part of a database held in a relational DBMS (Primary Key, Foreign Key):

Book (Book­_ID, Publisher, Type, Year, Edition, Price)

Employee (Employee_No, FirstName, LastName, Role)

EmployeeBook (Book_ID, Employee_No, Date_Taken, Date_Returned)

Provide the SQL for the following queries:

  1. List the numbers of Books taken by each IT Officer 
  2. Show the employees who have taken more than two Books
  3. List the maximum number of Books taken by any Employee
  4. Show the Employees who are Managers and have not taken any Book
  5. List the full details of the most expensive as well as the cheapest Book

QUESTION 3 (15 MARKS)

Give the relational algebra (not SQL) expressions for each of the queries in Question 2:

  1. List the numbers of Books taken by each IT Officer 
  2. Show the employees who have taken more than two Books
  3. List the maximum number of Books taken by any Employee
  4. Show the Employees who are Managers and have not taken any Book
  5. List the full details of the most expensive as well as the cheapest Book

QUESTION 4 (15 MARKS)

Convert the following relation into a relation or set of relations in at least 3NF:

NameAddressContactBookingIDDate_fromDate_toRegoMadeYearRateOfficeIDAddress
JackHay st.015926B00919-2-2121-2-21C201BMW202050L1Monk st
CharlieRea st.032569B003 B004  30-1-21 30-1-215-2-21 7-2-21B302 T210Suzuki BMW2021 201830 50L2 L2Bland st Bland st
Jack Kings st.087954B00610-2-2119-2-21C265HondaTwenty Twenty45L1Monk st
AliKings st.052949NA1-12-999931-12-9999NANA99990NANOT

QUESTION 5 (25 MARKS)

  • Create an ERD from the given case below using the Crow’s Foot notation
  • Use Visio or any other appropriate tool to create the ERD and legend, it should be clear, readable, concise, and complete. Attention to the naming of entities and overall presentation.
  • If you prefer, sketch it on a paper first as a draft. Note, a hand drawn ERD will not be accepted.

Your client is a company that supplies building materials.  The company want you to design a stock order database (they keep none or very little stock on hand).  Once this database is implemented, your client will then integrate it into its existing invoice system.

After interviewing members of the organisation, you have accumulated the following information:

Your client keeps track of its customer’s details, including their name, address and contact details, which includes multiple phone numbers.  Each customer has a unique number (CustID) and a nominated discount rate, which is applied to the list price of any product they purchase.

Customers purchase products from the company by placing an order, which has a unique number (OrderNo) and the date the order was placed.  An order may be for many products.  Each product has a unique product number (ProductNo), a description and a list price.

When a customer’s order is assembled (i.e., is complete), it is sometimes collected from the company by the customer, in which case no other documentation is required.  Alternatively, a customer’s order may be dispatched to a specified location (a delivery site) via a nominated freight company.  For each jobsite, your client records the name of the delivery site, the address and multiple phone numbers. The delivery site is not unique to, nor owned by, the customer, for example, many customers may have their deliveries sent to a particular mine site.

Your client has their own consignment book, which the nominated freight company’s driver signs and dates when an order is collected.  A unique consignment note number (ConNote) is provided for each delivery.  An order is not dispatched until it is completed (i.e., an order is only ever dispatched once).

A customer can have multiple orders for a particular delivery site, and different freight companies may deliver these orders.  This depends on the availability of the delivery vehicles, the size of the order and when the order is ready to be dispatched.

Your client keeps a record of the details of the various freight companies it uses, including their name, account number, address and contact details, which include multiple telephone numbers.

Each freight company has different vehicle types available, such as utilities, flatbeds, semitrailers, enclosed vans, etc.  The vehicle type used for a delivery depends upon where the order is going and what size the order is.

QUESTION 6 (15 MARKS)

Read the below scenario and answer ALL the following questions:

A tourist arrives in Perth city on a leisure trip. They are looking to visit Rottnest Island by taking the ferry. There are different ports for departure/arrival. Consider the transaction where this tourist (now customer) wants to book a ferry that goes from port A to port B on a given date and time.

  1. Explain step-by-step the different steps that are needed in order to update the database when a customer books a ferry. (7 marks)
  • Assuming that multiple customers can access the booking system from different locations. Identify the major issue that may arise in this situation.  (2 marks)
  • In each of the situations below, the users are accessing the same data at the same time. Which of the situations could give rise to a major problem? You should name the Major problem and fill in the provided table using X in relevant cell). (3 marks)
SituationYesNo
3 users executing SELECT statements
2 users executing SELECT statements
1 user executing an INSERT statement
2 users executing SELECT statements
1 user executing an UPDATE statement
3 users executing UPDATE statements
  • Using the transactions table above as an example, explain the concepts of Atomicity and Isolation. (3 marks)

****** END OF PAPER ******

Order Now

Get expert help for ICT285 Databases and many more. 24X7 help, plag free solution. Order online now!

Universal Assignment (September 27, 2022) ICT285 Databases. Retrieved from https://universalassignment.com/ict285-databases/.
"ICT285 Databases." Universal Assignment - September 27, 2022, https://universalassignment.com/ict285-databases/
Universal Assignment July 28, 2022 ICT285 Databases., viewed September 27, 2022,<https://universalassignment.com/ict285-databases/>
Universal Assignment - ICT285 Databases. [Internet]. [Accessed September 27, 2022]. Available from: https://universalassignment.com/ict285-databases/
"ICT285 Databases." Universal Assignment - Accessed September 27, 2022. https://universalassignment.com/ict285-databases/
"ICT285 Databases." Universal Assignment [Online]. Available: https://universalassignment.com/ict285-databases/. [Accessed: September 27, 2022]

Please note along with our service, we will provide you with the following deliverables:

Please do not hesitate to put forward any queries regarding the service provision.

We look forward to having you on board with us.

Get 45%* OFF on Assignment Help

Popular Assignments

VU22977 – Practice in a Legal Environment

Advanced Diploma of Legal Practice – 22565VIC PORTFOLIO- CLIENT MATTER FILE Assessment task 1 Prepare a comprehensive portfolio of documents to create a Client Matter File which must include: Element 1 Area of law Cover sheet stating client name and short description of legal matter Client Instruction Sheet, Costs Agreement

Read More »

KIT714 ICT Research Principles: Assignment 1

Practical Qualitative Research Exercise                                                                                                                                         Type:                 In-Semester, Individual Assignment Task Length:   minimum 2,000 words Weighting:     20% of total assessment for this unit Due Date:       Friday 5 August 2022 – 11:55 pm (Week05) Submission:    electronic submission on MyLO (WORD or PDF) Description:    This practical exercise will engage students in a qualitative research

Read More »

MODULE 44-704463 Dissertation Help

ASSESSMENT CRITERIA Dissertation (12,500 words) Deadline: JULY 29th 2022, 3pm Introduction and abstract  Situates the research question within the, theory, and concepts Explains the value of the study.  Relevance of the research question, to the field of study is fully justified. Leads logically into the Literature Review. Abstract of professional

Read More »

ITECH1103-Assignment: Assignment 2: Analytics Report

Overview The purpose of this task is to provide students with practical experience in writing a data analytical report to provide useful insights, patterns, and trends in a chosen dataset in the light of a set of tasks required within this document. This dataset will be chosen from the UC

Read More »

PBHL20001 Term 1, 2022 – Supplementary Assessment

PBHL20001 Term 1, 2022 Supplementary Assessment The influenza pandemic that followed World War I and took place over the period from 1918-1920 resulted in an estimated 100 million deaths worldwide and 500 million infections. In addition, this pandemic had a significant impact on public health practice throughout the 20th century

Read More »

Community Health Project 1 (HLTH 3058): Assessment 2: Portfolio Task 2

Community Health Project 1 (HLTH 3058) Assessment 2: Portfolio Task 2 – Evaluative Framework Marking Guide Weighting: 15% the total grade for the course   Criteria Outstanding performance (75-100%) Exceeds core requirements (65-74%) Meets core requirements (50-64%) Does not meet requirements (<50%) Comments Content (70%) Demonstrates adequate knowledge of integrating

Read More »

Community Health Project 1 (HLTH 3058): Assessment 2: Portfolio Task 1

Community Health Project 1 (HLTH 3058)Assessment 2: Portfolio Task 1 – Ethical Considerations Marking GuideWeighting of 15% the total grade for the course Criteria Outstanding performance (75-100%) Exceeds core requirements (65-74%)   Meets core requirements (50-64%) Does not meet requirements (<50%) Comments CONTENT (80%) Ethical Considerations   Demonstrates adequate knowledge

Read More »

SAM 11486/SAM G 6677: Assignment 1 – Individual

Assignment 1 – Individual (25%) Report Due: 11:59pm Sunday Week 5 04/09/2022 This assignment will be marked out of 25 marks and is worth 25% of the overall mark for the unit. Please check the unit outline for late penalties and restrictions on late submissions. This assignment is an individual

Read More »

11486 Systems Analysis and Modelling

Assignment Case Study C – Feed Me Now Disclaimer: The situation described in the following case study is fictional, and bears no resemblance to any persons, businesses, or organisations, living or dead. Any such resemblance, if exists, is merely co-incidental in nature, and is not intentional. Feed Me Now is

Read More »

MAA703 – Accounting for Management

Trimester 2 2022 Assessment Task 2 Part B – Teamwork Reflection (Individual) DUE DATE AND TIME:                        Wednesday, 7 September 2022 by 8:00pm (AEST) PERCENTAGE OF FINAL GRADE:    10% WORD COUNT:                                   750 – 800 words Description Teamwork is one of the vital components of many workplaces and the ability to demonstrate

Read More »

Research project assessment proforma and marking schemes

BSc Biomedical Sciences with Health, Exercise and Nutrition (HEN6004) and BSc Biomedical Science (APS6022) Instructions for use All forms must be completed electronically and uploaded to the student submission point in  turnitin. Please note the literature review assessment will be completed in January whereas the assessment of the paper/poster and

Read More »

XERO and Spotlight reporting software Report Assignment

Case facts – Read me carefully please City Infrastructure Holdings Ltd (CIH) and Network Maintenance Services Ltd (NMS) City Infrastructure Holdings Ltd (CIH) acquired 100% of the 300,000 issued shares in Network Maintenance Services (NMS) on 1 July 2021.  The consideration provided to shareholders of NMS consisted of $1.20 in cash plus 1

Read More »

KIT103 Computational Science

School of Information and Communication Technology College of Sciences and Engineering Unit Outline KIT103 Computational Science Semester 2, 2022 Sandy Bay Campus, Hobart Newnham Campus, Launceston COVID-19 (Coronavirus Information) For information on how you can help keep us all safe, please review the Coronavirus updates page here: https://www.utas.edu.au/about/safety-security-and-wellbeing/coronavirus Sections that

Read More »

SEN725 Urban Stormwater Asset Design

Assessment Task -4 Final Report – Analysis of CoGG Stormwater Network Weight 50% This is an individual assessment task. Plagiarism and collusion is unacceptable practice at Deakin University. You must appropriately reference your work. Failure to do so will result in disciplinary action. For more information on plagiarism and collusion,

Read More »

PSY3PRP 2022 Assignment 2: Research Project Report

PSY3PRP 2022 Assignment 2: Research Project Report Guidelines and Marking Rubric Individual Research Report due Wednesday 19th October 11.59pm (50% of total grade) For this assignment—with support through structured tutorial work across much of the semester— you will produce an individually-written report on the broad topic of feeding difficulties in

Read More »

Increasing Demand for assignment help in Namibia

With the growing population, the demand for clean energy, advanced technology, new structures, and better healthcare is escalating day by day which makes engineering the most popular degree for the youth. As more people pursue engineering nowadays, it also makes it one of the most difficult and competitive degrees also.

Read More »

Online Assignment Help in Germany

How Could You Do Exam Preparation Effectively? Exam time is full of challenges and difficulties. You have to juggle exams even in high school, college, or higher education. It defines your knowledge of a specific subject and how well you know the curriculum. The exam also tests how much you

Read More »

Get Excellent Human Values Assignment Help Immediately

Get Excellent Human Values Assignment Help Immediately: Expert Solutions to all your Queries Instant Human Values Assignment Help Online Students can get Human Values assignment help online in a fast and simple way from the experts at Universal Assignment Help Service. Their team of over 172+ subject experts works every

Read More »

Get Top Modern History Assignment Help Online

Order Modern History Assignment Help immediately Students often need modern history assignment help for many reasons. Getting immediate online help for assignments helps students boost their academics in incredible ways. Sometimes, the pressure of studies is too much to take for students. This causes them to score poor grades and

Read More »

Get Instant Assignment Help in the United Arab Emirates

How Can Assignments Help Students? Whatever the definition of homework is, one thing is certain: it helps students thoroughly learn the concepts they have studied in class. The number of such assignments may cause stress or a burden. It means students and tutors are both correct in their type of

Read More »

Assignment Help in Mexico

How to Gain Awesome Grades in Your Chemistry Subject? Whether you are pursuing a technical degree or studying in the science stream, chemistry is always at the forefront. If you love to observe reactions, molecules, acids, condensation, evaporation, and dilution, chemistry will prove a resource for getting outstanding academic marks.

Read More »

Assignment Help in Kenya

How Could You Find the Best Biology Homework Website? Getting online homework help from an expert is neither embarrassing nor unauthentic. The vast majority of students around the world believe that doing online assignments and tutoring is beneficial. The Standard Graduate School of Education revealed that 56% of students suffer

Read More »

Assignment Help in Bangladesh

The Best 6 Hacks to Shortlist the Top Civil Engineering Assignment Tutor? Finding a homework expert for civil engineering assignments is a hectic task. As civil engineering has several fields, getting expertise is difficult for tutors. You may struggle to receive civil engineering assignment help in Bangladesh due to this

Read More »

Assignment Help in Pakistan

What Will Unethical Occur from Getting Homework? Homework looks like a mode of learning in most cases. But what happens to its quantity? Its quantity should be more than enough for students. Such circumstances may be detrimental to the health of the pupils. Sometimes, college faculties or teachers forget to

Read More »

Assignment Help in Malaysia

The Best 4 Skills You Should Learn For Making Your Career in 2022 As technological innovations leap forward daily, new skills are entering our market. Sticking to one skill set enables you to lose lucrative job opportunities. That is why it gives importance to having diverse skills, which should be

Read More »

MKT200 Marketing Principles: Infographic

Assessment 2 Information Subject Code: MKT200 Subject Name: Marketing Principles Assessment Title: Infographic Assessment Type: Individual infographic Length: Two pages Weighting: 30% Total Marks: 100 Submission: Online Due Date: Week 9 Your task Individually, you are required to create a two-page infographic outlining the communications process of two competitor brands.

Read More »

MMP-223, Property and Real Estate Law and Practice

MMP-223, Property and Real Estate Law and Practice, Trimester 2, 2022 Assignment 2, Client Report, Individual Assignment 25% of Unit Assessment (1,250 words*) Due Date: September 16, 2022, 8-pm AEST Assignment Brief: Clyde, being a real estate agent, loves property. With his share of the sale of the Sorrento property

Read More »

NUR1398 – Foundations of Nursing Practice Theory

Assessment: Care Plan Report Task overview Course NUR1398 – Foundations of Nursing Practice Theory Brief task description Written care plan addressing nursing care requirements for older patients with degenerative conditions. Rationale for assessment task Registered Nurses are required to think through the different aspects of patient care to arrive at

Read More »

MBA641 Project Strategy Framework

Assessment 1 Information Subject Code: MBA641 Subject Name: Strategic Project Management Assessment Title: Project Strategy Framework Assessment Type: Length: Individual video recording and supporting Infographic Video: 10-minute (no more) Supporting infographic: 1 page (no more) Weighting: 30% Total Marks: Submission: 100 Online Due Date: Week 4 Your task You are

Read More »

SEJ201 Structural Design Assessment

SEJ201 Structural Design Assessment Task 5 FINAL PROJECT REPORT Summary The Final Project Report is an individual task which builds on teamwork completed earlier in the trimester. This report shall clearly explain and justify the design solution proposed. In this report students you should identify project scope, inputs and constraints,

Read More »

42907 Design for Durability

Introduction Overall, explain what readers will find in the report, general information about the structure, location, climatic conditions, detail which specific parts of the structure will be designed according to which Australian standard. Which type of foundation, why? You can use figures in this section. Provide the scope of the

Read More »

Can't Find Your Assignment?

Open chat
1
Free Assistance
Universal Assignment
Hello 👋
How can we help you?