THIS PAPER HAS BEEN DONE AT ESSAYLINK.NET; CONTACT US FOR A SOLUTION
Assessment Task
A Car Dealership sells both new and used cars, and it operates a service facility. We know that a Salesperson may sell many cars. Sales are recorded via an Invoice which are set up by the Salesperson Involved. Customers receive a copy of the invoice upon completion of the sale. Every Customer may buy many cars, but each car is bought by only one Customer. Each Invoice must include the Car Reg, Sales Date, Quantity, Total, Salesperson and Customer its for.
Customers details are recorded by the Dealership and periodically they review these as part of GDPR and ask customers via email/text message if they are happy for their details to remain in their system. If the customer wishes to be removed, the Dealership will remove only the personal information and hold onto the Business-Critical Information. Where a customer has not had their car serviced with them in 13 months or more, they are marked as “inactive” and a report is sent to the Marketing Dept to be contacted and offered deals etc.
The Dealership also offers servicing of cars. This is handled through a ticketing system, this uses an alphanumeric numbering system e.g., SRV001. When a customer drops a car in for either a service or repair, they will receive a ticket for that car. Overtime a customer may of course get their car serviced or repaired many times. When a customer collects their car, they will and pay they receive a receipt. The Receipt must show Car Reg, Customer, Service or Repair, Any parts Used and the Total Cost. To keep track of a cars service history, the Dealership records all history against the car’s registration number.
Page 2 of 9
Each Service Ticket is assigned to a Mechanic. Daily each Mechanic may work on many service tickets. Overtime there are a lot of different tickets per Mechanics and again this history is built up on the Ticketing System. Mechanics are uniquely identified by their Employee Number, this differs to their PPS number as under GDPR this is kept only in the HR system.
In terms of some of the key entities they have given you the following list:
• Car
• Customer
• Employee (SalesPerson or Mechanic)
• Service Ticket
In terms of data Updates, they have the following requirement:
• They want the ability to create:
o A new Customer via a parameterised Stored Procedure.
o A New or Used Car to the sales stock via a parameterised Stored Procedure.
o A new Employee (SalesPerson/Mechanic) via a parameterised Stored Procedure, this should set
up all the relevant information, such as PPS Number, Name, Skills etc.
o A New Service or Repair Ticket via a parameterised Stored Procedure.
Page 3 of 9
• They want the ability to delete a customer and foreign key records associated with this Customer via a parameterised Stored Procedure if they have a requested their details be deleted, as per GDPR compliance. This delete must also allow for Business-Critical Data to be kept.
In terms of Management Information (MI), they see real value in having a single view on all their
Mechanics, Tickets and Cars worked on and as such want the following extract:
They want an MI extract created as a SQL View which will return all Mechanics, Tickets and Cars worked on per day. The view should be run each day and show the previous days details. This should include the Mechanic’s Employee Number, Full Name, the Ticket Number, Date, Time, Car’s Reg., Make and Model at a minimum. Other relevant details may also be included.
Inactive Customer Data View
The company also want visibility of all Customers who have not serviced their car with them in the last 13 months. This information will be passed to the Marketing dept to send out deals etc to them. Details here should include the Customer’s Name, Contact Details, Car Reg, Date Last Serviced at a minimum.
They have come to you as an IT Consultant and they want these requirements developed into an enterprise solution. Along with the final solution you must supply the customer a Technical Design Document with the technical design details of the solution.
Page 4 of 9
Technical Document
The accompanying Technical Document should cover (but not limited to):
1. Scope of the document.
2. Technical Design to include:
a. ER Diagram (Physical Model to 3NF)
b. Data Dictionary
c. Technology used
3. List All Supplied DDL & DML Transact SQL Files (run order must be given)
4. Test Plan.
5. Reflection on Learnings.
6. References.
Page 5 of 9
Project Deliverables
The distribution of assessment marks will be as follows:
Deliverable
Breakdown of Marks
Submission Date
Technical Document
20%
8th December 2022
(Should not be less
(midnight)
than 300 words)
Database Design &
80%
8th December 2022
Development
(midnight)
ASSESSMENT CRITERIA Criteria/Mark
< 40
40-49
50-59
60–69
70+
Technical Document (20%)
Technical Document (20%) Key Areas include: Overall Presentation, Description & Functionality and detailed Design.
Very Poor
Poor
Adequate
documentation.
documentation.
documentation with
Description
Description and
adequately stated
&functionality
functionalities
details. Key areas are of
weak. Missing
stated but lack
reasonable standard.
key parts (e.g.
clarity. Some
database
key areas
schema).
missing.
Poorly
structured with
spelling and
syntax errors.
Good
documentation, all
essential key areas
covered. Description & functionalities clear.
Excellent documentation, Comprehensive design.
Page 6 of 9
ASSESSMENT
< 40
40-49
50-59
60–69
70+
CRITERIA
Criteria/Mark
Database Design & Development
(80%)
Database (ERD) with
Poor database
At least SIX
At least
SIX
tables
At least SIX tables with
Excellent
at least SIX tables
design with less
tables with
with
correct
data
correct data types &
database design
properly designed:
than SIX tables.
correct data
types
and
correct
correct Primary Key and
with correct data
20%
types.
Primary Keys.
Foreign Keys.
types and
correct Primary
Key and Foreign
Keys.
SQL to Create
SQL does not
SQL runs &
SQL Creates tables
SQL Creates ALL tables
SQL Creates ALL
the Database with
create tables
creates tables
MOST with PK &
with PK & FKs. Data
tables with PK &
Test Data: 20%
and generates
with PK but no
FKs. Data Inserts
Inserts have some
FKs. Inserts all
errors for
ALL FKs. Inserts
have some errors.
minor errors easily
data. NO errors
some/all tables.
some/all data.
Mostly
fixed to continue to
at all.
Cannot
Not fully
Implementing
build the database. For
Implementing
implementing
example: ONE missing
run/missing the
Referential
Referential
insert
Referential
Integrity.
comma, ONE spelling
Integrity.
Integrity.
mistake, IDENTITY
statements
value misaligned.
Implementing
Referential Integrity.
Stored
SPs not
SPs executes
SPs executes and inserts
Stored Procedure
Stored Procedure
Procedures
executing or
and inserts
all fields for a single
executes and
executes and
20% (SPs)
executes and
some
Customer,
Employee
inserts/updates/deletes
inserts/updates
updates/
fields for a
and Car. Including error
all fields for a single
/deletes all fields
returns no
single
checking
or transaction
customer and some
for all entities
fields.
Customer,
control
fields for Employee and
with Transaction
Employee and
Car. Containing error
Control.
checking or Transaction
Car.
Control
Customer Data GDPR Compliance 10%
Little or no design to support GDPR compliance
Partly supports
GDPR
compliance
Supports GDPR compliance, no real consideration for
Supports GDPR compliance some consideration for deleting only personal
Supports
GDPR
compliance and
fully
Page 7 of 9
deleting only personal
information Leaving
differentiates
information.
business-critical
between deleting
information.
personal
information &
business-critical.
MI Extract 5%
View returns
View returns
View returns data from
View returns data from
View returns full
little or no data.
some
all 3 tables but contains
all 3 tables but
dataset.
data but not
duplicate columns/data.
Data is incomplete
from all 3
tables.
Inactive Customer Data
View returns
View returns
View returns Inactive
View returns Inactive
View returns full
View
little or no
some Inactive
Customer data but not
Customer data but
Inactive
5%
Inactive
Customer data
all fields.
result set is incomplete.
Customer
Customer data.
but not all.
dataset.
Page 8 of 9
General Assessment Submission Requirements for Students:
1. Online assignments must be submitted no later than the stated deadline.
2. All relevant provisions of the Assessment Regulations must be complied with.
3. Extensions to assignment submission deadlines will not be granted, other than in exceptional circumstances. To apply for an extension please go to Service Desk and log a ticket. Please note the lecturer cannot & does not grant the extension.
4. Students are required to retain a copy of each assignment submitted, and the submission receipt.
5. Assignments that exceed the word count will be penalised.
6. Students are required to refer to the assessment regulations in their Student Guides and on the Student Website.
7. Dublin Business School penalises students who engage in academic impropriety (i.e. plagiarism, collusion and/or copying). Please refer to the attached referencing guidelines for information on correct referencing.
8. Late submissions will be penalised by 2marks per day late.
What is referencing and why is it necessary?
Please follow this link to the Harvard Style Referencing Guide – all referencing is required in this format. http://issuu.com/dbslibrary/docs/harvard-referencing-guide/1?mode=a_p
The School of Arts generally use APA Referencing, information is available under DBS library guides on www.library.dbs.ie.
Page 9 of 9
GET ALL YOUR ACADEMIC HELP AT ESSAYLINK.NET
