Posted: May 15th, 2023

DBMS and SQL Assignment

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: ☐ Case Study ☒ Assignment ☐ Project ☐ Exam

Assessment Name: DBMS and SQL Assignment
Assessment Task No.: 1 of 2
Assessment Due Date: Week 15
Date Submitted: [Insert Date]
Assessor Name: [Insert Assessor Name]

Instructions to Student:

General instructions:
Your 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.

Your teacher/assessor will take on the role of an Uptown IT Database Senior Analyst for this project.

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.

This assessment instrument requires the student to submit a two (2) parts assignment.

PART 1. Background knowledge of Structured Query Language (SQL) and database management system (DBMS)

Task 1. DBMS and SQL knowledge of concepts and terminology
Task 2. Selecting and setting up the DBMS environment
PART 2. Create a database, create tables, alter table structure and table properties and test database with specific queries

Task 1. Create database and create tables
Task 2. Alter tables
Task 3. Populate database using CSV files
Task 4. Testing tables with specific queries (6 queries)
Materials required:

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
PCs and peripherals – these may differ between classrooms
Internet access
Access to Connect (LMS)
Access to database server with administration account with sufficient permissions to create and alter structure.
DBMS software
Word processing software, such as Microsoft Word.
Special purpose tools, equipment, and materials to complete the assessment
Online delivery:
• Student to supply their own PC or laptop, internet access, and database software

Documentation:

Uptown IT Scenario or Case Study
CSV files to populate new tables (4 files)
SQL file to create table customers
Assessment criteria:
To 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:

Knowledge and understanding of SQL concepts and related terminology
Knowledge and understanding of DMBS concepts, procedures, features, and functions
Ability to create and alter database table structure (a minimum of three (3) tables.
Using appropriate table precision parameters such as:
Data types and range values
Primary and foreign keys
Nullability
Uniqueness
Auto_increment
Indexing
Using CHECK constraints
Using appropriate naming convention
Ability to populate and query tables, filter, retrieve and extract data using calculations, expressions, and number and text operations.
Ability to work with transactions
Refer to the marking criteria for specific details: ICTPRG431_AT1_MC_TQM_V1

Details of location:
Skills 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.

Research and project activities may be conducted in the classroom or at home.

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.

Time restrictions:
This 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.

Interactions:
Teamwork 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).

Level of assistance permitted:
Staff 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.

Contingencies:
A reasonable adjustment is available to students for a variety of reasons, including disability, language, literacy, and numeracy (LLN) problems or extenuating circumstances.

Number of attempts:
You 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.

Student rules:
Access 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.

Familiarize yourself with the content of the Student Rules, and if you require any further clarification, do not hesitate to contact your teacher.

Work, Health and Safety:
The 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.

Submission details: Evidence Required to be Submitted:
Insert your details on page 1 and sign the Student Declaration. Include this template with your submission.
Submission via Connect: Upload a single file into Assessment 1 (AT1) dropbox in Connect.
Name the file:
ICTPRG431_AT1_W_Surname_Student Number
TAFE Queensland Learning Management System:
Accessing Connect:
Connect URL: https://connect.tafeqld.edu.au/d2l/login
Username: 9 digit student number
Password:
For password reset, go to: https://passwordreset.tafeqld.edu.au/default.aspx

Instructions for the Assessor: Online delivery:
Please revise and modify the Instructions to Student section if you are delivering online.

Specifications of assessment:
To be judged competent in this assessment item, the student is required to demonstrate competence in all indicators shown in the marking guide.
Gather 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:

development environment
task requirements
Assessment Conditions:
Skills 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.

Storage devices:

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.
Assessor to provide access to:

Access to lab computers
Internet access
Access to Connect (LMS)
Access to the database server with administration account with sufficient permissions to create and alter the structure.
DBMS software
Word processing software, such as Microsoft Word.
Special purpose tools, equipment, and materials to complete the assessment
Documents/files supplied:

Uptown IT Scenario or Case Study
CSV files to populate new tables (4 files)
SQL file to create table customers
Level of Assistance Permitted:
Teachers 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.

Interactions:
Teamwork 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).

Contingencies:
A reasonable adjustment is available to students for a variety of reasons, including disability, language, literacy, and numeracy (LLN) problems or extenuating circumstances.

Work, Health and Safety:
The 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.

Note to Student: An overview of all Assessment Tasks relevant to this unit is located in the Unit Study Guide.

Scenario:
Your 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.

The selection criteria specify that potential candidates for senior roles must complete the following steps:

Step one: Interview and attitude test
Step two: Planning, scoping, and budgeting (project management)
Step three: Database analysis, design, and modeling
Step four: Practical DBMS and SQL skills

You have successfully completed steps 1 to 3, and this is your final step.

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.

For this last challenge, your task is to:

Evidence knowledge of DBMS and structured query language (SQL) concepts and terminology
Create 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
Your teacher/assessor will take on the role of an Uptown IT Database Senior Analyst for this project.

PART 1. Background knowledge of Structured Query Language (SQL) and database management system (DBMS)

Task 1. DBMS and SQL knowledge of concepts and terminology

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.

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.

TERM/PHRASE DESCRIPTION and EXAMPLE
Data types [Insert Description]
Primary key [Insert Description]
Foreign key [Insert Description]
Null values [Insert Description]
Indexing [Insert Description]
Expressions [Insert Description]
Logical operators [Insert Description]
Transactions [Insert Description]
Stored Procedures [Insert Description]
Views [Insert Description]
Triggers [Insert Description]
1.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.

Comparison re Features and Costs:

DBMS Name Features Costs
1.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.

Data Manipulation Language (DML):

Data Definition Language (DDL):

Data Control Language (DCL):

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.

Internal: [Insert Evaluation]
External: [Insert Evaluation]
Most Reliable: [Insert Selection]

1.5 Connection methods:

a) Identify and outline the characteristics of at least one (1) DBMS connection method that is language and platform independent.

Characteristics of at least one (1) DBMS connection method: [Insert Characteristics]

b) Are DBMS connection methods client-side or server-side? Explain your answer.

Client-side or server-side? Explanation: [Insert Explanation]

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.

Technique Name Discussion
1.7 Finding reliable data sources is a desirable but difficult undertaking. Define three (3) characteristics of a reliable data source.

Characteristic Definition
1.8 Specify two (2) desirable features of an information system.

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.

MYSQL
Microsoft SQL Server
ORACLE
[Insert Evaluation and Justification]

1.10 Procedures and systems that can be used in SQL may include stored procedures and the ACID model.

a) Outline the functionality of a stored procedure. [Insert Outline]

b) What is the relation between atomicity (ACID property) and transactions in relational databases and SQL? [Insert Relation]

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.

Parameter Type: [Insert Type]
Functionality: [Insert Functionality]

Parameter Type: [Insert Type]
Functionality: [Insert Functionality]

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.

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 –
[Insert Technique Name]
[Insert Explanation]

Task 2. Selecting and setting up the DBMS environment

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:

a) Access to the selected or available DBMS and administration tool
b) Access to an administrator account on the DBMS or a user account with permissions to create and alter tables.
c) 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.

Paste in an appropriate screen image of your installed Century Inventions database: [Insert Screen Image]

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.

What process would you follow to resolve the situation? [Insert Process]

PART 2. Create a database, create tables, alter table structure and table properties, and test the database with specific queries

TASK 1. Create database and tables

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.

2.1 Create a database and name it: CarolinesClassroomRobots.

Paste in an appropriate screen image of your Caroline’s Classroom Robots database: [Insert Screen Image]

2.2 Using an appropriate naming convention, create four (4) tables using the details provided below for table names and column headings:

Table 1 – Class: ClassID, PrimaryLocation, TeacherName
Table 2 – ClassAttendance: AttendanceID, TeacherName, ClassID, DateCompleted, TimeCompleted, Note
Table 3 – Students: StudentID, FirstName, LastName, Email, ParentGuardianNames, ParentGuardianPhones, Note
Table 4 – ClassAttendanceLines: ClassAttendanceLineID, AttendanceID, StudentID, ArrivalTime, DepartureTime, Note

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.

Paste in an appropriate screen image (or images) of your tables for your Caroline’s Classroom Robots database: [Insert Screen Images]

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)

Paste in a copy of your mini ERD for your Caroline’s Classroom Robots database: [Insert Mini ERD]

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.)

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.)

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.)

2.7 Once the tables have been created, identify which columns have been indexed by the system and explain why.

Paste in an appropriate screen image of the Relationships screen from your Caroline’s Classroom Robots database: [Insert Screen Image]

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 >>

Or…
Go into each table design, right-click on a field, and go to indexes/keys. Investigate the indexes on each table.

Task 2. Alter table structure

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).

Paste in your SQL Statement: [Insert SQL Statement]

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.

Paste in your SQL Statement: [Insert SQL Statement]

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.

Paste in your SQL Statement: [Insert SQL Statement]

Task 3. Populate the database using CSV files

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:

Class.txt
ClassAttendance.txt
ClassAttendanceLines.txt
Students.txt
If there are issues with the imported data, please identify the problem and provide a solution or explanation of how you would resolve the issue.

Paste in an appropriate screen image of your Class table with imported data: [Insert Screen Image]

Paste in an appropriate screen image of your ClassAttendance table with imported data: [Insert Screen Image]

Paste in an appropriate screen image of your ClassAttendanceLines table with imported data: [Insert Screen Image]

Paste in an appropriate screen image of your Students table with imported data: [Insert Screen Image]

Task 4. Testing tables with specific queries (6 queries)

Using the tables you created, write and execute the following SQL queries:

2.12 Retrieve the details of all the classes taught by a teacher named “John Smith.”

SQL Query: [Insert SQL Query]

2.13 Retrieve the attendance records for all students who attended class on a specific date (e.g., 2023-06-01).

SQL Query: [Insert SQL Query]

2.14 Retrieve the full names (concatenated first name and last name) of all students who have a parent or guardian named “Lisa Brown.”

SQL Query: [Insert SQL Query]

2.15 Retrieve the number of students in each class.

SQL Query: [Insert SQL Query]

2.16 Retrieve the student IDs and arrival times for all students who arrived after 8 am.

SQL Query: [Insert SQL Query]

2.17 Retrieve the student IDs and departure times for all students who departed before 3 pm.

SQL Query: [Insert SQL Query]

END OF ASSESSMENT TASK 1

Ensure that you have included your name, student number, and signature on page 1 and signed the Student Declaration.

Attach the completed document and submit it via Connect.