Database and Information Retrieval
Assessment Task- Project Documentation and Database
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.
Unit Learning Outcomes
• 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:
• 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.
Question 1
Pass level
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.
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.
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.
(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.