Write the relational schema and draw the dependency diagram

Assignment Help PL-SQL Programming

Reference no: EM132196213

Assessment: Database Design and Implementation Report

Learning Outcomes

This assessment assesses the following Unit Learning Outcomes (ULO) and related Graduate Learning Outcomes (GLO):

Unit Learning Outcome (ULO)
ULO 2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data.
ULO 3: Design and implement relational database systems through the use of SQL

Purpose

This task requires students to apply their understanding and ability to use Relational DataBase Management Systems (RDBMS) as well as use SQL in the modelling of the physical world. Students will be provided with a set of business scenarios and are required to design a database and provide related SQL queries.

Instructions

This is an individual assessment task. Students are required to submit a written report that describes the design and implementation of a small database project. Students are also required to submit the associated SQL scripts

- Read these instructions and the following four questions.
- Answer as many questions as possible.

Question 1

Problem statement

Saffron Gallery is about to host an exhibition next month. A number of works of art (paintings, Sculptures, etc.) will be exhibited. Every art work will have a barcode, a title and a description. Each Art Work is created by a single artist. Every artist has a name, an address and a contact number. One artist may create more than one art work for the exhibition.

People will come and visit the exhibition. As each person enters the exhibition, their name, address and phone number will be recorded. Each person will be given a unique barcode id.

During their visit, people will nominate which art work they consider the best and this will be recorded by Saffron Gallery. People will be able to vote for more than one art work.

Exercise 1:
Describe the elements (entities, attributes and relationships) of the table of this database system.

Exercise 2:
Draw the Entity Relationship Diagram (ERD) of the generated tables.

Exercise 3:
Write the SQL code to create the tables.

Question 2:

You are given the Student 1, Student 2, and Course tables in the database below:

Student 1

Student_ID	Student_FName	Student_Lname
10001	John	Smith
10002	Dave	Franklin
10003	Febby	Johns
10004	Mary	Gibson
10005	Glory	Anson

Student 2

Student_ID	Course_ID	Year_Joined
10001	SIT772	2016
10002	SIT774	2015
10003	SIT775	2017
10004	SIT712	2016
10005	SIT772	2017

Course

Course_ID	Course	Location
SIT772	Database	Burwood
SIT774	IT security	Burwood
SIT775	Software Development	Geelong
SIT712	Project Management	Burwood

For each of the following exercises please provide a screenshot of SQL query execution.

Exercise 1:

Write the SQL code to create the above three tables, and insert the sample data into the tables.

Exercise 2:
Write the SQL query to find all students who have enrolled into a course in 2016 or later.

Exercise 3:
Write the SQL query to generate a list of all students who have the letter ‘a' in their name (first name/last name).

Exercise 4:
Write the SQL query to find students who are enrolled for units offered in ‘Burwood'.

Exercise 5:
Write the SQL query to show only students who have enrolled to ‘Database' course or a course that has been offered in Geelong

Question 3:

Sales

CUS_ID	Name	Ord_NO	Ord_Date	Prod_ID	Description	Qty_Ord
C001	Gold	O81	15-Apr	P005	Chisel	6
C001	Gold	O81	15-Apr	P004	Plane	14
C075	Red	O99	16-Apr	P015	Saw	3
C009	Blue	O56	16-Apr	P033	Punch	24
C009	Blue	O56	16-Apr	P004	Plane	9
C001	Gold	O88	17-Apr	P015	Saw	10

Using the above Sales table structure, perform the followings:

Exercise 1:

Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You may assume that an order number references more than one product and that the table does not contain repeating groups.

Exercise 2:

Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 3:

Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 4:

Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3.

Question 4:

You are given the following table structure with sample data in it.

PET ID	PET NAME	PET TYPE PET AGE OWNER			VISIT DATE	PROC_ID	PROCEDURE

246	ROVER	DOG	12	SAM COOK	JAN 13/2002	01	RABIES VACCINATION
					MAR 27/2002	10	EXAMINE and TREAT WOUND
					APR 02/2002	05	HEART WORM TEST

298	SPOT	DOG	2	TERRY KIM	JAN 21/2002	08	TETANUS VACCINATION
					MAR 10/2002	05	HEART WORM TEST

341	MORRIS	CAT	4	SAM COOK	JAN 23/2001	01	RABIES VACCINATION
					JAN 13/2002	01	RABIES VACCINATION

519	TWEEDY	BIRD	2	TERRY KIM	APR 30/2002	20	ANNUAL CHECK UP
					APR 30/2002	12	EYE WASH

Exercise 1:

Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization.

Exercise 2:

Write SQL code to create the generated tables.

Attachment:- Database and Information Retrieval.rar

Reference no: EM132196213

Questions Cloud

Write a program that randomly generates the numbers : Write a C++ program that randomly generates the numbers 1-100 in an array and then I have to find the number 77 using the sequential search and computer O(n).

Make use of the if then or case in selection : Write a C++ program that makes use of the if then or Case in Selection. Ask the user if it is raining.

Write a c program for a pic-18 micro controller system : Write a C program for a PIC-18 micro controller system with a 32-MHz oscillator to implement an 8 KHz interrupt.

Write a c program to interpret the functional character : Write a C program to interpret the functional character (|) as a UNIX terminal does, Using system calls such as fork, waitpid, execvp, exit, pipe.

Write the relational schema and draw the dependency diagram : SIT772 - Database and Information Retrieval - Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams

Create 2 new words such that the first word starts and ends : Write a C++ program that; Prompts the user for two words then displays the number of characters, and the first and last characters of each word.

Find the sum of the first and second decimal digits : Write a C++ program that prompts the user to enter a positive decimal number, then the program finds the sum of the first and second decimal digits.

Inputs the worlds population at the end of last year : Write a C++ program that inputs the world's population at the end of last year. Assume population is growing at a rate of 1.12%.

Display the number of words in the file : Write a c++ program that prompts user for file name and displays the number of words in the file.

Reviews

len2196213

12/17/2018 4:40:07 AM

Question 4 Excellent Good Incorrect Draw the dependency diagram of the table and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization. The dependency diagram drawing of the table is correct. 3 marks The dependency diagram drawing of the table is partially correct. 2 marks The dependency diagram drawing of the table is incorrect or missing. 0 mark Write SQL code to create the generated tables. The written SQL code is correct. 2 marks The written SQL code is partially correct. 1 mark The written SQL code is incorrect or missing. 0 mark Overall Score Level 3 22 or more Level 2 14 or more Level 1 0 or more

len2196213

12/17/2018 4:40:00 AM

Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure. The relational schema written is correct. 2 marks The relational schema written is partially correct. 1 mark The relational schema written is incorrect or missing. 0 mark Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3 The Entity Relationship Diagram (ERD) drawing is correct. 2 marks The Entity Relationship Diagram (ERD) drawing is partially correct. 1 mark The Entity Relationship Diagram (ERD) drawing is incorrect or missing. 0 mark

len2196213

12/17/2018 4:39:53 AM

Question 3 Excellent Good Unsatisfactory Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies The relational schema written is correct 3 marks The relational schema written is partially correct. 2 marks The relational schema written is incorrect or missing. 0 mark Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure. The relational schema written is correct. 3 marks The relational schema written is partially correct. 2 marks The relational schema written is incorrect or missing. 0 marks

len2196213

12/17/2018 4:39:26 AM

Write the SQL query to find all students who have enrolled into a course in 2016 or later. The written SQL query is correct. 2 marks The written SQL query to generate a combined is partially correct. 1 mark The written SQL query is to generate a combined list missing. 0 mark Write the SQL query to generate a list of all students who have the letter ‘a’ in their name (first name/last name). The written SQL query is correct. 2 marks The written SQL query is partially correct. 1 mark The written SQL query is incorrect or missing. 0 mark

len2196213

12/17/2018 4:39:16 AM

Write the SQL code to create the tables The SQL code, which creates the tables is correct. 2 marks The SQL code, which creates the tables is partially correct. 1 mark The SQL code, which creates the tables is incorrect or missing. 0 mark Question 2 Excellent Good Unsatisfactory Write the SQL code to create the above three tables, and insert the sample data into the tables The written SQL code is correct. 2 marks The written SQL code is partially correct. 1 mark The written SQL code is incorrect or missing. 0 mark

len2196213

12/17/2018 4:39:08 AM

Question 1 Excellent Good Unsatisfactory Describe the elements of the table of this database system All elements of this table are correct. 1 mark At least one of the elements of this table is correct. 0.5 marks None of the elements of this table was correct or the elements are missing. 0 marks Draw the Entity Relationship Diagram (ERD) of the generated tables. The Entity Relationship Diagram (ERD) of the generated tables is correct. 2 marks The Entity Relationship Diagram (ERD) of the generated tables is partially correct. 1 mark The Entity Relationship Diagram (ERD) of the generated tables is not correct or is missing. 0 mark

len2196213

12/17/2018 4:39:00 AM

Please note that these regulations are not intended to discourage group work and exchange of views and information with other students and staff. Such interaction is most desirable, provided that you ultimately write your own answers and acknowledge any quoted sources. We see responsible attitudes to plagiarism as part of general good ethical practice. Ensure you have familiarised yourself with the rules and regulations on plagiarism and collusion.

len2196213

12/17/2018 4:38:52 AM

Referencing, plagiarism and collusion Any work that you submit for assessment must be your own work. Please note that this unit has systems in place to detect plagiarism and all submissions are submitted to this system. Submitting written work, in whole or in part, that is copied or paraphrased from other authors (including students), without correct acknowledgement, is considered one of the most serious academic offences. This practice is equivalent to cheating in examinations and it may lead to expulsion from the University.

len2196213

12/17/2018 4:38:38 AM

i have assignment question on database, in which lucid charts has to be used and tera term software for queries and also screenshots to be put in Key information • Due: Friday, 14, 11:59PM (AEDT). As of 3 the due date has been extended to 11:59 pm (AEDT), Friday 21. • Weighting: 30% - 20% written report - 10% SQL script • Submit: Through CloudDeakin via FutureLearn

Write a Review

Required(*) Message

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

User Account

All Pages