HOLMES
INSTITUTE
FACULTY OF
HIGHER
EDUCATION
UNDERGRADUATE
PROGRAM
Assessment Details and Submission Guidelines
Trimester T2 2020
Unit Code HS2021
Unit Title HS2021 Database Design and Use
Assessment Type Group Assignment
Assessment Title Database Design 2
Purpose of the
assessment (with ULO Mapping)
a) Define, interpret and evaluate different relational database terms.
b) Apply problem solving using SQL knowledge and skill.
c) Contribute and influence business decisions by consolidating valuable information from database.
Weight 25% of the total assessments
Total Marks 25
Word limit N/A
Due Date Week 11
Submission Guidelines • All work must be submitted on Blackboard by the due date along with a completed Assignment Cover Page. Submissions made after the due date and time will be penalized per day late (including weekend days) according to Holmes Institute policies.
• The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2 cm margins on all four sides of your page with appropriate section headings.
• Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using Harvard or IEEE referencing style.
• The SafeAssign similarity score will be used in determining the level, if any, of plagiarism. SafeAssign will check conference web-sites, Journal articles, the Web and your own class members’ submissions for plagiarism. You can see your SafeAssign similarity score (or match) when you submit your assignment to the appropriate drop-box. If this is a concern you will have a chance to change your assignment and resubmit. However, resubmission is only allowed prior to the submission due date and time. After the due date and time have elapsed your assignment will be graded as late. Submitted assignments that indicate a high level of plagiarism will be penalized according to the Holmes Academic Misconduct policy, there will be no exceptions.
Assignment Description
1. You have just been employed by the ROBCOR Trucking Company to develop a database. To gain a sense of the database’s intended functions, you have spent some time talking to ROBCOR’s employees and you’ve examined some of the forms used to track driver assignments and truck maintenance. Your notes include the following observations:
• Some drivers are qualified to drive more than one type of truck operated by ROBCOR. A driver may, therefore, be assigned to drive more than one truck type during some period of time. ROBCOR operates several trucks of a given type. For example, ROBCOR operates two panel trucks, four half-ton pick-up trucks, two single-axle dump trucks, one double-axle truck, and one 16-wheel truck. A driver with a chauffeur’s license is qualified to drive only a panel truck and a half-ton pick-up truck and, thus, may be assigned to drive any one of six trucks. A driver with a commercial license with an appropriate heavy equipment endorsement may be assigned to drive any of the nine trucks in the ROBCOR fleet. Each time a driver is assigned to drive a truck, an entry is made in a log containing the employee number, the truck identification, and the sign-out (departure) date. Upon the driver’s return, the log is updated to include the sign-in (return) date and the number of driver duty hours.
• If trucks require maintenance, a maintenance log is filled out. The maintenance log includes the date on which the truck was received by the maintenance crew. The truck cannot be released for service until the maintenance log release date has been entered and the log has been signed off by an inspector.
• All inspectors are qualified mechanics, but not all mechanics are qualified inspectors.
• Once the maintenance log entry has been made, the maintenance log number is transferred to a service log in which all service log transactions are entered. A single maintenance log entry can give rise to multiple service log entries. For example, a truck might need an oil change as well as a fuel injector replacement, a brake adjustment, and a fender repair.
• Each service log entry is signed off by the mechanic who performed the work. To track the maintenance costs for each truck, the service log entries include the parts used and the time spent to install the part or to perform the service. (Not all service transactions involve parts. For example, adjusting a throttle linkage does not require the use of a part.)
• All employees are automatically covered by a standard health insurance policy. However, ROBCOR’s benefits include optional co-paid term life insurance and disability insurance. Employees may select either options, one option, or no options.
Given those brief notes, create the ER diagram. Make sure you include all appropriate entities and relationships, and define all connectivities and cardinalities.
(10 Marks)
2. Use the database shown in Figure 1 to work Problems A-D.
(15 Marks)
Figure 1 library Database Tables
A. Write SQL code required to generate the tables in Figure 1 (4 Marks).
(4 Marks)
B. Write SQL code required to insert the data into the tables you created in Problem A.
(4 Marks)
C. Modify the BOOK table to include a new attribute which records the DATE_PUBLISHED. Write the SQL code required to update the DATE_PUBLISHED for the following books.
(4 Marks)
D. Write the query that will display the ISBN and title of all books that have been published for more than two years.
(3 Marks)
Marking criteria:
Marks are allocated for each part as above:
Marking Rubric for Assignment 1 Total Marks 15
Excellent Very Good Good Satisfactory Unsatisfactory
Query writing
Skills
/2 Evidence of accurate and
well- written queries Evidence of good query writing skills. Generally relevant. Displayed reasonable query writing skills. Demonstrated little evidence of understanding the topic.
Identification
/3 Demonstrat
ed excellent ability to think critically. Demonstrated
an ability to think critically. Demonstrated reasonable ability to think. Demonstrat ed some ability to think critically but not complete. Did not
demonstrate
ability to think critically.
Analyse
/2 Logic is clear and easy to follow. Consistency logical and convincing. Mostly consistent logical and convincing. Adequate cohesion and conviction. Arguments are confused and disjointed.
Design
/Presentation
/5 All elements are present and very well integrated. Most of the components present and integrated. Components present and mostly well integrated. Most components present. Report lacks structure.
Evaluate/
justification
/3
Excellent outstanding effort made. Extremely good effort. Good effort made but not outstanding. Made some effort. Very little attempt. Lazy effort with inaccuracies.