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:

1. Draw the dependency diagram. (3 marks)
2. Identify the multivalued dependencies. (3 marks)

1. Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks)
2. 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)

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 greater 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 codes 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 details 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 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 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.

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:

1. 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.
2. 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.
3. 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.
4. Unless your extension permits otherwise, submissions received 10 days after the originaldue date will receive zero.
5. 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.