Student Name: [Insert Student Name]
\nStudent Number: [Insert Student Number]
\nUnit Code\/s & Name\/s: ICTPRG431 Apply query language in relational databases
\nCluster Name: N\/A
\nIf applicable: N\/A
\nAssessment Type: \u2610 Case Study \u2612 Assignment \u2610 Project \u2610 Exam<\/p>\n
Assessment Name: DBMS and SQL Assignment
\nAssessment Task No.: 1 of 2
\nAssessment Due Date: Week 15
\nDate Submitted: [Insert Date]
\nAssessor Name: [Insert Assessor Name]<\/p>\n
Instructions to Student:<\/p>\n
General instructions:
\nYour role for this assessment item is that of an employee of Uptown IT completing a PD training program organized by the company for employees wishing to apply for senior roles within the Uptown IT Database Management Department.<\/p>\n
Your teacher\/assessor will take on the role of an Uptown IT Database Senior Analyst for this project.<\/p>\n
Read and familiarize yourself with the Project Scenario or Case Study before proceeding with the portfolio parts and tasks. Confirm anything you are not sure about with your teacher. It is essential that you have a clear understanding of the scenario and tasks that you need to complete.<\/p>\n
This assessment instrument requires the student to submit a two (2) parts assignment.<\/p>\n
PART 1. Background knowledge of Structured Query Language (SQL) and database management system (DBMS)<\/p>\n
Task 1. DBMS and SQL knowledge of concepts and terminology
\nTask 2. Selecting and setting up the DBMS environment
\nPART 2. Create a database, create tables, alter table structure and table properties and test database with specific queries<\/p>\n
Task 1. Create database and create tables
\nTask 2. Alter tables
\nTask 3. Populate database using CSV files
\nTask 4. Testing tables with specific queries (6 queries)
\nMaterials required:<\/p>\n
Students are required to provide their own storage device. The recommendation for this qualification is an external SSD drive with at least 256 GB capacity, if you need to store a copy of the Virtual Machine (VM). For assessment files only, a 64 GB thumb drive will be sufficient.
\nAccess to lab computers
\nPCs and peripherals \u2013 these may differ between classrooms
\nInternet access
\nAccess to Connect (LMS)
\nAccess to database server with administration account with sufficient permissions to create and alter structure.
\nDBMS software
\nWord processing software, such as Microsoft Word.
\nSpecial purpose tools, equipment, and materials to complete the assessment
\nOnline delivery:
\n\u2022 Student to supply their own PC or laptop, internet access, and database software<\/p>\n
Documentation:<\/p>\n
Uptown IT Scenario or Case Study
\nCSV files to populate new tables (4 files)
\nSQL file to create table customers
\nAssessment criteria:
\nTo achieve a satisfactory result, your assessor will be looking for your ability to demonstrate the following key skills\/tasks\/knowledge to an acceptable industry standard:<\/p>\n
Knowledge and understanding of SQL concepts and related terminology
\nKnowledge and understanding of DMBS concepts, procedures, features, and functions
\nAbility to create and alter database table structure (a minimum of three (3) tables.
\nUsing appropriate table precision parameters such as:
\nData types and range values
\nPrimary and foreign keys
\nNullability
\nUniqueness
\nAuto_increment
\nIndexing
\nUsing CHECK constraints
\nUsing appropriate naming convention
\nAbility to populate and query tables, filter, retrieve and extract data using calculations, expressions, and number and text operations.
\nAbility to work with transactions
\nRefer to the marking criteria for specific details: ICTPRG431_AT1_MC_TQM_V1<\/p>\n
Details of location:
\nSkills and knowledge in this competency unit must be demonstrated in a workplace or simulated environment where the conditions are typical of those in a working environment in this industry.<\/p>\n
Research and project activities may be conducted in the classroom or at home.<\/p>\n
If you are unable to attend a scheduled assessment activity, you must notify your teacher before the assessment is due and supply a doctor’s certificate and approval from the team manager for an extension.<\/p>\n
Time restrictions:
\nThis assignment is designed to take place over 5 weeks or approximately 20 hours. The student is expected to attend classes as per timetable details and should be able to commit up to 3 hours per week of their own time to study or study-related activities.<\/p>\n
Interactions:
\nTeamwork skills are essential in the IT industry; therefore, you should work in teams to consult and collaborate on practical activities. However, each student must complete the assessment tasks individually (unless indicated).<\/p>\n
Level of assistance permitted:
\nStaff cannot directly show students answers or solutions but support and guide them to complete tasks individually. Teachers and tutors should be available in class and accessible by email for students working from home.<\/p>\n
Contingencies:
\nA reasonable adjustment is available to students for a variety of reasons, including disability, language, literacy, and numeracy (LLN) problems or extenuating circumstances.<\/p>\n
Number of attempts:
\nYou will receive up to two (2) attempts at this assessment task. Should your 1st attempt be unsatisfactory (U), your teacher will provide feedback and discuss the relevant questions with you and will arrange a date for your 2nd attempt. If your 2nd attempt is unsatisfactory (U), or you fail to attend the scheduled date for a 2nd attempt, you will receive an overall unsatisfactory result for this assessment task. Only one re-assessment attempt may be granted for each assessment task.<\/p>\n
Student rules:
\nAccess TAFE Brisbane Student Rules from the TAFE Brisbane website. The Student Rules and Policies page provide information on your rights and responsibilities as a student. In terms of assessment, we recommend that you read the Assessment, Progression, and Misconduct section. Under this section, you will find Academic Misconduct. Plagiarism is a form of academic misconduct and will not be tolerated. It is a breach of assessment and academic progression rules. When you submit written work and more specifically research work, you must formally reference all sources using a formal referencing style such as APA or Harvard.<\/p>\n
Familiarize yourself with the content of the Student Rules, and if you require any further clarification, do not hesitate to contact your teacher.<\/p>\n
Work, Health and Safety:
\nThe work environment should be assessed for safety prior to class. Special considering should be taken regarding potential ICT-related hazards such as tripping hazards, electromagnetic radiation, ergonomics, and posture. TAFE Queensland health and safety policies and procedures should be followed at all times.<\/p>\n
Submission details: Evidence Required to be Submitted: Instructions for the Assessor: Online delivery: Specifications of assessment: development environment Storage devices:<\/p>\n Students are required to provide their own storage device. The recommendation for this qualification is an external SSD drive with at least 256 GB capacity, if you need to store a copy of the Virtual Machine (VM). For assessment files only, a 64 GB thumb drive will be sufficient. Access to lab computers Uptown IT Scenario or Case Study Interactions: Contingencies: Work, Health and Safety: Note to Student: An overview of all Assessment Tasks relevant to this unit is located in the Unit Study Guide.<\/p>\n Scenario: The selection criteria specify that potential candidates for senior roles must complete the following steps:<\/p>\n Step one: Interview and attitude test You have successfully completed steps 1 to 3, and this is your final step.<\/p>\n Uptown IT Database Department director believes that senior roles within the department must possess sound practical knowledge and skills of all areas related to the department, and this includes the practical DBMS administration and SQL skills.<\/p>\n For this last challenge, your task is to:<\/p>\n Evidence knowledge of DBMS and structured query language (SQL) concepts and terminology PART 1. Background knowledge of Structured Query Language (SQL) and database management system (DBMS)<\/p>\n Task 1. DBMS and SQL knowledge of concepts and terminology<\/p>\n This task (1.1 to 1.12) requires you to research and provide definitions\/answers for a number of concepts and\/or questions. Provide the answers in your own words. Plagiarism is a form of academic misconduct and will not be tolerated. Include references for all your sources using a formal referencing style such as APA or Harvard.<\/p>\n 1.1 The following terms and phrases are commonly used in relational databases and structure query language (SQL). Provide a brief description of each term\/phrase and an example of its usage.<\/p>\n TERM\/PHRASE\tDESCRIPTION and EXAMPLE Comparison re Features and Costs:<\/p>\n DBMS Name\tFeatures\tCosts Data Manipulation Language (DML):<\/p>\n Data Definition Language (DDL):<\/p>\n Data Control Language (DCL):<\/p>\n 1.4 Internally generated business data and external data, acquired from multiple sources, are the most common methods industry use to gather data that will later be used in relational databases. Evaluate them and select the most reliable.<\/p>\n Internal: [Insert Evaluation] 1.5 Connection methods:<\/p>\n a) Identify and outline the characteristics of at least one (1) DBMS connection method that is language and platform independent.<\/p>\n Characteristics of at least one (1) DBMS connection method: [Insert Characteristics]<\/p>\n b) Are DBMS connection methods client-side or server-side? Explain your answer.<\/p>\n Client-side or server-side? Explanation: [Insert Explanation]<\/p>\n 1.6 The purpose of data mining is to find patterns in the data. Discuss three (3) techniques used in data mining to find patterns.<\/p>\n Technique Name\tDiscussion Characteristic\tDefinition 1.9 Evaluate the suitability of the DBMS listed below for a small size business that sells mobile phone accessories online. Select one DBMS and justify your selection.<\/p>\n MYSQL 1.10 Procedures and systems that can be used in SQL may include stored procedures and the ACID model.<\/p>\n a) Outline the functionality of a stored procedure. [Insert Outline]<\/p>\n b) What is the relation between atomicity (ACID property) and transactions in relational databases and SQL? [Insert Relation]<\/p>\n 1.11 What types of parameters can be applied to stored procedures in SQL? Select at least two (2) types of parameters and outline their functionality.<\/p>\n Parameter Type: [Insert Type] Parameter Type: [Insert Type] 1.12 Examine the statement below and put a name to the type of data analysis technique that is described in it. In what type of data analysis would you use the technique described? Provide a detailed answer.<\/p>\n A type of analysis that uses historical data to understand how a dependent variable’s value reacts to changes in one or more independent variables – Task 2. Selecting and setting up the DBMS environment<\/p>\n 1.13 Before proceeding to PART 2, you must select an appropriate database management system (DBMS). Uptown IT uses Microsoft SQL Server, but depending on the context environment in which you are performing the tasks, you may have access to a number of server administration tools such as Microsoft SQL Server Management Studio or a similar tool. Set up the environment in preparation to start using SQL. The environment setup includes:<\/p>\n a) Access to the selected or available DBMS and administration tool Paste in an appropriate screen image of your installed Century Inventions database: [Insert Screen Image]<\/p>\n 1.14 Contingency task. Assume that you have attempted to import the data from the export\/dump file, and two tables are not imported. All other tables are correct. You have used a text editor to view the file content, and all the data appears to be correct. This includes table structure and data.<\/p>\n What process would you follow to resolve the situation? [Insert Process]<\/p>\n PART 2. Create a database, create tables, alter table structure and table properties, and test the database with specific queries<\/p>\n TASK 1. Create database and tables<\/p>\n In this task, you must create a database and four (4) tables. This is just a subset of a database to test your practical skills on this topic. Before you start creating the tables and columns, you need to select a naming convention and use it consistently.<\/p>\n 2.1 Create a database and name it: CarolinesClassroomRobots.<\/p>\n Paste in an appropriate screen image of your Caroline’s Classroom Robots database: [Insert Screen Image]<\/p>\n 2.2 Using an appropriate naming convention, create four (4) tables using the details provided below for table names and column headings:<\/p>\n Table 1 \u2013 Class: ClassID, PrimaryLocation, TeacherName Data for these tables are provided as CSV and Microsoft Excel files, and these will give you an indication of what data types are required for the fields. You do not need to import the data into the tables; this is done in a later question. Provide screen images of the tables once created.<\/p>\n Paste in an appropriate screen image (or images) of your tables for your Caroline’s Classroom Robots database: [Insert Screen Images]<\/p>\n 2.3 Identify the relation and cardinality ratio between the four tables and create appropriate foreign keys (FK). Provide a small diagram (a mini ERD) that displays the table relationships and the cardinality ratio (e.g., one-to-one or many-to-one)<\/p>\n Paste in a copy of your mini ERD for your Caroline’s Classroom Robots database: [Insert Mini ERD]<\/p>\n 2.4 Identify and properly create the primary keys (PK) for each table. The PK must be set to auto_increment. (What you need to submit for this item is noted below.)<\/p>\n 2.5 For each column, select the appropriate precision parameters. These may include: data type, range, attributes, default value, uniqueness, and nullability. (What you need to submit for this item is noted below.)<\/p>\n 2.6 For each table, identify the foreign keys (FKs) and set up the required relationships between the tables. (What you need to submit for this item is noted below.)<\/p>\n 2.7 Once the tables have been created, identify which columns have been indexed by the system and explain why.<\/p>\n Paste in an appropriate screen image of the Relationships screen from your Caroline’s Classroom Robots database: [Insert Screen Image]<\/p>\n Export a data dictionary or SQL script that lists or demonstrates that you have completed items 2.4-2.6. The name of your associated output file is: << Filename >><\/p>\n Or\u2026 Task 2. Alter table structure<\/p>\n 2.8 Write an SQL statement to alter the Class table and include an extra column named Notes (use the appropriate naming convention selected in Task 1).<\/p>\n Paste in your SQL Statement: [Insert SQL Statement]<\/p>\n 2.9 You have discovered that the Email field in the Students table is not large enough for some email addresses. Write an SQL statement to alter the Student table to ensure the email field is twice as large as before.<\/p>\n Paste in your SQL Statement: [Insert SQL Statement]<\/p>\n 2.10 Alter the ClassAttendanceLines table to add CHECK constraints to ensure that the Arrival Time never has a time entered prior to 7 am.<\/p>\n Paste in your SQL Statement: [Insert SQL Statement]<\/p>\n Task 3. Populate the database using CSV files<\/p>\n 2.11 Using the .txt files provided in the assessment folder, import the data into the corresponding tables. Each worksheet or .txt file corresponds to one of the tables:<\/p>\n Class.txt Paste in an appropriate screen image of your Class table with imported data: [Insert Screen Image]<\/p>\n Paste in an appropriate screen image of your ClassAttendance table with imported data: [Insert Screen Image]<\/p>\n Paste in an appropriate screen image of your ClassAttendanceLines table with imported data: [Insert Screen Image]<\/p>\n Paste in an appropriate screen image of your Students table with imported data: [Insert Screen Image]<\/p>\n Task 4. Testing tables with specific queries (6 queries)<\/p>\n Using the tables you created, write and execute the following SQL queries:<\/p>\n 2.12 Retrieve the details of all the classes taught by a teacher named “John Smith.”<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n 2.13 Retrieve the attendance records for all students who attended class on a specific date (e.g., 2023-06-01).<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n 2.14 Retrieve the full names (concatenated first name and last name) of all students who have a parent or guardian named “Lisa Brown.”<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n 2.15 Retrieve the number of students in each class.<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n 2.16 Retrieve the student IDs and arrival times for all students who arrived after 8 am.<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n 2.17 Retrieve the student IDs and departure times for all students who departed before 3 pm.<\/p>\n SQL Query: [Insert SQL Query]<\/p>\n END OF ASSESSMENT TASK 1<\/p>\n Ensure that you have included your name, student number, and signature on page 1 and signed the Student Declaration.<\/p>\n Attach the completed document and submit it via Connect.<\/p>\n","protected":false},"excerpt":{"rendered":" Student Name: [Insert Student Name] Student Number: [Insert Student Number] Unit Code\/s & Name\/s: ICTPRG431 Apply query language in relational databases Cluster Name: N\/A If applicable: N\/A Assessment Type: \u2610 Case Study \u2612 Assignment \u2610 Project \u2610 Exam Assessment Name: DBMS and SQL Assignment Assessment Task No.: 1 of 2 Assessment Due Date: Week 15 […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3695,3691,3690,1387,1388,1386,1385,3694,3689,3692,3688,1389,1390,3693],"tags":[1166,1175,1177,3701,3699,2875,3697,3700,3696,1176,1117,3698],"class_list":["post-8797","post","type-post","status-publish","format-standard","hentry","category-assignment-help-by-uks-no-1-writing-service","category-australia-essays","category-best-essay-writers-pinterest","category-cheap-essay-writer-australia","category-cheap-essay-writer-canada","category-cheap-essay-writer-uk","category-cheap-essay-writer-usa","category-dissertation-assignment-help-uae","category-do-my-university-assignment-for-me","category-essays-uk","category-homework-help-services-best-websites","category-homework-help-writing-service-usa","category-nursing-help-writing-service-usa","category-research-paper-ideas","tag-australia-dissertation-writers","tag-australia-essay-writers","tag-canada-essay-writers","tag-essay-pro-dissertation-writers","tag-i-need-help-writing-a-page-paper-assignment","tag-ireland-essay-writers","tag-masters-essays","tag-pay-someone-to-write-a-paper-for-you","tag-phd-assignment-help","tag-uae-essay-writers","tag-usa-essay-writers","tag-website-that-writes-assignments"],"_links":{"self":[{"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/posts\/8797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/comments?post=8797"}],"version-history":[{"count":0,"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/posts\/8797\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/media?parent=8797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/categories?post=8797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.colapapers.com\/us\/wp-json\/wp\/v2\/tags?post=8797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nInsert your details on page 1 and sign the Student Declaration. Include this template with your submission.
\nSubmission via Connect: Upload a single file into Assessment 1 (AT1) dropbox in Connect.
\nName the file:
\nICTPRG431_AT1_W_Surname_Student Number
\nTAFE Queensland Learning Management System:
\nAccessing Connect:
\nConnect URL: https:\/\/connect.tafeqld.edu.au\/d2l\/login
\nUsername: 9 digit student number
\nPassword:
\nFor password reset, go to: https:\/\/passwordreset.tafeqld.edu.au\/default.aspx<\/your><\/p>\n
\nPlease revise and modify the Instructions to Student section if you are delivering online.<\/p>\n
\nTo be judged competent in this assessment item, the student is required to demonstrate competence in all indicators shown in the marking guide.
\nGather evidence to demonstrate consistent performance in conditions that are safe and replicate the workplace. Noise levels, production flow, interruptions, and time variances must be typical of those experienced in the web development field of work and include access to:<\/p>\n
\ntask requirements
\nAssessment Conditions:
\nSkills and knowledge in this competency unit must be demonstrated in a workplace or simulated environment where the conditions are typical of those in a working environment in this industry.<\/p>\n
\nAssessor to provide access to:<\/p>\n
\nInternet access
\nAccess to Connect (LMS)
\nAccess to the database server with administration account with sufficient permissions to create and alter the structure.
\nDBMS software
\nWord processing software, such as Microsoft Word.
\nSpecial purpose tools, equipment, and materials to complete the assessment
\nDocuments\/files supplied:<\/p>\n
\nCSV files to populate new tables (4 files)
\nSQL file to create table customers
\nLevel of Assistance Permitted:
\nTeachers and tutors should be available in class and accessible by email for students working from home. Staff cannot directly show students answers but guide them to where to go to complete tasks individually. Students with disabilities will receive reasonable adjustments.<\/p>\n
\nTeamwork skills are essential in the IT industry; therefore, you should work in teams to consult and collaborate on practical activities. However, each student must complete the assessment tasks individually (unless indicated).<\/p>\n
\nA reasonable adjustment is available to students for a variety of reasons, including disability, language, literacy, and numeracy (LLN) problems or extenuating circumstances.<\/p>\n
\nThe work environment should be assessed for safety prior to class. Special consideration should be taken regarding potential ICT-related hazards such as tripping hazards, electromagnetic radiation, ergonomics, and posture. TAFE Queensland health and safety policies and procedures should be followed at all times.<\/p>\n
\nYour role for this assessment item is that of an employee of Uptown IT completing a PD training program organized by the company for those employees wishing to apply for senior roles within the Uptown IT Database Management Department.<\/p>\n
\nStep two: Planning, scoping, and budgeting (project management)
\nStep three: Database analysis, design, and modeling
\nStep four: Practical DBMS and SQL skills<\/p>\n
\nCreate a database subset (four tables), alter the structure of the tables, populate the tables using CSV files, and test the database with six (6) queries
\nYour teacher\/assessor will take on the role of an Uptown IT Database Senior Analyst for this project.<\/p>\n
\nData types\t[Insert Description]
\nPrimary key\t[Insert Description]
\nForeign key\t[Insert Description]
\nNull values\t[Insert Description]
\nIndexing\t[Insert Description]
\nExpressions\t[Insert Description]
\nLogical operators\t[Insert Description]
\nTransactions\t[Insert Description]
\nStored Procedures\t[Insert Description]
\nViews\t[Insert Description]
\nTriggers\t[Insert Description]
\n1.2 What are the characteristics of a DBMS? List three (3) current industry standard relational DBMS used in the industry and compare them in terms of features and costs.<\/p>\n
\n1.3 The three main components of Structured Query Language (SQL) are data manipulation language (DML), data definition language (DDL), and data control language (DCL). What type functionality can be achieved by each component? Provide three (3) functions for each component.<\/p>\n
\nExternal: [Insert Evaluation]
\nMost Reliable: [Insert Selection]<\/p>\n
\n1.7 Finding reliable data sources is a desirable but difficult undertaking. Define three (3) characteristics of a reliable data source.<\/p>\n
\n1.8 Specify two (2) desirable features of an information system.<\/p>\n
\nMicrosoft SQL Server
\nORACLE
\n[Insert Evaluation and Justification]<\/p>\n
\nFunctionality: [Insert Functionality]<\/p>\n
\nFunctionality: [Insert Functionality]<\/p>\n
\n[Insert Technique Name]
\n[Insert Explanation]<\/p>\n
\nb) Access to an administrator account on the DBMS or a user account with permissions to create and alter tables.
\nc) Access to the century_inventions database. To install the database, you can use the century_inventions.bak or the century_inventions.sql file (database export file) and use it to import the database structure and data.<\/p>\n
\nTable 2 \u2013 ClassAttendance: AttendanceID, TeacherName, ClassID, DateCompleted, TimeCompleted, Note
\nTable 3 \u2013 Students: StudentID, FirstName, LastName, Email, ParentGuardianNames, ParentGuardianPhones, Note
\nTable 4 \u2013 ClassAttendanceLines: ClassAttendanceLineID, AttendanceID, StudentID, ArrivalTime, DepartureTime, Note<\/p>\n
\nGo into each table design, right-click on a field, and go to indexes\/keys. Investigate the indexes on each table.<\/p>\n
\nClassAttendance.txt
\nClassAttendanceLines.txt
\nStudents.txt
\nIf there are issues with the imported data, please identify the problem and provide a solution or explanation of how you would resolve the issue.<\/p>\n