ITC556 Database Systems
{`ITC556 - Database Systems Session 2 2019 Faculty of Business, Justice and Behavioural Sciences School of Computing and Mathematics Charles Sturt University `}
Subject Overview
Abstract
This subject will provide a solid grounding in database design . It will show the importance of database design in the development of applications that meet business needs. There will be an emphasis on modeling a database using Entity Relationship Diagrams (ERDs), optimising its design using normalisation techniques and implementing it and querying it using Structured Query Language (SQL).
Learning outcomes
Upon successful completion of this subject, students should:
- be able to model complex business rules using Entity Relationship Diagrams (ERDs);
- be able to apply normalisation techniques to optimise a database design;
- be able to use complex SQL commands to query a database;
- be able to research a recent development in the field of database technology.
Subject content
The subject content is listed in the Schedule below.
Key subjects
Passing a key subject is one of the indicators of satisfactory academic progress through your course. You must pass the key subjects in your course at no more than two attempts. The first time you fail a key subject you will be at risk of exclusion; if you fail a second time you will be excluded from the course.
The Academic Progress Policy (https://policy.csu.edu.au/view.current.php?id=00250) sets out the requirements and procedures for satisfactory academic progress, for the exclusion of students who fail to progress satisfactorily and for the termination of enrolment for students who fail to complete in the maximum allowed time.
Assessment item 1
Online Quiz Value: 5%
Submission method options: Interact2 Test Task
Complete the online quiz in Interact 2 Test Centre.
The topics of the online quizzes are:
- Database Principles
- Data Models
- Business Rules
- The Relational Database Model
Rationale
This assessment task will assess the following learning outcome/s:
- be able to model complex business rules using Entity Relationship Diagrams (ERDs).
The online quizzes are designed to allow students to test and demonstrate their ability to understand the topics.
Marking criteria and standards
This assessment consists of 20 multiple choice questions. Each correct answer will score 0.25 marks. Marks will not be deducted for incorrect answers.
Marks will be given based on the correctness of the answers. The Test Centre will be marking automatically and you will receive marks according to the following criteria:
- HD - At least 85% answers were correct
- DI - At least 75% answers were correct
- CR - At least 65% answers were correct
- PS - At least 50% answers were correct
Assessment item 2
Research and Data Modelling
Task
Assessment: Research and Data Modelling Part A: Database research (approx. 200-250 words): (5 Marks)
Note: you are expected to conduct research about current and future Database technologies by answering the below question:
Research Question: Explore history of database systems and discuss the three most important developments in database development history to date. (1.25 marks each)
Note: Use proper references in the APA style. Your research report must be well presented using proper content organization. (1.25 marks)
Part B: Data Modelling (10 Marks)
Create a complete ERD in Crow’s Foot notation that can be implemented in the relational model using the following description of operations. Hot Water(HW) is a small start-up company that sells spas. HW does not carry any stock. A few spas are set up in a simple warehouse so customers can see some of the models available, but any products sold must be ordered at the time of the sale.
- HW can get spas from several different manufacturers.
- Each manufacturer produces one or more different brands of spas.
- Each and every brand is produced by only one manufacturer.
- Every brand has one or more models.
- Every model is produced as part of a brand. For example, Iguana Bay Spas is a manufacturer that produces Big Blue Iguana spas, a premium-level brand, and Lazy Lizard spas, an entrylevel brand. The Big Blue Iguana brand offers several models, including the BBI-6, an 81-jet spa with two 6-hp motors, and the BBI-10, a 102-jet spa with three 6-hp motors.
- Every manufacturer is identified by a manufacturer code. The company name, address, area code, phone number, and account number are kept in the system for every manufacturer.
- For each brand, the brand name and brand level (premium, mid-level, or entry-level) are kept in the system.
- For each model, the model number, number of jets, number of motors, number of horsepower per motor, suggested retail price, HW retail price, dry weight, water capacity, and seating capacity must be kept in the system.
Your task
Create an ERD for each of the above statements that describe the current business rules for HW. For each new statement being the business rule.
Use Crows Foot notations and include all of the following in each part:
- All entities with Primary key and any foreign key attributes; (3 marks)
- The relationships between entities; (2 marks)
- The cardinality and optionality of each relationship; (3 Marks)
- Any M:N relationships are be resolved to 1:M relationships and ERD description is clear; (2 Marks)
- Include your Student ID and Full Name under the ERD (in io) then copy the whole ERD as an image to your Word Document.
**Additional 1 mark is given when following all the above correctly and producing a
neat ERD with correct notations.
NOTE:
- Use https://www.draw.io to create your ERD
- Include the PKs where appropriate
- Manually drawing the ERD is NOT acceptable.
NOTE: For guidance on how to draw an ERD based on business rules, watch this YouTube video: https://www.youtube.com/watch?v=YvJ4t9_2SWk
Rationale
Subject learning outcomes
This assessment task will assess the following learning outcome/s:
- be able to model complex business rules using Entity Relationship Diagrams (ERDs).
- be able to research a recent development in the field of database technology.
Graduate learning outcomes
This task also contributes to the assessment of the following CSU Graduate Learning Outcome/s (https://student.csu.edu.au/study/glo):
- Information and Research Literacies (Knowledge) - CSU Graduates demonstrate that disciplinary knowledge is developed through research and evidence.
Marking criteria and standards
The marking criteria for this assignment are:
Criteria |
HD |
DI |
CR |
PS |
be able to research a development in the field of database technology. |
Demonstrates an excellent ability at researching recent developments in the field of database technology with all the responses supported by examples and use correct terminology. |
Demonstrates a good ability at researching recent developments in the field of database technology with most responses supported by examples and use correct terminology. |
Makes a genuine attempt at researching recent developments in the field of database technology with some responses supported by examples and use correct terminology. |
Limited knowledge of the implementation considerations and a basic explanation of the security issues. |
PART A:
PART B:
Criteria HD DI CR PS
be able to Model identifies Model identifies Model Model model all PKs, PKs, attributes identifies key represents
complex attributes and PKs, key business
business and relationships attributes and rules and
rules using relationships. with minor relationships. draws out
Entity Model omissions. Model the key
Relationship accurately Model represents relationships.
Diagrams represents all accurately key business It includes
(ERDs). four represents the rules and the related
business rules business rules includes the entities, PKs, and includes all and includes the related attributes, entities, PKs, identified entities, PKs, relationships, attributes, entities, attributes, cardinalities relationships, PKs, attributes, relationships, and associative relationships, associative optionalities entities (if associative entities (if with minor applicable), entities (if applicable), errors.
cardinalities applicable), cardinalities
and cardinalities and optionalities. and optionalities.
optionalities.
Presentation
Use Crows Foot notations and visit https://www.draw.io to create your ERD.
Requirements
Submit as a Word document: Add table of contents indicating part A and Part B. Embed your ERD as an image/object (use screenshot or snipping tools) into the Word document.
Don't use any other file formats
Don't submit your file in XML
Please include your student ID, name and subject code in the diagram
Assessment item 3
Normalisation
Value: 15% Return Date: 09-Oct-2019
Due Date: 13-Sep-2019
Submission method options: Alternative submission method
Task
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 Value
EMP_NUM 123 104 118
PROF_RANK Professor Asst. Professor Assoc. Assoc.
Professor Professor
EMP_NAME Ghee Rankin Ortega Smith
DEPT_CODE CIS CHEM CIS ENG
DEPT_NAME Computer Chemistry Computer Info. English
Info. Systems
Systems
PROF_OFFICE KDD-567 BLF-119 KDD-562 PRT-345
ADVISEE 1215, 2312, 3102, 2782, 2134, 2789, 2873, 2765,
3233, 3311, 3456, 2238,
2218, 2098 2008, 2876, 2002, 2046, 2901, 2308
2222, 2018,
3745, 1783, 2764
2378
COMMITTEE_CODE PROMO, TRAF DEV SPR, TRAF PROMO, SPR
APPL, DEV DEV
JOURNAL_CODE JMIS, QED, JCIS, JMGT
JMGT
Given the information in Table 1:
- Draw the dependency diagram. (3 marks)
- Identify the multivalued dependencies. (3 marks)
- Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks)
- 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 relational schema is NOT acceptable.
Rationale
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
The marking criteria for this assignment is:
Criteria HD DI
CR PS
be able to apply Analysis Analysis Analysis Analysis
normalisation identifies all PKs identifies PKs identifies key identifies key techniques to and functional, and functional, PKs and PKs and optimise a partial and partial and functional, functional database transitive transitive partial and dependencies design. dependencies. dependencies transitive and draws Accurately with minor dependencies. out relevant identifies all omissions. Identifies key 3NF relations.
3NF Accurately 3NF relations, Creates an relations, identifies 3NF including PKs ERD that including PKs, relations, and relevant represents relevant including PKs, attributes and identified 3NF attributes and relevant referential relations and referential attributes and integrity includes the integrity referential constraints. related constraints. integrity Creates an ERD entities, PKs, Creates an ERD constraints with that represents attributes, that represents minor identified 3NF relationships, all 3NF relations omissions. relations and cardinalities and includes all Creates an ERD includes the and entities, PKs, that represents related entities, optionalities.
attributes, identified 3NF PKs, attributes, relationships, relations and relationships, associative includes all associative entities (if entities, PKs, entities (if applicable), attributes, applicable), cardinalities relationships, cardinalities and associative and optionalities. entities (if optionalities.
applicable),
cardinalities and optionalities.
Requirements
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
Query databases using SQL
Value: 15% Return Date: 28-Oct-2019
Due Date: 04-Oct-2019
Submission method options: Alternative submission method
Task
Task: Complex SQL Queries 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 sqlite3.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 the 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 following tasks (Each Query : 1.5 (0.75 query+ 0.75 for screenshot) Marks)
- Show sums of line units for each invoice.
- Show the details of the products that do not have a value for the attribute v_code.
- Show the details of the invoices whose subtotal is greater than 24 but less than 76.
- Show the details of the invoice who has the minimum subtotal.
- Show the codes and names of the vendors who supplied products.
- Using EXCEPT show the codes of the vendors who did not supply any products.
- Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
- 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.
- Show the details of the employees who are located in area code 615.
- Using inner join, list the details of the products whose line price is greater than 99.
You are required to submit:
- 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.
- The screenshots of the results of the SQL 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
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
The marking criteria for this assignment is:
Criteria HD DI CR PS
be able to use SQL syntax SQL syntax SQL syntax SQL syntax complex SQL returns all returns all returns key returns key
commands to attributes that attributes that attributes that attributes that
query a meet the meet the meet the meet the database. business business business business
requirement requirement from requirement from requirement from the tables the tables that the tables that from the tables that hold the hold the data hold the data and that hold the data and with minor applies relevant data but the applies the omissions and restrictions to restrictions correct applies retrieve the applied to
restrictions to appropriate required retrieve the retrieve the restrictions to information. required
required retrieve the information information. required contain minor information. errors.
Presentation
The document must be prepared in a professional manner with clarity and sound organization.
Requirements
Either Submit Your Coursework 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.
Assessment item 5
Final Exam
Value: 50% Duration: 2 hours
Due Date: To be advised
Submission method options: N/A - submission not required/applicable
Requirements
The examination is a Closed book examination.
All questions must be answered.
The examination consists of:
- Answer 20 MCQ questions
- Draw an ERD
- Normalise to 3NF
- Write SQL statements
- Answer 4 short answer questions
Rationale
Subject learning outcomes
This assessment task will assess the following learning outcome/s:
- be able to model complex business rules using Entity Relationship Diagrams (ERDs).
- be able to apply normalisation techniques to optimise a database design.
- be able to use complex SQL commands to query a database.
Graduate learning outcomes
This task also contributes to the assessment of the following CSU Graduate Learning Outcome/s (https://student.csu.edu.au/study/glo):
- Information and Research Literacies (Knowledge) - CSU Graduates demonstrate that disciplinary knowledge is developed through research and evidence.
Marking criteria and standards
Criteria HD DI CR PS
Demonstrates an Demonstrates a Makes a genuine The answers do excellent ability at good ability at attempt at
This applyingconceptsthelearned applyingconceptsthelearned applyingconceptsthelearned notthelearnedconceptslink wellin thiswith assessment in this subject to in this subject to in this subject to subject but show assess the the questions the questions the questions effort is made at first three with all the with most with some supporting the subject answers answers answers answers with
learning supported by supported by supported by examples and outcomes examples and use examples and examples and using correct correct use correct use correct terminology. terminology. terminology. terminology.
Marks will be given based on the correctness of the answers.
- HD - At least 85% answers were correct
- DI - At least 75% answers were correct
- CR - At least 65% answers were correct
- PS - At least 50% answers were correct
Material provided by the university
Answer Booklets (1 X 12 page)
General Purpose Answer Sheet GPAS-200R
Material provided by the student
Writing implements, including a 2B pencil and an eraser.
Any calculator allowed, including programmable calculators (hand held, no printer). iPads and other hand-held computers are not accepted as calculators.
Sample exam paper
You are encouraged to complete this subject's sample exam (https://doms.csu.edu.au/csu/ file/08754316-08a6-4646-a27c-a5fe5b6ab4cd/1/ITC556-DatabaseSystems-
SAMPLExam201890%20--with-Solution.pdf?) in preparation for the end-of-session exam.
Assessment Information
Academic integrity
Academic integrity means acting with honesty, fairness and responsibility, and involves observing and maintaining ethical standards in all aspects of academic work. This subject assumes that you understand what constitutes plagiarism, cheating and collusion. If you are a new student we expect you to complete the modules called Academic Integrity at CSU (http://student.csu.edu.au/library/integrity/academic-integrity-course).
CSU treats plagiarism seriously. We may use Turnitin to check your submitted work for plagiarism. You can use Turnitin to check for plagiarism (http://student.csu.edu.au/library/ integrity/referencing-at-csu/checking) in your assessments before submission.
Referencing
Referencing is an important component of academic work. All assessment tasks should be appropriately referenced. The specific details of the referencing requirements are included in each assessment task description. Get referencing style guides and help
(http://student.csu.edu.au/library/integrity/referencing-at-csu) to use for your assessments.
How to submit your assessment items
Online submission process
Assessment items that are completed within the Interact 2 site, such as quizzes, tests and self and peer assessments in the blog and wiki are noted in the assessment section above. You need to complete these tasks within your subject site.
Assessment tasks that are NOT completed through the Subject site need to be submitted electronically via Turnitin site by the due date. Turnitin class details for this subject will be given by your lecturer.
Unless advised otherwise, all Turnitin submissions are due by midnight (AEST) of the date specified. Please note that the time and the date of your Turnitin submission will be used to determine your official submission time.
All textual elements within an assessment must be submitted in a format that is readable by Turnitin. Specific exceptions, where an assessment requires the insertion of image-based evidence of workings will be outlined in the context of the assessment. Students that deliberately attempt to insert the content of assessments in a format that is not readable by Turnitin may be subject to Academic misconduct investigations.
Additional Submission Information:
It is recommended that your name, student ID and page number are included in the header or footer of every page of any assignment. You are also required to rename your assignment file before you submit via Turnitin as per below protocol:
SUBJECT CODE, SI, SURNAME, STUDENT ID, ASSESSMENT NUMBER, SESSION.
Example – ITC561 SI PATEL 11554466 A1 201960.doc
Postal submission process
Under normal circumstances postal submissions will not be accepted for any of the assessments required.
Hand delivered submission process
Under normal circumstances hand delivered submissions will not be accepted for any of the assessments required.
Alternative submission process See online submission above.
Extensions
It is best to complete assessment items by the due date. However, when something unavoidable comes up an extension may be possible. The following principles are used when processing extensions:
- For in-session assessment items, an extension request for up to three (3) calendar days canbe made by emailing your subject coordinator directly before the due date. In your email please state the reason why you need more time as well as what precisely you are requesting. Supporting documentation is not required. If an extension is requested in the above format with a valid reason and your request does not disadvantage other students, the extension will be approved.
- For in-session assessment items, extension requests of more than three (3) calendar daysmust be made via the special consideration form: https://apps.csu.edu.au/specialcons/. The request must be made before the due date and must include supporting documentation. Acceptable reasons are given in the Special Consideration Policy<https://policy.csu.edu.au/ view.current.php?id=00298>. Each request will be considered on a case by case basis. The request may not be granted. The maximum extension possible will be seven (7) calendar days.
- If you receive an extension, then you should expect the assessment item and its feedback tobe returned later. If you submit later than the extended due date you will receive late penalties as per guidelines below.
- Unless your extension permits otherwise, submissions received 10 days after the originaldue date will receive zero.
- For end of session exams, you can request a supplementary exam via
https://apps.csu.edu.au/specialcons/. This request must be made within 3 working days of the date of exam and must include supporting documentation. Acceptable reasons are given in the Special Consideration Policy <https://policy.csu.edu.au/view.current.php?id=00298>. For medical issues, a CSU medical certificate is required. If the supplementary exam (SX) is awarded then your exam is moved to the next examination period. In order to preserve exam integrity and manage the logistics of exams, the timing of a supplementary exam
How to apply for special consideration
Academic regulations provide for special consideration to be given if you suffer misadventure or extenuating circumstances during the session (including the examination period) which prevents you from meeting acceptable standards or deadlines. Find the form on the Student Portal Special Consideration, Misadventure, Advice and Appeals (http://student.csu.edu.au/ study/academic-advice) page. Penalties for late submission
The penalty for late submission of an assessment task (without obtaining the Subject Coordinator's approval for an extension) will be:
10% deduction per day, including weekends, of the maximum marks allocated for the assessment task, i.e. 1 day late 10% deduction, or 2 days late 20% deduction.
An example of the calculation would be:
Maximum marks allocated = 20
Penalty for one day late = 2 marks (so, a score of 18/20 becomes 16/20 and a score of 12/20 becomes 10/20).
If an assignment is due on a Friday but is not submitted until the following Tuesday, then the penalty will be four days (40% deduction or 8 marks in the example above).
Submissions more than 10 days late will be acknowledged as received but will not be marked.
Resubmission
Under normal circumstances resubmission of assessment items will not be accepted for any of the assessments required in this subject.
Feedback processes
Feedback for assessment items will be provided by subject lecturer/s.
Assessment return
You should normally expect your marked assignment to be returned to you within 15 working days of the due date. If you submitted your assignment on time but have not returned by the return date, you should make enquiries in the first instance to the subject lecturer. If the subject lecturer is not available then contact your Course Coordinator on Level 4, 30 Church Lane.
Student Feedback & Learning Analytics
Evaluation of subjects
CSU values constructive feedback and relies on high response rates to Subject Experience
Surveys (SES) to enhance teaching. Responses are fed back anonymously to Subject
Coordinators and Heads of Schools to form the basis for subject enhancement and recognition of excellence in teaching. Schools report on their evaluation data; highlighting good practice and documenting how problems have been addressed. You can view a summary of survey results via the Student Portal SES Results (https://student.csu.edu.au/study/subjectexperience-survey-results) page.
We strongly encourage you to complete your online Subject Experience Surveys. You will be provided with links to your surveys via email when they open three [3] weeks before the end of
session.
Changes and actions based on student feedback
This subject has been developed over several years and we have included the feedback from students in previous years when preparing this subject. In particular, this year the subject has been changed in response to feedback from last year’s class in the following ways:
- A set of current and relevant web links and resources that can be used during the subject, as well as at work.
- More time spent discussing the planning the migration of business applications and services to the Cloud.
Learning analytics
Learning Analytics refers to the collection and analysis of student data for the purpose of improving learning and teaching. It enables the University to personalise the support we provide our students. All Learning Analytics activities will take place in accordance with the CSU Learning Analytics Code of Practice. For more information, please visit CSU's Learning Analytics (http://www.csu.edu.au/division/student-learning/home/analytics-and-evaluations/ learning-analytics) website.
Data about your activity in the Interact2 site and other learning technologies for this subject will be recorded and can be reviewed by teaching staff to inform their communication, support and teaching practices.