FACULTY OF ENGINEERING, BUILT ENVIRONMENT &

FACULTY OF ENGINEERING, BUILT ENVIRONMENT &

INFORMATION TECHNOLOGY

BACHELOR OF INFORMATION TECHNOLOGY (HONS)

Course: TCS4494 ADVANCED DATABASE Contribution: 50% of course

Part 1 (Data Modelling): 10% Part 2 (Practical): 40%

SEMESTER: JAN MAY 2024 PDF file for Report. ZIP file for SQL scripts

Course Leader: TS HAZLINA HARON Due date: 19TH APRIL 2024

Plagiarism is presenting somebody elses work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another students coursework; stealing or buying coursework from someone else and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University.

All material copied or amended from any source (e.g. internet, books) must be referenced correctly according to the reference style you are using.

Your work will be submitted for electronic plagiarism checking. Any attempt to bypass our plagiarism detection systems will be treated as a severe Assessment Offence.

INTENDED COURSE LEARNING OUTCOMES

CLO1 Explain and apply the concepts and techniques of Database Management Systems`, Database Design Methodology and Database Administration to the development and operation of an information system

CLO2 Explain and critically evaluate key concepts relating to Distributed Databases, Data Mining, Data Warehousing and OLAP.

CLO3 Systematically create an Entity-Relationship (ER) Model from a given situation and map it to a normalised relational model using CASE tools.

CLO4 Design, implement, optimise and critically evaluate an information system using an enterprise DBMS

MAPPING

Assessment Intended Course Learning Outcomes

Data Modelling CLO1, CLO2, CLO3

Database Implementation CLO1, CLO2, CLO4

Briefing

This coursework should be completed as an individual work. Part 1 component will carry 10% and part 2 component will carry 40% of total coursework marks. You must be ready to demonstrate your work to your tutor on the date specified on the header of this coursework or by your course coordinator if needed.

Deliverables:

Part 1 (Data Modelling): D1, D2, D3 (10%)

Part 2 (Implementation): D4, D5, D6, A1/B1-A4/B4 (40%) Please include proper cover page and table of content. Submission will be in 1 report but separated by Part 1 and 2.

The aim of this assignment is to undertake a range of tasks involved in the analysis, design and build of a database structure and applications for a database system. Where information is not available you should make reasonable assumptions. Please ensure that you include all business constraints that have been captured during the analysis phase. The scenario below is intentionally vague. This is to allow you the opportunity to show initiative by making sensible assumptions or spotting opportunities to remove inconsistencies/errors in the scenario.

Specification

The History Project

A local history teacher has had the idea of creating a web site which will allow pupils to develop materials on history. Whilst she is a good web programmer, designing a sustainable database for the site has fallen to you.

Any history teacher would need to set up a course and add their students to it. Each student then gets a course code and log in number. On first use, they are asked to add a password which is then used to log in subsequently. Younger or less capable classes may be required to tell their teacher their password, but that is up to the schoolteacher.

Pupils will be asked to add content as part of their history homework. This would be text or links to other sites only no images or videos. The content would be tagged by the learner for specific areas such as people, dates, location, or type of event (a battle, a meeting, a birth, etc.). This would allow the database to be searched by any of these tags so that a more rounded and personalised view of history could be created.

Each pupil would be asked to log in. When they make an entry their ID and course code should be stored automatically. The teacher could then search for those who have completed the homework (i.e. by course code) and then add a numerical mark and comments can be added for each entry. This means that the database would serve as both a teaching aid and as a marking tool. Reports can then be created for specific pupils to show progress.

The learners could generate the content on the computers in the school at home, depending on circumstances. The teacher would also be able to work at school or from home.

Requirements

Create the conceptual ERD, physical ERD, database (with test data) and queries to support the author.

Sample Queries

The following database queries are required to be displayed on screen (i.e. produce the SQL code only, no form, report or web page). Choose one of the sets of queries to answer.

List A

A1. Joining tables

List the students who have entered information about places.

A2 ORDER BY

Show the events for a particular year (entered at runtime) ordered by their date.

A3 GROUP BY HAVING

List the entries made by the students in a class grouped by the family name/surname of the students for those entries made in the last year.

A4 Sub-queries

List those place which have someone who was born there and had a battle.

Deliverables

Your electronic submission should include:

You should upload ONE (1) report and 1 zipped file:

Part 1: This report should contain D1, D2, D3 and D6.

Part 2: This report should contain D4, D5, A1/B1 A4/B4 with sufficient evidence that each step works.

Zipped file for SQL scripts (table creation, data insertion, SQL queries). This act as supporting materials should examiner need to run the scripts.

All deliverables are listed below:

D1. One A4 page containing the conceptual data model diagram (i.e. an entity relationship diagram using consistently either Chen or Crows Foot notation showing:

Relevant entity types

The primary key for each entity (underlined)

Relationship types with role names (plus relationship attributes if any)

Any sub-classes (showing disjoint or overlapping) which would make the diagram more informative.

Structural constraints on each relationship (both cardinality and participation)

Note: If you show attributes other than the primary keys (e.g. foreign keys) on the conceptual model then you will be penalised.

D2. For the above model, produce the physical data model. The model represents your mapping from the conceptual model into a relational schema and should show:

All entities and their relationships to the other entities with their cardinalities.

Resolve any sub-classes and M-M relationships as necessary.

For each prospective table identify the primary key and any necessary foreign keys then add all appropriate fields.

Identify the data types for each field.

D3. Make a clear statement of any assumptions that you make about the data, in particular noting any information that you believe should be included but is not mentioned in the outline specification or any assumptions that affect the design of your database.

D4. Create a database for the above schema using the Oracle DBMS using SQL and populate each table with typical records to clearly demonstrate the application results. Create an insertion plan and then use an on-line data generation site to generate a suitable quantity of test data for each table. Give the SQL code for the creation scripts and samples of the population scripts. You should populate the database with sufficient data to clearly and unambiguously demonstrate all queries submitted.

D5. The SQL queries used to fulfil the three queries chosen from A1 to A4 or B1 to B4.

Show the output results (i.e. screen dumps of your executed queries).

D6. Include a detailed evaluation of your work.

Marking Scheme

Conceptual Model Out Of Awarded

4

6

5

5

5

5

30 0

Reasonable assumptions made

Identifying necessary main entities/ sub-

classes

Identifying valid PKs

Identifying relationships

Cardinality of relationships

Participation constraints of relationships

Sub-total

Physical Model Out Of Awarded

5

5

5

5

20 0

Appropriate list of attributes

Sub-classes and M-Ms resolved

The correct placement of FKs

Normalisation check

Sub-total

Coursework Implementation Out Of List A List B

10

5

5

7

8

10

45 0 0

DB creation

Data population implementation

Query 1 - Joins

Query 2 - ORDER BY

Query 3 - GROUP BY HAVING

Query 4 - Sub-queries

Sub-total

5

Out Of Awarded Evaluation

100

TOTAL Grading Criteria

Assessment will be graded using the following criteria:

A: (>=75 %) Extremely well developed and presented.

Each candidate is expected to demonstrate a detailed understanding of the process of DB analysis & design (Conceptual & Physical). A completely, optimised and effective implementation of the required SQL applications (i.e. the production of a normalised relational schema with assurance is in 3rdNF). Excellent use and utilisation of facilities available by the DBMS to provide system robustness (e.g. minimising input errors, validation routines, using SQL correctly, etc.) with a sound user interface.

B: (60-74%) Well-developed and presented.

Work lacks detailed analysis of mapping process, normalisation, etc. Application requirements have been met but not in the most optimal ways (e.g. wrong mapping or placement of foreign keys). Some use of a [limited] range of DBMS facilities. User interface may not be totally robust.

C-C+ : 50% - 59% Submission contains minimum information requested and/or bad presentation.

Work and submitted report lack detailed analysis and/or displays poor understanding. No evidence of complete or fully working application(s), etc. poor demonstration of the use of DBMS facilities. Please note that the following will apply for obtaining a minimum pass mark:

F: (0-49%) Developed work does not meet the minimum standard required. This includes the following:

No evidence of understanding the purpose of the coursework.

No evidence of implementation having been performed on the DBMS regardless of the quality of the design.

Does not demonstrate the work to their tutor, or having very poor implementation (e.g. if the student is unable to produce a single fully operational query application correctly).

Marking Scheme

Components Marks Allocated Part

Assumptions 4 1

Basic identification of main entities

/ sub-classes 6 1

Identifying a proper unique identifier 5 1

Identifying relationships 5 1

Cardinality constraints 5 1

Participation constraints 5 1

Conceptual Model 30 1

Appropriate list of attributes: 5 1

Sub-classes and M-Ms resolved 5 1

The correct placement of FKs 5 1

Normalisation check 5 1

Physical Model 20 1

DB creation 10 2

Data Population 5 2

SQL Application A1/ B1/D5 5 2

SQL Application A2/ B2/D5 7 2

SQL Application A3/ B3/D5 8 2

SQL Application A4/ B4/D5 10 2

Implementation 45 2

Self-evaluation 5 1

Total 100 1 & 2