Assessment item 3
back to top
Normalisation
Value: 15%
Due Date: 13 -Sep -2019
Return Date: 09 -Oct -2019
Submission method options: Alternative submission method
Task
back to top
Suppose that you have been given the table structure and data shown in Table below, which was
imported from an Excel spreadsheet.
The data reflect that a professor can have multiple advisees, can serve on multiple committees, and
can edit more than one journal.
Table 1: Sample PROFESSOR Records
Attribute Name Sample Value Sample
Value
Sample
Value
Sample
Val ue
EMP_NUM 123 104 118
PROF_RANK Professor Asst.
Professor
Assoc.
Professor
Assoc.
Professor
EMP_NAME Ghee Rankin Ortega Smith
DEPT_CODE CIS CHEM CIS ENG
DEPT_NAME Computer Info.
Systems
Chemistry Computer
Info.
Systems
English
PROF_OFFICE KDD -567 BLF -119 KDD -562 PRT -345
ADVISEE 1215, 2312, 3233,
2218, 2098
3102, 2782,
3311,
2008, 2876,
2222,
3745, 1783,
2134, 2789,
3456,
2002, 2046,
2018,
2764
2873, 2765,
2238,
2901, 2308 2378
COMMITTEE_CODE PROMO, TRAF
APPL, DEV
DEV SPR, TRAF PROMO, SPR
DEV
JOURNAL_CODE JMIS, QED,
JMGT
JCIS, JMGT
Given the information in Table 1:
a. Draw the dependency diagram. (3 marks)
b. Identify the multivalued dependencies. (3 marks)
c. Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks)
e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in Part c. ( Note: You
might have to create additional attributes to define the proper PKs and FKs. Make sure that all of
your attributes conform to the naming conventions.) (3 marks)
NOTE
For guidance on how to normalise database relations from a dependency diagram, watch this very
short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ
Manually drawing the dependency diagram, or the set of 3NF relations or the relatio nal schema is
NOT acceptable.
Rationale
back to top
This assessment task will assess the following learning outcome/s:
• be able to apply normalisation techniques to optimise a database design.
Marking criteria and standards
back to top
The marking criteria for t his assignment is:
Criteria HD DI
be able to apply Analysis Analysis normalisation
techniques to
optimise a
database design.
identifies all PKs
and functional,
partial and
transitive
dependencies.
Accurately
identifies all 3NF
relations,
including PKs,
relevant
attributes and
referential
integrity
constraints.
Creates an ERD
that represents
all 3NF relations
and includes all
entities, PKs,
attributes,
relationships,
associative
entities (if
applicable),
cardinalities and
optionalities.
identifie s PKs
and functional,
partial and
transitive
dependencies
with minor
omissions.
Accurately
identifies 3NF
relations,
including PKs,
relevant
attributes and
referential
integrity
constraints with
minor
omissions.
Creates an ERD
that represents
identified 3NF
relations and
includes all
entities, PKs,
attributes,
relationships,
associative
entities (if
applicable),
cardinalities and optionalities.
Requirements
back to top
Either save your diagrams in a PDF file or embed them in a Word document.
Don't use any other file formats
Please include your student ID, name and subject code in your submission.
Assessment item 4
back to top
Query databases using SQL
Value: 15%
Du e Date: 04 -Oct -2019
Return Date: 28 -Oct -2019
Submission method options: Alternative submission method
Task
back to top
Task: Complex SQL Quer ies
back to top
NOTES
• This assignment requires you to use SQLite to complete the tasks listed below.
• For guidance on how to install SQLite, watch this short YouTube
video: https://www.youtube.com/watch?v=9ynCDVg54H0
• For guidance on how to create tables using SQLite, watch this short YouTube
video: https://www.youtube.com/watch?v=kij5lPw0F -o
• Typing the SQL statements or the results is NOT acceptable.
Instructions
From Resources under Main Menu in Interact 2 go to Assignment 4 Folder
Copy sqlit e3.exe file from the Assignment 4 Folder into a folder in your machine
Copy a4.txt from the Assignment 4 Folder into the folder in your machine where your SQLite file is
located
From the SQLite Command Prompt execute the a4.txt script. To do this, at t he SQLite Command Line
type .read a4.txt [to create the tables you will use to answer this assignment’s questions]
At the SQLite Command Line type .headers on
At the SQLite Command Line type .mode column
Write and run SQL statements to complete the f ollowing tasks (Each Query : 1.5 (0.75 query+ 0.75
for screenshot) Marks)
1. Show sums of line units for each invoice.
2. Show the details of the products that do not have a value for the attribute v_code.
3. Show the details of the invoices whose subtotal is great er than 24 but less than 76.
4. Show the details of the invoice who has the minimum subtotal.
5. Show the codes and names of the vendors who supplied products.
6. Using EXCEPT show the codes of the vendors who did not supply any products.
7. Using ‘NOT IN’ show the c odes and names of the vendors who did not supply any products.
8. List the names and codes of vendors and the number of products each vendor has supplied,
i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
9. Show the detail s of the employees who are located in area code 615.
10. Using inner join, list the details of the products whose line price is greater than 99.
You are required to submit:
1. The SQL statements for each query, which should be copied from your SQLite3 Command Line
and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will
output your SQL statement with the results of the query making it easy to copy and paste both.
2. The screenshots of the results of the S QL statements which are to be copied from your SQLite3
Command Line and pasted into your submission file immediately after you execute the SQL query. If
you used an output file please submit only the output file.
Rationale
back to top
This assessment task will assess the following learning outcome/s:
• be able to use complex SQL commands to query a database. Marking criteria and standards
back to top
The marking criteria for this assignment is:
Criteria HD DI
be able to use complex SQL
commands to query a database.
SQL syntax re turns all attributes that
meet the business requirement from
the tables that hold the data and
applies the correct restrictions to
retrieve the required information.
SQL syntax returns all attributes that
meet the business requirement from
the tables that hold the data with
minor omissions and applies
appropriate restrictions to retrieve the
required information.
Presentation
back to top
The document must be prepared in a professional manner with clarity and sound organization.
Requirements
back to top
Either submit your assignment as a PDF or a Word document.
Don't use any other file formats.
Please include your student ID, name and subject code in your submission.