ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 1 of 8
Assignment 1
SQL Database Design and Implementation
Purpose and Learning Outcomes
Purpose
The purpose of the assignment is to provide students with the opportunity to apply knowledge and skills developed during the semester with particular reference to:
• Interpretation of business rules from a case study;
• Conceptual data modelling through the creation of an Entity Relationship(ER) model;
• Application of DDL and DML components of SQL to:
o create and populate a relational database; and
o query the created relational database.
Learning Outcomes
The learning outcomes directly assessed are:
Knowledge:
K4. Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization
K5. Describe relational algebra and its relationship to Structured Query Language (SQL).
Skills:
S1. Interpret entity-relationship diagrams to implement a relational database.
S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
S3. Use a query language for data manipulation.
Application of Knowledge and Skills:
A1. Design and implement a relational database using a database management system.
A2. Utilise a query language tools and techniques to obtain data and information from a database.
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 2 of 8
Timelines and Expectations
Percentage Value of Task: 30% of the course marks
Due: Refer to Course Descriptor or Moodle
Minimum time expectation: 25 hours
Students are required to complete the assignment individually.
Students are expected to submit the required report and details (see below) to the submission box in their
Moodle shell.
Assignment Requirements
Overview
Students are expected to develop their own case study and then interpret that to create an ER model of that
system.
They are then expected to provide a physical implementation of the ER model in the form of the DDL to
create the required tables, attributes and relationships.
Students are then required to provide the DML to insert sufficient information into the database to answer a
set of queries.
Finally, students are expected to provide the DML to interrogate the database to answer the queries posed.
They should also provide proof of the running of those queries by providing images of the output obtained.
It is a requirement of this assignment that students use Postgres for the database components.
The submission must be presented in the format of a professional report. Further information is given in the
Detailed Requirements and Marking Criteria sections of this document.
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 3 of 8
Background
You are to design a scenario of your choosing. This scenario will be modelled initially as an ERD, and then implemented in SQL statements. As well as creating the requisite SQL Tables, you will need to manufacture data that is imported into these Tables. You will then be required to develop several SQL queries to provide key functionality for your database.
Requirements
1. Scenario choice: your chosen scenario must include at least FIVE entities which are related to each other. This really can be anything you choose, but you are advised to choose something you have knowledge of, to make the exercise easier. An example could be a hobby that you have – perhaps you enjoy a sport, and you wish to develop a database that models some form of performance related to the teams and games played in this sport. Another example could be a part-time job you may have, and you may wish to develop a database to assist some aspect of that business. Another example could be some area of science, perhaps an engineering type application that models a power station or a factory or an experiment of some kind. Because this “brief” is very open-ended, you are recommended to check your chosen scenario with the teaching staff to make sure it is suitable. A final consideration when thinking of your scenario are the list of SQL queries that you will need to execute within your database. For instance you will need to include certain search and statistical functions, so your scenario will need to be sophisticated enough to make these queries possible. PLEASE NOTE: each student is required to develop a UNIQUE scenario – it will not be permitted that two students use the exact same scenario.
2. Your ER Diagram must include at least FIVE entities. There is no upper bound on how many entities you choose, but you are advised to include no more than ten. Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. You will need to normalise all of your entities, to resolve any many to many relationships.
Observe the following restrictions when creating your scenario:
• Include a specialisation hierarchy, with super types and overlapping or disjoint sub types
• Include entities to model time-variant data
• Include an example of both composite and surrogate primary keys
• One (or more) of your entities must have a numeric field
• One (or more) of your entities must contain an alphanumeric (varchar) field
• Do not have include cyclic relationship (A->B->C->A)
Your attribute names, primary and foreign keys should be indicated as per the conventions given in the lecture slides (i.e. attributes as proper nouns, primary key underlined and foreign keys in italic
All many to many relationships should be resolved, and you may wish to include a discussion of normalisation including the normal form that each entity is in and why that is optimal.
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 4 of 8
For each entity, you must create some example data for that entity. Include at least 10 rows of data for each entity. This data must be initially stored in a text file that will be imported into the database.
Write the SQL statements to create the table structures from your developed ERD. The structures should contain the attributes specified in your ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by your ERD. Write the SQL code to insert your data from (3) into the SQL table structures.
Write the SQL statements that provide various database functionality
Detailed Requirements
This assignment is an individual assignment. It is a requirement of this assignment that students use Postgres for the database components.
Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content:
1. An ER model of the case study system. This should conform to third normal form. Students are able to use any drawing package to present the ER diagram but the diagram should use the Crows foot notation and conform to the standards identified in Coronel and Morris (2018). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FK identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity and attribute names to be in upper case. All relationships should be labelled and identified as weak (non-identifying)/strong (identifying) ones. All connectivity, participation and cardinalities (if there are specific limits) should be shown. For example ER diagrams see Figures 4.31 and 4.35 of Coronel and Morris (2018).
2. A screen shot of the pgAdmin 4 GUI showing the creation of a database with the name ITECH2004_yourStudentID_Delivery_System.
3. The DDL statements required to create an implementation of the conceptual data model above. Students must use Postgres and their created database to create these tables, attributes and relationships. Transaction and Commit statements should be included in the DDL. They should include DROP TABLE commands where necessary and must show the correct order of creation. Appropriate constraints must be created. Students must follow the naming conventions i.e. uppercase for keywords, lowercase names for tables and attributes with an underscore between words and new line for each clause. Students should use the default schema i.e. there is no need to create one.
4. DML statements to insert sufficient data into the database to correctly answer a set of queries. Transaction and Commit statements should be included in the DML.
5. DML statements and screen shots of the correct operation of the following queries. Students should ensure that they follow conventions in their writing of SQL – uppercase for keywords, lower case for table and column names and new line for each clause:
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 5 of 8
Query 1. Write an SQL statement that correctly uses implements an inner join. Explain the purpose of your query for your database solution.
Query 2. Write an SQL statement that correctly uses implements an outer join. Explain the purpose of your query for your database solution.
Query 3. Write an SQL statement that correctly uses your specialisation hierarchy. Explain the purpose of your query for your database solution.
Query 4. Write an SQL statement that correctly uses the ‘GROUP BY’ and ‘HAVING’ operators to select an aggregation of data (e.g. SUM, AVG, COUNT). Explain the purpose of your query for your database solution.
Query 5. Write an SQL statement that uses an inner SQL query (SELECT). Explain the purpose of your query for your database solution.
Query 6. Write an SQL statement that correctly uses the ‘UPDATE’ symbol to modify at least three rows of data in one of your tables. Explain the purpose of your query for your database solution.
Additional Information
General Comments
The submission must be presented in a professional, clear and concise manner. If you need further system information, please use your initiative and make reasonable and logical assumptions. State your assumptions in your report. Ask your lecturer or tutor for further information.
Readings
Your text, course material and references listed on the Course Description will assist you with this assignment.
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 6 of 8
Submission
Each student should submit an electronic copy of their report via Moodle. Please refer to the Course Description for information regarding late assignments, extensions, special consideration, and plagiarism. A reminder all academic regulations can be accessed via the university’s website, see:
http://federation.edu.au/staff/governance/legal/feduni-legislation/feduni-statutes-and-regulations
Students are reminded that there are supports available regarding writing, researching and general academic skills. Various sources of help are available at:
https://federation.edu.au/current-students#Learning_and_study;
https://studyskills.federation.edu.au/student-skills/; and
https://federation.edu.au/library/student-resources.
Documentation requirements
1. Prepare a report (single Word or PDF document) which contains the following:
a). ER diagram based on your created scenario
b). Example data
c). A copy of your SQL code – you must also provide associated output (as screengrabs, inserted into the report)
d). Statement of any resources used. These includes full disclosure of assistance from all sources including tutors and other students. Full APA referencing of any resources used.
Report file name: ITECH2004_Assignment1_Report_yourname_studentID
A separate copy of the SQL statements, file name: ITECH2004_Assignment1_SQL_yourname_studentID.txt
Feedback
Assessment marks will be made available in fdlMarks, Feedback to individual students will be provided via Moodle or as direct feedback during your tutorial class.
Plagiarism
Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without properly acknowledging that person. You must not allow other students to copy your work and must take care to
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 7 of 8
safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at:
http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism
Any support material must be compiled from reliable sources such as the academic resources in Federation University library which might include, but not be limited to: the main library collection, library databases and the BONUS+ collection as well as any reputable online resources (you should confirm this with your tutor).
References
Coronel, C., & Morris, S. (2018). Database systems: Design, implementation & management (13th ed.). Cengage Learning.
Feedback
Feedback and marks will be provided in Moodle. Marks will also be available in FDL Marks.
Marking Criteria
Work will be assessed according to the details provided in the following Marking Rubric.
ITECH2004 – DATA MODELLING ASSIGNMENT 1, SEM2 2022
ITECH2004 Assignment1 ERD_SQL_2022.docx Page 8 of 8
Assessment Criteria
Marking Scale
ER Model
• This should conform to third normal form.
• Must be valid disjoint subtype entities and hierarchy included.
• Crows foot notation and conformance to the standards identified in Coronel and Morris (2018). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FKn identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity and attribute names to be in upper case. All relationships should be labelled and identified as weak (non-identifying)/strong (identifying) ones.
• All connectivity, participation and cardinalities (if there are specific limits) should be shown
• All appropriate entities, attributes and relationships identified
/25
Database creation image
• A screen shot of the pgAdmin 4 GUI showing the creation of a database with the name ITECH2004_yourStudentID_DB_System.
/2
DDLStatements
• The DDL statements required to create a physical implementation of the conceptual data model above.
• Students must use Postgres to create these tables, attributes and relationships.
• Transaction and Commit statements should be included in the DDL. They should include DROP TABLE commands where necessary and must show the correct order of creation. Appropriate constraints must be created.
• Students must follow the naming conventions i.e. uppercase for keywords, lowercase names for tables and attributes with an underscore between words and new line for each clause.
/10
DML Statements to Insert Data
• DML statements to insert sufficient data into the database to correctly answer a set of queries.
• Transaction and Commit statements should be included in the DML
/10
DML Queries and Screenshots
Make sure you follow conventions in your writing of the SQL – uppercase for keywords, lower case for table and column names and new line for each clause (deduct 2 marks if not followed):
• Query 1
• Query 2
• Query 3
• Query 4
• Query 5
• Query 6
/18
Report style and presentation
• Report is well written using professional language and adheres to guidelines given for this assessment (Any assumptions must be clearly stated and appropriate)
/5
Comments
/
Total Mark [70 marks]
0.0
Course Mark [30%]
0.0