INFS601 LOGICAL DATABASE DESIGN
SEMESTER 1, 2020
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
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 practiced 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.
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.
Page 2 of 8
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 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.
Page 3 of 8
2. Assignment Parts and Tasks
Part A Entity Relationship Modelling and Logical Database Design (50 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
Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2
Task 2 Construct a logical Entity Relationship Diagram (ERD) [25 marks]
Based on the business rules developed in Task 1, construct a logical ERD for the case study using the
Visual Paradigm tool. (10 marks)
• 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
o strong and weak entities
o strong (identifying) and weak (non-identifying) relationships
• Verb phrases for relationships should be shown in both directions, except where they are
adjacent to an associative entity, in which case only the verb phrases related to the
bounding entities need to be shown.
• 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 connectivity for each relationship.
• Identify the mandatory/optional dependencies for the relationships.
• 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 and derive the relational schema [15
Use the technique of normalisation to validate the logical ERD data model that you developed in
Task 2 is in third normal form. (15 marks)
• Select two entities from your ERD. For each of these entities, discuss how and why they
are in 3NF:
o provide justification using the concepts of functional dependencies and the
steps involved with the first three normal formals.
Page 4 of 8
Part B Database Implementation (25 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 [15 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. Refer to Appendix
A of case study specification for examples of sample data. 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.
Page 5 of 8
Example for Part C task 6: An example of a query from an Employee database is given below for your
• 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
staff.staff_no, staff_fname|| ‘ ‘ || staff_lname “Staff Name”, qual_type Qualification
qual_type = ‘&Qualification’
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
Page 6 of 8
3. Submission Instructions
You must submit a soft-copy of your assignment on Blackboard before the specified due date and
All Parts: Due Friday June 19th, 5:00 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 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, VP & zip file) are named clearly with your
full name & ID number.
Page 7 of 8
4. Marking Scheme
Task Marking Criteria Max Mark
Part A Entity Relationship Modelling and Logical Database Design
Part A –
Identify Entities & Develop Business Rules
• Cover all the essential requirements from the case study
• Uses correct syntax for business rules
Part A –
Construct a logical Entity Relationship Diagram
• Identify all attributes in each entity including all primary and
• Identify (a) strong and weak entities and (b) strong
(identifying) and weak (non-identifying) relationships
• Identify the connectivity for each relationship.
• Identify the mandatory/optional dependencies for the
• Resolve all many-to-many (M:N) relationships.
• Includes any assumptions, if any.
Part A –
Use Normalisation to validate your ERD
• Discuss and provide justification for two entities selected from
the ERD that they are in third normal form (using the concepts
of functional dependencies and the steps involved with the
first three normal formals).
Part A Total 50
Part B Database Implementation
Part B –
Oracle Table Creation Scripts for all tables in the database
• correct identification and definition of all attributes and
• 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.
Part B –
Oracle Insert Scripts for populating the tables in your database:
• Insert statements along with sample realistic test Data
• the results of SELECT statements
Part B Total 25
Page 8 of 8
Part C Construct SQL Queries
Part C –
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx