Assignment Detail:- ITECH 1103 Big Data and Analytics Assignment - SQL Database
Overview - The purpose of this task is to develop student's skills in designing and implementing a relational database for a given case study-
Assessment Details -
Background - You are to design a scenario of your choosing- This scenario will be modelled initially as an ERD, and then implemented in SQL statements- As well as creating the requisite SQL Tables, you will need to manufacture data that is imported into these Tables- You will then be required to develop several SQL queries to provide key functionality for your database-
Requirements -
1- Scenario choice: your chosen scenario must include at least three entities which are related to each other- This really can be anything you choose, but you are advised to choose something you have knowledge of, to make the exercise easier- An example could be a hobby that you have - perhaps you enjoy a sport, and you wish to develop a database that models some form of performance related to the teams and games played in this sport- Another example could be a part-time job you may have, and you may wish to develop a database to assist some aspect of that business- Another example could be some area of science, perhaps an engineering type application that models a power station or a factory or an experiment of some kind- Because this "brief" is very open-ended, you are recommended to check your chosen scenario with the teaching staff to make sure it is suitable- A final consideration when thinking of your scenario are the list of SQL queries that you will need to execute within your database- For instance you will need to include certain search and statistical functions, so your scenario will need to be sophisticated enough to make these queries possible- PLEASE NOTE: each student is required to develop a UNIQUE scenario - it will not be permitted that two students use the exact same scenario-
2- Your ER Diagram must include at least three entities- There is no upper bound on how many entities you choose, but you are advised to include no more than five- Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated- You will need to normalise all of your entities, to resolve any many to many relationships-
Observe the following restrictions when creating your scenario:
- One -or more- of your entities must have a numeric field
- One -or more- of your entities must contain an alphanumeric -varchar- field
- One of your entities must contain a compound key
- Do not have include cyclic relationship -A->B->C->A-
Your attribute names, primary and foreign keys should be indicated as per the conventions given in the lecture slides -i-e- attributes as proper nouns, primary key underlined and foreign keys in italic
All many to many relationships should be resolved, and you may wish to include a discussion of normalisation including the normal form that each entity is in and why that is optimal-
3- For each entity, you must create some example data for that entity- Include at least 10 rows of data for each entity- This data must be initially stored in a text file that will be imported into the database-
4- Write the SQL statements to create the table structures from your developed ERD- The structures should contain the attributes specified in your ERD- Use data types that are appropriate for the data that will need to be stored in each attribute- Enforce primary key and foreign key constraints as indicated by your ERD- Write the SQL code to insert your data from -3- into the SQL table structures-
5- Write the SQL statements that provide the following database functionality:
Query 1- Write an SQL statement that correctly uses the 'LIKE' symbol on an alphanumeric field to select a subset of data- Explain the purpose of your query for your database solution-
Query 2- Write an SQL statement that correctly implements a JOIN statement over two or more entities to select a subset of data- Explain the purpose of your query for your database solution-
Query 3- Write an SQL statement that correctly uses the '>' operator to select a subset of data for a single entity- Explain the purpose of your query for your database solution-
Query 4- Write an SQL statement that correctly uses the 'GROUP BY' operator to select an aggregation of data -e-g- SUM, AVG, COUNT-- Explain the purpose of your query for your database solution-
Query 5- Write an SQL statement that correctly uses the 'IN' symbol to select a subset of data for a single entity- Explain the purpose of your query for your database solution-
Query 6- Write an SQL statement that correctly uses the 'UPDATE' symbol to modify at least three rows of data in one of your tables- Explain the purpose of your query for your database solution-
6- Write a short section -two or three paragraphs- about some aspect of your scenario that might be too difficult or very complex to implement in an SQL database-
Documentation requirements -
1- Prepare a report -single Word or PDF document- which contains the following:
a- ER diagram based on your created scenario
b- Example data
c- A copy of your SQL code - you must also provide associated output -as screengrabs, inserted into the report-
d- Statement of any resources used- These includes full disclosure of assistance from all sources including tutors and other students- Full APA referencing of any resources used-
Nolte - Scenario is changed which is bank system-
Most Recent Questions