DBFN212 Database Fundamentals Assessment :

For solution: +610482078788

+61482073403

+61482072848

ASSESSMENT BRIEF 3 Individual Project

COURSE:     Bachelor of Business/ Bachelor of IT
Unit Code:	DBFN212
Unit Title:	Database Fundamentals
Type of Assessment:	Assessment 3- Individual Project
Length/Duration:	N/A
Unit Learning Outcomes addressed:	Understand Recognize the role of data model in the process of developing a database System and produce a data model from source documents.Define views and formulate efficient queries using a query languageDesign and construct a physical system using database software to implement the logical design
Submission Date:	To be submitted in Week 11
Assessment Task:	The design, building, and querying of a relational database.
Total Mark:	20 Marks
Weighting:	20%
Students are advised that submission of an Assessment Task past the due date without a formally signed approved Assignment Extension Form (Kent Website MyKent Student Link> FORM – Assignment Extension Application Form – Student Login Required) or previously approved application for other extenuating circumstances impacting course of study, incurs a 5% penalty per calendar day, calculated by deduction from the total mark. For example. An Assessment Task marked out of 40 will incur a 2 mark penalty for each calendar day. More information, please refer to (Kent Website MyKent Student Link> POLICY – Assessment Policy & Procedures – Student Login Required)

ASSESSMENT DESCRIPTION:

This assessment is an individual Project. You will use the following scenario to work on and complete all of the required tasks.

Scenario

• A friend of yours has opened Professional Electronics and Repairs (PEAR) to repair smartphones, laptops, tablets, and MP3 players. She wants you to create a database to help her run her business.
• When a customer brings a device to PEAR for repair, data must be recorded about the customer, the device, and the repair. The customer’s name, address, and a contact phone number must be recorded (if the customer has used the shop before, the information already in the system for the customer is verified as being

current). For the device to be repaired, the type of device, model, and serial number are recorded (or verified if the device is already in the system). Only customers who have brought devices into PEAR for repair will be included in this system.

• Since a customer might sell an older device to someone else who then brings the device to PEAR for repair, it is possible for a device to be brought in for repair by more than one customer. However, each repair is associated with only one customer. When a customer brings in a device to be fixed, it is referred to as a repair

request, or just “repair,” for short. Each repair request is given a reference number, which is recorded in the system along with the date of the request, and a description of the problem(s) that the customer wants fixed. It is possible for a device to be brought to the shop for repair many different times, and only devices that are brought in for repair are recorded in the system. Each repair request is for the repair of one and only one device. If a customer needs multiple devices fixed, then each device will require its own repair request.

• There are a limited number of repair services that PEAR can perform. For each repair service, there is a service ID number, description, and charge. “Charge” is how much the customer is charged for the shop to perform the service, including any parts used. The actual repair of a device is the performance of the services necessary to address the problems described by the customer. Completing a repair request may require the performance of many services. Each service can be performed many different times during the repair of different devices, but each service will be performed only once during a given repair request.
• All repairs eventually require the performance of at least one service, but which services will be required may not be known at the time the repair request is made. It is possible for services to be available at PEAR but that have never been required in performing any repair.
• Some services involve only labor activities and no parts are required, but most services require the replacement of one or more parts. The quantity of each part required in the performance of each service should also be recorded. For each part, the part number, part description, quantity in stock, and cost is recorded in the system. The cost indicated is the amount that PEAR pays for the part. Some parts may be used in more than one service, but each part is required for at least one service.

Tasks to be completed:

1. Create a Crow’s Foot notation ERD to support the business operations of PEAR.
   1. Transform your ERD into relational model.
   1. For each table in your relational model, create the dependency diagram and show that your design is in 3NF.
   1. Create the SQL script to create your DB based on your relational model in part b.
   1. Run your script on MySQL Workbench to create your DB.

• Populate every table in your DB with few records.
  • Create SQL statements to do the following:
    • Write SQL statement to show all the records in the table Customer.
    • Write SQL statement to show Customer’s first and last name for those who live in place where the zip code is 2113 or 2114.
    • Write SQL statement to show all records in the table Device where type is ‘Samsung’.
    • Write SQL statement to show Customer’s ID, first name, last name and all Device attributes for those devices they placed for repair.
    • Write SQL statement to list all parts whose description contains the word ‘screen’.
    • Write SQL statement to show all parts with cost between 10 and 50.
    • Write SQL statement to show all the repairs that were performed in March 2022.
    • Write SQL statement to show for each Customer ID, the number of repairs he placed with the company. Show the records in ascending order of Customer ID.
    • Write SQL statement to show for each Service the total cost of all Parts used in that Service.
    • Write SQL statement to show for each repair the number of Services performed for that repair and the total cost for all parts for that repair (including all services).

ASSESSMENT SUBMISSION:

You need to submit three things: a) the report, b) The ERD, the normalisation and the dependency diagram in word document format, c) Database file (Using export option in My SQL workbench or Mysqldump command, d) Queries (execute your queries and take the screen shot).

Put all three files (Word, screenshot, SQL file) in a folder named ‘assm3_studentid’ (e.g. assm3_k123456), zip it and submit it on Moodle.

MARKING GUIDE:

Your work will be assessed as per the following marking criteria. Please read carefully each section/level and marks weightage.

Marking Criteria	Fail (0-49%)	Pass (50-64%)	Credit (65-74%)	Distinction (75-84%)	High Distinction (>85%)
E-R diagram /(20 marks)	No ERD or very poor construction of the ERD diagram without any relationship and                 proper symbols.	ERD is created in basic level using some style, specified some entities, attributes, relationships, and multiplicity.	ERD is created in good level using crow-foot style, well specified entities, attributes, relationships,         and multiplicity. Missing some details such as cardinalities and type of relationships.	ERD       is      created correctly using crow- foot style, correctly specified      entities, attributes, relationships,       and multiplicity. Minimum and             maximum cardinality            are specified. Some of the information such as type of relationships is missing.	ERD is created correctly using crow-foot style, excellently         specified entities,           attributes, relationships,             and multiplicity. Accurately shows minimum and maximum cardinality in the diagram. Relation types correctly identified.
Relational Model /(5 marks)	No or poor translation of ERD into a relational model	Translate ERD into a relational model in very basic level.	Translate ERD into a relational           model properly with some mistakes.	Translate ERD into a relational model very well with occasional mistakes.	Translate ERD into a relational model in excellent way without error.
Normalization /(10 marks)	No normalization checking        was conducted        or mentioned.	Tables in 1 NF only	Tables in 2NF only.	All tables in 3NF with few errors in the process shown	All tables in 3NF with no errors in the process shown
Creating        a database using SQL /(10 marks)	No or very little use      of      with primary        keys, foreign keys, and other    attributes	A database was created, and few primary keys, foreign keys, and other attributes mentioned for	A database was created successfully with most of the tables containing primary   keys,   foreign keys,     and          other	A database was created successfully with        well-defined primary keys, foreign keys,       and       other	A database was created successfully with all required primary keys, foreign keys, and other

	mentioned for all tables and contain data.	all tables and contain data	attributes.        Contain minimal data	attributes mentioned for most of the tables	attributes mentioned for all tables
Inserting Records /(5 marks)	No data was inserted, empty tables.	A few tables were populated with few records in each table to enable them to test their queries	Over 50% tables were populated with few records in each table to enable them to test their queries	Over 80% tables were populated with few records in each table to enable them to test their queries	All the tables were populated with few records in each table to enable them to test their queries
SQL statements 1 & 2 /(10 marks)	All SQL statements wrong,  do  not execute properly.	Some SQL statements are correct    and     execute properly.	Over        50%        SQL statements are correct and execute properly.	All SQL statements are correct.     Occasional error.	All SQL   statements   are correct    and     executes properly without error
SQL statements 3 & 4 /(10 marks)	All SQL statements wrong, do not execute properly.	Some SQL statements are correct and execute properly. The join and like are used properly	Over 50% SQL statements are correct and execute properly. The join and like are used properly	All SQL statements are correct. Occasional error. The join and like are used properly	All SQL statements are correct and executes properly without error . The join and like are used properly
SQL statements 5 & 6 /(10 marks)	All SQL statements wrong, do not execute properly.	Some SQL statements are correct and     execute properly. Using between and like	Over        50%        SQL statements are correct and execute properly. Using between and like	All SQL statements are correct.     Occasional error. Using between and like	All SQL statements are correct    and     executes properly without error . Using between and like
SQL statements 7 & 8 /(10 marks)	All                 SQL statements wrong, do not execute properly.	Some SQL statements are correct and execute properly. Using Date and simple            aggregate functions	Over 50% SQL statements are correct and execute properly. Using Date and simple aggregate functions	All SQL statements are correct. Occasional error. Using Date and simple        aggregate functions	All SQL statements are correct and executes properly without error . Using Date and simple aggregate functions
SQL statements 9 & 10 /(10 marks)	All SQL statements wrong, do not execute properly.	Some SQL statements are correct and execute properly.               More complicated        queries using aggregate functions that requires joins	Over 50% SQL statements are correct and execute properly. More         complicated queries using aggregate functions that requires joins	All SQL statements are correct. Occasional error.                More complicated queries using           aggregate functions that requires joins	All SQL statements are correct and executes properly without error . More complicated queries using aggregate functions that requires joins

GENERAL NOTES FOR ASSESSMENT TASKS

Content for Assessment Task papers should incorporate a formal introduction, main points and conclusion.

Appropriate academic writing and referencing are inevitable academic skills that you must develop and demonstrate in work being presented for assessment. The content of high quality work presented by a student must be fully referenced within-text citations and a Reference List at the end. Kent strongly recommends you refer to the Academic Learning Support Workshop materials available on the Kent Learning Management System (Moodle). For details please click the link http://moodle.kent.edu.au/kentmoodle/mod/folder/view.php?id=3606 and download the file titled “Harvard Referencing Workbook”. This Moodle Site is the location for Workbooks and information that are presented to Kent Students in the ALS Workshops conducted at the beginning of each Trimester.

Kent recommends a minimum of FIVE (5) references in work being presented for assessment. Unless otherwise specifically instructed by your Lecturer or as detailed in the Unit Outline for the specific Assessment Task, any paper with less than five (5) references may be deemed not meeting a satisfactory standard and possibly be failed.

Content in Assessment tasks that includes sources that are not properly referenced according to the “Harvard Referencing Workbook” will be penalised.

Marks will be deducted for failure to adhere to the word count if this is specifically stated for the Assessment Task in the Unit Outline. As a general rule there is an allowable discretionary variance to the word count in that it is generally accepted that a student may go over or under by 10% than the stated length.

GENERAL NOTES FOR REFERENCING

References are assessed for their quality. Students should draw on quality academic sources, such as books, chapters from edited books, journals etc. The textbook for the Unit of study can be used as a reference, but not the Lecturer Notes. The Assessor will want to see evidence that a student is capable of conducting their own research. Also, in order to help Assessors determine a student’s understanding of the work they cite, all in-text

references (not just direct quotes) must include the specific page number(s) if shown in the original. Before preparing your Assessment Task or own contribution, please review this ‘YouTube’ video (Avoiding Plagiarism through Referencing) by clicking on the following link: link: http://moodle.kent.edu.au/kentmoodle/mod/folder/view.php?id=3606

A search for peer-reviewed journal articles may also assist students. These type of journal articles can be located in the online journal databases and can be accessed from the Kent Library homepage. Wikipedia, online dictionaries and online encyclopaedias are acceptable as a starting point to gain knowledge about a topic, but should not be over-used – these should constitute no more than 10% of your total list of references/sources. Additional information and literature can be used where these are produced by legitimate sources, such as government departments, research institutes such as the National Health and Medical Research Council (NHMRC), or international organisations such as the World Health Organisation (WHO). Legitimate organisations and government departments produce peer reviewed reports and articles and are therefore very useful and mostly very current. The content of the following link explains why it is not acceptable to use non-peer reviewed websites (Why can’t I just Google?): https://www.youtube.com/watch?v=N39mnu1Pkgw

(thank you to La Trobe University for access to this video).

visit:https://auspali.info/

Also visit:https://www.notesnepal.com/archives/10012

For solution: +610482078788

+61482073403

+61482072848

For solution:

WhatsApp

+610482078788

+61482073403

+61482072848

[email protected]