User Account

All Pages

Write the sql statements to create the table structures

Assignment Help PL-SQL Programming
Reference no: EM133130302

ITECH1103 Big Data and Analytics - Federation University

Assignment: SQL Database

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.

Attachment:- SQL Database.rar

Reference no: EM133130302

Questions Cloud

Explain the importance of beta in risk management : Explain the importance of beta in risk management. Explain the relationship between Capital pricing model and Arbitrage theory
What types of shares do you think would be most suitable : What types of shares do you think would be most suitable for this investor? Come up with at least three different types of shares
How much will it be worth when you retire : If you invested $800 today and expected consistent 9% annual returns, how much will it be worth when you retire 40 years from yesterday
Estimate the project value at risk : Estimate the project's Value at risk (VAR) at a 95% confidence level over the project's life of four years. Comment on the answer
Write the sql statements to create the table structures : Design a scenario of your choosing. This scenario will be modelled initially as an ERD, and then implemented in SQL statements
Determine the cost of inventory : Prior to putting the goods up for sale, they cleaned and refurbished them at a cost of $650. Determine the cost of inventory
What is the amount of the total current assets : The related data of Amaya Company are as follows: Accounts payable P125,000. What is the amount of the total current assets
Create a collaborative dialogue : Create a collaborative dialogue - to deepen understanding of each other and so you need to go beyond agreement - A brief one line response is superficial
How would you put the information into a bank reconciliation : How would you put the information below into a bank reconciliation for Jordy Co. for August 31, 2020 - The bank statement balance is $4,010

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Major concern in enterprise application

Security and Privacy is a major concern in Enterprise Application. Write a short essay citing references, showing the roles of security and privacy and security in an enterprise Architecture program.

  Prepare a query with all fields from the student table

Create a query with all fields from the student table, where the student's last name is "Smith". Create a query that includes students' first names, last names, and phone numbers.

  Assignment on aggregate functions

After reviewing and completing the Unit 1 Guided Practice 2, I suggest that you review all tables using the Object Browser area of the SQL Workshop associated with the scenarios below, as well as field data types and data (case sensitivity) before..

  Commands that you would use to create a 7x7 matrix

Show the unix/linux commands that you would use to create a 7x7 matrix of alternating entries of 1's and 0's. It should look like this

  Create statements for the hotel management system

Create statements for the hotel management system and Database is loaded with data and UI to access and manage those data

  Assess emerging cyber security approaches

Write an 18 page (or longer) paper in which you identify and assess emerging cyber security approaches and technologies as well as discuss government efforts to nurture the development of these emerging technologies.

  Which team is in charge of unit testing

The software development team is separate from the test team in big corporations. Which team is in charge of unit testing?

  Design and implement an inventory database

This phase is to be implemented by each student individually, without the help of others. You will design and implement an inventory database - Design and implement an inventory database

  Write ten sql select statements to query

Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab 2. Your Select Statements should run error-free and should be valid.

  Calculate days between ordering and shipping

Calculate the Tax on an Order, Calculate the Total Shopper Spending - Develop a SELECT statement using the BB_SHOPPER table to produce a list of each shopper in the database and his or her respective totals.

  Relational schema of a firm database - write a sql query

Display the vendor ID and vendor name of the items whose item quantity is the largest and display the item ID and item quantity of all items whoseitem quantity is less than the average quantity. Also display the average quantity

  Create a script file containing the pl/sql code

For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL blo..

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd