ON the file please
Prepared by: Dr. Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 Assessment Details and Submission Guidelines Unit Code BN204 Unit Title Database Technologies Term, Year T3, 2021 Assessment Type Assignment 1 (Individual Assessment) Assessment Title Developing and Managing Databases Assessment Author Dr. Deepani Guruge Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool. On completion of this assignment students will be able to: a. Describe issues related to the integrity and security of database (DB) systems. b. Make an informed and critical assessment of database management systems (DBMS). c. Develop data models and implement DB systems. Weight 75 = (Part A 25 marks) + (for Part B 50 marks) Total Marks 15% of total assessment for the unit Word limit Not Applicable Due Date This assignment consists of two parts. Due dates are given below: Part A: -Database creation and SQL queries due in WEEK 3, 3rd December 2021, before 11.50 PM 5% of total assessment for the unit Part B: Advance SQL queries, issues related to the integrity and research question due in WEEK 8, 7th January 2022, before 11.55 PM 10% of total assessment for the unit Submission Guidelines Part A: -Submit WEEK 3 on Moodle. Submit 2 files o Database created o MS Word document with SQL queries and screen shots. Part B: Submit WEEK 8 on Moodle. Submit 2 files o Database created o MS Word document with the following on Moodle -due in Advance SQL queries, issues related to the integrity and a research question Part A and B should save separately as below. “BN204_2021_Assigment1_your_name.doc”. The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.5 cm margins on all four sides of your page with appropriate section headings. BN204 Database Technologies Page | 2 Prepared by: Dr Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using IEEE referencing style. Extension If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School's Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7. You must submit this application three working days prior to the due date of the assignment. Further information is available at: http://www.mit.edu.au/about-mit/institute-publications/policies-procedures- and-guidelines/specialconsiderationdeferment Academic Misconduct Academic Misconduct is a serious offence. Depending on the seriousness of the case, penalties can vary from a written warning or zero marks to exclusion from the course or rescinding the degree. Students should make themselves familiar with the full policy and procedure available at: http://www.mit.edu.au/about- mit/institute-publications/policies-procedures-and-guidelines/Plagiarism- Academic-Misconduct-Policy-Procedure. For further information, please refer to the Academic Integrity Section in your Unit Description. http://www.mit.edu.au/about http://www.mit.edu.au/about http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure BN204 Database Technologies Page | 3 Prepared by: Dr Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 Assignment Description This assignment consists of two parts. Answer All Questions (Part A and Part B). Part A: (25 Marks) 1. The snapshot of online Golf club management system database structure (GolfClubDB) is given below. GolfClubDB is a database that keeps track of information about the clubs, members, Professionals (who provide professional golf coaching) and details of lesson bookings. Assume that you are working as an IT specialist in this organisation and required to extract information from this database by executing SQL queries according to the instructions given below. The primary keys (PK), Foreign Keys (FK or PF) are marked in the following snapshot of database, shown in Figure 1. Figure 1: Snapshot of database for Golf Club members © Database Answers Ltd CREATE DATABASE – a. For Part A, create only 2 tables Golf_clubs and Members with all attributes given in the above database structure and the relationship between the two tables. You do not have to create other tables for assignment 1 Part A. Use MS Access. Populate those tables with suitable data (at least 3 records per table). You can use datasheet view in MS Access to enter suitable data records. Include Proper foreign keys to create relationships in between tables. Hint: If you want to create a one-to-many relationship in your database, include one side primary key in the many side table as foreign keys. BN204 Database Technologies Page | 4 Prepared by: Dr Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 NB: You need to upload your database on submission link before the due date and 10 marks will be deducted as per assignment ‘Marking Criteria’ for not submitting the database file. This is an individual assignment; it should be your own individual work (You should not copy Ms Access Database). If not, it is considered as cheating and you will get zero marks for the whole assignment. (10 Marks) 2. WRITE SQL QUERIES Write SQL queries (do not use QBE) for the following questions and execute the queries on the above “GolfClubDB ” database (created on MS Access). NB: Include screen shots of the outputs and write all SQL statements you used to answer following questions. (In each question, 3 marks for each screen shot & remaining marks for the SQL query) a. Display details of all Members in the database. Your result set should be sorted on ascending order of the “Member_first_name”. (5 Marks) b. Assume you need to find the details of all the Golf clubs established in 2004 (i.e year_established attribute is 2004), in the Golf_clubs Table. (5 Marks) c. Display total number of Members registered in the system. (5 Marks) --------------------END OF ASSIGNMENT 1A ---------------------------- BN204 Database Technologies Page | 5 Prepared by: Dr Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 Part B: (50 Marks) - 3 Questions 1. Add remaining tables to the database created in Assignment 1 Add remaining tables to the same database you created in Part A. Make sure you add suitable data items to the tables in the database you created in order to answer the following queries in Question2. (5 Marks) 2. SQL Queries -execute on “CarServicingDB ” Write SQL queries (do not use QBE) for the following questions and execute the queries on the “GolfClubDB” database created on MS Access. Include screen shots of the outputs and copy all SQL statements you used to answer following questions. (In each question 3 marks for each screen shot & remaining marks for the SQL query) a. Assume you need to find out member_Id, member_first_name, golf_club_Id, lesson_date, lesson_fee_amount of all members. (Hint: Here you need to join two tables) (5 Marks) b. This is an extension to query in question 2a. How do you filter the output receive in question 2a to obtain same information about members(member_Id, member_first_name, golf_club_Id, lesson_date, lesson_fee_amount) who needs to pay more than $200 for their lessons? Write a query. (5 Marks) c. Find the total of payments that need to be received for all the lessons scheduled on 20th December 2021. Eg Date type variable matching can be done by writing the date in format #24-sept-2021#. (5 Marks) d. Assume Golf club database administrator wants to group all the records by Golf_club_id and print following information. Write a SQL Query to perform this task. Member_id, member_email, member_first_name, golf_club_id, lesson_date, pro_first_name , pro_last_name (first and last names of the golf coaching professionals) (Hint: You may need to Join 3 tables) (10 Marks) 3. Make an informed and critical assessment of database and Issues related to integrity of databases a. Discuss why normalisation is important in database design process. (2 Marks) BN204 Database Technologies Page | 6 Prepared by: Dr Deepani Guruge Moderated by: Dr. Sadia Aziz Nov, 2021 b. This question is on “ER-to-Relational Mapping”. Figure 1 shows the ER diagram that captures important information about Golf clubs and the members. Convert the ER diagram in Figure 1 into