SIT103 – Database and Information Retrieval

SIT103 – Database and Information Retrieval (2015)
Assessment Task 2 – Project Documentation and Database
Due Date: Monday, September 28 at 9am
Introduction
• This assessment is for students to develop the capacity to design and implement a database.
• This assessment requires students to identify business rules, create a data dictionary, create an EntityRelationship diagram, and develop an Oracle database using SQL.
• This is an individual assessment task.
• The project documentation submitted should include business rules, data dictionaries, ER diagrams, SQL source code such as q1.sql, and SQL spool files such as q1.txt. See ‘Submission Instruictions.pdf’ for details.
Unit Learning Outcomes

Understanding specific need in health and social care

Unit 6 Research project
• Of the three Unit Learning Outcomes (ULOs) of this unit SIT103, this assessment task will focus on the last two ULOs. These are:
o ULO 2 – At the end of this unit students will be able to evaluate data models and apply data modelling techniques to capture the data aspects of real-world situations.
o ULO 3 – At the end of this unit students will be able to design and develop relational databases by using SQL and a database management system.
• The assessment of this task (Project Documentation and Database) will indicate whether students can partially attain these unit learning outcomes.
Instructions
• Read these instructions and the following 4 questions.
• Answer as many questions as possible.
• You should consider which questions to answer because Question 1 is at the Pass level, Question 2 is at the Credit level, Question 3 is at the Distinction level, and Question 4 is at the High Distinction level. If you are interested in obtaining a:
o Pass, answer Question 1 o Credit, answer Questions 1 and 2 o Distinction, answer Questions 1, 2 and 3 o High Distinction, answer Questions 1, 2, 3 and 4
• Clearly identify your answers 1a, 1b, 1c and so on, as this will ensure that the marker can find your answers.
• Place your name, ID and answers in your document. Please note that MS Word (docx) or PDF files may be submitted.
• As there will be several files that you will submit, you will place all files in a folder and ZIP that folder.
You might like to name this folder Docs and DB – John Smith 215123456
You will submit this ZIP file. Identify yourself within the filename of your ZIP file. For example, Docs and DB – John Smith 215123456.zip
Question 1
Pass level]

Understanding specific need in health and social care

Unit 6 Research project
Assessing this question focuses on whether students can demonstrate the ability to:
• list business rules for a simple database,
• create a data dictionary for a simple database (2 tables, 1:N), • create an ER diagram for a simple database (2 tables, 1:N), and
• develop a simple database (2 tables, 1:N).
Task www.winelovers.com provides data and information related to wine. It uses a small database containing data about different wines and winemakers. Each wine can only be manufactured by one winemaker, but each winemaker can produce several wines. However, clearly it is possible that a new winemaker has not produced a wine.
For this scenario:
(a) Determine the business rules. (b) Develop a data dictionary.
(c) Develop an ER diagram. Clearly label all entities, primary and foreign keys, relationship, connectivity and cardinalities.
(d) Develop an SQL script, say q1.sql, to run on Deakin’s Oracle database which:
1) removes previous tables related to this question, e.g., drop …
2) creates these two tables, e.g., create …
3) inserts all data from Table 1, e.g., insert …
4) uses the spool command to start recording to a file, e.g., spool /home/username/q1.txt
(please replace username with your Deakin login name)
5) turns on the echo, e.g., set echo on;
6) displays the wine name, vintage and price of all wines costing less than $20, e.g., select …
7) displays the wine name, price and maker of all wines made in 2012, e.g., select …
8) turns off the echo, e.g., set echo off;
9) turns off the spooling, e.g., spool off;
WINE
ID WINE NAME WINE
VINTAGE WINE PRICE WINEMAKER
ID WINEMAKER NAME
101 Grange 2010 750 1 Penfolds
102 Grange 2006 700 1 Penfolds
103 Reserve Shiraz 2013 10 2 Jacob’s Creek
104 Grey Label Shiraz 2012 35 3 Wolf Blass
105 Patricia Shiraz 2009 50 4 Brown Brothers
106 Ten Acres Shiraz 2012 25 4 Brown Brothers
107 Double Barrel Shiraz 2012 15 2 Jacob’s Creek
108 Platinum Label Shiraz 2006 170 3 Wolf Blass
5 Barrabool Hills
Table 1
Question 2
Credit level] Assessing this question focuses on whether students can demonstrate the ability to:
• list business rules for a small database,
• create a data dictionary for a small database (2 tables, N:M), • create an ER diagram for a small database (2 tables, N:M), and
• develop a small database (2 tables, N:M).
Task
www.winelovers.com provides data and information related to wine. It uses a small database containing data about different wines and wine retailers. Each wine can be sold by several retailers, and each retailer can sell several wines. However, clearly it is possible that a new retailer has no stock at the time of starting his/her business, and a new wine might not be stocked by any retailer.
For this scenario:
(a) Determine the business rules. (b) Develop a data dictionary.
(c) Develop an ER diagram. Clearly label all entities, primary and foreign keys, relationship, connectivity and cardinalities.
(d) Develop an SQL script, say q2.sql, to run on Deakin’s Oracle database which:
1) removes previous tables related to this question, e.g., drop …
2) creates these two tables, e.g., create …
3) inserts all data from Table 2, e.g., insert …
4) uses the spool command to start recording to a file, e.g., spool /home/username/q2.txt
(please replace username with your Deakin login name)
5) turns on the echo, e.g., set echo on;
6) displays the wine name, vintage and price of all wines at Dan Murphy’s costing less than $20, e.g., select …
7) displays the wine name, price and retailer of all wines at Dan Murphy’s made in 2012, e.g., select …
8) turns off the echo, e.g., set echo off;
9) turns off the spooling, e.g., spool off;
WINE
ID WINE NAME WINE
VINTAGE WINE PRICE RETAILER
ID RETAILER
NAME
101 Grange 2010 750 1001 Dan Murphy’s
102 Grange 2006 700 1001 Dan Murphy’s
103 Reserve Shiraz 2013 10 1001 Dan Murphy’s
104 Grey Label Shiraz 2012 35 1001 Dan Murphy’s
105 Patricia Shiraz 2009 50 1001 Dan Murphy’s
106 Ten Acres Shiraz 2012 25 1001 Dan Murphy’s
107 Double Barrel Shiraz 2012 15 1001 Dan Murphy’s
108 Platinum Label Shiraz 2006 170 1001 Dan Murphy’s
103 Reserve Shiraz 2013 9 1002 Woolworth’s
104 Grey Label Shiraz 2012 33 1002 Woolworth’s
105 Patricia Shiraz 2009 44 1002 Woolworth’s
106 Ten Acres Shiraz 2012 22 1002 Woolworth’s
107 Double Barrel Shiraz 2012 12 1002 Woolworth’s
Table 2.

Understanding specific need in health and social care

Unit 6 Research project
Question 3
Distinction level] Assessing this question focuses on whether students can demonstrate the ability to:
• list business rules for a database,
• create a data dictionary for a database (2 tables and a junction table, N:M), • create an ER diagram for a database (2 tables and a junction table, N:M), and
• develop a database (2 tables and a junction table, N:M).
Task
Use the same scenario as in Question 2, but instead of two tables with a many-to-many relationship you will focus on three tables: the two tables for wine and retailer plus a junction table to represent the ‘sell’ relationship.
For this scenario:
(a) Determine the business rules for these 3 tables.
(b) Develop a data dictionary for these 3 tables.
(c) Develop an ER diagram for these 3 tables. Clearly label all entities, primary and foreign keys, relationship, connectivity and cardinalities.
(d) Develop an SQL script, say q3.sql, to run on Deakin’s Oracle database which:
1) removes previous tables related to this question, e.g., drop …
2) creates these 3 tables, e.g., create …
3) inserts all data from Table 3 into these 3 tables, e.g., insert …
4) uses the spool command to start recording to a file, e.g., spool /home/username/q3.txt
(please replace username with your Deakin login name)
5) turns on the echo, e.g., set echo on;
6) displays the wine name, vintage and price of all wines at Dan Murphy’s costing less than $20, e.g., select …
7) displays the wine name, price and retailer of all wines at Dan Murphy’s made in 2012, e.g., select …
8) turns off the echo, e.g., set echo off;
9) turns off the spooling, e.g., spool off;
WINE
ID WINE NAME WINE
VINTAGE WINE PRICE RETAILER
ID RETAILER
NAME
101 Grange 2010 750 1001 Dan Murphy’s
102 Grange 2006 700 1001 Dan Murphy’s
103 Reserve Shiraz 2013 10 1001 Dan Murphy’s
104 Grey Label Shiraz 2012 35 1001 Dan Murphy’s
105 Patricia Shiraz 2009 50 1001 Dan Murphy’s
106 Ten Acres Shiraz 2012 25 1001 Dan Murphy’s
107 Double Barrel Shiraz 2012 15 1001 Dan Murphy’s
108 Platinum Label Shiraz 2006 170 1001 Dan Murphy’s
103 Reserve Shiraz 2013 9 1002 Woolworth’s
104 Grey Label Shiraz 2012 33 1002 Woolworth’s
105 Patricia Shiraz 2009 44 1002 Woolworth’s
106 Ten Acres Shiraz 2012 22 1002 Woolworth’s
107 Double Barrel Shiraz 2012 12 1002 Woolworth’s
Table 3.

Unit 4 – Personal  and professional development in health and social care

Business strategy assignment help
Question 4
High Distinction level] Assessing this question focuses on whether students can demonstrate the ability to:
• list business rules for a complex database,
• create a data dictionary for a complex database (several tables, 1:1, 1:N, N:M),
• create an ER diagram in 3rd normal form for a complex database (several tables, 1:1, 1:N, N:M), and
• develop a complex database in 3rd normal form (several tables, 1:1, 1:N, N:M).
Task
www.winelovers.com provides data and information related to wine. It uses a small database containing data about different wines, winemakers, retailers, compatible foods, and descriptions of compatible food.
• Each wine can only be manufactured by one winemaker, but each winemaker can produce several wines. However, clearly it is possible that a new winemaker has not produced a wine.
• Each wine can be sold by several retailers, and each retailer can sell several wines. However, clearly it is possible that a new retailer has no stock at the time of starting his/her business, and a new wine might not be stocked by any retailer.
• Each wine goes well with 0 or more foods, and each food goes well with 0 or more wines. A five point scoring system is used to rank compatible wines and foods. For each score there is a small description of a few words such as:
o o o o o 1
2
3 4
5 try another wine nearly compatible compatible very compatible excellent
For this scenario:
(a) Determine the business rules for all tables including junction tables.
(b) Develop a data dictionary for all tables including junction tables.
(c) Develop an ER diagram for all tables including junction tables. Clearly label all entities, primary and foreign keys, relationship, connectivity and cardinalities.

HND Assignment Help

HND Assignment Help

(d) Develop an SQL script, say q4.sql, to run on Deakin’s Oracle database which:
1) removes previous tables related to this question, e.g., drop …
2) creates these tables including junction tables, e.g., create …
3) inserts data from Table 1 and Table 3, and all data from Table 4 into appropriate tables, e.g., insert …
4) uses the spool command to start recording to a file, e.g., spool /home/username/q4.txt
(please replace username with your Deakin login name)
5) turns on the echo, e.g., set echo on;
6) increases the price of each wine at Dan Murphy’s by 10%, e.g., update …
7) displays the wine name, vintage and price of all wines at Dan Murphy’s that are excellent with steak, e.g., select …
8) displays the wine name, price and retailer of all wines that are compatible with beef sausages, e.g., select …
9) turns off the echo, e.g., set echo off;
10) turns off the spooling, e.g., spool off;
WINE
ID WINE NAME WINE
VINTAGE FOOD
ID FOOD
DESCRIPTION SCORE SCORE
DESCRIPTION
101 Grange 2010 1 Steak 5 excellent
101 Grange 2010 2 Beef Sausages 1 try another wine
101 Grange 2010 3 Chicken 1 try another wine
101 Grange 2010 4 Fish 1 try another wine
102 Grange 2006 1 Steak 5 excellent
102 Grange 2006 2 Beef Sausages 1 try another wine
102 Grange 2006 3 Chicken 1 try another wine
102 Grange 2006 4 Fish 1 try another wine
103 Reserve Shiraz 2013 1 Steak 5 excellent
103 Reserve Shiraz 2013 2 Beef Sausages 3 compatible
103 Reserve Shiraz 2013 3 Chicken 1 try another wine
103 Reserve Shiraz 2013 4 Fish 1 try another wine
104 Grey Label Shiraz 2012 1 Steak 4 very compatible
104 Grey Label Shiraz 2012 2 Beef Sausages 5 excellent
104 Grey Label Shiraz 2012 3 Chicken 1 try another wine
104 Grey Label Shiraz 2012 4 Fish 1 try another wine
105 Patricia Shiraz 2009 1 Steak 4 very compatible
105 Patricia Shiraz 2009 2 Beef Sausages 4 very compatible
105 Patricia Shiraz 2009 3 Chicken 1 try another wine
105 Patricia Shiraz 2009 4 Fish 1 try another wine
106 Ten Acres Shiraz 2012 1 Steak 4 very compatible
106 Ten Acres Shiraz 2012 2 Beef Sausages 3 compatible
106 Ten Acres Shiraz 2012 3 Chicken 1 try another wine
106 Ten Acres Shiraz 2012 4 Fish 1 try another wine
107 Double Barrel Shiraz 2012 1 Steak 5 excellent
107 Double Barrel Shiraz 2012 2 Beef Sausages 5 excellent
107 Double Barrel Shiraz 2012 3 Chicken 1 try another wine
107 Double Barrel Shiraz 2012 4 Fish 1 try another wine
108 Platinum Label Shiraz 2006 1 Steak 4 very compatible
108 Platinum Label Shiraz 2006 2 Beef Sausages 3 compatible
108 Platinum Label Shiraz 2006 3 Chicken 1 try another wine
108 Platinum Label Shiraz 2006 4 Fish 1 try another wine
Table 4.

BUMGT 6927 Creativity & Innovation

BUMGT 6927 Creativity & Innovation Assignment 1 Federation University Australia Assignment 1– Lecturer’sHints and Tips What follows is an overview of Assignment One with comments on how you might approach it.  The primary purpose of this assignment is for you to get an overall understanding of the breadth of the topic, AND for you to find some relevance or relationship to your own situation.  I have found that people who understand how creativity and innovation […]
read more

HI6007 Group Assignment Statistics

HI6007 Group Assignment statistics Holmes College 1. Part of an ANOVA table is shown below. Source of Variation                    Sum ofDegrees ofMean F                                                                     Squares        Freedom         Square             Between treatments                    90                3           ?                   ? Within treatments (Error)           120               20                              ?                     Total           ?                   ? a.    Compute the missing values and fill in the blanks in the above table. Use α = .01 to determine […]
read more

Economics for managers MPE781

MPE781 ECONOMICS FOR MANAGERS  MPE781 T3.2017 Assignment Question 1: (10 marks) In your own words, summarize the IMF report article, “Australia,” IMF Country Report No. 15/275, September 2015 (pp. 1-21 only).  In particular, what are the main messages of the article? Question 2: (10 marks) Using appropriate diagrams, figures and data collected to answer: “Are Melbourne’s housing prices overvalued?” a question raised in the above IMF report. Why or why not? Question 3: (10 marks) In […]
read more

HS2061 IS Project management

HS2061 Information system project management Table of Contents Introduction. 3 1.      Analysis of the Scenario. 3 1.1.       Resources Available. 4 1.2.       Employee Constraints. 5 1.3.       Problems Identified. 5 1.4.       Pareto Analysis. 6 1.5.       Solution recommended. 6 2.      Project Scope. 7 3.      Specify functional and non-functional requirements. 8 4.      Develop project plan. 10 5.      Estimate Project Cost and Benefit. 13 6.      Assess Project Risk. 14 7.      Specify Project Work Breakdown Structure. 15 8.      Calculate Project Critical […]
read more

ACC00715 Professional auditing SCU

ACC00715 Professional Auditing- assessment 2 Assessment task 2 (25%) In this folder are links to the relevant discussion forum and instructions about what you will need to do: (a) Identifying financial reporting disclosures relevant to auditing for an entity of your choice – 10% Due: anytime up until noon Monday, 4 December (2017) NOTE: this task will form the basis for the Part (b) requirements of this Task (see below). (b) Making an assessment of the overall inherent risk level for […]
read more

How to write an essay?

How to write a good hook for an essay? Writing an essay often seems to be a dreaded task among students. Now a days, essay writing is considered as an art which is not easy to gain expertise into but once done it can work wonders for the learner. Whether the essay is for a scholarship, a class, or maybe even a contest, many students often find the task overwhelming. While an essay is a large project, […]
read more

LAWS20058 BUSINESS LAW

LAWS20058 BUSINESS LAW WRITTEN ASSESSMENT TERM 3, 2016 – 40% MAXIMUM 4000 WORDS DUE DATE 19 JANUARY 2017 THURSDAY 5:00PM AEST GROUP AND INDIVIDUAL WORK – ON CAMPUS STUDENTS (MAXIMUM 3 STUDENTS) (EXCEPTION: INDIVIDUAL WORK – FLEX STUDENTS) Case Study 1: 10 Marks: Group Work Saber, a business student at CQU, walked into his local game store and saw a copy of the latest game, Marsattack: Metal Squad, with a price tag that said “$6.99”. […]
read more

Unit 8 Marketing in hospitality HND

Unit 8 Marketing in Hospitality Assignment Help Table of Contents Introduction. 3 Task 1. 4 1.1 Discuss concepts of marketing for a relevant services industry. 4 1.2 Assess the impact of the marketing environment on the industry. 4 1.3 Evaluate the relevance of consumer markets in the industry. 5 1.4 Discuss the rationale for developing different market segments. 5 Task 2. 7 2.1 Assess the importance of components of the marketing mix to the industry. […]
read more

Unit 14 Managing financial resources

Managing Financial resources in health and social care Task 1 1.1 Explain what principles of costing and business control systems may be adopted in BUPA. The principles of costing are to adequately cost a product, obtain stakeholder input – including non-financial staff, obtain quality input data to get accurate results, costing should focus on material costs, minimise subjectivity and costing should be transparent and auditable. BUPA could use the principles in costing to control its […]
read more

HI6025 Accounting theory PAT

HI6025 Accounting theory and current issues Please find attached file of requirements for Assignments.   Some information from lecturer :- 1. And Please select two company (Listed in ASX S&P 300 index) from same industry. 2. Try to make table or diagram if possible. 3. Use the annual reports of the companies that will be helpful. 4. In Sec-3 write something about earning bonus. If policies changes then describes AASB 5. Sec- 4 Very important part       1) Quality of disclosure   […]
read more

ITC133 Customer Support Management

ITC133 Customer Support Management Assignment Help Buy this assignment Price 100 AUD Email; [email protected]   Answer the following questions by writing in the Journal section of Interact2: (hints and tips to write this assignment is available under Assignment tab of Interact2) All answers (to Q1, Q2 and Q3) to be in one single journal entry only. Reflect on the case you presented in your first journal entry in Assignment 2. In Q1 and Q2 – […]
read more

LAW512 Corporate Law

LAW512Commercial and Corporate Law for Managers Semester 2, 2017 Assessment Item 1: Mini-Research Essay Buy this essay at [email protected] Price $100 AUD Deadline 24 hr What exactly is the ‘corporate veil’ and does Australian law adequately address abuses of the corporate form? Give specific examples and refer to relevant legislation and case law when you discuss your answer. Please note: The maximum word length for this essay is 1,500 words.  This is a research exercise, […]
read more

Get Assignment help for this assignment at [email protected]

Comments

comments

Database assignment- CQMC
Network & Security Assignment help
Fields marked with an * are required

About the author

HND Assignment Help is the #1 Assignment help company offering hnd business management assignments at most affordable prices and 100% plagiarism free content. Contact for hnd business assignments at [email protected] We are pioneer in btec hnd assignments since more than 7 years and have provided btec hnd assignment help for students around the world. Login http://www.hndassignmenthelp.co.uk for more details.