ICTDBS403 Create basic databases Assignment

Reference no: EM132510336

ICTDBS403 Create basic databases - Victoria University

Assessment #1 - Radio Database
Detailed instructions and files required to complete this assessment are located on the Moodle Database Fundamentals page.

PR1.
Database Requirements

Research client requirements for a database solution
Document the information that the database is required to hold.
Document the functionality of the database.
Identify the components of the database.
Document all operating system usernames and passwords.
Document all database usernames and passwords.

PR2.
Create a MariaDB database

Write and test a script that can be executed in the MariaDB client that will create a database with empty tables that implements the database from your ERD.

PR3.
Populate the database

Write and test some scripts that will load data into your new empty database tables.
PR4.
Test and debug the database

Ensure the database tables exist and have the correct number of columns. Ensure that column names are correct, and all fields are populated.
If errors are found, you may need to drop the database tables and recreate them.

PR5.
Develop SQL queries

Write and test suitable scripts that will produce the desired output when executed manually in the MariaDB client.

Query 1
Provide a screenshot of all distinct usernames from playlists.
Query 2
Provide a screenshot of all playlist names containing a song with the word "at" in the title. Include the playlist name, the track title, and the track artist in your output.
Query 3
Provide a screenshot of all playlist names containing a song by Faithless. Include the playlist name, the track title, and the track artist in your output.
Query 4
Provide a screenshot of all playlist names containing the genre Jazz or Jazz-House.
Include the playlist name, the track title, the track artist, and the track genre in your output.
Query 5
Provide a screenshot of all distinct playlist names that were created by user Mod_11.
Query 6
Provide a screenshot of all playlist names containing a song with the word "at" in the title. Include the playlist name, the track title, and the track artist in your output. Sort the result by track artist.
Query 7
Provide a screenshot of the thirteenth item from playlist "Mod_41 = 7" when the result is sorted by track artist. Include the playlist name, the track title, and the track artist in your output.
Query 8
Provide a screenshot of the number of tracks in the playlist "Mod_41 = 7".
Query 9
Provide a screenshot of the calculated length of the playlist "Mod_41 = 7".
Query 10
Provide a screenshot of the track title and the length of the track title of the 13th item from playlist "Mod_41 = 7" when the result is sorted by track artist.
Query 11
Provide a screenshot of all distinct usernames from playlists in lowercase.
Query 12
Provide a screenshot of all distinct usernames from playlists in uppercase.
PR6.
Create, Read, Update, and Delete (CRUD)

Using INSERT INTO, UPDATE,
and DELETE, insert a new record, update that same record, then delete that record from the playlists table. Ensure one row is affected for each command. Use a SELECT statement to show what each command achieved.

PR7.
Database Optimization

Check if optimisation is required.
Optimise if necessary. Provide screenshots as evidence.
Document any optimisation performed.
PR8.
Backup and Recovery

Create a backup of the radio database.
Restore the radio database to a new database.
Submit the database backup file to Moodle.
Provide screenshots as evidence.

PR9.
Normalization
PR10.
Data Dictionary

Develop a Data Dictionary based on the normalized ERD defining the data items to be included in the database tables.
Submit the Data Dictionary to Moodle.

Assessment #2 - Customer Database
Detailed instructions and files required to complete this assessment are located on the Moodle Database Fundamentals page.

Project Task
PR1.
Database Requirements

Research client requirements for a database solution.
Document the information that the database is required to hold.
Document the functionality of the database.
Identify the components of the database.
Document all operating system usernames and passwords.
Document all database usernames and passwords
PR2.
Create a MariaDB database Write and test a script that can be executed in the MariaDB
client that will create a
database with empty tables that implements the database from the ERD.
PR3.
Populate the database Write and test some scripts that will load data into your
new empty database tables.
PR4.
Test and debug the database
PR5.
Develop SQL queries
Query 1
Provide a screenshot of all first names from customers.
Query 2
Provide a screenshot of all distinct first names from customers.
Query 3
Provide a screenshot of all email addresses from ‘@reddit.com'.

Query 4
Provide a screenshot of all orders placed by customers with usernames beginning with ‘z'. Include the username, the order_id, and the date_time in your output.
Query 5
Provide a screenshot of all orders placed by the customer with username ‘kbech8m'.
Include the username, the order, and the date_time in your output. Sort the result by order_id.
Query 6
Provide a screenshot of the second order (and only the second order) from the customer with username ‘kwindows76' when the result is sorted by date. Include the username, the order_id, and the date_time in your output.
Query 7
Provide a screenshot counting the number of orders from the customer with username ‘kwindows76'.
Query 8
Provide a screenshot of the expected delivery date for order 311 if the delivery date for order 311 is 14 days.
Include the order_id, date_time, and delivery date in the output.
Query 9
Provide a screenshot of the number of days between ‘2006- 04-15' and ‘2010-11-04'.
Query 10
Provide a screenshot of the number of days between order 311 and order 2311. You must use subqueries in your answer.

You must give your result the alias ‘Days Between Orders'.
Query 11
Provide a screenshot of the number of days between the first and last order placed by the customer with the
username ‘kbech8m'. You must use subqueries in your answer. You must give your result the alias ‘Days Between Orders'.
PR6.
Create, Read, Update, and Delete (CRUD)

Using INSERT INTO, UPDATE,
and DELETE, insert a new record, update that same record, then delete that record from the accounts table. Ensure at least one row is affected for each command. Use a SELECT statement to show what each command achieved.
Provide screenshots as evidence.

Backup and Recovery Create a backup of the customers database.
Restore the customers database to a new database.
Submit the database backup file to Moodle.
Provide screenshots as evidence.

PR9.
Normalization Normalize the Entity-
Relationship Diagram (ERD).
PR10.
Data Dictionary
Develop a Data Dictionary based on your normalized ERD defining the data items to be included in the database tables.

Assessment #3 - Selva Vida Database
Detailed instructions and files required to complete this assessment are located on the Moodle Database Fundamentals page.
This assignment contributes 20% to your final grade for Database Fundamentals.
Grades are evenly distributed between the three units for Database Fundamentals.

Project Task
PR1.
Database Requirements
Research client requirements for a database solution.
Document the information that the database is required to hold.
Document the functionality of the database.
Identify the components of the database.
Document all operating system usernames and passwords.
Document all database usernames and passwords.
PR2.
Create a MariaDB database Write a script and execute it in the MariaDB client that will
create a database with empty
tables that implements the database from your ERD. The script should;
• create the database
• delete any existing tables
PR3.
Populate the database Write and test some scripts that will load your data into
your new empty database
tables.
PR4.
Test and debug the database Ensure the database tables exist and have the correct
number of columns. Ensure
that column names are correct, and all fields are populated.
If errors are found, you may need to drop the database tables and recreate them.

PR5.
Develop SQL queries
Write and test suitable scripts that will produce the desired output when executed in MariaDB.

Query 1
Display a customer's list of invoices. For each record, display the customers first name, last name, invoice id, and date. Ensure the chosen customer has at least three invoices.
Query 2
For the same customer, calculate the number of days between their last and second last invoice.
Query 3
For the same customer, display all the items corresponding to one of their invoices. Include the customers first and last name, the scientific name, origin, price, quantity, and date for each invoice item, and.
Ensure the chosen invoice has at least three items. Order the results by scientific name.
Query 4
For the same invoice, calculate the cost of the items (unit price
* quantity) on the invoice. Ensure that the quantity of at least one invoice item is more than one. Include the scientific name, origin, price, and quantity.
Query 5
For the same invoice, calculate the total cost of the invoice.
Ensure that the quantity of at least one invoice item is more than one.
Query 6
Display the scientific name, origin, unit price, quantity ordered, unit price * quantity, and the invoice date for all items ordered in the year 2018. Order the results by date.

Query 7
Find the sum of all invoices for the year 2018.
Query 8
Display the top ten most frequently sold products. Display the scientific name, origin, price, and the number of times they were sold.
Query 9
Display the top ten products with the greatest quantity ordered in one order. Display the scientific name, origin, price, quantity sold, and date.
Query 10
Display the top ten products with the greatest total quantity sold. Display the scientific name, origin, price, and total quantity sold.
PR6.
Create, Read, Update, and Delete (CRUD).
Create a new customer in the customer table.
Read the customer details from the customer table.
Update the customers delivery address.
Delete the customer.

PR7.
Database Optimization Check if optimisation is required.

Optimise if necessary.
Provide screenshots as
evidence.
Document any optimisation
performed.
PR8.
Backup and Recovery
Create a backup of the Selva Vida database.
Restore the Selva Vida
database to a new database.
Submit the database backup
file to Moodle.
Provide screenshots as evidence.

PR9.
Normalization
Normalize the Entity-
Relationship Diagram (ERD).
PR10.
Data Dictionary
Develop a Data Dictionary based on your normalized ERD defining the data items to be included in the database tables.

Assessment #4 - Report

Detailed instructions and files required to complete this assessment are located on the Moodle Database Fundamentals page.
This assignment contributes 40% to your final grade for Database Fundamentals.
You will receive a separate grade for each of the three units for Database Fundamentals.

Q1. Outline the principles of open platforms including browsers and databases.
Q2. List the processes associated with the creation of entities, attributes, and in populating fields using both software solutions and script- based input.
Q3. Describe data-modelling techniques to design a database.
Q4. Outline the steps in database design, modelling, and implementation.
Q5. Describe the internet operation related to web servers and clients.
Q6. Identify naming conventions appropriate to database design.
Q7. Identify security restrictions on servers incorporating some theoretical concepts.
Q8. Describe best practice communication and accessibility for audiences with special needs.

Q9. Recognise various query- related terminologies.
Q9.1. The SQL SELECT
Statement
Q9.2. The SQL SELECT DISTINCT
Statement
Q9.3. SQL Wildcard Characters
Q9.4. SQL INNER JOIN Keyword

Q9.5. The SQL WHERE Clause
Q9.6. The SQL AND, OR and
NOT Operators
Q9.7. The SQL ORDER BY
Keyword
Q10. Identify an SQL database data source.
Q11. Identify the necessary tools, and environment, in building queries.
Q12. Outline information gathering methods.
Q13. Outline data analysis techniques.
Q14. Define data mining.
Q15. Outline characteristics of the data source (consider the Selva Vida database).
Q16. Explain the features of the information system (consider the Selva Vida database).
Q17. Explain typical systems and procedures (consider how the Selva Vida database would be utilised).
ICTPRG412 Configure and maintain databases
Q18. Outline principles of database design.
Q19. Summarise features of desktop operating systems.
Q20. Explain the principles of relational databases.
Q21. Describe the functions of computer hardware and components.
Q22. Explain computer networking and components.
Q23. Explain uses of structured query language (SQL).

Attachment:- Database Fundamentals.rar

Reference no: EM132510336

Questions Cloud

Describe human p38 map kinase in complex with rl49 : Describe 'Human p38 MAP Kinase in Complex with RL49' in metabolic process

Describe concept of globalization to develop practical ways : Apply the concept of globalization (Howell and Paris, Chapter 10; Bailey and Peoples, Chapter 13) to develop practical ways in which the entire Church.

Prepare a normal-costing income statement for the month : Prepare a normal-costing income statement for the month Reconcile the difference in net income between the absorption-costing and normal-costing methods.

Explain the four different types of replications : Explain the four different types of replications and the purpose of each. Include for each type of replication a brief original psychological study example.

ICTDBS403 Create basic databases Assignment : ICTDBS403 Create basic databases Assignment Help and Solution, Victoria University - Identify the necessary tools, and environment, in building queries.

Primary choice for energy sources : Nonrenewable technologies rather than renewable technologies have become the primary choice for energy sources.

Test potential hypotheses : Explain suitable recommendations and interventions for controlling the problem and preventing additional spread of the outbreak.

Consequences of leakage of pancreatic enzymes : 1. In cirrhosis, the obstructed biliary channels would lead to? What usually happens in cirrhosis?

Briefly explain the example and the claim that has been made : Correlational research describes relations among variables but cannot indicate that one variable causes something to occur to another variable.

User Account

All Pages