ITECH 1103 Assignment: SQL Database

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. 

Timelines and Expectations 

Percentage Value of Task: 25% 

Due: Refer to Course Descriptor 

Minimum time expectation: Preparation for this task will take approximately 20 hours 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.