INFS601 LOGICAL DATABASE DESIGN

SEMESTER 1, 2021

Individual Assignment

Database Project

50% of the final grade

Due: 11.59pm, 4th June 2021

1. Introduction to Assignment Requirements

This assignment is designed to provide students with practical exposure to the stages involved in database development including the development of entity relationship modelling, database design and implementation.

This is an individual assignment. It provides an opportunity for students to apply to a larger case study the database concepts discussed in class and practised in exercises.

The case study, described in detail, is provided on Blackboard under Assessment -> Assignment - Database Project. Please read the case study (available as a separate file) carefully for more details. Students may ask for additional  clarification of the case study on the discussion board on Blackboard. The details of the three parts along with the tasks are specified in Section 2. Section 3 describes the submission details and dates. The marking scheme for the three tasks is provided in Section 4.

Expectations

The assignment includes 3 parts. Part A consists of Tasks 1, 2 and 3. Part B is comprised of Tasks 4 and 5. Part C has task 6. Students are required to attempt all the tasks.

Tools & Templates

You are to use the specified CASE tool, Visual Paradigm CE version for this assignment.

Plagiarism

Plagiarism means borrowing from the work of another without indicating by referencing (and by quotation marks where exact phrases are borrowed) that the ideas expressed are not one’s own. Students may use the ideas and information of other authors, but this use must be acknowledged. It is not acceptable to submit an assignment that is simply paraphrasing of extracts from other authors: the work submitted must include some intellectual contribution of the student.

Unauthorised Collaboration

Unauthorised collaboration means joint effort between students or students and others, in preparing material submitted for assessment, except where this has been pre-approved by the paper programme. Students are encouraged to discuss matters covered in classes, but the expression of ideas and arguments must be the student’s own work.

2. Assignment Parts and Tasks

Part A Entity Relationship Modelling and Logical Database Design (45 marks)

Task 1 Identify Entities & Develop Business Rules [10 marks]

Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Each relationship must be described by two rules, one in each direction.

Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2

Task 2 Construct a logical Entity Relationship Diagram (ERD) [20 marks]

Based on the business rules developed in Task 1, construct a logical ERD for the case study using the Visual Paradigm tool.

• Identify all attributes in each entity including all primary and foreign keys (any composite and multivalued attributes must be transformed according to the rules of the relational model).

• All relationships must be named (i.e. Verb phrases must be shown for all relationships)

• If super/subtypes are used, indicate whether the super-subtype relationships are disjoint or overlapping, and fully or partially specialised (complete or incomplete).

• Identify the Cardinality and Participation for each relationship.

• Resolve all M:N relationships.

• Describe any assumptions you have made in a text note in the diagram.

Task 3 Use Normalisation to validate your ERD [15 marks]

(a) Use the technique of normalisation to validate your logical ERD data model . Demonstrate that each of your relations is in third normal form (3NF) by displaying (a list of dependencies) the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model.

Part B Database Implementation (30 marks)

In this part you should develop a database based on your logical ERD developed and validated in Part A. You must populate the tables with sample realistic data.

Task 4 Create tables [20 marks]

Create the tables in Oracle. Write SQL scripts defining each table. The table definitions should include

(1) All attributes with appropriate data types

(2) all appropriate constraints such as primary key, foreign keys, and check statements. All constraints must be given names. Naming standards must be used.

Task 5 Populate data [10 marks]

• Insert a small sample of realistic test data (5 –10 rows min.) into each table. All test data must be meaningful in order to demonstrate your understanding of the data.

For Part B, you must include the following for each table in your database:

• SQL table creation script

• SQL insert script

• Sample data (include results of SELECT statements for each table)

Part C Construct SQL Queries (25 marks)

Task 6 Construct SQL Queries [25 marks]

Identify ten data retrieval and transaction requirements that would be used by the business described in the case study. For each of the requirements that you have identified, construct SQL queries needed to display the data. The queries must include a combination of the following SQL features:

• Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columns

• Use of comparison and logical operators

• Sorting output rows

• Multiple table join (Equijoin and Outerjoin)

• Single row and multiple row functions

Note: You may combine some of the preceding list of features in a single query. The design of each query should demonstrate your understanding of the correct usage of the various features and clauses of SQL. A simple ‘select * from table_name’ will fetch you a mark of zero. There should be no more than two queries that involve a single table.

For Part C, you must include the following for each SQL query statement:

■ Description of the purpose of the query.

■ SQL SELECT query

■ The result of the query.

Example for Part C task 6: An example of a query from an Employee database is given below for your reference

• Purpose of the query:

To search for staff with a specific qualification (e.g. BSc) and retrieve the following details (Staff Number, Staff Name (Concatenation of Last and First names) and their Qualification.

• SQL SELECT query

SELECT

staff.staff_no, staff_fname|| ‘ ‘ || staff_lname “Staff Name”, qual_type Qualification

FROM

staff, qualification

WHERE

qual_type = '&Qualification'

AND

staff.staff_no = qualification.staff_no;

• Output/Result of the query

Input for Qualification type variable = “BSC”

Staff No	Staff Name	Qualification
101	John Smith	BSc
212	Mike Bird	BSc
112	Susan Carr	BSc
117	David Cameron	BSc

3. Submission Instructions

You must submit a soft-copy of your assignment on Blackboard before the specified due date and time.

All Parts: Due Friday June 4th, 11:59 pm

1. Submit a ZIP file on Blackboard. The file must contain the following:

• DCT’s Individual Assignment Cover Sheet (available for download from the Blackboard). Please also make sure you write your lab tutor’s name and your tutorial day and time correctly.

• A PDF or Word file containing your work for all the Tasks in Parts A, B, and C and your student ID and name written on the first page of the document. A JPG/PNG image should be used when capturing your ERD diagram. Use appropriate formatting options (e.g. clear background, large fonts) so that the diagram should be clear and readable.

• A Visual Paradigm file containing the ERD for Part A.

Please ensure that each of these files (the PDF or Word, VP & zip file) are named clearly with your full name & ID number.

4. Marking Scheme

Task	Marking Criteria	Max Mark
Part A Entity Relationship Modelling and Logical Database Design
Part A – Task 1	Identify Entities & Develop Business Rules       • Cover all the essential requirements from the case study       • Uses correct syntax for business rules	10
Part A – Task 2	Construct a logical Entity Relationship Diagram       • Identify all attributes in each entity including all primary and foreign keys       • Name all relationships using verb phrases       • Identify Cardinality and Participation for each relationship.       • Resolve all many-to-many (M:N) relationships.       • Includes any assumptions, if any.	20
Part A – Task 3	Use Normalisation to validate your ERD       • Use the technique of normalisation to validate your logical          ERD data model . Demonstrate that each of your relations is          in third normal form (3NF) by displaying the functional          dependencies between attributes in each relation.	15
Part A Total		45
Part B Database Implementation
Part B – Task 4	Oracle Table Creation Scripts for all tables in the database       • correct identification and definition of all attributes and relationships       • identify correct data types for all attributes       • includes the correct definition of all primary and foreign key          constraints from the ERD       • uses consistent naming conventions for all table and attribute          names, constraints, and appropriate data types.	20
Part B – Task 5	Oracle Insert Scripts for populating the tables in your database:       • Insert statements along with sample realistic test Data       • the results of SELECT statements	10
Part B Total		30
Part C Construct SQL Queries
Part C - Task 6	Construct ten SQL Queries:   Each query       • Retrieves meaningful information to support the data and          transaction requirements of the case study       • each query is distinct and significantly different from one          another       • includes description of the purpose of the query       • Demonstrates knowledge and application of SQL content          covered in the course (covers important SQL features, clauses,          and functions)       • includes results (output rows)	25
Total of Parts A, B, and C		100