ITECH2004 – Data Modelling Assessment

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.

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 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

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.

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) 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
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 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 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:

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.